エンジニアブログ

Engineer's Blog

絞り込んで表示する

2016.9.16 - 

MAGELLAN BLOCKS利用におけるGCP入門その3 〜MAGELLAN BLOCKS での BigQuery の利用〜

前回は BigQuery の基本的な知識と BigQuery の web UI での操作をご紹介しました。
今回は MAGELLAN BLOCKS で BigQuery を利用する方法をご紹介いたします。
BigQuery のクエリーを単発で実行する場合は、web UI や bq Command-Line Tool を使うと便利です。
しかし、実際の業務で使う場合は、クエリーを多段に実行したりバッチで定期的に実行するといったことが必要になってきます。
そういったことを実現するには BigQuery の API を使用してプログラムを作らなくてはなりません。
MAGELLAN BLOCKS には BigQuery を使うためのブロックが用意されていて、そのブロックをつないでいくだけで、プログラミング不要で簡単に業務バッチが作れます。
それでは、実際に MAGELLAN BLOCKS を使って作ってみましょう。

1. BigQuery のクエリーを定期実行するフローを作る

1-1. ブロックをつなぐ

MAGELLAN BLOCKS のボード編集画面を開いて、左側のブロックリストからブロックを選びドラッグ&ドロップしてブロックをつないでいきます。
今回使用するブロックは以下の3つです。
1) 基本「フローの開始」
2) BigQuery「クエリーの実行」
3) 基本「フローの終了」
※ どのフローも「フローの開始」で始まり「フローの終了」で終わる必要があります。
ボード編集画面(フローの組み立て)

1-2. 定期実行の設定

定期実行するためにスケジュールの設定を行います。
MAGELLAN BLOCKS では crontab 書式で記述できるので、分/時/日/月/曜日と業務に合わせて柔軟に設定できます。
今回は、3分ごとに実行するようにします。
「フローの開始」ブロックのプロパティの「開始時間」に */3 * * * * と記述して、「開始時間を有効にする」にチェックを入れます。
ボード編集画面(プロパティ-定期実行設定)

1-3. クエリーを書く

フローのメインである BigQuery のクエリーを書いていきます。
前回作成したmy_school データセットの scores テーブルを使います。
scores テーブルの中身
このテーブルのデータから、男女別の各教科の平均値を集計してみましょう。
クエリーはこんな感じです。

SELECT
        sex,
        AVG( language_score ) as avg_language_score,
        AVG( math_score ) as avg_math_score,
        AVG( social_score ) as avg_social_score,
        AVG( sciencr_score ) as avg_sciencr_score
FROM
        my_school.scores
GROUP BY
        sex

それでは「クエリーの実行」ブロックのプロパティに記述していきます。
クエリーの結果は my_school データセットの avg_by_sex テーブルに 上書き で入れるようにします。
avg_by_sex テーブルは、クエリーの結果から BigQuery が自動的に作成してくれるので、あらかじめスキーマ作成する必要はありません。
前々回設定した GCP サービスアカウントの選択も忘れないでくださいね。
ボード編集画面(プロパティ-クエリー)
ここで1つポイントがあります。
プロパティの拡張設定をクリックすると、「クエリーの優先度」という設定項目があります。
デフォルトは バッチ になっていますが、説明に書いてあるように BigQuery のリソースに空きがある時に実行されるため遅い時があります。これを インタラクティブ に変更すると速くなります。
ボード編集画面(プロパティ-クエリーの優先度)
設定が終わったら画面右上の「保存」ボタンをクリックしてください。
これでフローが出来上がりました!!

2. フローの実行

定期実行するように設定してあるので、3分おきに自動実行されるはずですね。確認してみましょう。
画面下部の「ログ」をクリックして開いてください。そこに実行ログが出力されます。
ボード編集画面(ログ確認)
ちゃんと実行されていましたか?

3. 結果の確認

クエリーの結果がちゃんとテーブルに出力されいるか BigQuery のコンソールで確認しましょう。
avg_by_sex テーブルの中身
指定した avg_by_sex テーブルが作成され、男女別の各教科の平均値が出力されていますね!!

4. フローの実行停止

終わったら「フローの開始」ブロックのプロパティの「開始時間を有効にする」のチェックを外して、「保存」をクリックしフローの実行停止をしてください。
ボード編集画面(プロパティ-定期実行無効)
いかがでしたか?簡単なシナリオでしたがプログラミングは一切記述せずに BigQuery を使ったバッチが構築できることが分かっていただけたと思います。
他にもいろいろなブロックがありますので、自分で自由にブロックをつなぎあわせてフローを作ってみてくださいね!!