DML statements (data manipulation language) in Amazon Keyspaces
Data manipulation language (DML) is the set of Cassandra Query Language (CQL) statements that you use to manage data in Amazon Keyspaces (for Apache Cassandra) tables. You use DML statements to add, modify, or delete data in a table.
You also use DML statements to query data in a table. (Note that CQL doesn't support joins or subqueries.)
SELECT
Use a SELECT statement to query data.
Syntax
select_statement ::= SELECT [ JSON ] ( select_clause | '*' ) FROM table_name [ WHERE 'where_clause' ] [ ORDER BY 'ordering_clause' ] [ LIMIT (integer | bind_marker) ] [ ALLOW FILTERING ] select_clause ::= selector [ AS identifier ] ( ',' selector [ AS identifier ] ) selector ::= column_name | term | CAST '(' selector AS cql_type ')' | function_name '(' [ selector ( ',' selector )* ] ')' where_clause ::= relation ( AND relation )* relation ::= column_name operator term TOKEN operator ::= '=' | '<' | '>' | '<=' | '>=' | CONTAINS | CONTAINS KEY ordering_clause ::= column_name [ ASC | DESC ] ( ',' column_name [ ASC | DESC ] )*
Examples
SELECT name, id, manager_id FROM "myGSGKeyspace".employees_tbl ; SELECT JSON name, id, manager_id FROM "myGSGKeyspace".employees_tbl ;
For a table that maps JSON-encoded data types to Amazon Keyspaces data types, see JSON encoding of Amazon Keyspaces data types.
Ordering results
The ORDER BY
clause specifies the sort order of the returned results.
It takes as arguments a list of column names along with the sort order for each
column. You can only specify clustering columns in ordering clauses. Non-clustering
columns are not allowed. The sort order options are ASC
for ascending
and DESC
for descending sort order. If the sort order is omitted, the
default ordering of the clustering column is used. For possible sort orders, see
Ordering results in Amazon Keyspaces.
Example
SELECT name, id, division, manager_id FROM "myGSGKeyspace".employees_tbl WHERE id = '012-34-5678' ORDER BY division;
TOKEN
You can apply the TOKEN
function to the PARTITION KEY
column in SELECT
and WHERE
clauses. With the TOKEN
function, Amazon Keyspaces returns rows
based on the mapped token value of the PARTITION_KEY
rather than on the
value of the PARTITION KEY
.
Examples
SELECT TOKEN(id) from
my_table
; SELECT TOKEN(id) frommy_table
WHERE TOKEN(id) > 100 and TOKEN(id) < 10000;
TTL function
You can use the TTL
function with the SELECT
statement to retrieve the
expiration time in seconds that is stored for a column. If no TTL
value is set, the function returns null
.
Example
SELECT TTL (
my_column
) frommy_table
;
For compatibility with established Cassandra driver behavior, tag-based authorization policies are not enforced when you perform operations on system tables by using Cassandra Query Language (CQL) API calls through Cassandra drivers and developer tools. For more information, see Amazon Keyspaces resource access based on tags.
INSERT
Use the INSERT
statement to add a row to a table.
Syntax
insert_statement ::= INSERT INTO table_name ( names_values | json_clause ) [ IF NOT EXISTS ] names_values ::= names VALUES tuple_literal json_clause ::= JSON string [ DEFAULT ( NULL | UNSET ) ] names ::= '(' column_name ( ',' column_name )* ')'
Example
INSERT INTO "myGSGKeyspace".employees_tbl (id, name, project, region, division, role, pay_scale, vacation_hrs, manager_id) VALUES ('012-34-5678','Russ','NightFlight','US','Engineering','IC',3,12.5, '234-56-7890') ;
JSON support
For a table that maps JSON-encoded data types to Amazon Keyspaces data types, see JSON encoding of Amazon Keyspaces data types.
You can use the JSON
keyword to insert a JSON
-encoded
map as a single row. For columns that exist in the table but are omitted in the JSON
insert statement, use DEFAULT UNSET
to preserve the existing values.
Use DEFAULT NULL
to write a NULL value into each row of omitted columns
and overwrite the existing values (standard write charges apply). DEFAULT
NULL
is the default option.
Example
INSERT INTO "myGSGKeyspace".employees_tbl JSON '{"id":"012-34-5678", "name": "Russ", "project": "NightFlight", "region": "US", "division": "Engineering", "role": "IC", "pay_scale": 3, "vacation_hrs": 12.5, "manager_id": "234-56-7890"}';
If the JSON data contains duplicate keys, Amazon Keyspaces stores the last value for the key
(similar to Apache Cassandra). In the following example, where the duplicate key is
id
, the value 234-56-7890
is used.
Example
INSERT INTO "myGSGKeyspace".employees_tbl JSON '{"id":"012-34-5678", "name": "Russ", "project": "NightFlight", "region": "US", "division": "Engineering", "role": "IC", "pay_scale": 3, "vacation_hrs": 12.5, "id": "234-56-7890"}';
UPDATE
Use the UPDATE
statement to modify a row in a table.
Syntax
update_statement ::= UPDATE table_name [ USING update_parameter ( AND update_parameter )* ] SET assignment ( ',' assignment )* WHERE where_clause [ IF ( EXISTS | condition ( AND condition )*) ] update_parameter ::= ( integer | bind_marker ) assignment ::= simple_selection '=' term | column_name '=' column_name ( '+' | '-' ) term | column_name '=' list_literal '+' column_name simple_selection ::= column_name | column_name '[' term ']' | column_name '.' `field_name condition ::= simple_selection operator term
Example
UPDATE "myGSGKeyspace".employees_tbl SET pay_scale = 5 WHERE id = '567-89-0123' AND division = 'Marketing' ;
To increment a counter
, use the following syntax. For more
information, see Counters.
UPDATE ActiveUsers SET counter = counter + 1 WHERE user = A70FE1C0-5408-4AE3-BE34-8733E5K09F14 AND action = 'click';
DELETE
Use the DELETE
statement to remove a row from a table.
Syntax
delete_statement ::= DELETE [ simple_selection ( ',' simple_selection ) ] FROM table_name WHERE where_clause [ IF ( EXISTS | condition ( AND condition )*) ] simple_selection ::= column_name | column_name '[' term ']' | column_name '.' `field_name condition ::= simple_selection operator term
Where:
-
table_name
is the table that contains the row you want to delete.
Example
DELETE manager_id FROM "myGSGKeyspace".employees_tbl WHERE id='789-01-2345' AND division='Executive' ;