達人に学ぶSQL徹底指南書のSQLをSnowflakeで実行する為にdbtを使って構築してみた

##  はじめに

データエンジニア界隈でも重宝されるこちらの書籍「[達人に学ぶSQL徹底指南書](https://www.shoeisha.co.jp/book/detail/9784798157825)」。

基本的には複数のRDBMSを想定した記載になっていますが、各種クラウド上で提供されるDBサービス系については特に言及されていません。

この本を使って勉強するにあたって、ちょうど業務で使用しているSnowflakeで実行したいなぁ...と思いまして、データやSQLの移植を試みようとしました。

ある程度できたのでやったことをまとめてみます。

## 使ったもの

- 書籍の付属データ : [こちら](https://www.shoeisha.co.jp/book/detail/9784798157825)から入手できます。書籍はあらかじめ買っておきましょう!
- Snowflake : トライアルアカウントを作成しました。
- dbt : 書籍の付属データをSnowflakeに流し込むのに使いました。

## できたもの

こんなイメージです。

### コード

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/617410/14de3196-e288-0a86-e89a-5ffd5a4a3dae.png)

seedとmodel(marts)で作りました。
`dbt seed`と`dbt run`でデータを投入します。

### Snowflake

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/617410/4a5b6734-040f-3ac0-62b6-6bdf30749157.png)

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で作成されるスキーマ名に余計な接頭辞をつけて欲しくなかったので、以下のマクロを配置しました。

```python:macros/generate_schema_name.sql
{% 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`で作成されますが手を加えます。

以下のようにしました。
使っていないものもありますがそれはデフォルトそのままにしています。

```yml
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として作成しました。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/617410/ed96d07b-b6c0-a3c2-b285-e281652285e9.png)

これは、書籍付属のテキストファイル(code_1-1.txt等)に書かれている
`CREATE TABLE ...` と `INSERT INTO ...` をもとに書き起こしました。

これ、付属ファイル全てに対してやろうとするとすごく地道で大変な作業ですが、ChatGPTの力を借りて楽しました。

`dbt seed`コマンドを実行すると、Snowflake上にいい具合にテーブルにしてくれます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/617410/1ff66d5c-18b6-aeeb-bf12-985f3be8d15a.png)

seedのテーブル名(=csvファイル名)に関して、複数章にまたがって同じテーブル名が出てくるところは、テーブル名+ページといった感じでユニークになるようにしました。
どうしても同じページ内に同じテーブル名で中身が異なるものが出てきてしまう場合は連番を振りました。

#### 参考 : ChatGPTを使ってseedを作成

##### 与えたプロンプト

(書籍付属のテキストファイルの文章を読み込ませて)

```text
この情報から、全てのテーブル名、カラム名、insertするデータを抜き出して、
以下のようなcsv形式のファイルを作成してください。

例 : 「CREATE TABLE Meetings ...」 という記述から以下のcsvを作成することができます。

csvファイル名 : Meetings.csv

meeting,person
'第１回','伊藤'
'第１回','水島'
'第１回','坂東'
'第２回','伊藤'
'第２回','宮田'
'第３回','坂東'
'第３回','水島'
'第３回','宮田'

この例のように、与えられたファイル全体を走査して考えられるcsvを全て作成してください。
```

##### ChatGPTからの応答

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/617410/31723331-91c3-0e03-4f47-380e052842a0.png)

この結果に書かれたことをcsvファイルに落とせばOKです。
なお、設定上`quoting: identifier: true`としているため、ファイル名が大文字になるように後で修正しました。
(大文字小文字混じりのファイル名にしておくと、そのままテーブル名に反映されます。)

### model(marts)の作成

dbtのお作法に反するかもしれませんが、seed -> martsの流れで作りました。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/617410/2ee77ad4-49e6-8adf-abe0-06b5b51958fd.png)

SnowflakeのSQLワークシートでSQLを実行する際、章番号のスキーマを使用する想定で、`{{ config(schema='1-1')}}`といった記述を加えました。

書籍に記載のSQLを実行する時は、Snowflake上で以下のように実行します。

```sql
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そのまま

```sql:models/marts/1-2/LOADSAMPLE.SQL
{{ config(schema='1-2')}}

WITH final as (
  SELECT * FROM {{ source('seeds', 'LOADSAMPLE') }}
)

select * from final
```

今回サボっちゃいましたが、これは本来はstagingを経て作成されるものかと思います。

なお、`{{ source('seeds', 'LOADSAMPLE') }}`でseedを参照できるように、marts.ymlにはあらかじめ記載してあります。

```yml:models/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作成の記載を反映

```sql:models/marts/3-A/LOADSAMPLE.SQL
{{
  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 必ずわかるウィンドウ関数 より

```sql:書籍そのままのSQL
-- エラーになる : 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);

```

```sql:Snowflakeで実行できるように修正したもの
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のお作法([スタイルガイド](https://docs.getdbt.com/best-practices/how-we-style/0-how-we-style-our-dbt-projects))に近づけて再定義もしてみたいですね。

うまくやり切れば、Snowflake x dbt x 達人に学ぶSQL の組み合わせで一気に学びが深まるのかなと思います。(大変ですが...)

以上です。