Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Sqoop Import Oracle table to hive with ORC ?

Highlighted

Sqoop Import Oracle table to hive with ORC ?

New Contributor

I imported Oracle table using Hcatalog to import as ORC with below Syntax. In every run it appends data instead of overwrite. Is this any options to avoid this append ?

sqoop import \

--connect "jdbc:oracle:thin:@${ORACLE_TNS_STRING}" \

--username $USER \

--password $PASSWD \

--table tab1 \

--hcatalog-database hive_db \

--hcatalog-table hive_table \

--hcatalog-storage-stanza "stored as orc tblproperties ("orc.compress"="SNAPPY")" \

--m 1


1 REPLY 1

Re: Sqoop Import Oracle table to hive with ORC ?

Super Guru

@Amod Acharya

Create a shell script, that will truncate your hive orc table and then sqoop import to hive orc table.

Sample shell script:-

1.For Managed(internal) Tables:

bash$ cat sq.sh
#truncate the hive table first
hive -e "truncate table default.my_table_orc"
#sqoop import into Hive table
sqoop import --connect jdbc:mysql://localhost:3306/<db_name> --username root --password "<password>" --table <table_name>  --hcatalog-database <hive_database> --hcatalog-table <hive_table_name> --hcatalog-storage-stanza "stored as orcfile"

2.For External Tables:

bash$ cat sq.sh 
#drop the hive table first
hive -e "drop table default.my_table_orc"
#sqoop import into Hive table
sqoop import --connect jdbc:mysql://localhost:3306/<db_name> --username root --password "<password>" --table order_items  --hcatalog-database <hive_database> --hcatalog-table <hive_table_name> --create-hcatalog-table --hcatalog-storage-stanza "stored as orcfile"