I'm working with Nifi and I would like to transfer data from a postgresql's table to Json files.
I have a table in postgresql database with schema:
CREATE TABLE "public"."users" (
"id" int4 NOT NULL DEFAULT nextval('users_id_seq'::regclass),
"email" varchar(255) COLLATE "pg_catalog"."default" NOT NULL DEFAULT ''::character varying,
"settings" "public"."hstore" NOT NULL DEFAULT ''::hstore,
"credited_at" timestamp(6)
);
CREATE INDEX "index_users_on_settings" ON "public"."users" USING gist (
"settings" "public"."gist_hstore_ops"
);
ALTER TABLE "public"."users" ADD CONSTRAINT "users_pkey" PRIMARY KEY ("id");
I tried to get data from this user table to json files by Nifi with data flow.
Main Nifi flow:
Fetch Data from user table and move to Nifi RGP:
Get data and run ExecuteSQL:
Error when start ExecuteSQL:
And I got the error with Avro's schema
ExecuteSQL[id=1f22e067-0163-1000-ffff-ffff9c047041] ExecuteSQL[id=1f22e067-0163-1000-ffff-ffff9c047041] failed to process session due to createSchema: Unknown SQL type 1111 / hstore (table: users, column: settings) cannot be converted to Avro type; Processor Administratively Yielded for 1 sec: java.lang.IllegalArgumentException: createSchema: Unknown SQL type 1111 / hstore (table: users, column: settings) cannot be converted to Avro type
I tried to search a solution on google but there aren't any way to fix this. Could you please help me? Thank you.
This is my stacktrace from nifi-app.log
2018-05-03 04:54:43,418 ERROR [Timer-Driven Process Thread-6] o.a.nifi.processors.standard.ExecuteSQL ExecuteSQL[id=1f22e067-0163-1000-ffff-ffff9c047041] ExecuteSQL[id=1f22e067-0163-1000-ffff-ffff9c047041] failed to process session due to java.lang.IllegalArgumentException: createSchema: Unknown SQL type 1111 / hstore (table: users, column: settings) cannot be converted to Avro type; Processor Administratively Yielded for 1 sec: java.lang.IllegalArgumentException: createSchema: Unknown SQL type 1111 / hstore (table: users, column: settings) cannot be converted to Avro type java.lang.IllegalArgumentException: createSchema: Unknown SQL type 1111 / hstore (table: users, column: settings) cannot be converted to Avro type at org.apache.nifi.processors.standard.util.JdbcCommon.createSchema(JdbcCommon.java:621) at org.apache.nifi.processors.standard.util.JdbcCommon.convertToAvroStream(JdbcCommon.java:282) at org.apache.nifi.processors.standard.ExecuteSQL.lambda$onTrigger$1(ExecuteSQL.java:245) at org.apache.nifi.controller.repository.StandardProcessSession.write(StandardProcessSession.java:2621) at org.apache.nifi.processors.standard.ExecuteSQL.onTrigger(ExecuteSQL.java:235) at org.apache.nifi.processor.AbstractProcessor.onTrigger(AbstractProcessor.java:27) at org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1147) at org.apache.nifi.controller.tasks.ConnectableTask.invoke(ConnectableTask.java:175) at org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:117) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:748) 2018-05-03 04:54:43,418 WARN [Timer-Driven Process Thread-6] o.a.n.controller.tasks.ConnectableTask Administratively Yielding ExecuteSQL[id=1f22e067-0163-1000-ffff-ffff9c047041] due to uncaught Exception: java.lang.IllegalArgumentException: createSchema: Unknown SQL type 1111 / hstore (table: users, column: settings) cannot be converted to Avro type java.lang.IllegalArgumentException: createSchema: Unknown SQL type 1111 / hstore (table: users, column: settings) cannot be converted to Avro type at org.apache.nifi.processors.standard.util.JdbcCommon.createSchema(JdbcCommon.java:621) at org.apache.nifi.processors.standard.util.JdbcCommon.convertToAvroStream(JdbcCommon.java:282) at org.apache.nifi.processors.standard.ExecuteSQL.lambda$onTrigger$1(ExecuteSQL.java:245) at org.apache.nifi.controller.repository.StandardProcessSession.write(StandardProcessSession.java:2621) at org.apache.nifi.processors.standard.ExecuteSQL.onTrigger(ExecuteSQL.java:235) at org.apache.nifi.processor.AbstractProcessor.onTrigger(AbstractProcessor.java:27) at org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1147) at org.apache.nifi.controller.tasks.ConnectableTask.invoke(ConnectableTask.java:175) at org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:117) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:748) 2018-05-03 04:54:43,501 INFO [Flow Service Tasks Thread-1] o.a.nifi.controller.StandardFlowService Saved flow controller org.apache.nifi.controller.FlowController@65ee54e8 // Another save pending = false 2018-05-03 04:54:44,423 ERROR [Timer-Driven Process Thread-9] o.a.nifi.processors.standard.ExecuteSQL ExecuteSQL[id=1f22e067-0163-1000-ffff-ffff9c047041] ExecuteSQL[id=1f22e067-0163-1000-ffff-ffff9c047041] failed to process session due to java.lang.IllegalArgumentException: createSchema: Unknown SQL type 1111 / hstore (table: users, column: settings) cannot be converted to Avro type; Processor Administratively Yielded for 1 sec: java.lang.IllegalArgumentException: createSchema: Unknown SQL type 1111 / hstore (table: users, column: settings) cannot be converted to Avro type java.lang.IllegalArgumentException: createSchema: Unknown SQL type 1111 / hstore (table: users, column: settings) cannot be converted to Avro type at org.apache.nifi.processors.standard.util.JdbcCommon.createSchema(JdbcCommon.java:621) at org.apache.nifi.processors.standard.util.JdbcCommon.convertToAvroStream(JdbcCommon.java:282) at org.apache.nifi.processors.standard.ExecuteSQL.lambda$onTrigger$1(ExecuteSQL.java:245) at org.apache.nifi.controller.repository.StandardProcessSession.write(StandardProcessSession.java:2621) at org.apache.nifi.processors.standard.ExecuteSQL.onTrigger(ExecuteSQL.java:235) at org.apache.nifi.processor.AbstractProcessor.onTrigger(AbstractProcessor.java:27) at org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1147) at org.apache.nifi.controller.tasks.ConnectableTask.invoke(ConnectableTask.java:175) at org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:117) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:748) 2018-05-03 04:54:44,423 WARN [Timer-Driven Process Thread-9] o.a.n.controller.tasks.ConnectableTask Administratively Yielding ExecuteSQL[id=1f22e067-0163-1000-ffff-ffff9c047041] due to uncaught Exception: java.lang.IllegalArgumentException: createSchema: Unknown SQL type 1111 / hstore (table: users, column: settings) cannot be converted to Avro type java.lang.IllegalArgumentException: createSchema: Unknown SQL type 1111 / hstore (table: users, column: settings) cannot be converted to Avro type at org.apache.nifi.processors.standard.util.JdbcCommon.createSchema(JdbcCommon.java:621) at org.apache.nifi.processors.standard.util.JdbcCommon.convertToAvroStream(JdbcCommon.java:282) at org.apache.nifi.processors.standard.ExecuteSQL.lambda$onTrigger$1(ExecuteSQL.java:245) at org.apache.nifi.controller.repository.StandardProcessSession.write(StandardProcessSession.java:2621) at org.apache.nifi.processors.standard.ExecuteSQL.onTrigger(ExecuteSQL.java:235) at org.apache.nifi.processor.AbstractProcessor.onTrigger(AbstractProcessor.java:27) at org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1147) at org.apache.nifi.controller.tasks.ConnectableTask.invoke(ConnectableTask.java:175) at org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:117) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:748) 2018-05-03 04:54:45,427 ERROR [Timer-Driven Process Thread-9] o.a.nifi.processors.standard.ExecuteSQL ExecuteSQL[id=1f22e067-0163-1000-ffff-ffff9c047041] ExecuteSQL[id=1f22e067-0163-1000-ffff-ffff9c047041] failed to process session due to java.lang.IllegalArgumentException: createSchema: Unknown SQL type 1111 / hstore (table: users, column: settings) cannot be converted to Avro type; Processor Administratively Yielded for 1 sec: java.lang.IllegalArgumentException: createSchema: Unknown SQL type 1111 / hstore (table: users, column: settings) cannot be converted to Avro type java.lang.IllegalArgumentException: createSchema: Unknown SQL type 1111 / hstore (table: users, column: settings) cannot be converted to Avro type at org.apache.nifi.processors.standard.util.JdbcCommon.createSchema(JdbcCommon.java:621) at org.apache.nifi.processors.standard.util.JdbcCommon.convertToAvroStream(JdbcCommon.java:282) at org.apache.nifi.processors.standard.ExecuteSQL.lambda$onTrigger$1(ExecuteSQL.java:245) at org.apache.nifi.controller.repository.StandardProcessSession.write(StandardProcessSession.java:2621) at org.apache.nifi.processors.standard.ExecuteSQL.onTrigger(ExecuteSQL.java:235) at org.apache.nifi.processor.AbstractProcessor.onTrigger(AbstractProcessor.java:27) at org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1147) at org.apache.nifi.controller.tasks.ConnectableTask.invoke(ConnectableTask.java:175) at org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:117) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:748)
settingscolumn is of a non-standard type. Can you share the full stacktrace fromnifi-app.log? – Sivaprasanna Sethuramansettingscolumn, but I don't know how to fix it. I updated stacktrace on my post. – Wilson Ho