Support Questions

Find answers, ask questions, and share your expertise

is Append mode for hive imports supported now in SQOOP?

Master Collaborator

I saw articles from early last year saying its not supported but wondering if its supported now in sqoop 1.4.6 and HDP2.5 ?


Master Collaborator

i tried but its giving me error , how can I have append mode with ORC tables ?

[root@hadoop1 ~]# sqoop job --create incjob  -- import --connect "jdbc:oracle:thin:@(description=(address=(protocol=tcp)(host=patronQA)(port=1526))(connect_data=(service_name=patron)))" --username RON --password xxxx --incremental append --check-column txn_process_date --table PATRON.TAB1 --split-by TAB1.TXN_ID --hcatalog-database default --hcatalog-table PA_LANE_TXN_orc --create-hcatalog-table --hcatalog-storage-stanza 'stored as orc tblproperties ("orc.compress"="SNAPPY")' --map-column-hive 'txn_process_date=date ,ext_date_time=date ,ent_date_time=date' --columns 'TXN_ID,TXN_PROCESS_DATE,TRANSP_ID,TRANSP_CLASS,EXT_PLAZA_ID,EXT_LANE_ID,EXT_LANE_TYPE_CODE,EXT_DATE_TIME,TRANSP_CUR_BAL,AVC_CLASS,TOLL_AMT_CHARGED,TOLL_AMT_COLLECTED,TOLL_AMT_FULL,CREDIT_LIST_UPDATE_FLAG,TRANS_SOURCE,REVCLASS_REV_CLASS_CODE,PAYMENT_METHOD_CODE,ENT_PLAZA_ID,ENT_DATE_TIME,ENT_LANE_ID,ENT_LANE_TYPE_CODE,AGENCY_REJECT_CODE,MSG_ID,TRANSP_INTERNAL_NUM,UFM_PAYMENT_CODE,VEH_LIC_NUM,STATE_ID_CODE,ORIG_TXN_ID'
Warning: /usr/hdp/ does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
17/08/31 09:10:53 INFO sqoop.Sqoop: Running Sqoop version:
17/08/31 09:10:53 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
Append mode for imports is not  compatible with HCatalog. Please remove the parameter--append-mode
[root@hadoop1 ~]#
[root@hadoop1 ~]#

Sqoop import append works properly for normal hive table. But not sure how it works with Hive Partitioned table. Here I guess you are using same hive partitioned table so facing this issue.

@Sami Ahmad : There is another way to perform the same if its ok to update records instead of only append then you can use below command:

--table PARTON.TAB1 --split-by TAB1.TXN_ID --check-column txn_process_date --incremental lastmodified  --last-value 0 --merge-key TXN_ID --hcatalog-database default --hcatalog-table PA_LANE_TXN_orc;

If you don't want to update then you can write a --query to import only new data which will not update old records.

Hope this helps.!

Take a Tour of the Community
Don't have an account?
Your experience may be limited. Sign in to explore more.