README

PHP 的 Snowflake API 连接器

要求

PHP ^8.0

安装

使用 Composer 安装

composer require spyck/snowflake

1. 生成自己的公钥和私钥

openssl genrsa -out rsa_key.pem 2048
openssl rsa -in rsa_key.pem -pubout -out rsa_key.pub

2. 在 Snowflake 中设置用户的公钥

设置将与 Snowflake API 连接的用户公钥。请确保将您的公钥（rsa_key.pub）放在一行中。

ALTER USER <username> SET rsa_public_key = '<publicKey>';

3. 从 Snowflake 获取公钥

运行描述用户命令以获取 RSA_PUBLIC_KEY_FP。

DESC USER <username>;

示例用法

<?php
 
$client = new Client();
# Account can be found in the URL: https://[account].snowflakecomputing.com/
$client->setAccount('<account>');
# Username
$client->setUser('<username>');
# Public key from step 3
$client->setPublicKey('<publicKey>');
# Private key from step 1. Must be the path of the file.
$client->setPrivateKey('rsa_key.pem');
# This command will generate the JWT token. First parameter is the number of seconds the token will expire.
$client->setToken();

$service = $client->getService();
# Warehouse you want to use. Not required when default Warehouse is set in Snowflake for this user.
$service->setWarehouse('<warehouse>');
# Database you want to use. Not required when default Database is set in Snowflake for this user. 
$service->setDatabase('<database>');
# Schema you want to use. Not required when default Schema is set in Snowflake for this user.
$service->setSchema('<schema>');
# Role you want to use. Not required when default Role is set in Snowflake for this user.
$service->setRole('<role>');

可选配置

# Set too false to return a SQL NULL value as the string "null", rather than as the value null. Default: true
$service->setNullable(false);

执行语句

$statementId = $service->postStatement($statement);

执行语句并将 $statementId 返回。

获取语句结果

$result = $service->getResult($statementId);

Result 对象将返回，其中 isExecuted() 被设置为 false 或 true。如果 isExecuted() 为 false，结果尚未准备好。例如，使用队列服务每 10 分钟尝试一次，直到语句执行并且 isExecuted 将为 true。当 isExecuted() 为 true 时，结果已准备好。如果返回的 Result 对象具有 isExecuted() 设置为 false，则所有字段均为 NULL，除了 getId()。来自 Result 对象的属性

# The "Query ID" from Snowflake.
$result->getId();
# Total number of results (NULL when `isExecuted` is false).
$result->getTotal();
# Current page (NULL when `isExecuted` is false).
$result->getPage();
# Total number of pages (NULL when `isExecuted` is false).
$result->getPageTotal();
# Get fields (NULL when `isExecuted` is false).
$result->getFields();
# Get the raw data (NULL when `isExecuted` is false).
$result->getDataRaw();
# Get the data converted to PHP variables (NULL when `isExecuted` is false).
#
# The fields are converted and type juggling because:
# Boolean is returned as string, "0" will be false and "1" will be true.
# Number is returned as string, will be converted to float or int
# Date is returned as integer (in a string) of the number of days since the Epoch. For example: 18262. Will be converted to DateTime object.
# Time is returned as float (in a string with 9 decimal places) of the number of seconds since the Epoch. For example: 82919.000000000. Will be converted to DateTime object.
# Time with Timezone is returned as float (in a string with 9 decimal places) of the number of seconds since the Epoch, followed by a space and the time zone offset in minutes. For example: 1616173619000000000 960. Will be converted to DateTime object.
$result->getData();
# Get `DateTime` object when statement is executed.
$result->getTimestamp();
# Check if the statement is executed.
$result->isExecuted();

由于结果可能很大，Snowflake 将数据分成页面（分区）。使用这些方法，您可以分页查看结果

# Get next page
$result->getPaginationNext();
# Get previous page
$result->getPaginationPrevious();
# Get first page
$result->getPaginationFirst();
# Get last page
$result->getPaginationLast();
# Get specific page
$result->getPagination(2);

取消语句

$service->cancelStatement($results->getId());

当出现错误时抛出 ResultException，当缺少必需的配置时抛出 ParameterException。

更多信息请参阅 https://docs.snowflake.com/en/developer-guide/sql-api/reference.html。