Databricksとdbt Cloudの連携(実践編その1)

[Databricksとdbt Cloudの連携](https://qiita.com/taka_yayoi/items/efeb2f3b4a46abba7b5a)の[ステップ3: モデルを作成して実行する](https://qiita.com/taka_yayoi/items/efeb2f3b4a46abba7b5a#%E3%82%B9%E3%83%86%E3%83%83%E3%83%973-%E3%83%A2%E3%83%87%E3%83%AB%E3%82%92%E4%BD%9C%E6%88%90%E3%81%97%E3%81%A6%E5%AE%9F%E8%A1%8C%E3%81%99%E3%82%8B)までを実践した内容です。

# クラスターの準備

ここでは、Databricksクラスターに接続します。

1. 事前にパーソナルアクセストークンを取得しておきます。サイドメニューの**設定 > ユーザー設定 > アクセストークン**に移動し、**新規トークンの作成**をクリックして、トークンの名前、存続期間を指定します。**作成**をクリックするとトークンが表示されるのでコピーしておきます。
![Screen Shot 2022-02-27 at 8.38.26.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/86988492-c6be-9018-1eeb-7af4df1573f2.png)

    **注意** パーソナルアクセストークンは厳重に管理してください。第三者に教えたりしないでください。

1. 任意のスペックのクラスターを作成します。dbtからこのクラスターにアクセスすることになります。**高度なオプションのJDBC/ODBC**タブに表示される**サーバーのホスト名**、**ポート**、**HTTPパス**をメモしておきます。
![Screen Shot 2022-02-27 at 8.37.49.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/a911dfbc-04e9-f9ae-37f2-3ff77e5d6260.png)

# データベースとテーブルの準備

dbt Cloudで操作するデータベースとテーブルを準備しておきます。今回は自分用のデータベース`dbt_taka`で作業するので、このデータベースとソースデータを格納するテーブルを作成します。

1. 上で準備したクラスターにSQLノートブックをアタッチします。
1. 以下のSQL文を実行してデータベースとテーブルを作成します。

    ```sql:SQL
    CREATE SCHEMA taka_dbt;
    ```

    ```sql:SQL
    USE taka_dbt;
    
    DROP TABLE IF EXISTS diamonds;
    
    CREATE TABLE diamonds USING CSV OPTIONS (path "/databricks-datasets/Rdatasets/data-001/csv/ggplot2/diamonds.csv", header "true")
    ```


![Screen Shot 2022-02-27 at 8.50.07.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/131632c5-30d4-7c90-448d-df5cecb31355.png)


# dbt Cloudの設定

1. [dbt Cloud \- Signup](https://www.getdbt.com/signup/)にアクセスして、アカウント情報を入力します。
![Screen Shot 2022-02-26 at 16.07.32.JPG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/222122bc-5504-d2eb-6db1-0f3da7fd69f9.jpeg)
1. 以下のような画面が表示され、指定したアドレスに確認メールが届きます。
![Screen Shot 2022-02-26 at 16.08.32.JPG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/0d139053-a6fa-a4c3-b4de-66c3ed075769.jpeg)
![Screen Shot 2022-02-26 at 16.10.13.JPG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/14b3ee25-de82-25bd-5288-3825402fcf11.jpeg)
1. メールに記載の**Verify email address**をクリックします。
1. プロジェクトのセットアップを行います。
![Screen Shot 2022-02-26 at 16.10.55.JPG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/1403bc0b-31e9-3095-b5b9-8c6884e6c132.jpeg)
1. Database ConnectionではDatabricksを選択します。
![Screen Shot 2022-02-26 at 16.11.08.JPG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/da7bc921-76db-edae-d1e3-a5e73d1ca894.jpeg)
1. **NAME**には接続設定情報の名称、**HOSTNAME**には、[クラスターの準備](#クラスターの準備)でコピーした**サーバーのホスト名**、**PORT**には**ポート**、**CLUSTER**には**HTTPパス**の最後の`/`以降の文字列を指定します。**USER**には`token`を入力し、**TOKEN**には[クラスターの準備](#クラスターの準備)でコピーしたパーソナルアクセストークンを指定し、**SCHEMA**にはDatabricks上のデータベース名を指定します。ここでは`taka_dbt`としています。
![Screen Shot 2022-02-26 at 21.16.41.JPG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/e4ffa450-4259-c6ec-8402-3efd819a783a.jpeg)
![Screen Shot 2022-02-26 at 21.16.55.JPG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/975cf87d-bfed-bfd4-3027-a08cc78e5436.jpeg)
1. 上にある**Test Connection**ボタンを押して接続確認を行います。接続に成功したら**Continue**をクリックします。
![Screen Shot 2022-02-26 at 21.19.20.JPG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/23cfc87f-3716-4ab5-3dbc-e41b08ca1ec0.jpeg)
1. モデルなどを格納するリポジトリを設定します。ここでは**Managed**を選択してリポジトリの名称を入力し**Create**をクリックします。
![Screen Shot 2022-02-26 at 21.19.43.JPG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/a7b9444c-7402-da10-0ca7-18966dd6f46e.jpeg)
1. 作成に成功したら**Continue**をクリックします。
1. 今回は特にユーザーを招待しないので**Skip & Complete**をクリックします。
![Screen Shot 2022-02-26 at 21.20.18.JPG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/268192f6-aeb6-927a-308d-26e370009ef7.jpeg)
1. これでプロジェクトが作成できたので、**Start Developing**をクリックしてIDEに移動します。
![Screen Shot 2022-02-26 at 21.20.28.JPG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/11d49f62-b15f-796b-d95c-9636f27cb2ca.jpeg)
![Screen Shot 2022-02-26 at 21.21.06.JPG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/fb9a70f7-ad9c-d946-8ba4-52007453aeee.jpeg)
1. 左上にある**initialize your project**をクリックして、プロジェクトの初期化を行います。完了すると必要なフォルダーなどが生成されます。
![Screen Shot 2022-02-26 at 21.21.52.JPG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/d839886c-1b27-c5c3-1e44-ccfec45a5800.jpeg)

# モデルの定義

以降は[こちら](https://qiita.com/taka_yayoi/items/efeb2f3b4a46abba7b5a#%E3%82%B9%E3%83%86%E3%83%83%E3%83%973-%E3%83%A2%E3%83%87%E3%83%AB%E3%82%92%E4%BD%9C%E6%88%90%E3%81%97%E3%81%A6%E5%AE%9F%E8%A1%8C%E3%81%99%E3%82%8B)で説明されているようにモデルを作成していきます。データベースを独自のものにしていますので、若干SQL文を変更しています。

```sql:models/diamonds_four_cs.sql
{{ config(
 materialized='table',
 file_format='delta'
) }}
    
select carat, cut, color, clarity
from taka_dbt.diamonds
```

```sql:models/diamonds_list_colors.sql
select distinct color
from taka_dbt.diamonds_four_cs
order by color asc
```

```sql:models/diamonds_prices.sql
select color, avg(price) as price
from taka_dbt.diamonds
group by color
order by price desc
```

# モデルの実行

画面下の**Runs**の右のテキストボックスに以下のコマンドを入力し、**Enter**をクリックして実行します。

```bash:Bash
dbt run --model models/diamonds_four_cs.sql models/diamonds_list_colors.sql models/diamonds_prices.sql
```

![Screen Shot 2022-02-26 at 21.27.59.JPG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/d20ae83f-96af-c3fc-38e4-916166d21510.jpeg)

処理が成功すれば**RUN STATUS**がPassedとなります。
![Screen Shot 2022-02-27 at 9.08.19.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/828d982d-eedf-3558-d2bb-d31b18fc3c15.png)

# 処理結果の確認

Databricksのノートブック上でSQLを実行することで、テーブル一覧、テーブルの中身を確認することができます。

```sql:SQL
SHOW TABLES;
```

![Screen Shot 2022-02-27 at 9.11.20.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/25d2b634-dbe5-d1be-f2ff-9dabfaf58e32.png)

```sql:SQL
SELECT * FROM diamonds_four_cs;
```

![Screen Shot 2022-02-27 at 9.12.11.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/580d5f75-89ad-b6a8-fb0d-9558c4f5a284.png)

```sql:SQL
SELECT * FROM diamonds_list_colors;
```

![Screen Shot 2022-02-27 at 9.12.19.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/b2de8c38-b44b-8d6f-79d4-a8aa8e42dad3.png)

```sql:SQL
SELECT * FROM diamonds_prices;
```

![Screen Shot 2022-02-27 at 9.12.28.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/b322f0d2-bb32-75e7-ec31-1fc20c9a7a6e.png)

### Databricks 無料トライアル

[Databricks 無料トライアル](https://databricks.com/jp/try-databricks)