Support Questions

Find answers, ask questions, and share your expertise
Announcements
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

avatar
Rising Star

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?


qstn109.png
3 REPLIES 3

avatar
Rising Star

The error was resolved!

avatar
New Contributor

How? i have the same problem...

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