CODEWITHSUNDEEP
pythondatabasebigdataolapclickhouse
Hector Ta
Hector Ta

Reputation: 91

Import data from csv file into ClickHouse database table with an incorrect input data format

I've done the task of connecting Clickhouse server/client and created TABLE. Then I want to import data from csv into that TABLE. The problem is DateTime type in ClickHouse requires format like this: YYYY-MM-DD hh:mm:ss, but the dataset I downloaded only has this time format: 2016-01-13 6:15:00 AM (YYYY-MM-DD h:mm:ss) Hour in my dataset is only h, it should be hh. Please tell me how to convert all data in csv file to a correct data format (maybe Python code or something else)? Please give me a sample.

Below is my database table:

CREATE TABLE ChicagoTaxi
(
    taxi_id Int32, 
    trip_start_timestamp DateTime, 
    trip_end_timestamp DateTime, 
    trip_seconds Int16,
    trip_miles Float32,
    pickup_community_area Int8,
    dropoff_community_area Int8,
    fare Nullable(Float32),
    tips Float32,
    tolls Nullable(Float32),
    extras Nullable(Float32),
    trip_total Nullable(Float32),
    payment_type Nullable(String),
    company Int16,
    pickup_latitude Int16,
    pickup_longitude Int16,
    dropoff_latitude Nullable(Float64),
    dropoff_longitude Nullable(Float64)
) ENGINE = Log

Here's the dataset I'm using. Please tell me if I also need to modify the data type in Clickhouse table. enter image description here

Here's the error message of DateTime format enter image description here

Upvotes: 0

Views: 4524

Answers (1)

Denny Crane
Denny Crane

Reputation: 13275

Just pass best_effort as a parameter

clickhouse-client --date_time_input_format=best_effort

Upvotes: 4

Related Questions