How to add private_key_file to snowflake connection in airflow

Question

I'm trying to create a DAG on airflow that performs some query on snowflake. For our implementation we decided to connect using a private key in stead of using a password.

When I try to add the private_key_file to the extra field (see image) in the snowflake connection however it is not correctly stored. It says it saves it correctly but afterwards the key and value are not in the extra field. However the following is automatically inserted:

{"extra__snowflake__account": "", 
"extra__snowflake__aws_access_key_id": "",
"extra__snowflake__aws_secret_access_key": "",
"extra__snowflake__database": "", "extra__snowflake__region": "",
"extra__snowflake__warehouse": ""}

In the placeholder example the "private_key_file" is shown however as shown in the image and in the source code the "private_key_file" key is also used to get a value from this field, so I'm pretty sure I'm putting it in the right spot.

airflow version 2.1.1 apache-airflow-providers-snowflake version 2.0.0

How do I add a private key file to a snowflake connection on airflow?

private_key_file should point to the file where you stored the private key. So, it's only a path to that file. Is that your case? — Sergiu
yes, I put in the absolute path to the file which stores the private key. — Marc
The Extra shows authenticator set as oauth, is that what you've set it to or you have the value SNOWFLAKE_JWT as for key pair authentication we use JSON Web Token. Set the value to SNOWFLAKE_JWT if not already set. — Sergiu
I think the main problem is the webserver not allowing me to set the value in the first place. When I try to adjust the authenticator I run into the same problem. I save it and all that remains is : {"extra__snowflake__account": "", "extra__snowflake__aws_access_key_id": "", "extra__snowflake__aws_secret_access_key": "", "extra__snowflake__database": "", "extra__snowflake__region": "", "extra__snowflake__warehouse": ""} — Marc
All of those values were automatically inserted when I only tried setting authenticator, which it no longer contains — Marc

Mark Western Mark Western · Accepted Answer · 2021-09-14T10:35:04

You may have already worked this out but here is how I got around this issue. The web interface does not work for this you need to use the Airflow command line to create the connection with the private_key_file parameter.

You can use a python script as follows to create the Connection URI:

import json
from airflow.models.connection import Connection
c = Connection(
    conn_id='SF_SSO',
    conn_type='snowflake',
    description='cli generated',
    host='https://poc.us-east-1.snowflakecomputing.com/',
    schema='TEST',
    login='[email protected]',
    password='private_key_file_password',
    extra=json.dumps(dict(account='poc', database='TEST', region='us-east-1', warehouse='TEST_WH', private_key_file='/home/username/.snowsql/rsa_key.p8')),
)
print(f"AIRFLOW_CONN_{c.conn_id.upper()}='{c.get_uri()}'")

This should generate a URI that looks something like this:

snowflake://your.name%40yourcompany.com:private_key_file_password@https%3A%2F%2Fpoc.us-east-1.snowflakecomputing.com%2F/TEST?account=poc&database=TEST&region=us-east-1&warehouse=TEST_WH&private_key_file=%2Fhome%2Fusername%2F.snowsql%2Frsa_key.p8

Now using the URI above create the connection using the cli:

airflow connections add 'SF_SSO' --conn-uri 'snowflake://your.name%40yourcompany.com:private_key_file_password@https%3A%2F%2Fpoc.us-east-1.snowflakecomputing.com%2F/TEST?account=poc&database=TEST&region=us-east-1&warehouse=TEST_WH&private_key_file=%2Fhome%2Fusername%2F.snowsql%2Frsa_key.p8'

How to add private_key_file to snowflake connection in airflow

1 Answers