Import JSON into ClickHouse

database clickhouse
Vers_us picture Vers_us · Sep 8, 2016 · Viewed 8.4k times · Source

I create table with this statement:

CREATE TABLE event(
    date Date,
    src UInt8,
    channel UInt8,
    deviceTypeId UInt8,
    projectId UInt64,
    shows UInt32,
    clicks UInt32,
    spent Float64
) ENGINE = MergeTree(date, (date, src, channel, projectId), 8192);

Raw data looks like:

{ "date":"2016-03-07T10:00:00+0300","src":2,"channel":18,"deviceTypeId ":101, "projectId":2363610,"shows":1232,"clicks":7,"spent":34.72,"location":"Unknown", ...}
...

Files with data loaded with the following command:

cat *.data|sed 's/T[0-9][0-9]:[0-9][0-9]:[0-9][0-9]+0300//'| clickhouse-client --query="INSERT INTO event FORMAT JSONEachRow"

clickhouse-client throw exception: 

Code: 117. DB::Exception: Unknown field found while parsing JSONEachRow format: location: (at row 1)

Is it possible to skip fields from JSON object that not presented in table description?

Answer

Vitaliy L. picture Vitaliy L. · Oct 12, 2016

The latest ClickHouse release (v1.1.54023) supports input_format_skip_unknown_fields user option which eneables skipping of unknown fields for JSONEachRow and TSKV formats.

Try

clickhouse-client -n --query="SET input_format_skip_unknown_fields=1; INSERT INTO event FORMAT JSONEachRow;"

See more details in documentation.

Related questions

Clickhouse Data Import

I created a table in Clickhouse: CREATE TABLE stock ( plant Int32, code Int32, service_level Float32, qty Int32 ) ENGINE = Log there is a data file :~$ head -n 10 /var/rs_mail/IN/qv_stock_20160620035119.csv 2010,646,1.00,13 2010,2486,1.00,19 2010,8178,1.00,10 2010,15707,1.00,4 2010,15708,1.00,10 2010,15718,1.00,4 2010,16951,1.00,8 2010,17615,1.00,13 2010,17616,1.00,4 2010,17617,1.00,8 I am trying to load data: :~$ …

Read More
Updating data in Clickhouse

I went over the documentation for Clickhouse and I did not see the option to UPDATE nor DELETE. It seems to me its an append only system. Is there a possibility to update existing records or is there some workaround …

Read More
How to create primary keys in ClickHouse

I did found few examples in the documentation where primary keys are created by passing parameters to ENGINE section. But I did not found any description about any argument to ENGINE, what it means and how do I create a …

Read More