Running a script

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.

Previous
Running a script (with streaming support)
Next
Running a query