Module snowflake
![](https://bcentral-packageicons.azureedge.net/images/ballerinax_snowflake_1.2.1.png)
ballerinax/snowflake Ballerina library
Overview
The Ballerina connector for Snowflake allows you to programmatically access all of the Snowflake applications, databases, APIs, services via the Java Database Connectivity (JDBC) API using Ballerina. It provides operations to execute a wide range of standard DDL Commands, SQL Commands, and SQL Functions for querying data sources. You can find reference information for all the Snowflake SQL commands (DDL, DML, and query syntax) here.
Prerequisites
Before using this connector in your Ballerina application, complete the following:
To connect to Snowflake
- Create a Snowflake account.
- Obtain the
username
andpassword
which you use to login to Snowflake account andaccount_identifier
which uniquely identifies a Snowflake account within your business entity, as well as throughout the global network of Snowflake.
Quickstart
To use the Snowflake connector in your Ballerina application, update the .bal file as follows:
Step 1: Import connector and driver
Import the following modules into the Ballerina project:
import ballerina/sql; import ballerinax/snowflake; // Get the Snowflake connector import ballerinax/snowflake.driver as _; // Get the Snowflake driver
Step 2: Create a new connector instance
Provide the account_identifier
, <username>
and <password>
to initialize the Snowflake connector.
Options should be provided as follows, because requestGeneratedKeys
option must be set to snowflake:NONE
as snowflake does not support the retrieval of auto-generated keys.
snowflake:Options options = { requestGeneratedKeys: snowflake:NONE // This should be specified };
Depending on your requirement, you can also pass additional optional properties during the client connector initialization. For more information on connection string properties, see Connection String Options.
<account_identifier>
is the unique identifies a Snowflake account.<username>
is the username you use to login to Snowflake account.<password>
is the password you use to login to Snowflake account.
string account_identifier = "<account_identifier>"; // Eg: "z******.europe-west4.gcp" string user = "<username>"; string password = "<password>"; snowflake:Options options = { requestGeneratedKeys: snowflake:NONE // This should be specified }; snowflake:Client snowflakeClient = check new (account_identifier, user, password, options);
You can also define <account_identifier>
, <username>
and <password>
as configurable strings in your Ballerina program.
Step 3: Invoke the connector operation
-
Use the Snowflake connector to consume all of the Snowflake applications, databases, APIs, services via the Java Database Connectivity (JDBC) API using Ballerina.
Now let’s take a look at a few sample operations.
Let’s assume,
COMPANYDB
is the database name.PUBLIC
is the schema name.EMPLOYEES
is the table name
Use the
query
operation to query data.Following is a sample code to query data from a table.
public function main() returns error? { sql:ParameterizedQuery sqlQuery = `SELECT * FROM COMPANYDB.PUBLIC.EMPLOYEES LIMIT 10`; stream<record {}, error?> resultStream = snowflakeClient->query(sqlQuery); check from record{} result in resultStream do { io:println("Full details of employee: ", result); }; }
Use the
execute
operation to perform DML and DDL operations.Following is a sample code to insert data into a table
public function main() returns error? { sql:ParameterizedQuery sqlQuery = `INSERT INTO COMPANYDB.PUBLIC.EMPLOYEES (FirstName, LastName, Company) VALUES ('Shawn', 'Jerome', 'WSO2')`; _ = check snowflakeClient->execute(sqlQuery); }
Use the
batchExecute
operation to perform a batch of DML and DDL operations.Following is a sample code to insert multiple records into a table
public function main() returns error? { var insertRecords = [ { FirstName: "Gloria", LastName: "Shania", Company: "ABC" }, { FirstName: "Shane", LastName: "Warny", Company: "BCA" }, { FirstName: "Neo", LastName: "Mark", Company: "CAB" } ]; sql:ParameterizedQuery[] insertQueries = from var data in insertRecords select `INSERT INTO COMPANYDB.PUBLIC.EMPLOYEES (FirstName, LastName, Company) VALUES (${data.FirstName}, ${data.LastName}, ${data.Company})`; _ = check snowflakeClient->batchExecute(insertQueries); }
Use the
call
operation to execute a stored procedure.Following is a sample code to execute the stored procedure named
getEmployeeInfo
public function main() error? { sql:ParameterizedCallQuery sqlQuery = `{CALL COMPANYDB.PUBLIC.getEmployeeInfo()}`; _ = check snowflakeClient->call(sqlQuery); }
-
Use
bal run
command to compile and run the Ballerina program.