2020年も終わりが見えてきたので、そろそろGoogleが誇る最強のデータウェアハウス、BigQueryを触ってみませんか？
GCP、AWS、Azureを始めとしたパブリッククラウドを始め、様々なクラウドサービスが行き交うクラウド戦国時代の今日、
政府もクラウド型システムの導入を促進すべく動き出しているとかいないとか。
www.itmedia.co.jp
僕が仕事でメインで利用しているパブリッククラウドサービスは、Google Cloud Platform、GCPです。
このGCPが他のクラウドと比べて特に強みとしているのが、BigQueryです。
今回は、BigQueryを無料で触ってみようという回でございます！
なんていう、手元にデータがないから触れないという方ご安心を。Googleアカウントだけご準備ください。
BigQueryので参照できる一般公開データセット、NOAAの「Global Surface Summary of the Day Weather Data - GSOD」のデータを使って、
BigQuery上でSQL実行をしてみようと思います。
BigQueryって何？
BigQueryとは、Google Cloudが提供する、ペタバイト単位のデータに対するスケーラブルな分析を可能にする、フルマネージドのサーバーレスデータウェアハウスです。
ペタバイト規模と言われても、そんなデータに日頃触れることのない身としては想像も付きませんが、特徴としては、
- とにかく大量のデータを保存できる
- その大量データに対してクエリを実行し、高速で結果を取得できる
- クエリの実行結果をテーブルやファイルとして簡単に保存できる
- SQLの実行以外にも、BIツールなどと組み合わせて可視化できる
などが挙げられます。
詳しい説明は公式ドキュメントを参照しましょう。
他にも、いろいろな企業が導入事例を紹介しているので調べてみると良いと思います。
百聞は一見に如かず、早速触ってみましょう！
BigQueryサンドボックスを利用する
BigQuery サンドボックスという、GCPにクレジットカード情報を登録しなくても、制限付きで試用できる環境があります。
まずは以下の公式ドキュメントの手順に従って、BigQueryサンドボックスの利用を開始しましょう。
cloud.google.com
公開データセットにアクセスできるようにする
BigQueryの画面が開けたらまず、画面の左下の「リソース」の「＋データを追加」から、「一般公開データセットを調べる」をクリックします。
すると、一般公開データセットの一覧が出てきます。ここで「noaa_gsod」と検索し、検索結果に１件出てくるので、アイコンをクリックします。
GSODの概要やサンプルが見られます。ここで「データセットを表示」をクリックします。
すると、なんということでしょう。画面の左下に着目してください。こちらがデータセットの一覧です。さっきまでなにもデータのなかったBigQueryの画面で、大量の一般公開データセットが参照できるようになりました。GSOD以外のデータセットも表示されているので、データセットを検索するくだりは一体何だったのでしょうか？
データの見方
左下のリソースツリーは、
プロジェクト > データセット > テーブル
といった階層で表示されています。今回使うデータセットは、noaa_gsodという名前です。
このデータセットの中には、以下のテーブルがあります。
- gsodYYYY (1929～１年毎に１テーブル、気象情報のデータ)
- stations (観測地点のマスタデータ)
テーブル名をクリックすると、テーブルの項目定義や、データのプレビューが参照できます。
これらを見ながらSQLを書いていきましょう。
SQLを実行する
それでは、いよいよSQLを実行していきます。
SQLもこのWebUI上で実行できます。クエリエディタが表示されていなかったら、画面上部の「クエリを新規作成」をクリックして表示させてください。
BigQueryはMySQLやPostgreSQLといった一般的なデータベースと似た、標準SQLが利用できます。
試しに以下のSQLを実行してみてください。
SELECT count(*) FROM `bigquery-public-data.noaa_gsod.gsod2020`
gsodの2020年のテーブルのレコード数が画面に表示されます。
BigQueryのテーブルの指定方法ですが、「`プロジェクトID.データセットID.テーブルID`」
と指定します。プロジェクトIDは、自分のプロジェクト配下にあるテーブルを指定する場合は省略可能です。
また、以下のように、テーブル名をワイルドカードで複数指定できたりもします。
SELECT count(*) FROM `bigquery-public-data.noaa_gsod.gsod*`
このSQLを実行すると、1929年～最新のテーブルまでの全レコード数が返ってきます。執筆時点で約1.5億件、ビッグデータですね。
count(*)だけではつまらないので、実際の項目をSELECTしてみます。以下のSQLを実行してみてください。
SELECT name, lat, lon, FROM `bigquery-public-data.noaa_gsod.stations` WHERE country = 'JA' ORDER BY lat DESC
日本の観測地点の一覧が出てきます。約400件、意外と多いですね。緯度の北からソートしているしているので、上位に北海道の地名（ローマ字）が出てくると思います。僕は北海道に住んだことないので、地名を見てもさっぱりですが…。
こんな感じでSQLを実行して、結果を取得していくわけですね。
更にWebUI上で、結果をクリップボードにコピーしたり、ファイル出力したり、Googleスプレッドシートに保存したりなどができます。
余談ですが、BigQueryのSELECT句、一番最後の項目の末尾にカンマがあっても、MySQLのように構文エラーになることがありません。
Pythonのlistなども末尾にカンマがつけられる、明示的に全行にカンマをつけておくことであとから行の追加が楽になるところから便利すぎて推しポイントです。
クエリ実行による課金の仕組みを知る
先程のSQLを入力した際に表示される、エディタの右下の「このクエリを実行すると 〇〇B が処理されます。」というメッセージに注目してください。
BigQueryは従量課金制のサービス、課金対象の１つに、このクエリデータ処理容量があります。ここが多ければ多いほど、お金がかかるってことですね。
このクエリデータ処理容量（=コスト）は、読み込んだデータ数、つまりSELECTするテーブルのレコード数が増えれば増えるほど多くなります。
また、BigQueryは列指向のデータべース、SELECTした列を増やせばコストは増え、列を減らすとその列は参照されなくなるのでコストは減ります。
BigQueryサンドボックス、GCP無料枠、課金プロジェクトいずれも、毎月1TBのクエリデータ処理は無料枠として割り当てられています。
また、データ処理容量は最低10MBです。9MBを1MBに減らす努力は無駄ということですね。
SQLでデータを整形する
BigQueryのSQLは一般的なRDBに対して実行するSQLと違って、負荷を気にせず重いSQLが実行できます。もちろんコストには気をつけなければなりませんが。例えば、以下のSQLを実行してみましょう。2000年以降の日本の気象情報をがさっと取ってくる、5GBくらいのコストのSQLです。
このGSODのデータ、不明なデータが999.9のような値になっていたり、型がバラバラだったりと、分析で使う上である程度の前処理が必要になりそうです。データの前処理をPythonなどのプログラム上で実施しても良いのですが、せっかくBigQueryを使っているのですがら、その恩恵に与りましょう。
SELECT PARSE_DATE('%F', CONCAT(gs.year, '-', gs.mo, '-', gs.da)) AS date, -- 観測日 CASE WHEN gs.temp = 9999.9 THEN NULL ELSE TRUNC((5 / 9) * (gs.temp - 32),1) END AS celsius, -- 平均気温(摂氏) CASE WHEN gs.stp = 9999.9 THEN NULL ELSE gs.stp END AS station_pressure, -- 平均気圧（ミリバールから10分の1） CASE WHEN gs.wdsp = '999.9' THEN NULL ELSE CAST(gs.wdsp AS FLOAT64) END AS wind_speed, -- 平均風速 CASE WHEN gs.prcp = 99.99 THEN NULL ELSE gs.prcp END AS precipitation, -- 雨量 CASE WHEN gs.sndp = 999.9 THEN NULL ELSE gs.sndp END AS snow_depth, -- インチから10分の1単位の雪の深さ gs.fog, -- 霧 gs.rain_drizzle, -- 霧雨 gs.snow_ice_pellets, -- 凍雨(みぞれ、あられ) gs.hail, -- 雹(ひょう) gs.thunder, -- 雷 gs.tornado_funnel_cloud, -- 竜巻 st.name, -- 都市名 st.lat, -- 緯度 st.lon, -- 経度 FROM -- 2000年以降の気象情報 `bigquery-public-data.noaa_gsod.gsod2*` gs -- 観測地域マスタ(日本のみ) INNER JOIN `bigquery-public-data.noaa_gsod.stations` st ON gs.stn = st.usaf AND st.country = 'JA' AND st.usaf != '999999' ORDER BY 1, st.lat DESC
いかがでしょうか？この程度のSQLでも、10秒で結果が返ってきます。もっともっと複雑なSQLも実行できそうですね。
現実的には複雑なSQLを一度に書かず、中間テーブルとして保存したり、Viewにしたりして使うことが多いです。
まとめ
以上、今回はBigQueryのサンドボックス環境を利用し、一般公開データセットでSQLを実行してデータを参照するところまでを紹介しました。
これがBigQueryのパワーです。
最後のSQLで参照した2000年以降の気象情報のデータ量は 約7000万件ですが、実際の業務で利用するビッグデータ、それこそECサイトの購買履歴や、Webサイトのアクセスログなどは数億以上のデータが蓄積されていることと思います。そういったビッグデータをBigQueryに蓄積し、WebUIでクエリを実行したり、APIを使ってプログラムから呼び出したり、分析用にデータの前処理をしたり、他のBIツールと組み合わせて可視化することで、新しい発見が生まれるはずです。
BigQueryの一般公開データセットには、今回紹介したGSODのデータセット以外にも、たくさんのデータがあるので、ぜひじゃんじゃんクエリを実行して試してみてください。
