Export MATLAB Data into Cassandra Database

This example shows how to export data from a MATLAB® table into an Apache™ Cassandra® database using a Cassandra database connection with the Apache Cassandra database C++ interface.

In this example, the Cassandra database includes the employees_by_job database table, which contains employee data and the job_id partition key.

Create Cassandra Database Connection

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);

Explore Data from Cassandra Database

Return the names of the Cassandra database tables in the employeedata keyspace. t is a string array that contains the names of these tables.

keyspace = "employeedata";
t = tablenames(conn,keyspace)

t = 3×1 string array
    "employees_by_id"
    "employees_by_job"
    "employees_by_name"

Import employee data into MATLAB from the employees_by_job table in the employeedata keyspace by using the Cassandra database connection.

keyspace = "employeedata";
tablename = "employees_by_job";
results = partitionRead(conn,keyspace,tablename);

Display the last few rows of the imported employee data.

tail(results)

ans=8×13 table
    "SH_CLERK"    27-Jan-2004    184    NaN    50    "NSARCHAN"    "Nandita"    "Sarchand"    121    1×2 table    [2;2]    "650.509.1876"    4200
    "MK_REP"    17-Aug-2005    202    0.2500    20    "PFAY"    "Pat"    "Fay"    201    1×2 table    [8;10;3]    "603.123.6666"    6000
    "PU_CLERK"    10-Aug-2007    119    NaN    30    "KCOLMENA"    "Karen"    "Colmenares"    114    1×2 table    [9;3;6;1;5]    "515.127.4566"    2500
    "PU_CLERK"    15-Nov-2006    118    NaN    30    "GHIMURO"    "Guy"    "Himuro"    114    1×2 table    [4;6;2;10]    "515.127.4565"    2600
    "PU_CLERK"    24-Dec-2005    116    NaN    30    "SBAIDA"    "Shelli"    "Baida"    114    1×2 table    [3;7]    "515.127.4563"    2900
    "PU_CLERK"    24-Jul-2005    117    NaN    30    "STOBIAS"    "Sigal"    "Tobias"    114    1×2 table    [9;10]    "515.127.4564"    2800
    "PU_CLERK"    18-May-2003    115    NaN    30    "AKHOO"    "Alexander"    "Khoo"    114    1×2 table    [4;6]    "515.127.4562"    3100
    "AC_ACCOUNT"    07-Jun-2002    206    NaN    110    "WGIETZ"    "William"    "Gietz"    205    1×2 table    [2;3]    "515.123.8181"    8300

results is a table that contains these variables:

• job_id — Job identifier

• hire_date — Hire date

• employee_id — Employee identifier

• commission_pct — Commission percentage

• department_id — Department identifier

• email — Email address

• first_name — First name

• last_name — Last name

• manager_id — Manager identifier

• office — Office location (table that contains two variables for the building and room)

• performance_ratings — Performance ratings

• phone_number — Phone number

• salary — Salary

Display the CQL data types of the columns in the employees_by_job database table.

cols = columninfo(conn,keyspace,tablename);
cols(:,1:2)

ans=13×2 table
                 "job_id"         "text"
              "hire_date"         "date"
            "employee_id"          "int"
         "commission_pct"       "double"
          "department_id"          "int"
                  "email"         "text"
             "first_name"         "text"
              "last_name"         "text"
             "manager_id"          "int"
                 "office"       "office"
    "performance_ratings"    "list<int>"
           "phone_number"         "text"
                 "salary"          "int"

Insert Data from MATLAB into Cassandra Database

Create a table of data representing one employee to insert into the Cassandra database. Specify the names of the variables. Create a table for the office information. Then, create a table with the employee information that contains the nested table of office information. Set the names of the variables.

varnames = ["job_id" "hire_date" "employee_id" ...
    "commission_pct" "department_id" "email" "first_name" ...
    "last_name" "manager_id" "office" "performance_ratings" ... 
    "phone_number" "salary"];
office = table("South",160, ...
    'VariableNames',["building" "room"]);
data = table("IT_ADMIN",datetime('today'),301,0.25,30,"SMITH123", ...
    "Alex","Smith",114,office,{[4 5]},"515.123.2345",3000);
data.Properties.VariableNames = varnames;

Insert the employee information into the Cassandra database.

upsert(conn,keyspace,tablename,data)

Display the inserted data by importing it into MATLAB using the partition key IT_ADMIN. The employees_by_job table contains a new row.

keyValue = "IT_ADMIN";
results = partitionRead(conn,keyspace,tablename,keyValue)

results=1×13 table
    "IT_ADMIN"    07-Oct-2020    301    0.2500    30    "SMITH123"    "Alex"    "Smith"    114    1×2 table    [4;5]    "515.123.2345"    3000

Update Data in Cassandra Database

Update the email variable in the new row of employee information.

results.email = "SMITH456";
upsert(conn,keyspace,tablename,results)

Display the updated data by importing it into MATLAB. The row contains the updated data in the email variable of the employees_by_job table.

results = partitionRead(conn,keyspace,tablename,keyValue)

results=1×13 table
    "IT_ADMIN"    07-Oct-2020    301    0.2500    30    "SMITH456"    "Alex"    "Smith"    114    1×2 table    [4;5]    "515.123.2345"    3000

Close Cassandra Database Connection

close(conn)