executecql

Execute CQL query on Apache Cassandra database

Syntax

results = executecql(conn,query)

results = executecql(conn,query,'ConsistencyLevel',level)

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,'ConsistencyLevel',level) sets a consistency level to specify how many nodes must respond for the CQL query to execute.

Examples

Execute CQL Query

Using a Cassandra® database connection, 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 local host address. conn is a cassandra object.

contactPoints = "localhost";
conn = cassandra(contactPoints);

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 a Cassandra® database connection, 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 local host address. conn is a cassandra object.

contactPoints = "localhost";
conn = cassandra(contactPoints);

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 a quorum and display the results.

level = "QUORUM";
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"

Most of the replica nodes respond 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 — Cassandra database connection

cassandra object

Cassandra database connection, specified as a cassandra object.

query — CQL query

character vector | string scalar

CQL query, specified as a character vector or string scalar. For details about CQL, see the CQL Reference Documentation.

Example: "SELECT * FROM dev.maps"

Data Types: char | string

level — 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

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.

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