Неструктурированные данные

Введение в BigQuery

Matt Forrest

Field CTO

Зачем нужны неструктурированные данные

Пример неструктурированных данных:

/*--------------+-----------------------------------------*
 | customer_id  | emails                                  |
 +--------------+-----------------------------------------+
 | 12345        | ['[email protected]', '[email protected]']   |
 *--------------+-----------------------------------------*/

ARRAY

SELECT ARRAY
  (SELECT 'bigquery' UNION ALL
   SELECT 'analytics' UNION ALL
   SELECT 'sql') AS new_array;


/*------------------------------------*
 | new_array                           |
 +------------------------------------+
 | ['bigquery', 'analytics', 'sql']   |
 *-----------------------------------*/

SELECT ARRAY
  (SELECT 'bigquery' UNION ALL
   SELECT 'analytics' UNION ALL
   SELECT 'sql')[1] as result;


/*---------------*
 | result          |
 +---------------+
 | 'analytics'   |
 *---------------*/

STRUCT

SELECT
  STRUCT

  <skill string, learning bool>
  ('big query', true)    
  as skills;


/*--------------+--------------*
 | skills.skill | learning     |
 +--------------+------------- +
 | 'big query'  | true         |
 *--------------+--------------*/

SELECT
  STRUCT
  <skill string, learning bool>

  ('big query', true).skill    
  as key;


/*--------------+
 | key.         |
 +--------------+
 | 'big query'  |
 *--------------*/

ARRAY_LENGTH и ARRAY_CONCAT

SELECT
  ARRAY_LENGTH(
    [
      '[email protected]', 
      '[email protected]'
    ]) as len;

/*------*
 | len  |
 +------+
 | 2    |
 *------*/

SELECT
  ARRAY_CONCAT(
    ['one'], ['two']) as new_array;

/*-------------------*
 | new_array         |
 +-------------------+
 | ['one', 'two']    |
 *-------------------*/

UNNEST

Разворачиваем данные об email-адресах:

SELECT 
    * 
FROM
    UNNEST(['[email protected]', '[email protected]']) as emails;

/*-------------------+
 | emails            |
 +-------------------+
 | '[email protected]' |
 | '[email protected]'  |
 *-------------------*/

UNNEST со STRUCT

Пример данных — STRUCT внутри ARRAY:

[
    {'big query': true},
    {'sql': true}
]

SELECT 
    my_skills.skill, 
    my_skills.learned
FROM 
    UNNEST(skills) as my_skills

/*--------------+--------------*
 | skill        | learned      |
 +--------------+------------- +
 | 'big query'  | true         |
 +--------------+------------- +
 | 'sql'        | true         |
 *--------------+--------------*/

SEARCH

Поиск по данным email-адресов:

SELECT 
    SEARCH(['[email protected]', '[email protected]'], 'gmail.com') as results;

/*----------*
 | results  |
 +----------+
 | true     |
 *----------*/

Шпаргалка по неструктурированным данным

ARRAY

Аналог списка с упорядоченными значениями
Доступ к элементам — по индексу от 0 (например, my_array[0])

STRUCT

Аналог JSON или словаря
Поддерживает произвольную структуру с несколькими типами данных
Структура должна быть одинаковой для всех строк столбца

ARRAY_CONCAT/ARRAY_LENGTH

Объединение двух и более массивов и вычисление длины массива

UNNEST

Разворачивает данные типа ARRAY в плоский формат

SEARCH

Поиск совпадений в ARRAY или STRUCT

Давайте потренируемся!

Введение в BigQuery