question Re: Oracle to Hive table in Archives of Support Questions (Read Only)

Oracle to Hive table

tyas_saleh — Sun, 18 Aug 2019 05:04:22 GMT

Hi I'm new to Nifi,

I'm trying to do a simple data migration from oracle database to Hive table using Nifi. I'm using Nifi 1.4 version.

I have also read some tutorial from here and some other thread from this forum. Any pointer, thanks.

Re: Oracle to Hive table

mburgess — Sat, 26 May 2018 06:42:07 GMT

Your PutHDFS processor is placing the data into Hadoop (in ORC format after ConvertAvroToORC) for use by Hive, so you don't also need to send an INSERT statement to PutHiveQL. Rather with the pattern you're using, you should have ReplaceText setting the content to a Hive DDL statement to create a table on top of the ORC file(s) location, or a LOAD DATA INPATH to load from the HDFS location into an existing Hive table.

Re: Oracle to Hive table

tyas_saleh — Sat, 26 May 2018 12:52:54 GMT

Thanks Matt for the pointer. I have change the replacetext with "Load data inpath..". Now I'm having issue that the Nifi using anonymous user and access denied to move the file.

error-permission-denied.png

Re: Oracle to Hive table

tyas_saleh — Sun, 18 Aug 2019 05:03:53 GMT

I got it fix the write permission. Now the orc file able to load to hive table but only into one column only. The source data contain 8 column and 14 row only. The result into hive table only into one column and it is in loop.

Re: Oracle to Hive table

mburgess — Sun, 27 May 2018 10:36:02 GMT

What does your CREATE TABLE statement look like, and does it match the schema of the file(s) (Avro/ORC) you're sending to the external location?

Re: Oracle to Hive table

tyas_saleh — Sun, 27 May 2018 13:51:50 GMT

It works fine now I change within the replacetext to ${hive.ddl} location '${absolute.hdfs.path}'