Join us at ScyllaDB University LIVE, instructor-led training sessions | March 19
Register now

Table and Basic Concepts

4 min to complete

Table and Basic Concepts

A Partition is a collection of sorted rows, identified by a unique primary key. More on primary keys later on in this lesson. Each partition is stored on a node and replicated across nodes.

A Row in ScyllaDB is a unit that stores data. Each row has a primary key that uniquely identifies it in a Table. Each row stores data as pairs of column names and values. In case a Clustering Key is defined, the rows in the partition will be sorted accordingly. More on that later in the lesson. In the following example, since no  Clustering Key is defined, each partition contains one row:

A Table is how ScyllaDB stores data and can be thought of as a set of rows and columns.

 

To create a table, we define the table columns and which of those columns compose the primary key, as well as general settings for the table. Going back to our pet clinic example, create a table that stores the log for each pet’s heart rate (make sure you are in the same active cqlsh prompt you created before):

CREATE TABLE heartrate_v1 (
   pet_chip_id uuid,
   time timestamp,
   heart_rate int,
   PRIMARY KEY (pet_chip_id)
);

int and text are some commonly used data types, more on that later.
Next, insert some data into the newly created table:

INSERT INTO heartrate_v1(pet_chip_id, time, heart_rate) VALUES (123e4567-e89b-12d3-a456-426655440b23, '2019-03-04 07:01:00', 100);

Now read that data:

SELECT * from heartrate_v1 WHERE pet_chip_id = 123e4567-e89b-12d3-a456-426655440b23;

This query finds the pet with chip ID 123e4567-e89b-12d3-a456-426655440b23 and displays that pet’s heartrate.

But, there is a problem here. The way the table is defined, each pet can only have one heart rate recorded. When we write the next value for the same pet_chip_id, with a different time, it will actually overwrite the first value.
All inserts in ScyllaDB (and Cassandra) are actually upserts (insert/update). There can be only one set of values for each unique primary key. If we insert again with the same primary key, the values will be updated.

Try to insert another heart rate value with a different time, for the same pet:

INSERT INTO heartrate_v1(pet_chip_id, time, heart_rate) VALUES (123e4567-e89b-12d3-a456-426655440b23, '2019-03-04 07:02:30', 130);

And now read the data:

SELECT * from heartrate_v1 WHERE pet_chip_id = 123e4567-e89b-12d3-a456-426655440b23;

You can see that the operation was actually an update and that the data was not appended.
Next, we will see how we can solve this problem.

fa-angle-up