Setup and Best Practices
In addition to a hostname or IP and port, you will also need to know the site identifier (SID) of the Oracle server.
Resource Allocation Considerations
For RDBMS sources like Oracle, Dremio's query execution is largely single threaded. This means that for each Oracle directed query, only one Dremio node will experience a computational load. So unlike most other data sources, larger Dremio clusters won't lead to faster individual query execution times. However, if you expect a large number of concurrent queries (as in the case of many simultaneous Dremio users) these will be distributed evenly across the nodes.
NOTE: Ensure that your Dremio cluster has access to the appropriate port for your Oracle source. By default this is port 1521.
Dremio and Oracle
For some operations Dremio can tell the data source to execute that part of the query natively, often dramatically improving performance. These operations are called 'pushdowns.'
Since they share a common language (SQL), Dremio supports most operations as pushdowns in Oracle. These include:
- Filter (SQL:
- Limit (SQL:
- Sorting (SQL:
- Aggregation (SQL:
- Project (with expressions) (e.g. SQL:
SELECT columnA + columnB, columnC, columnD)
NOTE: Since Oracle has no boolean type, project operations that contain SQL expressions which evaluate to true or false (e.g.
SELECT username, friends > 0), and filter operations that include boolean literals in a filter (e.g.
WHERE currentAccount = true) cannot be executed as pushdowns.
Depending on the number of tables in your Oracle source, the final step of adding it to Dremio can take anywhere from a few seconds to a few minutes as the source's metadata is processed. However, this is a one-time cost and further queries to the source will not incur additional metadata reads.
Here are all available source specific options:
|Host||Oracle host name.|
|Port||Oracle port number. Defaults to 1521.|
|Service Name||Service Name of your database.|
|Username||Oracle user name.|
|Record Fetch Size||Record fetch size, use
Oracle TLS Configuration
To use TLS to connect to an Oracle source follow the instructions below.
Select the option
Connect using SSL/TLS when initially configuring it
Optionally add the
Server Certificate Distinguished Name
If you want to ensure the server you’re connecting to be the exact one matching a particular certificate string then add the
Server Certificate Distinguished Name under the
SSL/TLS option in
Add the Certificate Authority certificate to Dremio's trust store
To add the CA certificate that is used to sign the Oracle certificate into Dremio's trust store follow the instructions below.
Import the CA certificate Convert the certificate into DER format (required by Java keytool).
For example, using OpenSSL tool:
$ openssl x509 -outform der -in oracle-ca.pem -out oracle-ca.der
Add the certificate to a new or existing truststore
$ keytool -import -alias oracle-ca -keystore dremio-truststore.jks -file oracle-ca.der
DREMIO_JAVA_SERVER_EXTRA_OPTSsection of the
dremio.envconfiguration file to use the trust store by adding the following: