BigQuery脱初心者を目指せ！今から覚えるべきテクニック3つ

CASEやJOIN、GROUP BYなども覚えて、SQLは大体大丈夫！となっている方も多いかもしれません。

しかし、BigQueryに触れるなら、もうちょっと覚えておいた方が楽になることが色々あります。

ので、覚えてみましょうと言う話。3つで十分なのかという声もあるでしょうが、ネタが出てこないので、3つで許してください。

TABLE_DATE_RANGE() で、複数テーブルからフェッチ。

SELECT
  id
FROM
TABLE_DATE_RANGE([foo_dataset.], TIMESTAMP('2015-09-10'), TIMESTAMP('2016-06-15'))

ちなみに、 [] 使えば、テーブル名が日付だけでも大丈夫です。それがないと、こんなエラーになるので、気をつけてください。

Encountered " "TIMESTAMP" "TIMESTAMP "" at line 5, column 51. Was expecting: ")" ...

JSON_EXTRACT_SCALAR() を使ってJSONを操ることができるので、やってみましょう。

こんな風にフェッチできます。

SELECT 
  JSON_EXTRACT_SCALAR('{"a": ["x", {"b": "foo"}]}', '$.a[1].b') AS str

結果はこんな感じ。a配列の1番目のキーbの値を取るということですね。

Row  str  
1   foo

ちなみに、JSON_EXTRACT() というのはありますが、その場合は以下のように返ってきます。

Row  str  
1   "foo"

ダブルクォートも一緒に文字列として返してしまうので、よほどのことがない限りはJSON_EXTRACT_SCALAR() のほうがよいでしょう。

もちろん、WHERE句でも使えますので、駆使できるとよいでしょうね。

最後に CONTAINS 。上の項目でJSONをパースする方法をお伝えしましたが、BigQueryには、LIKE以外にも CONTAINS というのがあります。

こんな感じ。

SELECT
*
FROM dataset.table
WHERE sample_json CONTAINS "some_key"

正直、以下と何が違うのかはわからない。

SELECT
*
FROM dataset.table
WHERE sample_json LIKE "%some_key%"

まぁ、絞り込みができます。

ただ、JSONを対象にして、 CONTAINS を使いたい場合、もしキーが存在しているかどうかだけを見たいなら、以下の方がいいかも。

SELECT
*
FROM dataset.table
WHERE JSON_EXTRACT_SCALAR(sample_json, '$.a[1].some_key') IS NOT NULL

こうすることで、確実に some_key があるデータだけを抽出できます。

簡単に書いてみましたが、いかがだったでしょうか。

BigQueryはもう何年も使っているのですが、未だに新たな発見があり、覚えることが非常に多くありますね。

その分便利だとは思いますので、是非みなさんも覚えてみましょう！

これ便利だよとかあれば是非教えて下さい！ではでは！

カイワレの大冒険 Third