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

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.

    Previous
    Running a script
    Next
    Running parameterized queries