Support Questions

Find answers, ask questions, and share your expertise

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

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?


The error was resolved!

New Contributor

How? i have the same problem...

Super 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.