Running a script

Warning

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

You can use the scripting yql subcommand to run a YQL script. The script can include queries of different types. Unlike yql, the scripting yql command has a limit on the number of returned rows and accessed data.

General format of the command:

ydb [global options...] scripting yql [options...]

View the description of the YQL script command:

ydb scripting yql --help

Parameters of the subcommand

Name

Description

--timeout

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

--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, --script

Text of the YQL query to be executed.

-f, --file

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

--explain

Show the query execution plan.

--show-response-metadata

Show the response metadata.

--format

Result format.
Default value: pretty.
Acceptable 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.

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.

A script to create a table, populate it with data, and select data from the table:

ydb -p quickstart scripting yql -s '
    CREATE TABLE series (series_id Uint64, title Utf8, series_info Utf8, release_date Date, PRIMARY KEY (series_id));
    COMMIT;
    UPSERT INTO series (series_id, title, series_info, release_date) values (1, "Title1", "Info1", Cast("2023-04-20" as Date));
    COMMIT;
    SELECT * from series;
  '

Command output:

┌──────────────┬───────────┬─────────────┬──────────┐
| release_date | series_id | series_info | title    |
├──────────────┼───────────┼─────────────┼──────────┤
| "2023-04-20" | 1         | "Info1"     | "Title1" |
└──────────────┴───────────┴─────────────┴──────────┘

Running a script from the example above saved as the script1.yql file, with results output in JSON format:

ydb -p quickstart scripting yql -f script1.yql --format json-unicode

Command output:

{"release_date":"2023-04-20","series_id":1,"series_info":"Info1","title":"Title1"}

You can find examples of passing parameters to scripts in the article on how to pass parameters to YQL execution commands.