非構造化データ

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

メールデータをUNNESTで展開:

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

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

STRUCTをUNNEST

例データ - ARRAY内のSTRUCT:

[
    {'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

メールデータでSEARCHを使用:

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入門

非構造化データ

非構造化データが重要な理由

ARRAY

STRUCT

ARRAY_LENGTH と ARRAY_CONCAT

UNNEST

STRUCTをUNNEST

SEARCH

非構造化データ チートシート

練習しましょう！

非構造化データチートシート