Выполнение параметризованных YQL-запросов и скриптов

Обзор

YDB CLI поддерживает исполнение параметризованных YQL-запросов. Для работы с параметрами в тексте YQL-запроса должны присутствовать их определения командой YQL DECLARE.

Для выполнения параметризованных YQL-запросов вы можете использовать команды YDB CLI:

• ydb yql;
• ydb scripting yql;
• ydb table query execute.

Данные команды поддерживают одинаковый синтаксис и возможности передачи параметров запросов. Значения параметров могут быть заданы в командной строке, загружены из файлов в формате JSON, а также считаны с stdin в бинарном формате или в формате JSON. При передаче параметров через stdin поддерживается многократное поточное исполнение YQL-запроса с разными значениями параметров и возможностью пакетирования.

Единичное исполнение YQL-запроса

Параметры для исполнения YQL-запроса могут быть переданы из командной строки, JSON-файлов, и stdin, для чего в командах YDB CLI предназначены следующие параметры:

Запрос будет отправлен на исполнение на сервер один раз, если значения заданы для всех параметров в секции DECLARE. Если хотя бы для одного параметра значение не задано, запрос не будет выполнен с выдачей сообщения об ошибке "Missing value for parameter".

Примеры

В примерах используется команда table query execute, но они также могут быть выполнены командами yql и scripting yql.

Передача значения одного параметра

В командной строке:

ydb -p quickstart table query execute -q 'declare $a as Int64;select $a' --param '$a=10'

Через файл:

echo '{"a":10}' > p1.json
ydb -p quickstart table query execute -q 'declare $a as Int64;select $a' --param-file p1.json

Через stdin:

echo '{"a":10}' | ydb -p quickstart table query execute -q 'declare $a as Int64;select $a'

echo '10' | ydb -p quickstart table query execute -q 'declare $a as Int64;select $a' --stdin-par a

Передача значений параметров разных типов из нескольких источников

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'

Вывод команды:

┌─────────┬─────────────┬────────────────────────┬─────────┐
| column0 | column1     | column2                | column3 |
├─────────┼─────────────┼────────────────────────┼─────────┤
| 10      | "Some text" | "2012-04-23T18:25:43Z" | 30      |
└─────────┴─────────────┴────────────────────────┴─────────┘

Передача бинарных строк в кодировке Base64

ydb -p quickstart table query execute \
  -q 'declare $a as String;
      select $a' \
  --input-format json-base64 \
  --param '$a="SGVsbG8sIHdvcmxkCg=="'

Вывод команды:

┌──────────────────┐
| column0          |
├──────────────────┤
| "Hello, world\n" |
└──────────────────┘

Прямая передача бинарного контента

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

Вывод команды (точное количество байт может отличаться):

┌─────────┐
| column0 |
├─────────┤
| 66426   |
└─────────┘

Передача файла в формате CSV

echo '10,Some text' | ydb -p quickstart table query execute \
  -q 'declare $a as Int32;
      declare $b as String
      select $a, $b' \
  --stdin-format csv \
  --columns 'a,b'

Вывод команды:

┌─────────┬─────────────┐
| column0 | column1     |
├─────────┼─────────────┤
| 10      | "Some text" |
└─────────┴─────────────┘

Итеративная потоковая обработка

YDB CLI поддерживает возможность многократного исполнения YQL-запроса с разными наборами значений параметров, при их передаче через stdin. При этом соединение с базой данных устанавливается однократно, а план исполнения запроса кешируется, что существенно повышает производительность такого подхода по сравнению с отдельными вызовами CLI.

Для того чтобы воспользоваться этой возможностью, необходимо на stdin друг за другом передавать разные наборы значений одних и тех параметров, сообщив YDB CLI правило, по которому будет возможно отделить эти наборы друг от друга.

YQL-запрос выполняется столько раз, сколько наборов значений параметров получено через stdin. Каждый полученный на stdin набор объядиняется со значениями параметров, определенными через другие источники (--param, --param-file). Исполнение команды будет завершено после завершения потока на stdin. Каждый запрос исполняется в своей транзакции.

Правило отделения наборов параметров друг от друга (фрейминг) дополняет описание формата представления параметров на stdin, задаваемое параметром --stdin-format:

Пример

Потоковая обработка нескольких наборов параметров

echo -e '{"a":10,"b":20}\n{"a":15,"b":25}\n{"a":35,"b":48}' > par1.txt
cat par1.txt

{"a":10,"b":20}
{"a":15,"b":25}
{"a":35,"b":48}

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

{"column0":30}
{"column0":40}
{"column0":83}

echo -e 'a,b\n10,20\n15,25\n35,48' > par1.txt
cat par1.txt

a,b
10,20
15,25
35,48

cat par1.txt | \
ydb -p quickstart table query execute \
  -q 'declare $a as Int64;
      declare $b as Int64;
      select $a+$b, $a-$b' \
  --stdin-format newline-delimited \
  --stdin-format csv \
  --format csv

30,-10
40,-10
83,-13

echo -e 'a\tb\n10\trow1\n15\t"row\t2"\n35\t"""row""\n3"' > par1.txt
cat par1.txt

a  b
10  row1
15  "row  2"
35  """row""
3"

cat par1.txt | \
ydb -p quickstart table query execute \
  -q 'declare $a as Int64;
      declare $b as Utf8;
      select $a, $b' \
  --stdin-format newline-delimited \
  --stdin-format tsv \
  --format tsv

10  "row1"
15  "row\t2"
35  "\"row\"\n3"

Потоковая обработка с объединением значений параметров из разных источников

Допустим, нам необходимо выполнить запрос трижды, со следующими наборами значений параметров a и b:

1. a = 10, b = 100
2. a = 15, b = 100
3. a = 35, b = 100

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

Вывод команды:

{"sum1":110}
{"sum1":115}
{"sum1":135}

Пакетная потоковая обработка

YDB CLI поддерживает автоматическую конвертацию наборов параметров в List<>, позволяя одним запросом к серверу обработать множество наборов параметров в одной транзакции, что может дополнительно существенно повышать производительность по сравнению с выполнением запросов "по одному".

Поддерживаются два режима пакетирования:

• Полный
• Адаптивный

Полный режим пакетирования

Полный (full) режим является упрощенным вариантом пакетирования, когда запрос выполняется один раз, с заворачиванием в List<> всех полученных на stdin наборов параметров. Если размер запроса окажется слишком большим, будет выдана ошибка.

Данный вариант пакетирования необходим в случае, когда необходимо гарантировать атомарность за счет применения всех параметров в одной транзакции.

Адаптивный режим пакетирования

При работе в адаптивном (adaptive) режиме обработка входного потока разбивается на множество транзакций, с автоматическим подбором размера пакета для каждой из них.

Данный режим позволяет эффективно обрабатывать широкий спектр входной нагрузки, с непредсказуемым или бесконечным количеством данных, а также непредсказуемым или сильно меняющимся темпом их появления на входе. В частности, такой профиль характерен при подаче на stdin выхода другой команды через оператор |.

Адаптивный режим решает две основных проблемы обработки динамического потока:

1. Ограничение максимального размера пакета.
2. Ограничение максимальной задержки обработки данных.

Синтаксис

Для того чтобы воспользоваться возможностью пакетирования, необходимо описать параметр типа List<...> или List<Struct<...>> в секции DECLARE YQL-запроса, и выбрать режим следующим параметром:

В адаптивном режиме пакетирования доступны дополнительные параметры:

Примеры - полная пакетная обработка

echo -e '{"a":10,"b":20}\n{"a":15,"b":25}\n{"a":35,"b":48}' | \
ydb -p quickstart table query execute \
  -q 'declare $x as List<Struct<a:Int64,b:Int64>>;
      select ListLength($x), $x' \
  --stdin-format newline-delimited \
  --stdin-par x \
  --batch full

Вывод команды:

┌─────────┬───────────────────────────────────────────────────┐
| column0 | column1                                           |
├─────────┼───────────────────────────────────────────────────┤
| 3       | [{"a":10,"b":20},{"a":15,"b":25},{"a":35,"b":48}] |
└─────────┴───────────────────────────────────────────────────┘

Примеры - адаптивная пакетная обработка

Ограничение максимальной задержки обработки

Для демонстрации работы адаптивного пакетирования со срабатыванием ограничения по задержке обработки в первой строке команды ниже производится генерация 1000 строк с задержкой в 0.2 секунды в stdout, которые передаются на stdin команде исполнения YQL-запроса. Команда исполнения YQL-запроса, в свою очередь, отображает пакеты параметров в каждом следующем вызове YQL-запроса.

for i in $(seq 1 1000);do echo "Line$i";sleep 0.2;done | \
ydb -p quickstart table query execute \
  -q 'declare $x as List<Utf8>;
      select ListLength($x), $x' \
  --stdin-format newline-delimited \
  --stdin-format raw \
  --stdin-par x \
  --batch adaptive

Вывод команды (точные значения могут отличаться):

┌─────────┬────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
| column0 | column1                                                                                                                |
├─────────┼────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
| 14      | ["Line1","Line2","Line3","Line4","Line5","Line6","Line7","Line8","Line9","Line10","Line11","Line12","Line13","Line14"] |
└─────────┴────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
┌─────────┬─────────────────────────────────────────────────────────┐
| column0 | column1                                                 |
├─────────┼─────────────────────────────────────────────────────────┤
| 6       | ["Line15","Line16","Line17","Line18","Line19","Line20"] |
└─────────┴─────────────────────────────────────────────────────────┘
┌─────────┬─────────────────────────────────────────────────────────┐
| column0 | column1                                                 |
├─────────┼─────────────────────────────────────────────────────────┤
| 6       | ["Line21","Line22","Line23","Line24","Line25","Line26"] |
└─────────┴─────────────────────────────────────────────────────────┘
^C

В первый пакет попадают все строки, накопившиеся на входе за время открытия соединения с БД, и поэтому он больше чем последующие.

Выполнение команды можно прервать по Ctrl+C, или дождаться пока пройдут 200 секунд, в течение которых генерируется вход.

Ограничение по количеству записей

Для демонстрации работы адаптивного пакетирования со срабатыванием триггера по количеству наборов параметров в первой строке команды ниже производится генерация 200 строк. Команда будет отображать пакеты параметров в каждом следующем вызове YQL-запроса, учитывая заданное ограничение --batch-limit равное 20 (по умолчанию 1000).

В данном примере также показана возможность объединения параметров из разных источников, и формирование JSON на выходе.

for i in $(seq 1 200);do echo "Line$i";done | \
ydb -p quickstart table query execute \
  -q 'declare $x as List<Utf8>;
      declare $p2 as Int64;
      select ListLength($x) as count, $p2 as p2, $x as items' \
  --stdin-format newline-delimited \
  --stdin-format raw \
  --stdin-par x \
  --batch adaptive \
  --batch-limit 20 \
  --param '$p2=10' \
  --format json-unicode

Вывод команды:

{"count":20,"p2":10,"items":["Line1","Line2","Line3","Line4","Line5","Line6","Line7","Line8","Line9","Line10","Line11","Line12","Line13","Line14","Line15","Line16","Line17","Line18","Line19","Line20"]}
{"count":20,"p2":10,"items":["Line21","Line22","Line23","Line24","Line25","Line26","Line27","Line28","Line29","Line30","Line31","Line32","Line33","Line34","Line35","Line36","Line37","Line38","Line39","Line40"]}
...
{"count":20,"p2":10,"items":["Line161","Line162","Line163","Line164","Line165","Line166","Line167","Line168","Line169","Line170","Line171","Line172","Line173","Line174","Line175","Line176","Line177","Line178","Line179","Line180"]}
{"count":20,"p2":10,"items":["Line181","Line182","Line183","Line184","Line185","Line186","Line187","Line188","Line189","Line190","Line191","Line192","Line193","Line194","Line195","Line196","Line197","Line198","Line199","Line200"]}

Удаление множества записей из строковой таблицы YDB по первичным ключам

Данный пример показывает как можно удалять неограниченное количество записей из строковых таблиц YDB, не рискуя превысить ограничения на количество записей в транзакции.

Создадим тестовую строковую таблицу:

ydb -p quickstart yql -s 'create table test_delete_1( id UInt64 not null, primary key (id))'

Занесем в неё 100000 записей:

for i in $(seq 1 100000);do echo "$i";done | \
ydb -p quickstart import file csv -p test_delete_1

Удалим все записи с ID > 10:

ydb -p quickstart table query execute -t scan \
  -q 'select t.id from test_delete_1 as t where t.id > 10' \
  --format json-unicode | \
ydb -p quickstart table query execute \
  -q 'declare $lines as List<Struct<id:UInt64>>;
      delete from test_delete_1 where id in (select tl.id from AS_TABLE($lines) as tl)' \
  --stdin-format newline-delimited \
  --stdin-par lines \
  --batch adaptive \
  --batch-limit 10000

Обработка сообщений, считываемых из топика

Примеры обработки сообщений, считываемых из топика, приведены в статье Исполнение YQL-запроса с передачей сообщений из топика в качестве параметров.

См. также

• Параметризованные YQL-запросы в YDB SDK