PHP PDO driver for Snowflake

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

Prerequisites

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

• On Windows: Visual Studio
• On Linux:
  • gcc 5.2 or higher
  • 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.1, 8.0, 7.4 or 7.3 (Note: support for PHP 7.2 is deprecated)
• the php-pdo extension
• the php-json extension

Note: Some of the examples in the instructions refer to the php-fpm extension. This extension is not required. The driver also works with regular PHP CGI.

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.

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:

   If you built PHP from the source code, run these commands from the directory containing the PHP source code.

   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.

To build the PHP driver for Windows:

1. Download and install the PHP SDK:

   1. Download PHP 8.0.16 binaries from https://windows.php.net/downloads/releases/php-8.0.16-Win32-vs16-x64.zip.
   2. Unzip the file to <path to PHP SDK>, such as C:\php-sdk.

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 (x64 or x86).
   • <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 (VS14 or VS15).
   • <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 8.0.16 C:\php-sdk
   

4. Download and install the PHP binaries, or build PHP yourself.

   If you want to build PHP yourself, 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 PHP that you want to install (e.g. 7.2.24).

   For example:

   .\scripts\run_setup_php.bat x64 Release VS16 8.0.16 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.0.16 C:\php-sdk
   

6. Copy php_pdo_snowflake.dll from the directory where you built the driver to the PHP extension directory (the same directory that contains the php_pdo.dll file). Usually, the PHP extension directory is the ext subdirectory in the directory where PHP is installed.

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

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 directory where you built the driver to the PHP extension directory (the same directory that contains the pdo.so file).

   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 directory containing the PHP configuration files (e.g. /etc/php/7.2/fpm/conf.d for PHP-FPM version 7.2 on Ubuntu).

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:

   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 to the PHP extension directory (the same directory that contains the php_pdo.dll file). Usually, the PHP extension directory is the ext subdirectory in the directory where PHP is installed.

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.

For accounts in regions outside of US-West, use region parameter to specify the region or append the region to the account parameter.

$dbh = new PDO("snowflake:account=testaccount.us-east-1", "user", "password");
$dbh = new PDO("snowflake:account=testaccount;region=us-east-1", "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");

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.

<$php
  $account = "<account_name>";
  $user = "<user_name>";
  $password = "<password>";

  $dbh = new PDO("snowflake:account=$account", $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";
$>

Running Tests For the PHP PDO Driver

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

Prepare for Test

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>"
    }
}

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

./scripts/env.sh && env | grep SNOWFLAKE_TEST > testenv.ini

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.

Run Tests

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 ...

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/7.2/fpm/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.