Snowflake
The Snowflake origin reads data from a Snowflake database. You can use the Snowflake origin to read from any accessible Snowflake database, including those hosted on Amazon S3, Microsoft Azure, and private Snowflake installations.
When reading data from Snowflake, the origin stages the data in an internal stage. The origin can read data from a specified table or using a specified query. It can also perform incremental reads.
When you configure the origin, you specify the Snowflake region, database, table, and schema to use. You also specify the user account and password. You can optionally specify a custom role that overrides the default role for the user account. The user account or the custom role must have the required Snowflake privileges.
You can also use a connection to configure the origin.
You define the read type to perform and related properties, such as the table or query to use. If you enable incremental reads, you also specify the initial offset and offset column to use. The Snowflake origin supports numeric and datetime offsets.
By default, the origin performs a bulk read, also known as a copy unload. When not performing a copy unload, you can specify the partition size to use. You can configure the origin to preserve existing capitalization for column names. You can also specify the number of connections to use, enable pushdown optimization, and configure additional Snowflake properties.
Required Privileges and Custom Roles
The Snowflake origin requires a Snowflake role that grants the following privileges:
Object | Privilege |
---|---|
Internal Snowflake Stage | READ |
Table | SELECT |
- Assign the custom role as the default role
- In Snowflake, assign the custom role as the default role for the Snowflake user account specified in the stage. A Snowflake user account is associated with a single default role.
- Override the default role with the custom role
- In the stage, use the Role property to specify the name of the custom role. The custom role overrides the role assigned to the Snowflake user account specified in the stage.
- Use a Snowflake connection
- When working with Control Hub, you can configure a Snowflake connection to provide connection details for Snowflake stages.
Read Mode
The read mode determines how the Snowflake origin reads data from Snowflake.
- Table - Reads all columns from the specified table.
- Query - Reads data from a table based on a specified query. You might use query mode to read a subset of columns from a table, or to read a join or union of multiple tables.
Full or Incremental Read
The Snowflake origin can perform a full read or an incremental read each time you run the pipeline. By default, the origin performs a full read of the specified table or query.
When the origin performs a full read, the origin processes all data available in the table or returned by the query each time that the pipeline runs.
When the origin performs an incremental read, the first pipeline run is the same as a full read. When the pipeline stops, the origin stores the offset where it stopped processing. For subsequent pipeline runs, the origin reads the table or query starting from the last-saved offset.
SQL Query Guidelines
When the origin runs in Query read mode, you must specify the SQL query to use.
The SQL query defines the data returned from Snowflake. You can use any valid SQL query but the guidelines for the query depend on whether you configure the origin to perform a full or incremental read.
Incremental Read Query
When you define the SQL query for an incremental read, the query must include a WHERE clause and an ORDER BY clause.
- WHERE clause
- In the WHERE clause, include the offset column and the
offset value. Use the
$offset
variable to represent the offset value. - ORDER BY clause
- In the ORDER BY clause, include the offset column as the
first column to avoid returning duplicate data.Note: Using a column that is not a primary key or indexed column in the ORDER BY clause can slow performance.
Full Read Query
The query for a full read has no specific requirements or limitations.
SELECT * FROM invoice
When you define the SQL query for full mode, you can optionally include the WHERE and ORDER BY clauses using the same guidelines as for incremental mode. However, using these clauses to read from large tables can cause performance issues.
Pushdown Optimization
The Snowflake origin can perform pushdown optimization. When you enable pushdown, the origin pushes all possible processing to the Snowflake database, which can improve performance, especially for large data sets.
You can enable pushdown in the Snowflake origin independently from ludicrous mode. However, using it in conjunction with Ludicrous mode should provide best results. For details on the Spark SQL operators that can be pushed down to Snowflake, see the Snowflake documentation.
Use the Enable Pushdown property on the Connection tab to enable pushdown for the Snowflake origin.
Snowflake Data Types
When reading from Snowflake, the Snowflake origin converts Snowflake data types to Spark data types. The following table describes how this conversion occurs.
Snowflake data types that are not listed in the table are not supported.
Snowflake Data Type | Spark Data Type |
---|---|
ARRAY | StringType |
BIGINT |
DecimalType(38,0) |
BOOLEAN | BooleanType |
CHAR | StringType |
CLOB | StringType |
DATE | DateType |
DECIMAL | DecimalType |
DOUBLE | DoubleType |
FLOAT | DoubleType |
INTEGER | DecimalType(38,0) |
OBJECT | StringType |
TIMESTAMP | TimestampType |
TIME | StringType |
VARIANT | StringType |
Configuring a Snowflake Origin
Configure a Snowflake origin to read data from a Snowflake database.