Support Questions

Find answers, ask questions, and share your expertise
Celebrating as our community reaches 100,000 members! Thank you!

Error in Nifi during database query or conversion of records to Avro with QueryDatabaseTable processor

Rising Star

I have a table in posgres which has a schema and values as follows:


1111,vadfva/advasdvdv/avdva/adv=,,f,2017-06-02 11:59:00.543744+05:30,2017-06-02 11:59:00.543799+05:30,,123455,1234567901,,f,f,general,"{""state"": ""asdsfag"", ""pincode"": ""1234456"", ""address_city"": ""asdffgd"", ""address_line_1"": ""ASDFAFA"", ""address_line_2"": """", ""address_landmark"": """"}",,,1976-12-25 05:30:00+05:30,ASDF,ASDF,0,SD sxvadafc,t,SDFSDF,,f,f,0

When I use QueryDatabase processor where the table does not have complex columns this works, but in the above example, it has a json entry in one of the columns. My guess is that's what triggering error, since the value of address is "jsonb".

The error is as follows:

 ERROR [Timer-Driven Process Thread-2] o.a.n.p.standard.QueryDatabaseTable QueryDatabaseTable[id=9c7e1e9b-1088-115d-c426-d788e86d9ea7] Unable to execute SQL select query SELECT * FROM asdfad due to org.apache.nifi.processor.exception.ProcessException: Error during database query or conversion of records to Avro.: {}
org.apache.nifi.processor.exception.ProcessException: Error during database query or conversion of records to Avro.
        at org.apache.nifi.processors.standard.QueryDatabaseTable.lambda$onTrigger$13(
        at org.apache.nifi.controller.repository.StandardProcessSession.write(
        at org.apache.nifi.processors.standard.QueryDatabaseTable.onTrigger(
        at org.apache.nifi.controller.StandardProcessorNode.onTrigger(
        at org.apache.nifi.controller.scheduling.QuartzSchedulingAgent$
        at java.util.concurrent.Executors$
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(
        at java.util.concurrent.ScheduledThreadPoolExecutor$
        at java.util.concurrent.ThreadPoolExecutor.runWorker(
        at java.util.concurrent.ThreadPoolExecutor$
Caused by: java.lang.IllegalArgumentException: createSchema: Unknown SQL type 1111 / jsonb (table: account_user, column: address) cannot be converted to Avro type
        at org.apache.nifi.processors.standard.util.JdbcCommon.createSchema(
        at org.apache.nifi.processors.standard.util.JdbcCommon.convertToAvroStream(
        at org.apache.nifi.processors.standard.QueryDatabaseTable.lambda$onTrigger$13(
        ... 13 common frames omitted

The QueryDataBaseTable processor is configured as an image attached to this question.

I referred a similar question posted on the forum but no solution from there too, the link to that question is:

Any idea why the error is caused? since it work for the rest of the tables except this once. I tried changing the database type from default to Oracle but it does not help. Do I have a work around for it?


Rising Star

The error was resolved!

New Contributor

How? i have the same problem...

Master Guru

Yes please share any workaround / alternate solution if you have one. Looking at the code, PostgreSQL returns OTHER (1111) for JSON and JSONB types, which we don't currently handle when creating a schema, hence the error. I have written NIFI-5845 to cover this improvement.