snowflake

DolphinDB Server 2.00.16+ or 3.00.4+, and the package type is Linux x86_64 or Linux x86_64 ABI.

1. In a DolphinDB client, use the listRemotePlugins function to view the plugins available for installation.

   login("admin", "123456")
   listRemotePlugins()

2. Use the installPlugin function to install the plugin.

   installPlugin("snowflake")

3. Use the loadPlugin function to load the plugin.

   loadPlugin("snowflake")

Syntax

snowflake::connect(config)

Details

Connects to a Snowflake database and returns a connection handle.

Parameters

config is a dictionary (Dictionary<STRING, ANY>) specifying the connection settings. It supports the following key-value pairs:

The options dictionary supports the following key-value pairs:

Returns

A connection handle.

Example

// Basic configuration
config = dict(STRING, ANY)
config["account"] = "myorg-myaccount"
config["user"] = "DDB_READER"
config["privateKeyPath"] = "/opt/dolphindb/keys/snowflake_rsa_key.p8"
config["warehouse"] = "DDB_WH"
config["database"] = "SALES_DB"
config["schema"] = "PUBLIC"
config["role"] = "DDB_READER_ROLE"

// Set the private key passphrase and timeout through the options dictionary
connOptions = dict(STRING, ANY)
connOptions["privateKeyPassphrase"] = "<PRIVATE_KEY_PASSPHRASE>"
connOptions["connectTimeoutSeconds"] = 20
connOptions["requestTimeoutSeconds"] = 120
connOptions["pollIntervalMillis"] = 1000
connOptions["maxPollSeconds"] = 1800
connOptions["maxRetry"] = 5
config["options"] = connOptions

conn = snowflake::connect(config)

Syntax

snowflake::close(conn)

Details

Closes the specified connection. After the connection is closed, passing its handle to any plugin method causes the method to return the following error: Invalid connection object.

Parameters

conn is a connection handle created by connect.

Returns

The string "Connection is closed."

Syntax

snowflake::showTables(conn, [database], [schema])

Details

Shows the tables or views visible to the current Snowflake user.

Parameters

conn is a connection handle created by connect.

database is a STRING scalar specifying the database to query. The default is the database specified when the corresponding connection handle was created.

schema is a STRING scalar specifying the schema to query. The default is the schema specified when the corresponding connection handle was created.

Returns

A table containing the following columns, or an empty table if no visible objects are found:

Example

// Query the connection's default database and schema
objects = snowflake::showTables(conn)

// Specify the database while using the connection's default schema
objects = snowflake::showTables(conn, "ARCHIVE_DB")

// Specify both the database and schema
objects = snowflake::showTables(conn, "ARCHIVE_DB", "REPORTING")

// Specify only the schema; set the database parameter to NULL
objects = snowflake::showTables(conn, NULL, "REPORTING")

Syntax

snowflake::extractTableSchema(conn, sourceTableName)

Details

Obtains the schema of a Snowflake table.

Parameters

conn is a connection handle created by connect.

sourceTableName is a STRING scalar specifying the Snowflake table name. Table names can use one-, two-, or three-part formats: TABLE_NAME, SCHEMA_NAME.TABLE_NAME, or DATABASE_NAME.SCHEMA_NAME.TABLE_NAME.

Returns

A table containing the following columns:

Syntax

snowflake::extractQuerySchema(conn, sql)

Details

Executes the specified SELECT statement against a Snowflake table to obtain the schema of the query result.

Parameters

conn is a connection handle created by connect.

sql is a STRING scalar specifying the query SQL statement. The plugin removes the trailing semicolon from the SQL statement and wraps the query in a statement similar to the following:

SELECT *
FROM (<sql>) AS SNOWFLAKE_PLUGIN_SCHEMA
WHERE 1 = 0

Therefore, sql must be a SELECT statement that can be used as a subquery. Do not pass DDL or DML statements to this method.

Returns

A table containing the following columns:

Syntax

snowflake::loadTable(conn, sourceTableName, [options])

Details

Loads data from a Snowflake table and returns it as a DolphinDB in-memory table.

Parameters

conn is a connection handle created by connect.

sourceTableName is a STRING scalar specifying the Snowflake table name. Table names can use one-, two-, or three-part formats: TABLE_NAME, SCHEMA_NAME.TABLE_NAME, or DATABASE_NAME.SCHEMA_NAME.TABLE_NAME.

options (optional) is a dictionary (Dictionary<STRING, ANY>) supporting the keys "columnSchema", "offset", "limit", and "allowEmptyResult". For details, see Common Parameter for Data Query Methods.

Returns

A DolphinDB in-memory table.

Example

// Do not specify additional options
orders = snowflake::loadTable(conn, "SALES_DB.PUBLIC.ORDERS")

// Return data in pages
options = dict(STRING, ANY)
options["offset"] = 1000
options["limit"] = 500
options["allowEmptyResult"] = true

ordersPage = snowflake::loadTable(
    conn,
    "SALES_DB.PUBLIC.ORDERS",
    options
)

// User-defined column types
columnSchema = table(
    ["order_id", "customer", "amount"] as name,
    ["LONG", "STRING", "DOUBLE"] as type
)

options = dict(STRING, ANY)
options["columnSchema"] = columnSchema

orders = snowflake::loadTable(
    conn,
    "SALES_DB.PUBLIC.ORDER_SUMMARY",
    options
)

Syntax

snowflake::query(conn, sql, [options])

Details

Executes the specified SELECT statement on a Snowflake table and returns the query results as a DolphinDB in-memory table.

Parameters

conn is a connection handle created by connect.

sql is a STRING scalar specifying the query SQL statement. The plugin removes the trailing semicolon from the SQL statement and wraps the query in a statement similar to the following:

SELECT *
FROM (<sql>) AS SNOWFLAKE_PLUGIN_SCHEMA
WHERE 1 = 0

Therefore, sql must be a SELECT statement that can be used as a subquery. Do not pass DDL or DML statements to this method.

options (optional) is a dictionary (Dictionary<STRING, ANY>) supporting the keys "columnSchema" and "allowEmptyResult". For details, see Common Parameter for Data Query Methods.

Returns

A DolphinDB in-memory table.

Example

// Do not specify additional options
sql = "SELECT ORDER_ID, AMOUNT FROM SALES_DB.PUBLIC.ORDERS WHERE AMOUNT > 100 ORDER BY ORDER_ID"
result = snowflake::query(conn, sql)

// Return data in pages
sql = "SELECT ORDER_ID, AMOUNT FROM SALES_DB.PUBLIC.ORDERS ORDER BY ORDER_ID LIMIT 500 OFFSET 1000"

options = dict(STRING, ANY)
options["allowEmptyResult"] = true

result = snowflake::query(conn, sql, options)

Syntax

snowflake::loadTableInto(conn, sourceTableName, targetTable, [options])

Details

Loads data from a Snowflake table and writes it to a DolphinDB table.

Parameters

conn is a connection handle created by connect.

sourceTableName is a STRING scalar specifying the Snowflake table name. Table names can use one-, two-, or three-part formats: TABLE_NAME, SCHEMA_NAME.TABLE_NAME, or DATABASE_NAME.SCHEMA_NAME.TABLE_NAME.

targetTable is a DolphinDB in-memory table or DFS table.

options (optional) is a dictionary (Dictionary<STRING, ANY>) supporting the keys "columnSchema", "offset", "limit", and "batchTransform". For details, see Common Parameter for Data Query Methods.

Returns

None.

Example

// Write to a DolphinDB in-memory table
target = table(
    100000:0,
    `order_id`customer`amount,
    [LONG, STRING, DOUBLE]
)

writtenRows = snowflake::loadTableInto(
    conn,
    "SALES_DB.PUBLIC.ORDER_SUMMARY",
    target
)

// Limit the number of rows read
options = dict(STRING, ANY)
options["limit"] = 10000

writtenRows = snowflake::loadTableInto(
    conn,
    "SALES_DB.PUBLIC.ORDER_SUMMARY",
    target,
    options
)

Syntax

snowflake::queryInto(conn, sql, targetTable, [options])

Details

Executes the specified SELECT statement on a Snowflake table and writes the query results to a DolphinDB table.

Parameters

conn is a connection handle created by connect.

sql is a STRING scalar specifying the query SQL statement. The plugin removes the trailing semicolon from the SQL statement and wraps the query in a statement similar to the following:

SELECT *
FROM (<sql>) AS SNOWFLAKE_PLUGIN_SCHEMA
WHERE 1 = 0

Therefore, sql must be a SELECT statement that can be used as a subquery. Do not pass DDL or DML statements to this method.

targetTable is a DolphinDB in-memory table or DFS table.

options (optional) is a dictionary (Dictionary<STRING, ANY>) supporting the keys "columnSchema" and "batchTransform". For details, see Common Parameter for Data Query Methods.

Returns

None.

Example

// Write to a DolphinDB in-memory table
target = table(
    100000:0,
    `order_id`customer`amount,
    [LONG, STRING, DOUBLE]
)

sql = "SELECT ORDER_ID, CUSTOMER, AMOUNT FROM SALES_DB.PUBLIC.ORDERS ORDER BY ORDER_ID"
writtenRows = snowflake::queryInto(conn, sql, target)

// Write to a DolphinDB DFS table
target = loadTable("dfs://sales", `orders)

sql = "SELECT ORDER_ID, CUSTOMER, AMOUNT FROM SALES_DB.PUBLIC.ORDERS"
writtenRows = snowflake::queryInto(conn, sql, target)

The plugin maps types based on thetype, precision, and scale in the metadata returned by the Snowflake SQL API, rather than determining them solely from the type names used in the table-creation DDL.

The following Snowflake data types are currently mapped to STRING:

• CHAR, VARCHAR, TEXT

• DATE, TIME

• TIMESTAMP_NTZ, TIMESTAMP_LTZ, TIMESTAMP_TZ

• BINARY, VARBINARY

• ARRAY, OBJECT, VARIANT

• GEOGRAPHY, GEOMETRY, VECTOR

• All other types not listed in the mapping rules above

For types mapped to STRING, the returned value is the textual representation of the value returned by the Snowflake SQL API. In particular, do not currently assume that the return values for DATE, TIME, or TIMESTAMP are native DolphinDB temporal types.

columnSchema supports the following target types and aliases. Type names are case-insensitive.

Non-NULL text refers to the textual representation of a non-NULL field value returned by the Snowflake SQL API.

If columnSchema specifies a type not included in the list above, the API returns the following error: schema type ... is not supported yet

When requesting data from the Snowflake SQL API, the plugin sets nullable=true and processes the returned values according to the following rules:

• Snowflake SQL NULL: Returned as JSON null and converted to the NULL value of the corresponding DolphinDB type.

• The string "null": Treats it as an ordinary string rather than NULL.

• Non-NULL empty string: DolphinDB uses an empty string to represent NULL. Consequently, directly converting a non-NULL empty string from Snowflake would incorrectly interpret it as NULL, so the plugin blocks the conversion and reports an error.

• Non-NULL numeric value: If a non-NULL numeric value returned by Snowflake equals the NULL representation of the corresponding DolphinDB type, directly converting it would incorrectly interpret it as NULL. To prevent data errors, the plugin blocks the conversion and reports an error. For example, the minimum INT value returned by Snowflake, the minimum LONG value, and any numeric value equal to the DolphinDB DOUBLE NULL representation cannot be converted as non-NULL values.

• Numeric conversion: Numeric text must be fully parsed as the target type. A conversion error is returned if the text contains extra characters or exceeds the range representable by the target type.

• NUMBER/DECIMAL: When scale != 0, the value is mapped to DOUBLE by default, which may result in floating-point precision loss. To preserve the original numeric text exactly, specify the column as STRING through columnSchema.