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

クラスメソッド Google Cloud Advent Calendar 2021 19日目の記事です。dbtを使ってBigQueryのクエリを開発・管理するための環境設定を行います。
2021.12.19

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を操作する場合は、以下のブログが参考になります。

実行環境

環境 バージョン
OS Microsoft Windows 10 Pro
python 3.9.9  ※dbt CLIは2021/11時点でpython 3.6以上が必要。
dbt CLI 0.21.1 ※Python 3.9.xは不具合報告もあるため、安定利用の場合は3.8.x系が良いかも
git 2.34.1

※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定義のテーブル・ビューも作成されます)

ファイル内容(公式サイトチュートリアルのコードを分解して利用※表示は▶をクリック)

- schema.yaml

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)

- customers.sql

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

    from `dbt-tutorial`.jaffle_shop.customers
)

SELECT * FROM customer_us

- final.sql

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の開発、管理ツールとして、利用を検討してみてもよいかなと思います。

参考