Create a table with DateTime primary key

[Joni]

Hi,

I'm trying to create a table for a dataset that has DateTime and does not have just Date column.

When I run this: 

CREATE TABLE testjoni2
(
    servingrestriction String,
  time DateTime,
  userid String,
  ip String,
  advertiserid String,
  orderid String,
  optimizationtype String
)
ENGINE = MergeTree(time, time, 8192);

I get this warning:

Code: 169, e.displayText() = DB::Exception: Could not find Date column: (while initializing MergeTree partition key from date column `time`) (version 19.11.2.7 (official build))

sHo

What to about it?

Thanks

[Denis Zhuravlev]

This is an obsolete syntax.
Try new one: ENGINE = MergeTree Partition by toYYYYMM(time) Order by time;

[Joni]

Thank you so much. The one thing,  values passed into that column look like:   2019-07-20-13:56:53   How can I make the table to treat as date time?

Thanks again.

[Joni]

I use this command to insert data

cat *.csv | curl 'http://localhost:8123/?query=INSERT INTO clickdb.testjoni3 FORMAT CSV' --data-binary @-

[Denis Zhuravlev]

just add parameter date_time_input_format=best_effort

clickhouse-client --date_time_input_format=best_effort

[Joni]

Thanks Denis,

I run clickhouse using docker in my windows.

Unfortunately, when I run clickhouse-client  my cmd does not recognize it. When I first tried it did.

Can you please a have a look at this command I used to start clckhouse docker? Something is not fully configured 

docker run --restart=always -d -p 8123:8123 -p 9000:9000 --ulimit nofile=262144:262144 -v C:\Users\joni\clickhouse:/var/lib/clickhouse yandex/clickhouse-server

[Denis Zhuravlev]

 You can pass it to http as well

curl 'http://localhost:8123/?date_time_input_format=best_effort&query=INS.....

curl requires spaces encoded as %20 in URLSs

>Can you please a have a look at this command I used to start clckhouse docker? Something is not fully configured

It looks normal. What error did you get? And how you executed?

[Joni]

>It looks normal. What error did you get? And how you executed?

I just run it in CMD of windows. It does set up Clickhouse but it does not recognize when I run clickhouse-client in cmd.

 

'clickhouse-client' is not recognized as an internal or external command,
operable program or batch file.

In python clickhouse-client works fine.

I just tried this:

cat *.csv | curl 'http://localhost:8123/?date_time_input_format=best_effort&query=INSERT%20INTO%20clickdb.testjoni3%20FORMAT%20CSV' --data-binary @-

After completing pushing csv to server it shows this warning: 

Code: 27, e.displayText() = DB::Exception: Cannot parse input: expected , before: :53,

Row 1:
Column 0,   name: servingrestriction,           type: String,   parsed text: <EMPTY>
Column 1,   name: time,                         type: DateTime, parsed text: "2019-07-20-13:56"
ERROR: garbage after DateTime: ":53,,0.0.0"
ERROR: DateTime must be in YYYY-MM-DD hh:mm:ss or NNNNNNNNNN (unix timestamp, exactly 10 digits) format.


 (version 19.11.2.7 (official build))

[Denis Zhuravlev]

Seems I was wrong. CH does not support this format with BestEffort.

So you have 2 options. Replace dashes in source file using awk/sed or insert as string to engine=Null with materialized view and replace dashed in MV SQL

[Joni]

Hi Denis,

I managed to finally turn on the clickhouse client.

I replace the dashes with spaces.

When trying to insert

 cat test.csv | curl 'http://localhost:8123/?date_time_input_format=best_effort&input_format_allow_errors_num=10&query=INSERT%20INTO%20clickdb.testjoni4%20FORMAT%20CSV' --data-binary @-


  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100  4260    0   106  100  4154   1139  44666 --:--:-- --:--:-- --:--:-- 45806

It shows this warning:

Code: 32, e.displayText() = DB::Exception: Attempt to read after eof (version 19.11.2.7 (official build))

I was reading about this error apparently about memory, but the memory is ok on my windows machine also csv is tiny.

What could it be?

Thanks,

Joni