Insert or update data in Apache Cassandra database
Insert data from MATLAB® into a Cassandra database, 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 local host address. conn
is a cassandra
object.
contactPoints = "localhost";
conn = cassandra(contactPoints);
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_job"
"employees_by_id"
"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" 13-Mar-2019 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 a Cassandra database with MATLAB® data, and 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 local host address. conn
is a cassandra
object.
contactPoints = "localhost";
conn = cassandra(contactPoints);
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_job"
"employees_by_id"
"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 "QUORUM"
to ensure that most replica nodes commit the write operation.
data.commission_pct = 0.25; level = "QUORUM"; 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)
conn
— Cassandra database connectioncassandra
objectCassandra database connection, specified as a cassandra
object.
keyspace
— KeyspaceKeyspace, specified as a character vector or string scalar. If you do not know the keyspace,
then access the Keyspaces
property of the cassandra
object using dot notation to view the keyspaces in the Cassandra database.
Example: "employeedata"
Data Types: char
| string
tablename
— Cassandra database table nameCassandra 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 updateData 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.
Data Types: table
level
— Consistency level"ONE"
(default) | character vector | string scalarConsistency level of the write operation, specified as one of the values in this table.
Consistency Level Value | Write Operation |
---|---|
| Commit on all replica nodes. |
| Commit on a majority of replica nodes in each data center. |
| Commit on most replica nodes. |
| Commit on most replica nodes in the local data center. |
| Commit on one replica node. |
| Commit on two replica nodes. |
| Commit on three replica nodes. |
| Commit on one replica node in the local data center. |
| 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
A modified version of this example exists on your system. Do you want to open this version instead?
You clicked a link that corresponds to this MATLAB command:
Run the command by entering it in the MATLAB Command Window. Web browsers do not support MATLAB commands.
Choose a web site to get translated content where available and see local events and offers. Based on your location, we recommend that you select: .
Select web siteYou can also select a web site from the following list:
Select the China site (in Chinese or English) for best site performance. Other MathWorks country sites are not optimized for visits from your location.