この記事は GiXo アドベントカレンダー の 13 日目の記事です。
昨日は、pandas でヘッダーが複数ある POS データを縦持ち横持ち変換する（後編）でした。
Technology Div. の柳です。ロケットリーグ面白いですね。本記事ではGoogleの傘下に入ったことでわかに話題になっているDataformについて、触ってみたテイストのご紹介をいたします。こんなことが出来ちゃうんだやっほーい的内容に加え、これどうやるんだ？やり方がわからない…的な内容も書きたいと思います。ちなみに、Dataformは手持ちのGoogleアカウント等でぺろっと利用開始できます。便利ですね。
BigQueryを利用する構成で触った結果を書いていきます。
Dataformってなあに
一言でいうと「○○ツール」です！という書き出しにしたかったのですがすみません思い浮かびませんでした。なんて呼ぶんですかね、こういうの。出来るのはジョブ制御だけではないし…教えて欲しい。私の語彙力では一言で表現できないので、できる事を列挙してご説明していきます。
複数SQLの実行制御
SQLを複数実行したい、特に分岐や待ち合わせをさせる必要がある場合は別途ジョブ制御の実現方法を考える必要があるかと思います。弊社ではAirflowとかを使って実現してます。Dataformがこの役割を果たすことが可能です(AirflowはSQLに範囲を限定していませんので出来ることは遥かに多いですが)。
しかも、実行順序をわしわし書く必要がなくて、SQLの内容から依存関係を解釈して自動で組んでくれます。マーベラスですね。
SQL実行のスケジューリング
SQLを指定の日時に実行予約できます。cron形式で繰り返しの指定も可能です。
ユニットテスト
各SQLの実行結果の妥当性を検証する処理を組み込むことができます。NULLの判定や、想定外の値を持つ行が生まれていないかの判定なんかも簡単に設定出来ちゃいます。
その他
ご紹介した以外にも、まだまだ機能がありそうです。発売日の早いゲーム攻略本みたいな感じになってしまいますが、この先は君の目で確かめてくれよな！すみません私の目でも確かめて行きます。
Dataform上での設定の仕方
SQLXという形式で各種設定を記述していきます。といっても難しいものではなくて、SQLにJSONのようなフォーマットでいくらか書き足す感じです。
この場合は、SQLの実行結果をテーブルにするよ。ということとテーブルの説明を書き足しています。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
config { type: "table", description: "complaint_typeとstatus単位でunique_keyのユニーク数を数える" } SELECT complaint_type, status, COUNT(DISTINCT unique_key) AS count_unique_key FROM ${ref("311_service_requests")} GROUP BY complaint_type, status |
以下の記述は、別途設定しておいたテーブルの在りかの情報を呼び出しています。
|
1 |
${ref("311_service_requests")} |
Dataformによって解釈されて、今回の場合ですと以下の文字列に置換されて実行されます。
|
1 |
`bigquery-public-data.austin_311.311_service_requests` |
refで呼び出せるようにする設定もSQLXで記述します。SELECTを実行するSQLとは別ファイルとして切り出し、typeをdeclarationに設定してdatabaseとshema、nameを指定します。BigQueryの場合databaseがプロジェクト、schemaがデータセット、nameがテーブルを指します。
|
1 2 3 4 5 6 |
config{ type: "declaration", database: "bigquery-public-data", schema: "austin_311", name: "311_service_requests" } |
こうしておけば、開発環境で試した後に本番環境に持っていく際に個々のSQLを変更して回る必要がないので助かりますね。
複数SQLの実行制御をやってみる
私が特に感嘆した、SQL実行制御にフォーカスしてご紹介を続けます。
- Table1：BigQueryに保持している元データから、complaint_typeとstatusでGROUP BYして集計したテーブルを作成
- Table2：BigQueryに保持している元データから、complaint_typeでGROUP BYして集計したテーブルを作成
- Table3：Table1とTable2をJOIN
という処理をつくってみます。CLIでも操作を完結できるようなのですが、今回はGUIでの操作でご説明していきます。
画像赤枠のエリアでファイルの管理が出来ます。ここからファイルを新規作成もできます。ファイル管理はGitHubと連携も可能です。選択したファイルを青枠の領域で編集できます。
まずは、データ加工を行っていく元になるテーブルをconfigブロックで定義します。下記は先ほどの説明で利用したものと同じSQLXです。これでrefで呼び出せるようになります。
|
1 2 3 4 5 6 |
config { type: "declaration", database: "bigquery-public-data", schema: "austin_311", name: "311_service_requests" } |
次に、Table1,2,3それぞれのSQLXを書きます。
Table1
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
config { type: "table", description: "complaint_typeとstatus単位でunique_keyのユニーク数を数える" } SELECT complaint_type, status, COUNT(DISTINCT unique_key) AS count_unique_key FROM ${ref("311_service_requests")} GROUP BY complaint_type, status |
Table2
|
1 2 3 4 5 6 7 8 9 10 11 12 |
config { type: "table", description: "complaint_type単位でunique_keyのユニーク数を数える" } SELECT complaint_type, COUNT(DISTINCT unique_key) AS count_total_unique_key FROM ${ref("311_service_requests")} GROUP BY complaint_type |
Table3
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
config { type: "table", description: "table1とtable2をJOIN" } SELECT ${ref("table1")}.complaint_type, ${ref("table1")}.status, ${ref("table1")}.count_unique_key, ${ref("table2")}.count_total_unique_key FROM ${ref("table1")} LEFT OUTER JOIN ${ref("table2")} ON ${ref("table1")}.complaint_type = ${ref("table2")}.complaint_type |
ここまでのSQLXを作成した状態で「Dependency tree」画面に遷移すると…
自動でフロー出来上がっとる～。実行すると、この通りにSQLが順番に流れます。素晴らしい！こんなに簡単に出来るとは…
出来るかどうか分からないこと
私はもうDataformが大好きになっていますが、これどうやるんだ、できるのか…？という事柄もありました。
データベースへのデータ取り込み
ファイルをデータベースに取り込む機能を見つけられませんでした。現時点では自前で処理を組む必要がありそうです。そのうちCloud Storage上のファイル達を外部テーブルとして読み込めるようになるんじゃないかと期待しています。
カラムのデータ型定義
そもそもBigQueryにテーブルとして存在しているデータに対して処理を行うのだから、元テーブルのデータ型に準拠するでしょ、ということなのかもしれません。となると、元テーブルの定義情報は別管理にせざるをえないのか？テーブル定義も全部管理できちゃうのでは！と期待したのですが、それはできないのかもしれません…うーむ。
ともあれ
Dataformはよいものです。通知をメール or Slackで投げられるようなので、エラー検知も出来そうです。BigQueryとDataformだけでデータパイプライン成立しちゃうじゃん！という印象で感動しています。しかも無料です。本当に無料でいいのか。はやくDataform使ってなんかつくりたいです。
明日は「トチカチを支える技術」を公開予定です。
Yuki Yanagi
Technology Div. 所属
2020年現在、急激にLeague of Legendsにハマっています。
