PHP PDO driver for Snowflake
Private Preview. Linux Only. No PHP 5 support. PHP 7+ only.
Configuring Environment
PHP Versions and Extensions
PHP 7.0+ is supported. The following extensions are required:
pdo
json
Application Server (Optional)
If the PHP is used along with an application server, e.g., nginx, apache, which is the common use case, install them and configure the PHP handler accordingly so that the PHP file can run on the web.
For example, Nginx enables the PHP handler by adding the following config in /etc/nginx/sites-available/default:
server {
... the standard settings ...
# PHP handler
location ~ \.php$ {
fastcgi_split_path_info ^(.+\.php)(/.+)$;
fastcgi_pass unix:/var/run/php/php7.1-fpm.sock;
fastcgi_index index.php;
include fastcgi_params;
}
}
where PHP 7.1 and the corresponding PHP-FPM (https://php-fpm.org/) package are used, for example.
Restart Nginx and PHP-FPM services.
service nginx restart
service php7.1-fpm restart
Add a PHP test code test.php to the home location of an application server.
Ensure it can run and the output includes both pdo and json extensions.
curl http://localhost/test.php | grep -E "(pdo|json)"
Installing PDO driver for Snowflake
Two files require to copy.
pdo_snowflake.so
cacert.pem
Copy pdo_snowflake.so to the same location as pdo.so where all PHP extentions reside.
Copy cacert.pem to the PHP config directory. For example, PHP-FPM version 7.1 on Ubuntu12 has /etc/php/7.1/fpm/conf.d/ for the extensions.
Note
If you don't have pdo_snowflake.so, build it following the instruction below.
cp cacert.pem /etc/php/7.1/fpm/conf.d/
Add a config file /etc/php/7.1/fpm/conf.d/20-pdo_snowflake.ini including the following contents to the PHP config directory.
extension=pdo_snowflake.so
pdo_snowflake.cacert=/etc/php/7.1/fpm/conf.d/cacert.pem
# pdo_snowflake.logdir=/tmp # location of log directory
# pdo_snowflake.loglevel=DEBUG # log level
Restart Nginx and PHP-FPM services. For example:
service nginx restart
service php7.1-fpm restart
Ensure phpinfo() function return the output including pdo_snowflake.
curl http://localhost/test.php | grep -E "(pdo|json|snowflake)"
Note
We have not finalized what package would be the best for binary distribution. So far I'm trying to get pecl account but have not got one yet. Any suggestion is welcome.
Usage
Connection String
Create a database handle with connection parameters:
$dbh = new PDO("snowflake:account=testaccount", "user", "password");
For non-US-West region, specify region parameter or append it to 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");
Query
Here is an example of fetch a row:
$account = "";
$user = "";
$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";
Build and Tests
Build and Install PHP (Optional)
If PHP is not available, download and build from the PHP source code.
# Go to http://php.net/releases/
# Download php source code and copy to $WORKSPACE, for example
cd $WORKSPACE
Set PHP version to the environment variable. For example, set SF_PHP_VERSION to 7.1.6
if the downloaded PHP version is 7.1.6.
export SF_PHP_VERSION=7.1.6
Extract and build PHP:
cd $WORKSPACE
rm -rf $WORKSPACE/php-$SF_PHP_VERSION-src
rm -rf $WORKSPACE/install-php-$SF_PHP_VERSION
tar xvfj php-$SF_PHP_VERSION.tar.bz2
cd php-$SF_PHP_VERSION
./configure \
--prefix=$WORKSPACE/install-php-$SF_PHP_VERSION \
make
make install
Build
Set PHP_HOME to the base directory of the PHP. For example, if you built PHP, do this:
export PHP_HOME=$WORKSPACE/install-php-$SF_PHP_VERSION
or do this if the PHP is already installed in the system.
export PHP_HOME=/usr
where $PHP_HOME/bin is referred to run phpize:
Clone the this repository and run the build script.
git clone git@github.com:snowflakedb/pdo_snowflake.git
cd pdo_snowflake
./scripts/build_pdo_snowflake.sh
Run the following command to check if PHP PDO Driver for Snowflake is successfully loaded in memory.
$PHP_HOME/bin/php -dextension=modules/pdo_snowflake.so -m | grep pdo_snowflake
Note
As the build requires a special link process, a simple sequence of phpize followed by make doesn't work. See the build script for the detail.
Prepare for Test
Create a parameter file parameters.json under pdo_snowflake directory:
{
"testconnection": {
"SNOWFLAKE_TEST_USER": "",
"SNOWFLAKE_TEST_PASSWORD": "",
"SNOWFLAKE_TEST_ACCOUNT": "",
"SNOWFLAKE_TEST_WAREHOUSE": "",
"SNOWFLAKE_TEST_DATABASE": "",
"SNOWFLAKE_TEST_SCHEMA": "",
"SNOWFLAKE_TEST_ROLE": ""
}
}
Call env.sh script to set the test connection parametes in the environment variables.
source ./scripts/env.sh
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.
Trouble Shootings
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/20151012/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.1/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.