Snowflake connections

You can use AWS Glue for Spark to read from and write to tables in Snowflake in AWS Glue 4.0 and later versions. You can read from Snowflake with a SQL query. You can connect to Snowflake using a user and password. You can refer to Snowflake credentials stored in AWS Secrets Manager through the AWS Glue Data Catalog. Data Catalog Snowflake credentials for AWS Glue for Spark are stored separately from Data Catalog Snowflake credentials for crawlers. You must choose a SNOWFLAKE type connection and not a JDBC type connection configured to connect to Snowflake.

For more information about Snowflake, see the Snowflake website. For more information about Snowflake on AWS, see Snowflake Data Warehouse on Amazon Web Services.

Configuring Snowflake connections

There are no AWS prerequisites to connecting to Snowflake databases available through the internet.

Optionally, you can perform the following configuration to manage your connection credentials with AWS Glue.

In the following situations, you may require the following:

Reading from Snowflake tables

Prerequisites: A Snowflake table you would like to read from. You will need the Snowflake table name, tableName. You will need your Snowflake url snowflakeUrl, username snowflakeUser and password snowflakePassword. If your Snowflake user does not have a default namespace set, you will need the Snowflake database name, databaseName and the schema name schemaName. Additionally, if your Snowflake user does not have a default warehouse set, you will need a warehouse name warehouseName.

For example:

Additional Prerequisites: Complete the steps To manage your connection credentials with AWS Glue to configure snowflakeUrl, snowflakeUsername and snowflakePassword. To review these steps, see Configuring Snowflake connections, the previous section. To select which Additional network connection to connect with, we will use the connectionName parameter.

snowflake_read = glueContext.create_dynamic_frame.from_options(
  connection_type="snowflake",
  connection_options={
        "connectionName": "connectionName",
        "dbtable": "tableName",
        "sfDatabase": "databaseName",
        "sfSchema": "schemaName",
        "sfWarehouse": "warehouseName",
    }
)

Additionally, you can use the autopushdown and query parameters to read a portion of a Snowflake table. This can be substantially more efficient than filtering your results after they have been loaded into Spark. Consider an example where all sales are stored in the same table, but you only need to analyze sales from a certain store on holidays. If that information is stored in the table, you could use predicate pushdown to retrieve the results as follows:

snowflake_node = glueContext.create_dynamic_frame.from_options(
    connection_type="snowflake",
    connection_options={
        "autopushdown": "on",
        "query": "select * from sales where store='1' and IsHoliday='TRUE'",
        "connectionName": "snowflake-glue-conn",
        "sfDatabase": "databaseName",
        "sfSchema": "schemaName",
        "sfWarehouse": "warehouseName",
    }
)

Writing to Snowflake tables

Prerequisites: A Snowflake database you would like to write to. You will need a current or desired table name, tableName. You will need your Snowflake url snowflakeUrl, username snowflakeUser and password snowflakePassword. If your Snowflake user does not have a default namespace set, you will need the Snowflake database name, databaseName and the schema name schemaName. Additionally, if your Snowflake user does not have a default warehouse set, you will need a warehouse name warehouseName.

For example:

Additional Prerequisites: Complete the steps To manage your connection credentials with AWS Glue to configure snowflakeUrl, snowflakeUsername and snowflakePassword. To review these steps, see Configuring Snowflake connections, the previous section. To select which Additional network connection to connect with, we will use the connectionName parameter.

glueContext.write_dynamic_frame.from_options(
    connection_type="snowflake",
    connection_options={
        "connectionName": "connectionName",
        "dbtable": "tableName",
        "sfDatabase": "databaseName",
        "sfSchema": "schemaName",
        "sfWarehouse": "warehouseName",
    },
)

Snowflake connection option reference

The Snowflake connection type takes the following connection options:

You can retrieve some of the parameters in this section from a Data Catalog connection (sfUrl, sfUser, sfPassword), in which case you are not required to provide them. You can do this by providing the parameter connectionName.

You can retrieve some of the parameters in this section from an AWS Secrets Manager secret (sfUser, sfPassword), in which case you are not required to provide them. The secret must provide the content under the sfUser and sfPassword keys. You can do this by providing the parameter secretId.

The following parameters are used generally when connecting to Snowflake.

The following options are used to configure specific behaviors during the process of connecting to Snowflake.

Additionally, some of the options available on the Snowflake Spark connector may be supported in AWS Glue. For more information about options available on the Snowflake Spark connector, see Setting Configuration Options for the Connector in the Snowflake documentation.

Snowflake connector limitations

Connecting to Snowflake with AWS Glue for Spark is subject to the following limitations.