Import JSON in ClickHouse

Question

I create a table with this expression:

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 is as follows:

{ "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 a JSON object that are not represented in the table description?

Answer 1

The latest version of ClickHouse (v1.1.54023) supports a custom parameter input_format_skip_unknown_fieldsthat allows you to skip unknown fields for the JSONEachRow and TSKV formats.

Try

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

More details in the documentation .

Answer 2

It is currently impossible to skip unknown fields.

, INSERT INSERT SELECT . INSERT, "" , MergeTree.

(- 'format_skip_unknown_fields').