Support Questions
Find answers, ask questions, and share your expertise

NiFi ExecuteSQL and ExecuteSqlRecord Both fails to process large query

NiFi ExecuteSQL and ExecuteSqlRecord Both fails to process large query

New Contributor

am running a simple Pivot query in Presto via "ExecuteSqlRecord" processor but the moment I run the processor it gives me the error: Unable to execute SQL select.

It is interesting to see that I can run the same pivot query with ~37 fields in Presto CLI and QueryDatabaseTable successfully! Please help me achieve this in ExecuteSQLRecord processor as it has the added benefit of writing the output flowfile in any desired format (for my case, I am writing the output of the SQL query as CSV)
Here is the full stack trace of the error

java.sql.SQLException: Error executing query. No FlowFile to route to failure: java.sql.SQLException: Error executing query

java.sql.SQLException: Error executing query

at com.facebook.presto.jdbc.PrestoStatement.internalExecute(PrestoStatement.java:279)

at com.facebook.presto.jdbc.PrestoStatement.execute(PrestoStatement.java:228)

at com.facebook.presto.jdbc.PrestoPreparedStatement.<init>(PrestoPreparedStatement.java:80)

at com.facebook.presto.jdbc.PrestoConnection.prepareStatement(PrestoConnection.java:129)

at org.apache.commons.dbcp2.DelegatingConnection.prepareStatement(DelegatingConnection.java:303)

at org.apache.commons.dbcp2.DelegatingConnection.prepareStatement(DelegatingConnection.java:303)

at org.apache.nifi.processors.standard.AbstractExecuteSQL.onTrigger(AbstractExecuteSQL.java:237)

at org.apache.nifi.processor.AbstractProcessor.onTrigger(AbstractProcessor.java:27)

at org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1176)

at org.apache.nifi.controller.tasks.ConnectableTask.invoke(ConnectableTask.java:213)

at org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:117)

at org.apache.nifi.engine.FlowEngine$2.run(FlowEngine.java:110)

at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)

at java.base/java.util.concurrent.FutureTask.runAndReset(FutureTask.java:305)

at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:305)

at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)

at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)

at java.base/java.lang.Thread.run(Thread.java:834)

Caused by: java.lang.RuntimeException: Error fetching next at http://presto.testk8s.content.mywebspace.com:8889/b7/journal/20245300229_0354554_08r940_as8y4/2?slug... returned an invalid response: JsonResponse{statusCode=500, statusMessage=Server Error, headers={connection=[close]}, hasValue=false} [Error: ]

at com.facebook.presto.jdbc.internal.client.StatementClientV1.requestFailedException(StatementClientV1.java:446)

at com.facebook.presto.jdbc.internal.client.StatementClientV1.advance(StatementClientV1.java:386)

at com.facebook.presto.jdbc.PrestoResultSet.getColumns(PrestoResultSet.java:1742)

at com.facebook.presto.jdbc.PrestoResultSet.<init>(PrestoResultSet.java:119)

at com.facebook.presto.jdbc.PrestoStatement.internalExecute(PrestoStatement.java:250)

... 17 common frames omitted

 

 

Can someone help me to solve this issue? Thanks