Whenever I use Database Query component to load data from Snowflake database, it is giving the message of "No active warehouse selected in the current session. Select an active warehouse with the 'use warehouse' command."
But when I use to load data from Postgres database, it is loading like a charm. I have reviewed the connection details and all the properties of the component. Other components which are communicating with the Snowflake are working fine with the same connection configuration. I have used the SQL Script component to execute the USE DATAWAREHOUSE and USE SCHEMA commands to avoid this error but no help.
This error usually pops up when the snowflake WH is in a suspended or non active state. Did you double check that the environment defaults (or target parameter settings) are pointing to the same snowflake warehouse on the other components that you mentioned are working properly?
I tried making Snowflake Warehouse active explicitly and then tried loading but same error. Yes, I rechecked all other components are pointing to same database, warehouse and schema and doing all the things like creating a table, insertion/updation etc. but only this component is stuck.
One possible issue could be as @kbafia mentioned where the warehouse on the Environment needs to be set. We had an issue where the warehouse was set on the environment but then in Snowflake someone renamed the warehouse which broke Matillion because the Matillion couldn't find the the warehouse that it was configured to run on.
When I mentioned Environment, this is what I am referring to:
I would be interested to know your use case for needing to use the Database Query component for querying Snowflake. I ask because that would be that you are effectively querying Snowflake to insert the data back into another table in Snowflake. Are you doing some sort of transformation using the Database Query component? I'm more curious than anything else. We maybe doing something different for the same scenario and it may make sense to go your route or vice versa.
Yes, I checked this also and tested the environment defaults and it is success. Moreover, I am using the environment default as a value of warehouse property of Database Query component and warehouse name is not changed in Snowflake as well.
If for say name of the warehouse is changed in Snowflake, then other components or jobs should also not run because they are also using the same environment default warehouse.
As far as the reason for using this component is concerned, I was curious to load the data from Snowflake DB to stage as I have made some raw tables in Snowflake. This component is for loading data from wide range of databases, so I was checking its functionality for Snowflake.
Can you kindly use this component to load data in your Matillion instance? or Can you suggest an another method to load data to temporary stage from Snowflake DB tables?
I got it as issue has been resolved by my colleague. He was also facing the same trouble and he solved it this way.
Here is what worked. He carefully read the help of Database Query Component and found this link. From this page he tried different possibilities for configuring Connection URL property of the component.
For me and him the following configuration for JDBC worked jdbc:snowflake://myorganization-myaccount.snowflakecomputing.com/?warehouse=mywh_name
