PHP PDO driver for Snowflake

Snowflake provides a driver that uses the PHP Data Objects (PDO) extension. to connect to the Snowflake database.

Contents

• Prerequisites
• Building the PHP PDO Driver
  • Building the Driver on Linux and macOS
  • Building the Driver on Windows
• Installing the PHP PDO Driver
  • Installing the Driver on Linux and macOS
  • Installing the Driver on Windows
• Using the Driver
  • Connecting to the Snowflake Database
    • Using Key Pair Authentication
  • Configuring OCSP Checking
  • Proxy
  • Performing a Simple Query
  • Setting timeouts
• Running Tests For the PHP PDO Driver on Linux and macOS
  • Prepare Tests
  • Run Tests
  • Profile
  • Check memory leak by valgrind
• Running Tests For the PHP PDO Driver on Windows
  • Prepare Tests
  • Run Tests
• Additional Notes
  • Test Framework
• Troubleshooting
  • Cannot load module 'pdo_snowflake' because required module 'pdo' is not loaded
  • Where is the log files?

Prerequisites

To build the Snowflake PHP PDO Driver, the following software must be installed:

• On Windows: Visual Studio 2019
• On Linux:
  • gcc 5.2 or higher. Note: on certain OS (e.g. Centos 7) the preinstalled gcc/libstdc++ version is below the required minimum. For Centos 7, this is 4.8.5, which is below the requirement. Building and using the PHP PDO driver might be unsuccessful on such OS's until the prerequisite is fulfilled, i.e. libraries upgraded to at least the minimum version.
  • cmake 2.8 or higher
• On macOS:
  • clang
  • cmake 2.8 or higher

To install and use the Snowflake PHP PDO Driver, you must have the following software installed:

• PHP 8.3, 8.2 or 8.1 (Note: support for PHP 8.0 or lower is deprecated)
• the php-pdo extension
• the php-json extension

To build the driver, you must install the PHP development package for your operating system.

If you are using PHP with an application server or web server (e.g. Apache or nginx), configure the server to handle requests for PHP pages. See the PHP documentation for details.

Building the PHP PDO Driver

The following sections explain how to build the PHP PDO Driver on Linux, macOS, and Windows.

Note:	Snowflake PHP PDO driver does not yet support ARM/AARCH64 architecture on Linux.

While this feature is implemented, you can consider using the Snowflake ODBC driver https://developers.snowflake.com/odbc/ for PHP which supports multiple architectures.

Building the Driver on Linux and macOS

1. Download and install the PHP binaries, or build and install PHP from the source code.

   If you need to build PHP from the source code, see Building PHP source code.

2. Set the PHP_HOME environment variable to the path to the bin directory containing the phpize executable.

   For example, if the phpize executable is in /usr/bin, run the following command:

   export PHP_HOME=/usr

3. Clone the pdo_snowflake repository, and run the script to build the driver:

   git clone https://github.com/snowflakedb/pdo_snowflake.git
   cd pdo_snowflake
   ./scripts/build_pdo_snowflake.sh

4. Run the following command to verify that the driver can be loaded into memory successfully:

   $PHP_HOME/bin/php -dextension=modules/pdo_snowflake.so -m | grep pdo_snowflake

   pdo_snowflake should appear in the output from the command.

Building the Driver on Windows

Note: Snowflake supports only thread-safe versions of PHP.

You must install Microsoft Visual Studio 2019 (VS16) with the C++ development installer option.

To build the PHP driver for Windows:

1. Download and install PHP:

   1. Download the PHP version binaries from https://windows.php.net/downloads/releases/, such as https://windows.php.net/downloads/releases/php-8.1.28-Win32-vs16-x64.zip.

      Note

      The Snowflake PHP driver does not support x86 architecture or Windows NTS, so don't download packages that include nts or x86 in the package name.

   2. Unzip the file to the desired directory, such as C:\php.

2. Clone the pdo_snowflake repository:

   git clone https://github.com/snowflakedb/pdo_snowflake.git
   cd pdo_snowflake

3. Run the script to download the PHP SDK:

   .\scripts\setup_php_sdk.bat <arch> <build> <visual studio version> <path to PHP SDK>

   where:

   • <arch> is your CPU architecture (Currently, the driver only supports x64).
   • <build> is the type of binary that you want to build (Release or Debug).
   • <visual studio version> is the version of Visual Studio that you are using (Currently, the driver only supports VS16).
   • <path to PHP SDK> is the path to the directory where the PHP SDK should be downloaded. Do not create this directory. The script creates this directory for you when downloading the PHP SDK.

   For example:

   .\scripts\setup_php_sdk.bat x64 Release VS16 C:\php-sdk
   

4. Download and build the PHP source code.

   Run the script to download the PHP source and build PHP:

   .\scripts\run_setup_php.bat <arch> <build> <visual studio version> <full PHP version> <path to PHP SDK>

   For <arch>, <build>, <visual studio version>, and <path to PHP SDK>, specify the same values that you used in the previous step.

   For <full PHP version>, specify the full version number of the PHP binary you installed (e.g. 8.1.28).

   For example:

   .\scripts\run_setup_php.bat x64 Release VS16 8.1.28 C:\php-sdk
   

5. Run the script to build the driver:

   .\scripts\run_build_pdo_snowflake.bat <arch> <build> <visual studio version> <full PHP version> <path to PHP SDK>

   For example:

   .\scripts\run_build_pdo_snowflake.bat x64 Release VS16 8.1.28 C:\php-sdk
   

6. Copy php_pdo_snowflake.dll from the directory where you built the driver under the path to PHP SDK For example:

   C:\php-sdk\phpmaster\vs16\x64\php-src\x64\Release_TS
   

   to the PHP extension directory. Usually, the PHP extension directory is the ext subdirectory in the directory where PHP is installed. To find the PHP extension directory, run:

   C:\php\php.exe -i | findstr "extension_dir"

7. Run the following command to verify that the driver can be loaded into memory successfully:

   C:\php\php.exe -dextension=ext\php_pdo_snowflake.dll -m

   pdo_snowflake should appear in the output from the command.

Installing the PHP PDO Driver

The following sections explain how to install the PHP PDO Driver on Linux, macOS, and Windows.

Installing the Driver on Linux and macOS

1. Copy pdo_snowflake.so from the modules subdirectory in the repository to the PHP extension directory.

   To find the PHP extension directory, run:

   $PHP_HOME/bin/php -i | grep '^extension_dir'

2. Copy cacert.pem from the libsnowflakeclient subdirectory in the repository to the PHP configuration directory containing the PHP configuration files.

   To find the PHP configuration directory, run:

   $PHP_HOME/bin/php -ini

   In the output if the item of Scan for additional .ini files in is not (none), use that as the PHP configuration directory so we can have separated configuration file for Snowflake, otherwise use Configuration File (php.ini) Path:.

3. In the same directory that contains the PHP configuration files, create a config file named 20-pdo_snowflake.ini that contains the following settings (or in case using Configuration File (php.ini) Path:, add following lines to php.ini):

   extension=pdo_snowflake.so
   pdo_snowflake.cacert=<path to PHP config directory>/cacert.pem
   # pdo_snowflake.logdir=/tmp     # location of log directory
   # pdo_snowflake.loglevel=DEBUG  # log level

   where <path to PHP config directory> is the path to the directory where you copied the cacert.pem file in the previous step.

4. If you are using PHP with an application server or web server (e.g. Apache or nginx), restart the server.

Installing the Driver on Windows

1. Copy php_pdo_snowflake.dll from the directory where you built the driver under the path to PHP SDK For example:

   C:\php-sdk\phpmaster\vs16\x64\php-src\x64\Release_TS
   

   to the PHP extension directory. Usually, the PHP extension directory is the ext subdirectory in the directory where PHP is installed. To find the PHP extension directory, run:

   C:\php\php.exe -i | findstr "extension_dir"

2. Copy cacert.pem from the libsnowflakeclient subdirectory in the repository to the directory containing the PHP configuration files (e.g. C:\php if PHP is installed in that directory).

3. Add the following lines to your php.ini file:

   extension=php_pdo_snowflake.dll
   pdo_snowflake.cacert=<path to PHP config directory>\cacert.pem
   ; pdo_snowflake.logdir=C:\path\to\logdir     ; location of log directory
   ; pdo_snowflake.loglevel=DEBUG  ; log level

   where <path to PHP config directory> is the path to the directory where you copied the cacert.pem file in the previous step.

4. If you are using PHP with an application server or web server (e.g. Apache or nginx), restart the server.

Using the Driver

The next sections explain how to use the driver in a PHP page.

Connecting to the Snowflake Database

To connect to the Snowflake database, create a new PDO object, as explained in the PHP PDO documentation. Specify the data source name (dsn) parameter as shown below:

$dbh = new PDO("snowflake:account=<account_name>", "<user>", "<password>");

where:

• <account_name> is your Snowflake account name.
• <user> is the login name of the user for the connection.
• <password> is the password for the specified user.

Dependes on the region where your account being hosted, you might need to use region parameter to specify the region or append the region to the account parameter. You might also need to append cloud in region parameter in the format of <region>.<cloud>, or do the same when you append it to the account parameter.

where:

• <region> is the identifier for the cloud region.
• <cloud> is the identifier for the cloud platform (aws, azure, or gcp).

$dbh = new PDO("snowflake:account=testaccount.us-east-2.aws", "user", "password");
$dbh = new PDO("snowflake:account=testaccount;region=us-east-2.aws", "user", "password");

Using Key Pair Authentication

The PHP PDO driver supports key pair authentication and key rotation.

You must first complete the initial configuration for key pair authentication as shown in Key Pair Authentication & Key Pair Rotation.

To connect to the Snowflake database using key pair authentication, create a new PDO object, as explained in the PHP PDO documentation. Specify the data source name (dsn) parameter as shown below:

$dbh = new PDO("account=<account name>;authenticator=SNOWFLAKE_JWT;priv_key_file=<path>/rsa_key.p8;priv_key_file_pwd=<private_key_passphrase>",
                "<username>", "");

where:

• <account_name> Specifies your Snowflake account name.
• authenticator = SNOWFLAKE_JWT Specifies that you want to authenticate the Snowflake connection using key pair authentication with JSON Web Token (JWT).
• priv_key_file = <path>/rsa_key.p8 Specifies the local path to the private key file you created (i.e. rsa_key.p8).
• priv_key_file_pwd = <private_key_passphrase> Specifies the passphrase to decrypt the private key file. If you using an unecrypted private key file, omit this parameter.
• <username> Specifies the login name of the user for the connection.
• "" Specifies the password for the specified user. The parameter is required. When using key-pair authentication, specify an empty string.

Configuring OCSP Checking

By default, OCSP (Online Certificate Status Protocol) checking is enabled and is set per PDO connection.

To disable OCSP checking for a PDO connection, set insecure_mode=true in the DSN connection string. For example:

$dbh = new PDO("snowflake:account=testaccount;insecure_mode=true", "user", "password");

Proxy

PHP PDO Driver for Snowflake supports HTTP and HTTPS proxy connections using environment variables. To use a proxy server configure the following environment variables:

• http_proxy
• https_proxy
• no_proxy

export http_proxy="[protocol://][user:password@]machine[:port]"
export https_proxy="[protocol://][user:password@]machine[:port]"

More info can be found on the libcurl tutorial page.

Since version 1.2.5 of the driver, you can set individual proxy settings which are only valid for the PDO Snowflake driver. Use the:

• proxy
• no_proxy

directives on the connection string. Example:

$dbh = new PDO("snowflake:account=<account_name>;proxy=my.pro.xy;no_proxy=.mycompany.com", "<username>", "<password>");

Syntax is the same as is documented for the Snowflake ODBC driver

Performing a Simple Query

The following example connects to the Snowflake database and performs a simple query. Before using this example, set the $account, $user, and $password variables to your account, login name, and password. The warehouse, database, schema parameters are optional, but can be specified to determine the context of the connection in which the query will be run. In this example, we'll use those too.

<$php
  $account = "<account_name>";
  $user = "<user_name>";
  $password = "<password>";
  $warehouse = "<warehouse_name>";
  $database = "<database_name>";
  $schema = "<schema_name>";

  $dbh = new PDO("snowflake:account=$account;warehouse=$warehouse;database=$database;schema=$schema", $user, $password);
  $dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
  echo "Connected\n";

  $sth = $dbh->query("select 1234");
  while ($row=$sth->fetch(PDO::FETCH_NUM)) {
      echo "RESULT: " . $row[0] . "\n";
  }
  $dbh = null;
  echo "OK\n";
$>

Note: PUT and GET queries are not supported in the driver.

Setting timeouts

The following parameters are exposed in the PHP PDO Driver to affect the behaviour regarding various timeouts:

• logintimeout : The timeout for login requests. Defaults to 300 seconds
• retrytimeout: The timeout for query requests, large query result chunk download actions, and request retries, . Defaults to 300 seconds; cannot be decreased, only set higher than 300.
• maxhttpretries: The maximum number of retry attempts. Defaults to 7; cannot be decreased, only set higher than 7.

Example usage:

$dbh = new PDO("$dsn;application=phptest;authenticator=snowflake;priv_key_file=tests/p8test.pem;priv_key_file_pwd=password;disablequerycontext=true;includeretryreason=false;logintimeout=250;maxhttpretries=8;retrytimeout=350", $user, $password);

Running Tests For the PHP PDO Driver on Linux and macOS

In order to run the test scripts, you must have jq installed.

Prepare Tests

1. Create a parameter file parameters.json under pdo_snowflake directory:

   {
       "testconnection": {
           "SNOWFLAKE_TEST_USER":      "<your_user>",
           "SNOWFLAKE_TEST_PASSWORD":  "<your_password>",
           "SNOWFLAKE_TEST_ACCOUNT":   "<your_account>",
           "SNOWFLAKE_TEST_WAREHOUSE": "<your_warehouse>",
           "SNOWFLAKE_TEST_DATABASE":  "<your_database>",
           "SNOWFLAKE_TEST_SCHEMA":    "<your_schema>",
           "SNOWFLAKE_TEST_ROLE":      "<your_role>"
       }
   }
   

2. Set the workfolder to pdo_snowflake repository. e.g. Call cd pdo_snowflake.

3. Call env.sh script to set the test connection parameters in the environment variables.

   /bin/bash -c "source ./scripts/env.sh && env | grep SNOWFLAKE_TEST > testenv.ini"

Run Tests

To run the tests, do the following:

REPORT_EXIT_STATUS=1 NO_INTERACTION=true make test

Profile

You can use callgrind to profile PHP PDO programs. For example, run tests/selectnum.phpt testcase using valgrind along with callgrind option.

valgrind --tool=callgrind $PHP_HOME/bin/php -dextension=modules/pdo_snowflake.so tests/selectnum.phpt
callgrind_annotate callgrind.out.*

Check memory leak by valgrind

Use valgrind to check memeory leak. Both C API and PHP PDO can run along with valgrind. For example, run tests/selectnum.phpt testcase using valgrind by the following command.

valgrind --leak-check=full $PHP_HOME/bin/php -dextension=modules/pdo_snowflake.so tests/selectnum.phpt

and verify no error in the output:

ERROR SUMMARY: 0 errors from 0 contexts ...

Running Tests For the PHP PDO Driver on Windows

In order to run the test scripts, you must have jq installed.

Prepare Tests

1. Create a parameter file parameters.json under pdo_snowflake directory:

   {
       "testconnection": {
           "SNOWFLAKE_TEST_USER":      "<your_user>",
           "SNOWFLAKE_TEST_PASSWORD":  "<your_password>",
           "SNOWFLAKE_TEST_ACCOUNT":   "<your_account>",
           "SNOWFLAKE_TEST_WAREHOUSE": "<your_warehouse>",
           "SNOWFLAKE_TEST_DATABASE":  "<your_database>",
           "SNOWFLAKE_TEST_SCHEMA":    "<your_schema>",
           "SNOWFLAKE_TEST_ROLE":      "<your_role>"
       }
   }
   

2. Set the workfolder to pdo_snowflake repository. e.g. Call cd pdo_snowflake.

3. Set the PHP_HOME environment variable to the php install directory, such as C:\php.

4. Install the driver following the instructions above.

5. Call env.bat script to set the test connection parameters.

   .\scripts\env.bat

Run Tests

To run the tests, do the following:

%PHP_HOME%\php.exe <path to PHP SDK>\phpmaster\<visual studio version>\<arch>\php-src\run-tests.php .\tests

where:

• <arch> is your CPU architecture (Currently x64 is the only supported one).
• <visual studio version> is the version of Visual Studio that you are using (Currently VS16 is the only supported one).
• <path to PHP SDK> is the path to the directory where the PHP SDK should be downloaded.

Additional Notes

Test Framework

The PHP PDO Snowflake driver uses phpt test framework. Refer the following documents to write tests.

• https://qa.php.net/write-test.php
• https://qa.php.net/phpt_details.php

Troubleshooting

Cannot load module 'pdo_snowflake' because required module 'pdo' is not loaded

In some environments, e.g., Ubuntu 16, when you run make test, the following error message shows up and no test runs.

PHP Warning:  Cannot load module 'pdo_snowflake' because required module 'pdo' is not loaded in Unknown on line 0

Ensure the php has PDO:

$ php -i | grep -i "pdo support"
PDO support => enabled

If not installed, install the package.

Locate pdo.so under /usr/lib and specify it in phpt files, e.g.,

--INI--
extension=/usr/lib/php/20170718/pdo.so
pdo_snowflake.cacert=libsnowflakeclient/cacert.pem
pdo_snowflake.logdir=/tmp
pdo_snowflake.loglevel=DEBUG

Where is the log files?

The location of log files are specified by the parameters in php.ini:

extension=pdo_snowflake.so
pdo_snowflake.cacert=/etc/php/8.1/conf.d/cacert.pem
pdo_snowflake.logdir=/tmp     ; location of log directory
pdo_snowflake.loglevel=DEBUG  ; log level

where pdo_snowflake.loglevel can be TRACE, DEBUG, INFO, WARN, ERROR and FATAL.