YQL: Getting started

Introduction

YQL is a YDB query language, a dialect of SQL. Specifics of its syntax let you use it when executing queries on clusters.

For more information about the YQL syntax, see the YQL reference.

The examples below demonstrate how to get started with YQL and assume that the steps described will be completed sequentially: the queries in the Working with data section access data in the tables created in the Working with a data schema section. Follow the steps one by one so that the examples copied through the clipboard are executed successfully.

The YDB YQL basic interface accepts a script that may consist of multiple commands and not a single command as input.

YQL query execution tools

In YDB, you can make YQL queries to a database using:

Built-in YDB web interface

To execute YQL queries and scripts on self-hosted YDB databases, you can use the built-in YDB web interface. For a local deployment using Docker with the default parameters, it is available at http://localhost:8765.

Select Databases in the menu on the left, click on the database in the list, and switch to the Query tab:

embedded_query

To execute a YQL script, click Run Script.

YDB CLI

To enable scripts execution using the YDB CLI, ensure you have completed the following prerequisites:

Save the text of the scripts below to a file. Name it script.yql to be able to run the statements given in the examples by simply copying them through the clipboard. Next, run ydb yql indicating the use of the db1 profile and reading the script from the script.yql file:

  1. ydb --profile db1 yql -f script.yql

YQL query language - 图2

Working with a data schema

Creating tables

A table with the specified columns is created using the YQL CREATE TABLE statement. Make sure the primary key is defined in the table. Column data types are described in YQL data types.

Currently, YDB doesn’t support the NOT NULL constraint, all columns allow null values, including the primary key columns. In addition, YDB doesn’t support the FOREIGN KEY constraint.

Create series directory tables named series, seasons, and episodes by running the following script:

  1. CREATE TABLE series (
  2.     series_id Uint64,
  3.     title Utf8,
  4.     series_info Utf8,
  5.     release_date Date,
  6.     PRIMARY KEY (series_id)
  7. );
  9. CREATE TABLE seasons (
  10.     series_id Uint64,
  11.     season_id Uint64,
  12.     title Utf8,
  13.     first_aired Date,
  14.     last_aired Date,
  15.     PRIMARY KEY (series_id, season_id)
  16. );
  18. CREATE TABLE episodes (
  19.     series_id Uint64,
  20.     season_id Uint64,
  21.     episode_id Uint64,
  22.     title Utf8,
  23.     air_date Date,
  24.     PRIMARY KEY (series_id, season_id, episode_id)
  25. );

YQL query language - 图3

For a description of everything you can do when working with tables, review the relevant sections of the YQL documentation:

To execute a script via the YDB CLI, follow the instructions provided under Executing YQL scripts in the YDB CLI above.

Getting a list of existing DB tables

Check that the tables are actually created in the database.

The built-in YDB web interface displays the list of tables as a hierarchy on the left-hand side of the database page. On the Info tab, you can see detailed information about the object selected in the hierarchy.

To get a list of existing DB tables via the YDB CLI, make sure that the prerequisites under Executing YQL scripts in the YDB CLI above are complete and run the scheme ls statement:

  1. ydb --profile db1 scheme ls

YQL query language - 图4

Operations with data

Commands for running YQL queries and scripts in the YDB CLI and the web interface run in Autocommit mode meaning that a transaction is committed automatically after it is completed.

UPSERT: Adding data

The most efficient way to add data to YDB is through the UPSERT statement. It inserts new data by primary keys regardless of whether data by these keys previously existed in the table. As a result, unlike regular INSERT and UPDATE, it does not require a data pre-fetch on the server to verify that a key is unique. When working with YDB, always consider UPSERT as the main way to add data and only use other statements when absolutely necessary.

All statements that write data to YDB support working with both subqueries and multiple entries passed directly in a query.

Let’s add data to the previously created tables:

  1. UPSERT INTO series (series_id, title, release_date, series_info)
  2. VALUES
  3.     (
  4.         1,
  5.         "IT Crowd",
  6.         Date("2006-02-03"),
  7.         "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."),
  8.     (
  9.         2,
  10.         "Silicon Valley",
  11.         Date("2014-04-06"),
  12.         "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."
  13.     )
  14.     ;
  16. UPSERT INTO seasons (series_id, season_id, title, first_aired, last_aired)
  17. VALUES
  18.     (1, 1, "Season 1", Date("2006-02-03"), Date("2006-03-03")),
  19.     (1, 2, "Season 2", Date("2007-08-24"), Date("2007-09-28")),
  20.     (2, 1, "Season 1", Date("2014-04-06"), Date("2014-06-01")),
  21.     (2, 2, "Season 2", Date("2015-04-12"), Date("2015-06-14"))
  22. ;
  24. UPSERT INTO episodes (series_id, season_id, episode_id, title, air_date)
  25. VALUES
  26.     (1, 1, 1, "Yesterday's Jam", Date("2006-02-03")),
  27.     (1, 1, 2, "Calamity Jen", Date("2006-02-03")),
  28.     (2, 1, 1, "Minimum Viable Product", Date("2014-04-06")),
  29.     (2, 1, 2, "The Cap Table", Date("2014-04-13"))
  30. ;

YQL query language - 图5

To execute a script via the YDB CLI, follow the instructions provided under Executing YQL scripts in the YDB CLI above.

To learn more about commands for writing data, see the YQL reference:

  • INSERT: Add records.
  • REPLACE: Add/update records.
  • UPDATE: Update specified fields.
  • UPSERT: Add records/modify specified fields.

SELECT : Data retrieval

Make a select of the data added in the previous step:

  1. SELECT 
  2.     series_id,
  3.     title AS series_title,
  4.     release_date
  5. FROM series;

YQL query language - 图6

or

  1. SELECT * FROM episodes;

YQL query language - 图7

If there are several SELECT statements in the YQL script, its execution will return several samples, each of which can be accessed separately. Run the above SELECT statements as a single script.

To execute a script via the YDB CLI, follow the instructions provided under Executing YQL scripts in the YDB CLI above.

To learn more about the commands for selecting data, see the YQL reference:

Parameterized queries

Transactional applications working with a database are characterized by the execution of multiple similar queries that only differ in parameters. Like most databases, YDB will work more efficiently if you define variable parameters and their types and then initiate the execution of a query by passing the parameter values separately from its text.

To define parameters in the text of a YQL query, use the DECLARE statement.

Methods for executing parameterized queries in the YDB SDK are described in the Test case section under Parameterized queries for the appropriate programming language.

When debugging a parameterized query in the YDB SDK, you can test it by calling the YDB CLI, copying the full text of the query without any edits, and setting parameter values.

Save the parameterized query script in a text file namedscript.yql:

  1. DECLARE $seriesId AS Uint64;
  2. DECLARE $seasonId AS Uint64;
  4. SELECT sa.title AS season_title, sr.title AS series_title
  5. FROM   seasons AS sa
  6. INNER JOIN series AS sr ON sa.series_id = sr.series_id
  7. WHERE sa.series_id = $seriesId AND sa.season_id = $seasonId;

YQL query language - 图8

To run a parameterized select query, make sure to complete the prerequisites under Executing YQL scripts in the YDB CLI above and run:

  1. ydb --profile db1 yql -f script.yql -p '$seriesId=1' -p '$seasonId=1'

YQL query language - 图9

For a full description of the ways to pass parameters, see the YDB CLI reference.

YQL tutorial

You can learn more about YQL use cases by completing tasks from the YQL tutorial.

Next step

Go to YDB SDK - Getting started to proceed with the ‘Getting started’ scenario.