YQL: Getting started

    For more information about the YQL syntax, see the .

    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 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:

    To execute YQL queries and scripts on self-hosted YDB databases, you can use the built-in YDB web interface. For a local deployment 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:

    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 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:

    YQL query language - 图2

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

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

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

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

    • : Create a table and define its initial properties.
    • ALTER TABLE: Modify a table’s column structure and properties.
    • : Delete a table.

    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 :

    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.

    The most efficient way to add data to YDB is through the 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.         "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."),
    7.     (
    8.         2,
    9.         "Silicon Valley",
    10.         Date("2014-04-06"),
    12.     )
    13.     ;
    15. UPSERT INTO seasons (series_id, season_id, title, first_aired, last_aired)
    16. VALUES
    17.     (1, 1, "Season 1", Date("2006-02-03"), Date("2006-03-03")),
    18.     (1, 2, "Season 2", Date("2007-08-24"), Date("2007-09-28")),
    19.     (2, 1, "Season 1", Date("2014-04-06"), Date("2014-06-01")),
    20.     (2, 2, "Season 2", Date("2015-04-12"), Date("2015-06-14"))
    21. ;
    23. UPSERT INTO episodes (series_id, season_id, episode_id, title, air_date)
    24. VALUES
    25.     (1, 1, 1, "Yesterday's Jam", Date("2006-02-03")),
    26.     (1, 1, 2, "Calamity Jen", Date("2006-02-03")),
    27.     (2, 1, 1, "Minimum Viable Product", Date("2014-04-06")),
    28.     (2, 1, 2, "The Cap Table", Date("2014-04-13"))
    29. ;

    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:

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

    SELECT : Data retrieval

    YQL query language - 图6

    or

    1. 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 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:

    • : Select data.
    • SELECT … JOIN: Join tables in a select.
    • : Group data in a select.

    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 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:

    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:

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

      Next step

      Go to to proceed with the ‘Getting started’ scenario.