This repo provides an example project for the dbt-spark-livy adapter for dbt.
This directory is a dbt project.
This directory contains some utilities for generating fake data.
It is recommended to use venv
to create a Python virtual environment for the demo.
Python >= 3.8 dbt-core >= 1.1.0 git
Start by cloning this repo
git clone https://github.com/TapasSenapati/dbt-spark-example.git
Next install the requirements
pip install dbt-core
pip install dbt-spark-livy
Create a dbt profile in ~/.dbt/profiles.yml
For a Cloudera Data Platform cluster (CDW or DataHub), it should look like this:
dbt_spark_demo:
outputs:
dev:
host: https://dbt-spark-gateway.ciadev.cna2-sx9y.cloudera.site/dbt-spark/cdp-proxy-api/livy_for_spark3
method: livy
schema: testdbt
threads: 1
type: spark_livy
password: Password123!
user: srv_cia_test_user
target: dev
Test the profile with dbt debug
To generate fake data, we must first add our Impala details to util/data_gen/write_data.py
Modify the following section to reflect your environment:
impala_conf = {
'host': '',
'port': '',
'user': '',
'password': '!',
'auth_mechanism': 'ldap',
'use_ssl': True,
'use_http_transport': True,
'http_path': ''
}
Next, move to the util/data_gen
directory
cd util/data_gen
Run the start.sh
helper to generate the first set of fake data
./start.sh 1
This generates 2 days of fake data for the dates 01/01/2022 and 02/01/2022 and writes it to Impala.
It will create a database dbt_demo_raw_covid
with 2 tables.
With our fake data loaded, we can start using dbt.
First, run the Seeds to load some reference data.
Two Seeds are included in the demo, populations
and country_codes
.
Move to the dbt_impala_demo
dbt project directory.
Run the seeds with
dbt seed
Our Seeds are configured with a couple of Tests
We also have a custom test created in dbt_impala_demo/tests/generic/test_length.sql
which is used to test the character length of a column.
Our reference data has columns that include ISO Alpha2 and Alpha3 country codes - we know that these columns should always be 2 or 3 columns respectively. To ensure that our reference data is high quality, we can use dbt to test these assumptions and report the results.
We expect that Alpha2 and Alpha3 columns are the correct length, and that no fields should be null.
Run the tests with
dbt test
We have 3 sets of models in this demo project.
Firstly, we have raw
. Our raw models make use of Sources. This is data that already exists in our database that dbt needs to refer to. This is the fake data we loaded earlier.
Our raw models are defined in models/raw/covid/
.
Next, we have staging
. These are Models. Our staging models use the source()
method to refer to the Sources we defined in our raw models. The staging models are intermediate views created over our raw data to handle some basic type conversion. These are materialized as views, and we don't expect our end users to query the staging models.
Our staging models are defined in models/staging/covid
Lastly, we have mart
. These are Models. Our mart models use the ref()
method to refer to the staging models and reference seeds we created using dbt. We use the staging views to handle most of the logic for type casting, but we do some renaming here to make our models easier for users to understand. These models are materialized as tables, as this gives greater performance for user queries. We can use incremental models to make the building of the model more performant.
Our mart models are defined in models/mart/covid
Run the models with
dbt run
You should now have the following databases & tables:
- reference (database)
- ref__population (table)
- ref__country_codes (table)
- raw_covid (database)
- raw_covid__vaccines (table)
- raw_covid__cases (table)
- staging_covid (database)
- stg_covid__cases (view)
- mart_covid (database)
- covid_cases (table)
In the raw, staging and mart tables, you should see 2 days worth of data for the dates 01/01/2022 and 02/01/2022.
To demonstrate how we can handle new data arriving, let's generate some more data.
As before, move to the util/data_gen
dir and generate the next 2 days of data with
./start.sh 2
This will generate fake data for 03/01/2022 and 04/01/2022 and write it into the raw tables in Impala.
Select the data in raw_covid.raw_covid__cases
and you should see that you now have 4 days of data.
Select the data in staging_covid.stg_covid__cases
and you should see that you also have 4 days of data, as this is a view ontop of the raw table.
Selecting the data from mart_covid.covid_cases
will show that you still only have 2 days worth of data in the mart model. This is because we have not yet updated the model.
To get the latest data into our mart model, we must run the models again.
Run the models again with
dbt run
This will trigger the incremental update of the mart model, by selecting only the days that are later than the maximum date we already have (our current maximum is 02/01/2022, so it will only select the dates 03/01/2022 and 04/01/2022).
When complete, review the mart_covid.covid_cases
tables and you should see that you now have 4 days worth of data here.