Oracle Data Source Connections

To connect to Oracle, you must provide an Oracle JDBC driver and specify the driver.jar.path and driver.class.name properties. Okera tries to download the JAR files on the fly for the first time from the valid driver.jar.path. If in Amazon S3, this path/file should have appropriate IAM credentials for Okera to connect and download the file. For any other URI, this file should be downloadable.

Once you've uploaded the custom Oracle driver, you can create a connection to your Oracle database. Here is a programmatic example of a connection to an Oracle database.

CREATE DATACONNECTION oracle_connection CXNPROPERTIES
(
  'connection_type'='JDBC',
  'jdbc_driver'='oracle:thin',
  'host'='<Oracle host to connect to>',
  'port'='<Oracle port to connect to, typically 1521',
  'user_key'='awssm://my-username',
  'password_key'='awssm://my-password',
  'jdbc.db.name'='<default db>',
  'connection_properties'='{"driver.jar.path":"s3://path/to/ojdbc8.jar","driver.class.name":"oracle.jdbc.driver.OracleDriver"}'
);

Enable Query Pushdown

To enable transparent query pushdown, add oracle:thin to the list of enabled engines in the OKERA_CTE_REWRITE_ENABLED_ENGINES configuration setting. Multiple engines can be enabled (in addition to the ones enabled by default) by providing them as comma-separated values in this configuration setting.

For example:

OKERA_CTE_REWRITE_ENABLED_ENGINES: dremio:direct,redshift,oracle:thin