--- metadata: - name: generator content: Diplodoc Platform v5.52.0 alternate: - https://ydb.tech/docs/en/reference/ydb-cli/parameterized-queries-cli.md - https://ydb.tech/docs/ru/reference/ydb-cli/parameterized-queries-cli.md - href: en/reference/ydb-cli/parameterized-queries-cli.md type: text/markdown title: Markdown version - href: ../../llms.txt type: text/markdown title: llms.txt sourcePath: en/core/reference/ydb-cli/parameterized-queries-cli.md --- > **Documentation Index:** Fetch the complete configuration index at https://ydb.tech/docs/en/llms.txt {% note warning %} This page is outdated. Please refer to [Running parameterized queries](https://ydb.tech/docs/en/reference/ydb-cli/parameterized-query-execution.md) for up-to-date information. {% endnote %} # Running parametrized YQL queries and scripts ## Overview YDB CLI can execute [parameterized YQL queries](https://en.wikipedia.org/wiki/Prepared_statement). To use parameters you need to declare them using [the YQL `DECLARE`](https://ydb.tech/docs/en/yql/reference/syntax/declare.md) command in your YQL query text. To run parameterized YQL queries you can use the following YDB CLI commands: * [ydb yql](https://ydb.tech/docs/en/reference/ydb-cli/yql.md). * [ydb scripting yql](https://ydb.tech/docs/en/reference/ydb-cli/scripting-yql.md). * [ydb table query execute](https://ydb.tech/docs/en/reference/ydb-cli/table-query-execute.md). These commands support the same query parametrization options. Parameter values can be set on the command line, uploaded from [JSON](https://en.wikipedia.org/wiki/JSON) files, and read from `stdin` in binary or [JSON](https://en.wikipedia.org/wiki/JSON) format. On `stdin` you can stream multiple parameter values triggering multiple YQL query executions with batching options. {% note warning %} Among the above commands, only the `table query execute` applies retry policies. Such policies ensure reliable query execution and continuity when certain data ranges are unavailable for a short time because of partition changes or other regular processes in a distributed database. {% endnote %} ## Executing a single YQL query {#one-request} To provide parameters for a YQL query execution, you can use command line, JSON files, and `stdin`, using the following YDB CLI options: | Name | Description | |------------------|-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| | `-p, --param` | An expression in the format `$name=value`, where `$name` is the name of the YQL query parameter and `value` is its value (a correct [JSON value](https://www.json.org/json-ru.html)). The option can be specified repeatedly.

All the specified parameters must be declared in the YQL query by the [DECLARE operator](../../yql/reference/syntax/declare.md); otherwise, you will get an error "Query does not contain parameter". If you specify the same parameter several times, you will get an error "Parameter value found in more than one source".

Depending on your operating system, you might need to escape the `$` character or enclose your expression in single quotes (`'`). | | `--param-file` | Name of a file in [JSON](https://en.wikipedia.org/wiki/JSON) format in [UTF-8](https://en.wikipedia.org/wiki/UTF-8) encoding that contains parameter values matched against the YQL query parameters by key names. The option can be specified repeatedly.

If values of the same parameter are found in multiple files or set by the `--param` command line option, you'll get an error "Parameter value found in more than one source".

Names of keys in the JSON file are expected without the leading `$` sign. Keys that are present in the file but aren't declared in the YQL query will be ignored without an error message. | | `--input-format` | Format of parameter values, applied to all sources of parameters (command line, file, or `stdin`).
Available options: | | `--stdin-format` | Format of parameter values for `stdin`.
The YDB CLI automatically detects that a file or an output of another shell command has been redirected to the standard input device `stdin`. In this case, the CLI interprets the incoming data based on the following available options:If format of parameter values for `stdin` isn't specified, the `--input-format` is used. | | `--stdin-par` | Name of a parameter whose value is provided on `stdin`, without a `$` sign. This name is required when you use the `raw` format in `--stdin-format`.

When used with JSON formats, `stdin` is interpreted not as a JSON document but as a JSON value passed to the parameter with the specified name. | The query will be executed on the server once, provided that values are specified for all the parameters [in the `DECLARE` clause](https://ydb.tech/docs/en/yql/reference/syntax/declare.md). If a value is absent for at least one parameter, the command fails with the "Missing value for parameter" message. ### Examples {#examples-one-request} In our examples, we use the `table query execute` command, but you can also run them using the `yql` and `scripting yql` commands. {% note info %} The examples use the `quickstart` profile. To learn more, see [Creating a profile to connect to a test database](https://ydb.tech/docs/en/reference/ydb-cli/profile/create.md#quickstart). {% endnote %} #### Passing the value of a single parameter {#example-simple} From the command line: ```bash ydb -p quickstart table query execute -q 'declare $a as Int64;select $a' --param '$a=10' ``` Using a file: ```bash echo '{"a":10}' > p1.json ydb -p quickstart table query execute -q 'declare $a as Int64;select $a' --param-file p1.json ``` Via `stdin`: ```bash echo '{"a":10}' | ydb -p quickstart table query execute -q 'declare $a as Int64;select $a' ``` ```bash echo '10' | ydb -p quickstart table query execute -q 'declare $a as Int64;select $a' --stdin-par a ``` #### Passing the values of parameters of different types from multiple sources {#example-multisource} ```bash echo '{ "a":10, "b":"Some text", "x":"Ignore me" }' > p1.json echo '{ "c":"2012-04-23T18:25:43.511Z" }' | ydb -p quickstart table query execute \ -q 'declare $a as Int64; declare $b as Utf8; declare $c as DateTime; declare $d as Int64; select $a, $b, $c, $d' \ --param-file p1.json \ --param '$d=30' ``` Command output: ```text ┌─────────┬─────────────┬────────────────────────┬─────────┐ | column0 | column1 | column2 | column3 | ├─────────┼─────────────┼────────────────────────┼─────────┤ | 10 | "Some text" | "2012-04-23T18:25:43Z" | 30 | └─────────┴─────────────┴────────────────────────┴─────────┘ ``` #### Passing Base64-encoded binary strings {#example-base64} ```bash ydb -p quickstart table query execute \ -q 'DECLARE $a AS String; SELECT $a' \ --input-format json-base64 \ --param '$a="SGVsbG8sIHdvcmxkCg=="' ``` Command output: ```text ┌──────────────────┐ | column0 | ├──────────────────┤ | "Hello, world\n" | └──────────────────┘ ``` #### Passing binary content directly {#example-raw} ```bash curl -Ls http://ydb.tech/docs | ydb -p quickstart table query execute \ -q 'DECLARE $a AS String; SELECT LEN($a)' \ --stdin-format raw \ --stdin-par a ``` Command output (exact number of bytes may vary): ```text ┌─────────┐ | column0 | ├─────────┤ | 66426 | └─────────┘ ``` ## Iterative streaming processing {#streaming-iterate} YDB CLI supports execution of a YQL query multiple times with different sets of parameter values provided on `stdin`. In this case, the database connection is established once and the query execution plan is cached. This substantially increases the performance of such an approach compared to separate CLI calls. To use this feature, you need to stream different sets of the same parameters to `stdin` one after another, specifying a rule for the YDB CLI on how to separate the sets from each other. The YQL query runs as many times as many parameter value sets received on `stdin`. Each set received on `stdin` is joined with the parameter values defined on other sources (`--param`, `--param-file`). The command will complete once the `stdin` stream is closed. Each query is executed within a dedicated transaction. A rule for separating parameter sets from one another (framing) complements the `stdin` format specified by the `--stdin-format` option: | Name | Description | | --- | --- | | `--stdin-format` | Defines the `stdin` framing.
Available options: | {% note warning %} When using a newline character as a separator between the parameter sets, make sure that it isn't used inside the parameter sets. Putting some text value in quotes does not enable newlines within the text. Multiline JSON documents are not allowed. {% endnote %} ### Example {#example-streaming-iterate} #### Streaming processing of multiple parameter sets {#example-iterate} Suppose you need to run your query thrice, with the following sets of values for the `a` and `b` parameters: 1. `a` = 10, `b` = 20 2. `a` = 15, `b` = 25 3. `a` = 35, `b` = 48 Let's create a file that includes lines with JSON representations of these sets: ```bash echo -e '{"a":10,"b":20}\n{"a":15,"b":25}\n{"a":35,"b":48}' > par1.txt cat par1.txt ``` Command output: ```text {"a":10,"b":20} {"a":15,"b":25} {"a":35,"b":48} ``` Let's execute the query by passing the content of this file to `stdin`, formatting the output as JSON: ```bash cat par1.txt | \ ydb -p quickstart table query execute \ -q 'DECLARE $a AS Int64; DECLARE $b AS Int64; SELECT $a + $b' \ --stdin-format newline-delimited \ --format json-unicode ``` Command output: ```text {"column0":30} {"column0":40} {"column0":83} ``` This output can be passed as input to the next YQL query command. #### Streaming processing with joining parameter values from different sources {#example-iterate-union} For example, you need to run your query thrice, with the following sets of values for the `a` and `b` parameters: 1. `a` = 10, `b` = 100 2. `a` = 15, `b` = 100 3. `a` = 35, `b` = 100 ```bash echo -e '10\n15\n35' | \ ydb -p quickstart table query execute \ -q 'DECLARE $a AS Int64; DECLARE $b AS Int64; SELECT $a + $b AS sum1' \ --param '$b=100' \ --stdin-format newline-delimited \ --stdin-par a \ --format json-unicode ``` Command output: ```text {"sum1":110} {"sum1":115} {"sum1":135} ``` ## Batched streaming processing {#streaming-batch} The YDB CLI supports automatic conversion of multiple consecutive parameter sets to a `List<>`, enabling you to process them in a single request and transaction. As a result, you can have a substantial performance gain compared to one-by-one query processing. Two batch modes are supported: - Full - Adaptive ### Full batch mode {#batch-full} The `full` mode is a simplified batch mode where the query runs only once, and all the parameter sets received through `stdin` are wrapped into a `List<>`. If the request is too large, you will get an error. Use this batch mode when you want to ensure transaction atomicity by applying all the parameters within a single transaction. ### Adaptive batch mode {#batch-adaptive} In the `adaptive` mode, the input stream is split into multiple transactions, with the batch size automatically determined for each of them. In this mode, you can process a broad range of dynamic workloads with unpredictable or infinite amounts of data, as well as with unpredictable or significantly varying rate of new sets appearance at the input. For example, such a profile is typical when sending the output of another command to `stdin` using the `|` operator. The adaptive mode solves two basic issues of dynamic stream processing: 1. Limiting the maximum batch size. 2. Limiting the maximum data processing delay. ### Syntax {#batch-syntax} To use the batching capbilities, define the `List<...>` or `List>` parameter in the YQL query's DECLARE clause, and use the following options: | Name | Description | |-----------|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| | `--batch` | The batch mode applied to parameter sets on `stdin`.
Available options: