Запросы, синтаксис, UDF

Как сделать запрос по логам за последний день/неделю/месяц или какой-либо другой относительный период времени?

Если подставлять даты в текст запроса перед запуском неудобно, то можно сделать следующим образом:

1. Пишем UDF на Python или C++ с сигнатурой (String)->Bool. По имени таблицы она должна вернуть true только для попадающих в нужный диапазон, ориентируясь на текущее время или дату, полученные средствами выбранного языка программирования.
2. Передаём эту UDF в функцию FILTER вместе с префиксом до директории с логами.
3. Используем результат работы FILTER в любом месте, где ожидается путь к входным данным (FROM, PROCESS, REDUCE)

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

Как сделать фильтрацию по большому списку значений?

Можно, конечно, сгенерировать гигантское условие в WHERE, но лучше:

• Сгенерировать текстовый файл со списком значений по одному на строку, а затем приложить его к запросу и воспользоваться IN в сочетании с функцией ParseFile.
• Сформировать таблицу из значений, по которым нужно сделать фильтрацию, и выполнить LEFT SEMI JOIN с этой таблицей.
• Для строк можно сгенерировать регулярное выражение, проверяющее их через «или», и воспользоваться ключевым словом REGEXP, либо одной из предустановленных функций для работы с регулярными выражениями (Hyperscan, Pire или Re2).

Чем отличаются MIN, MIN_BY и MIN_OF? (или MAX, MAX_BY и MAX_OF)

• MIN(column) — агрегатная функция из SQL-стандарта, возвращающая минимальное значение, найденное в указанной колонке.
• MIN_BY(result, by) — нестандартная агрегатная функция, которая, в отличие от предыдущей, возвращает не само минимальное значение, а какое-то другое значение из строк(-и), где нашелся минимум.
• MIN_OF(a, b, c, d) — встроенная функция, которая возвращает среди N значений-аргументов минимальное.

Как обратиться к колонке, в имени которой есть знак минус или другой спецсимвол?

Обернуть имя колонки в backticks по аналогии с именами таблиц:

SELECT `field-with-minus` FROM `table`;

Что такое колонка _other?

Это виртуальная колонка. Она создаётся только YQL-движком (система YTsaurus про неё ничего не знает) и только для таблиц с нестрогой схемой. В этой колонке доступны столбцы таблицы, которые изначально в схеме не были заданы. Колонка имеет тип Dict<String,String>.

Есть несколько распространённых способов получить таблицу, в которой есть такая колонка:

• Например, создать таблицу без схемы и затем отсортировать её. Система YTsaurus выставит отсортированной таблице схему, в которой будут явно указаны только имена столбцов, по которым производилась сортировка. А если прочитать сортированную таблицу в YQL, то остальные столбцы будут доступны в поле _other.
• Другой способ — создать таблицу с нестрогой схемой и добавить в эту таблицу колонки, которые изначально не были явно указаны в схеме. Ниже приведён пример этого кейса.

Пример:

# Создадим таблицу с нестрогой схемой. В схеме явно заданы две колонки: 'id' и 'text'.
$ yt create table //tmp/table_not_strict --attributes '{schema = <strict=%false>[{name = id; type = int64}; {name = text; type = string}]}'

# Теперь добавим в таблицу данные, указав дополнительную колонку 'newColumn', которой нет в схеме.
$ echo '{ "id": 0, "text": "Hello, World!", "newColumn": "This column is not in schema" } ' | yt write-table //tmp/table_not_strict --format json

Если прочитать эту таблицу в YQL, поле newColumn будет доступно в колонке _other:

SELECT * FROM `//tmp/table_not_strict` AS t;

Получить значение колонки newColumn можно несколькими способами:

SELECT
    WeakField(t.newColumn, "String")
FROM `//tmp/table_not_strict` AS t;

или

SELECT
    t._other["newColumn"]
FROM `//tmp/table_not_strict` AS t;

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

Как создать пустую таблицу со схемой из другой таблицы?

-- Определяем тип записи исходных данных
$s = select * from `path/to/source_table`;
$s = process $s;

-- Создаём пустую таблицу с нужной схемой
INSERT INTO `path/to/my_destination_table` WITH TRUNCATE
SELECT * FROM as_table(ListCreate(ListItemType(TypeOf($s))))

Как не распаковывать protobuf, а работать со строкой?

YQL умеет распаковывать protobuf сообщения. Рекомендуется настраивать автоматическую распаковку через метаданные таблиц. В некоторых случаях вместо распакованного сообщения необходимо работать с исходной строкой. Для этого стоит воспользоваться специальным представлением raw:

SELECT *
FROM `path/to/my_table` VIEW raw;

Как превратить строку вида "a=b,c=d" в словарь?

Для этого достаточно применить предустановленную функцию из модуля Dsv UDF:

$str = "a=b,c=d";
SELECT Dsv::Parse($str, ",");

/*
Результат:
{
    "a": "b",
    "c": "d"
}
*/

Как удалить из таблицы дубликаты строк, не перечисляя все колонки?

SELECT * WITHOUT g FROM (
    SELECT Some(TableRow()) AS s
    FROM `path/to/table` AS t
    GROUP BY Digest::Sha256(StablePickle(TableRow())) AS g
)
FLATTEN COLUMNS;

Как скопировать пользовательские атрибуты на выходную таблицу

$input = "some/input/path";
$output = "destination/path";

-- Фильтруем пользовательские атрибуы, начинающиеся с подчеркивания, и исключаем некоторые системные YQL атрибуты
$user_attr = ($attr) -> {
    RETURN ListFilter(
        Yson::ConvertToStringList($attr.user_attribute_keys),
        ($name) -> {
            RETURN StartsWith($name, "_") AND $name NOT IN ["_yql_op_id", "_yql_row_spec", "_yql_runner"];
        }
    );
};

-- Добавляем префикс // к пути
$yt_path = ($path) -> {
    RETURN IF(StartsWith($path, "//"), $path, "//" || $path);
};

-- Создаём список атрибутов для копирования
$attribute_keys = (
    SELECT String::JoinFromList($user_attr(Attributes), ";")
    FROM Folder(Substring($input, NULL, RFind($input, "/")), "user_attribute_keys")
    WHERE $yt_path(Path) == $yt_path($input)
);

-- Читаем пользовательские атрибуты
$attributes = (
    SELECT CAST(Yson::SerializePretty(Attributes) AS String) as attrs
    FROM Folder(Substring($input, NULL, RFind($input, "/")), $attribute_keys)
    WHERE $yt_path(Path) == $yt_path($input)
);

-- Форсируем скалярный контекст
$attributes = EvaluateExpr($attributes);

INSERT INTO $output WITH (TRUNCATE, USER_ATTRS=$attributes)
SELECT *
FROM (
    SELECT *
    FROM $input
    LIMIT 5
)

JSON и YSON

Как превратить строку таблицы в JSON?

Для этого потребуются предустановленные функции сериализации и стандартная функция TableRow() для обращения ко всей строке:

USE <cluster-name>;
SELECT Yson::SerializeJson(Yson::From(TableRow()))
FROM `home/yql/tutorial/users`;

Как получить значение по ключу из JSON'а / YSON'а?

При работе с JSON'ом / YSON'ом стоит помнить:

• Несериализованное поле этих форматов нельзя вывести в браузере или сохранить в таблицу — возникает ошибка вида Expected persistable data, but got: List<Struct<'column0':Resource<'Yson.Node'>>>;
• Не стоит преобразовывать в словарь, если нужно получить значение только по одному ключу.

-- Тип my_column — Yson

SELECT Yson::Serialize(my_json_column.myKey) AS my_column
FROM my_table;

Если требуется конкретный тип данных, то можно воспользоваться функцией Yson::ConvertTo...:

-- Тип my_column — String

SELECT Yson::ConvertToString(my_json_column.myKey) AS my_column
FROM my_table;

Если исходный тип колонки строка, то можно воспользоваться функцией Yson::Parse...:

SELECT Yson::Serialize(Yson::Parse(my_yson_column)) AS my_yson;

-- В результате получается Yson, при работе с которым Yson::Parse подставляется автоматически

SELECT Yson::Serialize(my_yson.myKey) AS my_key_value;

Если требуется список из всех значений по ключу, то можно воспользоваться функцией ListMap:

SELECT
    ListMap(
        Yson::ConvertToList(my_yson_column),
        ($x) -> { RETURN Yson::Serialize($x.foo) }
    );

-- Результат выполнения — список строк

Пример получения элемента списка:

SELECT Yson::ConvertToString(Yson::ConvertToList(my_yson_column)[0].foo);

-- Результат выполнения — поле конкретного элемента списка

Как вертикально развернуть словарь/список из таблицы?

В случае, когда контейнер, который необходимо развернуть — список, стоит использовать FLATTEN LIST BY:

SELECT
    parents,
    name,
    geo_parents_list
FROM <cluster-name>.`home/yql/tutorial/users` VIEW complex
FLATTEN LIST BY geo_parents_list AS parents;

Если же список находится в колонке с типом Yson, то сначала необходимо преобразовать его в тип List. Например, так:

SELECT *
FROM (
    SELECT
        ListMap(
            Yson::ConvertToList(my_yson_column),
            ($x) -> { RETURN Yson::Serialize($x.foo) }
        ) AS my_list
    FROM my_table)
FLATTEN LIST BY my_list;

FLATTEN DICT BY следует использовать, когда развернуть нужно словарь:

SELECT
    dsv.dict.0 AS key,
    dsv.dict.1 AS value
FROM <cluster-name>.`home/yql/tutorial/users_dsv` AS dsv
FLATTEN DICT BY dict;

Подробнее про конструкцию FLATTEN BY можно прочитать в соответствующем разделе.

Как передать данные одной таблицы (справочника) в виде списка для обработки другой таблицы

Если размер таблицы справочника не превышает лимита на размер одной строки таблицы, то можно воспользоваться скалярным контекстом и функцией AGGREGATE_LIST.

$data = SELECT AGGREGATE_LIST(TableRow()) FROM small_table; -- скалярный контекст при использовании $data в выражении, тип Optional<List>

SELECT MyModule::MyUdf($data ?? [])(x) FROM big_table;

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

$data = SELECT * FROM medium_table;
$data = PROCESS $data; -- включаем табличный контекст, $data будет далее иметь тип List<Struct>

SELECT MyModule::MyUdf($data)(x) FROM big_table;