Reading and writing data

Queries can be sent to YDB in the following ways:

• From the management console.
• From an application written using the YDB SDK for Java, Python, or Go.
• Using the API compatible with Amazon DynamoDB (for document tables).

In this section, we are going to use the management console to execute queries.

Prerequisites

To run queries, you'll need to create a database and table in it.

SQL queries in the management console

To send an SQL query to the database from the management console:

1. In the management console, select the folder with the desired DB.
2. In the list of services, select Yandex Database.
3. Select the database from the list.
4. Click SQL query and enter the query text. When making queries, you can use the following templates:
   • To use one of the standard templates, select it from the drop-down list to the right of the SQL query button.
   • To insert data from a specific table into the template, click in the line with the desired table and select the template.
5. Click Run.

Insert and update data

To insert data into YDB, use REPLACE, UPSERT, and INSERT statements.

When executing REPLACE and UPSERT statements, a blind write is performed. With an INSERT statement, data is read before writing. This ensures that the primary key is unique.

We recommend using REPLACE and UPSERT statements to write and modify data.

A single REPLACE, UPSERT, or INSERT query can insert multiple rows into a table.

REPLACE INTO episodes (series_id, season_id, episode_id, title) VALUES (1, 1, 1, "Yesterday's Jam");
REPLACE INTO episodes (series_id, season_id, episode_id, title) VALUES (1, 1, 2, "Calamity Jen");

REPLACE

Once the series, seasons, and episodes tables are created, you can insert data into them using REPLACE. Basic syntax:

REPLACE INTO table_name (column_list) VALUES (list_of_added_values);

Use REPLACE statements to add a new row or change an existing row based on the specified value of the primary key. If a row with the specified primary key value does not exist, it is created. If the row already exists, the column values of the existing row are replaced with the new values. The values of columns not involved in the operation are set to their default values. This is what makes it different from the UPSERT statement.

Data added using the following code sample will be used later in this section.

REPLACE INTO series (series_id, title, release_date, series_info)
VALUES
    (
        1,
        "IT Crowd",
        CAST(Date("2006-02-03") AS Uint64),
        "The IT Crowd is a British sitcom produced by Channel 4, written by Graham Linehan, produced by Ash Atalla and starring Chris O'Dowd, Richard Ayoade, Katherine Parkinson, and Matt Berry."
    ),
    (
        2,
        "Silicon Valley",
        CAST(Date("2014-04-06") AS Uint64),
        "Silicon Valley is an American comedy television series created by Mike Judge, John Altschuler and Dave Krinsky. The series focuses on five young men who founded a startup company in Silicon Valley."
    )
    ;

REPLACE INTO seasons (series_id, season_id, title, first_aired, last_aired)
VALUES
    (1, 1, "Season 1", CAST(Date("2006-02-03") AS Uint64), CAST(Date("2006-03-03") AS Uint64)),
    (1, 2, "Season 2", CAST(Date("2007-08-24") AS Uint64), CAST(Date("2007-09-28") AS Uint64)),
    (2, 1, "Season 1", CAST(Date("2014-04-06") AS Uint64), CAST(Date("2014-06-01") AS Uint64)),
    (2, 2, "Season 2", CAST(Date("2015-04-12") AS Uint64), CAST(Date("2015-06-14") AS Uint64))
;

REPLACE INTO episodes (series_id, season_id, episode_id, title, air_date)
VALUES
    (1, 1, 1, "Yesterday's Jam", CAST(Date("2006-02-03") AS Uint64)),
    (1, 1, 2, "Calamity Jen", CAST(Date("2006-02-03") AS Uint64)),
    (2, 1, 1, "Minimum Viable Product", CAST(Date("2014-04-06") AS Uint64)),
    (2, 1, 2, "The Cap Table", CAST(Date("2014-04-13") AS Uint64))
;

UPSERT

Use UPSERT statements to add a new row or change an existing row based on the specified value of the primary key. If a row with the specified primary key value does not exist, it is created. If the row already exists, the column values of the existing row are replaced with the new values. The values of columns not involved in the operation are not changed. This is what makes it different from the REPLACE statement.

The code below inserts one row of data into the episodes table.

UPSERT INTO episodes
(
    series_id,
    season_id,
    episode_id,
    title,
    air_date
)
VALUES
(
    2,
    1,
    3,
    "Test Episode",
    CAST(Date("2018-08-27") AS Uint64)
)
;

INSERT

Use INSERT statements to insert one or more rows. If you try to insert a row into a table with an existing primary key value, YDB returns the error message Transaction rolled back due to constraint violation: insert_pk..

The code below inserts one row of data into the episodes table.

INSERT INTO episodes
(
    series_id,
    season_id,
    episode_id,
    title,
    air_date
)
VALUES
(
    2,
    5,
    21,
    "Test 21",
    CAST(Date("2018-08-27") AS Uint64)
)
;

UPDATE

UPDATE statements change the column value for table rows filtered by a WHERE predicate. Basic syntax:

UPDATE table_name SET column1_name=new_column1_value, ...,column_nameN=new_columnN_value WHERE row_filtering_criteria;

UPDATE statements can't change primary key values. Enter and execute the following UPDATE statement to change the value of the title column from "Test Episode" to "Test Episode Updated" for the episode with series_id = 2, season_id = 1, and episode_id = 3.

UPDATE episodes
SET title="Test Episode Updated"
WHERE
    series_id = 2
    AND season_id = 1
    AND episode_id = 3
;

DELETE

The DELETE statement deletes table rows filtered by the WHERE clause. The code below removes an episode with series_id = 2, season_id = 5, and episode_id = 21 from the episodes table.

DELETE
FROM episodes
WHERE
    series_id = 2
    AND season_id = 5
    AND episode_id = 21
;

Query data using SELECT

Use SELECT statements to read data from a table.

To query data from the series table, execute the code shown below.

SELECT
    series_id,
    title AS series_title,
    CAST (release_date AS Date) AS release_date
FROM series;

You can use an asterisk to select all the columns in a table. To obtain the values of all columns from the series table, execute the code shown below.

SELECT
    *
FROM series;

Make a parameterized query

Use parameterized queries to improve performance by reducing the frequency of compiling and recompiling your queries.

Example

DECLARE $seriesId AS Uint64;
DECLARE $seasonId AS Uint64;

$seriesId = 1;
$seasonId = 2;

SELECT sa.title AS season_title, sr.title AS series_title
FROM seasons AS sa
INNER JOIN series AS sr
ON sa.series_id = sr.series_id
WHERE sa.series_id = $seriesId AND sa.season_id = $seasonId;