Support Questions
Find answers, ask questions, and share your expertise

Error while using putHive processor

Error while using putHive processor

I am trying to move a table from oracle to hive. After referring some usecases here, I used QueryDatabaseTable -> convertAvroToOrc -> putHdfs -> ReplaceText -> putHiveQL processors for my usecase. but the flow is failing at putHive processor. I am getting the following exception:

Caused by: org.apache.hive.service.cli.HiveSQLException: Error while compiling statement: FAILED: ParseException line 1:0 cannot recognize input near 'ORC' 'P' '"\nNIFI1NIFI3P\00NIFI1NIFI2NIFI3\00\n\00\n\n\n\00 Asia/Kolkata\n\nP\00\n"' at org.apache.hive.service.cli.operation.Operation.toSQLException(Operation.java:315) ~[hive-service-1.2.1.jar:1.2.1] at org.apache.hive.service.cli.operation.SQLOperation.prepare(SQLOperation.java:112) ~[hive-service-1.2.1.jar:1.2.1] at org.apache.hive.service.cli.operation.SQLOperation.runInternal(SQLOperation.java:181) ~[hive-service-1.2.1.jar:1.2.1] at org.apache.hive.service.cli.operation.Operation.run(Operation.java:257) ~[hive-service-1.2.1.jar:1.2.1] at org.apache.hive.service.cli.session.HiveSessionImpl.executeStatementInternal(HiveSessionImpl.java:419) ~[hive-service-1.2.1.jar:1.2.1] at org.apache.hive.service.cli.session.HiveSessionImpl.executeStatementAsync(HiveSessionImpl.java:406) ~[hive-service-1.2.1.jar:1.2.1]

PS: Table in oracle has just one column (varchar(100)) with 3 records only and values (NIFI1, NIFI2, NIFI3)

4 REPLIES 4

Re: Error while using putHive processor

Which Hive processor are you using, PutHiveQL or PutHiveStreaming?

If you are using PutHiveQL, the content of your flow file should be a Hive QL statement, and it looks like your content is ORC format.

If you are using PutHiveStreaming, the content of your flow file should be Avro.

Re: Error while using putHive processor

I am using PutHiveQL processor. How can I make sure that the content my flow file is a Hive QL statement? Also, if I use the QueryDatabaseTable -> PutHiveQL flow for my usecase would it work? And what processor should I use to create flow files with HiveQL? I thought that PutHiveQL processor takes flow files with records as input and converts them into HiveQL statements.

Re: Error while using putHive processor

You have to create the HiveQL statements using ReplaceText.

A scenario using QueryDatabaseTable might be the following:

- Avro coming out of QueryDatabaseTable

- ConvertAvroToJson to get the data into JSON

- SplitJson so that you get one json document per flow file

- EvaluateJsonPath to extract the fields you want to use in the HiveQL statement

- ReplaceText processor that replaces the content of the flow file with something like "INSERT INTO my_table VALUES(?, ?, ?)"

The PutHiveQL processor expects two properties per column:

hiveql.args.N.value
hiveql.args.N.type

Where N is the column index, value is the value for the column and type is the JDBC type.

So you would probably use an UpdateAttribute processor to rename some of the attributes extract earlier into the appropriate hiveql.args.N.value and add the corresponding type attributes.

Documentation for PutHiveQL is here:

https://nifi.apache.org/docs/nifi-docs/components/org.apache.nifi.processors.hive.PutHiveQL/index.ht...

Re: Error while using putHive processor

Explorer

@Karthik Manchala

Since you have already put the ORC file in HDFS, update the replace text procesor "Replacement Value" to somthing like this

LOAD DATA INPATH '${absolute.hdfs.path}' INTO TABLE <TABLE_NAME>

By doing this you will, update the flow file contents to contain the hive ddl statement which will then be used by the putHiveQL processor to insert the data into hive