Skip to main content
Dependencies:
  • ProphecyWarehousePython 0.0.1+
  • ProphecyWarehouseScala 0.0.1+
Cluster requirements:
  • UC dedicated clusters 14.3+ supported
  • UC standard clusters 14.3+ supported
  • Livy clusters not supported
You can read from and write to Snowflake.

​
Parameters

ParameterTabDescription
Authentication TypeLocationHow you want to authenticate. Possible values are: Username & Password, or Key Pair Authentication. To configure key pair authentication on Snowflake, see Configuring key-pair authentication.
UsernameLocationUsername of the Snowflake user if you selected Username & Password for the authentication type.
PasswordLocationPassword for the Snowflake user if you selected Username & Password for the authentication type.
Private key filepathLocationLocation of your private key filepath in PKCS8 format if you selected Key Pair Authentication for the authentication type.
Private key passphraseLocationPassphrase of your private key file if you selected Key Pair Authentication for the authentication type.
Snowflake URLLocationHostname for your account in the format: <account_identifier>.snowflakecomputing.com For example: https://DJ07623.ap-south-1.aws.snowflakecomputing.com
DatabaseLocationDatabase to use for the session.
SchemaLocationSchema to use for the session.
WarehouseLocationDefault virtual warehouse to use for the session.
RoleLocationDefault security role to use for the session.
Data SourceLocationStrategy to read data. Possible values are: DB Table or SQL Query.
TableLocationName of the table to be read.
SQL QueryLocationSQL query that contains aSELECT statement to run.

​
Source

The Source gem reads data from Snowflake and allows you to optionally specify the following additional property.

​
Source properties

PropertiesDescriptionDefault
DescriptionDescription of your dataset.None

​
Compiled code

To see the compiled code of your project, switch to the Code view in the project header.
def sf_customer(spark: SparkSession) -> DataFrame:
 from pyspark.dbutils import DBUtils

 return spark.read\
 .format("snowflake")\
 .options(
 **{
 "sfUrl": "https://DJ07623.ap-south-1.aws.snowflakecomputing.com",
 "sfUser": "anshuman",
 "sfPassword": "*******",
 "sfDatabase": "SNOWFLAKE_SAMPLE_DATA",
 "sfSchema": "TPCDS_SF100TCL",
 "sfWarehouse": "COMPUTE_WH"
 }
 )\
 .option("dbtable", "CUSTOMER")\
 .load()

​
Target

The Target gem writes data to Snowflake and allows you to optionally specify the following additional properties.

​
Target properties

PropertyDescriptionDefault
DescriptionDescription of your dataset.None
Write ModeHow to handle existing data. For a list of the possible values, see Supported write modes.overwrite
Run post-script SQLDDL and DML SQL statements to execute before the Target gem writes data. This is intended for statements that do not return a result set, such as DDL statements like CREATE TABLE and DML statements like INSERT, UPDATE, and DELETE.None

​
Supported write modes

Write modeDescription
errorIf the data already exists, throw an exception.
overwriteIf the data already exists, overwrite the data with the contents of the DataFrame.
appendIf the data already exists, append the contents of the DataFrame.
ignoreIf the data already exists, do nothing with the contents of the DataFrame. This is similar to the CREATE TABLE IF NOT EXISTS clause in SQL.

​
Compiled code

To see the compiled code of your project, switch to the Code view in the project header.
def customer_snow_tg(spark: SparkSession, in0: DataFrame):
 from pyspark.dbutils import DBUtils
 options = {
 "sfUrl": "https://DJ07623.ap-south-1.aws.snowflakecomputing.com",
 "sfUser": "anshuman",
 "sfPassword": "******",
 "sfDatabase": "SNOWFLAKE_SAMPLE_DATA",
 "sfSchema": "TPCDS_SF100TCL",
 "sfWarehouse": "COMPUTE_WH"
 }
 writer = in0.write.format("snowflake").options(**options)
 writer = writer.option("dbtable", "CUSTOMERS")
 writer = writer.mode("overwrite")
 writer.save()