PHP, PDO & HP Vertica

As part of my new role, we’ll be working with HP’s Vertica for some of the data storage. I’ve been struggling the last few days to get it working on a Vagrant-provisioned linux box (VirtualBox) and finally have the process down. I thought I’d share it for those out there needing some help. I’m working with a Ubuntu server box, but these instructions could be pretty easily adapted to other distros:

1. Ensure you have the Unix ODBC package installed. This is what PDO uses to connect to the Vertica instance:

sudo apt-get -y install unixodbc

2. Ensure that your PHP installation has PDO support with ODBC handling:

php -m | grep -i odbc

You should see something like “PDO_ODBC” there if it’s installed.

3. Grab the latest Vertica linux drivers from the My.Vertica site (requires a login). Click on the Downloads section and scroll all the way down to the drivers. Grab the right ones for your linux installation. I used the Linux ODBC 64-bit.

4. Make a “/opt/vertica” directory on your system and untar the archive there.

sudo mkdir /opt/vertica
sudo tar zxvf vertica-odbc-6.1.3-0.x86_64.linux.tar.gz -C /opt/vertica/ > /dev/null

5. Create the configuration files where needed. There’s three of them – odbc.ini, odbcinst.ini and vertica.ini. Here’s the examples and the paths for them:

In /etc/odbc.ini:

[VerticaDev]
Description = Vertica Dev
Driver = /opt/vertica/lib64/libverticaodbc.so
Port = 5433
ServerName = your-hostname-here.com
DatabaseName = database-name
UserId = database-username
Driver = Vertica

In /etc/odbcinst.ini:

[Vertica]
Description = Vertica driver
Driver = /opt/vertica/lib64/libverticaodbc.so

In /etc/vertica.ini:

[Driver]
DriverManagerEncoding=UTF-16
ODBCInstLib = /usr/lib/x86_64-linux-gnu/libodbcinst.so.1
ErrorMessagesPath=/opt/vertica/lib64
LogLevel=4
LogPath=/tmp

If all goes well, you should be able to make a script like this to test it:

<?php
$dbName = 'your-database-name';
$hostname = 'your-hostname.com;
$account = 'your-username';
$password = 'your-password';
$driver = 'Vertica';

$dsn = 'Driver='.$driver.';Server='.$hostname.';Database='.$dbName;
$pdo = new PDO('odbc:'.$dsn, $account, $password);
var_export($pdo);
?>