querying snowflake database using databricks query federation: no active warehouse

[Eeg]

Hello Databricks community,

I'm confused right now because I was able to query snowflake table using query federation 2 days ago. But now it's giving me error about no active warehouse:

Status of query associated with resultSet is FAILED_WITH_ERROR. No active warehouse selected in the current session. Select an active warehouse with the 'use warehouse' command.

Please check the attached the error screenshot if you want further details on error.

Other details:

No change was made on snowflake side, with default warehouse been sat.

I created databricks connection by filling the needed fields for this scipt which was referenced from this link: https://docs.databricks.com/en/query-federation/snowflake.html#language-SQL

CREATE CONNECTION snowflake_connection1 TYPE snowflake
OPTIONS (
host '<hostname>',
port '<port>',
sfWarehouse '<warehouse-name>',
user secret ('<secret-scope>','<secret-key-user>'),
password secret ('<secret-scope>','<secret-key-password>')
)

CREATE FOREIGN CATALOG IF NOT EXISTS table USING CONNECTION snowflake_connection1
OPTIONS (database 'PR_JAPAN_APP_PROD_DB');

 Have you guys every had same difficulty?

[Alberto_Umana]

Hello @Eeg,

The error message indicates that no active warehouse is selected. You can explicitly set the warehouse in your session using the USE WAREHOUSE command

USE WAREHOUSE <warehouse-name>;

And make sure that there is an active SQL warehouse running, and attached it to the notebook.

[Eeg · Accepted solution]

Hello@Alberto_Umana 
Thank you very much for you response.

I was able to solve it on my side. The issue was lying on snowflake side. I realized I had to not only grant USAGE permission but also OPERATE permission to my snowflake account. Also added sfRole to the connection. After renewing connection, recreated the external table one more time and it worked on my side. 

CREATE CONNECTION snowflake_connection1 TYPE snowflake
OPTIONS (
host '<hostname>',
port '<port>',
sfWarehouse '<warehouse-name>',
sfRole '<role-name>',
user secret ('<secret-scope>','<secret-key-user>'),
password secret ('<secret-scope>','<secret-key-password>')
)