Running a query

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

global options: Global parameters.
options: Parameters of the subcommand.

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