CODEWITHSUNDEEP
yandexclickhouse
slesh
slesh

Reputation: 2007

Couldn't parse csv file if value contains from '

Here is demo DDL:

CREATE TABLE X (
    eventdate        Date default toDate(now()),
    f1               String,
    f2               String,
    f3               String
) ENGINE = MergeTree(eventdate, (f1, f2, f3), 8192)

When I try to import data, clickhous fails with error:

$echo "US,'Ewa Beach, Test" | clickhouse-client --database default --query "insert into X(f1, f2, f3) format CSV"

Code: 27. DB::Exception: Cannot parse input: expected , at end of
stream.: (at row 1)

Here is my click house version(it time of writing it's latest version, if I am not mistaking):

$ clickhouse-client --version
ClickHouse client version 1.1.54385.

The problem is ' symbol in the value of field f2. Please, any workaround to make it works?

Maybe is there some option to force using a only double quote as a string delimiter, and consider single quote as plain symbol without special meaning? Please, any solution is welcome.

I just tested with same values but as tsv, and it works. But unfortunately I cannot migrate to tsv right now. I need to find out some workaround for csv to make it work with signle quote in field value.

P.S.

Interesting that if left only two fields, it parses csv correctly.

CREATE TABLE X (
    eventdate        Date default toDate(now()),
    f1               String,
    f2               String
) ENGINE = MergeTree(eventdate, (f1, f2), 8192)

The following query workds:

$echo "US,'Ewa Beach" | clickhouse-client --database default --query "insert into X(f1, f2) format CSV"

Here is related issue on the GitHub

Upvotes: 3

Views: 3060

Answers (2)

Amos
Amos

Reputation: 3276

I just ran into the same issue. I've created a PR https://github.com/yandex/ClickHouse/pull/2574

With that you can specify format_allow_csv_single_quote=0 to parse csv data with string field starting with a '

Edit:

Now it's merged.

Upvotes: 4

Imaskar
Imaskar

Reputation: 2949

You need to use double quotes for values like this. 

echo "US,\"'Ewa Beach\", Test" | clickhouse-client --host ch_srv --query "insert into X(f1, f2, f3) format CSV"

This works.

:) select * from X format CSV;
SELECT *
FROM X 
FORMAT CSV
"2018-06-28","US","'Ewa Beach","Test"
:) select * from X;
SELECT *
FROM X 
┌──eventdate─┬─f1─┬─f2──────────┬─f3───┐
│ 2018-06-28 │ US │ \'Ewa Beach │ Test │
└────────────┴────┴─────────────┴──────┘

Upvotes: 0

Related Questions