Dataform CLI で CUI から BigQuery のクエリを管理する

はじめまして、株式会社 HQ のソフトウェアエンジニアの入谷です。この記事では Dataform CLI を使って CUI から BigQuery のクエリを管理する方法を紹介します。

Dataform を使った BigQuery のクエリ管理

HQ では BigQuery を使って、請求等の業務や重要指標の計測等、様々なデータ分析を行っています。その場だけのクエリであれば書き捨てでも良いですが、重要な業務に使われるクエリについては、当然ながらバージョン管理や検証を行って適切に管理したいという要求が出てきます。そこで、HQ ではクエリの管理に Dataform を採用しています。

Dataform は ELT (Extract Load Transform) を実現するデータ変換を管理するためのサービスです。2020 年に Google Cloud に買収されており、Google Cloud コンソールにも統合されています。

Google Cloud コンソール上で Dataform クエリを実行しているスクリーンショット

Dataform は、BigQuery を拡張した SQLX と呼ばれる独自の形式でクエリを管理します。Dataform を用いることには次のような利点が有ります。

GitHub と連携したクエリのバージョン管理ができる
クエリ内で JavaScript を使って定数を定義したり関数を実行したりできる
アサーションや単体テストでクエリの結果を検証できる
テーブルやビューの列の定義や依存関係が BigQuery 上で確認できる

似たようなツールとしては dbt が広く使われていますが、HQ では BigQuery との親和性の高さから Dataform を選択しています。追加料金が不要というのも HQ のようなスタートアップには嬉しい点です。

Google Cloud コンソールから気軽に利用できるのも強みです。GitHub との連携もサポートしているので、クラウドコンソール上で編集し、その変更を GitHub リポジトリに反映して変更履歴を管理することも可能です。

Dataform プロジェクトで view1.sqlx を追加するコミットを作成しているスクリーンショット

ウェブ上のエディタは気軽に使えるという利点が有る一方で、操作性に関してはやはり使い慣れたテキストエディタに軍配が上がります。また、ウェブ上のフォーマッタは自動でインデントを整えてくれるのですが、（上記スクリーンショットのクエリを見ても分かるように）あまり思ったようにフォーマットされないのが悩みの種でした。

そこで、オープンソースのコマンドラインインターフェースである Dataform CLI を使って、ローカルのテキストエディタを使いながらも Dataform を活用する方法を紹介します。

Dataform CLI をインストールする

Dataform CLI は @dataform/cli という npm パッケージで提供されています。HQ では Dataform プロジェクトの開発用依存関係として @dataform/cli を追加しています。その場合、dataform コマンドは npx dataform で実行することになります。

// package.json
{
  ...
  "devDependencies": {
    "@dataform/cli": "^2.7.0"
  }
}

公式文書にあるように npm -i でインストールしても良いです。

コンパイルしたクエリを BigQuery で実行する

dataform compile でプロジェクト内のクエリをコンパイルできます。SQLX ファイルの書式等が間違っている場合はここでエラーになります。

しかし、実際に実行してみないことにはコンパイルされたクエリが意図した挙動をするか確認できません。実は dataform compile にはコンパイル結果を JSON で出力するための --json オプションが有ります。これを指定すると次のような JSON が出力されます（例では一部を ... で省略しています）。

{
  "tables": [
    {
      "type": "view",
      "target": {
        "schema": "schema1",
        "name": "view1",
        "database": "database1"
      },
      "query": "\n\nWITH\n ...\nSELECT\n... \n",
      "fileName": "definitions/view1.sqlx"
      ...
    },
    ...
  ]
}

この JSON からクエリ部分を抽出して bq query コマンドに渡せばそのまま実行できます。これで CUI からでもクエリを編集、コンパイル、実行する流れが実現できました。

# コンパイル結果を JSON に出力
dataform compile --json > tables.json
# コンパイル結果からビュー view1 のクエリを抽出
cat tables.json | jq --raw-output '.tables[] | select(.target.name == "view1") | .query' > view1.bq
# BigQuery でクエリを実行
# 必要に応じて --format や --project_id も指定できる
cat view1.bq | bq query --use_legacy_sql=false

単体テストを実行する

Dataform では、テスト対象、結果の期待値、依存関係のデータを SQLX ファイルに記述することで、テーブルやビューに対する単体テストを作成できます。

dataform test でプロジェクト内の単体テストを実行できます。

> dataform test

Compiling...

Compiled successfully.

Running 61 unit tests...

...
test_view1: passed

SQLX を実行してビューやテーブルを作成・更新する

dataform run で SQLX ファイルを実行することで、SQLX ファイルで定義されているビューやテーブルを BigQuery で作成・更新できます。--include-deps で依存関係を含めて実行することもできます。

> dataform run --actions view1 --include-deps

Compiling...

Compiled successfully.

Running...

Dataset created:  database1.schema1.view1 [view]

もっと便利にする

実際には、上記の Dataform コマンドを手軽に実行できるようにするため、Makefile と Visual Studio Code 用のタスクを用意しています。というより、私が作りました。開発コンテナーや GitHub Codespaces から実行できるようにするため、認証情報の取り回し等も行っています。

HQ では、すぐ終わるタスクであればソフトウェアエンジニアの判断で適宜実施しても良いというスクラム運用が成されているため、このような生産性向上に繋がる改善も日々の業務の中で取り組めるのが良い所です。

最後に

この記事では、Dataform CLI を用いることで、ローカルのテキストエディタを使いながら快適に BigQuery の開発と管理が行えることを紹介しました。Dataform に関する情報は、特に日本語のものはまだまだ数が少ない印象です。これをご覧になった方が Dataform に挑戦し、その知見を共有していただけたらとても嬉しいです。

開発チームでは人材の採用をしています！HQ では初期プロダクトとして社員の個別最適なリモートワーク環境の構築を支援する「リモートHQ」を開発、運用していますが、今後更にプロダクトは増えていく予定です。

採用情報

株式会社HQ - 採用情報

hq-hq.notion.site