DataStax Developer Blog

Using the DataStax ODBC Driver for Apache Cassandra

By Robin Schumacher -  May 10, 2013 | 13 Comments

DataStax is pleased to make available an ODBC driver for Apache Cassandra that can be used free of charge with both open source Cassandra and DataStax Enterprise. Using the DataStax ODBC driver for Cassandra, you can connect to a database cluster with your favorite BI tools (e.g. Tableau, Microsoft Excel, etc.) or other development software and access data stored on Cassandra nodes.

The DataStax ODBC driver for Cassandra was developed by ODBC software leader Simba Corporation and is compliant with the latest ODBC 3.52 specification and runs on both 32 and 64-bit platforms.

Let’s take a quick walk through of how it works.

Installing the DataStax ODBC Driver for Cassandra

The DataStax ODBC driver for Cassandra is currently provided for Windows platforms, and can be downloaded from the DataStax website.

Installing the DataStax ODBC driver for Cassandra is simple. The only prerequisite needed for Windows is to have the Microsoft Visual C++ 2010 runtime installed for the appropriate platform (either 32 or 64-bit).

The installation of the driver is completed in just a few steps.

c odbc install start

c odbc install directory

Note that you may need to install both the 32 and 64-bit versions of the driver if, for example, you have certain tools that are 32-bit but are running on a 64-bit box.

Configuring the DataStax ODBC Driver for Cassandra

Configuring the DataStax ODBC driver for Cassandra is easy. On Windows, you invoke the ODBC Data Source Administrator utility and first validate that the driver is present:

c odbc define datasource start

Then, you create either a User or System DSN (data source name) that will be used by front end business intelligence tools. You’ll need to enter a name for the datasource as well as the IP or host name of the Cassandra node you want to connect to, along with a keyspace that will be referenced by the driver. Once the information is entered, you can test your connection to ensure it connect to your cluster.

Using the DataStax ODBC Driver for Cassandra

Note that the current driver does not currently support version 3.0 of the Cassandra Query Language (CQL3), which is the default for Cassandra 1.2 and higher (an upcoming version of the driver will support CQL3). However, CQL2 is supported, so you will want to make sure that any objects you want to reference with the driver are created with CQL2.

The Windows installer for Cassandra 1.2 and higher uses CQL3 by default and creates a shortcut for the CQL utility that can be easily clicked on and run. To create a CQL utility shortcut that uses CQL2, just create a new shortcut with the following target executable (substituting your install location if you changed it from the default location):

“C:\Program Files (x86)\DataStax Community\python\python.exe” “C:\Program Files (x86)\DataStax Community\apache-cassandra\bin\cqlsh” -2

Once you’ve successfully configured your ODBC datasource, you can use it in your preferred BI tools to connect to and pull data back from Cassandra. For example, to use the DataStax ODBC driver for Cassandra with Microsoft Excel, you can use the data connection wizard to select your new ODBC datasource:

c odbc excel start

c excel choose odbc

And then, select one or more data objects to pull back data:

c excel choose odbc dsn name

c excel choose odbc table

c excel populate

The ODBC driver also works with generic ODBC dev/query tools like Query Tool ODBC:

c tqodbc tool

Conclusions

Currently the DataStax ODBC Driver for Cassandra is in beta, but will be GA shortly. For more information, visit the client drivers downloads page on the DataStax website.



Comments

  1. Is there an ETA on when the ODBC driver will be remediation to support CQL3?

    Thanks.

    Rich

  2. Robin Schumacher Robin Schumacher says:

    No hard date yet. The dev teams are working on CQL3 support now and hope to have a more clear picture on timeline soon.

  3. Will the CQL (not 3) ODBC driver work within a tool like SSIS or Informatica to allow loads, extracts, and summarizations of Cassandra data?

    1. Robin Schumacher Robin Schumacher says:

      Simba (the company who makes the C* ODBC driver) does have a partnership with Informatica to support Cassandra. See the following for more details: http://goo.gl/gXJOv.

      1. Arun Reghunath says:

        This discussion looks interesting. Is there any ways in which we can access Cassandra from IBM Datastage (ETL tool). The ETL tool is installed in Redhat Linux (64bit)

  4. Vijay says:

    we are tryin to test cassandra data with IBM Cognos BI using ODBC.
    above screenshot based ODBC tool i am unable to find in datastax. can you please share direct URL to download.

  5. Corwin Brust says:

    Now that CQL2 is to be depreciated[1], is there any word on supporting for CQL3 with this product?

    [1] https://issues.apache.org/jira/browse/CASSANDRA-5918

    1. Robin Schumacher Robin Schumacher says:

      Development is currently underway so stay tuned…

  6. Vrushali says:

    Cassandra 1.2.13 with CQL 2. I ‘m not able to insert records into table but select works fine with c++ client & odbc driver beta version . Is this limitation or Am i missing something here? When is the next version of driver will be released?

  7. Giri Maradana says:

    [cqlsh 4.1.0 | Cassandra 2.0.4 | CQL spec 3.1.1 | Thrift protocol 19.39.0]

    Is the ODBC driver for CQL 3 available now. Is there an update on this.

  8. Robin Schumacher Robin Schumacher says:

    It is still in development at this time.

  9. In reference to the comment about about connecting this with DataStage: I just tried a POC for loading data to Cassandra using this driver with SAP BusinessObjects Data Services and was met with an unfortunate message:

    SQL submitted to ODBC data source <cassandra> resulted in error <[DataStax][SQLEngine] (31732) Driver is read-only and does not permit DELETE operations. >. The SQL submitted is <DELETE FROM keyspace.table >.

    Doh!!

    Any word if there will be a write-enabled driver developed?

    Thank you!

  10. Robin Schumacher Robin Schumacher says:

    Sorry for the issue you’re having. The driver is currently still an early alpha/beta. Currently, the development timeline shows a GA release sometime late in Q2 of this year, so stay tuned.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>