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.
lac32
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.
In this article:
 
 
Verify the Prerequisites
Before you connect to your Cassandra instance, ensure that you know your Cassandra REST connection information.
For more information about how to start the REST server, see the Apache Cassandra documentation.
Install the Apache Cassandra JDBC Driver
If you have installed 
CA Live API Creator
 on Tomcat, copy the 
cdata.jdbc.cassandra.jar
 JAR file (the JDBC driver) from the 
databaseDrivers
 directory to the 
%{CATALINA_HOME}/lib
 directory. After you have installed the JDBC driver, the Cassandra data source is available as an option in the connection wizard when creating an API.
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 
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.
For more information:
Connect to a Cassandra Database
Create your API and connect to a Cassandra database. For more information about creating APIs, see Create your API.
  1. In the connection wizard, select 
    Apache Cassandra
    .
  2. Complete the Host, Port, Database, Username, and Password fields, and then click 
    Continue
    .
You are connected to the 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 "Limitations using Cassandra as your Data Source" 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:
  • The Cassandra JDBC driver does not support connecting 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 Free SQL Resources.
  • Filtering rows:
    • You can filter records using system filters only based on partition keys. 
      CA Live API Creator
       does not support 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 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, 
      CA Live API Creator
       does not support ordering of 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. Cassandra does not support 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. Cassandra does not support 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 cannot use the following system filters if 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. Cassandra does not support the 
OR
 condition in 
SELECT
 queries.
 
notequal
 
You cannot use this system filter. Cassandra does not support 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. Cassandra does not support the 
NOT LIKE
 condition in 
SELECT
 queries.
For more information about system filters, see Structured Filters.