Как импортировать JSON в ClickHouse — ИТ консалтинг от Олега Солозобова

Проблема

При создании таблицы с помощью:

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);

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);

Необработанные данные выглядят так:

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

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

Файлы с данными, загружаемые следующей командой:

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"

1	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"

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);

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", ...}
...

{ "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"

1 2	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 выбрасывает исключение:

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

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

Можно ли пропускать поля из объекта JSON, которые не представлены в описании таблицы?

Решение

Версии ClickHouse, начиная с v1.1.54023, поддерживают пользовательский параметр input_format_skip_unknown_fields, который позволяет пропускать неизвестнве поля для форматов JSONEachRow и TSKV.

Попробуйте

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

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

Подробнее см. в документации

Источник