Clickhouse: problem with loading data

[trivita]

i know NULL isnt supported by clickhouse. but if i have a csv with ,,, line in it. why cant clickhouse just insert data with default values?

[alexey-milovidov]

Acknowledged. Will do.

[alexey-milovidov]

It is already supported for numeric types and String. But not for Date, DateTime.

[trivita]

It is already supported for numeric types and String. But not for Date, DateTime.

thanks, you mean the git version?

i'm still getting this

'Column 10, name: IsStored, type: UInt8, ERROR: text "22" is not like UInt8'

imho, this shouldnt be a problem. IsStored should be zero(default value)

[alexey-milovidov]

thanks, you mean the git version?

For numeric types and String, empty is parsed as zero/empty string (this behaviour was from the beginning).
Example:

:) CREATE TABLE test.csv (a String, b UInt64, c Int64, d String) ENGINE = Log

...

$ echo ',,,' | clickhouse-client --query="INSERT INTO test.csv FORMAT CSV"

...

:) SELECT * FROM test.csv

┌─a─┬─b─┬─c─┬─d─┐
│   │ 0 │ 0 │   │
└───┴───┴───┴───┘

But it is not implemented for Date, DateTime, Array and other data types.
If you have empty instead of Date/DateTime, parser will look for next field at 10/19 bytes forward, and will be confused with it.

I am going to implement the intended behaviour for Date, DateTime too.

[trivita]

ehh, yeah, but not seem to work with TabSeparatedWithNames

Column 10, name: IsStored, type: UInt8, ERROR: text "<TAB><TAB>2<TAB>2<TAB><TAB><TAB><TAB><TAB>" is not like UInt8

[alexey-milovidov]

Could you please provide complete example, because trivial examples working:

CREATE TABLE test.tsv (a String, b UInt64, c Int64, d String) ENGINE = Log

$ echo -e '\t\t\t' | clickhouse-client --query="INSERT INTO test.tsv FORMAT TabSeparated"
$ echo -ne 'a\tb\tc\td\n\t\t\t\n' | clickhouse-client --query="INSERT INTO test.tsv FORMAT TabSeparatedWithNames"

Maybe it works just accidently.

[seufagner]

@alexey-milodov Do you just implements default value to Date, DateTime? I can do this :)

[alexey-milovidov]

We have implicit default values for Date and DateTime - 0000-00-00 and 0000-00-00 00:00:00.
The problem is not with default values, but that empty string is not parsed as default in CSV.

[sunsingerus]

Hello!

It looks like Data type is still have issues with default values while reading from CSV file.
Have the following situation right now:

1. Log table with the following fields

date_1 Nullable(Date)
datetime_1 Nullable(DateTime)
timestamp_1 Nullable(DateTime)`

CSV file

date_1,datetime_1,timestamp_1,tinyint_1
0000-00-00,,,
2017-10-31,2017-10-31 11:37:42,2017-10-31 11:37:43,

All is OK

CSV file with empty Date field

date_1,datetime_1,timestamp_1,tinyint_1
,,,
2017-10-31,2017-10-31 11:37:42,2017-10-31 11:37:43,

run

tail -n +2 'ttimestamp.csv' | clickhouse-client  --host=192.168.74.251 --port=9000 --user=default --query='INSERT INTO `db`.`datatypes` (`date_1`, `datetime_1`, `timestamp_1`, `tinyint_1`) FORMAT CSV'
Code: 27. DB::Exception: Cannot parse input: expected , before: 10-31,2017-10-31 11:37:42,2017-10-31 11:37:43,\r\n: (at row 1)

Row 1:
Column 0,   name: date_1,      type: Nullable(Date),     parsed text: ",,,<CARRIAGE RETURN><LINE FEED>2017-"
ERROR: garbage after Nullable(Date): "10-31,2017"

clickhouse-client installed of the following version

sudo apt list installed 'clickhouse*'
Listing... Done
clickhouse-client/unknown,now 1.1.54304 amd64 [installed]
clickhouse-common-dbg/unknown 1.1.54304 amd64
clickhouse-compressor/unknown 1.1.54304 amd64
clickhouse-server-base/unknown,now 1.1.54304 amd64 [installed,automatic]
clickhouse-server-common/unknown 1.1.54304 amd64

Please, advise...
Thanks!

[kmatt]

I'm also seeing the error when tab delimited data to numeric types is not parsed when column values are empty:

ERROR: text "<TAB><TAB><TAB><TAB><TAB><TAB>5135" is not like UInt64

[abdrakhmanov]

i have similar problem. For example:

CREATE TABLE `t1` (
  `c0` UInt8,
  `c1` Nullable(Date),
  `c4` Date
) ENGINE = MergeTree(c4, (c4), 8192)

Insertion in tabix work fine:

INSERT INTO t1 VALUES (1,NULL,'2018-04-01')

Insert query in command-line doesn't work:

#cat c.csv
1,NULL,'2018-04-01'

cat c.csv | clickhouse-client --host=clickhouse.local --query="INSERT INTO t1 FORMAT CSV"



md5-98463e34d973cd866beedfcc561c59f4



Code: 27. DB::Exception: Cannot parse input: expected , before: -04-01\'\n: (at row 1)

Row 1:
Column 0,   name: c0, type: UInt8,          parsed text: "1"
Column 1,   name: c1, type: Nullable(Date), parsed text: "NULL,<SINGLE QUOTE>2018"
ERROR: garbage after Nullable(Date): "-04-01<SINGLE QUOTE><LINE FEED>"

What am I doing wrong?

[filimonov]

In TSV Nulls are represened as N
Usually you can check exactly which format clickhouse expect to see by writing few rows in that format by clickhouse. I.e.

SELECT * FROM t1 FORMAT TSV;

[Crazylearner30]

Trying to load data into the table and its throwing error which is not in specific

Table:

CREATE TABLE i2.company_temp (
a UInt32,
b String,
c String,
.....
......
) ENGINE = MergeTree PARTITION BY state_province_code
ORDER BY
domain SETTINGS index_granularity = 8192

Insert statement:

clickhouse-client -h1.0.1.2.3 -u default --password xxxx --query="INSERT INTO insightbasev2.company_temp FORMAT CSV"

Error:

Syntax error: failed at position 1:

clickhouse-client -h1.0.1.2.3 -u default --password xxxx --query="INSERT INTO insightbasev2.company_temp FORMAT CSV"

Expected one of: CREATE, ATTACH, DETACH, DROP, SHOW, SELECT, WITH, DESC, SYSTEM query, AST, list of elements, ALTER query, ALTER TABLE, CREATE TABLE or ATTACH TABLE query, Query with output, SHOW PROCESSLIST query, SHOW PROCESSLIST, RENAME query, RENAME TABLE, SELECT query, possibly with UNION, SET query, SHOW [TEMPORARY] TABLES|DATABASES [[NOT] LIKE 'str'], EXISTS or SHOW CREATE query, SELECT query, subquery, possibly with UNION, USE query, SELECT subquery, CHECK TABLE, DESCRIBE query, DROP query, EXISTS, TRUNCATE, INSERT query, INSERT INTO, KILL QUERY query, KILL QUERY, OPTIMIZE query, OPTIMIZE TABLE, SELECT query, DESCRIBE, Query, USE, SET

any suggestion will be appreciated

[filimonov]

@DivyaRao30 please don't duplicate your comments. I've answered you in #3847

[filimonov]

Let's continue the discussion in #469 to have a clearer backlog.