Download the PHP package neighborhoods/snowflake-sql-api-component without Composer

Snowflake SQL API Component

A Client Component for Snowflake SQL API.

Install

Via Composer

Usage

This package offers two ways for interacting with the Snowflake SQL API.

• Single Statement Client V1, which is a higher level client implementation. It's much simpler to use, but does not provide access to all the features offered by the API.
• Client V1, which is low level client implementation providing access to all features.

Both implementations use the same Authentication, which is an essential part of Client V1.

Single Statement Client V1

The Snowflake SQL API has many useful features which includes.

• Submitting multiple statments at once, possibly in the same transaction.
• The statements might take a long time to execute. If the execution threshold is exceeded the API indicates that the statements are still ongoing. The Client application can check the status later on.
• The results are patritioned/paginated.

While these features are important, in practice the following is encountered:

• There is often just one statement being executed without using transactions.
• The statement is typically lightweight due to which the first page of results is returned right away.

When the above is true, the Single Statement Client V1 can be used. Single Statement Client V1 uses Client V1 under the hood, taking care of building the appropriate API request(s) and error handling.

Execute a statement

The execute() method returns all the rows from all the pages. Each row is an associative array. The keys in the row are the column names, while the values are the cell value.

The code above results in the output below. There is only one row returned.

Notably all the column names are uppercased, because in Snowflake unquoted column names default to uppercase and are treated as case-insensitive.

Binding variables

It is possible to pass an array of binding variables as second argument to the execute() method.

The code above results in the output below.

Execute Paginated

For common select queries the volume of the results isn't that big. The execute() method combines the data from all the pages, if multiple.

If the statement is expected to produce many pages of data raising concerns on memory consumptions the executePaginated() method can be used instead.

The code above results in the output below.

Queries taking too long

If the execution of the statement takes too long, the results won't be returned right away, in which case the statement is canceled and an OngoingException is raised.

DI Paths

To build a DI container providing a SingleStatementClientV1\ClientInterface, the following paths are needed

Passing JwtTokenGenerator

The SingleStatementClientV1\ClientV1Interace DI service is only missing a configured ClientV1\JwtTokenGenerator for authentication.

Client V1

The Neighborhoods\SnowflakeSqlApiComponent\ClientV1 namespace contains a low level client implementation. While it allows using all the features of the Snowflake SQL API, using it is challenging. Often the simpler Single Statement Client V1 can be used instead. More information about the Client V1 is available here.

Authentication

At the moment only key-pair authentication is supported.

Key-pair authentication is handled by the ClientV1\JwtTokenGenerator. Configure the JwtTokenGenerator by setting the Account, User and Private Key as shown below.

This can also be done by defining a Symfony DI service using environment variables.

Test script

The vendor/bin/single_statement_client_v1_connection_test script can be run to build a container providing a SingleStatementClientV1\ClientInterface instance. The test script uses DICBC, which isn't a direct dependency of this package. You might need to add it to your development environment.

The SELECT 'Hello World' query is executed to test the Snowflake connection. If the credentials are valid, the result will be printed. Run the script for more details.

Examples

Application examples are available in the Fitness project.