Skip to main content

Oracle Profile

Overview of dbt-oracle​

Maintained by: Oracle
Source: Github
Core version: v1.0.7
dbt Cloud: Not Supported
dbt Slack channel #db-oracle

dbt-oracle stars

Installation​

dbt-oracle can be installed via the Python Package Index (PyPI) using pip

pip install dbt-oracle

Install Oracle Instant Client libraries​

To use dbt-oracle, you will need the Oracle Instant Client libraries installed. These provide the necessary network connectivity allowing dbt-oracle to access an Oracle Database instance.

Oracle client libraries versions 21, 19, 18, 12, and 11.2 are supported where available on Linux, Windows and macOS (Intel x86). It is recommended to use the latest client possible: Oracle’s standard client-server version interoperability allows connection to both older and newer databases.

  1. Download an Oracle 21, 19, 18, 12, or 11.2 “Basic” or “Basic Light” zip file matching your Python 64-bit or 32-bit architecture:

    1. x86-64 64-bit
    2. x86 32-bit
    3. ARM (aarch64) 64-bit

  2. Unzip the package into a single directory that is accessible to your application. For example:

    mkdir -p /opt/oracle
    cd /opt/oracle
    unzip instantclient-basic-linux.x64-21.1.0.0.0.zip

  3. Install the libaio package with sudo or as the root user. For example:

    sudo yum install libaio

    On some Linux distributions this package is called libaio1 instead.

  1. if there is no other Oracle software on the machine that will be impacted, permanently add Instant Client to the runtime link path. For example, with sudo or as the root user:

     sudo sh -c "echo /opt/oracle/instantclient_21_1 > /etc/ld.so.conf.d/oracle-instantclient.conf"
     sudo ldconfig

    Alternatively, set the environment variable LD_LIBRARY_PATH

    export LD_LIBRARY_PATH=/opt/oracle/instantclient_21_1:$LD_LIBRARY_PATH

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

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

To connect using the database hostname or IP address, you need to specify the following

  • host
  • port (1521 or 1522)
  • protocol (tcp or tcps)
  • service
export DBT_ORACLE_HOST=adb.us-ashburn-1.oraclecloud.com
export DBT_ORACLE_SERVICE=ga01d78d2ecd5f1_db202112221108_high.adb.oraclecloud.com
~/.dbt/profiles.yml
dbt_test:
   target: "{{ env_var('DBT_TARGET', 'dev') }}"
   outputs:
      dev:
         type: oracle
         user: "{{ env_var('DBT_ORACLE_USER') }}"
         pass: "{{ env_var('DBT_ORACLE_PASSWORD') }}"
         protocol: "tcps"
         host: "{{ env_var('DBT_ORACLE_HOST') }}"
         port: 1522
         service: "{{ env_var('DBT_ORACLE_SERVICE') }}"
         database: "{{ env_var('DBT_ORACLE_DATABASE') }}"
         schema: "{{ env_var('DBT_ORACLE_SCHEMA') }}"
         threads: 4

Connecting to Oracle Autonomous Database in Cloud​

To enable connection to Oracle Autonomous Database in Oracle Cloud, a wallet needs be downloaded from the cloud, and cx_Oracle needs to be configured to use it. The wallet gives mutual TLS which provides enhanced security for authentication and encryption. A database username and password is still required for your application connections.

Install the Wallet and Network Configuration Files​

From the Oracle Cloud console for the database, download the wallet zip file. It contains the wallet and network configuration files. Note: keep wallet files in a secure location and share them only with authorized users.

Unzip the wallet zip file. For cx_Oracle, only these files from the zip are needed:

  • tnsnames.ora - Maps net service names used for application connection strings to your database services
  • sqlnet.ora - Configures Oracle Network settings
  • cwallet.sso - Enables SSL/TLS connections

After downloading the wallet, put the unzipped wallet files in a secure directory and set the TNS_ADMIN environment variable to that directory name. Next, edit the sqlnet.ora file to point to the wallet directory.

sqlnet.ora
WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="/path/to/wallet/directory")))
SSL_SERVER_DN_MATCH=yes
TLS v/s mTLS

If you have enabled TLS connections on your Database instance then dbt can connect using only database username, password and the Oracle Net connect name given in the unzipped tnsnames.ora file.

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