Condividi tramite

Connettersi a dbt Cloud

  • Articolo

dbt (data build tool) è un ambiente di sviluppo che consente agli analisti dei dati e ai data engineer di trasformare i dati semplicemente scrivendo istruzioni select. dbt gestisce la trasformazione di queste istruzioni select in tabelle e viste. dbt compila il codice in SQL non elaborato e quindi esegue tale codice nel database specificato in Azure Databricks. dbt supporta modelli di scrittura del codice collaborativi e procedure consigliate, ad esempio il controllo della versione, la documentazione e la modularità.

dbt non estrae o carica i dati. dbt è incentrato solo sul passaggio di trasformazione, usando un'architettura di "trasformazione dopo il caricamento". dbt presuppone che nel database sia già presente una copia dei dati.

L'articolo è incentrato su dbt Cloud. dbt Cloud è dotato di supporto chiavi in mano per la pianificazione di processi, CI/CD, documentazione, monitoraggio e avvisi e un ambiente di sviluppo integrato (IDE).

È disponibile anche una versione locale di dbt denominata dbt Core. dbt Core consente di scrivere codice dbt nell'editor di testo o nell'IDE di propria scelta nel computer di sviluppo locale e quindi eseguire dbt dalla riga di comando. dbt Core include l'interfaccia della riga di comando dbt. L'interfaccia della riga di comando di dbt può essere usata gratuitamente ed è open source. Per ulteriori informazioni, consultare Connettersi a dbtCore.

Poiché dbt Cloud e dbt Core possono usare repository git ospitati (per esempio, su GitHub, GitLab o BitBucket), è possibile usare dbt Cloud per creare un progetto dbt e poi renderlo disponibile agli utenti di dbt Cloud e dbt Core. Per altre informazioni, consultare Creazione di un progetto dbt e Utilizzo di un progetto esistente nel sito Web dbt.

Per una panoramica generale e una dimostrazione di Prophecy, guardare il seguente video di YouTube (26 minuti).

Connettersi a dbt Cloud tramite Partner Connect

Questa sezione descrive come connettere un databricks SQL warehouse a dbt Cloud tramite Partner Connect e poi concedere a dbt Cloud l'accesso in lettura ai dati.

Differenze tra connessioni standard e dbt Cloud

Per connettersi a dbt Cloud tramite Partner Connect, seguire la procedura descritta in Connettersi ai partner di elaborazione dei dati utilizzando Partner Connect. La connessione dbt Cloud è diversa dalla preparazione dei dati standard e dalle connessioni di trasformazione nei seguenti modi:

  • Oltre a un'entità servizio e a un token di accesso personale, Partner Connect crea un archivio SQL (in precedenza endpoint SQL) denominato DBT_CLOUD_ENDPOINT per impostazione predefinita.

Passaggi per la connessione

Per Connettersi a dbt Cloud tramite Partner Connect, seguire le istruzioni riportate qui sotto:

  1. Connettersi ai partner di preparazione dati tramite Partner Connect.

  2. Dopo la connessione a dbt Cloud, viene visualizzata la dashboard dbt Cloud. Per esplorare un progetto dbt Cloud, nella barra dei menu, accanto al logo dbt, selezionare il nome del proprio account dbt dal primo menu a discesa, se non è visualizzato e poi selezionare il progetto Databricks Partner Connect Trial dal secondo menu a discesa, se non è visualizzato.

    Suggerimento

    Per visualizzare le impostazioni del progetto, fare clic sul menu "tre strisce" o "hamburger", fare clic su Impostazioni account > Progetti e poi sul nome del progetto. Per visualizzare le impostazioni di connessione, fare clic sul collegamento accanto a Connessione. Per modificare qualsiasi impostazione, fare clic su Modifica.

    Per visualizzare le informazioni sul token di accesso personale di Azure Databricks per questo progetto, fare clic sull'icona "persona" sulla barra dei menu, fare clic su Profilo > Credenziali > Databricks Partner Connect Trial e poi fare clic sul nome del progetto. Per apportare una modifica, fare clic su Modifica.

Passaggi per concedere a dbt Cloud l'accesso in lettura ai dati

Partner Connect concede l'autorizzazione di sola creazione all'entità servizio di DBT_CLOUD_USER solo nel catalogo predefinito. Seguire questa procedura nell'area di lavoro di Azure Databricks per concedere all'entità servizio di DBT_CLOUD_USER l'accesso in lettura ai dati scelti.

Avviso

È possibile adattare questi passaggi per fornire a dbt Cloud un accesso aggiuntivo ai cataloghi, ai database e alle tabelle dell'area di lavoro. Tuttavia, come procedura consigliata per la sicurezza, Databricks raccomanda vivamente di concedere l'accesso solo alle singole tabelle con cui è necessario che l’entità servizio DBT_CLOUD_USER possa operare e l'accesso in sola lettura a tali tabelle.

  1. Fare clic su Icona catalogo Catalogo nella barra laterale.

  2. Selezionare il Warehouse SQL (DBT_CLOUD_ENDPOINT) nell'elenco a discesa in alto a destra.

    Selezionare il warehouse

    1. In Esplora cataloghi selezionare il catalogo che contiene il database per la tabella.
    2. Selezionare il database che contiene la tabella.
    3. Seleziona la tabella.

    Suggerimento

    Se il catalogo, il database o la tabella non sono elencati, inserire una parte qualsiasi del nome rispettivamente nelle caselle Seleziona catalogo, Seleziona database o Filtra tabelle per restringere l'elenco.

    Filtro tabelle

  3. Fare clic su Autorizzazioni.

  4. Fare clic su Concedi.

  5. Per Digita per aggiungere più utenti o gruppi, selezionare DBT_CLOUD_USER. Si tratta dell'entità servizio di Azure Databricks creata automaticamente da Partner Connect nella sezione precedente.

    Suggerimento

    Se non viene visualizzato DBT_CLOUD_USER, iniziare a digitare DBT_CLOUD_USER nella casella Digita per aggiungere più utenti o gruppi fino a quando non viene visualizzato nell'elenco, poi sarà possibile selezionarlo.

  6. Concedere l'accesso in lettura solo selezionando SELECT e READ METADATA.

  7. Fare clic su OK.

Ripetere i passaggi da 4 a 9 per ogni tabella aggiuntiva a cui si vuole concedere l'accesso in lettura a dbt Cloud.

Risolvere i problemi relativi alla connessione a dbt Cloud

Se qualcuno elimina il progetto in dbt Cloud per questo account e si fa clic sul riquadro dbt, viene visualizzato un messaggio di errore che informa che è impossibile trovare il progetto. Per risolvere il problema, fare clic su Elimina connessione e quindi iniziare dall'inizio di questa procedura per creare di nuovo la connessione.

Connettersi manualmente a dbt Cloud

Questa sezione descrive come connettere un cluster di Azure Databricks o un Warehouse SQL Databricks nell'area di lavoro di Azure Databricks a dbt Cloud.

Importante

Databricks consiglia di connettersi a un'istanza di SQL Warehouse. Se non si ha il diritto di accesso a Databricks SQL o se si vogliono eseguire modelli Python, è invece possibile connettersi a un cluster.

Requisiti

  • Un cluster o SQL Warehouse nell'area di lavoro di Azure Databricks.

  • I dettagli di connessione per il cluster o SQL Warehouse, in particolare i valori Nome host server, Porta e Percorso HTTP.

  • Un token di accesso personale di Azure Databricks o un token Microsoft Entra ID (in precedenza Azure Active Directory). Per creare un token di accesso personale, eseguire le attività seguenti:

    1. Nell'area di lavoro di Azure Databricks, fare clic sul nome utente di Azure Databricks nella barra superiore, quindi selezionare Impostazioni nell'elenco a discesa.
    2. Fare clic su Sviluppatore.
    3. Accanto a Token di accesso fare clic su Gestisci.
    4. Fare clic su Generare nuovi token.
    5. (Facoltativo) Immettere un commento che consente di identificare questo token in futuro e modificare la durata predefinita del token di 90 giorni. Per creare un token senza durata (scelta non consigliata), lasciare vuota la casella Durata (giorni).
    6. Fare clic su Genera.
    7. Copiare il token visualizzato in un percorso sicuro e quindi fare clic su Fine.

    Nota

    Assicurarsi di salvare il token copiato in un percorso sicuro. Non condividere il token copiato con altri utenti. Se si perde il token copiato, non è possibile rigenerare lo stesso esatto token. È invece necessario ripetere questa procedura per creare un nuovo token. Se si perde il token copiato o si ritiene che il token sia stato compromesso, Databricks consiglia vivamente di eliminare immediatamente il token dall'area di lavoro facendo clic sull'icona del cestino (Revoca) accanto al token nella pagina Token di accesso.

    Se non è possibile creare o usare un token di accesso personale nell'area di lavoro, questo potrebbe essere dovuto al fatto che l'amministratore dell'area di lavoro ha disabilitato i token o non ha concesso l'autorizzazione per creare o usare token. Consultare l'amministratore dell'area di lavoro o i seguenti argomenti:

    Nota

    Come procedura consigliata per la sicurezza, quando si esegue l'autenticazione con strumenti automatizzati, sistemi, script e app, Databricks consiglia di usare token di accesso personali appartenenti alle entità servizio, anziché agli utenti dell'area di lavoro. Per creare token per le entità servizio, vedere Gestire i token per un'entità servizio.

  • Per connettere dbt Cloud ai dati gestiti dal catalogo Unity, dbt versione 1.1 o successiva.

    I passaggi descritti in questo articolo creano un nuovo ambiente che utilizza la versione dbt più recente. Per informazioni sull'aggiornamento della versione dbt per un ambiente esistente, consultare Aggiornamento alla versione più recente di dbt in Cloud nella documentazione di dbt.

Passaggio 1: Registrarsi a dbt Cloud

Passare a dbt Cloud - Registrati e immettere le informazioni di posta elettronica, nome e società. Creare una password e fare clic su Crea account personale.

Passaggio 2: Creare un progetto

In questo passaggio si crea un progetto dbt che contiene una connessione a un cluster Azure Databricks o a un warehouse SQL, un repository che contiene il codice sorgente e uno o più ambienti (ad esempio, ambienti di test e di produzione).

  1. Accedere a dbt Cloud.

  2. Fare clic sull'icona delle impostazioni, poi su Impostazioni account.

  3. Fai clic su Nuovo progetto.

  4. In Nome immettere un nome univoco per il progetto e poi fare clic su Continua.

  5. In Scegli una connessione fare clic su Databricks e poi su Avanti.

  6. In Nome immettere un nome univoco per la connessione dati.

  7. Per Seleziona adattatore fare clic su Databricks (dbt-databricks).

    Nota

    Databricks consiglia di usare dbt-databricks, che supporta il catalogo Unity, anziché dbt-spark. Per impostazione predefinita, i nuovi progetti usano dbt-databricks. Per eseguire la migrazione di un progetto esistente a dbt-databricks, consultare Migrazione da dbt-spark a dbt-databricks nella documentazione di dbt.

  8. In Impostazioni, per Nome host server, immettere il valore del nome host del server in base ai requisiti.

  9. Per Percorso HTTP immettere il valore del percorso HTTP in base ai requisiti.

  10. Se l'area di lavoro è abilitata per il catalogo Unity , in Impostazioni facoltative immettere il nome del catalogo per dbt Cloud da usare.

  11. In Credenziali di sviluppo, per Token immettere il token di accesso personale o il token di Microsoft Entra ID a seconda dei requisiti.

  12. Per Schema immettere il nome dello schema in cui si vuole che dbt Cloud crei le tabelle e le viste (ad esempio default).

  13. Fare clic su Verifica connessione.

  14. Se il test ha esito positivo, fare clic su Avanti.

Per altre informazioni, consultare Connessione a Databricks ODBC nel sito Web dbt.

Suggerimento

Per visualizzare o modificare le impostazioni per questo progetto o per eliminare completamente il progetto, fare clic sull'icona delle impostazioni, fare clic su Impostazioni account > Progetti e poi sul nome del progetto. Per modificare le impostazioni, fare clic su Modifica. Per eliminare il progetto, fare clic su Modifica > Elimina progetto.

Per visualizzare o modificare il valore del token di accesso personale di Azure Databricks per questo progetto, fate clic sull'icona “persona”, su Profilo > Credenziali e sul nome del progetto. Per apportare una modifica, fare clic su Modifica.

Dopo la connessione a un cluster Azure Databricks o a un Warehouse SQL databricks , seguire le istruzioni visualizzate in Configura un repository e poi fare clic su Continua.

Dopo aver configurato il repository, seguire le istruzioni visualizzate per invitare gli utenti e poi fare clic su Completa. In alternativa, fare clic su Ignora e completa.

Esercitazione

In questa sezione si utilizza il progetto dbt Cloud per lavorare con alcuni dati di esempio. Questa sezione presuppone che il progetto sia già stato creato e che l'IDE dbt Cloud sia aperto a quel progetto.

Passaggio 1: Creare ed eseguire modelli

In questo passaggio si usa l'IDE dbt Cloud per creare ed eseguire modelli, ovvero istruzioni select che creano una nuova vista (impostazione predefinita) o una nuova tabella in un database, in base ai dati esistenti nello stesso database. Questa procedura crea un modello basato sulla tabella di esempio diamonds dei Set di dati di esempio.

Usare il seguente comando per creare questo ruolo.

DROP TABLE IF EXISTS diamonds;

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

Questa procedura presuppone che la tabella sia già stata creata nel database default dell'area di lavoro.

  1. Con il progetto aperto, fare clic su Sviluppa nella parte superiore dell'interfaccia utente.

  2. Fare clic su Inizializza progetto dbt.

  3. Fare clic su Commit e sincronizzazione, immettere un messaggio di commit e poi fare clic su Commit.

  4. Fare clic su Crea ramo, immettere un nome per il ramo e poi fare clic su Invia.

  5. Creare il primo modello: fare clic su Crea nuovo file.

  6. Nell'editor di testo, inserire la seguente istruzione SQL. Questa istruzione seleziona solo i dettagli carat, cut, color e clarity per ogni diamante della tabella diamonds. Il blocco config indica a dbt di creare una tabella nel database in base a questa istruzione.

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

    select carat, cut, color, clarity
from diamonds

    Suggerimento

    Per altre opzioni config, ad esempio per la strategia incrementale merge, consultare Configurazioni di Databricks nella documentazione di dbt.

  7. Fare clic su Salva con nome.

  8. Per il nome del file, immettere models/diamonds_four_cs.sql e poi fare clic su Crea.

  9. Creare un secondo modello: fare clic su Icona Crea nuovo file (Crea nuovo file) nell'angolo in alto a destra.

  10. Nell'editor di testo, inserire la seguente istruzione SQL. Questa istruzione seleziona valori univoci dalla colonna colors nella tabella diamonds_four_cs, ordinando i risultati in ordine alfabetico prima per ultimo. Poiché non è presente alcun blocco config, questo modello indica a dbt di creare una vista nel database in base a questa istruzione.

    select distinct color
from diamonds_four_cs
sort by color asc

  11. Fare clic su Salva con nome.

  12. Per il nome del file, immettere models/diamonds_list_colors.sql e poi fare clic su Crea.

  13. Creare un terzo modello: fare clic su Icona Crea nuovo file (Crea nuovo file) nell'angolo in alto a destra.

  14. Nell'editor di testo, inserire la seguente istruzione SQL. Questa istruzione calcola i prezzi dei diamanti in base al colore, ordinando i risultati in base al prezzo medio dal più alto al più basso. Questo modello indica a dbt di creare una vista nel database in base a questa istruzione.

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

  15. Fare clic su Salva con nome.

  16. Per il nome del file, immettere models/diamonds_prices.sql e fare clic su Crea.

  17. Eseguire i modelli: nella riga di comando eseguire il comando dbt run con i percorsi dei tre file precedenti. Nel database default, dbt crea una tabella denominata diamonds_four_cs e due viste denominate diamonds_list_colors e diamonds_prices. dbt ottiene questi nomi di vista e tabella dai nomi file correlati .sql.

    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

  18. Eseguire il seguente codice SQL per elencare le informazioni sulle nuove viste e per selezionare tutte le righe dalla tabella e dalle viste.

    Se ci si connette a un cluster, è possibile eseguire questo codice SQL da un notebook collegato al cluster, specificando SQL come linguaggio predefinito per il notebook. Se ci si connette a un'istanza di SQL Warehouse, è possibile eseguire questo codice SQL da una query.

    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 |
+-------+---------+
...

Passaggio 2: Creare ed eseguire modelli più complessi

In questo passaggio vengono creati modelli più complessi per un set di tabelle dati correlate. Queste tabelle di dati contengono informazioni su una lega sportiva fittizia di tre squadre che giocano una stagione di sei partite. Questa procedura crea le tabelle dati, crea ed esegue i modelli.

  1. Eseguire il seguente codice SQL per creare le tabelle di dati necessarie.

    Se ci si connette a un cluster, è possibile eseguire questo codice SQL da un notebook collegato al cluster, specificando SQL come linguaggio predefinito per il notebook. Se ci si connette a un'istanza di SQL Warehouse, è possibile eseguire questo codice SQL da una query.

    Le tabelle e le viste in questo passaggio iniziano con zzz_ per identificarle come parte di questo esempio. Non è necessario seguire questo modello per tabelle e viste personalizzate.

    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. Creare il primo modello: fare clic su Icona Crea nuovo file (Crea nuovo file) nell'angolo in alto a destra.

  3. Nell'editor di testo, inserire la seguente istruzione SQL. Questa istruzione crea una tabella che fornisce i dettagli di ogni gioco, ad esempio i nomi e i punteggi della squadra. Il blocco config indica a dbt di creare una tabella nel database in base a questa istruzione.

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

  4. Fare clic su Salva con nome.

  5. Per il nome del file, immettere models/zzz_game_details.sql e poi fare clic su Crea.

  6. Creare un secondo modello: fare clic su Icona Crea nuovo file (Crea nuovo file) nell'angolo in alto a destra.

  7. Nell'editor di testo, inserire la seguente istruzione SQL. Questa istruzione crea una visualizzazione che elenca i record di vittoria della squadra per la stagione.

    -- 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 zzz_game_details
)
group by winner
order by wins desc

  8. Fare clic su Salva con nome.

  9. Per il nome del file, immettere models/zzz_win_loss_records.sql e poi fare clic su Crea.

  10. Eseguire i modelli: nella riga di comando eseguire il comando dbt run con i percorsi dei due file precedenti. Nel database default (come specificato nelle impostazioni del progetto), dbt crea una tabella denominata zzz_game_details e una vista denominata zzz_win_loss_records. dbt ottiene questi nomi di vista e tabella dai nomi file correlati .sql.

    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

  11. Eseguire il seguente codice SQL per elencare le informazioni sulla nuova vista e per selezionare tutte le righe dalla tabella e dalla vista.

    Se ci si connette a un cluster, è possibile eseguire questo codice SQL da un notebook collegato al cluster, specificando SQL come linguaggio predefinito per il notebook. Se ci si connette a un'istanza di SQL Warehouse, è possibile eseguire questo codice SQL da una query.

    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      |
+---------------+------+--------+

Passaggio 3: Creare ed eseguire test

In questo passaggio vengono creati test, che sono affermazioni sui modelli. Quando si eseguono questi test, dbt dice se ogni test del progetto ha esito positivo o negativo.

Esistono due tipi di test. I test dello schema, scritti in YAML, restituiscono il numero di record che non superano un'asserzione. Quando questo numero è zero, tutti i record vengono superati e quindi i test vengono superati. I test dei dati sono query specifiche che devono restituire zero record affinché l' esito sia positivo.

  1. Creare i test dello schema: fare clic su Icona Crea nuovo file (Crea nuovo file) nell'angolo in alto a destra.

  2. Nell'editor di testo immettere il seguente contenuto. Questo file include test dello schema che determinano se le colonne specificate hanno valori unici, non sono nulle, hanno solo i valori specificati o una loro combinazione.

    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

  3. Fare clic su Salva con nome.

  4. Per il nome del file, immettere models/schema.yml e poi fare clic su Crea.

  5. Creare il primo test dati: fare clic su Icona Crea nuovo file (Crea nuovo file) nell'angolo in alto a destra.

  6. Nell'editor di testo, inserire la seguente istruzione SQL. Questo file include un test dati per determinare se eventuali partite si sono verificate al di fuori della stagione regolare.

    -- 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 zzz_game_details
where date < '2020-12-12'
or date > '2021-02-06'

  7. Fare clic su Salva con nome.

  8. Per il nome del file, immettere tests/zzz_game_details_check_dates.sql e poi fare clic su Crea.

  9. Creare un secondo test dati: fare clic su Icona Crea nuovo file (Crea nuovo file) nell'angolo in alto a destra.

  10. Nell'editor di testo, inserire la seguente istruzione SQL. Questo file include un test dati per determinare se i punteggi sono stati negativi o se le partite sono state pareggiate.

    -- 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 zzz_game_details
where home_score < 0
or visitor_score < 0
or home_score = visitor_score

  11. Fare clic su Salva con nome.

  12. Per il nome del file, immettere tests/zzz_game_details_check_scores.sql e poi fare clic su Crea.

  13. Creare un terzo test dati: fare clic su Icona Crea nuovo file (Crea nuovo file) nell'angolo in alto a destra.

  14. Nell'editor di testo, inserire la seguente istruzione SQL. Questo file include un test dei dati per determinare se le squadre hanno record negativi di vittorie o sconfitte, se hanno record di vittorie o sconfitte superiori alle partite giocate o se hanno giocato più partite di quelle consentite.

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

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

  15. Fare clic su Salva con nome.

  16. Per il nome del file, immettere tests/zzz_win_loss_records_check_records.sql e poi fare clic su Crea.

  17. Eseguire i test: nella riga di comando eseguire il comando dbt test.

Passaggio 4: Pulizia

È possibile eliminare le tabelle e le viste create per questo esempio eseguendo il codice SQL seguente.

Se ci si connette a un cluster, è possibile eseguire questo codice SQL da un notebook collegato al cluster, specificando SQL come linguaggio predefinito per il notebook. Se ci si connette a un'istanza di SQL Warehouse, è possibile eseguire questo codice SQL da una query.

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;

Passaggi successivi

  • Altre informazioni sui modelli dbt.
  • Informazioni su come testare i progetti dbt.
  • Informazioni su come usare Jinja, un linguaggio di creazione modelli, per la programmazione di SQL nei progetti dbt.
  • Maggiori informazioni sulle procedure consigliate dbt.

Risorse aggiuntive