Help with basic connection to Snowflake

[Paul_Holden]

Hi, I'm looking for some assistance with setting up a basic connection to Snowflake using the Input tool and ODBC via a data connection configured on the Gallery.

I've tried searching the community but I'm struggling to find any directly relevant information. A lot of the KB articles appear to be redacted, possibly because they are no longer relevant to current versions of Alteryx/Snowflake.

My setup is as follows:

Alteryx version 2024.2

On the Gallery I have set up a data connection, let's call it MySnowFlakeConnection

I have assigned an ODBC string with the following format (I am aware that username/password is deprecated for SnowFlake. I just need to get this working as poc and then move to OAuth later)

ODBC:driver={SnowflakeDSIIDriver};server=myserver.region.azure.snowflakecomputing.com;uid=mySnowFlakeUserName;pwd=************;database=mySnowFlakeDatabase;schema=mySnowFlakeSchema;warehouse=mySnowFlakeWarehouse;role=mySnowFlakeRole

I have installed the Snowflake DSII Driver onto the Gallery server and my desktop running designer.

My issues are as follows:

When I attempt to add the connection to the Input tool, or any time I attempt to edit the SQL (bring up the SQL editing dialogue) it takes in excess of 20 minutes for the dialogue to appear. I can live with this but it doesn't seem correct?

When I attempt to run a query I get the following error:

Error: Input Data (1): Error SQLExecute: No active warehouse selected in the current session. Select an active warehouse with the 'use warehouse' command.

I have been told that I need to execute a USE <WarehousName> command to bring the warehouse online, but I can't figure out where to put that command? It isn't recognised if I add it to the SQL code or if I put it in the "Pre SQL statement" field?

I have successfully managed to run a query previously but I can only assume that for whatever reason the Warehouse was running at that point and isn't running now?

Any assistance gratefully received. If I need to take a different approach to this then so be it but if I can get the above "simple" approach working with a few tweaks that would be all I need for now.

Note that the next step would be publishing the poc workflow to the Gallery and have it run unattended so I need a solution that allows for that to happen.

Regards,

Paul

[SPetrie]

Are you sure the user id and/or role you are attempting to use has been granted usage on the warehouse you are trying to use in your connection string? Last time I saw that error message was because of insufficient privileges. 

It should not be taking that long to load a dialog either, so something may be going on. I created a test data connection with a string in this format and was able to load a table pretty quick.

odbc:Driver={SnowflakeDSIIDriver};Server=myserveraddress.com;Uid=userid;Pwd=__EncPwd1__;warehouse=warehouseIuse

[apathetichell]

1) you can configure your warehouse in your odbc.

2) you can configure a default warehouse in snowflake ---> and for enterprise settings that is the only correct way. A standard user has a standard warehouse. They are granted and controlled in Snowflake and set up as a default. Your connection just defaults to that.

Gemini sample --->  ALTER USER your_user_name SET DEFAULT_WAREHOUSE = your_default_warehouse;

[Paul_Holden · Accepted solution]

Thanks both.

We already have the warehouse configured in the ODBC. Apologies if that wasn't clear from my original post.

We previously confirmed that the user has access to the warehouse. The warehouse is, in fact, specific to the user account that we have set up in Alteryx.

The issue was purely that the warehouse was not starting and we could not determine how to configure the "USE <Warehouse>" command in order to "wake it up" for processing. We are still not sure how to do that from within the Alteryx Data Input tool or if it is even possible.

The solution for us has been to go back to our Snowflake data engineering team and have them adjust the configuration of the warehouse so that it wakes up automatically when a query is run - at least that is my understanding of what has been done. To quote the response from the engineering team...

"[we] have applied new roles to the user and warehouse."

I will comment further if we find a reason/solution to the issue where the SQL query editing window is taking ~20 minutes to refresh each time it is opened.

[OllieClarke]

@Paul_Holden You can run a "Pre SQL Statement" (option 9 in the input data tool) which will say "USE <Warehouse>". This will run before the configured query, so should remove that issue.

I'm not sure about the taking 20 mins to load the query window though, that definitely isn't normal...

Ollie

[Paul_Holden]

>>You can run a "Pre SQL Statement" (option 9 in the input data tool) which will say "USE <Warehouse>". This will run before the configured query, so should remove that issue.

@OllieClarke tried that at one point. It might be we didn't get the syntax correct, or the Snowflake permissions were not set up correctly but we just got a compilation error error.

Good to know that is the correct place to issue the command though. I'll revisit that if I get some time. At this point we have it working via the updated warehouse configuration.

>>I'm not sure about the taking 20 mins to load the query window though, that definitely isn't normal...

Yes, I'm still puzzling over this. It seems like it is just taking an age to return the schema information to populate the query builder?