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

[Databricksとdbt Cloudの連携](https://qiita.com/taka_yayoi/items/efeb2f3b4a46abba7b5a)の[ステップ4: より複雑なモデルを作成して実行する](https://qiita.com/taka_yayoi/items/efeb2f3b4a46abba7b5a#%E3%82%B9%E3%83%86%E3%83%83%E3%83%974-%E3%82%88%E3%82%8A%E8%A4%87%E9%9B%91%E3%81%AA%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)と[ステップ5: テストを作成して実行する](https://qiita.com/taka_yayoi/items/efeb2f3b4a46abba7b5a#%E3%82%B9%E3%83%86%E3%83%83%E3%83%975-%E3%83%86%E3%82%B9%E3%83%88%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)を実践した内容です。ステップ3以前の内容については[Databricksとdbt Cloudの連携\(実践編その1\)](https://qiita.com/taka_yayoi/items/9995400e17dad2dae4fd)をご覧ください。

# テーブルの作成

ここではデータベース`taka_dbt`に以下のテーブルを作成します。

```sql:SQL
DROP TABLE IF EXISTS zzz_game_opponents;
DROP TABLE IF EXISTS zzz_game_scores;
DROP TABLE IF EXISTS zzz_games;
DROP TABLE IF EXISTS zzz_teams;

CREATE TABLE zzz_game_opponents (
game_id INT,
home_team_id INT,
visitor_team_id INT
) USING DELTA;

INSERT INTO zzz_game_opponents VALUES (1, 1, 2);
INSERT INTO zzz_game_opponents VALUES (2, 1, 3);
INSERT INTO zzz_game_opponents VALUES (3, 2, 1);
INSERT INTO zzz_game_opponents VALUES (4, 2, 3);
INSERT INTO zzz_game_opponents VALUES (5, 3, 1);
INSERT INTO zzz_game_opponents VALUES (6, 3, 2);

CREATE TABLE zzz_game_scores (
game_id INT,
home_team_score INT,
visitor_team_score INT
) USING DELTA;

INSERT INTO zzz_game_scores VALUES (1, 4, 2);
INSERT INTO zzz_game_scores VALUES (2, 0, 1);
INSERT INTO zzz_game_scores VALUES (3, 1, 2);
INSERT INTO zzz_game_scores VALUES (4, 3, 2);
INSERT INTO zzz_game_scores VALUES (5, 3, 0);
INSERT INTO zzz_game_scores VALUES (6, 3, 1);

CREATE TABLE zzz_games (
game_id INT,
game_date DATE
) USING DELTA;

INSERT INTO zzz_games VALUES (1, '2020-12-12');
INSERT INTO zzz_games VALUES (2, '2021-01-09');
INSERT INTO zzz_games VALUES (3, '2020-12-19');
INSERT INTO zzz_games VALUES (4, '2021-01-16');
INSERT INTO zzz_games VALUES (5, '2021-01-23');
INSERT INTO zzz_games VALUES (6, '2021-02-06');

CREATE TABLE zzz_teams (
team_id INT,
team_city VARCHAR(15)
) USING DELTA;

INSERT INTO zzz_teams VALUES (1, "San Francisco");
INSERT INTO zzz_teams VALUES (2, "Seattle");
INSERT INTO zzz_teams VALUES (3, "Amsterdam");
```

上のSQLを実行すると、このように新たにテーブルが作成されます。
![Screen Shot 2022-02-28 at 7.30.37.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/c0e7ae30-6402-854a-01f2-ee53d3b955d5.png)


# モデルの定義

[こちら](https://qiita.com/taka_yayoi/items/efeb2f3b4a46abba7b5a#%E3%82%B9%E3%83%86%E3%83%83%E3%83%974-%E3%82%88%E3%82%8A%E8%A4%87%E9%9B%91%E3%81%AA%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/zzz_game_details.sql
{{ config(
  materialized='table',
  file_format='delta'
) }}

-- Step 4 of 4: Replace the visitor team IDs with their city names.
select
  game_id,
  home,
  t.team_city as visitor,
  home_score,
  visitor_score,
  -- Step 3 of 4: Display the city name for each game's winner.
  case
    when
      home_score > visitor_score
        then
          home
    when
      visitor_score > home_score
        then
          t.team_city
  end as winner,
  game_date as date
from (
  -- Step 2 of 4: Replace the home team IDs with their actual city names.
  select
    game_id,
    t.team_city as home,
    home_score,
    visitor_team_id,
    visitor_score,
    game_date
  from (
    -- Step 1 of 4: Combine data from various tables (for example, game and team IDs, scores, dates).
    select
      g.game_id,
      go.home_team_id,
      gs.home_team_score as home_score,
      go.visitor_team_id,
      gs.visitor_team_score as visitor_score,
      g.game_date
    from
      taka_dbt.zzz_games as g,
      taka_dbt.zzz_game_opponents as go,
      taka_dbt.zzz_game_scores as gs
    where
      g.game_id = go.game_id and
      g.game_id = gs.game_id
  ) as all_ids,
    taka_dbt.zzz_teams as t
  where
    all_ids.home_team_id = t.team_id
) as visitor_ids,
  taka_dbt.zzz_teams as t
where
  visitor_ids.visitor_team_id = t.team_id
order by game_date desc
```

```sql:models/zzz_win_loss_records.sql
{{ config(
  database="taka_dbt"
) }}

-- Create a view that summarizes the season's win and loss records by team.

-- Step 2 of 2: Calculate the number of wins and losses for each team.
select
  winner as team,
  count(winner) as wins,
  -- Each team played in 4 games.
  (4 - count(winner)) as losses
from (
  -- Step 1 of 2: Determine the winner and loser for each game.
  select
    game_id,
    winner,
    case
      when
        home = winner
          then
            visitor
      else
        home
    end as loser
  from taka_dbt.zzz_game_details
)
group by winner
order by wins desc
```

# モデルの実行

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

```bash:Bash
dbt run --model models/zzz_game_details.sql models/zzz_win_loss_records.sql
```

![Screen Shot 2022-02-28 at 7.29.54.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/a1e0bf12-fdd3-dbf0-7c60-2f114582f7b1.png)

処理が完了するとテーブルが追加されます。

![Screen Shot 2022-02-28 at 7.32.07.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/3443eeb1-e5b5-4e57-7de9-f8cafcc7bbf6.png)

ビューが追加されていることを確認します。
![Screen Shot 2022-02-28 at 7.34.45.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/869c7181-2c59-d42d-8f33-a12597b93add.png)

追加されたテーブルの中身を確認します。
![Screen Shot 2022-02-28 at 7.34.57.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/efaf9d8b-a075-5a35-ca84-bcb89996a49d.png)
![Screen Shot 2022-02-28 at 7.35.06.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/8f70fdf1-bb03-3931-c3e1-24871a858159.png)

# テストの定義

プロジェクトに`models/schema.yml`を追加します。以下の内容を貼り付けます。

```yaml:models/schema.yml
version: 2

models:
  - name: zzz_game_details
    columns:
      - name: game_id
        tests:
          - unique
          - not_null
      - name: home
        tests:
          - not_null
          - accepted_values:
              values: ['Amsterdam', 'San Francisco', 'Seattle']
      - name: visitor
        tests:
          - not_null
          - accepted_values:
              values: ['Amsterdam', 'San Francisco', 'Seattle']
      - name: home_score
        tests:
          - not_null
      - name: visitor_score
        tests:
          - not_null
      - name: winner
        tests:
          - not_null
          - accepted_values:
              values: ['Amsterdam', 'San Francisco', 'Seattle']
      - name: date
        tests:
          - not_null
  - name: zzz_win_loss_records
    columns:
      - name: team
        tests:
          - unique
          - not_null
          - relationships:
              to: ref('zzz_game_details')
              field: home
      - name: wins
        tests:
          - not_null
      - name: losses
        tests:
          - not_null
```

以下のデータテストを記述します。

```sql:tests/zzz_game_details_check_dates.sql
-- This season's games happened between 2020-12-12 and 2021-02-06.
-- For this test to pass, this query must return no results.

select date
from taka_dbt.zzz_game_details
where date < '2020-12-12'
or date > '2021-02-06'
```

```sql:tests/zzz_game_details_check_scores.sql
-- This sport allows no negative scores or tie games.
-- For this test to pass, this query must return no results.

select home_score, visitor_score
from taka_dbt.zzz_game_details
where home_score < 0
or visitor_score < 0
or home_score = visitor_score
```

```sql:tests/zzz_win_loss_records_check_records.sql
-- Each team participated in 4 games this season.
-- For this test to pass, this query must return no results.

select wins, losses
from taka_dbt.zzz_win_loss_records
where wins < 0 or wins > 4
or losses < 0 or losses > 4
or (wins + losses) > 4
```

> **筆者注**
2021/2/28時点で筆者が確認したところ、dbt Cloudの`dbt test`コマンドは`--schema`、`--data`オプションを受け付けませんでした。コマンド`dbt test`のみでスキーマテスト、データテストが実行されることは確認しています。

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

```bash:Bash
dbt test
```
![Screen Shot 2022-02-28 at 8.22.49.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/0fcf54e3-e5df-32c2-c3c1-6306d4d517b5.png)

### Databricks 無料トライアル

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