+7 (499) 350 90 93

Использование JSON в Google BigQuery

Прежде чем мы перейдем к разбору JSON в бигквери, давайте разберемся в базовых понятиях JSON.
JSON (англ. JavaScript Object Notation) — текстовый формат данных, базирующийся на JS. Прост в освоении, обособлен от Java Script и может применяться с любым языком программирования, в том числе в запросах BigQuery SQL.

Формат текста (в закодированном виде) имеет одну из двух структур:

Набор пар "ключ : значение". Ключом может быть только строка, как правило регистр учитывается. При повторяющихся именах ключей, возможны варианты:
- учитывать первый такой ключ
- учитывать только последний такой ключ
- выдать ошибку

Упорядоченный набор значений - обычно выглядит как массив. Массив — это просто набор значений, разделенных запятыми. Находится внутри квадратных скобок []. Пример: [ "значение_1" , "значение_2" ]

В качестве значений в JSON могут быть использованы:

запись или JSON объект — это неупорядоченное количество пар ключ : значение, заключенное в фигурные скобки «{ }». Ключ описывается строкой, между ним и значением стоит двоеточие «:». Пары ключ-значение отделяются друг от друга запятыми. Пример: { "text": "row", "number" : 1}
массив (одномерный) — это упорядоченное множество значений. Массив заключается в квадратные скобки «[ ]». Значения разделяются запятыми. Массив может быть пустым, то есть не содержать ни одного значения. Значения в пределах одного массива могут иметь разный тип.
число - целое или с плавающей точкой.
литералы - true (логическое значение «истина»), false (логическое значение «ложь») и null.
строка — это упорядоченное множество из нуля или более символов текста или юникода, заключенное в двойные кавычки.

Запись (JSON-объект)

JSON-объект — это неупорядоченное множество пар «ключ:значение».

Пары ключ:значение разделены запятыми

Строки (текст) берем в кавычки, числа записываем без кавычек. Ключ - это всегда строка, кавычки обязательны.
Переносить на новую строку не обязательно, система и так поймет, перенос нужен для человека, для лучшей понимаемости.

Массивы

Это упорядоченное множество значений или отсутствующие значения в квадратных скобках. Массив обязательно заключается в квадратные скобки «[ ]»

Внутри скобок перечисляются значения. Обращаться к массиву можно только по номеру элемента стоящего по порядку. То есть поменять местами значения в массиве нельзя.

Массив может содержать разные типы данных, текст, цифры, текст и цифры, объекты.

Вот как обычно выглядит структура JSON . Тут видно, что в объекте располагаются обычные строковые значения через ключ значение, а также через запятую новые объекты с значениями, также массивы.

{
   "Имя": "Валентин",
   "Фамилия": "Сикорски",
   "Адрес": {
       "Улица": "Проспект Мира 12, кв.54",
       "Город": "Ленинград",
       "Почтовый код": 143321
   	},
   "Номер телефона": [
       "8 800 975 4142", "+7 455 011 5575"
   	],
   "Автомобиль": {
       "Марка" : "Ауди",
       "Модель" : "RS 7"
   	}
}

Работа с JSON в Google BigQuery

Создание таблицы с помощью запроса
Загрузка JSON файла в BigQuery

1. Создание таблицы в BigQuery с помощью SQL запрос
Чтобы создать таблицу в которой один или несколько столбцов будет с типом json нужно просто указать тип поля ➜ JSON, так же как и когда мы указываем STRING, INT64 и т.д.

CREATE OR REPLACE TABLE business-metric.project.table2
(id INT64, clients JSON)

Чтобы заполнить данными таблицу в google bigquery используем INSERT INTO. Пример записи "ключ : значение" ниже:

INSERT INTO business-metric.project.table2 VALUES
(1, JSON """{
        "name": "Zik",
        "street": "Проспект Мира 23, кв. 112", 
        "city": "Kyiv",
        "post code" : 314564,
        "phone_number" : "8 800 942 2121",
        "items": [
          {"product": "Компьютер", "price": 44999},
          {"product": "Смартфон", "price": 19999}
       		 ]
    }"""),
(2, JSON """{
        "name": "Marya",
        "street": "Московское ш. 13, кв. 21", 
        "city": "Moscow",
        "post code" : 101000,
        "phone_number" : "+7 (500) 554 2154",
        "items": [
            {"product": "Смартфон", "price": 12359}
        		]
    }"""),
(3, JSON """{
        "name": "Dmitriy",
        "street":"Коммунистов 3б, кв. 3",
          "city": "Moscow",
          "post code" : 101000,
        "phone_number" : "8 (800) 322 7311",
        "items": [
            {"product": "Телевизор", "price": 22999},
            {"product": "Холодильник", "price": 13999},
            {"product": "Чайник", "price": 3599}
       		 ]
    }""")

Результат:

Как видим, запись в запросе немного отличается от стандартной.

2. Загрузка файла JSON в BigQuery

Формат файла который мы будем загружать посредством интерфейса имеет нестандартный формат.
Загрузить файл JSON с таким форматом не получится:

[
    {
        "id": "1",
        "first_name": "Annya",
        "last_name": "Krid",
        "dob": "1995-02-10",
        "addresses": [
            {
                "status": "current",
                "address": "123 First Avenue",
                "city": "Los Angeles",
                "zip": "11111"
            },
            {
                "status": "previous",
                "address": "456 Main Street",
                "city": "New York",
                "zip": "22222"
            }
        ]
    },
    {
        "id": "2",
        "first_name": "Will",
        "last_name": "Torw",
        "dob": "1993-11-22",
        "addresses": [
            {
                "status": "current",
                "address": "789 Any Avenue",
                "city": "New York",
                "zip": "33333"
            },
            {
                "status": "previous",
                "address": "321 Main Street",
                "city": "Chicago",
                "zip": "44444"
            }
        ]
    }
]

Хитрость заключается в том, чтобы использовать разделители новой строки (ndjson) вместо стандартного json. В BigQuery он называется - JSONL (Newline delimited JSON)

NDJSON — это удобный формат для хранения потоковой передачи структурированных данных, которые могут обрабатываться по одной записи за раз.

Каждая строка является допустимым значением JSON.
Разделитель строк - '\n'

То есть чтобы добавить файл с данными как выше, нужно его преобразовать:

{"id":"1","first_name":"Annya","last_name":"Krid","dob":"1995-02-10","addresses":[{"status":"current","address":"123 First Avenue","city":"Los Angeles","zip":"11111"},{"status":"previous","address":"456 Main Street","city":"New York","zip":"22222"}]}
{"id":"2","first_name":"Will","last_name":"Torw","dob":"1993-11-22","addresses":[{"status":"current","address":"789 Any Avenue","city":"New York","zip":"33333"},{"status":"previous","address":"321 Main Street","city":"Chicago","zip":"44444"}]}

Каждую группу мы разделяем "новой строкой", есть два человека (два id), для каждого запись идет без прерываний. Следующий идет с новой строки.

{"id":"1","first_name":"Annya","last_name":"Krid", ...
{"id":"2","first_name":"Will","last_name":"Torw", ...

Чтобы создать файл в формате json достаточно воспользоваться блокнотом.

Вводим название файла и в конце после точки вводим json. Все, файл с расширение json создан и подходит для загрузки его в BigQuery.

Порядок действия при загрузке файла формата JSON в Big Query - стандартный:

Кликаем на три точки справа нужного датасета

Создать таблицу

Откроется окно с настройками

1 - выбираем "загрузить"
2 - выбираем наш файл на компьютере (файл должен иметь тип - json)
3 - формат файла выбирается автоматически (если файл правильный)
4 - пишем название нашей будущей таблицы
5 - галочка "авто определение" названия столбцов и их типа (string, int и т.д.)
6 - клик "создать таблицу"
Таблица готова.

Как видим поле addresses имеет тип RECORD. У нас в блоге есть статья как работать с таким типом данных.

Чтобы избавиться от пустых ячеек (при большом количестве данных может вводить в заблуждение) используем функцию UNNEST

select id, first_name, last_name, dob, address
from `business-metric.project.table3`,
UNNEST (addresses) as address

Работа с данными типа json в Google BigQuery загруженными вручную (запросом)

Чтобы получить значение какого-либо ключа(параметра) достаточно просто указать название столбца и через "точку" ключ:

select id, clients.name from `business-metric.project.table2`

Чтобы достать значение ключа (параметра) с нестандартным названием можно записать так:

SELECT
  id,clients.name,
  (clients['post code']) as post_code
FROM
  `business-metric.project.table2`

Но лучше, чтобы избежать проблем в дальнейшем называть сразу правильно, например, post_code

Если мы хотим получить первый номер из массива, например, первый товар из списка, то мы в квадратных скобках пишем 0

SELECT
  id,clients.name,
  clients.items[0] as first_product
FROM
  `business-metric.project.table2`

Перечисление в массиве начинается с нуля, то есть первый элемент из списка имеет номер ноль, второй по списку имеет номер 1 и так далее, смещено как бы.

Мы можем просто перечислить ключи (параметры) через запятую, это облегчит понимание, но все равно останутся внутренние массивы которые не будут разбиты на столбцы или если где-то есть ключ, а где-то нету это опять приведет либо к ошибке, либо к путанице.

SELECT
  id,
  clients.name,
  clients.street,
  clients.phone_number,
  clients.items
FROM
  `business-metric.project.table2`

То есть мы не можем выбрать например "product", в ячейке будет null

SELECT
  id,
  clients.name,
  clients.items.product
FROM
  `business-metric.project.table2`

Чтобы решать задачи подобного типа будем использовать встроенные функции BigQuery для работы с JSON объектами.
Подробнее о всех функция в справке cloud.google.com. А мы разберем основные функции:
JSON_QUERY - возвращает значение ключа или массив или объект. Тоже самое что и без него.
JSON_VALUE - возвращает значение ключа без кавычек (скалярное значение). Тип поля STRING

SELECT
  id,
  JSON_VALUE (clients.name) as name
FROM
  `business-metric.project.table2`

JSON_EXTRACT - извлекает значение JSON, например массив или объект, или скалярное значение JSON, например строку, число или логическое значение. Если ключ JSON использует недопустимые символы, вы можете экранировать эти символы, используя одинарные кавычки и квадратные скобки.
JSON_EXTRACT_ARRAY - извлекает массив значений JSON, таких как массивы или объекты, и скалярные значения JSON, такие как строки, числа и логические значения.
JSON_EXTRACT_SCALAR - извлекает скалярное значение, а затем возвращает его в виде строки. Скалярное значение может представлять собой строку, число или логическое значение. Удаляет крайние кавычки и не экранирует возвращаемые значения.

JSON_VALUE_ARRAY - извлекает массив значений (скалярных значений) без кавычек. Имеет тип поля STRING
JSON_QUERY_ARRAY - извлекает массив значений

SELECT
  id,
  JSON_QUERY_ARRAY(clients.items) as items
FROM
  `business-metric.project.table2`

Как видим мы получили данные только из списка (массива) продуктов. Но отличие от простого ввода названия ключей - массив разбивается на строки.

Теперь у нас достаточно знаний, чтобы извлечь из массива название продукта "product".
Чтобы разбить массив на отдельные элементы, будем использовать оператор UNNEST, который возвращает таблицу с одной строкой для каждого элемента массива.

SELECT
  id,
  JSON_VALUE (clients.name) as name,
  JSON_VALUE (item.product) as items
FROM
  `business-metric.project.table2`, UNNEST (JSON_QUERY_ARRAY(clients.items)) AS item

Мы смогли получить имя пользователя и извлечь из массива items название купленного продукта конкретного пользователя.

Теперь мы сможем написать запрос в котором выведем всю информацию из файла формата JSON
Финальный запрос выглядит так:

SELECT
  id,
  JSON_VALUE (clients.name) as name,
  JSON_VALUE (clients.city) as city,
  JSON_VALUE (clients['post code']) as post_code,
  JSON_VALUE (clients.street) as street,
  JSON_VALUE (clients.phone_number) as phone_number,
  JSON_VALUE (item.product) as product,
  JSON_VALUE (item.price) as price
FROM
  `business-metric.project.table2`, 
  UNNEST (JSON_QUERY_ARRAY(clients.items)) AS item

В итоге у нас получилась понятная таблица, в которой мы можем посчитать, например, сумму покупок по конкретному пользователю.

SELECT
  id,name,SUM(price) AS sum_price
FROM (
  SELECT
    id,
    JSON_VALUE (clients.name) AS name,
    JSON_VALUE (clients.city) AS city,
    JSON_VALUE (clients['post code']) AS post_code,
    JSON_VALUE (clients.street) AS street,
    JSON_VALUE (clients.phone_number) AS phone_number,
    JSON_VALUE (item.product) AS product,
    CAST(JSON_VALUE (item.price) AS INT64) AS price
  FROM
    `business-metric.project.table2`,
    UNNEST (JSON_QUERY_ARRAY(clients.items)) AS item )
GROUP BY name,id