Snowflake is a cloud-based data warehousing service that offers high performance, scalability, and flexibility for managing large volumes of data. Using the offical pdo driver, PHP applications can directly interact with Snowflake, enabling seamless execution of queries and retrieval of data.
In order to integrate Snowflake with our Laravel application using PostgreSQL we're going to use the laravel-pdo-odbc package and the pdo_snowflake PHP PDO driver from snowflake.
At the end of the post I leave a script with all the steps to build and install the driver in a Dockerfile.
NOTE: Snowflake PHP PDO driver does not yet support ARM/AARCH64 architecture on Linux.
PHP PDO Driver
First we have to build and install the pdo driver.
Prerequisites
To build the Snowflake PHP PDO Driver, we need to have installed the following software:
On Linux:
- PHP 8.1 or higher (Note: support for PHP 8.0 or lower is deprecated).
- gcc 5.2 or higher.
- cmake 2.8 or higher
And the following php modules:
- php-pdo
- pdo_pgsql
- pdo_odbc
Building the Driver
1.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/local/bin
, run the following command:
export PHP_HOME=/usr/local
2.Clone the pdo_snowflake
repository, and run the script to build the driver:
git clone https://github.com/snowflakedb/pdo_snowflake.git
cd pdo_snowflake
./scripts/build_pdo_snowflake.sh
To verify that the driver was succesfully built, run the next command:
$PHP_HOME/bin/php -dextension=modules/pdo_snowflake.so -m | grep pdo_snowflake
It should appear pdo_snowflake
in the output from the command.
Installing the Driver
1.Copy pdo_snowflake.so
from the modules subdirectory in the repository to the PHP extension directory.
cp modules/pdo_snowflake.so /usr/local/lib/php/extensions/no-debug-non-zts-*/
2.Copy cacert.pem
from the libsnowflakeclient
subdirectory in the repository to the PHP configuration directory containing the PHP configuration files. To find the PHP configuration directory, you can use this command $PHP_HOME/bin/php -ini
.
cp ./libsnowflakeclient/cacert.pem /usr/local/etc/php/cacert.pem
3.In the same directory that contains the PHP configuration files, create a config file named pdo_snowflake.ini
that contains the following settings:
extension=pdo_snowflake.so
pdo_snowflake.cacert=<path to PHP config directory>/cacert.pem
4.If you are using PHP with an application server or web server (e.g. Apache or nginx), restart the server.
Integrating the PHP Driver in Laravel
Once we have the driver set up we need to install the package to make the integration with laravel.
1.To add the package to your project, run the following command:
composer require yoramdelangen/laravel-pdo-odbc
2.Register the service provider in the app
file from you project:
// app.php
'providers' => [
// ...
LaravelPdoOdbc\ODBCServiceProvider::class,
];
3.Add a Database configuration into the database
file:
// database.php
'snowflake' => [
'driver' => 'snowflake_native',
'account' => '{account_name}.eu-west-1',
'username' => '{username}',
'password' => '{password}',
'database' => '{database}',
'warehouse' => '{warehouse}',
'schema' => 'PUBLIC', // change it if necessary.
'options' => [
// Required for Snowflake usage
\PDO::ODBC_ATTR_USE_CURSOR_LIBRARY => \PDO::ODBC_SQL_USE_DRIVER
]
],
4.Finish! Now you can use Eloquent ORM, and other Illuminate components as usual.
# Facade
$users = DB::connection('snowflake')
->table('users')
->where('created_at', '>', '2024-03-15')
->get();
# ORM
$users = User::all();
To know more about the PHP pdo driver and the laravel package, you can check the documentation here:
PHP PDO drive: https://github.com/snowflakedb/pdo_snowflake
Laravel package: https://github.com/appsfortableau/laravel-pdo-odbc
Dockerfile
If you use Docker in your local environment, here is the script to add into your dockerfile to download and install the php pdo driver.
NOTE: If you are using Docker on an ARM architecture, you can force it to a x86 architecture adding
platform linux/x86_64
on thedocker-compose.yml
file in the container that you are installing the driver.
# Dockerfile
# Install system dependencies
RUN apt-get update && apt-get install -y \
cmake \
&& docker-php-ext-configure pgsql -with-pgsql=/usr/local/pgsql \
&& apt-get clean && rm -rf /var/lib/apt/lists/* \
&& docker-php-ext-configure pdo_odbc --with-pdo-odbc=unixODBC,/usr \
&& docker-php-ext-install \
pdo \
pdo_pgsql \
pdo_odbc \
ENV PHP_HOME /usr/local
# Pull the Snowflake PDO repo and install the driver
RUN mkdir /tmp/pdo_snowflake && \
git clone https://github.com/snowflakedb/pdo_snowflake.git /tmp/pdo_snowflake
RUN cd /tmp/pdo_snowflake && \
bash ./scripts/build_pdo_snowflake.sh && \
cp modules/pdo_snowflake.so /usr/local/lib/php/extensions/no-debug-non-zts-*/ && \
cp ./libsnowflakeclient/cacert.pem /usr/local/etc/php/cacert.pem
RUN echo "extension=pdo_snowflake.so" > /usr/local/etc/php/conf.d/pdo_snowflake.ini && \
echo "pdo_snowflake.cacert=/usr/local/etc/php/cacert.pem" >> /usr/local/etc/php/conf.d/pdo_snowflake.ini
Top comments (0)