dbt + BigQuery→Lightdashをやってみた・データ整形編
はじめに
以前、dltHub + dbt + evidenceでSpotify APIからデータの取得をして整形し、可視化するところまでを試してみました。
この記事で「dbtと相性の良いLightdashを使ってみたい」と記していたので、今回はLightdashを使ってみたいと思います。と、言いつつも、この記事ではLightdashを使う手前のBigQueryにデータマートを作るまでを紹介していきます。Lightdashについては、後編「可視化編」で紹介します。
詳細な実装はGitHubにあるので、こちらも参考にしてみてください！
準備
実際にdbtを使ってデータ整形をするために準備した内容を紹介します！
パッケージ
今回は、dbt-coreを使います。また、BigQueryも使うため、dbt-bigqueryもインストールします。パッケージのバージョンは以下の通りです。
- dbt-core 1.9.0
- dbt-bigquery 1.9.0
データ
今回は、たまたま発見したTableauのサンプルデータであるBookshopのデータセットを使ってみます。データセットの詳細は、Tableauの公式サイトを見てみてください。
前もって、ExcelのデータをCSVファイルに変換してGoogle Cloud Storage (GCS)にアップロードし、BigQueryのデータセットとしてロードしておきます。
ディメンショナル・モデリング
今回は、月別でBookshopで人気の本を分析するというシナリオを考え、必要なデータマートを設計しました。売上数と貸出回数を元に人気の本が分析できるようにします。
スタースキーマ
スタースキーマはこのようにしました。ファクトにあたる、売上数(amount)や貸出回数(checkouts)をファクトテーブルに集約します。それぞれの本に関する情報は1つのディメンションテーブルにまとめました。2つのテーブルをbook_idをキーに結合するシンプルな構造にしました。
スタースキーマについてはこちらの記事で詳しく紹介されています。
整形フロー
整形フローは以下の図のようにしました。先ほどのスタースキーマで示した2つのテーブルを作成し、最終的に結合したマートテーブルであるmonthly_popular_booksを作る処理をします。
アーキテクチャ
アーキテクチャは、このような感じです。メダリオンアーキテクチャっぽくしました。
- Bronze: 生データ (GCSからロードしたデータ)
- Silver: 中間テーブル
- Gold: ディメンション・ファクトテーブル
- Platinum: マートテーブル
のような感じで分けています。
dbtを使ってマートテーブルを作る
整形フローまでを設計したので、あとは実装していきます！
models/以下のファイル構成はこのような感じになっています。
.
└── models
├── datalake.yaml
├── silver
│ ├── silver_checkouts.sql
│ └── silver_sales.sql
├── gold
│ ├── dim_book.sql
│ └── fact_monthly_sales_and_checkouts.sql
└── platinum
├── monthly_popular_books.sql
└── monthly_popular_books.yml
Bronze
GCSからロードしたテーブルをsource関数で参照できるように、以下のようなYAMLファイルを書いておきます。
version: 2
sources:
- name: bronze
schema: Tableau_Bookshop
tables:
- name: Author
- name: Award
- name: Book
- name: Checkouts
- name: Edition
- name: Info
- name: Publisher
- name: Ratings
- name: Sales
- name: Series
Silver/Gold/Platinum
設計した整形フローをもとに、それぞれのレイヤーのモデルを実装していきます。
なぜかBookshopのデータが2193年の設定になっているので、日付の処理はちょっと工夫が必要でした。
実装したモデル
Silver
silver_checkouts
with final as (
select
-- Salesを見ると2193年のデータ
DATE(2193, checkout_month, 1) as checkout_month,
* except(checkout_month)
from {{ source('bronze', 'Checkouts') }}
)
select * from final
silver_sales
with cleaned_sales as (
select
-- Awardを見ると2100年代なので、200年足す
date_add(parse_date('%x', sales_date), interval 200 year) as sales_date,
* except(sales_date)
from {{ source('bronze', 'Sales') }}
),
final as (
select
book_id,
price,
cleaned_sales.*
from cleaned_sales
left join {{ source('bronze', 'Edition') }}
using(isbn)
)
select * from final
Gold
dim_book
with average_ratings as (
select
book_id,
-- 全期間でレビュー点数を平均
avg(rating) as average_rating
from {{ source('bronze', 'Ratings') }}
group by book_id
),
cleaned_info as (
select
concat(book_id_1, book_id_2) as book_id,
* except(book_id_1, book_id_2)
from {{ source('bronze', 'Info') }}
),
final as (
select
book.book_id,
book.title,
info.genre,
concat(author.first_name, ' ', author.last_name) as author_name,
average_rating
from {{ source('bronze', 'Book') }} as book
left join {{ source('bronze', 'Author') }} as author
using(auth_id)
left join cleaned_info as info
using(book_id)
left join average_ratings
using(book_id)
)
select * from final
fact_monthly_sales_and_checkouts
with monthly_sales as (
select
book_id,
date_trunc(sales_date, month) as sales_month,
count(book_id) as amount
from {{ ref('silver_sales') }}
-- 月別で集計する
group by book_id, date_trunc(sales_date, month)
),
final as (
select
monthly_sales.book_id,
ifnull(sales_month, checkout_month) as year_month,
monthly_sales.amount,
checkouts.number_of_checkouts as checkouts
from monthly_sales
left join {{ ref('silver_checkouts') }} as checkouts
on monthly_sales.book_id = checkouts.book_id
and monthly_sales.sales_month = checkouts.checkout_month
)
select * from final
Platinum
monthly_popular_books
with final as (
select
sales_checkouts.*,
dim_book.* except(book_id)
from {{ ref('fact_monthly_sales_and_checkouts') }} as sales_checkouts
left join {{ ref('dim_book') }} as dim_book
using(book_id)
)
select * from final
詳細はGitHubも参考にしてください。
整形フローと同じですが、リネージを可視化するとこんな感じです。
データマートを確認
作成したデータマート monthly_popular_bookをBigQueryで確認してみます。
以下のようなクエリで、2193年5月の売上数トップ10の本を見てみます。
select
*
from monthly_popular_book
where year_month = '2193-05-01'
order by amount desc
limit 10
クエリ実行の結果は以下のような感じです。それっぽい結果が返ってきてます。
おわりに
今回は、Lightdashで可視化するために、BigQueryとdbtを使ってマートテーブルを作る整形処理までをやってみました。次回は、可視化編でLightdashを使ってみた内容を書きます！
Discussion