Running a query

Warning

This command is deprecated.
The preferred way to run queries in YDB CLI is to use the ydb sql command.

The table query execute subcommand is designed for reliable execution of YQL queries. With this sub-command, you can successfully execute your query when certain table partitions are unavailable for a short time (for example, due to being split or merged) by using built-in retry policies.

General format of the command:

ydb [global options...] table query execute [options...]

View the description of the YQL query command:

ydb table query execute --help

Parameters of the subcommand

Name

Description

--timeout

The time within which the operation should be completed on the server.

-t, --type

Query type.
Acceptable values:

  • data: A YQL query that includes DML operations; it can be used both to update data in the database and fetch several selections limited to 1,000 rows per selection.
  • scan: A YQL query of the scan type. It can only be used to read data from the database. It returns a single selection, but without a limit on the number of records in it. The algorithm of executing a scan query on the server is more sophisticated compared to a data query. Hence, if you don't need to return more than 1,000 rows, data queries are more effective.
  • scheme: A YQL query that includes DDL operations.
    The default value is data.

--stats

Statistics mode.
Acceptable values:

  • none: Do not collect statistics.

  • basic: Collect statistics for basic events.

  • full: Collect statistics for all events.

    Defaults to none.

-s

Enable statistics collection in the basic mode.

--tx-mode

Transaction mode (for data queries).
Acceptable values:

  • serializable-rw: The result of parallel transactions is equivalent to their serial execution.
  • online-ro: Each of the reads in the transaction reads data that is most recent at the time of its execution.
  • stale-ro: Data reads in a transaction return results with a possible delay (fractions of a second).Default value: serializable-rw.

    		•

    -q, --query

    Text of the YQL query to be executed.

    -f, --file

    Path to the text of the YQL query to be executed.

    --format

    Result format.
    Possible values:

    • pretty (default): Human-readable format.
    • json-unicode: JSON output with binary strings Unicode-encoded and each JSON string in a separate line.
    • json-unicode-array: JSON output with binary strings Unicode-encoded and the result output as an array of JSON strings with each JSON string in a separate line.
    • json-base64: JSON output with binary strings Base64-encoded and each JSON string in a separate line.
    • json-base64-array: JSON output with binary strings Base64-encoded and the result output as an array of JSON strings with each JSON string in a separate line;
    • parquet: Output in Apache Parquet format.
    • csv: Output in CSV format.
    • tsv: Output in TSV format.

    Working with parameterized queries

    A brief help is provided below. For a detailed description with examples, see Running parametrized YQL queries and scripts.

    Name Description
    -p, --param The value of a single parameter of a YQL query, in the format: $name=value, where $name is the parameter name and value is its value (a valid JSON value).
    --param-file Name of the file in JSON format and in UTF-8 encoding that specifies values of the parameters matched against the YQL query parameters by key names.
    --input-format Format of parameter values. Applies to all the methods of parameter transmission (among command parameters, in a file or using stdin).
    Acceptable values:
    • json-unicode (default):JSON.
    • json-base64: JSON format in which values of binary string parameters (DECLARE $par AS String) are Base64-encoded.
    --stdin-format The parameter format and framing for stdin. To set both values, specify the parameter twice.
    Format of parameter encoding for stdin
    Acceptable values:
    • json-unicode: JSON.
    • json-base64: JSON format in which values of binary string parameters (DECLARE $par AS String) are Base64-encoded.
    • raw is binary data; the parameter name is set in --stdin-par.
    If the format of parameter encoding for stdin isn't specified, the format set in --input-format is used.

    Classification of parameter sets for stdin (framing)
    Acceptable values:
    • no-framing (default): Framing isn't used
    • newline-delimited: The newline character is used in stdin to end a given parameter set, separating it from the next one.
    --stdin-par The name of the parameter whose value will be sent over stdin is specified without a $.
    --batch The batch mode of transmitting parameter sets received via stdin.
    Acceptable values:
    • iterative (default): Batch mode is disabled
    • full: Full-scale batch mode is enabled
    • adaptive: Adaptive batching is enabled
    --batch-limit A maximum number of sets of parameters per batch in the adaptive batch mode. The setting of 0 removes the limit.

    The default value is 1000.
    --batch-max-delay The maximum delay related to processing the resulting parameter set in the adaptive batch mode. It's set as a number of s, ms, m.

    Default value: 1s (1 second).

    Examples

    Note

    The examples use the quickstart profile. To learn more, see Creating a profile to connect to a test database.

    Creating tables

    ydb -p quickstart table query execute \
  --type scheme \
  -q '
  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));
  '

    Populating the table with data

    ydb -p quickstart table query execute \
  -q '
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"));
'

    Simple data selection

    ydb -p quickstart table query execute -q '
  SELECT season_id, episode_id, title
  FROM episodes
  WHERE series_id = 1
'

    Result:

    ┌───────────┬────────────┬───────────────────┐
| season_id | episode_id | title             |
├───────────┼────────────┼───────────────────┤
| 1         | 1          | "Yesterday's Jam" |
├───────────┼────────────┼───────────────────┤
| 1         | 2          | "Calamity Jen"    |
└───────────┴────────────┴───────────────────┘

    Unlimited selection for automated processing

    Selecting data by a query whose text is saved to a file, without a limit on the number of rows in the selection and data output in the format: Newline-delimited JSON stream.

    Let's write the query text to the request1.yql file.

    echo 'SELECT season_id, episode_id, title FROM episodes' > request1.yql

    Now, run the query:

    ydb -p quickstart table query execute -f request1.yql --type scan --format json-unicode

    Result:

    {"season_id":1,"episode_id":1,"title":"Yesterday's Jam"}
{"season_id":1,"episode_id":2,"title":"Calamity Jen"}
{"season_id":1,"episode_id":1,"title":"Minimum Viable Product"}
{"season_id":1,"episode_id":2,"title":"The Cap Table"}

    Passing parameters

    You can find examples of executing parameterized queries, including streamed processing, in the Passing parameters to YQL execution commands article.