email iconemail phone iconcall

The DataStax ODBC driver with CQL connector for Apache Cassandra and DataStax Enterprise Available Now

By Alex Popescu - Brian Hess, Senior Product Manager for Analytics -  March 12, 2015 | 8 Comments

Today DataStax is delighted to announce the general availability of a new ODBC driver built on top of the Cassandra native protocol (CQL) that can be used free of charge with both open source Cassandra and DataStax Enterprise. The DataStax ODBC driver for Apache Cassandra, developed by ODBC software leader Simba Corporation, is compliant with the latest ODBC 3.52 specification, runs on both 32 and 64-bit versions of Windows and Linux, and can be downloaded from here.

The DataStax ODBC Driver for Apache Cassandra enables the integration with a broad array of tools for data processing, migration, analysis and visualization by leveraging the power of both CQL and SQL. It stands as a simple integration point to tap into the online data stored in Apache Cassandra, the massive, scale-out, always-on, distributed database that was built for the cloud, mobile, and internet-of-things.

Let’s take a look at how to get started with the DataStax ODBC Driver for Apache Cassandra. We will walk through using the 64-bit driver on Microsoft Windows, but the process is very similar for the 32-bit driver, and for Linux. In the end we will show how to connect from Tableau to explore a simple data set in Apache Cassandra.

First, ensure that you have a running Apache Cassandra cluster, and note the IP address of one of the nodes, as well as the native protocol port for that node.

Next, let’s install the driver. Download and run the DataStaxCassandraODBC64.msi:

Wizard Welcome

Click “Next”. Now, accept the license terms:

EULA

Click “I accept the terms in the License Agreement” and then click “Next”.  Now, choose a Destination Folder:

DestinationFolder

Click “Next”.

Installing

Click “Install” to complete the installation.  Once it is completed, you will see a success screen:

InstallationComplete

Now to set up a Data Source Name (DSN) for use in the ODBC tools.  Start by opening the ODBC Administrator.  You can find it under the Start menu, under the DataStax ODBC Driver for Apache Cassandra folder:

ODBCAdminStart

When it opens, click on the System DSN tab:

ODBCAdminSystem

You will see the DataStax Cassandra ODBC DSN64 DSN. Select that and click “Configure…”:

DSNSetup

Here you will enter the host and port for your cluster. You can also optionally change the name of the DSN and the description, as well. Click “Test…”:

DSNTest

Click “OK”, then click “OK”, and finally click “OK” a third time.  Now you have configured a DSN, and you can use this DSN in your ODBC-based tools.

As an example, let’s use Tableau and connect to a simple data set in Apache Cassandra to explore.  First, make sure you have downloaded and installed Tableau version 8.3, and go ahead and start it:

TableauWelcome

From here, click “Connect to data”:

TableauODBC

Select “Other Databases (ODBC)” and choose the DSN we created above. Here, we used the default name “DataStax Cassandra ODBC DSN64”. Next, click “Connect”, and fill out the Server, Port, and Database fields. For this example, we will use the WEATHERCQL keyspace and the DAILY table therein, which is included as a demo in DataStax Enterprise 4.6:

TableauConnection

Choose the “Cassandra” Database, and search for - and select - the weathercql Schema.  Lastly, search for the daily table. Drag the “daily (weathercql.daily)” table to the “Drag tables here” box in the upper right:

TableauChooseTable

Click “Go to Worksheet”.  You may get this popup warning, but you can click “OK”. There are some limitations in the driver, but Tableau will be able to compensate for them.  If you are interested, you can click “Show Details” for more information

TableauODBCWarning

We can now create a simple Worksheet:

TableauWorksheetBlank

This Worksheet is a “Live” Worksheet, meaning that Tableau will make live queries to the data in Cassandra as the operations happen. You may see status popups indicating that it has issued a query. Let’s look at the maximum temperature for each month in the data for each of the station locations.  First, create a filter on the Metric Dimension by dragging the Metric Dimension into the Filters box.  This will bring up a popup box to choose temperature:

TableauWorksheetFilter

Click “OK”. Now, drag the Date Dimension to the Columns box.  We want to change the function on the Date value to Month and Year, but it defaults to just Year.  If you hover over the blue bubble for YEAR(Date), there is a down arrow you can use to bring up another menu.  When you do this, choose the second Month option, the one that has “May 2011” (or something similar) next to it.  Next, drag the Max Measure to the Rows box.  We want the maximum of the maximums, so hover over the SUM(Max) bubble to find the down arrow that will allow you to choose the Measure submenu, where you can choose Maximum. Then, drag the Stationid Dimension to the Marks box, specifically on the Color box:

TableauWorksheetArrows

The finished chart should look like this:

TableauWorksheetOneLine

We can click on one of the lines to see just that line time series:

TableauWorksheetOneLine

You can continue refining this Worksheet and then save it (by clicking File->Save).

This completes our short guide. The online documentation for the driver is available here and a manual, in PDF format, is included with the installers. Now you can download the Download DataStax ODBC driver for Apache Cassandra and DataStax Enterprise with CQL connector and put it to real work!









DataStax has many ways for you to advance in your career and knowledge.

You can take free classes, get certified, or read one of our many white papers.



register for classes

get certified

DBA's Guide to NoSQL







Comments

  1. Tom Atwood says:

    The ODBC driver does not work with Cassandra 2.2. If attempted, it responds with an error that it does not support a “frozen” type.

    1. paul says:

      have this issue resolved?

  2. gaurav says:

    It does not seem to be working yet.

  3. Matt says:

    Hi,
    I installed Datastax ODBC driver – “DataStaxCassandraODBC64” and able to connect to Datastax using Tableau software.

    However, I am able to see the list of tables from one particular keyspace/schema.

    We have other Keyspaces for development purpose in Datastax and I am able to see all the tables from those keyspaces.

    Not sure what is the difference between those dummy keysapce vs the real, production keyspace.

    Any thoughts.

  4. Anil says:

    In Configuration I gave
    host : localhost and 127.0.0.1
    port : 9042 and 9160

    All those 4 combinations are not working.
    But Cassandra cluster is running in my windows machine on “Test Cluster” at 127.0.0.1:9042

    Any one help me why this ODBC is not connecting to Cassandra.

  5. chetan says:

    how to use ODBC driver with pentaho report designer

  6. Kumar says:

    Hi there,
    Please reply to this asap.
    I have given “CQL used for creating table:xxxx” by my client.
    I would like to know where should I be keeping this query, I don’tfind an option please help me.

  7. VAN AUSLOOS Xavier says:

    Hi,
    Try to install it in Windows 10 and I get
    error 2755.
    Was able to install it yesterday then I removed it from ODBC connectors (did not work).
    Installed more stuff and tried again with reinstalling it.

Comments are closed.




Subscribe for newsletter: