Starburst Oracle connector#

The Oracle connector allows querying and creating tables in an external Oracle database. This can be used to join data between different systems like Oracle and Hive, or between different Oracle database instances.

Note

The Starburst Oracle connector requires a valid Starburst Enterprise Presto license.

Configuration#

To configure the Oracle connector, create a catalog properties file in etc/catalog named, for example, oracle.properties, to mount the Oracle connector as the oracle catalog. Create the file with the following contents, replacing the connection properties as appropriate for your setup:

connector.name=oracle
connection-url=jdbc:oracle:thin:@HOST:PORT:SID
connection-user=USERNAME
connection-password=PASSWORD

Catalog files have to be located on all nodes and usage requires a restart.

The Oracle connector automatically uses connection pooling by default as a performance improvement.

The default values, listed below, are typically a suitable configuration. They can be updated by changing the properties in the catalog configuration file:

oracle.connection-pool.max-size=30
oracle.connection-pool.min-size=1
oracle.connection-pool.inactive-timeout=20m

If desired, connection pooling can also be disabled.

oracle.connection-pool.enabled=false

The connector supports parallel read operations to improve performance. By default, parallelism is disabled with the value NO_PARALLELISM. You can enable it, by changing the value of the oracle.parallelism-type property:

oracle.parallelism-type=PARTITIONS

The configuration property oracle.parallel.max-splits-per-scan, or the catalog session property max_splits_per_scan, can be used to limit the maximum number of splits per table, when a partition table is accessed in parallel and processed with multiple splits.

Multiple Oracle Databases#

If you want to connect to multiple Oracle Databases, configure another instance of the Oracle plugin as a separate catalog.

To add another Oracle catalog, simply add another properties file. For example, if you name the property file sales.properties, Presto creates a catalog named sales.

Kerberos Authentication#

The Oracle connector supports Kerberos-based authentication with the following configuration:

oracle.authentication.type=KERBEROS
kerberos.client.principal=example@example.com
kerberos.client.keytab=etc/kerberos/example.keytab
kerberos.config=etc/kerberos/krb5.conf

With this configuration the user example@example.com, defined in the principal property, is used to connect to the database, and the related Kerberos service ticket is located in the example.keytab file.

Kerberos Credential Passthrough#

The Oracle connector can be configured to pass through Kerberos credentials, received by Presto, to the Oracle database.

Configure Kerberos and Presto, following the instructions in Kerberos Credential Passthrough.

Then configure the connector to pass through the credentials from the server to the database in your catalog properties file and ensure the Kerberos client configuration properties are in place.

oracle.authentication.type=KERBEROS_PASS_THROUGH
http.authentication.krb5.config=/etc/krb5.conf
http-server.authentication.krb5.service-name=exampleServiceName
http-server.authentication.krb5.keytab=/path/to/Keytab/File

Now any database access via Presto is subject to the data access restrictions and permissions of the user supplied via Kerberos.

Querying Oracle Databases#

The Oracle Database connector provides a schema for each Oracle Database user’s schema. Assuming the catalog name is oracle, you can see the available schemas by running SHOW SCHEMAS:

SHOW SCHEMAS FROM oracle;

If you have an Oracle Database user named web, you can view the tables they own by running SHOW TABLES:

SHOW TABLES FROM oracle.web;

You can see a list of the columns in the clicks table in web’s schema using either of the following:

DESCRIBE oracle.web.clicks;
SHOW COLUMNS FROM oracle.web.clicks;

Finally, you can access the clicks table in web’s schema:

SELECT * FROM oracle.web.clicks;

Your privileges in these schemas are those of the user configured in the connection properties file. If the user does not have access to these tables, you will not be able to access them.

Mapping Data Types Between Presto and Oracle#

Because Presto and Oracle each support types that the other does not, the following type mapping tables apply.

Type Mapping Properties#

Configuration property name

Session property name

Description

Default

unsupported-type.handling-strategy

unsupported_type_handling_strategy

Configures how unsupported column data types are handled:

  • IGNORE - column is not accessible.

  • CONVERT_TO_VARCHAR - column is converted to unbounded VARCHAR.

IGNORE

oracle.number.default-scale

number_default_scale

Default Presto DECIMAL scale for Oracle NUMBER (without precision and scale) date type. When not set then such column is treated as not supported.

not set

oracle.number.rounding-mode

number_rounding_mode

Rounding mode for the Oracle NUMBER data type. This is useful when Oracle NUMBER data type specifies higher scale than is supported in Presto. Possible values are:

  • UNNECESSARY - Rounding mode to assert that the requested operation has an exact result, hence no rounding is necessary.

  • CEILING - Rounding mode to round towards positive infinity.

  • FLOOR - Rounding mode to round towards negative infinity.

  • HALF_DOWN - Rounding mode to round towards nearest neighbor unless both neighbors are equidistant, in which case rounding down is used.

  • HALF_EVEN - Rounding mode to round towards the nearest neighbor unless both neighbors are in which case rounding towards the nearest neighbor is performed.

  • HALF_UP - Rounding mode to round towards nearest neighbor unless both neighbors are equidistant, in which case rounding up is used

  • UP - Rounding mode to round towards zero.

  • DOWN - Rounding mode to round towards zero.

UNNECESSARY

Oracle to Presto Type Mapping#

Presto supports selecting the following Oracle Database types. The table shows the mappings from Oracle to Presto data types.

Oracle to Presto Type Mapping#

Oracle database type

Presto type

Notes

NUMBER(p, s)

DECIMAL(p, s)

See Mapping Numeric Types

NUMBER(p)

DECIMAL(p, 0)

See Mapping Numeric Types

FLOAT[(p)]

DOUBLE

BINARY_FLOAT

REAL

BINARY_DOUBLE

DOUBLE

VARCHAR2(n CHAR)

VARCHAR(n)

VARCHAR2(n BYTE)

VARCHAR(n)

NVARCHAR2(n)

VARCHAR(n)

CHAR(n)

CHAR(n)

NCHAR(n)

CHAR(n)

CLOB

VARCHAR

NCLOB

VARCHAR

RAW(n)

VARBINARY

BLOB

VARBINARY

DATE

TIMESTAMP

See Mapping Datetime Types

TIMESTAMP(p)

TIMESTAMP

See Mapping Datetime Types

TIMESTAMP(p) WITH TIME ZONE

TIMESTAMP WITH TIME ZONE

See Mapping Datetime Types

If an Oracle table contains a type not listed in this table:

  • and unsupported-type.handling-strategy equals FAIL (default configuration) any query on such table will fail.

  • and unsupported-type.handling-strategy equals IGNORE (default configuration) a column won’t be accessible from Presto.

  • and unsupported-type.handling-strategy equals CONVERT_TO_VARCHAR (default configuration) a column value will be converted to unbounded VARCHAR.

Presto to Oracle Type Mapping#

Presto supports creating tables with the following types into an Oracle Database. The table shows the mappings from Presto to Oracle data types.

Presto to Oracle Type Mapping#

Presto type

Oracle database type

Notes

TINYINT

NUMBER(3)

SMALLINT

NUMBER(5)

INTEGER

NUMBER(10)

BIGINT

NUMBER(19)

DECIMAL(p, s)

NUMBER(p, s)

REAL

BINARY_FLOAT

DOUBLE

BINARY_DOUBLE

VARCHAR

NCLOB

VARCHAR(n)

VARCHAR2(n CHAR) or NCLOB

See Mapping Character Types

CHAR(n)

CHAR(n CHAR) or NCLOB

See Mapping Character Types

VARBINARY

BLOB

DATE

DATE

See Mapping Datetime Types

TIMESTAMP

TIMESTAMP(3)

See Mapping Datetime Types

TIMESTAMP WITH TIME ZONE

TIMESTAMP(3) WITH TIME ZONE

See Mapping Datetime Types

Any Presto type not listed in this table may not be written using the Oracle connector.

Mapping Numeric Types#

An Oracle NUMBER(p, s) is mapped to Presto’s DECIMAL(p, s) except under these conditions:

  • No precision is specified for the column (e.g. NUMBER or NUMBER(*)), unless oracle.number.default-scale is set.

  • Scale (s) is greater than precision.

  • Precision (p) is greater than 38.

  • Scale is negative and the difference between p and s is greater than 38, unless oracle.number.rounding-mode is set to other value than UNNECESSARY.

When column meet any of these conditions:
  • and unsupported-type.handling-strategy equals FAIL (default configuration) any query on such table will fail.

  • and unsupported-type.handling-strategy equals IGNORE (default configuration) a column won’t be accessible from Presto.

  • and unsupported-type.handling-strategy equals CONVERT_TO_VARCHAR (default configuration) a column value will be converted to unbounded VARCHAR.

If s is negative, NUMBER(p, s) is mapped to DECIMAL(p + s, 0).

For Oracle NUMBER (without precision and scale), user may want to oracle.number.default-scale=s, then column is mapped to DECIMAL(38, s).

Mapping Datetime Types#

Selecting a timestamp with fractional second precision (p) greater than 3 truncates (not rounds) the fractional seconds to three digits.

Oracle DATE values may store hours, minutes, and seconds, so they are mapped to TIMESTAMP in Presto.

Warning

Because of differences in date and time libraries used by Presto and the Oracle JDBC driver, attempting to insert or select a datetime value earlier than 1582-10-15 will result in an incorrect date being inserted.

Mapping Character Types#

Presto’s VARCHAR(n) maps to VARCHAR2(n CHAR) if n is no greater than 4000. A larger or unbounded VARCHAR maps to NCLOB.

Presto’s CHAR(n) maps to CHAR(n CHAR) if n is no greater than 2000. A larger CHAR maps to NCLOB.

Using CREATE TABLE AS to create an NCLOB column from a CHAR value will remove the trailing spaces from the initial values for the column. Inserting CHAR values into existing NCLOB columns will keep the trailing spaces. For example:

presto> CREATE TABLE vals AS SELECT CAST('A' as CHAR(2001)) col;
presto> INSERT INTO vals (col) VALUES (CAST('BB' as CHAR(2001)));
presto> SELECT LENGTH(col) FROM vals;
2001
1

Note

This behaviors above may be subject to change in a future version.

Note

Attempting to write a CHAR that does not fit in the column’s actual size will fail. This is also true for the equivalent VARCHAR types.

User Impersonation#

Oracle connector supports user impersonation.

User impersonation can be enabled in the catalog file:

oracle.impersonation.enabled=true

User impersonation in Oracle connector is based on creating proxy user accounts and authorizing users to connect through them in Oracle Database. For more details visit: docs.oracle.com.

Table Statistics#

Oracle connector supports table and column statistics, as documented in Table Statistics. The statistics are collected by the Oracle database and retrieved by the connector. To collect statistics for a table, execute the following statement in Oracle Database.

EXECUTE DBMS_STATS.GATHER_TABLE_STATS('USER_NAME', 'TABLE_NAME');

Refer to Oracle documentation for additional options and instructions how to invoke a procedure when not using SQL*Plus.

The table and column statistics can be viewed in Presto using SHOW STATS and are used for Cost based optimizations.

Table Statistics Configuration Properties#

Property name

Description

Default

statistics.enabled

Enables table and column statistics

true

statistics.cache-ttl

Duration for which table and column statistics are cached.

0s

statistics.cache-missing

Cache the fact that table statistics were not available.

false

Synonyms#

Due the performance reasons support for Oracle SYNONYM is disabled by default. It can be turned on with:

oracle.synonyms.enabled=true

Pushdown#

The connector supports pushdown for optimized query processing.

Additionally, it supports pushdown of the following aggregate functions over DOUBLE type columns:

Limitations#

Presto supports connecting to Oracle Database 12c.

The following SQL statements are not yet supported: