こんにちは、中途で入社してからちょうど丸 1 年がたちました田邊(@emaggame)です。最近は React + TypeScript を利用したフロントエンドや Scala による Web API 開発もそこそこに、Akka Stream によるデータ収集や BigQuery での集計といったこともしており、毎日たのしく過ごしています。
BigQuery、最近さわり始めていろいろできるんだなあと今さらながら感動しているところです。本エントリではちょっとした Tips として、複数の配列を含むレコードをフラット化する方法についてご紹介します。
背景とやりたいこと
お題として、ここでは架空のブログサービスを利用しているとしましょう。サービス側からは以下のような JSON 形式のレポートが得られる API が提供されています。
{ "id": 1, "start_date": "2018-11-01", "end_date": "2018-11-03", "views": [10, 20, 30], "likes": [ 2, 4, 6], "shares": [ 1, 2, 3] }
上記の場合、id はブログ記事に一意にふられる id とし、start_date である 2018-11-01 から end_date の 2018-11-03 を含む 3 日分のレポートを表しています。views/likes/shares はその記事に対するメトリクスです(閲覧数/いいね! された数/SNS へのシェア数)。
views/likes/shares の値は配列となっており、各配列のインデックスがそれぞれの日付と対応しています。つまり、日毎には以下のようなデータを表します。
| date | views | likes | shares |
|---|---|---|---|
| 2018-11-01 | 10 | 2 | 1 |
| 2018-11-02 | 20 | 4 | 2 |
| 2018-11-03 | 30 | 6 | 3 |
この JSON を素直に BigQuery にロードすると以下のような 1 レコードになります。
SELECT id, start_date, end_date, views, likes, shares FROM `array_flatten.test`; +----+------------+------------+------------------+---------------+---------------+ | id | start_date | end_date | views | likes | shares | +----+------------+------------+------------------+---------------+---------------+ | 1 | 2018-11-01 | 2018-11-03 | ["10","20","30"] | ["2","4","6"] | ["1","2","3"] | +----+------------+------------+------------------+---------------+---------------+
これを日毎のレコードとするのが目標です。
最終的なクエリ
先に最終的なクエリを記載します。
以降に記載するクエリは Standard SQL とします
SELECT id, date, COALESCE(views[SAFE_OFFSET(offset)], 0) AS views, COALESCE(likes[SAFE_OFFSET(offset)], 0) AS likes, COALESCE(shares[SAFE_OFFSET(offset)], 0) AS shares FROM `array_flatten.test`, UNNEST(GENERATE_DATE_ARRAY(start_date, end_date, INTERVAL 1 DAY)) AS date WITH OFFSET AS offset ;
結果は以下のとおり、日毎のレコードになります。
+----+------------+-------+-------+--------+ | id | date | views | likes | shares | +----+------------+-------+-------+--------+ | 1 | 2018-11-01 | 10 | 2 | 1 | | 1 | 2018-11-02 | 20 | 4 | 2 | | 1 | 2018-11-03 | 30 | 6 | 3 | +----+------------+-------+-------+--------+
それでは少しずつ解説していきます。
BigQuery における配列型とその UNNEST
個別の解説に入る前に、BigQuery における配列型について簡単に確認しておきます。
https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#array-type
例えば以下のような 15 未満の素数を配列として表す場合、以下のように表現できます。
WITH data AS ( SELECT "primes under 15" AS description, [2, 3, 5, 7, 11, 13] AS primes_array ) SELECT * FROM data;
結果は以下のとおり、primes_array カラムに配列としてデータが格納されています。
+-----------------+-----------------------------+ | description | primes_array | +-----------------+-----------------------------+ | primes under 15 | ["2","3","5","7","11","13"] | +-----------------+-----------------------------+
ここで、配列をフラットにするのに利用できるのが UNNEST 演算子です。
https://cloud.google.com/bigquery/docs/reference/standard-sql/arrays#flattening-arrays
STRUCT に対しても UNNEST できるので、例えばロード元がネストした JSON オブジェクトといった場合にもフラットにできます
https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#unnest
先の例で UNNEST してみます。
WITH data AS ( SELECT "primes under 15" AS description, [2, 3, 5, 7, 11, 13] AS primes_array ) SELECT description, prime FROM data, UNNEST(primes_array) AS prime;
以下のように配列の要素ごとにフラット化されました。
+-----------------+-------+ | description | prime | +-----------------+-------+ | primes under 15 | 2 | | primes under 15 | 3 | | primes under 15 | 5 | | primes under 15 | 7 | | primes under 15 | 11 | | primes under 15 | 13 | +-----------------+-------+
なかなか強力ですね! 以下の記事で配列と UNNEST についてわかりやすく解説されていますので、もう少し詳しく見てみたい場合はご参考ください。この素数の例もこの記事から引用させていただきました。
https://developers-jp.googleblog.com/2017/04/bigquery-tip-unnest-function.html
さっそく UNNEST
では例に戻りまして UNNEST してみましょう。
日毎にする下準備として、start_date と end_date から日付の範囲を表す配列を GENERATE_DATE_ARRAY 関数を用いて生成します。
WITH test AS ( SELECT 1 AS id, DATE('2018-11-01') AS start_date, DATE('2018-11-03') AS end_date, [10, 20, 30] AS views, [ 2, 4, 6] AS likes, [ 1, 2, 3] AS shares ) SELECT id, GENERATE_DATE_ARRAY(start_date, end_date, INTERVAL 1 DAY) AS date, views, likes, shares FROM test;
実際には JSON をロードしたテーブルに対してクエリを実施しますが、簡単のために想定データを WITH 句で作成しています。以降はこの WITH 句の記載は省略します
結果は以下となります。
+----+------------------------------------------+------------------+---------------+---------------+ | id | date | views | likes | shares | +----+------------------------------------------+------------------+---------------+---------------+ | 1 | ["2018-11-01","2018-11-02","2018-11-03"] | ["10","20","30"] | ["2","4","6"] | ["1","2","3"] | +----+------------------------------------------+------------------+---------------+---------------+
ではこの生成した配列を UNNEST 対象としてみます。
SELECT id, date, views, likes, shares FROM test, UNNEST(GENERATE_DATE_ARRAY(start_date, end_date, INTERVAL 1 DAY)) AS date ;
日毎にレコードができるようになりました。
+----+------------+------------------+---------------+---------------+ | id | date | views | likes | shares | +----+------------+------------------+---------------+---------------+ | 1 | 2018-11-01 | ["10","20","30"] | ["2","4","6"] | ["1","2","3"] | | 1 | 2018-11-02 | ["10","20","30"] | ["2","4","6"] | ["1","2","3"] | | 1 | 2018-11-03 | ["10","20","30"] | ["2","4","6"] | ["1","2","3"] | +----+------------+------------------+---------------+---------------+
が、メトリクスについては同じ内容が表示されてしまいますね。次に、他の配列であるカラムのインデックスを読むようにしましょう。
WITH OFFSET を使って他の配列カラムも
配列を UNNEST する際、WITH OFFSET をつけることで、要素ごとのインデックスを取得することができます。
https://cloud.google.com/bigquery/docs/reference/standard-sql/arrays#flattening-arrays
やってみましょう。2 カラム目に出すようにしてみます。
SELECT id, offset, date, views, likes, shares FROM test, UNNEST(GENERATE_DATE_ARRAY(start_date, end_date, INTERVAL 1 DAY)) AS date WITH OFFSET AS offset ;
取れました。0 始まりですね。
+----+--------+------------+------------------+---------------+---------------+ | id | offset | date | views | likes | shares | +----+--------+------------+------------------+---------------+---------------+ | 1 | 0 | 2018-11-01 | ["10","20","30"] | ["2","4","6"] | ["1","2","3"] | | 1 | 1 | 2018-11-02 | ["10","20","30"] | ["2","4","6"] | ["1","2","3"] | | 1 | 2 | 2018-11-03 | ["10","20","30"] | ["2","4","6"] | ["1","2","3"] | +----+--------+------------+------------------+---------------+---------------+
あとは views/likes/shares のそれぞれに対してオフセット値の要素だけ読むようにすればよいということになります。
オフセットを用いて配列の要素にアクセスする場合、配列データ[OFFSET(0 始まりの値)] の形式でアクセスします。
SELECT id, date, views[OFFSET(offset)] AS views, likes[OFFSET(offset)] AS likes, shares[OFFSET(offset)] AS shares FROM test, UNNEST(GENERATE_DATE_ARRAY(start_date, end_date, INTERVAL 1 DAY)) AS date WITH OFFSET AS offset ;
できました!
+----+------------+-------+-------+--------+ | id | date | views | likes | shares | +----+------------+-------+-------+--------+ | 1 | 2018-11-01 | 10 | 2 | 1 | | 1 | 2018-11-02 | 20 | 4 | 2 | | 1 | 2018-11-03 | 30 | 6 | 3 | +----+------------+-------+-------+--------+
null な配列には SAFE_OFFSET で
これまでのものでだいたい良さそうなのですが、1 つ要件を追加します。何らかの条件において views/likes/shares の値に関しては null となることがありえるとします。例えば以下のようになる可能性がある、ということですね(shares が null)。
{ "id": 1, "start_date": "2018-11-01", "end_date": "2018-11-03", "views": [10, 20, 30], "likes": [ 2, 4, 6], "shares": null }
WITH 句では以下のようなデータとなるとしましょう。
WITH test AS ( SELECT 1 AS id, DATE('2018-11-01') AS start_date, DATE('2018-11-03') AS end_date, [10, 20, 30] AS views, [ 2, 4, 6] AS likes, [] AS shares )
このまま先のクエリを実行すると、null である shares フィールドにアクセスした時点で Array index 0 is out of bounds (overflow) となってしまいます。
このような場合に対しては SAFE_OFFSET が利用可能です。範囲外のインデックスへアクセスした際、null が返るようになります。試してみましょう。
SELECT id, date, views[OFFSET(offset)] AS views, likes[OFFSET(offset)] AS likes, shares[SAFE_OFFSET(offset)] AS shares FROM test, UNNEST(GENERATE_DATE_ARRAY(start_date, end_date, INTERVAL 1 DAY)) AS date WITH OFFSET AS offset ;
null、ですね。
+----+------------+-------+-------+--------+ | id | date | views | likes | shares | +----+------------+-------+-------+--------+ | 1 | 2018-11-01 | 10 | 2 | NULL | | 1 | 2018-11-02 | 20 | 4 | NULL | | 1 | 2018-11-03 | 30 | 6 | NULL | +----+------------+-------+-------+--------+
null のときは 0 とみなしてよいということであれば、COALESCE で囲んでしまいましょう。views および likes も同条件としておきます。
SELECT id, date, COALESCE(views[SAFE_OFFSET(offset)], 0) AS views, COALESCE(likes[SAFE_OFFSET(offset)], 0) AS likes, COALESCE(shares[SAFE_OFFSET(offset)], 0) AS shares FROM test, UNNEST(GENERATE_DATE_ARRAY(start_date, end_date, INTERVAL 1 DAY)) AS date WITH OFFSET AS offset ;
これで最初にお出ししたクエリになりました。結果もよさそうですね。
+----+------------+-------+-------+--------+ | id | date | views | likes | shares | +----+------------+-------+-------+--------+ | 1 | 2018-11-01 | 10 | 2 | 0 | | 1 | 2018-11-02 | 20 | 4 | 0 | | 1 | 2018-11-03 | 30 | 6 | 0 | +----+------------+-------+-------+--------+
まとめ
- BigQuery にはデータ型として配列があり、UNNEST することでフラットにできます
- レコード中に配列型のフィールドが複数あった場合、1 つのフィールドを代表選手(今回は date)にしたうえで他の配列の同じインデックスの要素にアクセスすることもできます
- 配列に対して範囲外のインデックスにアクセスをする場合に備えて SAFE_OFFSET もあります
複数の配列を zip できる、と言ってもいいかもしれませんね。
最後に
実は業務で今回の例と似たような JSON を返す API を利用しており、まさに日毎で集計するためにフラット化する必要に駆られたのが本エントリを書くきっかけでした。いろいろと対応方法は検討し、当初は JSON を収集する側で加工しようかな~と考えていました。Akka Stream で処理しているので、原本の JSON と加工した JSON をそれぞれ別の Sink とすることも簡単ですし。
ただ、今回の場合は BigQuery で実施することでよりシンプルに実現できたと考えています1。
BigQuery の登場は 2010 年と歴史を積み重ねているだけあり、機能も豊富で少し込み入ったこともできる点に魅力を感じている次第です。今後のアップデートにも目を光らせていこうと思ってます!
- 原本テーブルからの読み込み量が大量になった際の料金が気になるところではありますが。。↩
