Connecting to Vertica with PHP in Linux

Posted on Tuesday, June 24, 2014 at 5:17PM by Jack Servedio

Unlike MySQL, SQL Server, or PostegreSQL, PHP is unable to natively connect to Vertica databases. Luckily, PHP does include ODBC support natively with PDO and Vertica has ODBC drivers. With a little bit of configuration, we can be set up and connecting to a Vertica cluster in just a few minutes.

Before we begin, there are a few dependencies that have to be installed. G++ and OpenSSL are required (and you probably have them already) and we will be using the unixODBC package to implement the ODBC API.

Now that the main dependencies are out of the way, lets go ahead and download the appropriate Vertica Driver. You will have to create a My Vertica account, if you don't already have one, and log into the Vertica Web Site. Once in, navigate to the driver download page and download the appropriate ODBC Linux Driver - there are both 32 and 64 bit versions. You will want to download the driver to /opt/vertica. In my examples, I will be installing Vertica ODBC driver version 6.1.2 for x86 64-bit.

With the drivers downloaded, go ahead and uncompress them. The drivers are going to be gzip compressed tar archives, so a simple tar -zxvf will expand them for you. Before we configure the drivers, we need to make sure that all of the required packages and dependencies are installed with ldd on. If everything is good, it should look like this.

root@server:/opt/vertica# ldd lib64/libverticaodbc.so
	linux-vdso.so.1 =>  (0x00007fff2a3fe000)
	libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x00007fc2750f6000)
	libresolv.so.2 => /lib/x86_64-linux-gnu/libresolv.so.2 (0x00007fc274edc000)
	libcrypt.so.1 => /lib/x86_64-linux-gnu/libcrypt.so.1 (0x00007fc274ca2000)
	libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0 (0x00007fc274a85000)
	libstdc++.so.6 => /usr/lib/x86_64-linux-gnu/libstdc++.so.6 (0x00007fc274781000)
	libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x00007fc27447c000)
	libgcc_s.so.1 => /lib/x86_64-linux-gnu/libgcc_s.so.1 (0x00007fc274266000)
	libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007fc273e9e000)
	/lib64/ld-linux-x86-64.so.2 (0x00007fc276869000)
	

If any of the required shared objects (.so files) are shown as "Not Found", you will have to install or update those packages on your machine.

We are now ready to configure ODBC to connect to Vertica using the driver we just downloaded. To do this, we will need to edit two ODBC configuration files, create a new configuration file, and create an environment variable to reference it. To begin editing the ODBC configuration files, you first have to find them - in most cases they are located in /etc/, but if they are not there simply use odbcinst -j to determine the correct location.

The first file we will be updating is the odbc.ini file. In this file, we will be creating a DSN (Database Source Name) for our Vertica Database. This is what the file will look like if you have no other DSNs configured. Be sure to use your Vertica configuration.

[ODBC Data Sources]
	VerticaDSNunixodbc = vertica_database_name

[VerticaDSNunixodbc]
     Description = Vertica Database using ODBC Driver
     Driver = /opt/vertica/lib64/libverticaodbc.so
     Servername = vertica.myserver.com
     Database = vertica_database_name
     UserName = vertica_username
     Password = vertica_password
     Port = 5433
	 

Next, we will be editing odbcinst.ini, which is located in the same directory has odbc.init, typically /etc/. Here is what it will look like if you have no other DSNs configured.

[VerticaDSNunixodbc]
     Description = Vertica Database using ODBC Driver
     Driver = /opt/vertica/lib64/libverticaodbc.so
	 

Next, we will need to create the configuration file /etc/vertica.ini. This is the configuration that the Vertica Driver will use to communicate with unixODBC. In this file, we will provide the path to the libodbcinst Shared Object. It should be located in /usr/lib, but may not be there so be sure to verify it's location. Here is what the vertica.ini file should look like.

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

Now that the Vertica configuration file is created, we will need to create an environment variable called VERTICAINI that points to it. This is how the Vertica Driver will find the configuration file. Here is how to create and verify the environment variable.

root@server:/opt/vertica# export VERTICAINI=/etc/vertica.ini
root@server:/opt/vertica# cat $VERTICAINI
[Driver]
	DriverManagerEncoding = UTF-16
	ODBCInstLib = /usr/lib/x86_64-linux-gnu/libodbcinst.so
	ErrorMessagesPath = /opt/vertica/lib64
	LogLevel = 4
	LogPath = /tmp
root@server:/opt/vertica#

The Vertica Driver is now configured to work with unixODBC and the DSN for your Vertica Database is all set and ready to go. We will use the isql tool to verify that the connection is working before we attempt to connect using PHP.

root@server:/opt/vertica# isql -v VerticaDSNunixodbc
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> quit
root@server:/opt/vertica#

Great! Everything is working. Lets write some basic PHP Code that uses PHP's PDO module to connect to the Vertica DSN we just created and run a sample query.

<?php
    # Vertica DSN Configuration - Edit with your Database Name!
    $vadb_dsn = 'odbc:DSN=VerticaDSNunixodbc;Database=vadb';
    $vadb_options = array(PDO::ATTR_AUTOCOMMIT=>FALSE, PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION);
    $vadb_user = 'username';
    $vadb_pass = 'password';    


    # Connect to Vertica Database
    try {
        $vadb = new PDO( $vadb_dsn, $vadb_user, $vadb_pass, $vadb_options );
    }
    catch ( PDOException $e ) {
        die( "Couldn't connect to Vertica" );
    }

    # Query to Select Timestamp of Vertica Database
    $vsql = "SELECT CURRENT_TIMESTAMP as 'ts';";

    # Prepare Statement and Execute
    $stmt = $vadb->prepare( $vsql );
    $stmt->execute();

    # Fetch Results
    while( $row = $stmt->fetch(PDO::FETCH_ASSOC) ) {
        $vertica_timestamp = $row['ts'];
    }

    # Close Connection
    $vadb = null;

    # Output the Timestamp
    echo "Vertica Current Timestamp: " . $vertica_timestamp;
?>

When this script is run, it will connect to the configured Vertica Database and execute a query to get the current timestamp. You have successfully configured PHP and your server to connect with Vertica.