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

Обзор

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

Основной инструмент для выполнения параметризованных запросов в YDB CLI — это команда ydb sql.

Зачем использовать параметризованные запросы?

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

• Улучшенная производительность: Параметризованные запросы значительно повышают производительность при выполнении множества схожих запросов, которые различаются только входными параметрами. Это достигается с помощью подготовленных запросов. Запрос компилируется один раз и затем кешируется на сервере. Последующие запросы с тем же текстом минуют фазу компиляции и сразу начинают выполняться.

• Защита от SQL-инъекций: Другим важным преимуществом использования параметризованных запросов является защита от SQL-инъекций. Эта функция безопасности гарантирует правильную обработку входных данных, что снижает риск выполнения вредоносного кода.

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

Эта команда поддерживает передачу параметров через опции командной строки, файл, а также через stdin. При передаче параметров через stdin или файл поддерживается многократное поточное исполнение запроса с разными значениями параметров и возможностью пакетирования. Для этого в команде ydb sql предназначены следующие параметры:

Если значения указаны для всех параметров, которые не допускают NULL (то есть с типом NOT NULL), в составе оператора DECLARE, запрос будет выполнен на сервере. Если отсутствует значение хотя бы для одного такого параметра, выполнение команды завершится с ошибкой и сообщением "Не задано значение для параметра".

Более специфичные опции для использования входных параметров

Следующие опции не представлены в выводе --help. Их описание можно увидеть только в выводе -hh.

Примеры

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

В командной строке, через опцию --param:

ydb -p quickstart sql -s 'DECLARE $a AS Int64; SELECT $a' --param '$a=10'

Через файл в формате JSON (который используется по умолчанию):

echo '{"a":10}' > p1.json
ydb -p quickstart sql -s 'DECLARE $a AS Int64; SELECT $a' --input-file p1.json

Через stdin, передавая JSON-строку как набор из одного параметра:

echo '{"a":10}' | ydb -p quickstart sql -s 'DECLARE $a AS Int64; SELECT $a'

Через stdin, передавая только значение параметра и задавая имя параметра через опцию --input-param-name:

echo '10' | ydb -p quickstart sql -s 'DECLARE $a AS Int64; SELECT $a' --input-param-name a

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

# Create a JSON file with fields 'a', 'b', and 'x', where 'x' will be ignored in the query
echo '{ "a":10, "b":"Some text", "x":"Ignore me" }' > p1.json

# Run the query using ydb-cli, passing in 'a' and 'b' from the input file, and 'c' as a direct parameter
ydb -p quickstart sql \
  -s 'DECLARE $a AS Int64;
      DECLARE $b AS Utf8;
      DECLARE $c AS Int64;

      SELECT $a, $b, $c' \
  --input-file p1.json \
  --param '$c=30'

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

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

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

ydb -p quickstart sql \
  -s 'DECLARE $a AS String;
      SELECT $a' \
  --input-format json \
  --input-binary-strings base64 \
  --param '$a="SGVsbG8sIHdvcmxkCg=="'

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

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

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

curl -Ls http://ydb.tech/docs/en | ydb -p quickstart sql \
  -s 'DECLARE $a AS String;
      SELECT LEN($a)' \
  --input-format raw \
  --input-param-name a

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

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

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

echo '10,Some text' | ydb -p quickstart sql \
  -s 'DECLARE $a AS Int32;
      DECLARE $b AS String;
      SELECT $a, $b' \
  --input-format csv \
  --input-columns 'a,b'

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

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

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

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

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

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

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

Пример

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

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

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

cat par1.txt | \
ydb -p quickstart sql \
  -s 'DECLARE $a AS Int64;
      DECLARE $b AS Int64;
      SELECT $a+$b' \
  --input-framing newline-delimited \
  --format json-unicode

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

ydb -p quickstart sql \
  -s 'DECLARE $a AS Int64;
      DECLARE $b AS Int64;
      SELECT $a + $b' \
  --input-file par1.txt \
  --input-framing newline-delimited \
  --format json-unicode

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

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

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

cat par1.txt | \
ydb -p quickstart sql \
  -s 'DECLARE $a AS Int64;
      DECLARE $b AS Int64;
      SELECT $a + $b' \
  --input-format csv \
  --input-framing newline-delimited \
  --format csv

30
40
83

ydb -p quickstart sql \
  -s 'DECLARE $a AS Int64;
      DECLARE $b AS Int64;
      SELECT $a + $b' \
  --input-file par1.txt \
  --input-format csv \
  --input-framing newline-delimited \
  --format csv

30
40
83

echo -e 'a\tb\n10\t20\n15\t25\n35\t48' | tee par1.txt

a  b
10 20
15 25
35 48

cat par1.txt | \
ydb -p quickstart sql \
  -s 'DECLARE $a AS Int64;
      DECLARE $b AS Utf8;
      SELECT $a, $b' \
  --input-framing newline-delimited \
  --input-format tsv \
  --format tsv

30
40
83

ydb -p quickstart sql \
  -s 'DECLARE $a AS Int64;
      DECLARE $b AS Int64;
      SELECT $a + $b' \
  --input-file par1.txt \
  --input-format tsv \
  --input-framing newline-delimited \
  --format tsv

30
40
83

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

Допустим, нам необходимо выполнить запрос трижды, со следующими наборами значений параметров 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 sql \
  -s 'DECLARE $a AS Int64;
      DECLARE $b AS Int64;
      SELECT $a + $b AS sum1' \
  --param '$b=100' \
  --input-framing newline-delimited \
  --input-param-name a \
  --format json-unicode

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

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

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

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

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

• Полный (full);
• Адаптивный (adaptive).

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

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

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

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

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

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

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

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

Синтаксис

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

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

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

echo -e '{"a":10,"b":20}\n{"a":15,"b":25}\n{"a":35,"b":48}' | \
ydb -p quickstart sql \
  -s 'DECLARE $x AS List<Struct<a:Int64,b:Int64>>;
      SELECT ListLength($x), $x' \
  --input-framing newline-delimited \
  --input-param-name x \
  --input-batch full

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

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

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

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

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

for i in $(seq 1 1000); do echo "Line$i"; sleep 0.2; done | \
ydb -p quickstart sql \
  -s 'DECLARE $x AS List<Utf8>;
      SELECT ListLength($x), $x' \
  --input-framing newline-delimited \
  --input-format raw \
  --input-param-name x \
  --input-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 строк. Команда будет отображать пакеты параметров в каждом следующем вызове запроса, учитывая заданное ограничение --input-batch-max-rows равное 20 (по умолчанию 1000).

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

for i in $(seq 1 200); do echo "Line$i"; done | \
ydb -p quickstart sql \
  -s 'DECLARE $x AS List<Utf8>;
      DECLARE $p2 AS Int64;
      SELECT ListLength($x) AS count, $p2 AS p2, $x AS items' \
  --input-framing newline-delimited \
  --input-format raw \
  --input-param-name x \
  --input-batch adaptive \
  --input-batch-max-rows 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 по первичным ключам

Если вы попытаетесь удалить большое количество строк из крупной таблицы с помощью простого запроса DELETE FROM large_table WHERE id > 10;, вы можете столкнуться с ошибкой из-за превышения ограничения на количество записей в транзакции. Данный пример показывает, как можно удалять неограниченное количество записей из таблиц YDB без нарушения этого ограничения.
Создадим тестовую строковую таблицу:

ydb -p quickstart sql -s 'CREATE TABLE test_delete_1(id UInt64 NOT NULL, PRIMARY KEY (id))'

Занесем в неё 100,000 записей:

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 sql \
  -s 'SELECT t.id FROM test_delete_1 AS t WHERE t.id > 10' \
  --format json-unicode | \
ydb -p quickstart sql \
  -s 'DECLARE $lines AS List<Struct<id:UInt64>>;
      DELETE FROM test_delete_1 WHERE id IN (SELECT tl.id FROM AS_TABLE($lines) AS tl)' \
  --input-framing newline-delimited \
  --input-param-name lines \
  --input-batch adaptive \
  --input-batch-max-rows 10000

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

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

См. также

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