Integrate Snowflake with Laravel

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 the docker-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