Oracle database plugin

Owned by Illia
Last updated: Jul 05, 2019 by Vlad Hlinskiy
3 min read

Introduction

A separate database plugin to support Oracle\-specific features and configurations.

Use-Case

  • Users can choose and install Oracle source and sink plugins.
  • Users should see Oracle logo on plugin configuration page for better experience.
  • Users should get relevant information from the tool tip:
    • The tool tip for the connection string should be customized specifically to the Oracle database,
    • The tool tip should describe accurately what each field is used for.
  • Users should not have to specify any redundant configuration (ex: JDBC type in source plugin, columns in the sink plugin).
  • Users should get field level lineage for the source and sink that is being used.
  • Reference documentation should be updated to account for the changes.
  • The source code for Oracle database plugin should be placed in repo under data-integrations org.
  • Integration tests for Oracle database plugin should be  placed in repo under  data-integrations org.
  • The data pipeline using source and sink plugins should run on both mapreduce and spark engines.

User Stories

  • User should be able to install Oracle specific database source and sink plugins from the Hub
  • Users should have each tool tip accurately describe what each field does
  • Users should get field level lineage information for the Oracle source and sink
  • Users should be able to setup a pipeline avoiding specifying redundant information
  • Users should get updated reference document for Oracle source and sink
  • Users should be able to read all the DB types

Plugin Type

  • Batch Source
  • Batch Sink 
  • Real-time Source
  • Real-time Sink
  • Action
  • Post-Run Action
  • Aggregate
  • Join
  • Spark Model
  • Spark Compute

Design Tips

Oracle connector reference: https://www.oracle.com/technetwork/database/application-development/jdbc/downloads/index.html

Existing database plugins: https://github.com/cdapio/hydrator-plugins/tree/develop/database-plugins

Oracle datatypes mappings and conversions:


Oracle has two types of driver thin and oci.

The JDBC Thin client is a pure Java, Type IV driver. It is lightweight and easy to install. More https://docs.oracle.com/cd/E11882_01/java.112/e16548/jdbcthin.htm#JJDBC28195

Oci driver requires native libraries to be installed, but provides some additional features like OCI Connection Pooling, Client Result Cache etc.

More https://www.oracle.com/database/technologies/appdev/oci.html,

https://docs.oracle.com/cd/E11882_01/java.112/e16548/instclnt.htm#JJDBC28218


Also oracle support tnsnames.ora file on client machine. More https://docs.oracle.com/database/121/NETRF/tnsnames.htm#NETRF007

Design

The suggestion is to create maven submodule oracle-plugin under database-plugins repo.


Sink Properties

User Facing NameTypeDescriptionConstraints
LabelString Label for UI
Reference NameStringUniquely identified name for lineage
HostStringOracle hostRequired (defaults to localhost on UI)
PortNumberSpecific port where Oracle running on

Optional

(default 1521)

SIDStringSID name to connectRequired
Service nameStringService name to connectRequired
UsernameStringDB usernameRequired
PasswordPasswordUser passwordRequired
Connection ArgumentsKeyvalue

A list of arbitrary string tag/value pairs as connection arguments, list of properties

https://docs.oracle.com/cd/E11882_01/appdev.112/e13995/oracle/jdbc/OracleDriver.html


Table NameStringName of a database table to write to
Driver typeSelectOracle driver typePossible values (thin, oci)

Source Properties


User Facing NameTypeDescriptionConstraints
LabelStringLabel for UI
Reference NameStringUniquely identified name for lineage
HostStringOracle hostRequired (defaults to localhost on UI)
PortNumberSpecific port where Oracle running on

Optional

(default 1521)
SIDStringSID name to connectRequired
Service nameStringService name to connectRequired
Import QueryStringQuery for import dataValid SQL query
UsernameStringDB usernameRequired
PasswordStringUser passwordRequired
Bounding QueryStringReturns max and min of split-By FiledValid SQL query
Split-By Field NameStringField name which will be used to generate splits
Number of Splits to GenerateNumberNumber of splits to generate
Connection ArgumentsKeyvalueA list of arbitrary string tag/value pairs as connection arguments, list of properties https://docs.oracle.com/cd/E11882_01/appdev.112/e13995/oracle/jdbc/OracleDriver.html
Driver typeSelectOracle driver typePossible values (thin, oci)


Action Properties


User Facing NameTypeDescriptionConstraints
LabelStringLabel for UI
HostStringOracle hostRequired (defaults to localhost on UI)
PortNumberSpecific port where Oracle running on

Optional

(default 1521)
SIDStringSID name to connectRequired
Service nameStringService name to connectRequired
Username

String

DB usernameRequired
PasswordStringUser passwordRequired
Connection ArgumentsKeyvalue

A list of arbitrary string tag/value pairs as connection arguments, list of properties 

https://docs.oracle.com/cd/E11882_01/appdev.112/e13995/oracle/jdbc/OracleDriver.html


Database CommandStringDatabase command to runValid SQL query
Driver typeSelectOracle driver typePossible values (thin, oci)


Data Types Mapping

Oracle Data TypeCDAP Schema Data TypeSupportComment
VARCHAR2Schema.Type.STRING+
NVARCHAR2Schema.Type.STRING+
VARCHARSchema.Type.STRING+
NUMBERSchema.LogicalType.DECIMAL+
FLOATSchema.Type.DOUBLE+FLOAT(126) by default value is represented internally as NUMBER
LONGSchema.Type.STRING+Character data of variable length.
DATESchema.LogicalType.TIMESTAMP_MICROS+
BINARY_FLOATSchema.Type.FLOAT+
BINARY_DOUBLESchema.Type.DOUBLE+
TIMESTAMPSchema.LogicalType.TIMESTAMP_MICROS+
TIMESTAMP WITH TIME ZONESchema.LogicalType.TIMESTAMP_MICROS*Currently converted to UTC time, modifying original time zone.
TIMESTAMP WITH LOCAL TIME ZONESchema.LogicalType.TIMESTAMP_MICROS+
INTERVAL YEAR TO MONTHSchema.Type.STRING+
INTERVAL DAY TO SECONDSchema.Type.STRING+
RAWSchema.Type.BYTES+
LONG RAWSchema.Type.BYTES+
ROWIDSchema.Type.STRING+
UROWIDSchema.Type.STRING+
CHARSchema.Type.STRING+
NCHARSchema.Type.STRING+
CLOBSchema.Type.STRING+
NCLOBSchema.Type.STRING+
BLOBSchema.Type.BYTES+
BFILESchema.Type.BYTES*Deprecated by Oracle in java api, added mapping to Type.BYTES



Approach

Create a module oracle-plugin in database-plugins project, reuse existing database-plugins code if possible. Add Oracle-specific properties to configuration, add support for Oracle-specific datatypes. Update UI widgets JSON definitions.

The default driver should be used for connection to oracle, otherwise user should connect via generic-database plugin.

Pipeline Samples


API changes

Deprecated Programmatic APIs

database-plugins is moved to Data Integrations

UI Impact or Changes

Configurable database properties are presented as named text fields instead of arbitrary key value pairs. Oracle source and sink are separate entries with Oracle logo in source and sink lists.

Test Scenarios

TODO

Releases

Release X.Y.Z

Related Work

Database plugin enhancements

Future work

MSSQL database plugin


Created in 2020 by Google Inc.