Search code examples
apache-nifi

Nifi: Failed to perform ExecuteSQL due to illegal character in SQL query

I have a NIFI flow which has 

...->ExecuteSQL->CovertRecord->..

While processing my flow, it gets stuck at ExecuteSQL processor. My ExecuteSQL runs a query something like this:

select f_id,UDF_MD5('J=iDl47av=L'||foo||'%Xh)R/ljFrp') from bar

ERROR [Timer-Driven Process Thread-10] o.a.nifi.processors.standard.ExecuteSQL ExecuteSQL[id=5ea0e2b3-0af5-3e47-6d53-964fe8cbf9f7] ExecuteSQL[id=5ea0e2b3-0af5-3e47-6d53-964fe8cbf9f7] failed to process session due to org.apache.avro.SchemaParseException: Illegal character in: udf_md5((('J=iDl47av=L'||foo)||'%Xh)R/ljFrp')); Processor Administratively Yielded for 1 sec: org.apache.avro.SchemaParseException: Illegal character in: udf_md5((('J=iDl47av=L'||foo)||'%Xh)R/ljFrp')) org.apache.avro.SchemaParseException: Illegal character in: udf_md5((('J=iDl47av=L'||foo)||'%Xh)R/ljFrp'))

I believe it has to do something with the Avro naming specification for the udf_md5 attribute. May I know the work around for the same?

Solution

  • Try with alias name for the udf_md5 function column and try to run the query again.

    Query:

    select f_id,UDF_MD5('J=iDl47av=L'||foo||'%Xh)R/ljFrp') as udf_md from bar