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 execute scripts using the YDB CLI, first do the following:

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:

ydb --profile db1 yql -f script.yql

Working with a data schema

Creating tables

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

All columns are optional by default and can contain NULL. You can specify a NOT NULL limit for the columns that are part of the primary key. YDB does not support FOREIGN KEY limits.

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

CREATE TABLE series (
    series_id Uint64 NOT NULL,
    title Utf8,
    series_info Utf8,
    release_date Date,
    PRIMARY KEY (series_id)
);

CREATE TABLE seasons (
    series_id Uint64,
    season_id Uint64,
    title Utf8,
    first_aired Date,
    last_aired Date,
    PRIMARY KEY (series_id, season_id)
);

CREATE TABLE episodes (
    series_id Uint64,
    season_id Uint64,
    episode_id Uint64,
    title Utf8,
    air_date Date,
    PRIMARY KEY (series_id, season_id, episode_id)
);

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

To execute the script via the YDB CLI, follow the instructions given under Executing YQL queries in the YDB CLI in this article.

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 are complete and run the scheme ls command:

ydb --profile db1 scheme ls

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 command. It inserts new data by primary keys regardless of whether data by these keys previously existed in the table. As a result, unlike regular INSERTand UPDATE, it does not require a data pre-fetch from the server to verify that a key is unique before it runs. When working with YDB, always consider UPSERT as the main way to add data and only use other statements when absolutely necessary.

All commands that write data to YDB support working with both samples and multiple logs passed directly in a query.

Let's add data to the previously created tables:

UPSERT INTO series (series_id, title, release_date, series_info)
VALUES
    (
        1,
        "IT Crowd",
        Date("2006-02-03"),
        "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",
        Date("2014-04-06"),
        "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."
    )
    ;

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

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

To execute the script via the YDB CLI, follow the instructions given under Executing YQL queries in the YDB CLI in this article.

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

SELECT : Data retrieval

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

SELECT
    series_id,
    title AS series_title,
    release_date
FROM series;

or

SELECT * FROM episodes;

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 the script via the YDB CLI, follow the instructions given under Executing YQL queries in the YDB CLI in this article.

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 updateable 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.

A description of the execution methods for the parametrized YDB SDK queries is available in the Test example section under Parametrized queries for the desired 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 named script.yql:

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

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;

To make a parameterized select query, make sure the prerequisites of the Executing YQL scripts in the YDB CLI section of this article are met, then run:

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

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.

Learn more about YDB

Proceed to the YDB SDK - Getting started article to learn more about YDB.