Работаем с JSON в Clickhouse
Достаточно часто мы не можем заранее определить структуру данных из-за того, что они постоянно меняются. Характеристики данных зависят от времени и других важных обстоятельств, они априори не могут быть статичными. В таких случаях весьма полезно работать с форматом JSON, который позволяет сохранять динамически изменяющиеся данные, а также с Clickhouse, предоставляющим ряд удобных и мощных инструментов для обработки информации.
Хранение данных в формате JSON
Самый простой (и на данный момент единственный) способ хранения JSON-объекта – создание колонки типа String и сохранение в ней текстового представления JSON:
CREATE TABLE test_string ( `t` DateTime, `v` String ) ENGINE = MergeTree ORDER BY t
Сохраним данные в формате JSON в столбце v:
INSERT INTO test_string VALUES(now(), '{"name":"Joe","age":95}')
У Clickhouse есть ряд функций для работы с данными JSON, включая проверку самих данных JSON.
Проверка и подтверждение ключей
Проверить правильность JSON файла можно с помощью функции isValidJSON:
SELECT v, isValidJSON(v) FROM test_string
Который возвращает 1 (или 0), если JSON действителен (или нет):
Мы также можем проверить, содержит ли какой-либо определенный ключ (или любой из заданных ключей), что особенно полезно при очистке данных:
SELECT
JSONHas(v, 'name') AND JSONHas(v, 'rating') AS is_valid,
count(*)
FROM test_string GROUP BY is_valid
В данном случае мы хотим, чтобы наше JSON-поле имело определенные ключи name и rating, тогда оно будет отмечено как действительное:
Как мы видим, наша таблица содержит 2 недопустимых значения.
Получение значений
В большинстве случаев мы хотим оперировать значениями JSON-атрибутов (ключей), что можно сделать несколькими способами. Во-первых, существует специальная функция извлечения типизированных значений на основе имен ключей:
SELECT
JSONExtract(v, 'name', 'String'),
JSONExtract(v, 'rating', 'UInt32')
FROM test_string LIMIT 5
Здесь мы извлекаем значение ключа name как строку, а также значение ключа rating как целое число из значения столбца v (в котором хранится JSON):
Мы можем извлекать как скалярные типы (например, String, Int/UInt* или Float*), так и сложные структуры, такие как Array или Tuple (а также их комбинации):
SELECT JSONExtract('{"val": [1,2,3,4]}', 'val', 'Array(UInt8)')[2]
Который вернет второе целочисленное значение из массива val (который является ключом JSON):
Другой вариант извлечения значения (без учета типов) - использование функции JSON_VALUE:
SELECT JSON_VALUE(v, '$.name') FROM test_string LIMIT 5
Которая, опять же, перечислит значение ключа name столбца v JSON:
Использование значений ключей JSON в целях индексирования
Как мы знаем, у Clickhouse есть специальные функции построения ключей сортировки. Поэтому для оптимизации некоторых запросов мы можем использовать функции извлечения JSON в индексах:
CREATE TABLE test_index ( `t` Int64, `v` String ) ENGINE = MergeTree ORDER BY JSONExtractUInt(v, 'rating')
В этом случае мы использовали извлеченное значение ключа рейтинга в качестве ключа сортировки, поэтому соответствующие запросы будут достаточно эффективными:
SELECT count(*) FROM test_index WHERE JSONExtractUInt(v, 'rating') = 140970729
В этом случае Clickhouse справится с запросом очень быстро, поскольку применит индексирование :
И наоборот, если мы не будем использовать фильтр по ключу JSON, который не индексирован, произойдет сканирование всей таблицы. Например, тот же запрос к той же таблице/данным, но с другим индексом сортировки, приводит к следующему:
Просканировано огромное количество строк, использован большущий объем оперативной памяти, потрачена уйма времени… Кошмар.
Ключи или отдельные столбцы?
Если Вы точно знаете, что Ваше JSON-поле будет содержать строго определенные ключи строго определенных типов, перед записью в Clickhouse лучше перенести их из JSON-значения в отдельные столбцы. Это позволит Вам сэкономить место (благодаря сохранению данных с использованием соответствующих типов, а не строк) и повысить производительность в целом (так как накладные расходы на извлечение файлов JSON будут равны 0).
Экспериментальный тип объекта JSON
В Clickhouse есть экспериментальный тип JSON , на который стоит обратить внимание (подчеркиваю, функция пока является экспериментальной!):
SET allow_experimental_object_type = 1
Теперь при создании таблиц мы можем использовать тип JSON:
CREATE TABLE test_json ( `t` DateTime, `v` JSON ) ENGINE = MergeTree ORDER BY t
Первый плюс этого типа - в том, что мы можем использовать объектную нотацию напрямую в запросах:
SELECT v.name, v.rating FROM test_json LIMIT 5
Clickhouse знает, что нужно делать в таком случае:
Второй плюс этого типа заключается в том, что по сравнению с использованием поля String он занимает намного меньше места (в нашем случае это ~30%):
Но есть и один достаточно серьезный минус - это скорость вставки данных. Если мы сравним скорость вставки одних и тех же данных в колонки String и JSON, то увидим разницу почти в 10 (!) раз:
В любом случае, это все еще пока не более чем эксперимент, так что, надеюсь, что в самое ближайшее время команда Clickhouse подробно задокументирует эту функцию и добавит ее в арсенал своего продукта.
Резюме
Работать с JSON в Clickhouse так же просто, как сначала использовать поле String, а затем подключить набор функций JSON* для проверки и извлечения ключевых значений из полей JSON:
SELECT JSONExtract(json_col, 'key', 'String') FROM table
Мы можем использовать функции извлечения в качестве индексов (или части индексов) для повышения производительности запросов. Но не забывайте и о том, что их также можно перенести в отдельные столбцы и оставить только определенный набор ключей для хранения в столбцах JSON.
