Расширенные запросы BigQuery с использованием JavaScript и JSON

BigQuery имеет множество опций и возможностей, в нашем распоряжении есть несколько встроенных методов. В этом посте я хотел бы показать вам, как мы можем использовать преимущества синтаксического анализа JSON и JavaScript внутри запросов!

Прежде всего, давайте определим некоторые данные, не так ли? Имя таблицы - my_table. Давайте посмотрим на это:

Это действительно простая структура данных, но она напоминает то, что я испытывал в прошлом. Этот журнал был получен из приложения Ruby, и если вы знаете Ruby, вы, вероятно, заметите, что params внутри payload - это Ruby Hash. Не идеально, но бывает.

Запросы с JSON

Теперь наша первая задача - просто показать все журналы удаления. Легко, правда?

SELECT id, payload
FROM my_table
WHERE payload LIKE '%delete%'

И это работает! Ну ... вроде как. Если бы какой-либо параметр имел delete где-нибудь еще, мы бы выбрали неправильные строки. Мы должны смотреть только внутрь клавиши действие. Давай сделаем это:

SELECT id, payload
FROM my_table
WHERE JSON_EXTRACT_SCALAR(payload, '$.action') = 'delete'

Теперь мы говорим! BigQuery имеет встроенный анализатор JSON, который мы можем использовать и злоупотреблять. Допустим, мы хотим вернуть только параметры каждого действия create, например:

SELECT id, JSON_EXTRACT_SCALAR(payload, '$.params') AS params
FROM my_table
WHERE JSON_EXTRACT_SCALAR(payload, '$.action') = 'create'

и он отлично работает.

Использование JavaScript внутри запросов

Теперь перейдем к более сложным вещам. Первый: получить параметры каждого действия create, но в формате JSON вместо хэша Ruby.

Для решения этой проблемы мы должны проанализировать params, а в BigQuery нет предварительно созданных методов для этого. Итак, создадим свою!

Чтобы еще больше усложнить ситуацию, для каждого атрибута внутри params мы хотим вывести каждый ключ и значение вместе с идентификатором строки. Таким образом, результат будет таким:

Мое решение таково:

Вывод

Как показано выше, BigQuery может быть действительно гибким с помощью всего нескольких строк кода. Парсер JSON довольно приятный, а когда вам нужны более сложные вещи, JavaScript очень удобен. Следует ли его использовать везде? Нет. Это связано со снижением производительности.

Но когда у вас есть огромные данные, а время не имеет значения, но вы должны быть уверены, что возвращает только то, что вам абсолютно необходимо, или вам нужно выполнить некоторую логику для анализа некоторых данных, тогда функция JS в вашем запросе может быть хорошей решение.