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
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.
- Linux
- Windows
- MacOS
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:
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.zipInstall 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.
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 ldconfigAlternatively, set the environment variable
LD_LIBRARY_PATH
export LD_LIBRARY_PATH=/opt/oracle/instantclient_21_1:$LD_LIBRARY_PATH
- Download an Oracle 21, 19, 18, 12, or 11.2 “Basic” or “Basic Light” zip file: 64-bit or 32-bit, matching your Python architecture.
Windows 7 users
Note that Oracle Client versions 21c and 19c are not supported on Windows 7.
Unzip the package into a directory that is accessible to your application. For example unzip
instantclient-basic-windows.x64-19.11.0.0.0dbru.zip
toC:\oracle\instantclient_19_11
.Oracle Instant Client libraries require a Visual Studio redistributable with a 64-bit or 32-bit architecture to match Instant Client’s architecture.
- For Instant Client 21 install VS 2019 or later
- For Instant Client 19 install VS 2017
- For Instant Client 18 or 12.2 install VS 2013
- For Instant Client 12.1 install VS 2010
- For Instant Client 11.2 install VS 2005 64-bit
Add the Oracle Instant Client directory to the
PATH
environment variable.The directory must occur inPATH
before any other Oracle directories. Restart any open command prompt windows.SET PATH=C:\oracle\instantclient_19_9;%PATH%
Download the instant client DMG package
cd $HOME/Downloads
curl -O https://download.oracle.com/otn_software/mac/instantclient/198000/instantclient-basic-macos.x64-19.8.0.0.0dbru.dmgMount the instant client DMG package
hdiutil mount instantclient-basic-macos.x64-19.8.0.0.0dbru.dmg
Run the install script in the mounted package
/Volumes/instantclient-basic-macos.x64-19.8.0.0.0dbru/install_ic.sh
Unmount the package
hdiutil unmount /Volumes/instantclient-basic-macos.x64-19.8.0.0.0dbru
The Instant Client directory will be
$HOME/Downloads/instantclient_19_8
. You could move it to some place convenient.Add links to
~/lib
or/usr/local/lib
to enable dbt to find the libraries.mkdir ~/lib
ln -s ~/instantclient_19_8/libclntsh.dylib ~/lib/
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
- Using Database hostname
- Using TNS net service name
- Using Connect string
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_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
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
net_service_name=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=dbhost.example.com)(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=orclpdb1)))
The net_service_name
can be defined as environment variable and referred in profiles.yml
export DBT_ORACLE_TNS_NAME=net_service_name
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
The connection string identifies which database service to connect to. It can be one of the following
- An Oracle Easy Connect String
- An Oracle Net Connect Descriptor String
- A Net Service Name mapping to a connect descriptor
export DBT_ORACLE_CONNECT_STRING="(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbhost.example.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orclpdb1)))"
dbt_test:
target: "{{ env_var('DBT_TARGET', 'dev') }}"
outputs:
dev:
type: oracle
user: "{{ env_var('DBT_ORACLE_USER') }}"
pass: "{{ env_var('DBT_ORACLE_PASSWORD') }}"
database: "{{ env_var('DBT_ORACLE_DATABASE') }}"
schema: "{{ env_var('DBT_ORACLE_SCHEMA') }}"
connection_string: "{{ env_var('DBT_ORACLE_CONNECT_STRING') }}"
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 servicessqlnet.ora
- Configures Oracle Network settingscwallet.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.
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