Ballerina Snowflake Connector

Package 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

1. Log in to your Snowflake account.

2. Go to the Warehouses tab under the Admin section, as shown below.

3. Click + Warehouse and select a name and type for a new warehouse, as shown below.

4. 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.

5. 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

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.

Issues and projects

The Issues and Projects tabs are disabled for this repository as this is part of the Ballerina library. To report bugs, request new features, start new discussions, view project boards, etc., visit the Ballerina library parent repository.

This repository only contains the source code for the package.

Building from the source

Setting up the prerequisites

1. Download and install Java SE Development Kit (JDK) version 17. You can install either OpenJDK or Oracle.

   Note: Set the JAVA_HOME environment variable to the path name of the directory into which you installed JDK.

2. Download and install Ballerina Swan Lake.

Building the source

Execute the commands below to build from the source.

• To build the library:

  ./gradlew clean build

• To run the integration tests:

  ./gradlew clean test

Contributing to Ballerina

As an open source project, Ballerina welcomes contributions from the community.

For more information, go to the contribution guidelines.

Code of conduct

All the contributors are encouraged to read the Ballerina Code of Conduct.

Useful links

• Discuss the code changes of the Ballerina project in ballerina-dev@googlegroups.com.
• Chat live with us via our Discord server.
• Post all technical questions on Stack Overflow with the #ballerina tag.