Why do I get warehouse error when trying to read from Snowflake table?

user_6239742 0
Viewed 549

When trying to read from a table with Snowflake Python connector I am getting the following error:

*** snowflake.connector.errors.ProgrammingError: 000606 (57P03): No active warehouse selected in the current session. Select an active warehouse with the 'use warehouse' command.

Searching the web for solutions I saw that the main recommendation is to apply USE WAREHOUSE <warehouse name> However, when I apply this command I get the following error:

*** snowflake.connector.errors.ProgrammingError: 002043 (02000): SQL compilation error: Object does not exist, or operation cannot be performed.

I also granted "USAGE" privileges to the relevant user, but the errors still occurred. There are no errors when I apply the commands USE DATABASE and USE SCHEMA on the other hand. Also, I am able to read from the table from Snowflake web UI with another user.

Any idea what might be wrong?

snowflake-connector snowflake-cloud-data-platform
2 Answers

so use SHOW WAREHOUSES

show warehouses;
name state type size running queued is_default is_current auto_suspend auto_resume
COMPUTE_WH SUSPENDED STANDARD X-Small 0 0 Y Y 600 true

The name has to be one you can use. And it needs to be AUTO RESUME or you need it STARTED or to START IT

ALTER WAREHOUSE <name> RESUME IF SUSPENDED;

It's nice to have DEFAULT_WAREHOUSE set on your user also, so you don't have set it each time

enter image description here

 ALTER USER <user_name> SET DEFAULT_WAREHOUSE = <warehouse_name>;
user_439920

So I've finally managed to figure out what was the problem:

  1. At first I solved it by granting 'USAGE' privileges to 'PUBLIC'. However, this was not making sense to me because I configured a different user than 'PUBLIC' to my Snowflake Python Connector. The user I am using was assigned a different role.

  2. Then, I figured that I did not configure a role to my Snowflake Python Connector. After I configured the relevant role to the Python connector, granting 'USAGE' privileges to that role solved the problem. I suppose that when no role is configured to the Python Connector, 'PUBLIC' role is used by default.

Since I'm just starting off with Snowflake and this is all based on my trial and error experiments, there may be some more details/info that can be shared by anyone who is more experienced in Snowflake configuration. So that could be helpful for others who experience similar problems.

user_62397420
Related