Export selective columns from hive to Oracle table using Sqoop

To export selected columns into target Oracle table from Hive table, below are the detailed steps:

1. You need to create a hive table with all the selected columns that you are planning to export to the target oracle table with any delimiter for the fields.
2. Please be aware of any primary key columns that exists in the target table.
3. If you are exporting primay key columns as well, please make sure they are not having any duplicate values for those column.
4. If you are not exporting any primary key columns, then you need to ensure that the primary key columns is defined as autoincrement. You either need to create a trigger on that table such that whenever the other columns are inserted, this primary key column is autoincremented or the columns should be defined as Identity field where it increments automatically(This is supported in latest versions of Oracle 12).
5. The Username to connect the oracle database from sqoop should have all the required privileges to connect. The access levels required are detailed in the apache sqoop dcoumentation at https://sqoop.apache.org/docs/1.4.5/SqoopUserGuide.html#_export_data_into_oracle

Sqoop Command:


sqoop export -D 'oraoop.oracle.append.values.hint.usage=ON' -D 'oraoop.oracle.session.initialization.statements=alter session force parallel dml;alter session disable parallel query;' '-Dmapred.map.tasks.speculative.execution=false' '-Doraoop.nologging=true' --direct --connect jdbc:oracle:thin:@//HOSTNAME:PORT/SERVICE NAME --columns col1,col2,col3,...,coln --export-dir /hive/hive-database.db/hivetablename --table TARGET-TABLE --input-fields-terminated-by FIELD-DELIMITER --input-lines-terminated-by '\n' --input-null-non-string '\\N' --input-null-string '\\N' --username USERNAME -P


Thanks for looking my blog. Have a great day !!!!

Java UDFs with Distributed Cache in Apache Pig

Sometimes, you might have to place the files in Distributed Cache and access them through Java UDFs. I would like to explain the steps for doing that in detail.


You have to identify the files you need to place in Distributed Cache and define them in Pig script as below. Here am placing two files file1, file2 in Distibuted Cache and naming them with aliases as input1 (#input1) and input2(#input2). This is the Script from which you would be accessing your Java UDF as well.


SET mapred.cache.files <Path to file1>/file1#input1,<path to file2>file2#input2;

You should also register the Java UDF Jar file from which you need to access these files in the same pig script.


REGISTER /pathtoudflib/pigudf.jar;


When you define this way, the files will be placed in distributed cache and available for accessing in the Java UDF.


In you Java UDF, you can access these files as below:

FileReader frinput1 = new FileReader("./input1");
FileReader frinput2 = new FileReader("./input2");
BufferedReader brin1 = new BufferedReader(frinput1);
BufferedReader brin2 = new BufferedReader(frinput2);

Now you can read each file, and do further processing.

Thanks for looking myblog !!! Have a good day !!!