Tutorial: Crear, ejecutar y probar modelos dbt localmente

Artículo
08/02/2024

En este tutorial se explica cómo crear, ejecutar y probar modelos dbt localmente. También puede ejecutar proyectos dbt como tareas de trabajo de Azure Databricks. Para más información, consulte Usar transformaciones de dbt en un trabajo de Azure Databricks.

Antes de empezar

Para seguir este tutorial, primero debe conectar el área de trabajo de Azure Databricks a dbt Core. Para más información, consulte Conexión a dbt Core.

Paso 1: Creación y ejecución de modelos

En este paso, usará su editor de texto favorito para crear modelos, que son instrucciones select que crean una nueva vista (predeterminada) o una nueva tabla en una base de datos, a partir de los datos existentes en esa misma base de datos. En este procedimiento se crea un modelo basado en la diamondstabla de muestra a partir de los conjuntos de datos de muestra.

Use el código siguiente para crear esta tabla.

DROP TABLE IF EXISTS diamonds;

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

En el directorio models del proyecto, cree un archivo denominado diamonds_four_cs.sql con la instrucción SQL siguiente. Esta instrucción selecciona solo los detalles de quilates, corte, color y claridad de cada diamante de la tabla diamonds. El bloque config indica a dbt que cree una tabla en la base de datos a partir de esta instrucción.
```
{{ config(
  materialized='table',
  file_format='delta'
) }}
```
```
select carat, cut, color, clarity
from diamonds
```
Sugerencia

Para ver opciones adicionales config, como el uso del formato de archivo Delta y la estrategia incremental merge, consulte Configuración de Databricks en la documentación de dbt.
En el directorio models del proyecto, cree un segundo archivo denominado diamonds_list_colors.sql con la instrucción SQL siguiente. Esta instrucción selecciona valores únicos de la columna colors de la tabla diamonds_four_cs y ordena los resultados en orden alfabético del primero al último. Dado que no hay ningún bloque config, este modelo indica a dbt que cree una vista en la base de datos basada en esta instrucción.
```
select distinct color
from {{ ref('diamonds_four_cs') }}
sort by color asc
```
En el directorio models del proyecto, cree un tercer archivo denominado diamonds_prices.sql con la instrucción SQL siguiente. Esta instrucción hace un promedio de los precios de los diamantes por color y ordena los resultados por precio medio de mayor a menor. Este modelo indica a dbt que cree una vista en la base de datos basada en esta instrucción.
```
select color, avg(price) as price
from diamonds
group by color
order by price desc
```

Con el entorno virtual activado, ejecute el comando dbt run con las rutas de acceso a los tres archivos anteriores. En la base de datos default (como se especifica en el archivo profiles.yml), dbt crea una tabla denominada diamonds_four_cs y dos vistas denominadas diamonds_list_colors y diamonds_prices. dbt obtiene estos nombres de vista y de tabla de sus nombres de archivo .sql relacionados.

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

Ejecute el siguiente código SQL para mostrar información sobre las nuevas vistas y para seleccionar todas las filas de la tabla y las vistas.

Si se conecta a un clúster, puede ejecutar este código SQL desde un cuaderno que esté conectado al clúster, especificando SQL como lenguaje predeterminado para el cuaderno. Si se conecta a un almacén de SQL, puede ejecutar este código SQL desde una consulta.

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

Paso 2: Creación y ejecución de modelos más complejos

En este paso, creará modelos más complejos para un conjunto de tablas de datos relacionadas. Estas tablas de datos contienen información sobre una liga deportiva ficticia con tres equipos que jugarán una temporada de seis partidos. Este procedimiento crea las tablas de datos y crea y ejecuta los modelos.

Ejecute el siguiente código SQL para crear las tablas de datos necesarias.

Las tablas y vistas de este paso comienzan por zzz_ para ayudar a identificarlas como parte de este ejemplo. No es necesario seguir este patrón para sus propias tablas y vistas.

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

En el directorio models del proyecto, cree un archivo denominado zzz_game_details.sql con la instrucción SQL siguiente. Esta instrucción crea una tabla que proporciona los detalles de cada partido, como los nombres y las puntuaciones de los equipos. El bloque config indica a dbt que cree una tabla en la base de datos a partir de esta instrucción.

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

En el directorio models del proyecto, cree un archivo denominado zzz_win_loss_records.sql con la instrucción SQL siguiente. Esta instrucción crea una vista que enumera los registros de victorias y derrotas de los equipos de la temporada.

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

Con el entorno virtual activado, ejecute el comando dbt run con las rutas de acceso a los tres archivos anteriores. En la base de datos default (como se especifica en el archivo profiles.yml), dbt crea una tabla denominada zzz_game_details y una vista denominada zzz_win_loss_records. dbt obtiene estos nombres de vista y de tabla de sus nombres de archivo .sql relacionados.

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

Ejecute el código SQL siguiente para enumerar la información sobre la nueva vista y seleccionar todas las filas de la tabla y la vista.

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

Paso 3: Creación y ejecución de pruebas

En este paso, creará pruebas, que son aserciones que hace en relación con los modelos. Al ejecutar estas pruebas, dbt le muestra si cada prueba del proyecto se supera o se suspende.

Hay dos tipos de pruebas. Las pruebas de esquema, aplicadas en YAML, devuelven el número de registros que no superan una aserción. Cuando este número es cero, todos los registros son correctos y, por tanto, la prueba se supera. Las pruebas de datos son consultas específicas que deben devolver cero registros para superarse.

En el directorio models del proyecto, cree un archivo denominado schema.yml con el contenido siguiente. Este archivo incluye pruebas de esquema que determinan si las columnas especificadas tienen valores únicos, no son valores NULL, solo tienen los valores especificados o una combinación.

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

En el directorio tests del proyecto, cree un archivo denominado zzz_game_details_check_dates.sql con la instrucción SQL siguiente. Este archivo incluye una prueba de datos para determinar si se ha jugado algún partido fuera de la temporada estándar.
```
-- 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'
```

En el directorio tests del proyecto, cree un archivo denominado zzz_game_details_check_scores.sql con la instrucción SQL siguiente. Este archivo incluye una prueba de datos para determinar si las puntuaciones eran negativas o si hubo algún empate.

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

En el directorio tests del proyecto, cree un archivo denominado zzz_win_loss_records_check_records.sql con la instrucción SQL siguiente. Este archivo incluye una prueba de datos para determinar si algún equipo tenía registros de victorias o derrotas negativos, más registros de victorias o derrotas que partidos jugados o más partidos jugados de la cuenta.
```
-- 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
```

Con el entorno virtual activado, ejecute el comando dbt test.

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

Paso 4: Limpieza

Puede eliminar las tablas y vistas que creó para este ejemplo ejecutando el código SQL siguiente.

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;

Solución de problemas

Para obtener información sobre problemas comunes al usar dbt Core con Azure Databricks y cómo resolverlos, consulte Obtener ayuda en el sitio web de dbt Labs.

Pasos siguientes

Ejecute proyectos de dbt Core como tareas de trabajo de Azure Databricks. Consulte Uso de transformaciones de dbt en un trabajo de Azure Databricks.

Recursos adicionales

Consulte los siguientes recursos en el sitio web de dbt Labs:

Compartir a través de

Tutorial: Crear, ejecutar y probar modelos dbt localmente

Antes de empezar

Paso 1: Creación y ejecución de modelos

Paso 2: Creación y ejecución de modelos más complejos

Paso 3: Creación y ejecución de pruebas

Paso 4: Limpieza

Solución de problemas

Pasos siguientes

Recursos adicionales

Comentarios

Recursos adicionales