Change from Hive Loader to DB Loader failed

Hi again, we´ve got an running workflow which contains only legacy nodes. I changed all nodes now to the current versions. Changing the hive loader to the db loader node leads to the errormessage “table does not exist”. The table exists of course in the DB since it is still in use b y the legacy workflow. Parameters for schema, target table and target file are checked and ok. Does anybody has an idea, what else could cause such error???
Thanks a lot,

Heiko

Hello Heiko,
this might be a problem with identifier quoting. The old framework did only quote identifier with spaces whereas the new framework does always quote identifier. You can change this behavior via the Advanced tab of the Hive Connector node by enabling the “Delimit only identifier with spaces” option.
Bye
Tobias

2 Likes

Hello, unfortunately there isn´t any advanced tab, maybe due to knime 4.2.4?
Is there another option to avoid the quoting?

Hello @hestoelt,

you don’t see Advanced tab in Hive Connector node? Here is shot from latest version:

HiveConnector

Should be there in 4.2.4 version as well.

Br,
Ivan

Hi together, sorry, I didn´t read the hint carefully engough. Of course, the advanced tab exists in the Hive connector! I checked the respective parameter and indeed, the quotes are gone. But the error is still the same: Table does not exist. I copied the errormessage out of the knime log into dbeaver and successfully read data from that table.
What else could be the problem?

Hello,
can you search for the by clicking on the Select a table button and when found select it and click the ok button fill in the information into the text fields?
Internally the node perform the following query to check if the table exists:
SELECT 0 FROM schemaname.tablename WHERE 1 = 0
whereas schemaname and tablename can be quoted e.g. schemaname.tablename depending on the settings in the connector node.
If the problem still persists please check the generated query. To do so enable the JDBC logger via the advanced tab. KNIME will then log all executed statements with debug level. To change the log level for the console open KNIME Analytics Platform and go to File->Preferences. In the Preferences window go to KNIME->KNIME GUI and change the Console View Log Level to debug. Once done execute the DB Loader again and try to execute the logged query in dbeaver.
Bye
Tobias

Hi Tobias, thanks for your support! Yes, we tried that select table button right in the begining: I can select the table in the hive metastore, no problem.
Debug level is set: The qhive_load_example.knwf (37.4 KB) uery is executable in hive!
I copy some of the new debug entries here and also upload the critical part of the workflow, maybe one or more precursor nodes are neccessary prior to the db loader?
What about datatypes: We´ve got one date which is defined as double, in the table it is currently ‘int’ since we just tried everything. Could this lead to ‘table does not exist’?

DEBUG ResetAction Resetting 1 node(s)
DEBUG Column Filter 0:969 reset
DEBUG Column Filter 0:969 clean output ports.
DEBUG WorkflowDataRepository Removing handler 466cea11-a5d0-4cf8-b673-7bcc43247b01 (Column Filter 0:969: ) - 15 remaining
DEBUG NodeContainer Column Filter 0:969 has new state: IDLE
DEBUG Column Filter 0:969 Configure succeeded. (Column Filter)
DEBUG NodeContainer Column Filter 0:969 has new state: CONFIGURED
DEBUG DB Loader 0:3983 Acquiring connection.
INFO DB Loader 0:3983 12. The connection has been opened (borrowed): URL=“jdbc:hive2://fe01.bdmp2.telekom.de:10000/db_a02_bbe_iws_pwr;ssl=1”, user=“hestoelt”
INFO DB Loader 0:3983 12. Statement.executeQuery(sql=): SELECT 0 FROM db_a02_bbe_iws_pwr.cl_tmagic_log_runtime_mt WHERE 1 = 0 ;
DEBUG TimeoutExecutionMonitor$TimeoutNodeProgressMonitor Node configuration was aborted after 3 seconds. To change the timeout parameter ‘Retrieve in configure timeout’ go to the ‘Advanced’ tab of the database connector node.
DEBUG JdbcCanceler$StatementCanceler Canceling the executing statement.
DEBUG JdbcCanceler$StatementCanceler The executing statement has been canceled.
INFO DB Loader 0:3983 12. The connection has been closed (relinquished).
DEBUG DB Loader 0:3983 All the statements have already been closed.
DEBUG DB Loader 0:3983 The connection has been relinquished.
DEBUG DB Loader 0:3983 The managed connection has been closed.
DEBUG DB Loader 0:3983 The transaction managing connection has been closed.
WARN DB Loader 0:3983 Table db_a02_bbe_iws_pwr.cl_tmagic_log_runtime_mt does not exist
DEBUG MemoryAlertSystem Tenured gen heap space usage below threshold (84%) after GC, currently 22% (0.45GB/2.00GB)
DEBUG MemoryAlertSystem Tenured gen heap space usage below threshold (64%) after GC, currently 22% (0.45GB/2.00GB)
DEBUG NodeContainerEditPart Column Filter 0:969 (CONFIGURED)
DEBUG NodeContainerEditPart DB Loader 0:3983 (IDLE)

Hi,
thanks for the log file. The problem seems to be that the execution if the table exists query takes longer than 3 seconds to execute which is why it is automatically canceled during the node configuration phase which in turn leads to the misleading table does not exist exception. You can either increase the configuration time out or better disable the metadata fetching during configure if your Hive db requires a lot of time to process these queries. To do so go to the Advanced tab of the database connector and disable the Retrieve in configure option.
I will also create a ticket (internal number AP-16246) to make it clearer what the actual problem is.
I’m sorry for the inconveniences.
Bye
Tobias

2 Likes

ok, thanks, that helped, I reached the next error, type mismatch :grin:
I tried that option already myself since it is mentionend in the log, but without success. Weired. Anyway, now it seems to work!!
Thanks a lot again
Bye
Heiko

3 Likes

one more thing, in the doku the db loade is marked as deprecated?

Hello Heiko,
great that it works now. Thanks for the info. What docu are you referring to?
We have recently updated the DB Loader to be compatible with the new File Handling framework which is why we had to deprecate the node. This is the link to the current version of the node on KNIME Hub:

Bye
Tobias

1 Like