Sunday 25 December 2016

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 !!!!

No comments:

Post a Comment