Skip to main content
ram_
ram_
Silver ⭐️⭐️
October 13, 2021
Question

Sqoop from Postgre to Hive and convert to ORC format

I'm trying to sqoop some tables from Postgres to Hive and convert them to ORC while sqooping.

 

1. I used the below command.

sqoop import --hcatalog-home /usr/hdp/current/hive-webhcat --connect jdbc:postgresql://<host>/<database> --username user --password pwd --table table --hcatalog-database default --hcatalog-table table  --hcatalog-storage-stanza "stored as orcfile" --create-hcatalog-table -m 1

 

I was getting the below error:

ERROR tool.ImportTool: Import failed: org.apache.hive.hcatalog.common.HCatException : 2016 : Error operation not supported : Store into a transactional table default.agent from Pig/Mapreduce is not supported

 

2. I changed the sqoop to look like this.

sqoop import --hcatalog-home /usr/hdp/current/hive-webhcat --connect jdbc:postgresql://<host>/<database> --username user --password pwd --table table --hcatalog-database default --hcatalog-table table  --hcatalog-storage-stanza "stored as orcfile tblproperties (\"transactional\"=\"false\")" --create-hcatalog-table -m 1

 

Error:

INFO hcat.SqoopHCatUtilities: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:Table default.duplicates failed strict managed table checks due to the following reason: Table is marked as a managed table but is not transactional.)
 

3. and I added the below after some googling,

sqoop import --hcatalog-home /usr/hdp/current/hive-webhcat --connect jdbc:postgresql://<host>/<database> --username user --password pwd --table table --hcatalog-database default --hcatalog-table table  --hcatalog-storage-stanza "stored as orcfile tblproperties (\"transactional\"=\"false\")" --create-hcatalog-table --hcatalog-external-table -m 1

 

sqoop import --hcatalog-home /usr/hdp/current/hive-webhcat --connect jdbc:postgresql://<host>/<database> --username user --password pwd --table table --hcatalog-database default --hcatalog-external-table table  --hcatalog-storage-stanza "stored as orcfile tblproperties (\"transactional\"=\"false\")" --create-hcatalog-table -m 1

 

I tried both given above,

 

This is the Error:

ERROR tool.BaseSqoopTool: Unrecognized argument: --hcatalog-external-table
 
 
Now, I don't know what to do.. Please help me out here.

 

4 replies

ShankerSharma
Gold ⭐️
ShankerSharma
Gold ⭐️
October 14, 2021

Hello,

 

Please try to run the sqoop command as below and let us know how it goes:-

 

$ sqoop import --connect jdbc:mysql://localhost/employees --username hive --password hive --table departments --hcatalog-database default --hcatalog-table my_table_orc --create-hcatalog-table --hcatalog-storage-stanza "stored as orcfile"

 

 

Translate
ram_
ram_Author
Silver ⭐️⭐️
October 14, 2021

Hi COE,

I don't get what you changed,

But, I got the below error

 

ERROR tool.ImportTool: Import failed: No primary key could be found for table dup_07jul. Please specify one with --split-by or perform a sequential import with '-m 1'.

 

after adding '-m 1', I got

 

ERROR tool.ImportTool: Import failed: org.apache.hive.hcatalog.common.HCatException : 2016 : Error operation not supported : Store into a transactional table default.duplicates_1 from Pig/Mapreduce is not supported

 

 

Translate
ShankerSharma
Gold ⭐️
ShankerSharma
Gold ⭐️
October 14, 2021

Hello,

 

Please try the below command and let us know:-

 

$ sqoop import --connect jdbc:mysql://localhost/employees --username hive --password hive --table departments --hcatalog-database default --hcatalog-table my_table_orc --create-hcatalog-table --hcatalog-storage-stanza --hcatalog-storage-stanza "stored as orc tblproperties (\"transactional\"=\"false\")"

 

If it doesn’t work then the workaround is the two step process.

1. Create the ORC table in hive with the keyword external and set transactional to false

2. Then use the sqoop command to load the data into the orc table.

Translate
Terms & ConditionsAccessibility statement