GCP

BigQueryのunnest関数が便利

はじめに

BigQueryの「大容量のデータを高速でスキャンできる」「従量課金の料金形態」という性質から、テーブル設計をはじめるとRDBMSといろいろ違いがでてくる。 今回は配列をカラムに格納する上で便利なunnest（アンネスト）関数を紹介。

前提

サンプルコードを動かす前に、with関数を使ってメモリー上に試験的に使うデータを保持するようにする。

#standardSQL
WITH data AS (
  SELECT "primes under 15" AS description,
  [1,2,3,5,7,11,13] AS primes_array)

この時点で

#standardSQL
WITH data AS (
  SELECT "primes under 15" AS description,
  [1,2,3,5,7,11,13] AS primes_array)
  
SELECT count(*) FROM data

を実施すると 1 が返ってくる。つまり1レコード登録されている状態。

unnest関数で紐解く

#standardSQL
WITH data AS (
  SELECT "primes under 15" AS description,
  [1,2,3,5,7,11,13] AS primes_array)
SELECT description, prime 
FROM data, UNNEST (primes_array) as prime

 

from テーブル名, unnest(内部カラム) ;

という形式でデータを取り出す。 この時点で

#standardSQL
WITH data AS (
  SELECT "primes under 15" AS description,
  [1,2,3,5,7,11,13] AS primes_array)
  
SELECT count(*)
FROM data, UNNEST (primes_array) as prime

を実施すると 7 レコード返ってくる。

Google Developerドキュメント

Firebase blogのドキュメントに具体的な利用方法が書かれてある。 https://developers-jp.googleblog.com/2017/04/bigquery-tip-unnest-function.html    

参考情報

ストーリー仕立てで分かりやすくまとまっているのでおすすめ。