executecql

Execute CQL query on Apache Cassandra database

Since R2021a

Syntax

results = executecql(conn,query)

results = executecql(conn,query,Name,Value)

Description

results = executecql(conn,query) returns the results of executing a Cassandra^® Query Language (CQL) query on the Cassandra database using the Cassandra database connection. The executecql function imports the query results into MATLAB^®.

results = executecql(conn,query,Name,Value) specifies options using one or more name-value arguments. For example, 'ConsistencyLevel',"TWO" sets the consistency level to specify that two nodes must respond for the CQL query to execute.

Examples

Execute CQL Query

Using an Apache® Cassandra® database C++ interface, create a Cassandra® database connection and execute a CQL query to import data from a Cassandra database table into MATLAB®. In this case, the Cassandra database contains the employees_by_job database table with employee data in the employeedata keyspace.

Create a Cassandra database connection using the configured data source CassandraDataSource and a blank user name and password. The apacheCassandra function returns conn as a connection object.

datasource = "CassandraDataSource";
username = "";
password = "";
conn = apacheCassandra(datasource,username,password);

Write a CQL query that selects all employees who are programmers or shop clerks hired before April 30, 2006, and retrieves their job identifiers, hire dates, and email addresses. job_id is the partition key of the employees_by_job database table, and hire_date is a clustering column.

query = strcat("SELECT job_id,hire_date,email ", ... 
    "FROM employeedata.employees_by_job ", ...
    "WHERE job_id IN ('IT_PROG','SH_CLERK') ", ...
    "AND hire_date < '2006-04-30';");

Execute the CQL query and display the first few rows of results.

results = executecql(conn,query);
head(results)

ans=8×3 table
      job_id       hire_date       email   
    __________    ___________    __________

    "IT_PROG"     05-Feb-2006    "VPATABAL"
    "IT_PROG"     03-Jan-2006    "AHUNOLD" 
    "IT_PROG"     25-Jun-2005    "DAUSTIN" 
    "SH_CLERK"    24-Apr-2006    "AWALSH"  
    "SH_CLERK"    23-Feb-2006    "JFLEAUR" 
    "SH_CLERK"    24-Jan-2006    "WTAYLOR" 
    "SH_CLERK"    13-Aug-2005    "JDILLY"  
    "SH_CLERK"    14-Jun-2005    "KCHUNG"  

results is a table with the job_id, hire_date, and email variables. The hire_date variable is a datetime array and the job_id and email variables are string arrays.

Close the Cassandra database connection.

close(conn)

Execute CQL Query Using Consistency Level

Using the Apache® Cassandra® database C++ interface, create a Cassandra database connection and execute a CQL query to import data from a Cassandra database table into MATLAB®. Specify a consistency level for returning query results. In this case, the Cassandra database contains the employees_by_job database table with employee data in the employeedata keyspace.

Create a Cassandra database connection using the configured data source CassandraDataSource and a blank user name and password. The apacheCassandra function returns conn as a connection object.

datasource = "CassandraDataSource";
username = "";
password = "";
conn = apacheCassandra(datasource,username,password);

Write a CQL query that selects all employees who are programmers and retrieves their hire dates and email addresses. job_id is the partition key of the employees_by_job database table. Limit the returned data to three rows.

query = strcat("SELECT hire_date,email ", ... 
    "FROM employeedata.employees_by_job ", ...
    "WHERE job_id = 'IT_PROG'", ...
    "LIMIT 3;");

Execute the CQL query with the consistency level set to one node and display the results.

level = "ONE";
results = executecql(conn,query,'ConsistencyLevel',level)

results=3×2 table
     hire_date       email   
    ___________    __________

    21-May-2007    "BERNST"  
    07-Feb-2007    "DLORENTZ"
    05-Feb-2006    "VPATABAL"

In this case, only one replica node responds with returned data. results is a table with the hire_date and email variables. The hire_date variable is a datetime array and the email variable is a string array.

Close the Cassandra database connection.

close(conn)

Input Arguments

conn — Apache Cassandra database connection

connection object

Apache Cassandra database connection, specified as a connection object.

query — CQL query

character vector | string scalar

CQL query, specified as a character vector or string scalar. For details about CQL, see the Apache Software Foundation's CQL Reference Documentation.

Example: "SELECT * FROM dev.maps"

Data Types: char | string

Name-Value Arguments

Specify optional pairs of arguments as Name1=Value1,...,NameN=ValueN, where Name is the argument name and Value is the corresponding value. Name-value arguments must appear after other arguments, but the order of the pairs does not matter.

Before R2021a, use commas to separate each name and value, and enclose Name in quotes.

Example: results = executecql(conn,query,'ConsistencyLevel',"TWO",'RequestTimeout',15) specifies to return query results when two nodes respond for the CQL query execution, and the database must wait 15 seconds to return the query before throwing an error.

ConsistencyLevel — Consistency level

"ONE" (default) | character vector | string scalar

Consistency level, specified as one of these values.

Consistency Level Value	Consistency Level Description
"ALL"	Return query results when all replica nodes respond (read operation) or commit the change (write operation).
"EACH_QUORUM"	Finish execution when most replica nodes in each data center commit the change (write operation only).
"QUORUM"	Return query results when most replica nodes respond (read operation) or commit the change (write operation).
"LOCAL_QUORUM"	Return query results when most replica nodes in the local data center respond (read operation) or commit the change (write operation).
"ONE" (default)	Return query results when one replica node responds (read operation) or commits the change (write operation).
"TWO"	Return query results when two replica nodes respond (read operation) or commit the change (write operation).
"THREE"	Return query results when three replica nodes respond (read operation) or commit the change (write operation).
"LOCAL_ONE"	Return query results when one replica node in the local data center responds (read operation) or commits the change (write operation).
"ANY"	Finish execution even if all replica nodes for the specified partition are not available (write operation only).
"SERIAL"	Return query results for current (and possibly uncommitted) data for replica nodes in any data center (read operation only).
"LOCAL_SERIAL"	Return query results for current (and possibly uncommitted) data for replica nodes in the local data center (read operation only).

You can specify the value of the consistency level as a character vector or string scalar.

For details about consistency levels, see Configuring Data Consistency.

Data Types: char | string

RequestTimeout — Request timeout

Read-only: 12 (default) | positive numeric scalar

This property is read-only.

Request timeout, specified as a positive numeric scalar. The request timeout indicates the number of seconds the database waits to return a CQL query before throwing an error.

Data Types: double

Output Arguments

results — CQL query results

table

CQL query results, returned as a table. Each Cassandra database column from the result of the CQL query is a variable in the table. The variable names match the names of the Cassandra database columns from the result of the CQL query.

The data types of the variables in the table depend on the CQL data types. For details about how CQL data types convert to MATLAB data types, see Convert CQL Data Types to MATLAB Data Types Using Apache Cassandra Database C++ Interface.

For read or write operations that return no data, the executecql function returns an empty table.

Version History

Introduced in R2021a