PHP PDO driver for Snowflake

.. image:: https://codecov.io/gh/snowflakedb/pdo_snowflake/branch/master/graph/badge.svg :target: https://codecov.io/gh/snowflakedb/pdo_snowflake

.. image:: http://img.shields.io/:license-Apache%202-brightgreen.svg :target: http://www.apache.org/licenses/LICENSE-2.0.txt

Snowflake provides a driver that uses the PHP Data Objects (PDO) extension <https://www.php.net/manual/en/book.pdo.php>_. to connect to the Snowflake database.

.. contents::

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. 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.2, 8.1 or 8.0 (Note: support for PHP 7.4 or lower is deprecated)
the :code:php-pdo extension
the :code: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 <https://www.php.net/manual/en/install.php>_ 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

#. 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 <https://github.com/php/php-src/blob/master/README.md#building-php-source-code>_.

#. Set the :code:PHP_HOME environment variable to the path to the :code:bin directory containing the :code:phpize executable.

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

.. code-block:: bash

   export PHP_HOME=/usr

#. Clone the :code: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.

.. code-block:: bash

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

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

.. code-block:: bash

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

:code:pdo_snowflake should appear in the output from the command.

Building the Driver on Windows

.. |win-vs-version| replace:: VS16 .. |win-php-version| replace:: 8.1.18

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

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

To build the PHP driver for Windows:

#. Download and install PHP:

#. Download the PHP version binaries from `<https://windows.php.net/downloads/releases/>`_, 
   such as `<https://windows.php.net/downloads/releases/php-8.1.18-Win32-vs16-x64.zip>`_.

   .. note::

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

#. Unzip the file to the desired directory, such as :code:`C:\php`.

#. Clone the :code:pdo_snowflake repository:

.. code-block:: batch

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

#. Run the script to download the PHP SDK:

.. code-block:: batch

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

where:

:code:<arch> is your CPU architecture (:code:x64 or :code:x86).
:code:<build> is the type of binary that you want to build (:code:Release or :code:Debug).
:code:<visual studio version> is the version of Visual Studio that you are using (:code:VS14, :code:VS15, or :code:VS16).
:code:<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:

.. parsed-literal::

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

#. Download and build the PHP source code.

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

.. code-block:: batch

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

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

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

For example:

.. parsed-literal::

   .\\scripts\\run_setup_php.bat x64 Release |win-vs-version| |win-php-version| C:\\php-sdk

#. Run the script to build the driver:

.. code-block:: batch

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

For example:

.. parsed-literal::

   .\\scripts\\run_build_pdo_snowflake.bat x64 Release |win-vs-version| |win-php-version| C:\\php-sdk

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

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

.. code-block:: batch

   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

#. Copy :code:pdo_snowflake.so from the directory where you built the driver to the PHP extension directory (the same directory that contains the :code:pdo.so file).

To find the PHP extension directory, run:

.. code-block:: bash

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

#. Copy :code:cacert.pem from the :code:libsnowflakeclient subdirectory in the repository to the directory containing the PHP configuration files (e.g. :code:/etc/php/7.2/fpm/conf.d for PHP-FPM version 7.2 on Ubuntu).

#. In the same directory that contains the PHP configuration files, create a config file named :code:20-pdo_snowflake.ini that contains the following settings:

.. code-block:: 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 :code:<path to PHP config directory> is the path to the directory where you copied the :code:cacert.pem file in the previous step.

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

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

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

#. Add the following lines to your :code:php.ini file:

.. code-block:: ini

   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 :code:<path to PHP config directory> is the path to the directory where you copied the :code:cacert.pem file in the previous step.

#. 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 :code:PDO object, as explained in the PHP PDO documentation <https://www.php.net/manual/en/pdo.connections.php>_. Specify the data source name (:code:dsn) parameter as shown below:

.. code-block:: php

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

where:

:code:<account_name> is your Snowflake account name <https://docs.snowflake.com/en/user-guide/connecting.html#your-snowflake-account-name>_.
:code:<user> is the login name of the user for the connection.
:code:<password> is the password for the specified user.

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

where:

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

.. code-block:: php

$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 <https://docs.snowflake.com/en/user-guide/key-pair-auth.html#key-pair-authentication-key-pair-rotation>_.

To connect to the Snowflake database using key pair authentication, create a new :code:PDO object, as explained in the PHP PDO documentation <https://www.php.net/manual/en/pdo.connections.php>_. Specify the data source name (:code:dsn) parameter as shown below:

.. code-block:: php

$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:

:code:<account_name> Specifies your Snowflake account name <https://docs.snowflake.com/en/user-guide/connecting.html#your-snowflake-account-name>_.
:code:authenticator = SNOWFLAKE_JWT Specifies that you want to authenticate the Snowflake connection using key pair authentication with JSON Web Token (JWT).
:code:priv_key_file = <path>/rsa_key.p8 Specifies the local path to the private key file you created (i.e. :code:rsa_key.p8).
:code: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.
:code:<username> Specifies the login name of the user for the connection.
:code:"" 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 :code:insecure_mode=true in the DSN connection string. For example:

.. code-block:: php

$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

.. code-block:: bash

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.

.. __: https://curl.haxx.se/libcurl/c/libcurl-tutorial.html#Proxies

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:

.. code-block:: php

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

Syntax is the same as is documented for the Snowflake ODBC driver <https://docs.snowflake.com/en/user-guide/odbc-parameters.html#using-connection-parameters>_

Performing a Simple Query

The following example connects to the Snowflake database and performs a simple query. Before using this example, set the :code:$account, :code:$user, and :code:$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.

.. code-block:: php

<$php $account = "<account_name>"; $user = "<user_name>"; $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:

:code:logintimeout : The timeout for login requests. Defaults to 300 seconds
:code: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.
:code:maxhttpretries: The maximum number of retry attempts. Defaults to 7; cannot be decreased, only set higher than 7.

Example usage:

.. code-block:: php

$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

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

Prepare for Test

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

.. code-block:: none

{
    "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 :code:env.sh script to set the test connection parameters in the environment variables.

.. code-block:: bash

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

Run Tests

.. code-block:: bash

REPORT_EXIT_STATUS=1 NO_INTERACTION=true make test

Profile

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

.. code-block:: bash

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 :code:valgrind to check memeory leak. Both C API and PHP PDO can run along with :code:valgrind. For example, run :code:tests/selectnum.phpt testcase using :code:valgrind by the following command.

.. code-block:: bash

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

and verify no error in the output:

.. code-block:: bash

 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 :code:make test, the following error message shows up and no test runs.

.. code-block:: bash

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

Ensure the php has PDO:

.. code-block:: bash

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

If not installed, install the package.

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

.. code-block:: bash

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

.. code-block:: bash

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 :code:pdo_snowflake.loglevel can be :code:TRACE, :code:DEBUG, :code:INFO, :code:WARN, :code:ERROR and :code:FATAL.

pdo_snowflake
pdo_snowflake copied to clipboard

Metadata

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

Configuring OCSP Checking

Proxy

Performing a Simple Query

Setting timeouts

Running Tests For the PHP PDO Driver

Prepare for Test

Run Tests

Profile

Check memory leak by valgrind

Additional Notes

Test Framework

Troubleshooting

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

Where is the log files?

← Metadata

Owner

Metadata

pdo_snowflake pdo_snowflake copied to clipboard

Metadata

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

Configuring OCSP Checking

Proxy

Performing a Simple Query

Setting timeouts

Running Tests For the PHP PDO Driver

Prepare for Test

Run Tests

Profile

Check memory leak by valgrind

Additional Notes

Test Framework

Troubleshooting

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

Where is the log files?

← Metadata

Owner

Metadata

pdo_snowflake
pdo_snowflake copied to clipboard