1

I'm trying insert record with JSON into Postgres table with column type of jsonb using AVROSchemaRegistry and Processor "PutDatabaseRecord". Apache NiFi version: nifi-2.0.0-M2-RC4 Postgres version: 12.12

Processor "PutDatabaseRecord" properties: Processor "PutDatabaseRecord" properties

My AVRO schema:

{
    "name": "ebs_data",
    "namespace": "nifi",
    "type": "record",
    "fields": [
              {
               ***
              },
              {
            "name": "log", 
            "type": {
                "type": "array",
                "items": {
                    "type": "record",
                    "name": "log_rec",
                    "fields": [
                        {"name": "log_level", "type": ["string","null"]},
                        {"name": "log_message", "type": ["string","null"]},
                        {"name": "log_date", "type": ["string","null"]}
                    ]
                }
            }
        }
    ]
}

And I get this stacktrace:

2024-07-29 12:50:29,388 INFO [Timer-Driven Process Thread-2] o.a.n.c.s.TimerDrivenSchedulingAgent Scheduled PutDatabaseRecord[id=1f964dda-cd85-3499-190a-a3724f133e23
] to run with 1 threads
2024-07-29 12:50:29,392 WARN [Timer-Driven Process Thread-2] com.zaxxer.hikari.HikariConfig HikariCPConnectionPool[id=28d9771d-2472-3215-407c-038ac82c0116] - maxLife
time is less than 30000ms, setting to default 1800000ms.
2024-07-29 12:50:29,393 INFO [Timer-Driven Process Thread-2] com.zaxxer.hikari.HikariDataSource HikariCPConnectionPool[id=28d9771d-2472-3215-407c-038ac82c0116] - Sta
rting...
2024-07-29 12:50:29,528 INFO [Timer-Driven Process Thread-2] com.zaxxer.hikari.pool.HikariPool HikariCPConnectionPool[id=28d9771d-2472-3215-407c-038ac82c0116] - Adde
d connection org.postgresql.jdbc.PgConnection@3c556a44
2024-07-29 12:50:29,530 INFO [Timer-Driven Process Thread-2] com.zaxxer.hikari.HikariDataSource HikariCPConnectionPool[id=28d9771d-2472-3215-407c-038ac82c0116] - Sta
rt completed.
2024-07-29 12:50:29,701 ERROR [Timer-Driven Process Thread-2] o.a.n.p.standard.PutDatabaseRecord PutDatabaseRecord[id=1f964dda-cd85-3499-190a-a3724f133e23] Failed to
 put Records to database for StandardFlowFileRecord[uuid=acbcce68-f9f4-45b3-b88d-7e8694167097,claim=StandardContentClaim [resourceClaim=StandardResourceClaim[id=1722
246429765-23, container=default, section=23], offset=9744, length=73727],offset=0,name=349045823834552,size=73727]. Routing to failure.
java.sql.BatchUpdateException: Batch entry 0 INSERT INTO ebs_data.ebs_data.rnd_heap_and_parts (***, log) VALUES (***,('[Ljava.lang.Object;@1d1458e1')) was aborted: ERROR: invalid input syntax for type json
  Detail: Token "Ljava" is invalid.
  Where: JSON data, line 1: [Ljava...  Call getNextException to see other errors in the batch.
        at org.postgresql.jdbc.BatchResultHandler.handleError(BatchResultHandler.java:165)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2413)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:579)
        at org.postgresql.jdbc.PgStatement.internalExecuteBatch(PgStatement.java:912)
        at org.postgresql.jdbc.PgStatement.executeBatch(PgStatement.java:936)
        at org.postgresql.jdbc.PgPreparedStatement.executeBatch(PgPreparedStatement.java:1733)
        at com.zaxxer.hikari.pool.ProxyStatement.executeBatch(ProxyStatement.java:127)
        at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeBatch(HikariProxyPreparedStatement.java)
        at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
        at java.base/java.lang.reflect.Method.invoke(Method.java:580)
        at org.apache.nifi.controller.service.StandardControllerServiceInvocationHandler.invoke(StandardControllerServiceInvocationHandler.java:254)
        at org.apache.nifi.controller.service.StandardControllerServiceInvocationHandler$ProxiedReturnObjectInvocationHandler.invoke(StandardControllerServiceInvocationHandler.java:240)
        at jdk.proxy23/jdk.proxy23.$Proxy206.executeBatch(Unknown Source)
        at org.apache.nifi.processors.standard.PutDatabaseRecord.executeDML(PutDatabaseRecord.java:958)
        at org.apache.nifi.processors.standard.PutDatabaseRecord.putToDatabase(PutDatabaseRecord.java:1140)
        at org.apache.nifi.processors.standard.PutDatabaseRecord.onTrigger(PutDatabaseRecord.java:559)
        at org.apache.nifi.processor.AbstractProcessor.onTrigger(AbstractProcessor.java:27)
        at org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1274)
        at org.apache.nifi.controller.tasks.ConnectableTask.invoke(ConnectableTask.java:244)
        at org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:102)
        at org.apache.nifi.engine.FlowEngine$2.run(FlowEngine.java:110)
        at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:572)
        at java.base/java.util.concurrent.FutureTask.runAndReset(FutureTask.java:358)
        at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:305)
        at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1144)
        at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:642)
        at java.base/java.lang.Thread.run(Thread.java:1583)
Caused by: org.postgresql.util.PSQLException: ERROR: invalid input syntax for type json
  Detail: Token "Ljava" is invalid.
  Where: JSON data, line 1: [Ljava...
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2725)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2412)
        ... 25 common frames omitted
2024-07-29 12:50:29,811 INFO [Flow Service Tasks Thread-1] o.a.nifi.controller.StandardFlowService Saved flow controller org.apache.nifi.controller.FlowController@3230417f // Another save pending = false

What I'm doing wrong?

Expecting successfully inserted record.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.