Skip to main content

Oracle setup

Overview of dbt-oracle

  • Maintained by: Oracle
  • Authors: Oracle
  • GitHub repo: oracle/dbt-oracle
  • PyPI package: dbt-oracle
  • Slack channel: #db-oracle
  • Supported dbt Core version: v1.2.1 and newer
  • dbt Cloud support: Not Supported
  • Minimum data platform version: SQlite Version 3.0

Installation​

Installing dbt-oracle

pip is the easiest way to install the adapter:

pip install dbt-oracle

Installing dbt-oracle will also install dbt-core and any other dependencies.

Configuring dbt-oracle

For Oracle-specifc configuration please refer to Oracle Configuration

For further info, refer to the GitHub repository: oracle/dbt-oracle

Configure the Python driver mode​

info

python-oracledb is the renamed, major release of Oracle's popular cx_Oracle interface

python-oracledb makes it optional to install the Oracle Client libraries. This driver supports 2 modes

  1. Thin mode (preferred) : Python process directly connects to the Oracle database. This mode does not need the Oracle Client libraries
  2. Thick mode : Python process links with the Oracle Client libraries. Some advanced Oracle database functionalities (for e.g. Advanced Queuing and Scrollable cursors) are currently available via Oracle Client libraries

It is highly recommended to use the thin mode as it vastly simplifies installation. You can configure the driver mode using the environment variable ORA_PYTHON_DRIVER_TYPE

Driver ModeOracle Client libraries required?Configuration
ThinNoORA_PYTHON_DRIVER_TYPE=thin
ThickYesORA_PYTHON_DRIVER_TYPE=thick
cx_oracle (old driver)YesORA_PYTHON_DRIVER_TYPE=cx

The default value of ORA_PYTHON_DRIVER_TYPE is cx. This might change in the future as more users migrate towards the new python driver.

export ORA_PYTHON_DRIVER_TYPE=thin

Configure wallet for Oracle Autonomous Database in Cloud​

dbt can connect to Oracle Autonomous Database (ADB) in Oracle Cloud using either TLS (Transport Layer Security) or mutual TLS (mTLS). TLS and mTLS provide enhanced security for authentication and encryption. A database username and password is still required for dbt connections which can be configured as explained in the next section Connecting to Oracle Database.

With TLS, dbt can connect to Oracle ADB without using a wallet. Both Thin and Thick modes of the python-oracledb driver support TLS.

info

In Thick mode, dbt can connect through TLS only when using Oracle Client library versions 19.14 (or later) or 21.5 (or later).

Refer to Oracle documentation to connect to an ADB instance using TLS authentication and the blog post Easy wallet-less connections to Oracle Autonomous Databases in Python to enable TLS for your Oracle ADB instance.

Connecting to Oracle Database​

Define the following mandatory parameters as environment variables and refer them in the connection profile using env_var jinja function. Optionally, you can also define these directly in the profiles.yml file, but this is not recommended

export DBT_ORACLE_USER=<username>
export DBT_ORACLE_PASSWORD=***
export DBT_ORACLE_SCHEMA=<username>

Starting with dbt-oracle==1.0.2, it is optional to set the database name

export DBT_ORACLE_DATABASE=example_db2022adb

If database name is not set, adapter will retrieve it using the following query. 

SELECT SYS_CONTEXT('userenv', 'DB_NAME') FROM DUAL

An Oracle connection profile for dbt can be set using any one of the following methods

The tnsnames.ora file is a configuration file that contains network service names mapped to connect descriptors. The directory location of tnsnames.ora file can be specified using TNS_ADMIN environment variable

tnsnames.ora
db2022adb_high = (description = 
                 (retry_count=20)(retry_delay=3)
                 (address=(protocol=tcps)
                 (port=1522)
                 (host=adb.example.oraclecloud.com))
                 (connect_data=(service_name=example_high.adb.oraclecloud.com))
                 (security=(ssl_server_cert_dn="CN=adb.example.oraclecloud.com,
                 OU=Oracle BMCS US,O=Oracle Corporation,L=Redwood City,ST=California,C=US")))

The TNS alias db2022adb_high can be defined as environment variable and referred in profiles.yml

export DBT_ORACLE_TNS_NAME=db2022adb_high
~/.dbt/profiles.yml
dbt_test:
   target: dev
   outputs:
      dev:
         type: oracle
         user: "{{ env_var('DBT_ORACLE_USER') }}"
         pass: "{{ env_var('DBT_ORACLE_PASSWORD') }}"
         database: "{{ env_var('DBT_ORACLE_DATABASE') }}"
         tns_name: "{{ env_var('DBT_ORACLE_TNS_NAME') }}"
         schema: "{{ env_var('DBT_ORACLE_SCHEMA') }}"
         threads: 4

Supported Features​

  • Table materialization
  • View materialization
  • Incremental materialization
  • Seeds
  • Data sources
  • Singular tests
  • Generic tests; Not null, Unique, Accepted values and Relationships
  • Operations
  • Analyses
  • Exposures
  • Document generation
  • Serve project documentation as a website
  • All dbt commands are supported

Not Supported features​

  • Ephemeral materialization
0
Edit this page