Snowflake through R/RJDBC - Unable to retrieve JDBC result set

Question

I have latest Java installed, as checked using Windows command prompt

java --version

C:\Users\sweepydodo>java --version
java 17.0.1 2021-10-19 LTS
Java(TM) SE Runtime Environment (build 17.0.1+12-LTS-39)
Java HotSpot(TM) 64-Bit Server VM (build 17.0.1+12-LTS-39, mixed mode, sharing)

I have run the following

library(RJDBC)

drv <- JDBC(driverClass = "net.snowflake.client.jdbc.SnowflakeDriver"
            , classPath = "C:/Users/sweepydodo/Documents/R/snowflake-jdbc-3.13.9.jar"
            , identifier.quote = "`"
            )

con <- dbConnect(drv, "jdbc:snowflake://gs46004.eu-west-1.snowflakecomputing.com:443?warehouse=prd_wh&ROLE=prod_readonly&authenticator=externalbrowser&database=dwh_db", "sweepydodo@abc.com", "dummy password")

R console then reads

Nov 17, 2021 3:37:01 PM net.snowflake.client.core.CredentialManager fillCachedCredential
INFO: JNA jar files are needed for Secure Local Storage service. Please follow the Snowflake JDBC instruction for Secure Local Storage feature. Fall back to normal process.
Initiating login request with your identity provider. A browser window should have opened for you to complete the login. If you can't see it, check existing browser windows, or your OS settings. Press CTRL+C to abort and try again...

It then opens up a browser window with a white background and a single line that reads:

Your identity was confirmed and propagated to Snowflake JDBC driver. You can close this window now and go back where you started from.

I then went back to R and ran

dbListTables(con)                 # list all tables

All is well, and I am presented with a list of tables. My problem comes when I run an actual query:

dbGetQuery(con,
           "
           select  *
           from    FACT_VISIT
           limit   10
           "
           )

Error reads

Error in .verify.JDBC.result(r, "Unable to retrieve JDBC result set",  : 
  Unable to retrieve JDBC result set
  JDBC ERROR: SQL compilation error:
Object 'FACT_VISIT' does not exist or not authorized.
  Statement: 
                select  *
                from    FACT_VISIT
                limit   10

I find it strange I am able to view a list of tables using dbListTables(con), but unable to query any table. I look forward to any idea/solutions.

The error clearly states that you have no access to that table. Are you able to run the same SELECT from Snowflake UI using same user/role/database/schema as in your script? I am not seeing a schema set in your connectionstring therefore I assume it's PUBLIC, the default one and this may cause the issue. — Sergiu, Nov 17 '21 at 16:09
Thank you, @Sergiu As it turns out, not only was the table's prefix missing (database name and scheme). The biggest problem was the combination of the incorrect version of `Java` and `Snowflake`'s jar file. I shall post detailed answer. Still, thank you. — Sweepy Dodo, Nov 17 '21 at 18:53

Sweepy Dodo · Accepted Answer · 2022-04-05T10:42:20.177

After a day of searching and colleague's help we found the culprit being the combination of Java's latest version jdk-17_windows-x64_bin and Snowflake's one of the latest versions 3.13.9.

What worked was everything as described in original post but using Java 11.0.12 from here and snowflake-jdbc-3.10.3.jar from here.

The latest versions from Java and Snowflake's .jar together will bring you problem outlined here.

Lastly, as @Sergiu pointed out. In the SQL query I should also have explicitly specified database and scheme name.

dbGetQuery(con,
           "
           select  *
           from    dwh_db.visitor.fact_visit
           limit   10
           "
           )

Snowflake through R/RJDBC - Unable to retrieve JDBC result set

1 Answers1

Linked