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
    "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
       job_id        hire_date     employee_id    commission_pct    department_id      email       first_name      last_name      manager_id         office         performance_ratings     phone_number     salary
                                                                                                                                                building    room                                                   
    ____________    ___________    ___________    ______________    _____________    __________    ___________    ____________    __________    ________________    ___________________    ______________    ______

    "SH_CLERK"      27-Jan-2004        184              NaN               50         "NSARCHAN"    "Nandita"      "Sarchand"         121        "North"     256         {2×1 int32}        "650.509.1876"     4200 
    "MK_REP"        17-Aug-2005        202             0.25               20         "PFAY"        "Pat"          "Fay"              201        "East"      349         {3×1 int32}        "603.123.6666"     6000 
    "PU_CLERK"      10-Aug-2007        119              NaN               30         "KCOLMENA"    "Karen"        "Colmenares"       114        "West"      252         {5×1 int32}        "515.127.4566"     2500 
    "PU_CLERK"      15-Nov-2006        118              NaN               30         "GHIMURO"     "Guy"          "Himuro"           114        "East"      227         {4×1 int32}        "515.127.4565"     2600 
    "PU_CLERK"      24-Dec-2005        116              NaN               30         "SBAIDA"      "Shelli"       "Baida"            114        "North"     189         {2×1 int32}        "515.127.4563"     2900 
    "PU_CLERK"      24-Jul-2005        117              NaN               30         "STOBIAS"     "Sigal"        "Tobias"           114        "South"     195         {2×1 int32}        "515.127.4564"     2800 
    "PU_CLERK"      18-May-2003        115              NaN               30         "AKHOO"       "Alexander"    "Khoo"             114        "West"      135         {2×1 int32}        "515.127.4562"     3100 
    "AC_ACCOUNT"    07-Jun-2002        206              NaN              110         "WGIETZ"      "William"      "Gietz"            205        "East"      258         {2×1 int32}        "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
            Name              DataType  
    _____________________    ___________

    "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
      job_id       hire_date     employee_id    commission_pct    department_id      email       first_name    last_name    manager_id         office         performance_ratings     phone_number     salary
                                                                                                                                          building    room                                                   
    __________    ___________    ___________    ______________    _____________    __________    __________    _________    __________    ________________    ___________________    ______________    ______

    "IT_ADMIN"    07-Oct-2020        301             0.25              30          "SMITH123"      "Alex"       "Smith"        114        "South"     160         {2×1 int32}        "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
      job_id       hire_date     employee_id    commission_pct    department_id      email       first_name    last_name    manager_id         office         performance_ratings     phone_number     salary
                                                                                                                                          building    room                                                   
    __________    ___________    ___________    ______________    _____________    __________    __________    _________    __________    ________________    ___________________    ______________    ______

    "IT_ADMIN"    07-Oct-2020        301             0.25              30          "SMITH456"      "Alex"       "Smith"        114        "South"     160         {2×1 int32}        "515.123.2345"     3000 

Close Cassandra Database Connection

close(conn)