Snowflake provides a driver that uses the PHP Data Objects (PDO) extension. to connect to the Snowflake database.
Contents
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.
The following sections explain how to build the PHP PDO Driver on Linux, macOS, and Windows.
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.
Set the
PHP_HOME
environment variable to the path to thebin
directory containing thephpize
executable.For example, if the
phpize
executable is in/usr/bin
, run the following command:export PHP_HOME=/usr
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
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.
Note: Snowflake supports only thread-safe versions of PHP.
To build the PHP driver for Windows:
Download and install the PHP SDK:
- Download PHP 8.0.16 binaries from https://windows.php.net/downloads/releases/php-8.0.16-Win32-vs16-x64.zip.
- Unzip the file to <path to PHP SDK>, such as
C:\php-sdk
.
Clone the
pdo_snowflake
repository:git clone https://github.com/snowflakedb/pdo_snowflake.git cd pdo_snowflake
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
orx86
).<build>
is the type of binary that you want to build (Release
orDebug
).<visual studio version>
is the version of Visual Studio that you are using (VS14
orVS15
).<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
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
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
Copy
php_pdo_snowflake.dll
from the directory where you built the driver to the PHP extension directory (the same directory that contains thephp_pdo.dll file
). Usually, the PHP extension directory is theext
subdirectory in the directory where PHP is installed.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
The following sections explain how to install the PHP PDO Driver on Linux, macOS, and Windows.
Copy
pdo_snowflake.so
from the directory where you built the driver to the PHP extension directory (the same directory that contains thepdo.so
file).To find the PHP extension directory, run:
$PHP_HOME/bin/php -i | grep '^extension_dir'
Copy
cacert.pem
from thelibsnowflakeclient
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).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 thecacert.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.
Copy
php_pdo_snowflake.dll
from the directory where you built the driver to the PHP extension directory (the same directory that contains thephp_pdo.dll
file). Usually, the PHP extension directory is theext
subdirectory in the directory where PHP is installed.Copy
cacert.pem
from thelibsnowflakeclient
subdirectory in the repository to the directory containing the PHP configuration files (e.g.C:\php
if PHP is installed in that directory).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 thecacert.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.
The next sections explain how to use the driver in a PHP page.
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");
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");
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";
$>
In order to run the test scripts, you must have jq installed.
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
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.
REPORT_EXIT_STATUS=1 NO_INTERACTION=true make test
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.*
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 ...
The PHP PDO Snowflake driver uses phpt test framework. Refer the following documents to write tests.
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
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
.