search

snowflakedb / pdo_snowflake

PHP PDO driver for snowflake
Apache License 2.0
62 stars 29 forks source link
database php snowflake

readme

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:

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

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:

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

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:

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

. 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

Choose a target directory where none of the subdirectories contain any spaces or special characters on the path. E.g. :code:C:\temp\pdo_snowflake. Without this, one of the setup scripts (phpsdk-starter.bat) will fail during step 4.

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

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

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 under the path to PHP SDK

For example:

.. parsed-literal::

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

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

.. code-block:: bash

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

. 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

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

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

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 PHP configuration directory

containing the PHP configuration files.

To find the PHP configuration directory, run:

.. code-block:: bash

   $PHP_HOME/bin/php -ini

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

. 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 (or in case using :code:Configuration File (php.ini) Path:, add following lines to :code:php.ini):

.. 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 under the path to PHP SDK

For example:

.. parsed-literal::

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

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

.. code-block:: bash

   C:\php\php.exe -i | findstr "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: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:

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-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");

You can specify the host name for your account directly as shown below instead of using account and region:

.. code-block:: php

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

where:

where:

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:

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:

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

directives on the connection string. Example:

.. code-block:: php

$dbh = new PDO("snowflake:account=;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 = ""; $user = ""; $password = ""; $warehouse = ""; $database = ""; $schema = "";

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

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 on Linux and macOS

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

Prepare Tests

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

. Set the workfolder to :code:pdo_snowflake repository. e.g. Call :code:cd pdo_snowflake.

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

.. code-block:: bash

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

Run Tests

To run the tests, do the following:

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

Running Tests For the PHP PDO Driver on Windows

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

Prepare Tests

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

. Set the workfolder to :code:pdo_snowflake repository. e.g. Call :code:cd pdo_snowflake.

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

. Install the driver following the instructions above.

. Call :code:env.bat script to set the test connection parameters.

.. code-block:: batch

   .\scripts\env.bat

Run Tests

To run the tests, do the following:

.. code-block:: bash

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

where:

Additional Notes

Test Framework

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

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