[NIFI] [SelectHiveSQL] failed negative string in the column name

[#1]

Hi all,

I've used SelectHiveSQL to make a select but I got an issue

it seems that the column name "delta-1", it was not correctly set on the select command.

2017-10-20
14:28:05,805 ERROR [Timer-Driven Process Thread-7]
o.a.nifi.processors.hive.SelectHiveQL SelectHiveQL[id=d3043004] 
Unable to execute HiveQL select query select country,year,delta-1 from
table1 for StandardFlowFileRecord[uuid=d1e4662,claim=,offset=0,name=2846304697532889,size=0]
due to org.apache.nifi.processor.exception.ProcessException: org.apache.hive.service.cli.HiveSQLException:
Error while compiling statement: 
FAILED:SemanticException [Error 10004]: Line 1:33 Invalid table alias or column
reference 'delta': (possible column names are: country, year, delta-1, ); routing to failure:
org.apache.nifi.processor.exception.ProcessException:
org.apache.hive.service.cli.HiveSQLException: Error while compiling statement: 

thanks for helps

[#2 · Accepted solution]

@mayki wogno

Can you use hive escape character in your select statement

Hive escape character:-

`

Use the above character to escape from your select statement and try to run select HiveQL again

New Select HIVEQL statement:-

select country,year,`delta-1` from <table>

Select HiveQL Processor config Screenshot:-

[#3]

@Shu : thanks. I have tried with espace caracter, but i've sent it to splitAvro and got the issue with avro.

[#4]

@mayki wogno, We can do alias for the delta-1 column as delta_1 this alias will help you to resolve your issue.

select country,year,`delta-1` as delta_1 from <table>

[#5]

In Apache NiFi 1.5.0 (not yet released at the time of this writing), SelectHiveQL (via NIFI-4473) will have a property to Normalize Names for Avro, so you won't have to do the alias.