Handling iso 8601 format

[Todd]

New to Clickhouse and trying to experiment with it, but am stuck on my CSV data having a timestamp that is in iso 8601 with the fractional part.

seaspray:/# /usr/bin/clickhouse-client --multiline

ClickHouse client version 19.1.6.

Connecting to localhost:9000.

Connected to ClickHouse server version 19.1.6 revision 54413.

seaspray :) SET date_time_input_format = 'best_effort';

SET date_time_input_format = 'best_effort'

Ok.

0 rows in set. Elapsed: 0.001 sec. 

seaspray :) CREATE TABLE test_csv (   timestamp DateTime,   id        String ) ENGINE = MergeTree() PARTITION BY toHour(timestamp) ORDER BY timestamp SETTINGS index_granularity=8192;

CREATE TABLE test_csv

(

    timestamp DateTime, 

    id String

)

ENGINE = MergeTree()

PARTITION BY toHour(timestamp)

ORDER BY timestamp

SETTINGS index_granularity = 8192

Ok.

0 rows in set. Elapsed: 0.002 sec. 

seaspray :)

seaspray:/base/data/files# echo "2018-07-10T19:48:15.682Z,seaspray" | /usr/bin/clickhouse-client --query="INSERT INTO test_csv FORMAT CSV"

Code: 27. DB::Exception: Cannot parse input: expected , before: .682Z,seaspray\n: (at row 1)

Row 1:

Column 0,   name: timestamp, type: DateTime, parsed text: "2018-07-10T19:48:15"

ERROR: garbage after DateTime: ".682Z,seas"

ERROR: DateTime must be in YYYY-MM-DD hh:mm:ss or NNNNNNNNNN (unix timestamp, exactly 10 digits) format.

It seemed like 'best_effort' might handle this, but I haven't been able to get it to work, and am somewhat of a noob when it comes to SQL, 

Any recommendations on how to best address this? Is there some kind of conversion I can specify for my timestamp column when creating the test_csv table?

Thanks

[Denis Zhuravlev]

SET date_time_input_format = 'best_effort';
Works for inserts in current session.

To load csv you have to pass it as a parameter to ch-cli.
clickhouse-client --help|grep date_time
Or set it in /etc/clickhouse-client/config.xml