Синтаксис SELECT

SELECT

Возвращает результат вычисления выражений, указанных после SELECT.

Может использоваться в сочетании с другими операциями для получения иного эффекта.

Примеры:

SELECT "Hello, world!";

SELECT 2 + 2;

FROM

Источник данных для SELECT. В качестве аргумента может принимать имя таблицы, результат другого SELECT или именованное выражение. Между SELECT и FROM через запятую указываются имена столбцов из источника или * для выбора всех столбцов.

Таблица по имени ищется в базе данных, заданной оператором USE.

Примеры

SELECT key FROM my_table;

SELECT * FROM
  (SELECT value FROM my_table);

$table_name = "my_table";
SELECT * FROM $table_name;

WITH

Задается после источника данных во FROM и используется для указания дополнительных подсказок использования таблиц. Подсказки нельзя задать для подзапросов и именованных выражений.

Поддерживаются следующие значения:

• INFER_SCHEMA — задает флаг вывода схемы таблицы. Поведение аналогично заданию прагмы yt.InferSchema, только для конкретного источника данных. Можно задать число строк для выведения (число от 1 до 1000).
• FORCE_INFER_SCHEMA — задает флаг вывода схемы таблицы. Поведение аналогично заданию прагмы yt.ForceInferSchema, только для конкретного источника данных. Можно задать число строк для выведения (число от 1 до 1000).
• DIRECT_READ — подавляет работу некоторых оптимизаторов и заставляет использовать содержимое таблицы как есть. Поведение аналогично заданию отладочной прагмы DirectRead, только для конкретного источника данных.
• INLINE — указание на то, что содержимое таблицы небольшое и нужно использовать его представление в памяти для обработки запроса. Реальный объем таблицы при этом не контролируется, и если он большой, то запрос может упасть по превышению памяти.
• UNORDERED — подавляет использование исходной сортировки таблицы.
• XLOCK — указание на то, что нужно брать эксклюзивный лок на таблицу. Полезен, когда чтение таблицы происходит на стадии обработки метапрограммы запроса, а затем ее содержимое обновляется в основном запросе. Позволяет избежать потери данных, если между исполнением фазы метапрограммы и основной частью запроса внешний процесс успел изменить таблицу.
• SCHEMA type — указание на то, что следует использовать указанную схему таблицы целиком, игнорируя схему в метаданных.
• COLUMNS type — указание на то, что следует использовать указанные типы для колонок, чьи имена совпадают с именами колонок таблицы в метаданных, а также какие колонки дополнительно присутствуют в таблице.
• IGNORETYPEV3, IGNORE_TYPE_V3 — задает флаг игнорирования type_v3 типов в таблице. Поведение аналогично заданию прагмы yt.IgnoreTypeV3, только для конкретного источника данных.

При задании подсказок SCHEMA и COLUMNS в качестве значения типа type должен быть задан тип структуры.

Если задана подсказка SCHEMA, то при использовании табличных функций EACH, RANGE, LIKE, REGEXP, FILTER допускается пустой список таблиц, который обрабатывается как пустая таблица с колонками, описанными в SCHEMA.

Примеры:

SELECT key FROM my_table WITH INFER_SCHEMA;
SELECT key FROM my_table WITH FORCE_INFER_SCHEMA="42";

$s = (SELECT COUNT(*) FROM my_table WITH XLOCK);

INSERT INTO my_table WITH TRUNCATE
SELECT EvaluateExpr($s) AS a;

SELECT key, value FROM my_table WITH SCHEMA Struct<key:String, value:Int32>;

SELECT key, value FROM my_table WITH COLUMNS Struct<value:Int32?>;

SELECT key, value FROM EACH($my_tables) WITH SCHEMA Struct<key:String, value:List<Int32>>;

WHERE

Фильтрация строк в результате SELECT по условию.

Пример

SELECT key FROM my_table
WHERE value > 0;

ORDER BY

Сортировка результата SELECT по разделенному запятыми перечню критериев сортировки. В качестве критерия может выступать значение столбца, или выражение над столбцами. Не поддерживается указание порядкового номера колонки выборки (ORDER BY N, где N - номер).

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

• ASC — по возрастанию. Применяется по умолчанию.
• DESC — по убыванию.

Несколько критериев сортировки будут применены слева направо.

Пример

SELECT key, string_column
FROM my_table
ORDER BY key DESC, LENGTH(string_column) ASC;

Ключевое слово ORDER BY также может использоваться в механизме оконных функций.

LIMIT и OFFSET

LIMIT ограничивает вывод указанным количеством строк. Если значение лимита равно NULL, или LIMIT не указан, то вывод не ограничен.

OFFSET указывает отступ от начала (в строках). Если значение отступа равно NULL, или OFFSET не указан, то используется значение ноль.

Примеры

SELECT key FROM my_table
LIMIT 7;

SELECT key FROM my_table
LIMIT 7 OFFSET 3;

SELECT key FROM my_table
LIMIT 3, 7; -- эквивалентно предыдущему примеру

SELECT key FROM my_table
LIMIT NULL OFFSET NULL; -- эквивалентно SELECT key FROM my_table

ASSUME ORDER BY

Проверка сортированности результата SELECT по значению в указанном столбце или нескольких столбцах. Результат такого SELECT-а будет считаться сортированным, но без выполнения фактической сортировки. Проверка сортированности осуществляется на этапе исполнения запроса.

Как и для ORDER BY, поддерживается задание порядка сортировки с помощью ключевых слов ASC (по возрастанию) и DESC (по убыванию). Выражения в ASSUME ORDER BY не поддерживается.

Примеры:

SELECT key || "suffix" as key, -CAST(subkey as Int32) as subkey
FROM my_table
ASSUME ORDER BY key, subkey DESC;

TABLESAMPLE и SAMPLE

Построение случайной выборки из указанного во FROM источника данных.

TABLESAMPLE является частью SQL стандарта и работает следующим образом:

• Указывается режим работы:
  • BERNOULLI означает «медленно, честно просмотрев все данные, но по-настоящему случайно»;
  • SYSTEM должен использовать знание о физическом хранении данных, чтобы избежать полного их чтения частично жертвуя случайностью выборки.
    Данные разбиваются на достаточно большие блоки, и происходит сэмплирование данных блоков целиком. Для прикладных расчётов на достаточно больших таблицах результат вполне может оказаться состоятельным.
• Размер случайной выборки указывается в процентах следом за режимом в круглых скобках.
• Управлять размером блоков для режима SYSTEM можно с помощью прагмы yt.SamplingIoBlockSize
• Опционально далее указывается ключевое слово REPEATABLE и целое число в скобках, которое будет использовано как seed для генератора псевдослучайных чисел.

SAMPLE является более коротким алиасом, где нет сложных настроек, а размер выборки указывается в долях. На данный момент он соответствует режиму BERNOULLI.

Примеры:

SELECT *
FROM my_table
TABLESAMPLE BERNOULLI(1.0) REPEATABLE(123); -- один процент таблицы

SELECT *
FROM my_table
TABLESAMPLE SYSTEM(1.0); -- примерно один процент таблицы

SELECT *
FROM my_table
SAMPLE 1.0 / 3; -- треть таблицы

DISTINCT

Выбор уникальных строк.

Пример

SELECT DISTINCT value -- только уникальные значения из таблицы
FROM my_table;

Также ключевое слово DISTINCT может использоваться для применения агрегатных функций только к уникальным значениям. Подробнее можно прочитать вдокументации по GROUP BY.

Процедура выполнения SELECT

Результат запроса SELECT вычисляется следующим образом:

• определяется набор входных таблиц – вычисляются выражения после FROM;
• к входным таблицам применяется SAMPLE / TABLESAMPLE
• выполняется FLATTEN COLUMNS или FLATTEN BY; алиасы, заданные во FLATTEN BY, становятся видны после этой точки;
• выполняются все JOIN;
• к полученным данным добавляются (или заменяются) колонки, заданные в GROUP BY ... AS ...;
• выполняется WHERE — все данные не удовлетворяющие предикату отфильтровываются;
• выполняется GROUP BY, вычисляются значения агрегатных функций;
• выполняется фильтрация HAVING;
• вычисляются значения оконных функций;
• вычисляются выражения в SELECT;
• выражениям в SELECT назначаются имена заданные алиасами;
• к полученным таким образом колонкам применяется top-level DISTINCT;
• таким же образом вычисляются все подзапросы в UNION ALL, выполняется их объединение (смотрите PRAGMA AnsiOrderByLimitInUnionAll);
• выполняется сортировка согласно ORDER BY;
• к полученному результату применяются OFFSET и LIMIT.

Порядок колонок в YQL

В стандартном SQL порядок колонок указанных в проекции (в SELECT) имеет значение. Помимо того, что порядок колонок должен сохраняться при отображении результатов запроса или при записи в новую таблицу, некоторые конструкции SQL этот порядок используют.
Это относится в том числе к UNION ALL и к позиционному ORDER BY (ORDER BY ordinal).

По умолчанию в YQL порядок колонок игнорируется:

• порядок колонок в выходных таблицах и в результатах запроса не определен
• схема данных результата UNION ALL выводится по именам колонок, а не по позициям

При включении PRAGMA OrderedColumns; порядок колонок сохраняется в результатах запроса и выводится из порядка колонок во входных таблицах по следующим правилам:

• SELECT с явным перечислением колонок задает соответствующий порядок;
• SELECT со звездочкой (SELECT * FROM ...) наследует порядок из своего входа;
• порядок колонок после JOIN: сначала колонки левой стороны, потом правой. Если порядок какой-либо из сторон присутствующей в выходе JOIN не определен, порядок колонок результата также не определен;
• порядок UNION ALL зависит от режима выполнения UNION ALL;
• порядок колонок для AS_TABLE не определен;

При использовании PRAGMA OrderedColumns; порядок колонок после PROCESS/REDUCE не определен.

UNION ALL

Конкатенация результатов нескольких SELECT (или подзапросов).

Поддерживаются два режима выполнения UNION ALL – по именам колонок (режим по умолчанию) и по позициям колонок (соответствует стандарту ANSI SQL и включается через соответствующую PRAGMA).

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

• в результирующую таблицу включаются все колонки, которые встречались хоть в одной из входных таблиц;
• если колонка присутствовала не во всех входных таблицах, то ей автоматически присваивается опциональный тип данных (допускающий значение NULL);
• если колонка в разных входных таблицах имела разные типы, то выводится общий тип (наиболее широкий);
• если колонка в разных входных таблицах имела разнородный тип, например строку и число, то это считается ошибкой.

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

В режиме "по позициям" результирующая схема данных выводится по следующим правилам:

• число колонок во всех входах должно быть одинаковым
• порядок колонок во всех входах должен быть определен
• имена результирующих колонок совпадают с именами колонок первой таблицы
• тип результирующих колонок выводится как общий (наиболее широкий) тип из типов входных колонок стоящих на одинаковых позициях

Порядок выходных колонок в этом режиме совпадает с порядком колонок первого входа.

При наличии ORDER BY/LIMIT/DISCARD/INTO RESULT в объединяемых подзапросах применяются следующие правила:

• ORDER BY/LIMIT/INTO RESULT допускается только после последнего подзапроса;
• DISCARD допускается только перед первым подзапросом;
• указанные операторы действуют на результат UNION ALL а на не подзапрос;
• чтобы применить оператор к подзапросу, подзапрос необходимо взять в скобки.

Примеры

SELECT 1 AS x
UNION ALL
SELECT 2 AS y
UNION ALL
SELECT 3 AS z;

В результате выполнения данного запроса в режиме по-умолчанию будет сформирована выборка с тремя колонками x, y, и z. При включенной PRAGMA PositionalUnionAll; в выборке будет одна колонка x.

PRAGMA PositionalUnionAll;

SELECT 1 AS x, 2 as y
UNION ALL
SELECT * FROM AS_TABLE([<|x:3, y:4|>]); -- ошибка: порядок колонок в AS_TABLE не определен

SELECT * FROM T1
UNION ALL
(SELECT * FROM T2 ORDER BY key LIMIT 100); -- при отсутствии скобок ORDER BY/LIMIT применится к результату всего UNION ALL 

INSERT INTO @tmp
SELECT 1 AS x
UNION ALL
SELECT 2 AS y
UNION ALL
SELECT 3 AS z;

COMMIT;

SELECT * FROM @tmp;

COMMIT

По умолчанию весь YQL запрос выполняется в рамках одной транзакции и независимые его части внутри выполняются по возможности параллельно.
С помощью ключевого слова COMMIT; можно добавить барьер в процесс выполнения, чтобы отложить выполнение идущих следом выражений до тех пор, пока не выполнятся все предшествующие.

Чтобы коммит выполнялся аналогичным образом автоматически после каждого выражения в запросе, можно использовать PRAGMA autocommit;.

Примеры:

INSERT INTO result1 SELECT * FROM my_table;
INSERT INTO result2 SELECT * FROM my_table;
COMMIT;
-- В result2 уже будет содержимое SELECT со второй строки:
INSERT INTO result3 SELECT * FROM result2;

Обращение к нескольким таблицам в одном запросе

В стандартном SQL для выполнения запроса по нескольким таблицам используется UNION ALL, который объединяет результаты двух и более SELECT. Это не совсем удобно для сценария использования, в котором требуется выполнить один и тот же запрос по нескольким таблицам (например, содержащим данные на разные даты). В YQL, чтобы было удобнее, в SELECT после FROM можно указывать не только одну таблицу или подзапрос, но и вызывать встроенные функции, позволяющие объединять данные нескольких таблиц.

Для этих целей определены следующие функции:

CONCAT(`table1`, `table2`, `table3` VIEW view_name, ...) — объединяет все перечисленные в аргументах таблицы.

EACH($list_of_strings) или EACH($list_of_strings VIEW view_name) — объединяет все таблицы, имена которых перечислены в списке строк. Опционально можно передать несколько списков в отдельных аргументах по аналогии с CONCAT.

RANGE(`prefix`, `min`, `max`, `suffix`, `view`) — объединяет диапазон таблиц. Аргументы:

• prefix — каталог для поиска таблиц, указывается без завершающего слеша. Единственный обязательный аргумент, если указан только он, то используются все таблицы в данном каталоге.
• min, max — следующие два аргумента задают диапазон имен для включения таблиц. Диапазон инклюзивный с обоих концов. Если диапазон не указан, используются все таблицы в каталоге prefix. Имена таблиц или директорий, находящихся в указанной в prefix директории, сравниваются с диапазоном [min, max] лексикографически, а не конкатенируются, таким образом важно указывать диапазон без лидирующих слешей.
• suffix — имя таблицы. Ожидается без начального слеша. Если suffix не указан, то аргументы [min, max] задают диапазон имен таблиц. Если suffix указан, то аргументы [min, max] задают диапазон папок, в которых существует таблица с именем, указанным в аргументе suffix.

LIKE(`prefix`, `pattern`, `suffix`, `view`) и REGEXP(`prefix`, `pattern`, `suffix`, `view`) — аргумент pattern задается в формате, аналогичном одноименным бинарным операторам: LIKE и REGEXP.

FILTER(`prefix`, `callable`, `suffix`, `view`) — аргумент callable должен являться вызываемым выражением с сигнатурой (String)->Bool, который будет вызван для каждой таблицы/подкаталога в каталоге prefix. В запросе будут участвовать только те таблицы, для которых вызываемое значение вернуло true. В качестве вызываемого значения удобнее всего использовать лямбда функции.

По умолчанию схемы всех участвующих таблиц объединяются по правилам UNION ALL. Если объединение схем не желательно, то можно использовать функции с суффиксом _STRICT, например CONCAT_STRICT или RANGE_STRICT, которые работают полностью аналогично оригинальным, но считают любое расхождение в схемах таблиц ошибкой.

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

Все аргументы описанных выше функций могут быть объявлены отдельно через именованные выражения. В этом случае в них также допустимы и простые выражения посредством неявного вызова EvaluateExpr.

Имя исходной таблицы, из которой изначально была получена каждая строка, можно получить при помощи функции TablePath().

Примеры:

USE some_cluster;
SELECT * FROM CONCAT(
  `table1`,
  `table2`,
  `table3`);

USE some_cluster;
$indices = ListFromRange(1, 4);
$tables = ListMap($indices, ($index) -> {
    RETURN "table" || CAST($index AS String);
});
SELECT * FROM EACH($tables); -- идентично предыдущему примеру

USE some_cluster;
SELECT * FROM RANGE(`my_folder`);

SELECT * FROM some_cluster.RANGE( -- Кластер можно указать перед названием функции
  `my_folder`,
  `from_table`,
  `to_table`);

USE some_cluster;
SELECT * FROM RANGE(
  `my_folder`,
  `from_folder`,
  `to_folder`,
  `my_table`);

USE some_cluster;
SELECT * FROM RANGE(
  `my_folder`,
  `from_table`,
  `to_table`,
  ``,
  `my_view`);

USE some_cluster;
SELECT * FROM LIKE(
  `my_folder`,
  "2017-03-%"
);

USE some_cluster;
SELECT * FROM REGEXP(
  `my_folder`,
  "2017-03-1[2-4]?"
);

$callable = ($table_name) -> {
    return $table_name > "2017-03-13";
};

USE some_cluster;
SELECT * FROM FILTER(
  `my_folder`,
  $callable
);

Перечисление содержимого директории на кластере

Указывается как функция FOLDER в FROM.

Аргументы:

1. Путь к директории;
2. Опционально строка со списком интересующих мета атрибутов через точку с запятой.

В результате получается таблица с тремя фиксированными колонками:

1. Path (String) — полное имя таблицы;
2. Type (String) — тип узла (table, map_node, file, document и пр.);
3. Attributes (Yson) — Yson-словарь с заказанными во втором аргументе мета атрибутами.

Рекомендации по использованию:

• Чтобы получить только список таблиц, нужно не забывать добавлять ...WHERE Type == "table". Затем, опционально добавив ещё условий, с помощью агрегатной функции AGGREGATE_LIST от колонки Path можно получить только список путей и передать их в EACH.
• Так как колонка Path выдаётся в том же формате, что и результат функции TablePath(), то их можно использоваться для JOIN атрибутов таблицы к её строкам.
• C колонкой Attributes рекомендуется работать через Yson UDF.

Примеры:

USE hahn;

$table_paths = (
    SELECT AGGREGATE_LIST(Path)
    FROM FOLDER("my_folder", "schema;row_count")
    WHERE
        Type = "table" AND
        Yson::GetLength(Attributes.schema) > 0 AND
        Yson::LookupInt64(Attributes, "row_count") > 0
);

SELECT COUNT(*) FROM EACH($table_paths);

WITHOUT

Исключение столбцов из результата SELECT *.

Примеры

SELECT * WITHOUT foo, bar FROM my_table;

PRAGMA simplecolumns;
SELECT * WITHOUT t.foo FROM my_table AS t
CROSS JOIN (SELECT 1 AS foo) AS v;

FROM ... SELECT ...

Перевернутая форма записи, в которой сначала указывается источник данных, а затем — операция.

Примеры

FROM my_table SELECT key, value;

FROM a_table AS a
JOIN b_table AS b
USING (key)
SELECT *;

Представления данных (VIEW)

В YQL реализовано два вида представления данных:

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

Для обращения к представлению (VIEW):

• привязанные к таблице требуют использования специального синтаксиса: [cluster.]`path/to/table` VIEW view_name;
• независимые с точки зрения пользователя выглядят как обычные таблицы.

Если в мета-атрибутах таблицы указан автоматический вызов UDF для преобразования сырых данных в структурированный набор колонок, обратиться к сырым данным можно с помощью специального представления raw, например [cluster.]`path/to/table` VIEW raw.

Примеры:

USE some_cluster;
SELECT *
FROM my_table VIEW my_view;

Явно создаваемые временные (анонимные) таблицы

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

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

Эта функциональность позволяет не заботиться о конфликтах в путях временных таблиц между параллельно работающими операциями, а также не удалять их явно в конце запроса.

Примеры:

INSERT INTO @my_temp_table
SELECT * FROM my_input_table ORDER BY value;

COMMIT;

SELECT * FROM @my_temp_table WHERE value = "123"
UNION ALL
SELECT * FROM @my_temp_table WHERE value = "456";

В имени временной таблицы может использоваться именованное выражение:

$tmp_name = "my_temp_table";

INSERT INTO @$tmp_name
SELECT 1 AS one, 2 AS two, 3 AS three;

COMMIT;

SELECT * FROM @$tmp_name;

FROM AS_TABLE

Обращение к именованным выражениям как к таблицам с помощью функции AS_TABLE.

AS_TABLE($variable) позволяет использовать значение $variable в качестве источника данных для запроса. При этом переменная $variable должна иметь тип List<Struct<...>>.

Пример

$data = AsList(
    AsStruct(1u AS Key, "v1" AS Value),
    AsStruct(2u AS Key, "v2" AS Value),
    AsStruct(3u AS Key, "v3" AS Value));

SELECT Key, Value FROM AS_TABLE($data);