Import Data from Cassandra Database Table Using CQL

This example shows how to import data from an Apache™ Cassandra® database table into MATLAB® using the Cassandra Query Language (CQL) and a Cassandra database connection with the Apache Cassandra database C++ interface.

In this example, you use the executecql function to execute a CQL query that filters by a clustering column and limits rows in the query results. Alternatively, you can use the executecql function to write non-SELECT CQL statements. For easy data import using the partition key values of a Cassandra database table, use the partitionRead function instead.

For this example, the Cassandra database contains the employees_by_job database table with employee data and the job_id partition key. The hire_date database column is a clustering column.

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, and retrieves their job identifiers, hire dates, and email addresses. Filter the query by those employees hired before April 30, 2006, using the hire_date clustering column. Limit the returned data to four rows.

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'", ...
    "LIMIT 4;");

Execute the CQL query using the Cassandra database connection and display the results.

results = executecql(conn,query)

results=4×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"  

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)