update.こんにちは。LocoPartnersでReluxのサーバーサイドを担当している山口です。2018」21日目の記事です。本日はGoogleのBigQueryでデータを参照する際に私が学んだことについて紹介します。BigQueryにアクセスすると、下記の画面が表示されるのでサイドバーのリソースから検索窓を利用。目視により必要なテーブルを探すことが可能でした。上記の画像をみても解るようにBigQueryはリソースを3つの階層で管理しています。表中のレベル列に記載しているものは正式名称が解らなかったので私が勝手に命名しているものになります。ご存知の方がいらっしゃれば教えてください。events例でいうとeventsがテーブルになります。表示されているのが不思議に感じたので調べてみるとeventsテーブルには分割テーブルという特殊なテーブルを採用しているからでした。分割テーブルについて簡単にはなりますが少しだけ分割テーブルについても調べてみました。このeventsテーブルには毎日膨大な数のデータが蓄積されていくのですが、それを1つのテーブルで扱おうとすると下記のような問題が発生します。一度のSQLで読み取られるバイト数増加によるコストパフォーマンスの上昇たしかに分割するに限りますね。events_20181103など)が生成されています。よばれる2種類のSQLが用意されており、そのどちらかを利用することが出来る。いうことが新たに解りました。Googleのリファレンスを確認後、下記の理由から私は標準SQLを利用することにしました。※この記事内の説明でも標準SQLを利用しています。標準SQLのクエリ構文を使用してください。記載があることレガシーSQLと比較して標準SQLの機能が多いことレガシーSQLと比較して標準ドキュメントが充実していることまた、標準SQLを利用する場合には簡単な設定変更が必要になったのでGoogleのドキュメントを参照して設定を行いました。標準SQLを利用して単一テーブルを検索する利用するSQLの種類も決定したのでBigQueryで単純なSELECT文のSQLを記述してみます。SQLを記述すると即座にSQLのチェックが行われSQLの文法に誤りがないこと、実行した場合に参照されるデータの量が表示されました。参照されるデータ量はそこそこありましたが一度の実行で凄い金額が請求されるレベルではなかったので、そのままSQLを実行し結果を取得することにしました。1000参照されるデータ量を抑えたい場合BigQueryは参照したデータ量に応じても課金が行われます。低コストで便利なものを使いたいものです。簡単にですがどのように参照されるデータ量を抑えることができるかを確認してみたところ、取得列を絞ることで参照されるデータ量を抑えることが可能でした。個人的にはWHERE句を用いてもデータ量を参照できないことが以外でした。events_20181130)のそれぞれに対して、日毎にSQLを実行して情報を取得するのは範囲が増加するほど面倒な作業になっていくのは想像に難くないですよね。BigQueryでは下記のように分割テーブルを纏めて検索することが可能でした。先頭のrは正規表現を用いる場合は必須です。下記のような料理名と料理に必要な具材を保存しているrecipesテーブルがあったとします。葱このデータに対して料理の具材で絹ごし豆腐を利用している料理だけを取得したいと考えたので下記のSQLを記述してみました。いう、エラーメッセージが表示されました。これはingredient自体が文字列でないことが原因で発生しています。UNNEST関数の3つを用いる必要があることが解りました。JOIN句について知ってはいましたが改めて動作を確認しました。下記のようなテーブルが存在していたとします。すべての組み合わせになります。WITH句WITH句は複数回同じ副問合せを記述する場合やSQLの見易さを考慮する際にとても有効です。自分にとって都合の良いデータが集まったテーブルをSQLの実行中に一時的に定義しておくものと自分は考えています。下記は具体例になります。先頭のrは正規表現を用いる場合は必須です。WITHで作成したテーブルに対してSELECTを行える。event_params_201811UNNEST関数UNNEST関数は配列や配列に含まれる構造体を値テーブルにして返す関数です。冒頭で例としたrecipesテーブルを利用して具体的な例を紹介します。葱このテーブルのingredient列は複数の値(配列の構造体)が含まれているので、1行のingredientに複数の値が含まれています。このingredient列を展開して考えると下記のようにkey列とvalue列のテーブルになっていることになります。上記のingredient列を展開して得られるテーブルを、元のrecipesテーブルの各行に紐づけることができれば、配列の中身を条件に行を絞ることが可能になりそうだということが解りました。JOINとUNNEST関数を利用して、上記で検討したことを実現してみました。unnested_ingredient==========結果==========複雑な箇所だと思うので改めてrecipesテーブルとingredient列を展開して得られるテーブル。2つのテーブルを結合した結果をまとめて載せておきます。JOINによって出来上がったテーブルからingredient列を省いたほうが解りやすい人もいるかもしれないので別バージョンも載せておきます。片栗粉これで列中の値が配列であったとしても1つの行として扱う方法を修得できたはずです。利用したレシピのみを抽出してみましょう。後は、いつものようにWHERE句を足すだけです。Developerと呼ばれる職種以外の方達が自身でSQLを記述し、必要な情報の取得と分析を行われているのを社内外を問わず見かけるようになりました。当記事を参考にBigQueryに未だ触れたことがないSoftwareDeveloper以外の方達の助けになれば幸いです。2018をお楽しみください。upLoginユーザーは見つかりませんでした。