Can't get Snowflake bulk uploder to work with new key-pair authentication to AWS

[pozzyal]

I'm trying to get the snowflake bulk uploader to work with snowflake key-pair authentication, and just can't make it work. Is anyone doing this? I can't find any decent documentation to describe how to do it, and no matter what I do within the interface, I can't get it to stick. I have no problem getting snowflake input and output connectors to work with the key-pair, but the output is far too slow for our needs and we need bulk uploading to work with key-pair.

The problem is when I get to the screen to pick my connections:

 

When I click the snowflake Bullk connection button, the only optoin it gives me is for username/password, but I need key-pair

 

Can anyone help? Does any step-by-step documentation exist for this? 

Thanks!

[jrlindem]

The method I use to handle credentials for snowflake is to use the ODBC Admin panel on my PC and then configured a Snowflake Driver connection that gets used by Alteryx.  In this way I'm not using DCM.  I'm not sure if that's helpful, but you're right finding good documentation on this is hard to find.

[SPetrie]

I believe DCM is the recommended method for storing the info, but you can use ODBC as well which is what we currently use. I found this one from Alteryx which gives their suggested setup.

[pozzyal]

Unfortunately, DCM doesn't appear to work for this function, unless I'm missing something. We'd prefer to use it, all the options are available in the GUI, this just seems like a bug, but I'm hoping someone has found a way. What do you mean you "use ODBC as well"? How/where do you configure the S3 bucket keys in ODBC?

[jrlindem]

@pozzyal you don't configure them in the S3 bucket.  They contain the "instructions" for connecting in the same way you would configure or connect via other systems.  You setup the configuration identifiers like (below) and then call for that connection in Alteryx.

So it uses those instructions to connect.  In red you can see I'm using externalbrowser for authentication, but the server address in yellow is how it knows where to connect.

[pozzyal]

Yes, but isn't this just the connection to Snowflake, not the 2nd connection needed to AWS for bulk upload? I need to get it to take both these values, but can't figure out how or where. My snowflake authentication is fine, it's bulk upload that I can't make work. Does that make sense? A co-worker said he was just able to make it work by coding the odbc string directly, but we should be able to use the Alteryx interface as designed.

 

[SPetrie]

Are you on 2024.1 or newer? I believe Keypair is only supported inside Alteryx from 2024.1 and onward.

If you have one of the newer snowflake ODBC drivers installed, you can setup the key file directly in the ODBC config.

The setup you show in your original screenshot is attempting to use DCM, but this method would not use that, so you need to uncheck use DCM and point it to the ODCB connection that has the keypair configured.

If you want to use DCM and have 2024.1 or newer, you need to create your credentials in the DCM and use Snowflake Key-Pair as the credential type

Once you have that created you can link it to a data source for Snowflake Bulk using the staging method you prefer.

[pozzyal]

It still doesn't work with DCM, when you go to connect the credentials, the only option available is username/pw, there's no way to use the key-pair. This appears to be a bug. As a workaround we can do it once not using DCM and then just copy the connection string, as it would be ridiculous to have to re-configure this thing every time we want a new connection, and DCM can't seem to save it. I am using 2024.1

[pozzyal]

@jrlindem If you're not using DCM, does that mean there's no way to save this connection? That appears to be the case to me, which makes this incredibly clunky. Do you have a workable solution for each time you add a new connector? In theory we can just copy/paste an existing one, but that's not ideal, I'd prefer a clean way to set up a connection and re-use it as it's deisgned to do. Thanks!

[dataguyW]

We just went through all of this.  We upgraded to 2024.2 to be above the version and I tested out the Key/Pair method.  All worked fine using Gallery connection as setup above.  The main issue is that all users must have access to the file location and file that holds the passphrase.     It can work just fine.    There is also a new method that can be used within SnowFlake as well called a Programmatic Access Token (PAT) that to the naked eye looks just like an incredibly long password (paragraph) and simply replaces the password.    It locks down to network filtering rules, and to 1 and only 1 Role.    This makes cutting away from older methods easier as there is no change for the developers or odbc updates.   Just throwing another idea out there if you run into a jam on this.