upsert

Insert or update data in Apache Cassandra database

Since R2021a

Syntax

upsert(conn,keyspace,tablename,data)

upsert(conn,keyspace,tablename,data,Name,Value)

Description

upsert(conn,keyspace,tablename,data) exports data from MATLAB^® by inserting or updating it in an Apache Cassandra^® database table.

upsert(conn,keyspace,tablename,data,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

Insert MATLAB Data into Cassandra Database

Insert data from MATLAB® into an Apache™ Cassandra® database using the Apache Cassandra database C++ interface, and display the data by using a Cassandra database connection.

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

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

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 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             NaN                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"      

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"    06-Oct-2020        301             0.25              30          "SMITH123"      "Alex"       "Smith"        114        "South"     160         {2×1 int32}        "515.123.2345"     3000 

Close the Cassandra database connection.

close(conn)

Update Data in Cassandra Database Using Consistency Level

Using the Apache™ Cassandra® database C++ interface, update data in an Apache Cassandra database with MATLAB® data. Display the updated data by using a Cassandra database connection. Specify a consistency level for the write operation.

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

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

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 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             NaN                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"      

Import the data to update by using the partitionRead function with the partition key value MK_REP. The data is for an employee who is a marketing representative.

keyValue = "MK_REP";
data = partitionRead(conn,keyspace,tablename,keyValue)

data=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                                                   
    ________    ___________    ___________    ______________    _____________    ______    __________    _________    __________    ________________    ___________________    ______________    ______

    "MK_REP"    17-Aug-2005        202             NaN               20          "PFAY"      "Pat"         "Fay"         201         "East"     349         {3×1 int32}        "603.123.6666"     6000 

Update the commission percentage to 0.25 for the marketing representative. Also, specify the consistency level "ONE" to ensure that one replica node commits the write operation.

data.commission_pct = 0.25;
level = "ONE";
upsert(conn,keyspace,tablename,data,'ConsistencyLevel',level)

Display the updated data by importing it into MATLAB using the partition key value MK_REP. The updated commission percentage for the marketing representative is 0.25.

keyValue = "MK_REP";
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                                                   
    ________    ___________    ___________    ______________    _____________    ______    __________    _________    __________    ________________    ___________________    ______________    ______

    "MK_REP"    17-Aug-2005        202             0.25              20          "PFAY"      "Pat"         "Fay"         201         "East"     349         {3×1 int32}        "603.123.6666"     6000 

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.

keyspace — Keyspace

character vector | string scalar

Keyspace, specified as a character vector or string scalar. If you do not know the keyspace, then access the Keyspaces property of the connection object using dot notation to view the keyspaces in the Cassandra database.

Example: "employeedata"

Data Types: char | string

tablename — Cassandra database table name

character vector | string scalar

Cassandra database table name, specified as a character vector or string scalar. If you do not know the name of the table, then use the tablenames function to find it.

Example: "employees_by_job"

Data Types: char | string

data — Data to insert or update

table

Data to insert or update in a Cassandra database, specified as a table. You must specify the primary keys of the Cassandra database table, but you can ignore other Cassandra columns. The names of the variables in the table must match the names of the Cassandra columns in the database table, without case sensitivity. The data types of the variables in the table must be compatible with the CQL data types of the Cassandra columns. For details, see Convert CQL Data Types to MATLAB Data Types Using Apache Cassandra Database C++ Interface.

Data Types: table

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: upsert(conn,keyspace,tablename,data,'ConsistencyLevel',"ONE",'RequestTimeout',15) exports data by receiving a write response from one node, and the database must wait 15 seconds to perform the write operation before throwing an error.

ConsistencyLevel — Consistency level

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

Consistency level, specified as one of these values.

Consistency Level Value	Write Operation
"ALL"	Commit on all replica nodes.
"EACH_QUORUM"	Commit on a majority of replica nodes in each data center.
"QUORUM"	Commit on most replica nodes.
"LOCAL_QUORUM"	Commit on most replica nodes in the local data center.
"ONE" (default)	Commit on one replica node.
"TWO"	Commit on two replica nodes.
"THREE"	Commit on three replica nodes.
"LOCAL_ONE"	Commit on one replica node in the local data center.
"ANY"	Commit on at least one replica node.

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

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

Version History

Introduced in R2021a