We are extending an existing process to access a new Snowflake database. Thus is Release 9.04.01M7P080520
In the existing processes that all work for over a year now, the WAREHOUSE does not need to be defined in the libname -- there is a default warehouse associated with the role.
In the new database there is no default WAREHOUSE, and there is a new ROLE. The signon is the same Snowflake account that we have used all the time.
So to start with we issue this libname:
libname FCH_data SNOW
server ="bnz_dap_prod_aws.ap-southeast-2.privatelink.snowflakecomputing.com"
user =SRV_FCH_SAS_UAT02_SASCONNECT
pw ='XXXXXXXXXXXXXXXXX'
database =DB_LENDING_UAT02
role =RL_SF_DP_MDS_CONSUMER_UAT02
;
This results in
60 create table mytable as
61 (select * from connection to fch_data
62 (
63 select * from DB_LENDING_UAT02.MDS_CCD.ccd_accounts_sas
64 limit 10
65
66 )
67 )
68 ;
ERROR: CLI open cursor error: No active warehouse selected in the current session. Select an active warehouse with the 'use
warehouse' command.
So thats fairly simple, I need to define a warehouse. So I got the warehouse from the Snowflake team and submitted this libname (the Snowflake admin confirmed that all necessary grants are in place and I sent them the grant stream from the original database to mimic)
libname FCH_data SNOW
server ="bnz_dap_prod_aws.ap-southeast-2.privatelink.snowflakecomputing.com"
user =SRV_FCH_SAS_UAT02_SASCONNECT
database =DB_LENDING_UAT02
role =RL_SF_DP_MDS_CONSUMER_UAT02
warehouse =wh_lending_m
It is clear though that this did not set the warehouse. We got the same No ACTIVE WAREHOUSE message. So I added a simple check of running an SQL to query the warehouse and sure enough there is no warehouse defined.
create table mytable as
(select * from connection to fch_data
(
SELECT CURRENT_WAREHOUSE();
)
)
;
So I commented out the ROLE in the libname and tried again, but the warehouse doesnt get set.
After reading up a bit on Snowflake I realised the libname isnt the only way to set the WAREHOUSE. I can just execute the USE WAREHOUSE XXXXX command
So I did this
proc sql ;
connect using fch_data ;
execute (use warehouse wh_lending_m) by fch_data ;
That also fails, with this Snowflake message
ERROR: CLI execute error: SQL compilation error: Object does not exist, or operation cannot be performed.
At this point one of my teammates started to experiment and removed the ROLE from the libname. That failed, but then he changed the WAREHOUSE to the original one (which we dont want to use as the COMPUTE cost shouldnt be against that database). That worked and set the WAREHOUSE. The query didnt work as there is no ROLE.
So I placed the ROLE in an EXECUTE instead of the libname
execute (use role RL_SF_DP_MDS_CONSUMER_UAT02;) by fch_data ;
select current_role() shows that worked fine.
So I then added the WAREHOUSE
execute (use role RL_SF_DP_MDS_CONSUMER_UAT02;) by fch_data ;
execute (use warehouse wh_lending_m;) by fch_data ;
The role was set, the WAREHOUSE is not ACTIVE. So that tends to verify that the problem is unrelated to the libname.
I've tried flipping the WAREHOUSE to run before the USE ROLE but no WAREHOUSE is defined
So we have this situation, whether its libname or EXECUTE
* if the warehouse is defined as the new warehouse in the new database, it does not get set whether from the libname or execute. No query runs as there is no warehouse. select current_warehouse() verifies no warehouse exists
* if the warehouse is the old warehouse which we have a directive to not use, then it is set provided the ROLE is not set in libname or EXECUTE.
* The old role cant run queries in the new database (thats intentional there is no grant for that role, and if it could the COMPUTE costs would be against wrong database) . We will get that grant done to see if it works.
But -- has anyone struck this issue of WAREHOUSE and ROLE being in conflict from SAS??? OI did think maybe the Snowflake grants are not right, but Snowflake admin is sure they are ...
