Connecting QuerySurge to Cassandra with JDBC (Adejanovski DataStax Wrapper)

Apache Cassandra, a prominent NOSQL database, is supported by the DataStax driver stack. While DataStax provides a Java driver (among other language bindings), the driver is not a JDBC driver. QuerySurge (similar to other data tools) requires a JDBC driver to connect to Cassandra, and the need for a JDBC-compliant driver has been filled by the open source Adejanovski wrapper around the DataStax Java driver (available on GitHub). Note that this wrapper is released under the Apache 2.0 license. Setup details for using this Cassandra JDBC wrapper for the DataStax Java Driver with QuerySurge follow.

Note: Cassandra uses Cassandra Query Language (CQL), a variant of SQL. For more information, please see the DataStax CQL documentation.

Setting up a QuerySurge Connection with the Cassandra JDBC DataStax Wrapper

A QuerySurge Connection to Cassandra is set up using the Connection Extensibility feature of the QuerySurge Connection Wizard. Following are the details you'll need to set up your QuerySurge Connection to Cassandra with the "fat" DataStax wrapper.

1. Download the "fat" DataStax JDBC wapper, which is built with all dependencies. You can download the driver wrapper here.
   
   
2. Deploy the Cassandra JDBC driver to your Agent(s). The procedure for deploying a new driver to a QuerySurge Agent is here (for Agents on Windows) and here (for Agents on Linux).
   
   
3. Log into QuerySurge as a QuerySurge Admin user, and navigate to the Admin view. Steps for using the Connection Extensibility feature can be found here. To use the Connection Extensibility option in the Connection Wizard with the DataStax wrapper, you'll need the following information:
   
   Driver Class: com.github.adejanovski.cassandra.jdbc.CassandraDriver
   
   Connection URL: jdbc:cassandra://<cassandra-host-or-IP>:<port>/<keyspace>
   
   You'll need to provide the server and the port number. For the server, use either the fully qualified name of the server or the server IP address. Verify the port with a Cassandra admin or knowledgeable resource; port 9042 is the default Cassandra port. In addition, you should provide the keyspace that you want to connect to. Authentication credentials are typically needed as well. When you've entered your information, the Connection Wizard will look like:
   
   
   
   Note: You may want or need a more elaborate JDBC URL; see the GitHub documentation for details.
   
   
4. If you have a CQL Test Query, feel free to enter it in order to verify that your Connection parameters are correct. It should be a standard query that returns a small amount of information - one row is enough. You can use the Test Connection button if you've entered a Test Query:
   
   
   
   
5. Once you have the information entered and (optionally) verified, click the Save button to save the Connection. You're ready to use the Connection in a QueryPair.

Note: The JDBC driver returns the following data types as an object type that can be cast either to ascii or text type when used in QuerySurge: inet, smallint, time, timeuuid, tinyint, uuid, and date. For more information, see the DataStax CQL data types reference.

Note: The varint type must be cast to one of the following types: tinyint, smallint, int, bigint, float, double, decimal, text.

Note: The date type can be cast to a timestamp (in addition to ascii and text). Keep in mind, however, that Cassandra considers your Agent's local UTC time when converting a date value to timestamp. So, for example, the date "12-01-2017", would be read as "12-01-2017 00:00:00" at UTC+0, and when cast to a timestamp by an Agent located in UTC-5 would become "11-30-2017 19:00:00" because of the five-hour difference.

Note: The CQL text and ascii types (ascii and text are CQL synonyms) are 2GB in size, qualifying them as CLOB types. For more information on how QuerySurge handles CLOBs, please see the our Knowledge Base article on QuerySurge and CLOB Data.

Note: The JDBC driver discussed in this article is open-source software that may or may not be maintained for future Apache Cassandra releases.