This seems to be a 5 year old bug for other databases and it is fixed,
But I am facing the same issue with ThoughtSpot DB. the database writer node is not appending data to existing table because it is not able to verify the existence of a table in ThoughtSpot DB using the default statement
“SELECT * FROM (SELECT 1 as tmpcol FROM BACOE_VCG_TEST.falcon_default_schema.sample_retail_jdbc)” tempTable_801008611561288158 WHERE (1 = 0)".
thoughtspot DB does not recognize this statement and throws an error and KNIME thinks that the table does not exist and trying to create one, so the error is shown.
Is there a workaround for this? How can I initiate the insert statement within the database writer node? Or Is there a way to change the default statement (mentioned above) to suit the ThoughtSpot DB?
Please help.
Thanks,
Sriram
Please find below the error log:
DEBUG Database Writer 5:36 Database Writer 5:36 Start execute
DEBUG Database Writer 5:36 Workflow user found: r********
DEBUG Database Writer 5:36 Try to lock key to obtain connection: ConnectionKey [db-user=t********, db-name=jdbc:simba://:, wf-user=r*******]
DEBUG Database Writer 5:36 Connection found for key: ConnectionKey [db-user=t*******, db-name=jdbc:simba://:, wf-user=r*******]
DEBUG Database Writer 5:36 Valid connection found in cache with key: ConnectionKey [db-user=t********, db-name=jdbc:simba://:, wf-user=r*******]
DEBUG Database Writer 5:36 Workflow user found: r********
DEBUG Database Writer 5:36 Try to lock key for stmt execution: ConnectionKey [db-user=t********, db-name=jdbc:simba://:, wf-user=r*******]
DEBUG Database Writer 5:36 Check connection for key: ConnectionKey [db-user=t******, db-name=jdbc:simba://:, wf-user=r*******]
DEBUG Database Writer 5:36 Checking if table BACOE_VCG_TEST.falcon_default_schema.sample_retail_jdbc exists
DEBUG Database Writer 5:36 Execute query: SELECT * FROM (SELECT 1 as tmpcol FROM BACOE_VCG_TEST.falcon_default_schema.sample_retail_jdbc) tempTable_801008611561288158 WHERE (1 = 0)
DEBUG Database Writer 5:36 Got exception while checking for existence of table ‘BACOE_VCG_TEST.falcon_default_schema.sample_retail_jdbc’: Internal parsing error: Syntax invalid, msg=Unexpected token, token=“SELECT”
sql="SELECT * FROM ( SELECT 1 a… "
^^^
java.sql.SQLException: Internal parsing error: Syntax invalid, msg=Unexpected token, token=“SELECT”
sql="SELECT * FROM ( SELECT 1 a… "
^^^
at com.simba.client.protocol.DiagRecord.generateException(DiagRecord.java:117)
at com.simba.client.protocol.ClientProtocolV1.parseInfoResponse(ClientProtocolV1.java:324)
at com.simba.client.protocol.ClientProtocolV1.handleDefaultMessages(ClientProtocolV1.java:175)
at com.simba.client.protocol.ClientProtocolV1.handleDefaultMessages(ClientProtocolV1.java:152)
at com.simba.client.dataengine.SCDataEngine.prepare(SCDataEngine.java:342)
at com.simba.jdbc.common.SStatement.executeNoParams(SStatement.java:3255)
at com.simba.jdbc.common.SStatement.executeNoParams(SStatement.java:3201)
at com.simba.jdbc.common.SStatement.executeQuery(SStatement.java:1101)
at org.knime.core.node.port.database.DatabaseUtility.tableExists(DatabaseUtility.java:329)
at org.knime.core.node.port.database.writer.DBWriterImpl.lambda$0(DBWriterImpl.java:118)
at org.knime.core.node.port.database.DatabaseConnectionSettings.execute(DatabaseConnectionSettings.java:684)
at org.knime.core.node.port.database.writer.DBWriterImpl.writeData(DBWriterImpl.java:108)
at org.knime.base.node.io.database.DBWriterNodeModel.execute(DBWriterNodeModel.java:250)
at org.knime.core.node.NodeModel.executeModel(NodeModel.java:567)
at org.knime.core.node.Node.invokeFullyNodeModelExecute(Node.java:1186)
at org.knime.core.node.Node.execute(Node.java:973)
at org.knime.core.node.workflow.NativeNodeContainer.performExecuteNode(NativeNodeContainer.java:559)
at org.knime.core.node.exec.LocalNodeExecutionJob.mainExecute(LocalNodeExecutionJob.java:95)
at org.knime.core.node.workflow.NodeExecutionJob.internalRun(NodeExecutionJob.java:179)
at org.knime.core.node.workflow.NodeExecutionJob.run(NodeExecutionJob.java:110)
at org.knime.core.util.ThreadUtils$RunnableWithContextImpl.runWithContext(ThreadUtils.java:328)
at org.knime.core.util.ThreadUtils$RunnableWithContext.run(ThreadUtils.java:204)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at org.knime.core.util.ThreadPool$MyFuture.run(ThreadPool.java:123)
at org.knime.core.util.ThreadPool$Worker.run(ThreadPool.java:246)
Caused by: com.simba.support.exceptions.ErrorException: Internal parsing error: Syntax invalid, msg=Unexpected token, token=“SELECT”
sql="SELECT * FROM ( SELECT 1 a… "
^^^
… 26 more
INFO Database Writer 5:36 Table “BACOE_VCG_TEST.falcon_default_schema.sample_retail_jdbc” does not exist in database, will create new table.
DEBUG Database Writer 5:36 Executing SQL statement as execute: CREATE TABLE BACOE_VCG_TEST.falcon_default_schema.sample_retail_jdbc (store_brand varchar(255), store_num integer, division varchar(255), category varchar(255), prod_code varchar(255), order_dt varchar(255), season varchar(255), units integer, sales_amt double, price_grp integer, price_grp1 integer, prod_brand varchar(255))
DEBUG Database Writer 5:36 reset
ERROR Database Writer 5:36 Execute failed: Internal error: Table exists on server.
DEBUG Database Writer 5:36 Execute failed: Internal error: Table exists on server.
java.sql.SQLException: Internal error: Table exists on server.
at com.simba.client.protocol.DiagRecord.generateException(DiagRecord.java:117)
at com.simba.client.protocol.ClientProtocolV1.parseInfoResponse(ClientProtocolV1.java:324)
at com.simba.client.protocol.ClientProtocolV1.handleDefaultMessages(ClientProtocolV1.java:175)
at com.simba.client.protocol.ClientProtocolV1.handleDefaultMessages(ClientProtocolV1.java:152)
at com.simba.client.dataengine.SCDataEngine.prepare(SCDataEngine.java:342)
at com.simba.jdbc.common.SStatement.executeNoParams(SStatement.java:3255)
at com.simba.jdbc.common.SStatement.execute(SStatement.java:693)
at org.knime.core.node.port.database.writer.DBWriterImpl.lambda$0(DBWriterImpl.java:255)
at org.knime.core.node.port.database.DatabaseConnectionSettings.execute(DatabaseConnectionSettings.java:684)
at org.knime.core.node.port.database.writer.DBWriterImpl.writeData(DBWriterImpl.java:108)
at org.knime.base.node.io.database.DBWriterNodeModel.execute(DBWriterNodeModel.java:250)
at org.knime.core.node.NodeModel.executeModel(NodeModel.java:567)
at org.knime.core.node.Node.invokeFullyNodeModelExecute(Node.java:1186)
at org.knime.core.node.Node.execute(Node.java:973)
at org.knime.core.node.workflow.NativeNodeContainer.performExecuteNode(NativeNodeContainer.java:559)
at org.knime.core.node.exec.LocalNodeExecutionJob.mainExecute(LocalNodeExecutionJob.java:95)
at org.knime.core.node.workflow.NodeExecutionJob.internalRun(NodeExecutionJob.java:179)
at org.knime.core.node.workflow.NodeExecutionJob.run(NodeExecutionJob.java:110)
at org.knime.core.util.ThreadUtils$RunnableWithContextImpl.runWithContext(ThreadUtils.java:328)
at org.knime.core.util.ThreadUtils$RunnableWithContext.run(ThreadUtils.java:204)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at org.knime.core.util.ThreadPool$MyFuture.run(ThreadPool.java:123)
at org.knime.core.util.ThreadPool$Worker.run(ThreadPool.java:246)
Caused by: com.simba.support.exceptions.ErrorException: Internal error: Table exists on server.
… 24 more
DEBUG Database Writer 5:36 Database Writer 5:36 doBeforePostExecution
DEBUG Database Writer 5:36 Database Writer 5:36 has new state: POSTEXECUTE
DEBUG Database Writer 5:36 Database Writer 5:36 doAfterExecute - failure
The same issue occurs in DB insert node(labs). because of the nested query KNIMEpasses to check the existence of a table in the database.
“ SELECT * FROM (SELECT 1 as tmpcol FROM BACOE_VCG_TEST.falcon_default_schema.sample_retail_jdbc) ” tempTable_801008611561288158 WHERE (1 = 0)".
Error Log:
DEBUG NodeContainerEditPart DB Connector (Labs) 2:71 (EXECUTED)
DEBUG NodeContainerEditPart DB Connector (Labs) 2:71 (EXECUTED)
DEBUG NodeContainerEditPart DB Insert (Labs) 2:70 (IDLE)
DEBUG DB Insert (Labs) 2:70 Acquiring connection.
DEBUG DB Insert (Labs) 2:70 A connection has been acquired.
DEBUG DB Insert (Labs) 2:70 Acquiring connection.
DEBUG DB Insert (Labs) 2:70 A connection has been acquired.
DEBUG DB Insert (Labs) 2:70 Acquiring connection.
DEBUG DB Insert (Labs) 2:70 A connection has been acquired.
WARN DB Insert (Labs) 2:70 java.sql.SQLException: Internal parsing error: Syntax invalid, msg=Unexpected token, token=“SELECT”
sql="SELECT * FROM ( SELECT * F… "
^^^
DEBUG DB Insert (Labs) 2:70 java.sql.SQLException: Internal parsing error: Syntax invalid, msg=Unexpected token, token=“SELECT”
sql="SELECT * FROM ( SELECT * F… "
^^^
org.knime.core.node.InvalidSettingsException: java.sql.SQLException: Internal parsing error: Syntax invalid, msg=Unexpected token, token=“SELECT”
sql="SELECT * FROM ( SELECT * F… "
^^^
at org.knime.database.port.DBDataPortObjectSpec.create(DBDataPortObjectSpec.java:139)
at org.knime.database.node.io.DBManipulationNodeModel.configure(DBManipulationNodeModel.java:210)
at org.knime.core.node.NodeModel.configureModel(NodeModel.java:1051)
at org.knime.core.node.Node.invokeNodeModelConfigure(Node.java:1855)
at org.knime.core.node.Node.configure(Node.java:1791)
at org.knime.core.node.workflow.NativeNodeContainer.performConfigure(NativeNodeContainer.java:530)
at org.knime.core.node.workflow.SingleNodeContainer.callNodeConfigure(SingleNodeContainer.java:304)
at org.knime.core.node.workflow.SingleNodeContainer.configure(SingleNodeContainer.java:189)
at org.knime.core.node.workflow.WorkflowManager.configureSingleNodeContainer(WorkflowManager.java:5888)
at org.knime.core.node.workflow.WorkflowManager.configureNodeAndPortSuccessors(WorkflowManager.java:6071)
at org.knime.core.node.workflow.WorkflowManager.configureNodeAndSuccessors(WorkflowManager.java:6014)
at org.knime.core.node.workflow.WorkflowManager.loadNodeSettings(WorkflowManager.java:1751)
at org.knime.core.node.workflow.NodeContainer.applySettingsFromDialog(NodeContainer.java:1002)
at org.knime.core.ui.wrapper.NodeContainerWrapper.applySettingsFromDialog(NodeContainerWrapper.java:260)
at org.knime.workbench.ui.wrapper.WrappedNodeDialog.doApply(WrappedNodeDialog.java:624)
at org.knime.workbench.ui.wrapper.WrappedNodeDialog.doOK(WrappedNodeDialog.java:539)
at org.knime.workbench.ui.wrapper.WrappedNodeDialog.access$8(WrappedNodeDialog.java:537)
at org.knime.workbench.ui.wrapper.WrappedNodeDialog$5.widgetSelected(WrappedNodeDialog.java:475)
at org.eclipse.swt.widgets.TypedListener.handleEvent(TypedListener.java:249)
at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:86)
at org.eclipse.swt.widgets.Display.sendEvent(Display.java:4428)
at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:1079)
at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:4238)
at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:3817)
at org.eclipse.jface.window.Window.runEventLoop(Window.java:818)
at org.eclipse.jface.window.Window.open(Window.java:794)
at org.knime.workbench.ui.wrapper.WrappedNodeDialog.open(WrappedNodeDialog.java:182)
at org.knime.workbench.editor2.editparts.NodeContainerEditPart.openNodeDialog(NodeContainerEditPart.java:872)
at org.knime.workbench.editor2.editparts.NodeContainerEditPart.openDialog(NodeContainerEditPart.java:780)
at org.knime.workbench.editor2.editparts.NodeContainerEditPart.performRequest(NodeContainerEditPart.java:351)
at org.eclipse.gef.tools.SelectEditPartTracker.performOpen(SelectEditPartTracker.java:194)
at org.eclipse.gef.tools.SelectEditPartTracker.handleDoubleClick(SelectEditPartTracker.java:137)
at org.knime.workbench.editor2.WorkflowSelectionDragEditPartsTracker.handleDoubleClick(WorkflowSelectionDragEditPartsTracker.java:126)
at org.eclipse.gef.tools.AbstractTool.mouseDoubleClick(AbstractTool.java:1069)
at org.eclipse.gef.tools.SelectionTool.mouseDoubleClick(SelectionTool.java:527)
at org.eclipse.gef.EditDomain.mouseDoubleClick(EditDomain.java:231)
at org.eclipse.gef.ui.parts.DomainEventDispatcher.dispatchMouseDoubleClicked(DomainEventDispatcher.java:291)
at org.eclipse.draw2d.LightweightSystem$EventHandler.mouseDoubleClick(LightweightSystem.java:518)
at org.eclipse.swt.widgets.TypedListener.handleEvent(TypedListener.java:197)
at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:86)
at org.eclipse.swt.widgets.Display.sendEvent(Display.java:4428)
at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:1079)
at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:4238)
at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:3817)
at org.eclipse.e4.ui.internal.workbench.swt.PartRenderingEngine$5.run(PartRenderingEngine.java:1150)
at org.eclipse.core.databinding.observable.Realm.runWithDefault(Realm.java:336)
at org.eclipse.e4.ui.internal.workbench.swt.PartRenderingEngine.run(PartRenderingEngine.java:1039)
at org.eclipse.e4.ui.internal.workbench.E4Workbench.createAndRunUI(E4Workbench.java:153)
at org.eclipse.ui.internal.Workbench.lambda$3(Workbench.java:680)
at org.eclipse.core.databinding.observable.Realm.runWithDefault(Realm.java:336)
at org.eclipse.ui.internal.Workbench.createAndRunWorkbench(Workbench.java:594)
at org.eclipse.ui.PlatformUI.createAndRunWorkbench(PlatformUI.java:148)
at org.knime.product.rcp.KNIMEApplication.start(KNIMEApplication.java:144)
at org.eclipse.equinox.internal.app.EclipseAppHandle.run(EclipseAppHandle.java:196)
at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.runApplication(EclipseAppLauncher.java:134)
at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.start(EclipseAppLauncher.java:104)
at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:388)
at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:243)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.eclipse.equinox.launcher.Main.invokeFramework(Main.java:653)
at org.eclipse.equinox.launcher.Main.basicRun(Main.java:590)
at org.eclipse.equinox.launcher.Main.run(Main.java:1499)
Caused by: java.sql.SQLException: Internal parsing error: Syntax invalid, msg=Unexpected token, token=“SELECT”
sql="SELECT * FROM ( SELECT * F… "
^^^
at com.simba.client.protocol.DiagRecord.generateException(DiagRecord.java:117)
at com.simba.client.protocol.ClientProtocolV1.parseInfoResponse(ClientProtocolV1.java:324)
at com.simba.client.protocol.ClientProtocolV1.handleDefaultMessages(ClientProtocolV1.java:175)
at com.simba.client.protocol.ClientProtocolV1.handleDefaultMessages(ClientProtocolV1.java:152)
at com.simba.client.dataengine.SCDataEngine.prepare(SCDataEngine.java:342)
at com.simba.jdbc.common.SStatement.executeNoParams(SStatement.java:3255)
at com.simba.jdbc.common.SStatement.executeNoParams(SStatement.java:3201)
at com.simba.jdbc.common.SStatement.executeQuery(SStatement.java:1101)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.tomcat.jdbc.pool.interceptor.StatementDecoratorInterceptor$StatementProxy.invoke(StatementDecoratorInterceptor.java:237)
at com.sun.proxy.$Proxy71.executeQuery(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:114)
at com.sun.proxy.$Proxy71.executeQuery(Unknown Source)
at org.knime.database.connection.impl.AbstractStatementWrapper.executeQuery(AbstractStatementWrapper.java:95)
at org.knime.database.connection.impl.monitored.MonitoredStatement.executeQuery(MonitoredStatement.java:84)
at org.knime.database.agent.metadata.impl.DefaultDBMetadataReader.getDBDataObject(DefaultDBMetadataReader.java:139)
at org.knime.database.node.io.DBManipulationNodeModel.createDbDataObject(DBManipulationNodeModel.java:231)
at org.knime.database.node.io.DBManipulationNodeModel.lambda$0(DBManipulationNodeModel.java:213)
at org.knime.database.port.DBDataPortObjectSpec.create(DBDataPortObjectSpec.java:127)
… 64 more
Caused by: com.simba.support.exceptions.ErrorException: Internal parsing error: Syntax invalid, msg=Unexpected token, token=“SELECT”
sql="SELECT * FROM ( SELECT * F… "
^^^
… 90 more
Hello SriramRamanujam,
right, the node doesn’t check if the table exists but it is executing this query to get the specification of the DB Data out port. I haven’t thought about that. In this case you can not use the db nodes to insert data into a ThoughtSpot database. I will create a ticket to allow users to disable the DB Data port to also support databases that do not support nested queries.
Bye
Tobias
Hello SriramRamanujam,
we have just completed this feature. It is now possible to disable the DB Data output port via the node dialog of the DB manipulation nodes e.g. insert, update, delete, merge which will prevent KNIME from executing metadata query which causes problems with ThoughtSpot. If The feature will be part of version 4.1 which will be released on December 6th. If you want to try it out before have a look at the nightly build.
Bye
Tobias