Module snowflake
![](https://bcentral-packageicons.azureedge.net/images/ballerinax_snowflake_2.0.0.png)
ballerinax/snowflake Ballerina library
Overview
The Snowflake is a cloud-based data platform that provides a data warehouse as a service designed for the cloud, providing a single integrated platform with a single SQL-based data warehouse for all data workloads. The Snowflake data warehouse uses a new SQL database engine with a unique architecture designed for the cloud. 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.
The ballerinax/snowflake
package allows you to access the Snowflake database via the Ballerina SQL APIs and manage data persistent in the Snowflake database.
Setup guide
To use the Snowflake connector, you must have a valid Snowflake account. If you do not have an account, you can sign up for a account here.
Create a warehouse and database
-
Log in to your Snowflake account.
-
Go to the Warehouses tab under the Admin section, as shown below.
-
Click + Warehouse and select a name and type for a new warehouse, as shown below.
-
Optional - You can set the created warehouse as the default warehouse for the account by editing the profile settings, as shown below.
NOTE If you do not set a default warehouse, you must specify the warehouse name when you create a connection to the Snowflake database.
- Go to the Databases tab under the Data section and click + Database to create a new database, as shown below.
NOTE Create a database can either be created using the Snowflake web interface or using the SQL command with the Snowflake connector.
Quickstart
To use the snowflake connector in your Ballerina application, modify the .bal file as follows:
Step 1: Import the connector
Import the ballerinax/snowflake
package into your Ballerina project.
import ballerinax/snowflake;
Step 2: Import the Snowflake driver into your Ballerina project
import ballerinax/snowflake.driver as _;
Step 3: Instantiate a new connector
NOTE: Snowflake driver fails with Java 16 and above because starting with JDK 16, strong encapsulation was turned on by default and one of the driver dependencies have employed the use of sun.misc.Unsafe along with reflection. This is not allowed in Java 16 and above. Therefore, to run this example, you need to use Java 15 or below. For more information, see here. If you are using Java 16 or above, you can use the following workaround to work with the Snowflake connector:
- Export the following environment variable:
export JDK_JAVA_OPTIONS="--add-opens java.base/java.nio=ALL-UNNAMED"
- Set Snowflake property
JDBC_QUERY_RESULT_FORMAT
toJSON
as follows:snowflake:Options options = { properties: { "JDBC_QUERY_RESULT_FORMAT": "JSON" } };
Create a Snowflake client endpoint by giving authentication details in the Snowflake configuration.
snowflake:Client snowflakeClient = check new(accountIdentifier, user, password);
Step 4: Invoke the connector operation
Now, utilize the available connector operations.
Execute a DDL command
sql:ExecutionResult result = check snowflakeClient->execute(`CREATE TABLE COMPANY_DB.PUBLIC.EMPLOYEES ( ID INT NOT NULL AUTOINCREMENT, FirstName VARCHAR(255), LastName VARCHAR(255), BusinessUnit VARCHAR(255), PRIMARY KEY (ID) )`);
Execute a DML command
sql:ExecutionResult result = check snowflakeClient->execute(`INSERT INTO COMPANY_DB.PUBLIC.EMPLOYEES (FirstName, LastName, BusinessUnit) VALUES ('Shawn', 'Jerome', 'Integration')`);
Execute a query
type Employee record { int id; string firstName; string lastName; string businessUnit; }; ... stream<Employee, error?> resultStream = check snowflakeClient->query(`SELECT * FROM COMPANY_DB.PUBLIC.EMPLOYEES`);
Execute a query returning a single row
type Employee record { int id; string firstName; string lastName; string businessUnit; }; ... Employee|error result = check snowflakeClient->queryRow(`SELECT * FROM COMPANY_DB.PUBLIC.EMPLOYEES WHERE ID = 1`);
Execute batch DML commands
sql:ExecutionResult[] result = check snowflakeClient->batchExecute([ `INSERT INTO COMPANY_DB.PUBLIC.EMPLOYEES (FirstName, LastName, BusinessUnit) VALUES ('Shawn', 'Jerome', 'Integration')`, `INSERT INTO COMPANY_DB.PUBLIC.EMPLOYEES (FirstName, LastName, BusinessUnit) VALUES ('John', 'Doe', 'Integration')` ]);
Call a stored procedure
sql:ProcedureCallResult ret = check snowflakeClient->call(`{call PROCEDURES_DB.PUBLIC.SELECT_EMPLOYEE_DATA(1)}`, [Employee]); stream<record {}, sql:Error?>? qResult = ret.queryResult;
Examples
The following example shows how to use the Snowflake connector to create a table, insert data, and query data from the Snowflake database.
Employees Data Management Example - Manages employee data in a Snowflake database and exposes an HTTP service to interact with the database.