ballerinax/snowflake Ballerina library

1.2.1

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 and password which you use to login to Snowflake account and account_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:

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

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

    Copy
    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

    Copy
    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

    Copy
    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

    Copy
    public function main() error? {
        sql:ParameterizedCallQuery sqlQuery = `{CALL COMPANYDB.PUBLIC.getEmployeeInfo()}`;
        _ = check snowflakeClient->call(sqlQuery);
    }
  2. Use bal run command to compile and run the Ballerina program.

Import

import ballerinax/snowflake;Copy

Metadata

Released date: over 1 year ago

Version: 1.2.1

License: Apache-2.0


Compatibility

Platform: java11

Ballerina version: 2201.7.0

GraalVM compatible: Yes


Pull count

Total: 211

Current verison: 115


Weekly downloads


Source repository


Keywords

IT Operations/Cloud Services

Cost/Paid


Contributors

Other versions

See more...