DB Export Oracle
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
- 1 Introduction
- 2 User Stories
- 2.1 Example
- 3 Design
Checklist
Comments
1) Yes this should be an action plugin. In case that user is going to dump the data, action type plugin will be suitable for the task. However in the introduction "data from Oracle to be used as a source in Hydrator." seems a little confusing as this would not be a batch source.
2) It is not clear how are you going to use .par file.
Will the file reside on the cluster running cdap or the oracle server?
In case of file residing on the cdap cluster, How are you planning to scp it to oracle cluster to be able to use it? Also, If the user has to scp .par file by himself, why would he need a separate oracle dump plugin, why he just wouldn`t use ssh action plugin.
In general I did not like the idea of having the configuration distributed among the plugin configuration and .par file. It would be more intuitive for user to have a configuration at single place. And if .par file has some configuration that should be static and should not change with every query.
3) If we need to execute the dump command on oracle db server than we have to get the ssh parameters. I can see some of the code from ssh action could be reused and may be this plugin can extend ssh action.
4) & 5) IMO the user should be able to dump the file on hdfs server. So that the file can be visible to other plugins.
.par file is supposed to reside on Oracle server.we have mentioned this approach since it is recommended by Oracle to use .par file while export where complex queries are involved to avoid character handling issues.
https://docs.oracle.com/cd/B19306_01/server.102/b14215/dp_export.htm
About4) We assume dump file should reside on Oracle server as the file is supposed to be used by import Oracle plugin which is lined up for future.Import plugin would need the dump file to be situated on the DB server itself.
Other than import,we see no usage of dump file to other plugins.
@abhinavc Assuming above points,We have come up with the design.Please review.
Introduction – This is not clear from this introduction if this is a batch type plugin or action type plugin.
use case – Please provide an end to end use case, that reflects what kind of data user wants to fetch? How will the data flow from the pipeline? where will the data end up? Why/how will this be useful for users perspective?
Could you please get this addressed from product team as use case and user stories are mentioned on WIKI as part of plugin requirements referring to which we start design ?
@abhinavc
Please review the updated design using spool command line utility of oracle.
@abhinavc
We have gone through the user stories and below are some notes.Please review and confirm/correct.
1.As mentioned in the description mentioned in the plugin matrix document,Oracle-Export would be an action plugin that will use Oracle native tool to dump data(Data dump expdp) from the Oracle table in bulk. The plugin will always SSH to a box to run the dump command
2.User will need to specify a .par file where in all the parameters would be configured.The use of parameter files is highly recommended if we are using parameters whose values require the use of quotation marks which is the case in query parameter.
e.g.User wants to export the data for the query:select * from test where name='cask'
Command :expdp system/cask parfile=param.par
param.par would contain:
DIRECTORY = test_dir(This should be already configured and should have required access)
DUMPFILE = table.dmp(dump file name)
LOGFILE = table_lg.log
TABLES = test(tables on which data export needs to be done)
QUERY = test:"WHERE name = 'cask'"
3.User would provide the credentials of Oracle DB server for ssh access.
4.Possible type of output would be a dump file at the specified location of the oracle db server.
5.Do we need to support external table dump as well ?