SNOW-1846830: SNOW-1863648 PUT command does not transfer file for JDBC driver from 3.17.0

[tescnovozymes]

First reported here:
DB Loader not working with Snowflake driver from 3.17

Using command: PUT 'file:///C:/KNIME/test.csv' '@~' AUTO_COMPRESS=FALSE;
And then listing with: list '@~'

No file is transferred with drivers from and above 3.17.0

1. What version of JDBC driver are you using?

• snowflake-jdbc-3.20.0.jar
• snowflake-jdbc-3.19.1.jar
• snowflake-jdbc-3.18.0.jar
• snowflake-jdbc-3.17.0.jar

2. What operating system and processor architecture are you using?
   Windows 11, running KNIME 5.2.2

3. What version of Java are you using?
   KNIME 5.2.x ships with JRE 17

4. What did you do?

Registrated different drivers in KNIME:

drivers/snowflake_test_3.14.1/name=Snowflake Test 3.14.1
drivers/snowflake_test_3.14.1/database_type=snowflake
drivers/snowflake_test_3.14.1/description=Snowflake JDBC Driver Test
drivers/snowflake_test_3.14.1/driver_class=net.snowflake.client.jdbc.SnowflakeDriver
drivers/snowflake_test_3.14.1/paths/0=C:\\KNIME\\snowflake_test\\snowflake-jdbc-3.14.1.jar
drivers/snowflake_test_3.14.1/url_template=jdbc\:snowflake\://novozymes-prod.snowflakecomputing.com/?warehouse\=<warehouse>&role\=[role]&db\=[database]&schema\=[schema]
drivers/snowflake_test_3.14.1/version=3.14.1
drivers/snowflake_test_3.16.1/name=Snowflake Test 3.16.1
drivers/snowflake_test_3.16.1/database_type=snowflake
drivers/snowflake_test_3.16.1/description=Snowflake JDBC Driver Test
drivers/snowflake_test_3.16.1/driver_class=net.snowflake.client.jdbc.SnowflakeDriver
drivers/snowflake_test_3.16.1/paths/0=C:\\KNIME\\snowflake_test\\snowflake-jdbc-3.16.1.jar
drivers/snowflake_test_3.16.1/url_template=jdbc\:snowflake\://novozymes-prod.snowflakecomputing.com/?warehouse\=<warehouse>&role\=[role]&db\=[database]&schema\=[schema]
drivers/snowflake_test_3.16.1/version=3.16.1
drivers/snowflake_test_3.17.0/name=Snowflake Test 3.17.0
drivers/snowflake_test_3.17.0/database_type=snowflake
drivers/snowflake_test_3.17.0/description=Snowflake JDBC Driver Test
drivers/snowflake_test_3.17.0/driver_class=net.snowflake.client.jdbc.SnowflakeDriver
drivers/snowflake_test_3.17.0/paths/0=C:\\KNIME\\snowflake_test\\snowflake-jdbc-3.17.0.jar
drivers/snowflake_test_3.17.0/url_template=jdbc\:snowflake\://novozymes-prod.snowflakecomputing.com/?warehouse\=<warehouse>&role\=[role]&db\=[database]&schema\=[schema]
drivers/snowflake_test_3.17.0/version=3.17.0
drivers/snowflake_test_3.20.0/name=Snowflake Test 3.20.0
drivers/snowflake_test_3.20.0/database_type=snowflake
drivers/snowflake_test_3.20.0/description=Snowflake JDBC Driver Test
drivers/snowflake_test_3.20.0/driver_class=net.snowflake.client.jdbc.SnowflakeDriver
drivers/snowflake_test_3.20.0/paths/0=C:\\KNIME\\snowflake_test\\snowflake-jdbc-3.20.0.jar
drivers/snowflake_test_3.20.0/url_template=jdbc\:snowflake\://novozymes-prod.snowflakecomputing.com/?warehouse\=<warehouse>&role\=[role]&db\=[database]&schema\=[schema]
drivers/snowflake_test_3.20.0/version=3.20.0

And then run the PUT command and list command

5. What did you expect to see?

I expected to see file in user stage.

I validated query_history was listing the commands.

select q.START_TIME, q.EXECUTION_STATUS, q.ERROR_MESSAGE, q.QUERY_TEXT--, q.*
from table(information_schema.query_history()) AS q
--WHERE QUERY_TYPE = 'PUT_FILES'
WHERE USER_NAME = CURRENT_USER
order by q.START_TIME desc;

6. Can you set logging to DEBUG and collect the logs?

No

[tescnovozymes]

I tried in DBeaver community with same error

Not Working

Bumping down to 3.16.1 works

[sfc-gh-sghosh]

Hello @tescnovozymes ,

Thanks for raising the issue.
I just tried with latest JDBC driver 3.20.0 and I see its uploading the file successfully via PUT command.
Both user stage and named stage working fine.

code:
Statement stmt= con.createStatement();
String str = "put 'file:////Users/sghosh/Documents/Traces/File24.csv' @~ auto_compress=false overwrite=true";
stmt.execute(str);

output:
ls @~;
I can see the file uploaded.

Could you please check again.

Regards,
Sujan

[tescnovozymes]

Hi Sujan,

I first updated DBeaver Community to latest.
Version 24.3.0.202412011650

Then I tried with 3.16.1 driver

Authenticator: externalbrowser

This works

[tescnovozymes]

Then I bumped to 3.20

And now no file is transferred

[tescnovozymes]

https://dbeaver.io/download/#:~:text=Java%20notes%3A,in%20the%20DBeaver%20installation%20folder.

DBeaver requires Java 17 or higher. Since version 23.0 all distributions include OpenJDK 17 bundle.

[koettert]

This seems to be only a problem on Windows but not on Mac or Linux. We have automatic tests for all three OS and the DB Loader tests that use the PUT command to upload files only fail on Windows but succeed on Linux and Mac.

[tescnovozymes]

Bug located. Seems the has been a change in accepting PATH from 16.1 to 20.0

KNIME generates PATH with an extra /

# Not working
PUT 'file:///C:/KNIME/test.csv' '@~' AUTO_COMPRESS=FALSE;

# Working
PUT 'file://C:/KNIME/test.csv' '@~' AUTO_COMPRESS=FALSE;
PUT 'file://C:\\KNIME\\test.csv' '@~' AUTO_COMPRESS=FALSE;

[tescnovozymes]

Yup, fault of KNIME (at least 5.2.2)

[tescnovozymes]

Closing issue here, as this is not a fault of Snowflake driver, but a fault in KNIME code that generates the PUT command.
Reporting bag to original thread.

[koettert]

The three / in the beginning of the URI is the proper URI format for local Windows paths as described here. This was accepted and properly parsed by the driver until version 3.16.1. So I would consider this a bug in the path handling of the driver and thus would reopen this issue request. It would be also great if you could explain in more detail what method you use or link to the part in the source code that parses the path argument since in the documentation it mentions that it is expecting a URI but also the space in the file name "load data" is not URI encoded e.g. load%20data.
Thanks a lot.

[sfc-gh-sghosh]

Thank you @tescnovozymes ,

Will check and update.

Regards,
Sujan

[jdimeo]

This is definitely a regression from working past behavior. I was using Path.toUri().toString() to inject into the SQL PUT (nothing to do with KNIME) and it mysteriously stopped working with no error or non-zero query return code, but I'll try the /// -> // workaround in the short term. thanks!

[sfc-gh-sghosh]

Hello @tescnovozymes ,

We are able to reproduce the issue, we will work on it and update.

Regards,
Sujan

[sfc-gh-sghosh]

Hello @tescnovozymes ,

We checked further, the snowflake documentation for PUT for windows is file://C:/temp/load data.
https://docs.snowflake.com/en/sql-reference/sql/put

Windows
You must include the drive and backslash in the path and replace backslash characters with forward slashes. For example, for a file named load data use file://C:/temp/load data.

Will update further.

Regards,
Sujan

[tescnovozymes]

Should the documentation then be expanded with a section, that snowflake use a custom implementation of the "file:" uri protocol and not following standards?

[sfc-gh-sghosh]

Hello @tescnovozymes ,

We are working on the issue to fix the regression, will update further.

Regards,
Sujan

[wheezil]

We also hit this, and spent a couple days chasing it. It would have been hard to deduce without seeing this issue report, because the PUT command fails quietly and reports no error that I can see. Maybe it shows up in logging somewhere?
Please either allow file:/// or make PUT fail noisily (throw an exception) on invalid paths.

[koettert]

Hello @sfc-gh-sghosh ,
is there any update on when this fix might be available?
Thanks
Tobias

[sfc-gh-dszmolka]

no known ETTR for now, but we'll keep this thread posted with the progress if any. Right now, the team is working on the fix. Thank you for bearing with us while this is fixed!

[koettert]

Hello @sfc-gh-dszmolka ,
is there any update on this? We plan to release a bugfix release of our software soon and it would be great if we could include the new version of the driver that fixes the problem.
Thanks
Tobias

[sfc-gh-dszmolka]

hi @koettert unfortunately I don't have any ETTR still. You can follow #2033 for realtime progress updates if you wish, but it's in Draft phase so really not sure when it can be ready for review, actually reviewed, merged, and eventually released.

But if this issue is very impactful for you, maybe you can build the driver from https://github.com/snowflakedb/snowflake-jdbc/tree/SNOW-1863648-allow-triple-slash-file-notation or use it as an inspiration for a local fix, and if it works well for you, then use the custom built driver until the official release is out.

editing to add some details after conferring with the team:

• during investigating this issue, we uncovered a more serious one which we definitely would like to address first
• as a consequence, it is likely that the fix for both this and the aforementioned issue won't make the next upcoming release (which i believe might be imminent), but we count on it to make the next release towards end of February 2025

Hope this helps and apologies this takes so long. As mentioned earlier, I'll keep this issue posted if there's any important change like having a PR in review or the release.

[koettert]

Thanks for the info and the alternative. Will monitor this ticket for further updates.

[sfc-gh-dszmolka]

fix is merged and will be part of the next release

[koettert]

Thanks @sfc-gh-dszmolka for fixing the problem. Looking forward to the release :-)

[sfc-gh-dszmolka]

released with JDBC driver v3.23.0

[jdimeo]

@sfc-gh-dszmolka there seems to be a packaging issue with 3.23.0... some unresolved property made it into the POM?
dependency: com.nimbusds:oauth2-oidc-sdk:jar:${nimbusds.oauth2.version}

[sfc-gh-dszmolka]

yup :( #2104