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);
?>

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s