Apache Cassandra Data Source

Use the information in this article to connect to an Apache Cassandra database. Cassandra is a columnar database that used to store non-tabular big data. With the JDBC driver installed, the Cassandra data source is available as an option in the connection wizard when creating an API.
lac42
Use the information in this article to connect to an Apache Cassandra database. Cassandra is a columnar database that used to store non-tabular big data. With the JDBC driver installed, the 
Cassandra
 data source is available as an option in the connection wizard when creating an API.
In this article:
 
 
Install the Apache Cassandra JDBC Driver
The self-contained, single-user version of 
CA Live API Creator
 based on Jetty includes the Apache Cassandra JDBC driver. The JDBC driver is located in the 
%JETTY_HOME%/caliveapicreator/lib/ext
 directory. If you have installed this version of 
CA Live API Creator
, no additional steps are required to install the JDBC driver.
If you have installed 
CA Live API Creator
 on another Java container, such as Apache Tomcat, copy the 
cdata.jdbc.cassandra.jar
 JAR file (the JDBC driver) that is located in the 
databaseDrivers
 directory to the directory that is based on the Java container on which you have installed 
CA Live API Creator
:
Apache Tomcat
Copy the JDBC driver into the 
${CATALINA_HOME}/lib
 directory.
Oracle WebLogic
Copy the JDBC driver to the 
<Your WebLogic Domain Directory>/lib
 directory.
JBoss/WildFly
For more information, see Install on JBoss.
 
IBM WebSphere
Add the JDBC driver to your Web application server.
Using the WebSphere administrative console, create a JDBC provider for the driver in the server.
For more information about how to create a JDBC provider, see the IBM WebSphere documentation.
For more information:
Connect to a Cassandra Database
You can connect to your Cassandra database using the following methods:
  • (If you have not created your API yet) When you create your API.
    For more information about how to create an API, see Creating APIs.
  • (If you have an existing API) By adding a connection to a Cassandra data source.
    For more information about how to add a connection to a data source, see Database Connectivity.
The following procedure describes how to connect to your Cassandra database when creating your API.
Prerequisite:
 You know your Cassandra REST connection information. For more information about how to start the REST server, see the Apache Cassandra documentation.
Follow these steps:
 
  1. In API Creator, on the APIs page, click 
    Create New API
    .
    The connection wizard opens.
  2. Click 
    Database First
    .
  3. Select 
    Apache Cassandra
     as the data source type.
  4. Complete the Host, Port, Database, Username, and Password fields, and then click 
    Continue
    .
You are connected to the Cassandra database.
Cassandra Data Types
You can read and write the following Cassandra data types:
Data type
Modeled as...
Notes
ascii
string
bigint
long
blob
binary
boolean
boolean
counter
long
You can only view this data type.
For more information about this limitation, see the "Cassandra Data Source Limitations" section.
date
SimpleDate
For more information about how to use this data type in JavaScript, see Date, Time, Timestamp, and TimestampOffset.
decimal
decimal
double
decimal
float
decimal
inet
string
int
decimal
smallint
decimal
text
string
time
SimpleTime
For more information about how to use this data type in JavaScript, see Date, Time, Timestamp, and TimestampOffset.
timestamp
SimpleTimestampOffset
For more information about how to use this data type in JavaScript, see Date, Time, Timestamp, and TimestampOffset.
timeuuid
string
tinyint
decimal
uuid
string
varchar
string
varint
string
Set<v>
string
Map<k,v>
string
List<v>
string
Cassandra Data Source Limitations
The following are known limitations in Cassandra:
  • You cannot use the Cassandra JDBC driver to connect to multiple Cassandra nodes.
  • Executing queries:
    • You cannot execute queries with nested selects. 
    • You can execute a query as part of a Free SQL resource type by including the  and the  strings in the query. Including these queries in the strings disables the outer selects that API Server adds for pagination.
      For more information about how to enable manual control of Free SQL resource types, see Define Free SQL Resource Types.
  • Filtering rows:
    • You can filter records using system filters only based on partition keys. You cannot use Cassandra's 
      ALLOW FILTERING
       option in filters.
      For more information about the 
      ALLOW FILTERING
       option, see the Cassandra website.
    • SQL queries must conform to the Cassandra protocol when adding conditional filtering to queries using a ‘where’ clause in the following cases:
      • When adding SQL queries to filters in resources.
        For more information about how to apply filters and sorts to each level of an endpoint, including the 
        Filter
         field, see Customize your API.
      • When adding row filters when setting entity-level permissions for roles.
        For more information about how to set entity-level permissions for roles, including the Row Filter field, see Authorization and Role-Based Endpoint Access.
      • When adding a SQL query to user filters.
        For more information about how to add user filters, including the 
        Filter
         field, see Structured Filters.
  • Viewing data in Data Explorer
    • To avoid performance lag when dealing with a large number of rows, you cannot order Cassandra records based on keys. 
      CA Live API Creator
       returns the records based on the ordering the Cassandra database returns.
    • You cannot sort data by clicking the column name in the Main Grid of Data Explorer.
    • You cannot search in Data Explorer. You cannot add the 
      LIKE
       condition in 
      SELECT
       queries.
    • You cannot view the data for tables that are part of a defined relationship in Data Explorer. Data Explorer queries the Cassandra database with the 
      OR
       condition for tables that are part of those relationships. You cannot add the OR condition in 
      SELECT
       queries (see the 
      equal_or/equal_uc_or
       system filter name entry in the "Limitations using Cassandra as your Data Source" section).
  • You can only view Cassandra's 
    counter
     data type.
Limitations using Cassandra as your Data Source
You can use all systems filters 
except
 the following when you are using Cassandra as your data source:
System Filter Name
Limitation
equal
To perform an 
AND
 query for tables that have a partition key and clustered keys, use multiple 
equal
 system filters.
 
equal_or/equal_uc_or
 
You cannot use this system filter. You cannot add the 
OR
 condition in 
SELECT
 queries.
notequal
You cannot use this system filter. You cannot add the 
!=
 relation in 
SELECT
 queries. 
less/lessequal/ greater/greaterequal
To filter out results based on partition keys, you must use this system filter on clustered keys in conjunction with 
equal
 .
like
You can use this system filter only for properly indexed columns. To filter out results based on partition keys, add this system filter in conjunction with the 
equal
 system filter.
notlike
You cannot use this system filter. You cannot add the 
NOT LIKE
 condition in 
SELECT
 queries.
For more information about system filters, see Structured Filters.