BigQueryへのクエリ発行・管理に「dbt CLI on Windows」を使ってみる

DA事業本部コンサルティングチームのnkhrです。

本エントリは、Google Cloud Advent Calendar 2021の19日目の記事です。dbtを使ってBigQueryのクエリを開発・管理するための環境設定を行います。

dbtは、ELTにおいて主にTransform（データ加工）をカバーするツールで、SaaS版のdbt Cloud（Developer版はFree）とCommunity版のdbt Core があります。今回は、dbt Coreに含まれるコマンドラインインタフェース「dbt CLI」をWindows10にインストールします。

mac OSでdbt CLIによりBigQueryを操作する場合は、以下のブログが参考になります。

実行環境

※Microsoft StoreからPythonをインストールしている場合は「C:\Users\<username>\AppData\Local\Microsoft\WindowsApps」配下に各バージョンのPython実行用exeファイルがインストールされています。

Windowsで過去バージョンと現在バージョンの両方を使い分けたい場合は、pipenvを利用する方法があります。

pipenv --python 3.8
> pipenv shell

pythonの複数バージョン（3.8と3.9など）をインストールしている環境では、
pipenvでpythonバージョンを指定して仮想環境を立ち上げることができます

dbt CLIのインストール

dbt CLIは、homebrew、pip、sourceのいずれかで入手できます。本ブログではpipインストールを行います。

＜仮想環境の作成＞

> python --version
Python 3.9.9

> mkdir dbt-test 
> cd dbt-test

> python3 -m venv dbt-env
python3の標準モジュール「venv(仮想環境の作成ツール）」を利用し、dbt-envという名称で仮想環境を作成します。

＜仮想環境の起動/pip install＞

> .\dbt-env\Scripts\activate 

(dbt-env)> pip install dbt
(dbt-env)> pip freeze
インストールされたライブラリを表示

(dbt-env)> dbt --version
installed version: 0.21.1
   latest version: 0.21.1

(dbt-env)> pip install --upgrade dbt

BigQuery設定

DBTのGetting Startedに従って、以下の手順を実行します。

1. BigQuery用のプロジェクトを作成する
2. dbtがPublicに公開しているBigQueryのデータセットを確認する
3. dbt CLIから接続するためのService Accountを作成する

1. BigQuery用プロジェクト作成

[Create Project]からBigQuery用のProjectを作成する。

2. dbtのPublicデータセットを確認

dbtでは、BigQueryのチュートリアル用にPublicなデータセット「`dbt-tutorial`.jaffle_shop」を提供しています。

• プロジェクト名：dbt-tutorial
• データセット名：jaffle_shop

データセットには、以下の2テーブルが存在します。

• customers
• orders

BigQueryのコンソールの「SQL workspace」のSQLエディタで以下を実行するとテーブルが確認できます。

＜データセット内の全テーブルを表示＞
SELECT * FROM `dbt-tutorial`.jaffle_shop.INFORMATION_SCHEMA.TABLES

3. dbt用のService Accountを作成

GCPで[APIs & Service]コンソールへ移動し、Credentialsタブを表示します。

[CREATE CREDENTIALS] > Service Account を選択し、下記を入力してdbt用のアカウントを作成します。

• Service account name： dbt-service-account
• Role
  • BigQuery Job User
  • BigQuery User
  • BigQuery Data Editor

作成したServcie Accountの詳細画面において、[KEYS]タブからKEYを追加（[ADD KEY] > Create new key)し 、JSONファイルをダウンロードします。ダウンロードしたJSONファイルは適切な名前に変更して~/.dbt/フォルダ配下に格納します。（後続のprofile.yaml設定で指定するファイル）

dbt project作成

Geting Startedのチュートリアルを参考に、dbtプロジェクトを作成します。チュートリアルではGitHubにリポジトリを作成してコードを管理していますが、今回はGitHubでのリポジトリ作成やコード管理の手順を省略しています。

dbt initコマンドでプロジェクトの初期設定（必要なフォルダや設定テンプレートの作成）を行います。

> cd dbt-test
> .\Scripts\activate 

> dbt init test-project
> cd test-project

projectの設定を変更

dbt_project.ymlファイルの以下の箇所を修正します。

(修正前)
name: 'my_new_project' 
… 
profile: 'default'  
… 
models:
my_new_project: 

(修正後) 
name: 'test_project'
… 
profile: 'test_project'
… 
models:
test_project:

• profile: XXXに指定した「XXX」は、後続で設定するprofile.yamlのフィールド名になります。defaultのまま変更しない場合は、profile.yamlのdefaultフィールドに接続情報を記入します。
• プロジェクト名（name）は、'^[^\\d\\W]\\w*$'の正規表現に一致している必要があるため、記号はアンダーバーのみ利用できます。

profileの設定（BigQuery接続設定）

dbtインストール後にHOME配下に「.dbt」フォルダが作成されています（~/.dbt）。この設定フォルダ内のprofile.yamlにBigQueryの設定情報を記入します。

Profile修正後に「dbt debug」コマンドにより接続確認を行います。

> cd ~/.dbt
> vim profile.yaml  
(テキストエディタでprofile.yamlを編集する）

> cd dbt-env
> .\Scripts\activate
> cd .\test-project
> dbt debug

…
All checks passed!

以下は設定イメージです。

• projectには接続先BigQueryのプロジェクトIDを設定。default設定は残して置いたままでもよい。
• datasetはBigQueryのデータセット名を設定

BigQueryデータセットとは何だ？という方は、以下のブログが参考になります。

dbtによるクエリ作成と実行

ここまでの手順でBigQueryの環境と、BigQueryを操作するためのdbtの環境が作れました。初期設定後のプロジェクトでdbt runを実行すると、下記のリソースが作成されます。

• データセット： dbt_test
  • Profileで指定したデータセットが存在しない場合、作成される
• テーブル： my_first_dbt_model
  • ＜project folder>/models/example/schema.yamlに定義したテーブル
• ビュー：　my_second_db_model

テーブルや処理の定義を変える場合は、以下のファイルを修正します。

• dbt_project.ymlの「models」フィールド

      example:
            +materialized: view
      

  • 上記はmodels/example配下に作成されるオブジェクトはデフォルトでビューとする定義です。他の設定としては、table, incremental, ephemeralが指定できます。
• ＜project folder>/models/配下のファイルの修正、追加

dbtによるテーブル作成

<dbt_project.yml変更>

models:
  test_project:
    # example:　(コメントアウト)
    #   +materialized: view (コメントアウト)
    customer:
      +materialized: table

＜モデルの追加＞

以下のファイルを作成します。

• /models/customer/schema.yaml （Yamlファイルの名前は任意）
• /models/customer/customers.sql
• /models/customer/finals.sql

models/exampleは削除します。（残っている場合、example定義のテーブル・ビューも作成されます）

version: 2

models:
  - name: customers
    description: "customer model"
    columns:
      - name: customer_id
        data_type: STRING(10)
        tests:
          - unique
          - not_null
      - name: first_name
        data_type: STRING(20)
      - name: last_name
        data_type: STRING(20)

  - name: finals
    columns:
      - name: customer_id(10)
        data_type: STRING(10)
        tests:
          - unique
          - not_null
      - name: first_name
        data_type: STRING(20)
      - name: last_name
        data_type: STRING(20)
      - name: first_order_date
        data_type: DATETIME
      - name: most_recent_order_date
        data_type: DATETIME
      - name: number_of_orders
        data_type: NUMERIC(10)

with customer_us AS (
    select
        id as customer_id,
        first_name,
        last_name

    from `dbt-tutorial`.jaffle_shop.customers
)

SELECT * FROM customer_us

with orders as (
    select
        id as order_id,
        user_id as customer_id,
        order_date,
        status
    from `dbt-tutorial`.jaffle_shop.orders
),

customer_orders as (
    select
        customer_id,
        min(order_date) as first_order_date,
        max(order_date) as most_recent_order_date,
        count(order_id) as number_of_orders
    from orders
    group by 1
)

select
    customers.customer_id AS customer_id,
    customers.first_name AS first_name,
    customers.last_name AS last_name,
    customer_orders.first_order_date AS order_date,
    customer_orders.most_recent_order_date AS most_recent_order_date,
    coalesce(customer_orders.number_of_orders, 0) as number_of_orders

from customers

left join customer_orders using (customer_id)

モデルの実行

> dbt test
> dbt run

…
Completed successfully
Done. PASS=2 WARN=0 ERROR=0 SKIP=0 TOTAL=2

新たに定義した2テーブルが、BigQuery上に作成されます。

ログ設定の変更

ログの出力先や出力レベルの変更方法を調べました。

実行ログの出力先を変更

projectの設定ファイル（dbt_project.yaml）内のlog-pathで指定します。デフォルトは「<project folder>/logs/dbt.log」です。

下記の設定例では、ユーザのHOMEフォルダ配下のlogsフォルダに「dbt.log」ファイルを出力します。

log-path: C:\Users\<user name>\logs

Debugログの出力

debugログの出力のON/OFF切り替えができます。profile.yamlで定義するか実行時のオプションとして指定できます。

<profile.yamlの定義>

config:
	debug: true

test_project:
  target: dev
  outputs:
　　……

<実行時のオプション>

> dbt --debug run

出力フォーマット変更

出力フォーマットをJSONに変更すれば、JSONラインとしてログが出力されます。

<profile.yamlの定義>

config:
	LOG_FORMAT: json

<実行時のオプション>

> dbt --log-format json run

ログのTimezoneを変更

デフォルトでは、UTCのTimezoneでログが出力される。2021/12時点ではログ出力時刻のTimezoneをUTC→JSTに変更する設定はなさそうです。

ワークフローの設定

dbtではテーブルの作成順序をref関数により設定できます。ref関数で別のモデル（modelsフォルダ下のSQLファイルの名称と対応）を指定し、モデル間の依存関係を定義することで実行順序が制御されます。

CLIではスケジュール機能を含んでいないため、dbt runをcronに設定したり、airflowやその他のワークフローツールで実行する必要があります。依存関係の可視化は、dbt Cloudでは簡易な可視化が可能ですが、CLIではThird partyのカタログツールなどと組み合わせる必要がありそうです。

まとめ

dbtは、プログラマティックにLakeHouseを管理する場合の機能を十分に備えているようです。また、開発・本番の環境ごとの定義の切り替えや、テストも実施できるため、GitHubを利用したBuild/Deplopy開発フローに適用できそうです。

LakeHouseの開発、管理ツールとして、利用を検討してみてもよいかなと思います。

参考

• dbt tutorial (getting started)
• dbt command reference
• profile.yaml reference
• dbt_project.yaml reference