Oracle

This topic describes Oracle data source considerations and Dremio configuration.

Connection Information

The following connection information is needed prior to adding Oracle as a data source.

• Hostname or IP
• Port
• Site Identifier (SID) of the Oracle server

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

Predicate Pushdowns

Dremio delegates the execution of these expressions and functions to the database being queried, often dramatically improving query performance. It can also offload entire SQL queries that include one or more of these expressions and functions.

*, +, -, /, %
<, <=, <>, =, >, >=, !=
AND, NOT, OR, ||
ABS
ACOS
ADD_MONTHS
ASIN
ATAN
ATAN2
AVG
CAST
CEIL
CEILING
CHAR_LENGTH
CHARACTER_LENGTH
CONCAT
COS
COSH
COT
COVAR_POP
COVAR_SAMP
DATE_ADD
DATE_SUB
DATE_TRUNC_DAY
DATE_TRUNC_HOUR
DATE_TRUNC_MINUTE
DATE_TRUNC_MONTH
DATE_TRUNC_QUARTER
DATE_TRUNC_WEEK
DATE_TRUNC_YEAR
DEGREES
E
EXP
EXTRACT_CENTURY
EXTRACT_DAY
EXTRACT_DOW
EXTRACT_DOY
EXTRACT_HOUR
EXTRACT_MINUTE
EXTRACT_MONTH
EXTRACT_QUARTER
EXTRACT_SECOND
EXTRACT_WEEK
EXTRACT_YEAR
FLOOR
IS DISTINCT FROM
IS NOT DISTINCT FROM
IS NOT NULL
IS NULL
LAST_DAY
LCASE
LEFT
LENGTH
LIKE
LN
LOCATE
LOG
LOG10
LOWER
LPAD
LTRIM
MAX
MEDIAN
MIN
MOD
PERCENT_CONT
PERCENT_DISC
PI
POSITION
POW
POWER
RADIANS
REGEXP_LIKE
REPLACE
REVERSE
RIGHT
ROUND
RPAD
RTRIM
SIGN
SIN
SINH
SQRT
STDDEV
STDDEV_POP
STDDEV_SAMP
SUBSTR
SUBSTRING
SUM
TAN
TANH
TO_CHAR
TO_DATE
TRIM
TRUNC
TRUNCATE
UCASE
UPPER
VAR_POP
VAR_SAMP

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.

Initial Connection

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.

Dremio Configuration

General

Host

Name	Description
Host	Oracle host name.
Port	Oracle port number. Defaults to 1521.
Service Name	Service Name of your database.
Encrypt connection	Enables secure connections.

Authentication

• No authentication method
• Master Authentication method (default)
  • Username: Oracle username
  • Password: Oracle password
• Secret Resource Url (provide the username and the Secret Resource URL that allows Dremio to fetch the password from AWS Secrets Manager).
  • Username: Oracle username
  • Secret Resource Url: the Amazon Resource Name (ARN) for the secret (for example, arn:aws:secretsmanager:us-west-2:123456789012:secret:my-rds-secret-VNenFy)

Advanced Options

!

• Use timezone as connection region: If checked, uses timezone to set connection region.
• Include synonyms: If checked, includes synonyms as datasets.
• Map Oracle DATE columns to TIMESTAMP: If selected, the DATE column will display values in timestamp format.
• Record fetch size: Number of records to fetch at once. Set to 0 (zero) to have Dremio automatically decide. Default: 10
• Maximum idle connections: The total number of connections allowed to be idle at a given time. By default, this is set to 8.
• Connection idle time (s): The amount of time (in seconds) allowed for a connection to remain idle before the connection is terminated. By default, this is set to 60.
• Query timeout: The amount of time (in seconds) allowed to wait for the results of a query. If this time expires, the connection being used is returned to an idle state.
• Enable legacy dialect
• Encryption: Provide the SSL/TLS server certificate distinguished name, otherwise, leave blank to disable the DN match.

Reflection Refresh

!

• Never refresh -- Specifies how often to refresh based on hours, days, weeks, or never.
• Never expire -- Specifies how often to expire based on hours, days, weeks, or never.

Metadata

!

Dataset Handling

• Remove dataset definitions if underlying data is unavailable (Default).
  If this box is not checked and the underlying files under a folder are removed or the folder/source is not accessible, Dremio does not remove the dataset definitions. This option is useful in cases when files are temporarily deleted and put back in place with new sets of files.

Metadata Refresh

• Dataset Discovery: Refresh interval for top-level source object names such as names of DBs and tables.
  • Fetch every -- Specify fetch time based on minutes, hours, days, or weeks. Default: 1 hour
• Dataset Details: The metadata that Dremio needs for query planning such as information needed for fields, types, shards, statistics, and locality.
  • Fetch mode -- Specify either Only Queried Datasets, All Datasets, or As Needed. Default: Only Queried Datasets
    • Only Queried Datasets -- Dremio updates details for previously queried objects in a source.
      This mode increases query performance because less work is needed at query time for these datasets.
    • All Datasets -- Dremio updates details for all datasets in a source. This mode increases query performance because less work is needed at query time.
    • As Needed -- Dremio updates details for a dataset at query time. This mode minimized metadata queries on a source when not used, but might lead to longer planning times.
  • Fetch every -- Specify fetch time based on minutes, hours, days, or weeks. Default: 1 hour
  • Expire after -- Specify expiration time based on minutes, hours, days, or weeks. Default: 3 hours

Sharing

!

You can specify which users can edit. Options include:

• All users can edit.
• Specific users can edit.

Oracle TLS Configuration

To use TLS to connect to an Oracle source, do the following:

1. Select the option Connect using SSL/TLS when initially configuring it.
2. 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 Advanced Options.
3. 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:
   1. Import the CA certificate and 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
   2. Add the certificate to a new or existing truststore.
      $ keytool -import -alias oracle-ca -keystore dremio-truststore.jks -file oracle-ca.der
   3. Modify the DREMIO_JAVA_SERVER_EXTRA_OPTS section of the dremio-env configuration file to use the trust store by adding the following:
      Djavax.net.ssl.trustStore=<path/to>/dremio-truststore.jks Djavax.net.ssl.trustStoreType=JKS
Djavax.net.ssl.trustStorePassword=<password>

Running Queries Directly on Oracle Through Dremio

Dremio users can run pass queries through Dremio to run on Oracle. Doing so can sometimes decrease query execution times. For more information, see Querying Relational-Database Sources Directly.

For More Information

• See Oracle Data Types for information about mapping to Dremio data types.