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

0
votes

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

1
pythondatabasebigdataolapclickhouse
Could you provide the error message and way how you import data from file (is it clickhouse-client or clikhouse-driver)? Look at this git issue. - vladimir
I added the error message and the command I used for importing csv data above. Please tell me how to write Python code or any language to convert csv time data h:mm:ss to hh:mm:ss such as 6:04:07 convert to 06:04:07 - Hector Ta
just friendly advice - better insert raw text than screnshots.. - vladimir

1 Answers

3
votes

Just pass best_effort as a parameter

clickhouse-client --date_time_input_format=best_effort