Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Introduction 

A plugin that can efficiently export data from Oracle to be used as a source in Hydrator.  Oracle includes command line tools to export data  that can be utilized to perform this task.  

 

Use-case
A Hydrator User would like to incorporate export oracle data into a pipeline using a source onto hdfs using an action plugin that does not require a JDBC connection to perform the export from Oracle.

User Stories

  • As a Hydrator User I want to export data from Oracle to be used in my hydrator Pipeline.
  • As a Hydrator User I want a Oracle Export plugin that exports data efficiently using existing existing Oracle tools. 
  • As a Hydrator User I want the export data capability of the Oracle plugin to be based on a sql query that I issue.  
  • User should be able to specify credentials.
  • Passwords should not be viewable in plain text from inside pipeline viewer or hydrator studio.  
  • User should be able to specify Oracle Instance
  • User should be able to specify location of EXP Utility.   
  • User should be able to specify type of output.
  • User should be able to specify location of output files.  
  • User should know of connectivity errors, or malformed queries/output identifier.   

Example

User wants to export the data from test table using filter on name='cask' i.e Select * from test where name='cask'

Plugin configurations:

"oracleServerHostname":

"example.com",


"oracleServerPort":

"22",


"serverUsername":

"oracle",


"serverPassword":

"oracle@123",


"dbUsername":

"system",


"dbPassword":

"cask",


"oracleHome":

"/u01/app/oracle/product/11.2.0/xe",


"oracleSID":

"cask",


"queryToExecute":

"select

*

from

test

where

name='cask'"

"columnSeparator"

:

","

                   

"outputFilename" : "results.csv"

                   

"destinationDirectory" : "file:///home/cdap/export/"

Prerequisites on the DB server:

1.Directory should be configured on the server where we want to place the export files.

Steps could be:

CREATE OR REPLACE DIRECTORY test_dir AS '/u01/app/oracle/oradata/';
GRANT READ, WRITE ON DIRECTORY test_dir TO oracle;

2.param.par file should be already present and should contain below:

DIRECTORY = test_dir
DUMPFILE = table.dmp
TABLES = test
QUERY = test:"WHERE name = 'cask'"

3.On execution of plugin,table.dmp would be stored in the test_dir.

Implementation Tips

Design 

Design:

Code Block
{
    "name": "OracleExportAction",
      "plugin": {
        "name": "OracleExportAction",
        "type": "action",
        "label": "OracleExportAction",
        "artifact": {
          "name": "core-plugins",
          "version": "1.4.0-SNAPSHOT",
          "scope": "SYSTEM"
      },
      "properties": {
          "oracleServerHostname": "example.com",
          "oracleServerPort": "22",
          "serverUsername": "oracle",
          "serverPassword": "oracle@123",
          "dbUsername": "system",
          "dbPassword": "cask",
          "oracleHome": "/u01/app/oracle/product/11.2.0/xe",
          "oracleSID": "cask",
          "queryToExecute": "select * from test where name='cask'"
          "columnSeparator" : ","
          "paramFilePathoutputFilename" : "/parameters/param.par"results.csv"
       }   "destinationDirectory" : "file:///home/cdap/export/"
      }
}
oracleServerHostname:Host name of the remote DB machine where the data dump command is to be executed
oracleServerPort:Port of the remote DB machine to connect to.Defaults to 22
serverUsername:User name used to connect to remote DB host
serverPassword:Password used to connect to remote DB host
dbUsername:User name to connect to oracle DB
dbPassword:Password to connect to oracle DB
oracleHome:Path of the ORACLE_HOME
oracleSID:Oracle SIDparamFilePath
queryToExecute:Full pathQuery ofto thebe PARAMexecuted file.Paramto fileexport should contain required parameters to run data dump command. e.g If you wantthe data.
columnSeparator: Column separator to be used in the output file.
outputFilename: Output file name in which to export the data.
destinationDirectory: fromAbsolute testpath tableof usingdirectory filteron forthe namecurrent column-
machine where DIRECTORYthe = test_dir(This should be already configured and should have required access)
DUMPFILE = table.dmp(dump file name)
TABLES = test(tables on which data export needs to be done)
QUERY = test:"WHERE name = 'cask'"file is to be copied. (Can be local or hdfs.)

 

Plugin would perform below steps:

1.SSH to the box using the provided $serverUsername and $userPassword.

2.Run below commands:

a.export ORACLE_HOME = $oracleHome;

b.export ORACLE_SID = $oracleSID

c. Export file on the remote machine using spool

Eg- 

set colsep ","
set linesize 9999
set trimspool on
set heading off
set pagesize 0
set wrap off
set feedback off
spool /tmp/results.csv
select * from test where name='cask';
spool off

d. The plugin would internally create a temporary sql file with the above contents and run the bellow command:

Eg - "echo \"@/tmp/test.sql"+"\" | $oracleHome+/bin/sqlplus system/
./expdp $dbUsername/$dbUserpassword  parfile=$paramFilePathOn comepletion of above commands,dump file would be generated in the DIRECTORY specified in param
cask@cask";

NOTE : Cannot directly use spool in sqlplus prompt since it prints the query as well along with the data in the final output file.

e. Copy the exported file from the remote machine to the folder specified by the user on the current machine.

f. Remove temporary .sql and dump file.

 

 

Table of Contents

Table of Contents
stylecircle

Checklist

  •  User stories documented 
  •  User stories reviewed 
  •  Design documented 
  •  Design reviewed 
  •  Feature merged 
  •  Examples and guides 
  •  Integration tests 
  •  Documentation for feature 
  •  Short video demonstrating the feature