Query to Snowflake database isn't working because no active warehouse is selected

3
votes

I'm able to successfully connect to the Snowflake database via R but I'm having trouble getting the data because no active warehouse is selected. Below is the error message:

No active warehouse selected in the current session. Select an active warehouse with the 'use warehouse' command.

Here is my code I'm using.

  con <- DBI::dbConnect(
    odbc::odbc(), 
    UID    = user, 
    PWD    = pass, 
    Server = host,
    Warehouse = 'YOUR_WAREHOUSE_NAME',
    Driver = "SnowflakeDSIIDriver",
    Role = role,
    Database = database,
    Autthenticator = "external browser"
  )

dbGetQuery(con, "SELECT * FROM MY_TABLE LIMIT 100")

I've based my connection and query from this thread on RStudio Community but I'm not having any luck. I've also tried using the 'use warehouse MY_WAREHOUSE' command in my query without any luck.

Note: I can connect successfully and query data via Python so I think this is an R specific issue.

2
pythonrsnowflake-cloud-data-platform
1) Maybe your role has no privileges to use this warehouse. Are you sure you use the correct role? 2) If this is something about the R, have you tried to run "USE WAREHOUSE MY_WAREHOUSE" statement as a separate statement? What is the result of running this command (from R)? - Gokhan Atil
1) I'm using the same role as my Python connection. I also switched roles in R to check and I'm getting the same error. 2) I've also used that in my query as a separate statement. dbGetQuery(con, "SELECT * FROM MY_TABLE LIMIT 10", "USE WAREHOUSE MY_WAREHOUSE") I get the following error Error: nanodbc/nanodbc.cpp:1617: 57P03: No active warehouse selected in the current session. Select an active warehouse with the 'use warehouse' - trevin_flick
some other ideas to try: 1) what happens if you use another warehouse? 2) could it be a name resolution issue with the casing of the warehouse name - so perhaps wrap your reference in "<wh_name>" if it is not defined as upper case 3) what happens if you ensure that the WH is started (resumed) before initiating the connection from R (rather than letting it try to auto-resume) - Mike Donovan
Hi Mike, I only have access to one warehouse (at least for now). I tried 2 but that didn't seem to work. Can you elaborate on #3 a little further? How would I start the WH before establishing the connection from R? Thanks - trevin_flick
I'm not sure if this is the only problem, but there's a misspelling in your code. "Autthenticator" should be "Authenticator". You should only need that parameter if you're using SSO. - Greg Pavlik

2 Answers

3
votes

I ran into this issue too. It's probably not the best answer as I'm just testing some dev code (in python), but I had to issue:

GRANT USAGE ON WAREHOUSE my_warehouse TO ROLE writer_role;

Of which the user I was using to connect to Snowflake is a member of, then I was able to write to the table.

0
votes
  1. Seems like an R bug IMHO if the python equivalent code is fine
  2. Your role can have a default warehouse assigned, perhaps it is blank and requires it need be specified upon connection; and a bug in the R code is stopping it from being set?