Snowflake

Snowflake provides an API client service for interaction with the Snowflake SQL API.

Note: the SQL API is different from the PHP PDO Driver for Snowflake and this module does not currently support the PHP PDO driver.

Requirements

This module requires the following modules:

• Key - For storing API access credentials.

At least one additional library is required depending on the authentication method the module will use.

• For Key Pair authentication install firebase/php-jwt: composer require firebase/php-jwt

Configuration

Configure authentication settings by navigating to Configuration > Snowflake > Snowflake Authentication (/admin/config/snowflake/auth). Currently, the only supported authentication method is Key Pair Authentication.

Configure default settings and parameters for SQL statements by navigating to Configuration > Snowflake > Snowflake Settings (/admin/config/snowflake/settings).

Usage

The Snowflake module provides a single service -- the Snowflake SQL API client.

/** @var \Drupal\snowflake\SqlApi $client */
$client = Drupal::service('snowflake.sql_api');

Create statements with the Drupal\snowflake\Statement\Statement class.

$statement1 = Drupal\snowflake\Statement\Statement::create('SELECT FIRST_NAME, LAST_NAME FROM CONTACTS LIMIT 10');
$statement2 = Drupal\snowflake\Statement\Statement::create('SELECT FIRST_NAME, LAST_NAME FROM CONTACTS WHERE USER_ID=?')->addBinding('TEXT', '1234567890');
$statement3 = Drupal\snowflake\Statement\Statement::create('CALL my_stored_procedure()');

Execute a single statement or combine multiple statements with the
Drupal\snowflake\Statement\Statements class.

/** @var \Drupal\snowflake\SqlApi $client */
$client = Drupal::service('snowflake.sql_api');

try {
  // Execute a single statement.
  $statements = Drupal\snowflake\Statement\Statements::create($statement1);
  $result = $client->executeStatements($statements);
  if ($result instanceof \Drupal\snowflake\StatementResult\ResultSet) {
    $data = $result->getData();
    $metadata = $result->getMetadata();
  }

  // Combine multiple statements, and run asynchronously.
  $statements = new Drupal\snowflake\Statement\Statements();
  $statements->addStatement($statement1)
    ->addStatement($statement2)
    ->addStatement($statement3)
    ->setAsync(TRUE);
  $result = $client->executeStatements($statements);
  $status = $client->getStatementStatus($result->getStatementHandle());
  foreach ($status->getStatementHandles() as $handle) {
    $statement_status = $client->getStatementStatus($handle);
    $data = $statement_status->getData();
  }
}
catch (\GuzzleHttp\Exception\BadResponseException $e) {
  watchdog_exception('my_module', $e);
}