はじめに
データエンジニア界隈でも重宝されるこちらの書籍「達人に学ぶSQL徹底指南書」。
基本的には複数のRDBMSを想定した記載になっていますが、各種クラウド上で提供されるDBサービス系については特に言及されていません。
この本を使って勉強するにあたって、ちょうど業務で使用しているSnowflakeで実行したいなぁ...と思いまして、データやSQLの移植を試みようとしました。
ある程度できたのでやったことをまとめてみます。
使ったもの
- 書籍の付属データ : こちらから入手できます。書籍はあらかじめ買っておきましょう!
- Snowflake : トライアルアカウントを作成しました。
- dbt : 書籍の付属データをSnowflakeに流し込むのに使いました。
できたもの
こんなイメージです。
コード
seedとmodel(marts)で作りました。
dbt seedとdbt runでデータを投入します。
Snowflake
TATSUJINデータベース内にseed用のスキーマ(SEEDS)と書籍の各章に対応したスキーマ("1-1","1-2",...)を作る感じにしています。
なお、dbtを使ってSnowflake上に環境のセットアップをしようという目的を主として用意しましたので、dbtのお作法に沿っていない点が多々あるかと思いますが、ご了承ください。
やったこと
セットアップ
詳細は割愛しますが、以下を行いました。
- Snowflakeアカウント(トライアルアカウント)の作成
- dbt-snowflakeのインストール
- dbtの初期設定(dbt init)
- dbtからSnowflakeにアクセスするための設定
- macros(generate_schema_name.sql)を追加
- Snowflake上にリソース(TATSUJINデータベース)を作成
dbt debugを実行して「All checks passed!」が表示される状態にします。
SYSADMINロールで操作するようにしました。
また、マクロ(macros)にはgenerate_schema_name.sqlを作成し、既存の挙動から変えました。dbtで作成されるスキーマ名に余計な接頭辞をつけて欲しくなかったので、以下のマクロを配置しました。
{% macro generate_schema_name(custom_schema_name, node) -%}
{%- set default_schema = target.schema -%}
{%- if custom_schema_name is none -%}
{{ default_schema }}
{%- else -%}
{{ custom_schema_name | trim }}
{%- endif -%}
{%- endmacro %}
dbt_project.ymlの作成
dbt initで作成されますが手を加えます。
以下のようにしました。
使っていないものもありますがそれはデフォルトそのままにしています。
name: 'tatsujin'
version: '1.0.0'
config-version: 2
model-paths: ["models"]
analysis-paths: ["analyses"]
test-paths: ["tests"]
seed-paths: ["seeds"]
macro-paths: ["macros"]
snapshot-paths: ["snapshots"]
clean-targets:
- "target"
- "dbt_packages"
models:
tatsujin:
marts:
+database: TATSUJIN
+schema: TATSUJIN
+materialized: view
seeds:
tatsujin:
enabled: true
+database: TATSUJIN
+schema: SEEDS
quoting:
schema: true
identifier: true
seedの作成
書籍で紹介されているSQLを実行するために必要なデータをseedとして作成しました。
これは、書籍付属のテキストファイル(code_1-1.txt等)に書かれている
CREATE TABLE ... と INSERT INTO ... をもとに書き起こしました。
これ、付属ファイル全てに対してやろうとするとすごく地道で大変な作業ですが、ChatGPTの力を借りて楽しました。
dbt seedコマンドを実行すると、Snowflake上にいい具合にテーブルにしてくれます。
seedのテーブル名(=csvファイル名)に関して、複数章にまたがって同じテーブル名が出てくるところは、テーブル名+ページといった感じでユニークになるようにしました。
どうしても同じページ内に同じテーブル名で中身が異なるものが出てきてしまう場合は連番を振りました。
参考 : ChatGPTを使ってseedを作成
与えたプロンプト
(書籍付属のテキストファイルの文章を読み込ませて)
この情報から、全てのテーブル名、カラム名、insertするデータを抜き出して、
以下のようなcsv形式のファイルを作成してください。
例 : 「CREATE TABLE Meetings ...」 という記述から以下のcsvを作成することができます。
csvファイル名 : Meetings.csv
meeting,person
'第１回','伊藤'
'第１回','水島'
'第１回','坂東'
'第２回','伊藤'
'第２回','宮田'
'第３回','坂東'
'第３回','水島'
'第３回','宮田'
この例のように、与えられたファイル全体を走査して考えられるcsvを全て作成してください。
ChatGPTからの応答
この結果に書かれたことをcsvファイルに落とせばOKです。
なお、設定上quoting: identifier: trueとしているため、ファイル名が大文字になるように後で修正しました。
(大文字小文字混じりのファイル名にしておくと、そのままテーブル名に反映されます。)
model(marts)の作成
dbtのお作法に反するかもしれませんが、seed -> martsの流れで作りました。
SnowflakeのSQLワークシートでSQLを実行する際、章番号のスキーマを使用する想定で、{{ config(schema='1-1')}}といった記述を加えました。
書籍に記載のSQLを実行する時は、Snowflake上で以下のように実行します。
use role sysadmin;
use warehouse compute_wh;
use database TATSUJIN;
use schema "1-1";
select * from poptbl;
SELECT CASE pref_name
WHEN '徳島' THEN '四国'
WHEN '香川' THEN '四国'
WHEN '愛媛' THEN '四国'
WHEN '高知' THEN '四国'
WHEN '福岡' THEN '九州'
WHEN '佐賀' THEN '九州'
WHEN '長崎' THEN '九州'
ELSE 'その他' END AS district,
SUM(population)
FROM PopTbl
GROUP BY CASE pref_name
WHEN '徳島' THEN '四国'
WHEN '香川' THEN '四国'
WHEN '愛媛' THEN '四国'
WHEN '高知' THEN '四国'
WHEN '福岡' THEN '九州'
WHEN '佐賀' THEN '九州'
WHEN '長崎' THEN '九州'
ELSE 'その他' END;
marts作成のパターンは大体以下の2つです。
SEEDそのまま
{{ config(schema='1-2')}}
WITH final as (
SELECT * FROM {{ source('seeds', 'LOADSAMPLE') }}
)
select * from final
今回サボっちゃいましたが、これは本来はstagingを経て作成されるものかと思います。
なお、{{ source('seeds', 'LOADSAMPLE') }}でseedを参照できるように、marts.ymlにはあらかじめ記載してあります。
version: 2
sources:
- name: seeds
schema: SEEDS
tables:
# 1-1
- name: POPTBL
- name: POPTBL2
- ... (中略)
# 1-2
- name: LOADSAMPLE
- ... (中略)
# 1-3
- name: ADDRESSES
- ... (中略)
書籍のテーブル結合やview作成の記載を反映
{{
config(
schema='3-A',
materialized='table'
)
}}
WITH final as (
SELECT ROW_NUMBER()
OVER(PARTITION BY name, price
ORDER BY name) AS row_num,
name, price
FROM {{ source('seeds', 'PRODUCTS') }}
)
SELECT * FROM final
上記は書籍内でviewではなくtableとして作成されていたのでそれはそのまま反映しています。
そのほか
さて、Snowflake上に必要なネタ(データ)が揃った、いざ書籍内のSQLを実行しよう...となりますが、途中で引っ掛かります。
Snowflakeでは対応していないSQLの表現(方言等)があるからです。
例 : 1-2 必ずわかるウィンドウ関数 より
-- エラーになる : Window関数の別名をつけて参照することができないため
SELECT sample_date AS cur_date,
load_val AS cur_load,
MIN(sample_date) OVER W AS latest_date,
MIN(load_val) OVER W AS latest_load
FROM LoadSample
WINDOW W AS (ORDER BY sample_date ASC
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING);
SELECT sample_date AS cur_date,
load_val AS cur_load,
MIN(sample_date)
OVER (ORDER BY sample_date ASC
ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) AS latest_date,
MIN(load_val)
OVER (ORDER BY sample_date ASC
ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) AS latest_load
FROM LoadSample;
こういうものは引っかかり次第修正が必要です。
おわりに
ここまで作って満足しちゃいましたが、書籍を使って勉強が本来の目的なので、これをもとに学習を進めようかと思います。
また、余力があればdbtのお作法(スタイルガイド)に近づけて再定義もしてみたいですね。
うまくやり切れば、Snowflake x dbt x 達人に学ぶSQL の組み合わせで一気に学びが深まるのかなと思います。(大変ですが...)
以上です。