13.18. Oracle Connector

The Oracle Database 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

This connector is bundled in Presto Enterprise and requires a license from Starburst. For more information about Presto Enterprise and Oracle connector or to obtain a free trial, please contact hello@starburstdata.com.

Installation and Configuration

Prerequisites

Any of the below installation and configuration methods require the following artifacts:

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

Plugin Configuration

To install the Oracle Database connector manually, use the following directions. You will need an installation of the Starburst Distribution of Presto and an Oracle JDBC Driver (ojdbc8.jar).

  1. Add the Oracle JDBC Driver JAR to the Presto plugin/oracle directory.
  2. Add Presto Oracle catalog properties file (eg. oracle.properties for a Presto catalog named oracle).
  3. Perform the above steps on every Presto cluster node.
  4. Restart Presto on every node.

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 to ~/.prestoadmin/catalog with a different name (making sure it ends in .properties). For example, if you name the property file sales.properties, Presto will create a catalog named sales.

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.

Configuration

Configuration Property Name Sessison Property name Description Default
unsupported-type.handling-strategy unsupported_type_handling_strategy
Configures how unsupported column data types should be handled:
  • FAIL - query that is reading such column will fail.
  • IGNORE - column will not be accessible from Presto.
  • CONVERT_TO_VARCHAR - column will converted to unbounded VARCHAR.
 FAIL
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 will be treated as not supported. not set
oracle.number.rounding-mode number_rounding_mode

Rounding mode for 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 round down.
  • HALF_EVEN - Rounding mode to round towards the nearest neighbor unless both neighbors are equidistant, in which case, round towards the even neighbor.
  • HALF_UP - Rounding mode to round towards nearest neighbor unless both neighbors are equidistant, in which case round up.
  • 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 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 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 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 Database connector.

Mapping Numeric Types

An Oracle NUMBER(p, s) will be 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. in results.
  • 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) will be mapped to DECIMAL(p + s, 0).

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

Mapping Datetime Types

Selecting a timestamp with fractional second precision (p) greater than 3 will truncate (not round) 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. Please see User Impersonation for more details.

User impersonation can be turned with:

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 please visit: docs.oracle.com.

Table Statistics

Oracle connector supports table and column statistics. The statistics are collected by 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');

Please 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

Oracle Database Connector Limitations

Presto supports connecting to Oracle Database 12c.

Unsupported Statements

The following SQL statements are not yet supported: