dbt-oracle version 1.0.0
dbt (data build tool) adapter for the Oracle database. dbt "adapters" are responsible for adapting dbt's functionality to a given database. Check https://docs.getdbt.com/docs/contributing/building-a-new-adapter
Prior to version 1.0.0, dbt-oracle was created and maintained by Indicium on their GitHub repo. Contributors in this repo are credited for laying the groundwork and maintaining the adapter till version 0.4.3. From version 1.0.0, dbt-oracle is maintained and distributed by Oracle.
Table of contents
What is dbt?
dbt does the T in ELT (Extract, Load, Transform). To work with dbt you need a copy of your data already loaded in your warehouse.
dbt features
- With dbt, you can express all transforms with SQL select
- Different materialization strategies.
- view
- table
- incremental; selective rebuild for new rows
- ephemeral; Model 1 interpolated into Model 2 as a Common Table Expression (CTE)
- No need to write boilerplate code
- All code to create table or views is generated using macros.
- Idempotence; rerun models
- If your source data were to stop updating, successive runs of your transformations would still result in the same tables and views in your warehouse.
- If your production deployment of your transformations were interrupted, the next run of the transformations would result in the same tables and views as if the deployment had not been interrupted.
- If you manually triggered transformations between scheduled runs, the scheduled run would result in the same tables and views as if the manual runs had not been triggered.
- All transformation code is accessible and can be version controlled.
- Different materialization strategies.
- Dependency resolution
- Use of ref() function
select * from {{ ref('MODEL_NAME')}} - dbt automatically resolves dependencies in between models and builds a Directed Acyclic Graph (DAG). Each path in the DAG can be independently executed using multiple threads.
- Interpolates the name of database schema
- Use of ref() function
- Includes a built-in testing framework to ensure model accuracy
- not null
- unique
- contains accepted values
- relationships
- custom tests
- Generate documentation for your project and render it as a website.
- Use macros to write reusable SQL
An example
dbt model
--models/sales_internet_channel.sql
{{ config(materialized='table') }}
WITH sales_internet AS (
SELECT * FROM {{ source('sh_database', 'sales') }}
WHERE channel_id = 4 )
SELECT * FROM sales_internet
dbt compiles the above SQL template to run the below DDL statement.
CREATE TABLE dbt_test.sales_internet_channel AS
WITH sales_internet AS (
SELECT * from sh.sales
WHERE channel_id = 4 )
SELECT * FROM sales_internet
For dbt documentation, refer https://docs.getdbt.com/docs/introduction
Installation
dbt-oracle can be installed via the Python Package Index (PyPI) using pip
pip install -U dbt-oracle
Support
dbt-oracle will provide support for the following
- Python versions 3.6, 3.7, 3.8 and 3.9
- Autonomous Database versions 19c and 21c
- OS
- Linux
- MacOS
- Windows
Core dependencies
dbt-oracle requires the following 3 python packages.
dbt-core
- Open source framework for data transformation
- Jinja Templating and core SQL compilation logic
- Latest version of dbt-core is preferred; From version 1.0.0, dbt-core supports Python 3.7 or higher
- For Python 3.6, pip will fallback to version 0.21.1 of dbt-core
cx-Oracle
- Python driver for Oracle database
- Oracle client libraries should be installed on the system. For details check, https://cx-oracle.readthedocs.io/en/latest/user_guide/installation.html
dataclasses; python_version < '3.7'
- dataclasses package was introduced in the standard Python library from Python 3.7. This is conditional dependency and required only for Python 3.6
Getting Started
Create a dbt project for oracle database using the dbt init command. The init command is interactive and will help you get started with a new project.
dbt init will:
- ask you the name of the project
- ask you the database adapter you are using i.e. oracle
- prompt to specify necessary connection details
This example shows initialization of test project dbt_oracle_test_project
>> dbt init
Running with dbt=1.0.4
Enter a name for your project (letters, digits, underscore): dbt_oracle_test_project
Which database would you like to use?
[1] oracle
Enter a number: 1
protocol (tcp or tcps) [tcps]:
host (adb.<oci-region>.oraclecloud.com) [{{ env_var('DBT_ORACLE_HOST') }}]:
port [1522]:
user [{{ env_var('DBT_ORACLE_USER') }}]:
password [{{ env_var('DBT_ORACLE_PASSWORD') }}]:
service (service name in tnsnames.ora) [{{ env_var('DBT_ORACLE_SERVICE') }}]:
dbname (database name in which dbt objects should be created) [{{ env_var('DBT_ORACLE_DATABASE') }}]:
schema (database schema in which dbt objects should be created) [{{ env_var('DBT_ORACLE_SCHEMA') }}]:
threads (1 or more) [1]: 4
Profile dbt_oracle_test_project written to ~/.dbt/profiles.yml using target's profile_template.yml and your supplied values. Run 'dbt debug' to validate the connection.
Your new dbt project "dbt_oracle_test_project" was created!
Then dbt init command will:
-
Create the following folder with project name and sample files to get you started
├── README.md ├── analyses ├── dbt_project.yml ├── macros ├── models │ └── example ├── seeds ├── snapshots └── tests -
Create a connection profile on your local machine. The default location is
~/.dbt/profiles.ymlNext step, configure connection related parameters and test if dbt connection works using dbt debug command
>> dbt debug os info: macOS-11.6-x86_64-i386-64bit Using profiles.yml file at ~/.dbt/profiles.yml Using dbt_project.yml file at /dbt_oracle_test_project/dbt_project.yml Configuration: profiles.yml file [OK found and valid] dbt_project.yml file [OK found and valid] Required dependencies: - git [OK found] Connection: user: *** database: ga01d76d2ecd5e0_db202112221108 schema: *** protocol: tcps host: adb.us-ashburn-1.oraclecloud.com port: 1522 service: <service_name>_high.adb.oraclecloud.com connection_string: None shardingkey: [] supershardingkey: [] cclass: None purity: None Connection test: [OK connection ok] All checks passed!
Documentation [TODO]
Link to the homepage - https://oracle.github.io/dbt-oracle
Link to documentation - https://dbt-oracle.readthedocs.io
Contributing
This project welcomes contributions from the community. Before submitting a pull request, please review our contribution guide.
Security
Please consult the security guide for our responsible security vulnerability disclosure process.
License
dbt-oracle is licensed under Apache 2.0 License which you can find here
![[Bug] get_relation reporting dbt found an approximate match](https://avatars.githubusercontent.com/u/81630318?v=4)
![[Bug] don't drop temporary table o$pt](https://avatars.githubusercontent.com/u/32124036?v=4)
![[Bug]](https://avatars.githubusercontent.com/u/8052991?v=4)
![[Feature] oracle__make_temp_relation identifier uniqueness improvements](https://avatars.githubusercontent.com/u/30044742?v=4)
![[Bug] multi schema materializations not supported without administrative privileges](https://avatars.githubusercontent.com/u/1694964?v=4)
![[Feature] affected row count](https://avatars.githubusercontent.com/u/76617629?v=4)
![[Feature] Adding WHERE codintion during MERGE update](https://avatars.githubusercontent.com/u/46685799?v=4)
206 Dec 30, 2022
262 Nov 25, 2022
93 Jan 8, 2023
81 Jan 6, 2023
270 Jan 8, 2023
419 Jan 1, 2023
2 Nov 12, 2021
3 Apr 18, 2022
7 Oct 30, 2022
1 Oct 17, 2022
31 Nov 1, 2022
14 Aug 5, 2022
0 Mar 27, 2022
70 Jan 2, 2023
27 Oct 22, 2022
64 Sep 28, 2022
39 Nov 8, 2022
26 Dec 5, 2022
35 Jan 2, 2023