For those out there working with Dato(Graphlab) and trying to setup an ODBC connection to just pull all the data straight into the SFrame, here are some tips I’ve learned from troubleshooting.

What is ODBC?
Open Database Connectivity which is a middleware API to help standardize and simplify access to database management systems.

**Connection Pointers:**There are a number of links on odbc setup but it was a little tricky to get it to work with Graphlab, Linux and OSX and Graphlab’s documentation is a little sparse in that area right now.

LinuxThis is one of the links I found that was helpful for setting up on a Linux machine. The following are the steps I used

  • wget http://yum.postgresql.org/%5Bversion #]/redhat/rhel-[version #]/pgdg-centos-[OS type & #].noarch.rpm
  • Use the package version link from http://yum.postgresql.org/ in the wget command above to pull the rpm file that you need. Note, you are setting up the postgres yum server on your computer to run yum install postgres odbc packages after the fact
  • rpm -ivh ./pgdg-[OS type & #].noarch.rpm
  • yum install postgresql[version #]-odbc.[version #]
  • yum install postgresql[version #]-odbc-debuginfo.[verions #]
  • yum install unixODBCl

In the yum install portion, you can combine and separate with spaces each package on one line. You may need to sudo install depending on the role you are logged into the system as and the available permissions. Best practice is to avoid using sudo.

Now that you have the packages installed, update the odbcinist.ini file which should be in /etc/ directory. Sample file contents include:

[PostgreSQL]
Description = ODBC for PostgreSQL
Driver = /usr/pgsql-[version #]/lib/psqlodbc.so
Setup = /usr/lib64/libodbcpsqlS.so
Driver64 = /usr/pgsql-[version #]/lib/psqlodbcw.so
Setup64 = /usr/lib64/libodbcpsqlS.so.2.0.0
Database = [database name]
Server = [address for server which if redshift it will look like: ?……redshift.amazonaws.com]
Port = [port for your setup something like 5432 or 5439]
FileUsage = 1

Settings above can vary. Definitely read up on options and how it relates to your connection setup.

OSX
This was a little trickier because the documentation wasn’t as clear. I ended up using homebrew package manager and the following steps worked.

  • brew update
  • brew install unixodbc
  • brew install psqlodbc

Next setup odbc.ini which should be under the /usr/local/Cellar/unixodbc/[version #]/etc/ directory. Sample file contents include:

[Postgres_db]
Description = ODBC for PostgreSQL
Driver = PostgreSQL
Database = [database name]
Server = [address for server which if redshift it will look like: ?……redshift.amazonaws.com]
Port = [port for your setup]
Protocol = [protocol for your setup]
Debug = 1

Then setup odbcinst.ini which should also be under the /usr/local/Cellar/unixodbc/[version #]/etc/ directory. Sample file contents include:

[PostgreSQL]
Description = PostgreSQL ODBC driver
Driver = /usr/local/Cellar/psqlodbc/[version #]/lib/psqlodbcw.so
Setup = /usr/local/Cellar/unixodbc/[version #]/lib/libodbc.2.dylib
Debug = 0
CommLog = 1
UsageCount = 1

The sticky part for getting graphlab odbc connect to work was that I needed path variables to point to the odbc config files. Thankfully I got this idea from this Stackoverflow post. So in the .bash_profile (which should be in your home directory – use ~/ to get there) add the following:

export ODBCINI=/usr/local/Cellar/unixodbc/[version #]/etc/odbc.ini
export ODBCSYSINI=/usr/local/Cellar/unixodbc/[version #]/etc/

Same with Linux, the setup will vary based on your configuring needs. If at first you don’t succeed, keep researching on how to adjust.

Graphlab/DataAt this point you can go into a python or Ipython kernal and try:

  • import graphlab
  • graphlab.connect_odbc(“Driver=PostgreSQL;Server=[server address like above];Database=[database name];UID=[username];PWD=[password]”)

For some reason even though the parameters in the connection string are defined in the odbcinst.ini config files, Graphlab complains that the string is missing data without them. Specifically, you need to include Driver, Server, Database, UID and PWD. Its good security to pass in your password at least as a variable that comes form a config file and/or the environment.

Once the odbc connection worked, it made the data product run so much more effectively. I’m able to pull the data directly into the package that will build the model and stripped out an extra step that previously existed to query the data into a middle storage before loading it to the package that would train the model. There are other tools out there like that coming into wider use to cut to the chase regarding data processing and machine learning. Spark is one such tool that I’m especially interested in and will try to write about in the future.