topic Re: querying snowflake database using databricks query federation: no active warehouse in Data Engineering

querying snowflake database using databricks query federation: no active warehouse

Eeg — Thu, 21 Nov 2024 06:28:54 GMT

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?

Re: querying snowflake database using databricks query federation: no active warehouse

Alberto_Umana — Thu, 21 Nov 2024 16:06:07 GMT

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.

Re: querying snowflake database using databricks query federation: no active warehouse

Eeg — Thu, 28 Nov 2024 01:51:56 GMT

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>') )