Integrate Snowflake with Laravel
Tags: laravel, php, snowflake, database
Published: 2024-06-03 21:29:55

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](https://github.com/appsfortableau/laravel-pdo-odbc) package and the [pdo_snowflake](https://github.com/snowflakedb/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
```