12.17. 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.

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.

Manual Connector Configuration (Using Presto-Admin)

Create the catalog properties file in ~/.prestoadmin/catalog and refer to catalog add for adding a catalog using presto-admin.

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.

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.
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.

Any Oracle type not listed in this table may not be selected using the Oracle Database connector and won’t be visible to the user.

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(*)).
  • 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.

Columns that meet any of these conditions will be ignored.

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

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: