dbtでは公式・非公式を含め、非常に多くのWeb資料が公開されています。dbtがどんな機能を備えているのか、どんなUIや画面デザインなのかを把握するのはやはり実際にモノを動かして試してみる、挙動を体験してみるというのが一番ですね。
そこで当エントリでは、全15パートで構成されているdbt公式の入門向けドキュメント『Quickstart for dbt Cloud and BigQuery』を実際に試した内容をお届けしたいと思います。
目次
- 01.はじめに
- 02.新しいGoogle Cloudプロジェクトを作成
- 03.BigQueryデータセットの作成
- 04.BigQuery環境へアクセスするための認証情報を作成
- 05.dbt Cloud環境からBigQueryに接続
- 06.dbt Cloud管理のリポジトリを設定
- 07.dbtプロジェクトの初期化と開発の開始
- 08.はじめてのモデル構築
- 09.モデルのマテリアライズ方法を変更
- 10.サンプルモデルの削除
- 11.他のモデルの上にモデルを作成する
- 12.モデルに対してテストを追加
- 13.モデルに対してドキュメントを生成
- 14.ここまでの変更をコミット＆マージ
- 15.dbtのデプロイ
- まとめ
01.はじめに
このステップでは、クイックスタートを実践することで何を学ぶことが出来るのかについて紹介がされています。
- Google Cloudのプロジェクトを作成
- Google BigQuery上のパブリックデータセットにあるサンプルデータにアクセス
- dbt Cloud環境からBigQueryに接続
- 作成したdbtのプロジェクト内でモデルに対してサンプルクエリを実行し、dbtにおけるモデルはSelect文で構成されていることを理解
- モデルに対するテストの追加
- モデルの情報に対してドキュメントを生成
- 実行するジョブのスケジューリング
また、このステップを実践していく上では予め以下の情報や環境を用意しておく必要があります。ここではこれらの要素自体の準備、設定に関する手順の紹介は割愛します。
- dbt Cloudのアカウント
- Googleのアカウント
- BigQueryが利用可能な何らかの(個人用でも仕事用でも)Google Cloudアカウント
02.新しいGoogle Cloudプロジェクトを作成
予め用意しておいたGoogle Cloudアカウント上でBigQueryコンソールにアクセス。複数のGoogle Cloudアカウントを所有している場合は、選択しているアカウントが意図した正しいものであることを入念に確認しておいてください。
データセットを管理するプロジェクトを作成・指定。詳細に関しては下記ドキュメントをご参照ください。
(※今回は予め作成したこのプロジェクトを用意します。といってもプロジェクト名はモザイク掛けているので見せられませんが...)
03.BigQueryデータセットの作成
BigQueryコンソールにて「クエリを新規作成」を選択。新しくクエリエディタ画面を開き、下記SQLをそれぞれ実行し、結果が得られていることを確認します。
select * from `dbt-tutorial.jaffle_shop.customers`;
select * from `dbt-tutorial.jaffle_shop.orders`;
select * from `dbt-tutorial.stripe.payment`;
上記クエリで参照した要素が今回扱うデータセット及びテーブルの情報となります。このテーブル情報とテーブルを含むデータセットの情報を、自ら用意したプロジェクトを通じて作成・管理していきます。
04.BigQuery環境へアクセスするための認証情報を作成
このステップでは、Google BigQuery環境へアクセスするための認証情報を作成します。多くのデータウェアハウスではDBユーザー名とパスワードの対でアクセスする事が多いですが、Gogole BigQueryではキーファイルを利用してアクセスを行います。
所定のプロジェクト配下であることを確認の上、「認証情報の作成」経由で必要な情報を作成します。下記の選択内容で[次へ]を押下。
- APIを選択：BigQuery API
- アクセスするデータの種類：アプリケーションデータ
- Compute Engine、...(中略)でこのAPIを使用する予定はありますか？：いいえ、使用していません
サービスアカウント名に任意の名前を指定、[作成して続行]を押下。
特定のアクション権限を付与：BigQuery管理者 で[続行]を押下。
「ユーザーにこのサービスへのアクセスを許可」については指定無しで[完了]を押下。
サービスアカウントが作成されました。次いでこのアカウントに紐づく形でサービスアカウントキーを作成します。「サービスアカウントの管理」のリンクをクリック。
先程作成したサービスアカウントキーのメニューから「鍵を管理」を選択。
「鍵の追加」ボタンから「新しい鍵の作成」を選択。
鍵のタイプは「JSON」を選択します。[作成]リンクをクリック。
対象のサービスアカウントにおける鍵が作成されました。なお、作成されたファイルは分かりやすい名前にリネーム(※今回の場合はdbt-user-shinyaa31-creds.json)しています。
05.dbt Cloud環境からBigQueryに接続
ここからはdbt Cloudでの操作となります。dbt Cloudに作成・払い出し済みのユーザーでログインし、
画面右上の設定アイコンから[Account Settings]を選択。
プロジェクト一覧画面に遷移します。[New Project]を押下。
任意のプロジェクト名を入力し、[Continue]を押下。
接続対象となるデータウェアハウスの種類を選択します。ここでは[BigQuery]を指定し、[Next]を押下。
前段手順で作成したサービスアカウントキーの設定をここで連携させます。[Settings]の項にある[Upload a Service Account JSON file]を押下し、予め作成しておいたJSONファイルを読み込ませてください。
ファイルを読み込ませると、dbt側でその内容を解析し、諸々の項目に値が設定されていきます。それらの値については今回は特に変更する必要はありません。[Development Credentials]の項では[Dataset]欄に(恐らく接続ユーザー名から)自動生成された値が自動で設定されています。特に問題なければその部分の値もそのままで進めてください。[Test Connection]を押下。
(※データセット名の注釈文『開発中、dbt はモデルをこの名前のデータセットに構築します。このデータセット名は個人の開発環境に固有のものである必要があり、チームの他のメンバーと共有しないでください。』)
※ここの[Dataset]名は『個人開発環境で用いるデータセット名』が入るイメージです。この箇所とは別に、手順の最終盤(dbtプロジェクトのデプロイ作業)で『本番環境で用いるデータセット』を入力させる場面が出てきます。以下のような例を参考に、環境毎のデータセットの名前に関しては予め検討しておきましょう。
例)
・個人開発環境用データセット名：xxxxxxx_個人名_dev
・本番環境用データセット名：xxxxxxx_prod
ちなみに今回のエントリでは開発用は接尾辞をつけず、本番用は『_prod』という名前で識別子として分けて設定しています。
dbt側で接続テストが実施されます。[COMPLETE]が出ていれば接続確認もOKです。[Next]押下。
06.dbt Cloud管理のリポジトリを設定
上記手順に次いで、dbtプロジェクトで利用するリポジトリの設定に移ります。dbtでは下記画面にあるように、幾つかの選択肢を対処として選ぶことが出来ます。個別にGitリポジトリを持っていない場合でも問題ありません。このクイックスタートではdbt側で用意した「マネージド」なリポジトリを活用する事が出来ます。
ということで、選択肢の中から[Managed]を選択。リポジトリ名に任意の名前を入力して[Create]を押下。
程無くしてリポジトリが作成され、dbtプロジェクト利用の準備が整いました！[Start developing in the IDE]のリンクをクリックして次の手順に進みます。
07.dbtプロジェクトの初期化と開発の開始
前述手順で[Start developing in the IDE]のリンクをクリックすると以下の進捗画面が現れます。こちらも程なくすると初期設定が完了し、
以下のIDE画面が表示されるようになります。画面左上にある[Version Control]配下にあるメニューに[Initialize dbt project]と表示されていますのでこれをクリック。
プロジェクトの初期化が行われ、配下に設定ファイル/フォルダ群が表示されるようになりました。一旦この時点の状況をコミットしておきましょう。画面左上の[Version Control]配下にあるメニューが[Commit and sync]とメッセージが変わっています。これをクリック。
任意のコミットメッセージを入力し、[Commit Changes]を押下。
コミット完了です。ここまでの状況を踏まえた環境で初めてのdbtコマンド実行を試してみます。画面下のこの部分でdbtコマンドを入力し実行する事が可能です。
dbt runと入力しEnter押下。するとdbtコマンドが実行されます。実行時のシステムログが以下のような形で出力され、成功する形で処理が行われました。
また、画面右上の[+]をクリックすることで新しいファイルを作成する事が出来、以下のようにSQLを記載、実行することも可能です。
08.はじめてのモデル構築
ここからは実際にモデルを作り上げ、そのモデルに対して色々処理をしていきます。まずは対象となるモデルの作成から。
ここまでの手順を経ると[Version Control]欄の要素が[Create branch]となっているのでこれを押下。
任意のブランチ名を入力し、[Submit]を押下。
次に、File Explorerの要素内に[models]というフォルダがありますので、その配下にファイルを新規作成します。[models]フォルダのメニューから[Create File]を選択。
customers.sqlというファイルを作成します。
ファイルの中身は以下の内容を転記、貼り付けてください。
models/customers.sql
with customers as (
select
id as customer_id,
first_name,
last_name
from `dbt-tutorial`.jaffle_shop.customers
),
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
),
final as (
select
customers.customer_id,
customers.first_name,
customers.last_name,
customer_orders.first_order_date,
customer_orders.most_recent_order_date,
coalesce(customer_orders.number_of_orders, 0) as number_of_orders
from customers
left join customer_orders using (customer_id)
)
select * from final[Save]押下で内容を保存します。
この状態でモデル作成を実行します。画面下部の入力欄にdbt runと入力し実行(Enter押下)。
customersに関するモデル実行が処理され、成功ステータスを確認することが出来ました。
上記ジョブ全体のログの他に、customers個別の実行結果も確認が出来ています。
09.モデルのマテリアライズ方法を変更
dbtの最も強力な機能の1つは、設定を一部変えるだけで、データウェアハウス内でモデルを具体化する方法を切り替えられることです。バックグラウンドでデータ定義言語(DDL)を作成するのではなく、キーワード変更を行うことで「テーブル」と「ビュー」の間の変更を行うことが出来ます。
デフォルト設定では、モデルの全てがビューとして作成されます。これはディレクトリレベルでオーバーライドを行い、対象ディレクトリ配下のすべてのものをマテリアライズ化(実体化)する事が出来ます。
dbt_project.ymlに関しては、プロジェクト名を以下のように、
dbd_project.yml
name: 'jaffle_shop'models配下の要素を以下のように修正します。この設定でjaffle_shop配下の要素はすべてがテーブルとして、example配下の要素はすべてがビューとしてマテリアライズ化(実体化)される形となります。
dbd_project.yml
models:
jaffle_shop:
+materialized: table
example:
+materialized: viewこの状態でdbt runを実行。するとエラーでコケてしまいました。エラーメッセージを見ると「既に存在しているテーブルがあるのでそれを手動で削除する(DROP TABLE)か、dbt run --full-refreshの形で強制的に情報をリフレッシュさせる形のいずれかを行ってください」と言っています。
ここは後者の dbt run --full-refreshコマンドを実行することにしてみました。今度はエラーなく処理が完了しました。
10.サンプルモデルの削除
ここまで来ると、プロジェクト作成時にサンプルとして用意されていたモデルを気兼ねなく削除出来るようになります。対象ファイルはmodels/example/フォルダ配下のファイルです。フォルダごと削除してしまいましょう。exampleフォルダのメニューから[Delete]を選択。
内容を確認し、[Submit]を押下。
フォルダ及び配下のファイルを物理的に削除する事ができました。
11.他のモデルの上にモデルを作成する
SQLのベストプラクティスとして、「データをクリーンアップするロジック」と「データを変換するロジック」は分けて考えるべき、というものがあります。前述作成したクエリでは、共通テーブル式(CTE)を使用することでこのベストプラクティスに倣っています。
このお作法に則ることで、ロジックを別々のモデルに分離させ、それらを「ref関数」と呼ばれる機能を使って他のモデルの上に新たにモデルを構築する事が出来ます。
前述のcustomers.sqlファイルを作成した要領で、models/stg_customers.sqlと、
models/stg_customers.sql
select
id as customer_id,
first_name,
last_name
from `dbt-tutorial`.jaffle_shop.customersmodels/stg_orders.sqlを作成します。
models/stg_orders.sql
select
id as order_id,
user_id as customer_id,
order_date,
status
from `dbt-tutorial`.jaffle_shop.orders作成後のフォルダ状況。
上記作成した2つのモデルを参照させる形にmodels/customers.sqlを修正します。先頭のwith句に関する部分を以下のように修正。
models/customers.sql
with customers as (
select * from {{ ref('stg_customers') }}
),
orders as (
select * from {{ ref('stg_orders') }}
),差分内容は以下の通り。
今回の作業を経てdbt runコマンドを実行することで、stg_customers、stg_orders、customersのビューやテーブルが作成される事が確認出来ました。customersはstg_customers、stg_ordersに依存する設定となっているため、dbtのビルド処理ではcustomersは最後に構築される流れになっています。dbtではこれらの依存関係を明示的に定義する必要はなく、設定内容に応じてdbt側で処理の順番を推測しています。
12.モデルに対してテストを追加
dbtではモデルに対してテストを追加し、実行する事が出来ます。
models/フォルダにschema.ymlというYAMLファイルを作成。以下のコードを記述してください。
models/schema.yml
version: 2
models:
- name: customers
columns:
- name: customer_id
tests:
- unique
- not_null
- name: stg_customers
columns:
- name: customer_id
tests:
- unique
- not_null
- name: stg_orders
columns:
- name: order_id
tests:
- unique
- not_null
- name: status
tests:
- accepted_values:
values: ['placed', 'shipped', 'completed', 'return_pending', 'returned']
- name: customer_id
tests:
- not_null
- relationships:
to: ref('stg_customers')
field: customer_id内容を記載後、ファイルとして保存しておきます。
そしてdbt testコマンド実行。dbt test を実行すると、dbt は YAML ファイルを繰り返し処理し、それぞれのテストに対してクエリを作成します。それぞれのクエリはテストに失敗したレコードの数を返します。この数が0であればテストは成功です。
13.モデルに対してドキュメントを生成
モデルに対してドキュメント生成の処理を実行すると、モデルを詳細に説明したドキュメントを作成し、その情報をチームと共有することができます。
ここでは、プロジェクトに基本的なドキュメントを追加していきます。先程テスト実行時に作成したmodels/schema.ymlの内容を以下の形に修正します。各種要素にdescriptionの項目が追加された形です。
models/schema.yml
version: 2
models:
- name: customers
description: One record per customer
columns:
- name: customer_id
description: Primary key
tests:
- unique
- not_null
- name: first_order_date
description: NULL when a customer has not yet placed an order.
- name: stg_customers
description: This model cleans up customer data
columns:
- name: customer_id
description: Primary key
tests:
- unique
- not_null
- name: stg_orders
description: This model cleans up order data
columns:
- name: order_id
description: Primary key
tests:
- unique
- not_null
- name: status
tests:
- accepted_values:
values: ['placed', 'shipped', 'completed', 'return_pending', 'returned']dbt docs generateコマンド実行。ドキュメント生成の処理が行われます。
画面左上の方に本のアイコンが表示されているのでこれをクリック。
以下のような形で、キレイなドキュメントが参照出来るようになりました！
dbtモデルベースのドキュメントタブ：
DWHテーブル定義ベースのドキュメントタブ：
14.ここまでの変更をコミット＆マージ
クイックスタート完了まであともう少しです。ひとまずはここまでの作業内容をリポジトリに反映しておきましょう。
[Commit and sync]を押下。
コミットメッセージを入力、[Submit]押下。
[Merge this branch to main]押下で変更をメインブランチにマージ。
これで最終ステップ、デプロイの準備が整いました。
15.dbtのデプロイ
いよいよ最後のステップ、プロジェクトのデプロイ作業です。
画面メニュー左上にある[Deploy]から[Environments]を選択。
環境一覧(Environments)に遷移。[Create Environment]を押下。
環境名に任意の値を入力、そしてdbtのバージョンについては現行最新のバージョン(latestと付いているもの)を選んでください。
[Deployment Credentitals]には、[Dataset]項目に設定する名称の件を踏まえた文字列を設定しておいてください。「任意の名称_prod」的な形が概ね選ばれ得る選択肢かなと思います。
環境作成完了。[Create Job]を押下。
任意のジョブ名称を入力し、画面を下にスクロール。
"Execution Settings"欄の設定項目について、『Generate docs on run』のチェックを有効にしておきます。また、実行コマンド(Commands)の中にdbt buildがあるかどうかも確認しておいてください。(あればそのまま、無ければ追加)
設定が完了していたら[Save]を押下。
ジョブの実行準備が出来ました。作成したジョブを早速動かしましょう。[Run Now]を押下。
ジョブの実行が始まりました。
ジョブを選択すると実行詳細・進捗情報が確認出来ます。
ジョブ実行完了。ジョブ内の処理それぞれはログなど状況を確認出来ます。
[Run Summary]のタブで参照出来る情報の他に、[Model Timing]タブ、
[Artifacts]タブで別の切り口から情報を辿ることも可能です。
プロジェクトデプロイ時に作成を指示したドキュメントも、この結果から辿れるようになっています。Documentationの[View]リンクをクリックすることで、
dbt本番環境上に用意されたドキュメントを参照出来ました。
まとめ
という訳で、dbtの公式入門ドキュメント『Quickstart for dbt Cloud and BigQuery』の実践内容紹介エントリでした。
このドキュメントに関しては、ほぼ3年前に『データ変換処理をモダンな手法で開発できる「dbt」を使ってみた | DevelopersIO』というエントリ(dbtとしてもDevIO初！)で公開、言及されていたのですが、3年という月日が経過していること、またやはりこの手の情報は実際に自分の手で触ってみる(その際のインプットをアウトプットに変える)のが一番！ということで2023年時点のものとしてチャレンジしてみた次第です。全15ステップそれぞれで作業の流れをキャプチャして進めてたらスクロールバーの長さがエラいことになってしまいましたが、非常に分かりやすくdbt全体の概要を掴むのにはうってつけのガイドでした。引き続きdbt(dbt Core/dbt Cloud共に)は様々なケースを想定した実践・検証を行って行きたいと思います。
2
