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

[#1]

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

id,password,last_login,is_deleted,created_at,modified_at,email,customer_id,phone,image_url,is_staff,is_email_verified,user_type,address,company_location,company_name,dob,first_name,last_name,redeem_points,membership_id,is_active,segment,otp_pin_id,password_otp_verified,register_otp_verified,transit_points

1111,vadfva/advasdvdv/avdva/adv=,,f,2017-06-02 11:59:00.543744+05:30,2017-06-02 11:59:00.543799+05:30,asdf@DFASDF.com,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(QueryDatabaseTable.java:305)
        at org.apache.nifi.controller.repository.StandardProcessSession.write(StandardProcessSession.java:2529)
        at org.apache.nifi.processors.standard.QueryDatabaseTable.onTrigger(QueryDatabaseTable.java:299)
        at org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1120)
        at org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:147)
        at org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:47)
        at org.apache.nifi.controller.scheduling.QuartzSchedulingAgent$2.run(QuartzSchedulingAgent.java:165)
        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
        at java.util.concurrent.FutureTask.run(FutureTask.java:266)
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180)
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
        at java.lang.Thread.run(Thread.java:748)
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(JdbcCommon.java:564)
        at org.apache.nifi.processors.standard.util.JdbcCommon.convertToAvroStream(JdbcCommon.java:242)
        at org.apache.nifi.processors.standard.QueryDatabaseTable.lambda$onTrigger$13(QueryDatabaseTable.java:303)
        ... 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:

https://community.hortonworks.com/questions/36464/how-to-use-nifi-to-incrementally-ingest-data-from....

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?

[#2]

The error was resolved!

[#3]

How? i have the same problem...

[#4]

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.