Hi ,
I am trying to insert data by using spark sql but statment fails .
This is the spark code:
dfDremioLakehouse = spark.read
.format(“jdbc”)
.option(“url”, “jdbc:dremio:direct=localhost:31010”)
.option(“driver”,“com.dremio.jdbc.Driver”)
.option(“dbtable”, “lakehouse.lakehouse.cust5”)
.option(“user”, “aaaa”)
.option(“password”, “bbbb”)
.load()
dfDremioLakehouse.createOrReplaceTempView(“lakehousecustomer”)
aaa=1
spark.sql(f"insert into lakehousecustomer(id) values({aaa})")
the error message is:
py4j.protocol.Py4JJavaError: An error occurred while calling o27.sql.
: org.apache.spark.SparkException: Job aborted due to stage failure: Task 0 in stage 2.0 failed 1 times, most recent failure: Lost task 0.0 in stage 2.0 (TID 2) (LAPTOP-1Q5UUL23 executor driver): java.sql.SQLException: Failed to create prepared statement: error_id: “e7f1760e-5495-4fe0-8754-e26f8b815d27”
endpoint {
address: “09da6f90f02d”
user_port: 31010
fabric_port: 45678
roles {
sql_query: true
java_executor: true
master: true
}
startTime: 1702665338469
max_direct_memory: 8589934592
available_cores: 12
105: “”
106: 36313
203: “24.2.6-202311250456170399-68acbe47”
}
error_type: VALIDATION
message: “VALIDATION ERROR: Illegal use of dynamic parameter\n\nstartLine 1\nstartColumn 54\nendLine 1\nendColumn 54\nSQL Query INSERT INTO lakehouse.lakehouse.cust5 ("id") VALUES (?)\n\n[Error Id: e7f1760e-5495-4fe0-8754-e26f8b815d27 on 09da6f90f02d:31010]\n\n (org.apache.calcite.runtime.CalciteContextException) At line 1, column 54: Illegal use of dynamic parameter\n sun.reflect.NativeConstructorAccessorImpl.newInstance0():-2\n sun.reflect.NativeConstructorAccessorImpl.newInstance():62\n sun.reflect.DelegatingConstructorAccessorImpl.newInstance():45\n java.lang.reflect.Constructor.newInstance():423\n org.apache.calcite.runtime.Resources$ExInstWithCause.ex():463\n org.apache.calcite.sql.SqlUtil.newContextException():791\n org.apache.calcite.sql.SqlUtil.newContextException():776\n org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError():4951\n org.apache.calcite.sql.validate.SqlValidatorImpl.inferUnknownTypes():1807\n org.apache.calcite.sql.validate.SqlValidatorImpl.inferUnknownTypes():1886\n org.apache.calcite.sql.validate.SqlValidatorImpl.validateValues():4839\n org.apache.calcite.sql.validate.TableConstructorNamespace.validateImpl():59\n org.apache.calcite.sql.validate.AbstractNamespace.validate():84\n org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace():976\n org.apache.calcite.sql.validate.AbstractNamespace.getRowType():115\n org.apache.calcite.sql.validate.TableConstructorNamespace.getRowType():28\n org.apache.calcite.sql.validate.AbstractNamespace.getType():126\n org.apache.calcite.sql.validate.TableConstructorNamespace.getType():28\n org.apache.calcite.sql.validate.SqlValidatorImpl.getValidatedNodeTypeIfKnown():1590\n org.apache.calcite.sql.validate.SqlValidatorImpl.getValidatedNodeType():1572\n com.dremio.exec.planner.sql.SqlValidatorImpl.validate():123\n com.dremio.exec.planner.sql.SqlValidatorAndToRelContext.validate():80\n com.dremio.exec.planner.sql.handlers.SqlToRelTransformer.validateNode():161\n com.dremio.exec.planner.sql.handlers.SqlToRelTransformer.validateAndConvert():137\n com.dremio.exec.planner.sql.handlers.SqlToRelTransformer.validateAndConvert():105\n com.dremio.exec.planner.sql.handlers.query.DataAdditionCmdHandler.getPlan():160\n com.dremio.exec.planner.sql.handlers.query.InsertTableHandler.doInsert():110\n com.dremio.exec.planner.sql.handlers.query.InsertTableHandler.getPlan():76\n com.dremio.exec.planner.sql.handlers.commands.HandlerToPreparePlanBase.plan():91\n com.dremio.exec.work.foreman.AttemptManager.plan():571\n com.dremio.exec.work.foreman.AttemptManager.lambda$run$4():462\n com.dremio.service.commandpool.ReleasableBoundCommandPool.lambda$getWrappedCommand$3():140\n com.dremio.service.commandpool.CommandWrapper.run():70\n com.dremio.context.RequestContext.run():109\n com.dremio.common.concurrent.ContextMigratingExecutorService.lambda$decorate$4():226\n com.dremio.common.concurrent.ContextMigratingExecutorService$ComparableRunnable.run():206\n java.util.concurrent.Executors$RunnableAdapter.call():511\n java.util.concurrent.FutureTask.run():266\n java.util.concurrent.ThreadPoolExecutor.runWorker():1149\n java.util.concurrent.ThreadPoolExecutor$Worker.run():624\n java.lang.Thread.run():750\n Caused By (org.apache.calcite.sql.validate.SqlValidatorException) Illegal use of dynamic parameter\n sun.reflect.NativeConstructorAccessorImpl.newInstance0():-2\n sun.reflect.NativeConstructorAccessorImpl.newInstance():62\n sun.reflect.DelegatingConstructorAccessorImpl.newInstance():45\n java.lang.reflect.Constructor.newInstance():423\n org.apache.calcite.runtime.Resources$ExInstWithCause.ex():463\n org.apache.calcite.runtime.Resources$ExInst.ex():572\n org.apache.calcite.sql.SqlUtil.newContextException():791\n org.apache.calcite.sql.SqlUtil.newContextException():776\n org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError():4951\n org.apache.calcite.sql.validate.SqlValidatorImpl.inferUnknownTypes():1807\n org.apache.calcite.sql.validate.SqlValidatorImpl.inferUnknownTypes():1886\n org.apache.calcite.sql.validate.SqlValidatorImpl.validateValues():4839\n org.apache.calcite.sql.validate.TableConstructorNamespace.validateImpl():59\n org.apache.calcite.sql.validate.AbstractNamespace.validate():84\n org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace():976\n org.apache.calcite.sql.validate.AbstractNamespace.getRowType():115\n org.apache.calcite.sql.validate.TableConstructorNamespace.getRowType():28\n org.apache.calcite.sql.validate.AbstractNamespace.getType():126\n org.apache.calcite.sql.validate.TableConstructorNamespace.getType():28\n org.apache.calcite.sql.validate.SqlValidatorImpl.getValidatedNodeTypeIfKnown():1590\n org.apache.calcite.sql.validate.SqlValidatorImpl.getValidatedNodeType():1572\n com.dremio.exec.planner.sql.SqlValidatorImpl.validate():123\n com.dremio.exec.planner.sql.SqlValidatorAndToRelContext.validate():80\n com.dremio.exec.planner.sql.handlers.SqlToRelTransformer.validateNode():161\n com.dremio.exec.planner.sql.handlers.SqlToRelTransformer.validateAndConvert():137\n com.dremio.exec.planner.sql.handlers.SqlToRelTransformer.validateAndConvert():105\n com.dremio.exec.planner.sql.handlers.query.DataAdditionCmdHandler.getPlan():160\n com.dremio.exec.planner.sql.handlers.query.InsertTableHandler.doInsert():110\n com.dremio.exec.planner.sql.handlers.query.InsertTableHandler.getPlan():76\n com.dremio.exec.planner.sql.handlers.commands.HandlerToPreparePlanBase.plan():91\n com.dremio.exec.work.foreman.AttemptManager.plan():571\n com.dremio.exec.work.foreman.AttemptManager.lambda$run$4():462\n com.dremio.service.commandpool.ReleasableBoundCommandPool.lambda$getWrappedCommand$3():140\n com.dremio.service.commandpool.CommandWrapper.run():70\n com.dremio.context.RequestContext.run():109\n com.dremio.common.concurrent.ContextMigratingExecutorService.lambda$decorate$4():226\n com.dremio.common.concurrent.ContextMigratingExecutorService$ComparableRunnable.run():206\n java.util.concurrent.Executors$RunnableAdapter.call():511\n java.util.concurrent.FutureTask.run():266\n java.util.concurrent.ThreadPoolExecutor.runWorker():1149\n java.util.concurrent.ThreadPoolExecutor$Worker.run():624\n java.lang.Thread.run():750\n”
exception {
exception_class: “org.apache.calcite.runtime.CalciteContextException”
message: “At line 1, column 54: Illegal use of dynamic parameter”
stack_trace {
class_name: “…”
line_number: 0
method_name: “…”
is_native_method: false
}
stack_trace {
class_name: “org.apache.calcite.runtime.Resources$ExInstWithCause”
file_name: “Resources.java”
line_number: 463
method_name: “ex”
is_native_method: false
}
stack_trace {
class_name: “org.apache.calcite.sql.SqlUtil”
file_name: “SqlUtil.java”
line_number: 791
method_name: “newContextException”
is_native_method: false
}
stack_trace {
class_name: “org.apache.calcite.sql.SqlUtil”
file_name: “SqlUtil.java”
line_number: 776
method_name: “newContextException”
is_native_method: false
}
stack_trace {
class_name: “org.apache.calcite.sql.validate.SqlValidatorImpl”
file_name: “SqlValidatorImpl.java”
line_number: 4951
method_name: “newValidationError”
is_native_method: false
}
stack_trace {
class_name: “org.apache.calcite.sql.validate.SqlValidatorImpl”
file_name: “SqlValidatorImpl.java”
line_number: 1807
method_name: “inferUnknownTypes”
is_native_method: false
}
stack_trace {
class_name: “org.apache.calcite.sql.validate.SqlValidatorImpl”
file_name: “SqlValidatorImpl.java”
line_number: 1886
method_name: “inferUnknownTypes”
is_native_method: false
}
stack_trace {
class_name: “org.apache.calcite.sql.validate.SqlValidatorImpl”
file_name: “SqlValidatorImpl.java”
line_number: 4839
method_name: “validateValues”
is_native_method: false
}
stack_trace {
class_name: “org.apache.calcite.sql.validate.TableConstructorNamespace”
file_name: “TableConstructorNamespace.java”
line_number: 59
method_name: “validateImpl”
is_native_method: false
}
stack_trace {
class_name: “org.apache.calcite.sql.validate.AbstractNamespace”
file_name: “AbstractNamespace.java”
line_number: 84
method_name: “validate”
is_native_method: false
}
stack_trace {
class_name: “org.apache.calcite.sql.validate.SqlValidatorImpl”
file_name: “SqlValidatorImpl.java”
line_number: 976
method_name: “validateNamespace”
is_native_method: false
}
stack_trace {
class_name: “org.apache.calcite.sql.validate.AbstractNamespace”
file_name: “AbstractNamespace.java”
line_number: 115
method_name: “getRowType”
is_native_method: false
}
stack_trace {
class_name: “org.apache.calcite.sql.validate.TableConstructorNamespace”
file_name: “TableConstructorNamespace.java”
line_number: 28
method_name: “getRowType”
is_native_method: false
}
stack_trace {
class_name: “org.apache.calcite.sql.validate.AbstractNamespace”
file_name: “AbstractNamespace.java”
line_number: 126
method_name: “getType”
is_native_method: false
}
stack_trace {
class_name: “org.apache.calcite.sql.validate.TableConstructorNamespace”
file_name: “TableConstructorNamespace.java”
line_number: 28
method_name: “getType”
is_native_method: false
}
stack_trace {
class_name: “org.apache.calcite.sql.validate.SqlValidatorImpl”
file_name: “SqlValidatorImpl.java”
line_number: 1590
method_name: “getValidatedNodeTypeIfKnown”
is_native_method: false
}
stack_trace {
class_name: “org.apache.calcite.sql.validate.SqlValidatorImpl”
file_name: “SqlValidatorImpl.java”
line_number: 1572
method_name: “getValidatedNodeType”
is_native_method: false
}
stack_trace {
class_name: “com.dremio.exec.planner.sql.SqlValidatorImpl”
file_name: “SqlValidatorImpl.java”
line_number: 123
method_name: “validate”
is_native_method: false
}
stack_trace {
class_name: “com.dremio.exec.planner.sql.SqlValidatorAndToRelContext”
file_name: “SqlValidatorAndToRelContext.java”
line_number: 80
method_name: “validate”
is_native_method: false
}
stack_trace {
class_name: “com.dremio.exec.planner.sql.handlers.SqlToRelTransformer”
file_name: “SqlToRelTransformer.java”
line_number: 161
method_name: “validateNode”
is_native_method: false
}
stack_trace {
class_name: “com.dremio.exec.planner.sql.handlers.SqlToRelTransformer”
file_name: “SqlToRelTransformer.java”
line_number: 137
method_name: “validateAndConvert”
is_native_method: false
}
stack_trace {
class_name: “com.dremio.exec.planner.sql.handlers.SqlToRelTransformer”
file_name: “SqlToRelTransformer.java”
line_number: 105
method_name: “validateAndConvert”
is_native_method: false
}
stack_trace {
class_name: “com.dremio.exec.planner.sql.handlers.query.DataAdditionCmdHandler”
file_name: “DataAdditionCmdHandler.java”
line_number: 160
method_name: “getPlan”
is_native_method: false
}
stack_trace {
class_name: “com.dremio.exec.planner.sql.handlers.query.InsertTableHandler”
file_name: “InsertTableHandler.java”
line_number: 110
method_name: “doInsert”
is_native_method: false
}
stack_trace {
class_name: “com.dremio.exec.planner.sql.handlers.query.InsertTableHandler”
file_name: “InsertTableHandler.java”
line_number: 76
method_name: “getPlan”
is_native_method: false
}
stack_trace {
class_name: “com.dremio.exec.planner.sql.handlers.commands.HandlerToPreparePlanBase”
file_name: “HandlerToPreparePlanBase.java”
line_number: 91
method_name: “plan”
is_native_method: false
}
stack_trace {
class_name: “com.dremio.exec.work.foreman.AttemptManager”
file_name: “AttemptManager.java”
line_number: 571
method_name: “plan”
is_native_method: false
}
stack_trace {
class_name: “com.dremio.exec.work.foreman.AttemptManager”
file_name: “AttemptManager.java”
line_number: 462
method_name: “lambda$run$4”
is_native_method: false
}
stack_trace {
class_name: “com.dremio.service.commandpool.ReleasableBoundCommandPool”
file_name: “ReleasableBoundCommandPool.java”
line_number: 140
method_name: “lambda$getWrappedCommand$3”
is_native_method: false
}
stack_trace {
class_name: “com.dremio.service.commandpool.CommandWrapper”
file_name: “CommandWrapper.java”
line_number: 70
method_name: “run”
is_native_method: false
}
stack_trace {
class_name: “com.dremio.context.RequestContext”
file_name: “RequestContext.java”
line_number: 109
method_name: “run”
is_native_method: false
}
stack_trace {
class_name: “com.dremio.common.concurrent.ContextMigratingExecutorService”
file_name: “ContextMigratingExecutorService.java”
line_number: 226
method_name: “lambda$decorate$4”
is_native_method: false
}
stack_trace {
class_name: “com.dremio.common.concurrent.ContextMigratingExecutorService$ComparableRunnable”
file_name: “ContextMigratingExecutorService.java”
line_number: 206
method_name: “run”
is_native_method: false
}
stack_trace {
class_name: “…”
line_number: 0
method_name: “…”
is_native_method: false
}
cause {
exception_class: “org.apache.calcite.sql.validate.SqlValidatorException”
message: “Illegal use of dynamic parameter”
stack_trace {
class_name: “…”
line_number: 0
method_name: “…”
is_native_method: fals
In dremio sessions , this is what i see:
What is the reason ?
Thanks
tolga