Tutorial: Membuat, menjalankan, dan menguji model dbt secara lokal

Tutorial ini memandu Anda melalui cara membuat, menjalankan, dan menguji model dbt secara lokal. Anda juga dapat menjalankan proyek dbt sebagai tugas pekerjaan Azure Databricks. Untuk informasi lebih lanjut, lihat Gunakan transformasi dbt di Lakeflow Jobs.

Sebelum Anda memulai

Untuk mengikuti tutorial ini, Anda harus terlebih dahulu menyambungkan ruang kerja Azure Databricks Anda ke dbt Core. Untuk informasi selengkapnya, lihat Menyambungkan ke dbt Core.

Langkah 1: Membuat dan menjalankan model

Dalam langkah ini, Anda menggunakan editor teks favorit Anda untuk membuat model , yang merupakan pernyataan select yang membuat tampilan baru (default) atau tabel baru dalam database, berdasarkan data yang ada dalam database yang sama. Prosedur ini membuat model berdasarkan contoh tabel diamonds dari himpunan data sampel .

Gunakan kode berikut untuk membuat tabel ini.

DROP TABLE IF EXISTS diamonds;

CREATE TABLE diamonds USING CSV OPTIONS (path "/databricks-datasets/Rdatasets/data-001/csv/ggplot2/diamonds.csv", header "true")

  1. Di direktori proyek models , buat file bernama diamonds_four_cs.sql dengan pernyataan SQL berikut. Pernyataan ini hanya memilih detail karat, potong, warna, dan kejelasan untuk setiap berlian dari tabel diamonds. Blok config menginstruksikan dbt untuk membuat tabel dalam database berdasarkan pernyataan ini.

    {{ config(
  materialized='table',
  file_format='delta'
) }}

    select carat, cut, color, clarity
from diamonds

    Petunjuk / Saran

    Untuk opsi tambahan config seperti menggunakan format file Delta dan merge strategi tambahan, lihat Konfigurasi Databricks dalam dokumentasi dbt.

  2. Di direktori proyek models , buat file kedua bernama diamonds_list_colors.sql dengan pernyataan SQL berikut. Pernyataan ini memilih nilai unik dari kolom colors dalam tabel diamonds_four_cs, mengurutkan hasil dalam urutan alfabet terlebih dahulu hingga terakhir. Karena tidak ada config blok, model ini menginstruksikan dbt untuk membuat tampilan dalam database berdasarkan pernyataan ini.

    select distinct color
from {{ ref('diamonds_four_cs') }}
sort by color asc

  3. Di direktori proyek models , buat file ketiga bernama diamonds_prices.sql dengan pernyataan SQL berikut. Pernyataan ini rata-rata harga berlian berdasarkan warna, menyortir hasil dengan harga rata-rata dari tertinggi ke terendah. Model ini menginstruksikan dbt untuk membuat tampilan dalam database berdasarkan pernyataan ini.

    select color, avg(price) as price
from diamonds
group by color
order by price desc

  4. Dengan lingkungan virtual diaktifkan, jalankan perintah dbt run dengan jalur ke tiga file sebelumnya. Dalam database default (seperti yang ditentukan dalam file profiles.yml), dbt membuat satu tabel bernama diamonds_four_cs dan dua tampilan bernama diamonds_list_colors dan diamonds_prices. dbt mendapatkan nama tampilan dan tabel ini dari nama file .sql terkait.

    dbt run --model models/diamonds_four_cs.sql models/diamonds_list_colors.sql models/diamonds_prices.sql

    ...
... | 1 of 3 START table model default.diamonds_four_cs.................... [RUN]
... | 1 of 3 OK created table model default.diamonds_four_cs............... [OK ...]
... | 2 of 3 START view model default.diamonds_list_colors................. [RUN]
... | 2 of 3 OK created view model default.diamonds_list_colors............ [OK ...]
... | 3 of 3 START view model default.diamonds_prices...................... [RUN]
... | 3 of 3 OK created view model default.diamonds_prices................. [OK ...]
... |
... | Finished running 1 table model, 2 view models ...

Completed successfully

Done. PASS=3 WARN=0 ERROR=0 SKIP=0 TOTAL=3

  5. Jalankan kode SQL berikut untuk mencantumkan informasi tentang tampilan baru dan untuk memilih semua baris dari tabel dan tampilan.

    Jika Anda tersambung ke kluster, Anda dapat menjalankan kode SQL ini dari buku catatan yang tersambung ke kluster, dengan menetapkan SQL sebagai bahasa default untuk buku catatan. Jika Anda menyambungkan ke gudang SQL, Anda dapat menjalankan kode SQL ini dari kueri.

    SHOW views IN default;

    +-----------+----------------------+-------------+
| namespace | viewName             | isTemporary |
+===========+======================+=============+
| default   | diamonds_list_colors | false       |
+-----------+----------------------+-------------+
| default   | diamonds_prices      | false       |
+-----------+----------------------+-------------+

    SELECT * FROM diamonds_four_cs;

    +-------+---------+-------+---------+
| carat | cut     | color | clarity |
+=======+=========+=======+=========+
| 0.23  | Ideal   | E     | SI2     |
+-------+---------+-------+---------+
| 0.21  | Premium | E     | SI1     |
+-------+---------+-------+---------+
...

    SELECT * FROM diamonds_list_colors;

    +-------+
| color |
+=======+
| D     |
+-------+
| E     |
+-------+
...

    SELECT * FROM diamonds_prices;

    +-------+---------+
| color | price   |
+=======+=========+
| J     | 5323.82 |
+-------+---------+
| I     | 5091.87 |
+-------+---------+
...

Langkah 2: Membuat dan menjalankan model yang lebih kompleks

Dalam langkah ini, Anda membuat model yang lebih kompleks untuk sekumpulan tabel data terkait. Tabel data ini berisi informasi tentang liga olahraga fiktif dari tiga tim yang memainkan musim enam pertandingan. Prosedur ini membuat tabel data, membuat model, dan menjalankan model.

  1. Jalankan kode SQL berikut untuk membuat tabel data yang diperlukan.

    Jika Anda tersambung ke kluster, Anda dapat menjalankan kode SQL ini dari buku catatan yang tersambung ke kluster, dengan menetapkan SQL sebagai bahasa default untuk buku catatan. Jika Anda menyambungkan ke gudang SQL, Anda dapat menjalankan kode SQL ini dari kueri.

    Tabel dan tampilan dalam langkah ini dimulai dengan zzz_ untuk membantu mengidentifikasinya sebagai bagian dari contoh ini. Anda tidak perlu mengikuti pola ini untuk tabel dan tampilan Anda sendiri.

    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);

-- Result:
-- +---------+--------------+-----------------+
-- | game_id | home_team_id | visitor_team_id |
-- +=========+==============+=================+
-- | 1       | 1            | 2               |
-- +---------+--------------+-----------------+
-- | 2       | 1            | 3               |
-- +---------+--------------+-----------------+
-- | 3       | 2            | 1               |
-- +---------+--------------+-----------------+
-- | 4       | 2            | 3               |
-- +---------+--------------+-----------------+
-- | 5       | 3            | 1               |
-- +---------+--------------+-----------------+
-- | 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);

-- Result:
-- +---------+-----------------+--------------------+
-- | game_id | home_team_score | visitor_team_score |
-- +=========+=================+====================+
-- | 1       | 4               | 2                  |
-- +---------+-----------------+--------------------+
-- | 2       | 0               | 1                  |
-- +---------+-----------------+--------------------+
-- | 3       | 1               | 2                  |
-- +---------+-----------------+--------------------+
-- | 4       | 3               | 2                  |
-- +---------+-----------------+--------------------+
-- | 5       | 3               | 0                  |
-- +---------+-----------------+--------------------+
-- | 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');

-- Result:
-- +---------+------------+
-- | game_id | game_date  |
-- +=========+============+
-- | 1       | 2020-12-12 |
-- +---------+------------+
-- | 2       | 2021-01-09 |
-- +---------+------------+
-- | 3       | 2020-12-19 |
-- +---------+------------+
-- | 4       | 2021-01-16 |
-- +---------+------------+
-- | 5       | 2021-01-23 |
-- +---------+------------+
-- | 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");

-- Result:
-- +---------+---------------+
-- | team_id | team_city     |
-- +=========+===============+
-- | 1       | San Francisco |
-- +---------+---------------+
-- | 2       | Seattle       |
-- +---------+---------------+
-- | 3       | Amsterdam     |
-- +---------+---------------+

  2. Di direktori proyek models , buat file bernama zzz_game_details.sql dengan pernyataan SQL berikut. Pernyataan ini membuat tabel yang menyediakan detail setiap game, seperti nama dan skor tim. Blok config menginstruksikan dbt untuk membuat tabel dalam database berdasarkan pernyataan ini.

    -- Create a table that provides full details for each game, including
-- the game ID, the home and visiting teams' city names and scores,
-- the game winner's city name, and the game date.

    {{ 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
      zzz_games as g,
      zzz_game_opponents as go,
      zzz_game_scores as gs
    where
      g.game_id = go.game_id and
      g.game_id = gs.game_id
  ) as all_ids,
    zzz_teams as t
  where
    all_ids.home_team_id = t.team_id
) as visitor_ids,
  zzz_teams as t
where
  visitor_ids.visitor_team_id = t.team_id
order by game_date desc

  3. Di direktori proyek models , buat file bernama zzz_win_loss_records.sql dengan pernyataan SQL berikut. Pernyataan ini membuat tampilan yang mencantumkan catatan menang-kalah tim untuk musim ini.

    -- 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 {{ ref('zzz_game_details') }}
)
group by winner
order by wins desc

  4. Dengan lingkungan virtual diaktifkan, jalankan perintah dbt run dengan jalur menuju dua file sebelumnya. Dalam database default (seperti yang ditentukan dalam file profiles.yml), dbt membuat satu tabel bernama zzz_game_details dan satu tampilan bernama zzz_win_loss_records. dbt mendapatkan nama tampilan dan tabel ini dari nama file .sql terkait.

    dbt run --model models/zzz_game_details.sql models/zzz_win_loss_records.sql

    ...
... | 1 of 2 START table model default.zzz_game_details.................... [RUN]
... | 1 of 2 OK created table model default.zzz_game_details............... [OK ...]
... | 2 of 2 START view model default.zzz_win_loss_records................. [RUN]
... | 2 of 2 OK created view model default.zzz_win_loss_records............ [OK ...]
... |
... | Finished running 1 table model, 1 view model ...

Completed successfully

Done. PASS=2 WARN=0 ERROR=0 SKIP=0 TOTAL=2

  5. Jalankan kode SQL berikut untuk mencantumkan informasi tentang tampilan baru dan untuk memilih semua baris dari tabel dan tampilan.

    Jika Anda tersambung ke kluster, Anda dapat menjalankan kode SQL ini dari buku catatan yang tersambung ke kluster, dengan menetapkan SQL sebagai bahasa default untuk buku catatan. Jika Anda menyambungkan ke gudang SQL, Anda dapat menjalankan kode SQL ini dari kueri.

    SHOW VIEWS FROM default LIKE 'zzz_win_loss_records';

    +-----------+----------------------+-------------+
| namespace | viewName             | isTemporary |
+===========+======================+=============+
| default   | zzz_win_loss_records | false       |
+-----------+----------------------+-------------+

    SELECT * FROM zzz_game_details;

    +---------+---------------+---------------+------------+---------------+---------------+------------+
| game_id | home          | visitor       | home_score | visitor_score | winner        | date       |
+=========+===============+===============+============+===============+===============+============+
| 1       | San Francisco | Seattle       | 4          | 2             | San Francisco | 2020-12-12 |
+---------+---------------+---------------+------------+---------------+---------------+------------+
| 2       | San Francisco | Amsterdam     | 0          | 1             | Amsterdam     | 2021-01-09 |
+---------+---------------+---------------+------------+---------------+---------------+------------+
| 3       | Seattle       | San Francisco | 1          | 2             | San Francisco | 2020-12-19 |
+---------+---------------+---------------+------------+---------------+---------------+------------+
| 4       | Seattle       | Amsterdam     | 3          | 2             | Seattle       | 2021-01-16 |
+---------+---------------+---------------+------------+---------------+---------------+------------+
| 5       | Amsterdam     | San Francisco | 3          | 0             | Amsterdam     | 2021-01-23 |
+---------+---------------+---------------+------------+---------------+---------------+------------+
| 6       | Amsterdam     | Seattle       | 3          | 1             | Amsterdam     | 2021-02-06 |
+---------+---------------+---------------+------------+---------------+---------------+------------+

    SELECT * FROM zzz_win_loss_records;

    +---------------+------+--------+
| team          | wins | losses |
+===============+======+========+
| Amsterdam     | 3    | 1      |
+---------------+------+--------+
| San Francisco | 2    | 2      |
+---------------+------+--------+
| Seattle       | 1    | 3      |
+---------------+------+--------+

Langkah 3: Membuat dan menjalankan pengujian

Pada langkah ini, Anda membuat pengujian, yang merupakan pernyataan yang Anda buat tentang model Anda. Saat Anda menjalankan pengujian ini, dbt memberi tahu Anda apakah setiap pengujian dalam proyek Anda lulus atau gagal.

Terdapat dua jenis pengujian. pengujian Skema, diterapkan di YAML, mengembalikan jumlah rekaman yang tidak lulus pernyataan. Ketika angka ini nol, semua catatan berhasil, maka pengujian pun berhasil. Pengujian data adalah kueri khusus yang harus mengembalikan nol catatan untuk lulus.

  1. Di direktori proyek models , buat file bernama schema.yml dengan konten berikut. File ini mencakup pengujian skema yang menentukan apakah kolom yang ditentukan memiliki nilai unik, bukan null, hanya memiliki nilai yang ditentukan, atau kombinasi.

    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

  2. Di direktori proyek tests , buat file bernama zzz_game_details_check_dates.sql dengan pernyataan SQL berikut. File ini mencakup pengujian data untuk menentukan apakah ada pertandingan yang terjadi di luar musim reguler.

    -- 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 {{ ref('zzz_game_details') }}
where date < '2020-12-12'
or date > '2021-02-06'

  3. Di direktori proyek tests , buat file bernama zzz_game_details_check_scores.sql dengan pernyataan SQL berikut. File ini mencakup pengujian data untuk menentukan apakah ada skor negatif atau permainan apa pun yang terikat.

    -- 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 {{ ref('zzz_game_details') }}
where home_score < 0
or visitor_score < 0
or home_score = visitor_score

  4. Di direktori proyek tests , buat file bernama zzz_win_loss_records_check_records.sql dengan pernyataan SQL berikut. File ini mencakup pengujian data untuk menentukan apakah ada tim yang memiliki catatan menang atau kalah negatif, memiliki lebih banyak catatan menang atau kalah daripada pertandingan yang dimainkan, atau memainkan lebih banyak pertandingan daripada yang diizinkan.

    -- Each team participated in 4 games this season.
-- For this test to pass, this query must return no results.

select wins, losses
from {{ ref('zzz_win_loss_records') }}
where wins < 0 or wins > 4
or losses < 0 or losses > 4
or (wins + losses) > 4

  5. Dengan lingkungan virtual diaktifkan, jalankan dbt test perintah .

    dbt test --models zzz_game_details zzz_win_loss_records

    ...
... | 1 of 19 START test accepted_values_zzz_game_details_home__Amsterdam__San_Francisco__Seattle [RUN]
... | 1 of 19 PASS accepted_values_zzz_game_details_home__Amsterdam__San_Francisco__Seattle [PASS ...]
...
... |
... | Finished running 19 tests ...

Completed successfully

Done. PASS=19 WARN=0 ERROR=0 SKIP=0 TOTAL=19

Langkah 4: Bersihkan

Anda dapat menghapus tabel dan tampilan yang Anda buat untuk contoh ini dengan menjalankan kode SQL berikut.

Jika Anda tersambung ke kluster, Anda dapat menjalankan kode SQL ini dari buku catatan yang tersambung ke kluster, dengan menetapkan SQL sebagai bahasa default untuk buku catatan. Jika Anda menyambungkan ke gudang SQL, Anda dapat menjalankan kode SQL ini dari kueri.

DROP TABLE zzz_game_opponents;
DROP TABLE zzz_game_scores;
DROP TABLE zzz_games;
DROP TABLE zzz_teams;
DROP TABLE zzz_game_details;
DROP VIEW zzz_win_loss_records;

DROP TABLE diamonds;
DROP TABLE diamonds_four_cs;
DROP VIEW diamonds_list_colors;
DROP VIEW diamonds_prices;

Pemecahan masalah

Untuk informasi tentang masalah umum saat menggunakan dbt Core dengan Azure Databricks dan cara mengatasinya, lihat Mendapatkan bantuan di situs web dbt Labs.

Langkah berikutnya

Jalankan proyek dbt Core sebagai tugas pekerjaan Azure Databricks. Lihat Menggunakan transformasi dbt di Pekerjaan Lakeflow.

Sumber Daya Tambahan:

Jelajahi sumber daya berikut di situs web dbt Labs:

  • Last updated on