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.
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
"
)