New CSV Writer node causing error on polybase

Hi there,

We recently updated Knime to 4.3 and the new CSV Writer is causing an error when we try to update our Azure Synapse SQL using the CSV on Azure Blob Storage.

To clarify, the previous version of the CSV Writer node would always quote the missing values for strings. So, the missing string values would be like “” on the csv file.

The new version doesn’t do that, instead it asks you how you would handle the missing values. When you choose to quote them. It also quotes the numeric fields.

Afterwards, when you execute the insert from temp table to table it results the following error:
ERROR DB SQL Executor 0:480 Execute failed: HdfsBridge::recordReaderFillBuffer - Unexpected error encountered filling record reader buffer: HadoopExecutionException: Could not find a delimiter after string delimiter.

Do you know how to fix that using the new node?

Many thanks

Hello rafaelrfuentes,
I also couldn’t find a settings combination for that and forwarded your request to the developer to look into the problem. As far as I know Azure Synapse SQL is based on SQL Server. Did you try to use the DB Loader that uses the bulk loading API of the official SQL Server driver to upload large amounts of data into a database table or do you have another use case? To use the DB Loader you have to register the JDBC driver from Microsoft as described here.
Bye
Tobias

Hi Tobias, how are you?

Many thanks for your answer. We didn’t try this node, actually we didn’t know it. Thanks for the advice, we will studie the viability and performance.

But it would be nice to have the option on CSV node. As we are heavy user of this process: CSV > Blob Storage > Temp Table > Synapse Table using Knime server. Without the posibility to use the current CSV Writer, we will probably be using the deprecated node insted in most cases.

Hi Tobias, How are you?

We tried with DB Loader on desktop, works great! Now we are working on setup of JDBC driver on Knime Server. Apart from that, our problem now will be not being able to “update” an properly insert with DB Loaders:

Many of our flows execute an “upsert” based on Blob Storage CSV > Temp table > Table. Then execute an update and insert based on a key. In addition, as Azure Synapse doesn’t have a PK, we set the proper insert on node “DB SQL EXECUTOR”.

DB Loader would very partly solve the insert. Do you know any node that would update as quick as DB Loader?

Many thanks!