Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 21 Next »

Introduction 

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

 

Use-case
A Hydrator User would like to export oracle data onto hdfs or local file system 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",
"oracleServerUsername": "oracle",
"oracleServerPassword": "oracle@123",
"dbUsername": "system",
"dbPassword": "cask",
"oracleHome": "/u01/app/oracle/product/11.2.0/xe",
"oracleSID": "cask",
"queryToExecute": "select * from test where name='cask';"
"pathToWriteFinalOutput" : "/tmp/data.csv"
"format" : "csv"

Implementation Tips

Design 

Design:

Oracle export json
{
    "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",
          "oracleServerUsername": "oracle",
          "oracleServerPassword": "oracle@123",
          "dbUsername": "system",
          "dbPassword": "cask",
          "oracleHome": "/u01/app/oracle/product/11.2.0/xe",
          "oracleSID": "cask",
          "queryToExecute": "select * from test where name='cask';"
          "pathToWriteFinalOutput" : "/tmp/data.csv"
          "format" : "csv"
      }
}
oracleServerHostname:Hostname of the remote DB machine 
oracleServerPort:Port of the remote DB machine.Defaults to 22
oracleServerUsername:Username for remote DB host
oracleServerPassword:Password for remote DB host
dbUsername:Username to connect to oracle DB
dbPassword:Password to connect to oracle DB
oracleHome:Path of the ORACLE_HOME
oracleSID:Oracle SID
queryToExecute: Query to be executed to export.Query should have ';' at the end.
pathToWriteFinalOutput: Path where output file to be exported
format: Format of the output file

 

Plugin would run below sequence of commands in one session:

1.export ORACLE_HOME and ORACLE_SID

2.create a script file(/tmp/test.sql) and add below content.We can take the path of the tmp file as a config from the user or use the home folder of the logged in user where program would always have the access.

set colsep ","
set linesize 10000
set newpage none
set wrap off
set heading off
spool on
select * from test;
spool off
exit

3.execute $oracleHome/bin/sqlplus -s $dbUsername/$dbPassword@$oracleSID  @/tmp/test.sql

4.Read the outstream and write into the specified output file on local.Before writing,multiline trailing spaces remover regex will be applied.

5.Since sqlplus spool generates trailing spaces before and after the column separators,sed command will be applied to remove the spaces.

 

Table of Contents

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

 

  • No labels