# Create, read, update, and delete data (CRUD) using CQL in Amazon Keyspaces
In this step of the tutorial, you'll learn how to insert, read, update, and delete data in an Amazon Keyspaces table using CQL data manipulation language (DML) statements. In Amazon Keyspaces, you can only create DML statements in CQL language. In this tutorial, you'll practice running DML statements using the `cqlsh-expansion` with [AWS CloudShell](using-aws-with-cloudshell.md) in the AWS Management Console.
+ **Inserting data** – This section covers inserting single and multiple records into a table using the `INSERT` statement. You'll learn how to upload data from a CSV file and verify successful inserts using `SELECT` queries.
+ **Reading data** – Here, you'll explore different variations of the `SELECT` statement to retrieve data from a table. Topics include selecting all data, selecting specific columns, filtering rows based on conditions using the `WHERE` clause, and understanding simple and compound conditions.
+ **Updating data** – In this section, you'll learn how to modify existing data in a table using the `UPDATE` statement. You'll practice updating single and multiple columns while understanding restrictions around updating primary key columns.
+ **Deleting data** – The final section covers deleting data from a table using the `DELETE`statement. You'll learn how to delete specific cells, entire rows, and the implications of deleting data versus deleting the entire table or keyspace.
Throughout the tutorial, you'll find examples, tips, and opportunities to practice writing your own CQL queries for various scenarios.
**Topics**
+ [Inserting and loading data into an Amazon Keyspaces table](getting-started.dml.create.md)
+ [Read data from a table using the CQL `SELECT` statement in Amazon Keyspaces](getting-started.dml.read.md)
+ [Update data in an Amazon Keyspaces table using CQL](getting-started.dml.update.md)
+ [Delete data from a table using the CQL `DELETE` statement](getting-started.dml.delete.md)
# Inserting and loading data into an Amazon Keyspaces table
To create data in your `book_awards` table, use the `INSERT` statement to add a single row.
1. Open AWS CloudShell and connect to Amazon Keyspaces using the following command. Make sure to update *us-east-1* with your own Region.
```
cqlsh-expansion cassandra.us-east-1.amazonaws.com 9142 --ssl
```
The output of that command should look like this.
```
Connected to Amazon Keyspaces at cassandra.us-east-1.amazonaws.com:9142
[cqlsh 6.1.0 | Cassandra 3.11.2 | CQL spec 3.4.4 | Native protocol v4]
Use HELP for help.
cqlsh current consistency level is ONE.
```
1. Before you can write data to your Amazon Keyspaces table using cqlsh, you must set the write consistency for the current cqlsh session to `LOCAL_QUORUM`. For more information about supported consistency levels, see [Write consistency levels](consistency.md#WriteConsistency). Note that this step is not required if you are using the CQL editor in the AWS Management Console.
```
CONSISTENCY LOCAL_QUORUM;
```
1. To insert a single record, run the following command in the CQL editor.
```
INSERT INTO catalog.book_awards (award, year, category, rank, author, book_title, publisher)
VALUES ('Wolf', 2023, 'Fiction',3,'Shirley Rodriguez','Mountain', 'AnyPublisher') ;
```
1. Verify that the data was correctly added to your table by running the following command.
```
SELECT * FROM catalog.book_awards;
```
The output of the statement should look like this.
```
year | award | category | rank | author | book_title | publisher
------+-------+----------+------+-------------------+------------+--------------
2023 | Wolf | Fiction | 3 | Shirley Rodriguez | Mountain | AnyPublisher
(1 rows)
```
**To insert multiple records from a file using cqlsh**
1. Download the sample CSV file (`keyspaces_sample_table.csv`) contained in the archive file [samplemigration.zip](samples/samplemigration.zip). Unzip the archive and take note of the path to `keyspaces_sample_table.csv`.
![\[Screenshot of a CSV file showing the output of the table after importing the csv file.\]](http://docs.aws.amazon.com/keyspaces/latest/devguide/images/keyspaces-awards.png)
1. Open AWS CloudShell in the AWS Management Console and connect to Amazon Keyspaces using the following command. Make sure to update *us-east-1* with your own Region.
```
cqlsh-expansion cassandra.us-east-1.amazonaws.com 9142 --ssl
```
1. At the `cqlsh` prompt (`cqlsh>`), specify a keyspace.
```
USE catalog ;
```
1. Set write consistency to `LOCAL_QUORUM`. For more information about supported consistency levels, see [Write consistency levels](consistency.md#WriteConsistency).
```
CONSISTENCY LOCAL_QUORUM;
```
1. In the AWS CloudShell choose **Actions** on the top right side of the screen and then choose **Upload file** to upload the csv file downloaded earlier. Take note of the path to the file.
1. At the keyspace prompt (`cqlsh:catalog>`), run the following statement.
```
COPY book_awards (award, year, category, rank, author, book_title, publisher) FROM '/home/cloudshell-user/keyspaces_sample_table.csv' WITH header=TRUE ;
```
The output of the statement should look similar to this.
```
cqlsh:catalog> COPY book_awards (award, year, category, rank, author, book_title, publisher) FROM '/home/cloudshell-user/keyspaces_sample_table.csv' WITH delimiter=',' AND header=TRUE ;
cqlsh current consistency level is LOCAL_QUORUM.
Reading options from /home/cloudshell-user/.cassandra/cqlshrc:[copy]: {'numprocesses': '16', 'maxattempts': '1000'}
Reading options from /home/cloudshell-user/.cassandra/cqlshrc:[copy-from]: {'ingestrate': '1500', 'maxparseerrors': '1000', 'maxinserterrors': '-1', 'maxbatchsize': '10', 'minbatchsize': '1', 'chunksize': '30'}
Reading options from the command line: {'delimiter': ',', 'header': 'TRUE'}
Using 16 child processes
Starting copy of catalog.book_awards with columns [award, year, category, rank, author, book_title, publisher].
OSError: handle is closed 0 rows/s; Avg. rate: 0 rows/s
Processed: 9 rows; Rate: 0 rows/s; Avg. rate: 0 rows/s
9 rows imported from 1 files in 0 day, 0 hour, 0 minute, and 26.706 seconds (0 skipped).
```
1. Verify that the data was correctly added to your table by running the following query.
```
SELECT * FROM book_awards ;
```
You should see the following output.
```
year | award | category | rank | author | book_title | publisher
------+------------------+-------------+------+--------------------+-----------------------+---------------
2020 | Wolf | Non-Fiction | 1 | Wang Xiulan | History of Ideas | Example Books
2020 | Wolf | Non-Fiction | 2 | Ana Carolina Silva | Science Today | SomePublisher
2020 | Wolf | Non-Fiction | 3 | Shirley Rodriguez | The Future of Sea Ice | AnyPublisher
2020 | Kwesi Manu Prize | Fiction | 1 | Akua Mansa | Where did you go? | SomePublisher
2020 | Kwesi Manu Prize | Fiction | 2 | John Stiles | Yesterday | Example Books
2020 | Kwesi Manu Prize | Fiction | 3 | Nikki Wolf | Moving to the Chateau | AnyPublisher
2020 | Richard Roe | Fiction | 1 | Alejandro Rosalez | Long Summer | SomePublisher
2020 | Richard Roe | Fiction | 2 | Arnav Desai | The Key | Example Books
2020 | Richard Roe | Fiction | 3 | Mateo Jackson | Inside the Whale | AnyPublisher
(9 rows)
```
To learn more about using `cqlsh COPY` to upload data from csv files to an Amazon Keyspaces table, see [Tutorial: Loading data into Amazon Keyspaces using cqlsh](bulk-upload.md).
# Read data from a table using the CQL `SELECT` statement in Amazon Keyspaces
In the [Inserting and loading data into an Amazon Keyspaces table](getting-started.dml.create.md) section, you used the `SELECT` statement to verify that you had successfully added data to your table. In this section, you refine your use of `SELECT` to display specific columns, and only rows that meet specific criteria.
The general form of the `SELECT` statement is as follows.
```
SELECT column_list FROM table_name [WHERE condition [ALLOW FILTERING]] ;
```
**Topics**
+ [Select all the data in your table](#getting-started.dml.read.all)
+ [Select a subset of columns](#getting-started.dml.read.columns)
+ [Select a subset of rows](#getting-started.dml.read.rows)
## Select all the data in your table
The simplest form of the `SELECT` statement returns all the data in your table.
**Important**
In a production environment, it's typically not a best practice to run this command, because it returns all the data in your table.
**To select all your table's data**
1. Open AWS CloudShell and connect to Amazon Keyspaces using the following command. Make sure to update *us-east-1* with your own Region.
```
cqlsh-expansion cassandra.us-east-1.amazonaws.com 9142 --ssl
```
1. Run the following query.
```
SELECT * FROM catalog.book_awards ;
```
Using the wild-card character ( `*` ) for the `column_list` selects all columns. The output of the statement looks like the following example.
```
year | award | category | rank | author | book_title | publisher
------+------------------+-------------+------+--------------------+-----------------------+---------------
2020 | Wolf | Non-Fiction | 1 | Wang Xiulan | History of Ideas | AnyPublisher
2020 | Wolf | Non-Fiction | 2 | Ana Carolina Silva | Science Today | SomePublisher
2020 | Wolf | Non-Fiction | 3 | Shirley Rodriguez | The Future of Sea Ice | AnyPublisher
2020 | Kwesi Manu Prize | Fiction | 1 | Akua Mansa | Where did you go? | SomePublisher
2020 | Kwesi Manu Prize | Fiction | 2 | John Stiles | Yesterday | Example Books
2020 | Kwesi Manu Prize | Fiction | 3 | Nikki Wolf | Moving to the Chateau | AnyPublisher
2020 | Richard Roe | Fiction | 1 | Alejandro Rosalez | Long Summer | SomePublisher
2020 | Richard Roe | Fiction | 2 | Arnav Desai | The Key | Example Books
2020 | Richard Roe | Fiction | 3 | Mateo Jackson | Inside the Whale | AnyPublisher
```
## Select a subset of columns
**To query for a subset of columns**
1. Open AWS CloudShell and connect to Amazon Keyspaces using the following command. Make sure to update *us-east-1* with your own Region.
```
cqlsh-expansion cassandra.us-east-1.amazonaws.com 9142 --ssl
```
1. To retrieve only the `award`, `category`, and `year` columns, run the following query.
```
SELECT award, category, year FROM catalog.book_awards ;
```
The output contains only the specified columns in the order listed in the `SELECT` statement.
```
award | category | year
------------------+-------------+------
Wolf | Non-Fiction | 2020
Wolf | Non-Fiction | 2020
Wolf | Non-Fiction | 2020
Kwesi Manu Prize | Fiction | 2020
Kwesi Manu Prize | Fiction | 2020
Kwesi Manu Prize | Fiction | 2020
Richard Roe | Fiction | 2020
Richard Roe | Fiction | 2020
Richard Roe | Fiction | 2020
```
## Select a subset of rows
When querying a large dataset, you might only want records that meet certain criteria. To do this, you can append a `WHERE` clause to the end of our `SELECT` statement.
**To query for a subset of rows**
1. Open AWS CloudShell and connect to Amazon Keyspaces using the following command. Make sure to update *us-east-1* with your own Region.
```
cqlsh-expansion cassandra.us-east-1.amazonaws.com 9142 --ssl
```
1. To retrieve only the records for the awards of a given year, run the following query.
```
SELECT * FROM catalog.book_awards WHERE year=2020 AND award='Wolf' ;
```
The preceding `SELECT` statement returns the following output.
```
year | award | category | rank | author | book_title | publisher
------+-------+-------------+------+--------------------+-----------------------+---------------
2020 | Wolf | Non-Fiction | 1 | Wang Xiulan | History of Ideas | AnyPublisher
2020 | Wolf | Non-Fiction | 2 | Ana Carolina Silva | Science Today | SomePublisher
2020 | Wolf | Non-Fiction | 3 | Shirley Rodriguez | The Future of Sea Ice | AnyPublisher
```
### Understanding the `WHERE` clause
The `WHERE` clause is used to filter the data and return only the data that meets the specified criteria. The specified criteria can be a simple condition or a compound condition.
**How to use conditions in a `WHERE` clause**
+ A simple condition – A single column.
```
WHERE column_name=value
```
You can use a simple condition in a `WHERE` clause if any of the following conditions are met:
+ The column is the only partition key column of the table.
+ You add `ALLOW FILTERING` after the condition in the `WHERE` clause.
Be aware that using `ALLOW FILTERING` can result in inconsistent performance, especially with large, and multi-partitioned tables.
+ A compound condition – Multiple simple conditions connected by `AND`.
```
WHERE column_name1=value1 AND column_name2=value2 AND column_name3=value3...
```
You can use compound conditions in a `WHERE` clause if any of the following conditions are met:
+ The columns you can use in the `WHERE` clause need to include either all or a subset of the columns in the table's partition key. If you want to use only a subset of the columns in the `WHERE` clause, you must include a contiguous set of partition key columns from left to right, beginning with the partition key's leading column. For example, if the partition key columns are `year`, `month`, and `award` then you can use the following columns in the `WHERE` clause:
+ `year`
+ `year` AND `month`
+ `year` AND `month` AND `award`
+ You add `ALLOW FILTERING` after the compound condition in the `WHERE` clause, as in the following example.
```
SELECT * FROM my_table WHERE col1=5 AND col2='Bob' ALLOW FILTERING ;
```
Be aware that using `ALLOW FILTERING` can result in inconsistent performance, especially with large, and multi-partitioned tables.
### Try it
Create your own CQL queries to find the following from your `book_awards` table:
+ Find the winners of the 2020 Wolf awards and display the book titles and authors, ordered by rank.
+ Show the first prize winners for all awards in 2020 and display the book titles and award names.
# Update data in an Amazon Keyspaces table using CQL
To update the data in your `book_awards` table, use the `UPDATE` statement.
The general form of the `UPDATE` statement is as follows.
```
UPDATE table_name SET column_name=new_value WHERE primary_key=value ;
```
**Tip**
You can update multiple columns by using a comma-separated list of `column_names` and values, as in the following example.
```
UPDATE my_table SET col1='new_value_1', col2='new_value2' WHERE col3='1' ;
```
If the primary key is composed of multiple columns, all primary key columns and their values must be included in the `WHERE` clause.
You cannot update any column in the primary key because that would change the primary key for the record.
**To update a single cell**
Using your `book_awards` table, change the name of a publisher the for winner of the non-fiction Wolf awards in 2020.
```
UPDATE book_awards SET publisher='new Books' WHERE year = 2020 AND award='Wolf' AND category='Non-Fiction' AND rank=1;
```
Verify that the publisher is now `new Books`.
```
SELECT * FROM book_awards WHERE year = 2020 AND award='Wolf' AND category='Non-Fiction' AND rank=1;
```
The statement should return the following output.
```
year | award | category | rank | author | book_title | publisher
------+-------+-------------+------+-------------+------------------+-----------
2020 | Wolf | Non-Fiction | 1 | Wang Xiulan | History of Ideas | new Books
```
## Try it
**Advanced:** The winner of the 2020 fiction "Kwezi Manu Prize" has changed their name. Update this record to change the name to `'Akua Mansa-House'`.
# Delete data from a table using the CQL `DELETE` statement
To delete data in your `book_awards` table, use the `DELETE` statement.
You can delete data from a row or from a partition. Be careful when deleting data, because deletions are irreversible.
Deleting one or all rows from a table doesn't delete the table. Thus you can repopulate it with data. Deleting a table deletes the table and all data in it. To use the table again, you must re-create it and add data to it. Deleting a keyspace deletes the keyspace and all tables within it. To use the keyspace and tables, you must re-create them, and then populate them with data. You can use Amazon Keyspaces Point-in-time (PITR) recovery to help restore deleted tables, to learn more see [Backup and restore data with point-in-time recovery for Amazon Keyspaces](PointInTimeRecovery.md) . To learn how to restore a deleted table with PITR enabled, see [Restore a deleted table using Amazon Keyspaces PITR](restoredeleted.md).
## Delete cells
Deleting a column from a row removes the data from the specified cell. When you display that column using a `SELECT` statement, the data is displayed as *null*, though a null value is not stored in that location.
The general syntax to delete one or more specific columns is as follows.
```
DELETE column_name1[, column_name2...] FROM table_name WHERE condition ;
```
In your `book_awards` table, you can see that the title of the book that won the first price of the 2020 "Richard Roe" price is "Long Summer". Imagine that this title has been recalled, and you need to delete the data from this cell.
**To delete a specific cell**
1. Open AWS CloudShell and connect to Amazon Keyspaces using the following command. Make sure to update *us-east-1* with your own Region.
```
cqlsh-expansion cassandra.us-east-1.amazonaws.com 9142 --ssl
```
1. Run the following `DELETE` query.
```
DELETE book_title FROM catalog.book_awards WHERE year=2020 AND award='Richard Roe' AND category='Fiction' AND rank=1;
```
1. Verify that the delete request was made as expected.
```
SELECT * FROM catalog.book_awards WHERE year=2020 AND award='Richard Roe' AND category='Fiction' AND rank=1;
```
The output of this statement looks like this.
```
year | award | category | rank | author | book_title | publisher
------+-------------+----------+------+-------------------+------------+---------------
2020 | Richard Roe | Fiction | 1 | Alejandro Rosalez | null | SomePublisher
```
## Delete rows
There might be a time when you need to delete an entire row, for example to meet a data deletion request. The general syntax for deleting a row is as follows.
```
DELETE FROM table_name WHERE condition ;
```
**To delete a row**
1. Open AWS CloudShell and connect to Amazon Keyspaces using the following command. Make sure to update *us-east-1* with your own Region.
```
cqlsh-expansion cassandra.us-east-1.amazonaws.com 9142 --ssl
```
1. Run the following `DELETE` query.
```
DELETE FROM catalog.book_awards WHERE year=2020 AND award='Richard Roe' AND category='Fiction' AND rank=1;
```
1. Verify that the delete was made as expected.
```
SELECT * FROM catalog.book_awards WHERE year=2020 AND award='Richard Roe' AND category='Fiction' AND rank=1;
```
The output of this statement looks like this after the row has been deleted.
```
year | award | category | rank | author | book_title | publisher
------+-------+----------+------+--------+------------+-----------
(0 rows)
```
You can delete expired data automatically from your table using Amazon Keyspaces Time to Live, for more information, see [Expire data with Time to Live (TTL) for Amazon Keyspaces (for Apache Cassandra)](TTL.md).