dbtを試してみた

# 背景・目的
クラウドデータ基盤のELTツールのdbtについて、特徴の整理と簡単な動作を確認してみます。

# まとめ
- dbtは、SQLファーストのELTをツールです。
- dbtのアカウントは、下記の3種類あります。
    - Developer
    - Team
    - Enterprise


# 概要
## The analytics engineering workflow
dbt を使用すると、データ チームはウェアハウス内で直接作業して、レポート、ML モデリング、運用ワークフロー用の信頼できるデータセットを生成します。

## What is dbt?
https://www.getdbt.com/product/what-is-dbt/

dbt は、モジュール性、移植性、CI/CD、ドキュメントなどのソフトウェア エンジニアリングのベスト プラクティスに従って、チームが分析コードを迅速かつ共同で展開できるようにする SQL ファーストの変換ワークフローです。 


### How dbt works
#### Version Control and CI/CD
開発環境を使用して安全にデプロイする。 
Git 対応のバージョン管理により、コラボレーションと以前の状態への復帰が可能。

#### Test and Document
運用前にすべてのモデルをテストし、動的に生成されたドキュメントをすべてのデータ関係者と共有する。

#### Develop
モジュール形式のデータ変換を .sql または .py ファイルに書き込む。
依存関係管理の面倒な作業は dbt が処理する。

### The new standard for data transformation
#### Develop faster
定型的な DDL/DML を、依存関係を推論し、テーブルとビューを構築し、モデルを順番に実行する単純な SQL SELECT ステートメントに置き換える。 
Cloud IDE でマクロ、ref ステートメント、オートコンプリート コマンドを使用してコード自体を記述するコードを開発する。
Python パッケージを利用して、複雑な分析を高速化する。

#### Work from the same assumptions
自動生成された依存関係グラフと動的データ ディクショナリは、データ利用者の信頼と透明性を促進する。

#### Deploy with confidence
アプリ内スケジュール、ログ記録、アラート機能を使用して、変換ワークフローに可観測性を組み込む。ブランチ上の保護ポリシーにより、CI の実行ごとに生成される開発、ステージ、本番環境などの管理されたプロセスを通じてデータが確実に移動される。

#### Eliminate silos
データ サイエンス チームは、分析チームが構築したモデルと接続するモデルを、それぞれが好みの言語を使用して構築できるようになった。dbt は SQL または Python でのモデリングをサポートしており、分析コードを扱う全員が共有ワークスペースを利用できるようする。

#### Security
SOC-2 準拠、CI/CD 導入、RBAC、ELT アーキテクチャによりリスクを管理する。

#### Governance
バージョン管理、テスト、ロギング、アラートによりデータの疑いを排除する。スナップショットは時間の経過とともに変化し、ホストされているドキュメントへのオープン アクセスを提供する。


# 実践
[Quickstart for dbt Cloud and Redshift](https://docs.getdbt.com/quickstarts/redshift?step=1)を参考に試します。

## 【dbt】 dbtアカウントの作成
まずは、dbtアカウントを作成します。

1. [Start using dbt now](https://www.getdbt.com/pricing/)のページから、FreeのDeveloperでSign upします。
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/e8d0d1db-0c9c-6b99-3f42-164f338604cf.png)

1. 下記を入力し、「Create my account」をクリックします。
    - Email
    - First nameとLast name
    - Company
    - Password
    - How did you hear about dbt?
    - I agree to the Terms of Useをチェック
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/03f2fc16-1e26-990f-d22b-50456d0c3b2f.png)

1. メールが送られてくるので、「Verify email address」をクリックします。
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/d4e47f90-5b63-0268-ec1c-dbed14deb373.png)

1. 画面が表示されます。ここでは、「I don't have a data warehouse」を選択します。
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/10dd5cbc-6fc9-77a2-cccd-e25f52565cf9.png)

1. 画面が表示されました。
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/971e7e4a-1620-ba5f-53c5-48f6a772da9c.png)

### Developer Planを選択
:::note warn
DeveloperでSign upしてもデフォルトでは、TeamのPlanになるようです。
:::

1. 画面右上の歯車→Account Settingsをクリックします。
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/dfde3cc6-f0ce-1a1e-122f-26e68b92fe6b.png)

1. ナビゲーションペインで「Billing」をクリックします。
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/920cc3fc-7572-8a61-acb0-0dc6afda2566.png)

1. なんと、Teamで登録されています。このままでは、課金されるので、「Developer」に切り替えます。(Developerを選択します。)
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/1084b197-f673-2966-47ee-73684bd3f5d5.png)

1. 「Developer」で「Select Plan」をクリックします。
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/7dce763b-e3a4-7574-0c11-e5ccd9ec32bd.png)

1. ポップアップが表示されるので、「Switch to Developer」をクリックします。
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/0c4847e2-43a0-05d1-9818-cd7a0f7518fa.png)

1. Developerで「Currently Enrolled」になり、Teamが「Select Plan」になりました。 
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/8c0d58e0-bfb7-ef7f-794b-c1f37ac09890.png)

## 【AWS】Redshift環境を作成する。
[こちら](https://github.com/aws-samples/aws-modernization-with-dbtlabs/blob/main/resources/cloudformation/create-dbtworkshop-infr)のCloudFormationを実行して環境を作成します。

### CloudFormationを実行

1. dbtのCloundFormationには、RedshiftのCredentialがベタ書きされているので、変更したものをアップロードします。
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/e4ee013a-ba49-f1a4-ca9b-e8909be8140f.png)


1. 何も変更せずに、「次へ」をクリックします。
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/804e6ebe-268b-56e3-af36-2eca66877148.png)

1. 最後に、「送信」をクリックします。
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/99094d29-3161-1d90-8139-2b8fefe07c73.png)

1. 5分程度で作成完了しました。
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/a7b26e85-1de8-f8ad-8718-b7dbddfaae4b.png)



## 【dbt】 dbtクラウドからRedshiftに接続
dbtクラウドでからRedshiftに接続します。

1. Choose a connectionで「Redshift」を選択し、「Next」をクリックします。
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/f63c043a-c354-70a3-2d64-353861d5b9dc.png)

1. 下記を入力し、「Test Connection」をクリックします。
    - Settings
        - Hostname:Redshiftのエンドポイント
        - Port：Redshiftのポート
        - Database：Redshiftのデータベース
    - Development Credentials
        - Username:Redshiftで作成したUser名
        - Password:Redshiftで作成したパスワード
        - Schema:dbtで作成したデフォルト
        - Target Name: default
        - Thread: 4
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/470586ac-0a2a-7313-56f4-45759591e390.png)
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/c02cf765-328d-2762-dd5b-cfb3fed55f1f.png)

1. COMPLETEになりました。
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/597fff29-a32c-2574-e569-c4fc937eba23.png)

## 【AWS】データの準備
### S3にデータをアップロード
Redshift で使用するファイルをアップロードします。CloudFormationで作成されたS3バケットにファイルアップロードをします。

1. 下記のファイルをダウンロードします。（このファイルは、[dbtチュートリアルページ](https://docs.getdbt.com/quickstarts/redshift?step=3)から取得できます。）
    - [jaffle_shop_customers.csv](https://dbt-tutorial-public.s3-us-west-2.amazonaws.com/jaffle_shop_customers.csv)
    - [jaffle_shop_orders.csv](https://dbt-tutorial-public.s3-us-west-2.amazonaws.com/jaffle_shop_orders.csv)
    - [stripe_payments.csv](https://dbt-tutorial-public.s3-us-west-2.amazonaws.com/stripe_payments.csv)

1. S3バケットで「アップロードを」クリックします。
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/6748a6ea-0cef-8c2c-7a2e-e562e3eea7a2.png)

1. ファイルを指定して「アップロード」をクリックします。
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/3d6017fe-e245-1986-2338-2232fa4e61b6.png)

### Redshift でオブジェクトを作成
1. Redshiftクラスタで「クエリデータ＞クエリエディタv2でクエリ」をクリックします。
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/f11ee90e-e166-6c78-c926-35a2fca5c992.png)


1. 下記を入力し、「Create Connection」をクリックします。
    - Database user name and password
        - User name
        - Password
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/6a77dceb-2d0a-8128-9cf7-15bafe1fc37a.png)

1. 下記のSQLを実行し、スキーマを作成します。
    ```
    create schema if not exists jaffle_shop;
    create schema if not exists stripe;
    ```
1. 下記のSQLを実行し、テーブルを作成します。
    ```
    create table jaffle_shop.customers(
        id integer,
        first_name varchar(50),
        last_name varchar(50)
    );
    
    create table jaffle_shop.orders(
        id integer,
        user_id integer,
        order_date date,
        status varchar(50),
        _etl_loaded_at timestamp default current_timestamp
    );
    
    create table stripe.payment(
        id integer,
        orderid integer,
        paymentmethod varchar(50),
        status varchar(50),
        amount integer,
        created date,
        _batched_at timestamp default current_timestamp
    );
    
    ```

### S3のデータをRedshiftにロード（コピー）
1. 下記のSQLを実行し、Redshiftのテーブルにコピーします。
    ```
    copy jaffle_shop.customers( id, first_name, last_name)
    from 's3://dbt-data-lake-xxxx/jaffle_shop_customers.csv'
    iam_role 'arn:aws:iam::XXXXXXXXXX:role/RoleName'
    region 'ap-northeast-1'
    delimiter ','
    ignoreheader 1
    acceptinvchars;
       
    copy jaffle_shop.orders(id, user_id, order_date, status)
    from 's3://dbt-data-lake-xxxx/jaffle_shop_orders.csv'
    iam_role 'arn:aws:iam::XXXXXXXXXX:role/RoleName'
    region 'ap-northeast-1'
    delimiter ','
    ignoreheader 1
    acceptinvchars;
    
    copy stripe.payment(id, orderid, paymentmethod, status, amount, created)
    from 's3://dbt-data-lake-xxxx/stripe_payments.csv'
    iam_role 'arn:aws:iam::XXXXXXXXXX:role/RoleName'
    region 'ap-northeast-1'
    delimiter ','
    ignoreheader 1
    Acceptinvchars;
    ```
1. 下記のSQLを実行し、データを確認します。登録されています。
    ```
    select * from jaffle_shop.customers;
    select * from jaffle_shop.orders;
    select * from stripe.payment;
    ```

    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/a23f15d3-68d1-8101-9014-e38471df0e9d.png)
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/a862cbd0-90ae-8d9b-9376-6216ebb1c373.png)
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/2bc4d8fa-ed01-9070-0a29-e7d3eb463ec6.png)

## 【dbt】セットアップと疎通
### リポジトリをセットアップ
dbt cloudでは、リポジトリを利用してコードをバージョン管理できます。

1. 「Managed」を選択し、「Create」をクリックします。
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/85fc3d79-ae57-87cd-f0cf-2ac67b06e66e.png)

1. 作成されました。
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/0a7840c8-5228-8161-bdfe-f81ff9841624.png)

### dbtプロジェクトの初期化
dbtリポジトリを初期化し、ファイルを追加します。
1. 「Start developing in the IDE」をクリックします。
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/a74107c5-d3b9-62d2-8b46-c84fed810363.png)

1. 「initialize dbt project」をクリックします。
1. 作成されました。
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/2687799a-307a-4c12-6cec-39fbaf4533c7.png)

1. 「Commit and sync」をクリックします。
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/9650f9f7-33f7-3d33-7a49-dc212589f2f8.png)

1. メッセージを入力し、「Commit Changes」をクリックします。
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/dba502f8-926e-1376-46e7-3d6cf4567187.png)

1. 「Create File」をクリックします。
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/16df9e8d-d393-19de-fd26-13860e306051.png)

1. ファイル名を入力し、「Create」をクリックします。
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/8eff3335-e966-26a3-894c-f5946d4bd4ad.png)

1. 下記のコードを入力し、「Save As」をクリックします。
    ```
    select * from jaffle_shop.customers
    ```
1. Enterで実行します。Redshiftに登録したデータが参照できました。
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/7eeac1de-9b6a-3194-f871-36fefaf4aa0e.png)


# 考察
今回は、dbtでアカウントを作成し、dbtからRedshiftへの接続を試してみました。次回以降は、複数のELTを作成しワークフローを構成してみたいと思います。

# 参考
https://www.getdbt.com/

https://docs.getdbt.com/

https://docs.getdbt.com/quickstarts/redshift?step=1