Actions
The Snowflake connector spans 2 packages:
ballerinax/snowflakeballerinax/snowflake.driver
Available clients:
| Client | Purpose |
|---|---|
Client | Executes SQL queries, DML/DDL statements, batch operations, and stored procedures using basic authentication. |
Advanced Client | Executes SQL operations with support for both basic and key-pair authentication. |
Client
Executes SQL queries, DML/DDL statements, batch operations, and stored procedures using basic authentication.
Configuration
| Field | Type | Default | Description |
|---|---|---|---|
accountIdentifier | string | Required | Snowflake account identifier (e.g., "myorg-myaccount"). |
user | string | Required | Snowflake username. |
password | string | Required | Snowflake password. |
options | Options? | () | Additional JDBC datasource options. |
connectionPool | sql:ConnectionPool? | () | Connection pool configuration. |
Initializing the client
import ballerinax/snowflake;
import ballerinax/snowflake.driver as _;
configurable string accountIdentifier = ?;
configurable string user = ?;
configurable string password = ?;
snowflake:Client snowflakeClient = check new (accountIdentifier, user, password, options = {
properties: {"JDBC_QUERY_RESULT_FORMAT": "JSON"}
});
Operations
Query operations
query
Executes a SQL query and returns the results as a stream of records.
Parameters:
| Name | Type | Required | Description |
|---|---|---|---|
sqlQuery | sql:ParameterizedQuery | Yes | The SQL query to execute. |
rowType | typedesc<record {}> | No | Expected return record type for each row. |
Returns: stream<rowType, sql:Error?>
Sample code:
type Employee record {
int employee_id;
string first_name;
string last_name;
string email;
string phone;
string job_title;
};
stream<Employee, sql:Error?> employees = snowflakeClient->query(
`SELECT * FROM EMPLOYEES`
);
Sample response:
[
{"employee_id": 1, "first_name": "John", "last_name": "Smith", "email": "[email protected]", "phone": "555-1234", "job_title": "Software Engineer"},
{"employee_id": 2, "first_name": "Jane", "last_name": "Doe", "email": "[email protected]", "phone": "555-5678", "job_title": "Data Analyst"}
]
queryRow
Executes a SQL query that returns at most one row.
Parameters:
| Name | Type | Required | Description |
|---|---|---|---|
sqlQuery | sql:ParameterizedQuery | Yes | The SQL query to execute. |
returnType | typedesc<anydata> | No | Expected return type. |
Returns: returnType|sql:Error
Sample code:
type Employee record {
int employee_id;
string first_name;
string last_name;
string email;
string phone;
string job_title;
};
int id = 1;
Employee employee = check snowflakeClient->queryRow(
`SELECT * FROM EMPLOYEES WHERE employee_id = ${id}`
);
Sample response:
{"employee_id": 1, "first_name": "John", "last_name": "Smith", "email": "[email protected]", "phone": "555-1234", "job_title": "Software Engineer"}
DML & DDL operations
execute
Executes a DDL or DML SQL statement and returns execution metadata.
Parameters:
| Name | Type | Required | Description |
|---|---|---|---|
sqlQuery | sql:ParameterizedQuery | Yes | The SQL statement to execute. |
Returns: sql:ExecutionResult|sql:Error
Sample code:
sql:ExecutionResult result = check snowflakeClient->execute(
`INSERT INTO EMPLOYEES (employee_id, first_name, last_name, email, phone, job_title)
VALUES (6, 'Alice', 'Johnson', '[email protected]', '555-9999', 'Product Manager')`
);
Sample response:
{"affectedRowCount": 1, "lastInsertId": null}
batchExecute
Executes multiple parameterized SQL statements in a single batch.
Parameters:
| Name | Type | Required | Description |
|---|---|---|---|
sqlQueries | sql:ParameterizedQuery[] | Yes | Array of parameterized SQL statements. |
Returns: sql:ExecutionResult[]|sql:Error
Sample code:
sql:ParameterizedQuery[] insertQueries = [
`INSERT INTO EMPLOYEES (employee_id, first_name, last_name, email, phone, job_title)
VALUES (7, 'Bob', 'Wilson', '[email protected]', '555-1111', 'Designer')`,
`INSERT INTO EMPLOYEES (employee_id, first_name, last_name, email, phone, job_title)
VALUES (8, 'Carol', 'Brown', '[email protected]', '555-2222', 'QA Engineer')`
];
sql:ExecutionResult[] results = check snowflakeClient->batchExecute(insertQueries);
Sample response:
[
{"affectedRowCount": 1, "lastInsertId": null},
{"affectedRowCount": 1, "lastInsertId": null}
]
Stored procedures
call
Invokes a stored procedure, optionally returning result sets.
Parameters:
| Name | Type | Required | Description |
|---|---|---|---|
sqlQuery | sql:ParameterizedCallQuery | Yes | The stored procedure call query. |
rowTypes | typedesc<record {}>[] | No | Expected record types for result sets. |
Returns: sql:ProcedureCallResult|sql:Error
Sample code:
sql:ProcedureCallResult result = check snowflakeClient->call(
`CALL GET_EMPLOYEE_COUNT()`
);
Sample response:
{"executionResult": {"affectedRowCount": -1, "lastInsertId": null}, "queryResult": null}
Connection management
close
Closes the client connection and releases the connection pool resources.
Returns: sql:Error?
Sample code:
check snowflakeClient.close();
Advanced client
Executes SQL operations with support for both basic and key-pair authentication.
Configuration
| Field | Type | Default | Description |
|---|---|---|---|
accountIdentifier | string | Required | Snowflake account identifier (e.g., "myorg-myaccount"). |
authConfig | AuthConfig | Required | Authentication configuration: either BasicAuth (user/password) or KeyBasedAuth (user/private key). |
options | Options? | () | Additional JDBC datasource options. |
connectionPool | sql:ConnectionPool? | () | Connection pool configuration. |
Initializing the client
import ballerinax/snowflake;
import ballerinax/snowflake.driver as _;
configurable string accountIdentifier = ?;
configurable string user = ?;
configurable string privateKeyPath = ?;
configurable string privateKeyPassphrase = ?;
snowflake:AdvancedClient snowflakeClient = check new (accountIdentifier, {
user: user,
privateKeyPath: privateKeyPath,
privateKeyPassphrase: privateKeyPassphrase
}, options = {
properties: {"JDBC_QUERY_RESULT_FORMAT": "JSON"}
});
Operations
Query operations
query
Executes a SQL query and returns the results as a stream of records.
Parameters:
| Name | Type | Required | Description |
|---|---|---|---|
sqlQuery | sql:ParameterizedQuery | Yes | The SQL query to execute. |
rowType | typedesc<record {}> | No | Expected return record type for each row. |
Returns: stream<rowType, sql:Error?>
Sample code:
type Employee record {
int employee_id;
string first_name;
string last_name;
string email;
};
stream<Employee, sql:Error?> employees = snowflakeClient->query(
`SELECT employee_id, first_name, last_name, email FROM EMPLOYEES WHERE job_title = ${"Data Analyst"}`
);
Sample response:
[
{"employee_id": 2, "first_name": "Jane", "last_name": "Doe", "email": "[email protected]"}
]
queryRow
Executes a SQL query that returns at most one row.
Parameters:
| Name | Type | Required | Description |
|---|---|---|---|
sqlQuery | sql:ParameterizedQuery | Yes | The SQL query to execute. |
returnType | typedesc<anydata> | No | Expected return type. |
Returns: returnType|sql:Error
Sample code:
int count = check snowflakeClient->queryRow(
`SELECT COUNT(*) FROM EMPLOYEES`
);
Sample response:
5
DML & DDL operations
execute
Executes a DDL or DML SQL statement and returns execution metadata.
Parameters:
| Name | Type | Required | Description |
|---|---|---|---|
sqlQuery | sql:ParameterizedQuery | Yes | The SQL statement to execute. |
Returns: sql:ExecutionResult|sql:Error
Sample code:
int id = 3;
string newTitle = "Senior Engineer";
sql:ExecutionResult result = check snowflakeClient->execute(
`UPDATE EMPLOYEES SET job_title = ${newTitle} WHERE employee_id = ${id}`
);
Sample response:
{"affectedRowCount": 1, "lastInsertId": null}
batchExecute
Executes multiple parameterized SQL statements in a single batch.
Parameters:
| Name | Type | Required | Description |
|---|---|---|---|
sqlQueries | sql:ParameterizedQuery[] | Yes | Array of parameterized SQL statements. |
Returns: sql:ExecutionResult[]|sql:Error
Sample code:
int[] idsToDelete = [7, 8];
sql:ParameterizedQuery[] deleteQueries = from int id in idsToDelete
select `DELETE FROM EMPLOYEES WHERE employee_id = ${id}`;
sql:ExecutionResult[] results = check snowflakeClient->batchExecute(deleteQueries);
Sample response:
[
{"affectedRowCount": 1, "lastInsertId": null},
{"affectedRowCount": 1, "lastInsertId": null}
]
Stored procedures
call
Invokes a stored procedure, optionally returning result sets.
Parameters:
| Name | Type | Required | Description |
|---|---|---|---|
sqlQuery | sql:ParameterizedCallQuery | Yes | The stored procedure call query. |
rowTypes | typedesc<record {}>[] | No | Expected record types for result sets. |
Returns: sql:ProcedureCallResult|sql:Error
Sample code:
sql:ProcedureCallResult result = check snowflakeClient->call(
`CALL MY_PROCEDURE(${param1}, ${param2})`
);
Sample response:
{"executionResult": {"affectedRowCount": -1, "lastInsertId": null}, "queryResult": null}
Connection management
close
Closes the client connection and releases the connection pool resources.
Returns: sql:Error?
Sample code:
check snowflakeClient.close();