YTsaurus Flow

", "Row": 1, "Column": 19) ``` ## WithIssue {#with_issue} #### Signature ```yql WithIssue(x, File:String, Row: Uint32, Column: Uint32, Message: String)->TypeOf(x) ``` This function is available starting from version [2026.01](https://ytsaurus.tech/docs/changelog/2026.01.md). It adds a custom frame with a specified position and message to the validation stack. It has no effect if the request passes validation successfully. Typically used for linking to a DSL file. #### Examples ```yql -- Expression decoration SELECT WithIssue( 1 + "foo", -- to get an error "my_file", 100, 200, "my_message" ); -- Subquery decoration DEFINE SUBQUERY $sub() AS SELECT 1 + "foo"; -- to get an error END DEFINE; $sub = ($world)->{ return WithIssue( $sub($world), "my_file", 100, 200, "my_message" ); }; PROCESS $sub(); # Functions for JSON **JSON** is a lightweight [data-interchange format](https://www.json.org). It's represented by the `Json` type in YQL. Unlike relational tables, JSON can store data with no schema defined. Here is an example of a valid JSON object: ```json [ { "name": "Jim Holden", "age": 30 }, { "name": "Naomi Nagata", "age": "twenty years old" } ] ``` Despite the fact that the `age` field in the first object is of the `Number` type (`"age": 21`) and in the second object its type is `String` (`"age": "twenty years old"`), this is a fully valid JSON object. To work with JSON, YQL implements a subset of the [SQL support for JavaScript Object Notation (JSON)](https://www.iso.org/standard/67367.html) standard, which is a part of the generally adopted ANSI SQL standard. ## Cookbook ```yql $json = CAST(@@{ "friends": [ { "name": "James Holden", "age": 35 }, { "name": "Naomi Nagata", "age": 30 } ] }@@ AS Json); SELECT JSON_EXISTS($json, "$.friends[*].name"), -- True, JSON_VALUE($json, "$.friends[0].age"), -- "35" (Utf8? type) JSON_QUERY($json, "$.friends[0]"); -- {"name": "James Holden", "age": 35} ``` ### Accessing a database field Table data can be stored in JSON or as a string. `JSON_*` functions expect JSON as input. For the `String->JSON` conversion, use the `CAST` function, for example, `CAST (my_string AS JSON)`. ## JsonPath Values inside JSON objects are accessed using a query language called JsonPath. All functions for JSON accept a JsonPath query as an argument. Let's look at an example. Suppose we have a JSON object like: ```json { "comments": [ { "id": 123, "text": "A whisper will do, if it's all that you can manage." }, { "id": 456, "text": "My life has become a single, ongoing revelation that I haven’t been cynical enough." } ] } ``` Then, to get the text of the second comment, we can write the following JsonPath query: ```yql $.comments[1].text ``` In this query: 1. `$` is a way to access the entire JSON object. 2. `$.comments` accesses the `comments` key of the JSON object. 3. `$.comments[1]` accesses the second element of the JSON array (element numbering starts from 0). 4. `$.comments[1].text` accesses the `text` key of the JSON object. 5. Query execution result: `"My life has become a single, ongoing revelation that I haven’t been cynical enough."` ### Quick reference | Operation | Example | |-----------------------------------------|-------------------------------------------------| | Retrieving a JSON object key | `$.key` | | Retrieving all JSON object keys | `$.*` | | Accessing an array element | `$[25]` | | Retrieving an array subsegment | `$[2 to 5]` | | Accessing the last array element | `$[last]` | | Accessing all array elements | `$[*]` | | Unary operations | `- 1` | | Binary operations | `(12 * 3) % 4 + 8` | | Accessing a variable | `$variable` | | Logical operations | `(1 > 2) || (3 <= 4) && ("string" == "another")| | | Matching a regular expression | `$.name like_regex "^[A-Za-z]+$"` | | Checking the string prefix | `$.name starts with "Bobbie"` | | Checking if a path exists | `exists ($.profile.name)` | | Checking a Boolean expression for null | `($.age > 20) is unknown` | | Filtering values | `$.friends ? (@.age >= 18 && @.gender == "male")` | | Getting the value type | `$.name.type()` | | Getting the array size | `$.friends.size()` | | Converting a string to a number | `$.number.double()` | | Rounding up a number | `$.number.ceiling()` | | Rounding down a number | `$.number.floor()` | | Returning the absolute value | `$.number.abs()` | | Getting key-value pairs from an object | `$.profile.keyvalue()` | ### Data model The result of executing all JsonPath expressions is a sequence of JSON values. For example: - The result of executing the `"Bobbie"` expression is a sequence with the only element `"Bobbie"`. Its length is 1. - The result of executing the `$` expression (that takes the entire JSON object) in JSON `[1, 2, 3]` is `[1, 2, 3]`. A sequence of 1 element of the array `[1, 2, 3]` - The result of executing the `$[*]` expression (retrieving all array elements) in JSON `[1, 2, 3]` is `1, 2, 3`. A sequence of three items:`1`, `2`, and `3` If the input sequence consists of multiple values, some operations are performed for each element (for example, accessing a JSON object key). However, other operations require a sequence of one element as input (for example, binary arithmetic operations). The behavior of a specific operation is described in the corresponding section of the documentation. ### Execution mode JsonPath supports two execution modes, `lax` and `strict`. Setting the mode is optional. By default, `lax`. The mode is specified at the beginning of a query. For example, `strict $.key`. The behavior for each mode is described in the corresponding sections with JsonPath operations. #### Auto unpacking of arrays When accessing a JSON object key in `lax` mode, arrays are automatically unpacked. ##### Example ```json [ { "key": 123 }, { "key": 456 } ] ``` The `lax $.key` query is successful and returns `123, 456`. As `$` is an array, it's automatically unpacked and accessing the key of the `$.key` JSON object is executed for each element in the array. The `strict $.key` query returns an error. The `strict` mode doesn't support automatic unpacking of arrays. `$` is an array rather than an object. That's why you can't access the object key `$.key`. You can fix this by writing `strict $[*].key`. Unpacking is only 1 level deep. In the event of nested arrays, only the outermost one is unpacked. #### Wrapping values in arrays When accessing an array element in `lax` mode, JSON values are automatically wrapped in an array. ##### Example ```json { "name": "Avasarala" } ``` The `lax $[0].name` query is successful and returns `"Avasarala"`. As `$` isn't an array, it's automatically wrapped in an array of length 1. Accessing the first element `$[0]` returns the source JSON object where the `name` key is taken. The `strict $[0].name` query returns an error. The `strict` mode does not support automated wrapping into an array. `$` is an object rather than an array. That's why you can't access the item `$[0]`. You can fix this by writing `strict $.name`. #### Handling errors Some errors are converted to an empty result when a query is executed in `lax` mode. ### Literals Values of some types can be specified in a JsonPath query using literals: | Type | Example | | ------------------ | ---------------- | | Numbers | `42`, `-1.23e-5` | | Boolean values | `false`, `true` | | Null | `null` | | Stings | `"Belt"` | ### Accessing JSON object keys JsonPath supports accessing JSON object keys, such as `$.session.user.name`. {% note info %} Accessing keys without quotes is only supported for keys that start with an English letter or underscore and only contain English letters, underscores, numbers, and a dollar sign. Use quotes for all other keys. For example, `$.profile."this string has spaces"` or `$.user."42 is the answer"` {% endnote %} For each value from the input sequence: 1. If the value is an array, it's automatically unpacked in `lax` mode. 2. If the value isn't a JSON object or if it is and the specified key is missing from this JSON object, a query executed in `strict` mode fails. In `lax` mode, an empty result is returned for this value. The expression execution result is the concatenation of the results for each value from the input sequence. #### Example ```json { "name": "Amos", "friends": [ { "name": "Jim" }, { "name": "Alex" } ] } ``` | | `lax` | `strict` | |------------------|------------------|----------| | `$.name` | `"Amos"` | `"Amos"` | | `$.surname` | Empty result | Error | | `$.friends.name` | `"Jim", "Alex"` | Error | ### Accessing all JSON object keys JsonPath supports accessing all JSON object keys at once: `$.*`. For each value from the input sequence: 1. If the value is an array, it's automatically unpacked in `lax` mode. 2. If the value isn't a JSON object, a query executed in `strict` mode fails. In `lax` mode, an empty result is returned for this value. The expression execution result is the concatenation of the results for each value from the input sequence. #### Example ```json { "profile": { "id": 123, "name": "Amos" }, "friends": [ { "name": "Jim" }, { "name": "Alex" } ] } ``` | | `lax` | `strict` | | ------------- | --------------- | ------------- | | `$.profile.*` | `123, "Amos"` | `123, "Amos"` | | `$.friends.*` | `"Jim", "Alex"` | Error | ### Accessing an array element JsonPath supports accessing array elements: `$.friends[1, 3 to last - 1]`. For each value from the input sequence: 1. If the value isn't an array, a query executed in `strict` mode fails. In `lax` mode, values are automatically wrapped in an array. 2. The `last` keyword is replaced with the array's last index. Using `last` outside of accessing the array is an error in both modes. 3. The specified indexes are calculated. Each of them must be a single number, otherwise the query fails in both modes. 4. If the index is a fractional number, it's rounded down. 5. If the index goes beyond the array boundaries, the query executed in `strict` mode fails. In `lax` mode, this index is ignored. 6. If a segment is specified and its start index is greater than the end index (for example, `$[20 to 1]`), the query fails in `strict` mode. In `lax` mode, this segment is ignored. 7. All elements by the specified indexes are added to the result. Segments include **both ends**. #### Examples ```json [ { "name": "Camina", "surname": "Drummer" }, { "name": "Josephus", "surname": "Miller" }, { "name": "Bobbie", "surname": "Draper" }, { "name": "Julie", "surname": "Mao" } ] ``` | | `lax` | `strict` | | ----------------------------- | ------------------------------- | ------------------------------- | | `$[0].name` | `"Camina"` | `"Camina"` | | `$[1, 2 to 3].name` | `"Josephus", "Bobbie", "Julie"` | `"Josephus", "Bobbie", "Julie"` | | `$[last - 2].name` | `"Josephus"` | `"Josephus"` | | `$[2, last + 200 to 50].name` | `"Bobbie"` | Error | | `$[50].name` | Empty result | Error | ### Accessing all array elements JsonPath supports accessing all array elements at once: `$[*]`. For each value from the input sequence: 1. If the value isn't an array, a query executed in `strict` mode fails. In `lax` mode, values are automatically wrapped in an array. 2. All elements of the current array are added to the result. #### Examples ```json [ { "class": "Station", "title": "Medina" }, { "class": "Corvette", "title": "Rocinante" } ] ``` | | `lax` | `strict` | | ------------------- | ------------------------- | ----------------------- | | `$[*].title` | `"Medina", "Rocinante"` | `"Medina", "Rocinante"` | | `lax $[0][*].class` | `"Station"` | Error | Let's analyze the last example step by step: 1. `$[0]` returns the first element of the array, that is `{"class": "Station", "title": "Medina"}` 2. `$[0][*]` expects an array for input, but an object was input instead. It's automatically wrapped in an array as `[ {"class": "Station", "title": "Medina"} ]` 3. Now, `$[0][*]` can be executed and returns all elements of the array, that is `{"class": "Station", "title": "Medina"}` 4. `$[0][*]`.class returns the `class` field value: `"Station"`. ### Arithmetic operations {% note info %} All arithmetic operations work with numbers as with Double. You might [lose precision](https://docs.oracle.com/cd/E19957-01/806-3568/ncg_goldberg.html) during calculations. {% endnote %} #### Unary operations JsonPath supports unary `+` and `-`. A unary operation applies to all values from the input sequence. If a unary operation's input is a value that isn't a number, a query fails in both modes. ##### Example ```json [1, 2, 3, 4] ``` The `strict -$[*]` query is successful and returns `-1, -2, -3, -4`. The `lax -$` query fails as `$` is an array and not a number. #### Binary operations JsonPath supports binary arithmetic operations (in descending order of priority): 1. Multiplication `*`, dividing floating-point numbers `/`, and taking the remainder `%` (works as the `MOD` function in `SQL`). 2. Addition `+`, subtraction `-`. You can change the order of operations using parentheses. If each argument of a binary operation is not a single number or a number is divided by 0, the query fails in both modes. ##### Examples - `(1 + 2) * 3` returns `9` - `1 / 2` returns `0.5` - `5 % 2` returns `1` - `1 / 0` fails - If JSON is `[-32.4, 5.2]`, the `$[0] % $[1]` query returns `-1.2` - If JSON is `[1, 2, 3, 4]`, the `lax $[*] + $[*]` query fails as the `$[*]` expression execution result is `1, 2, 3, 4`, that is multiple numbers. A binary operation only requires one number for each of its arguments. ### Boolean values Unlike some other programming languages, Boolean values in JsonPath are not only `true` and `false`, but also `null` (uncertainty). JsonPath considers any values received from a JSON document to be non-Boolean. For example, a query like `! $.is_valid_user` (a logical negation applied to the `is_valid_user`) field is syntactically invalid because the `is_valid_user` field value is not Boolean (even when it actually stores `true` or `false`). The correct way to write this kind of query is to explicitly use a comparison with a Boolean value, such as `$.is_valid_user == false`. ### Logical operations JsonPath supports some logical operations for Boolean values. The arguments of any logical operation must be a single Boolean value. All logical operations return a Boolean value. #### Logical negation, ! Truth table: | `x` | `!x` | | ------- | ------- | | `true` | `false` | | `false` | `true` | | `null` | `null` | #### Logical AND, && In the truth table, the first column is the left argument, the first row is the right argument, and each cell is the result of using the Logical AND both with the left and right arguments: | `&&` | `true` | `false` | `null` | | ------- | ------- | ------- | ------- | | `true` | `true` | `false` | `null` | | `false` | `false` | `false` | `false` | | `null` | `null` | `false` | `null` | #### Logical OR, || In the truth table, the first column is the left argument, the first row is the right argument, and each cell is the result of using the logical OR with both the left and right arguments: | `\|\|` | `true` | `false` | `null` | | ------- | ------- | ------- | ------- | | `true` | `true` | `true` | `true` | | `false` | `true` | `false` | `null` | | `null` | `true` | `null` | `null` | ##### Examples - `! (true == true)`, the result is `false` - `(true == true) && (true == false)`, the result is `false` - `(true == true) || (true == false)`, the result is `true` ### Comparison operators JsonPath implements comparison operators for values: - Equality, `==` - Not equal to, `!=` and `<>` - Less than and less than or equal to, `<` and `<=` - Greater than and greater than or equal to, `>` and `>=` All comparison operators return a Boolean value. Both operator arguments support multiple values. If an error occurs when calculating the operator arguments, it returns `null`. In this case, the JsonPath query execution continues. The arrays of each of the arguments are automatically unpacked. After that, for each pair where the first element is taken from the sequence of the left argument and the second one from the sequence of the right argument: 1. The elements of the pair are compared 2. If an error occurs during the comparison, the flag set is `ERROR` 3. If the comparison result is true, the flag set is `FOUND` 4. If either the `ERROR` or `FOUND` flag is set and the query is executed in `lax` mode, no more pairs are analyzed. If the pair analysis results in: 1. The `ERROR` flag is set, the operator returns `null` 2. The `FOUND` flag is set, the operator returns `true` 3. Otherwise, it returns `false` We can say that this algorithm considers all pairs from the Cartesian product of the left and right arguments, trying to find the pair whose comparison returns true. Elements in a pair are compared according to the following rules: 1. If the left or right argument is an array or object, the comparison fails. 2. `null == null` returns true 3. In all other cases, if one of the arguments is `null`, false is returned. 4. If the left and right arguments are of different types, the comparison fails. 5. Strings are compared byte by byte. 6. `true` is considered greater than `false` 7. Numbers are compared with the accuracy of `1e-20` #### Example Let's take a JSON document as an example ```json { "left": [1, 2], "right": [4, "Inaros"] } ``` and analyze the steps for executing the `lax $.left < $.right` query: 1. Auto unpacking of arrays in the left and right arguments. As a result, the left argument is the sequence `1, 2` and the right argument is `4, "Iranos"` 2. Let's take the pair `(1, 4)`. The comparison is successful as `1 < 4` is true. Set the flag `FOUND` 3. Since the query is executed in `lax` mode and the `FOUND` flag is set, we aren't analyzing any more pairs. 4. Since we have the `FOUND` flag set, the operator returns true. Let's take the same query in a different execution mode: `strict $.left < $.right`: 1. Auto unpacking of arrays in the left and right arguments. As a result, the left argument is the sequence `1, 2` and the right argument is `4, "Iranos"` 2. Let's take the pair `(1, 4)`. The comparison is successful as `1 < 4` is true. Set the flag `FOUND` 3. Let's take the pair `(2, 4)`. The comparison is successful as `2 < 4` is true. Set the flag `FOUND` 4. Let's take the pair `(1, "Iranos")`. The comparison fails as a number can't be compared with a string. Set the flag `ERROR` 5. Let's take the pair `(2, "Iranos")`. The comparison fails as a number can't be compared with a string. Set the flag `ERROR` 6. Since we have the `ERROR` flag set, the operator returns `null` ### Predicates JsonPath supports predicates which are expressions that return a Boolean value and check a certain condition. You can use them, for example, in filters. #### `like_regex` The `like_regex` predicate lets you check if a string matches a regular expression. The syntax of regular expressions is the same as in [Hyperscan UDF](https://ytsaurus.tech/docs/udf/list/hyperscan.md) and [REGEXP](https://ytsaurus.tech/docs/syntax/expressions.md#regexp). ###### Syntax ```yql like_regex [flag ] ``` Where: 1. `` is a JsonPath expression with strings to be checked for matching the regular expression. 2. `` is a string with the regular expression 3. `flag ` is an optional section where `` is a string with regular expression execution flags. Supported flags: - `i`: Disable the case sensitivity. ##### Execution Before the check, the input sequence arrays are automatically unpacked. After that, for each element of the input sequence: 1. A check is made to find out if a string matches a regular expression. 2. If the element isn't a string, the flag set is `ERROR` 3. If the check result is true, the flag set is `FOUND` 4. If either the `ERROR` or `FOUND` flag is set and the query is executed in `lax` mode, no more pairs are analyzed. If the pair analysis results in: 1. Setting the `ERROR` flag, the predicate returns `null` 2. Setting the `FOUND` flag, the predicate returns `true` 3. Otherwise, the predicate returns `false` #### Examples 1. `"123456" like_regex "^[0-9]+$"` returns `true` 2. `"123abcd456" like_regex "^[0-9]+$"` returns `false` 3. `"Naomi Nagata" like_regex "nag"` returns `false` 4. `"Naomi Nagata" like_regex "nag" flag "i"` returns `true` #### `starts with` The `starts with` predicate lets you check if one string is a prefix of another. ###### Syntax ```yql starts with ``` Where: 1. `` is a JsonPath expression with the string to check. 2. `` is a JsonPath expression with a prefix string. This means that the predicate will check that the `` starts with the `` string. ##### Execution The first argument of the predicate must be a single string. The second argument of the predicate must be a sequence of (possibly, multiple) strings. For each element in a sequence of prefix strings: 1. A check is made for whether "an element is a prefix of an input string" 2. If the element isn't a string, the flag set is `ERROR` 3. If the check result is true, the flag set is `FOUND` 4. If either the `ERROR` or `FOUND` flag is set and the query is executed in `lax` mode, no more pairs are analyzed. If the pair analysis results in: 1. Setting the `ERROR` flag, the predicate returns `null` 2. Setting the `FOUND` flag, the predicate returns `true` 3. Otherwise, the predicate returns `false` ##### Examples 1. `"James Holden" starts with "James"` returns `true` 2. `"James Holden" starts with "Amos"` returns `false` #### `exists` The `exists` predicate lets you check whether a JsonPath expression returns at least one element. ###### Syntax ```yql exists () ``` Where `` is the JsonPath expression to be checked. Parentheses around the expression are required. ##### Execution 1. The passed JsonPath expression is executed. 2. If an error occurs, the predicate returns `null` 3. If an empty sequence is obtained as a result of the execution, the predicate returns `false` 4. Otherwise, the predicate returns `true` ##### Examples Let's take a JSON document: ```json { "profile": { "name": "Josephus", "surname": "Miller" } } ``` 1. `exists ($.profile.name)` returns `true` 2. `exists ($.friends.profile.name)` returns `false` 3. `strict exists ($.friends.profile.name)` returns `null`, because accessing non-existent object keys in `strict` mode is an error. #### `is unknown` The `is unknown` predicate lets you check if a Boolean value is `null`. ###### Syntax ```yql () is unknown ``` Where `` is the JsonPath expression to be checked. Only expressions that return a Boolean value are allowed. Parentheses around the expression are required. ##### Execution 1. If the passed expression returns `null`, the predicate returns `true` 2. Otherwise, the predicate returns `false` ##### Examples 1. `(1 == 2) is unknown` returns `false`. The `1 == 2` expression returned `false`, which is not `null` 2. `(1 == "string") is unknown` returns `true`. The `1 == "string"` expression returned `null`, because strings and numbers can't be compared in JsonPath. ### Filters JsonPath lets you filter values obtained during query execution. An expression in a filter must return a Boolean value. Before filtering, the input sequence arrays are automatically unpacked. For each element of the input sequence: 1. The value of the current filtered `@` object becomes equal to the current element of the input sequence. 2. Executing the expression in the filter 3. If an error occurs during the expression execution, the current element of the input sequence is skipped. 4. If the expression execution result is the only `true` value, the current element is added to the filter result. #### Example Suppose we have a JSON document describing the user's friends ```json { "friends": [ { "name": "James Holden", "age": 35, "money": 500 }, { "name": "Naomi Nagata", "age": 30, "money": 345 } ] } ``` and we want to get a list of friends who are over 32 years old using a JsonPath query. To do this, you can write the following query: ```yql $.friends ? (@.age > 32) ``` Let's analyze the query in parts: - `$.friends` accesses the `friends` array in the JSON document. - `? ( ... )` is the filter syntax. An expression inside the parentheses is called a predicate. - `@` accesses the currently filtered object. In our example, it's the object describing a friend of the user. - `@.age` accesses the `age` field of the currently filtered object. - `@.age > 32` compares the `age` field with the value 32. As a result of the query, only the values for which this predicate returned true remain. The query only returns the first friend from the array of user's friends. Like many other JsonPath operators, filters can be arranged in chains. Let's take a more complex query that selects the names of friends who are older than 20 and have less than 400 currency units: ```yql $.friends ? (@.age > 20) ? (@.money < 400) . name ``` Let's analyze the query in parts: - `$.friends` accesses the `friends` array in the JSON document. - `? (@.age > 20)` is the first filter. Since all friends are over 20, it just returns all the elements of the `friends` array. - `? (@.money < 400)` is the second filter. It only returns the second element of the `friends` array, since only its `money` field value is less than 400. - `.name` accesses the `name` field of filtered objects. The query returns a sequence of a single element: `"Naomi Nagata"`. In practice, it's recommended to combine multiple filters into one if possible. The above query is equivalent to `$.friends ? (@.age > 20 && @.money < 400) . name`. ### Methods JsonPath supports methods that are functions converting one sequence of values to another. The syntax for calling a method is similar to accessing the object key: ```yql $.friends.size() ``` Just like in the case of accessing object keys, method calls can be arranged in chains: ```yql $.numbers.double().floor() ``` #### `type` The `type` method returns a string with the type of the passed value. For each element of the input sequence, the method adds this string to the output sequence according to the table below: | Value type | String with type | | ------------------ | ------------------------ | | Null | `"null"` | | Boolean value | `"boolean"` | | Number | `"number"` | | String | `"string"` | | Array | `"array"` | | Objects | `"object"` | ##### Examples 1. `"Naomi".type()` returns `"string"` 2. `false.type()` returns `"boolean"` #### `size` The `size` method returns the size of the array. For each element of the input sequence, the method adds the following to the output sequence: 1. The size of the array if the element type is an array. 2. For all other types (including objects), it adds `1` #### Examples Let's take a JSON document: ```json { "array": [1, 2, 3], "object": { "a": 1, "b": 2 }, "scalar": "string" } ``` And queries to it: 1. `$.array.size()` returns `3` 2. `$.object.size()` returns `1` 3. `$.scalar.size()` returns `1` #### `double` The `double` method converts strings to numbers. Before its execution, the input sequence arrays are automatically unpacked. All elements in the input sequence must be strings that contain decimal numbers. It's allowed to specify the fractional part and exponent. ##### Examples 1. `"125".double()` returns `125` 2. `"125.456".double()` returns `125.456` 3. `"125.456e-3".double()` returns `0.125456` #### `ceiling` The `ceiling` method rounds up a number. Before its execution, the input sequence arrays are automatically unpacked. All elements in the input sequence must be numbers. ##### Examples 1. `(1.3).ceiling()` returns `2` 2. `(1.8).ceiling()` returns `2` 3. `(1.5).ceiling()` returns `2` 4. `(1.0).ceiling()` returns `1` #### `floor` The `floor` method rounds down a number. Before its execution, the input sequence arrays are automatically unpacked. All elements in the input sequence must be numbers. ##### Examples 1. `(1.3).floor()` returns `1` 2. `(1.8).floor()` returns `1` 3. `(1.5).floor()` returns `1` 4. `(1.0).floor()` returns `1` #### `abs` The `abs` method calculates the absolute value of a number (removes the sign). Before its execution, the input sequence arrays are automatically unpacked. All elements in the input sequence must be numbers. ##### Examples 1. `(0.0).abs()` returns `0` 2. `(1.0).abs()` returns `1` 3. `(-1.0).abs()` returns `1` #### `keyvalue` The `keyvalue` method converts an object to a sequence of key-value pairs. Before its execution, the input sequence arrays are automatically unpacked. All elements in the input sequence must be objects. For each element of the input sequence: 1. Each key-value pair in the element is analyzed. 2. For each key-value pair, an object is generated with the keys `name` and `value` 3. `name` stores a string with the name of the key from the pair. 4. `value` stores the value from the pair. 5. All objects for this element are added to the output sequence. ##### Examples Let's take a JSON document: ```json { "name": "Chrisjen", "surname": "Avasarala", "age": 70 } ``` The `$.keyvalue()` query returns the following sequence for it: ```json { "name": "age", "value": 70 }, { "name": "name", "value": "Chrisjen" }, { "name": "surname", "value": "Avasarala" } ``` ### Variables Functions using JsonPath can pass values into a query. They are called variables. To access a variable, write the `$` character and the variable name: `$variable`. #### Example Let the `planet` variable be equal to ```json { "name": "Mars", "gravity": 0.376 } ``` Then the strict `$planet.name` query returns `"Mars"`. Unlike many programming languages, JsonPath doesn't support creating new variables or modifying existing ones. ## Common arguments All functions for JSON accept: 1. A JSON value (can be an arbitrary `Json` or `Json?` expression) 2. A JsonPath query (must be explicitly specified with a string literal) 3. **(Optional)** `PASSING` section ### PASSING section Lets you pass values to a JsonPath query as variables. ##### Syntax ```yql PASSING AS , AS , ... ``` `` can be of the following types: - Numbers, `Date`, `DateTime`, and `Timestamp` (a `CAST` into `Double` will be made before passing a value to JsonPath) - `Utf8`, `Bool` and `Json` You can set a `` in several ways: - As an SQL name like `variable` - In quotes, for example, `"variable"` #### Example ```yql JSON_VALUE( $json, "$.timestamp - $Now + $Hour" PASSING 24 * 60 as Hour, CurrentUtcTimestamp() as "Now" ) ``` ## JSON_EXISTS {#json_exists} The `JSON_EXISTS` function checks if a JSON value meets the specified JsonPath. #### Syntax ```yql JSON_EXISTS( , , [] [{TRUE | FALSE | UNKNOWN | ERROR} ON ERROR] ) ``` Returned value: `Bool?` Default value: If the `ON ERROR` clause is not specified, the default error-handling behavior is `FALSE ON ERROR`. Behavior: 1. If `` is `NULL` or an empty `Json?`, it returns an empty `Bool?` 2. If an error occurs during JsonPath execution, the returned value depends on the `ON ERROR` section: - `TRUE`: Return `True` - `FALSE`: Return `False` - `UNKNOWN`: Return an empty `Bool?` - `ERROR`: Abort the entire query 3. If the result of JsonPath execution is one or more values, the return value is `True`. 4. Otherwise, `False` is returned. #### Examples ```yql $json = CAST(@@{ "title": "Rocinante", "crew": [ "James Holden", "Naomi Nagata", "Alex Kamai", "Amos Burton" ] }@@ as Json); SELECT JSON_EXISTS($json, "$.title"), -- True JSON_EXISTS($json, "$.crew[*]"), -- True JSON_EXISTS($json, "$.nonexistent"); -- False because JsonPath will return an empty result SELECT -- JsonPath error will return False because FALSE ON ERROR is used by default JSON_EXISTS($json, "strict $.nonexistent"); SELECT -- JsonPath error, the entire YQL query fails with an error JSON_EXISTS($json, "strict $.nonexistent" ERROR ON ERROR); ``` ## JSON_VALUE {#json_value} The `JSON_VALUE` function retrieves a scalar value from JSON (anything that isn't an array or object). #### Syntax ```yql JSON_VALUE( , , [] [RETURNING ] [{ERROR | NULL | DEFAULT } ON EMPTY] [{ERROR | NULL | DEFAULT } ON ERROR] ) ``` Returned value: `?` Default values: 1. If the `ON EMPTY` section isn't specified, the section used is `NULL ON EMPTY` 2. If the `ON ERROR` section isn't specified, the section used is `NULL ON ERROR` 3. If the `RETURNING` section is omitted, it uses `Utf8` as `` Behavior: 1. If `` is `NULL` or an empty `Json?`, it returns an empty `?` 2. If an error occurs, the returned value depends on the `ON ERROR` section: - `NULL`: Return an empty `?` - `ERROR`: Abort the entire query - `DEFAULT `: Return `` with a prior `CAST` to the type `?`. If the `CAST` fails, the entire query fails, too. 3. If the JsonPath execution result is empty, the returned value depends on the `ON EMPTY` section: - `NULL`: Return an empty `?` - `ERROR`: Abort the entire query - `DEFAULT `: Return `` with a prior `CAST` to the type `?`. If the `CAST` fails, the behavior matches the `ON ERROR` section. 4. If the result of JsonPath execution is a single value, then: - If the `RETURNING` section isn't specified, the value is converted to `Utf8`. - Otherwise, the `CAST` function is run to convert the value to ``. If the `CAST` fails, the behavior matches the `ON ERROR` section. In this case, the value from JSON must match the `` type. 5. Return the result Correlation between JSON and YQL types: - JSON Number: Numeric types, `Date`, `DateTime`, and `Timestamp` - JSON Bool - `Bool` - JSON String: `Utf8` and `String` Errors executing `JSON_VALUE` are as follows: - Errors evaluating JsonPath - The result of JsonPath execution is a number of values or a non-scalar value. - The type of value returned by JSON doesn't match the expected one. The `RETURNING` section supports such types as numbers, `Date`, `DateTime`, `Timestamp`, `Utf8`, `String`, and `Bool`. #### Examples ```yql $json = CAST(@@{ "friends": [ { "name": "James Holden", "age": 35 }, { "name": "Naomi Nagata", "age": 30 } ] }@@ as Json); SELECT JSON_VALUE($json, "$.friends[0].age"), -- "35" (Utf8? type) JSON_VALUE($json, "$.friends[0].age" RETURNING Uint64), -- 35 (Uint64? type) JSON_VALUE($json, "$.friends[0].age" RETURNING Utf8); -- an empty Utf8? because an error has occurred. JSON type of the Number value mismatches the Utf8 string type SELECT -- "empty" (String? type) JSON_VALUE( $json, "$.friends[50].name" RETURNING String DEFAULT "empty" ON EMPTY ); SELECT -- 20 (Uint64? type). The result of JsonPath execution is empty, but the default value -- from the ON EMPTY section cannot be cast to Uint64. -- That's why the value from ON ERROR is used JSON_VALUE( $json, "$.friends[50].age" RETURNING Uint64 DEFAULT -1 ON EMPTY DEFAULT 20 ON ERROR ); ``` ## JSON_QUERY {#json_query} The `JSON_QUERY` function lets you retrieve arrays and objects from JSON. #### Syntax ```yql JSON_QUERY( , , [] [WITHOUT [ARRAY] | WITH [CONDITIONAL | UNCONDITIONAL] [ARRAY] WRAPPER] [{ERROR | NULL | EMPTY ARRAY | EMPTY OBJECT} ON EMPTY] [{ERROR | NULL | EMPTY ARRAY | EMPTY OBJECT} ON ERROR] ) ``` Returned value: `Json?` Default values: 1. If the `ON EMPTY` section isn't specified, the section used is `NULL ON EMPTY` 2. If the `ON ERROR` section isn't specified, the section used is `NULL ON ERROR` 3. If the `WRAPPER` section isn't specified, the section used is `WITHOUT WRAPPER` 4. If the `WITH WRAPPER` section is specified but `CONDITIONAL` or `UNCONDITIONAL` is omitted, then the section used is `UNCONDITIONAL` Behavior: {% note info %} You can't specify the `WITH ... WRAPPER` and `ON EMPTY` sections at the same time. {% endnote %} 1. If `` is `NULL` or an empty `Json?`, it returns an empty `Json?` 2. If the `WRAPPER` section is specified, then: - `WITHOUT WRAPPER` or `WITHOUT ARRAY WRAPPER`: Don't convert the result of JsonPath execution in any way. - `WITH UNCONDITIONAL WRAPPER` or `WITH UNCONDITIONAL ARRAY WRAPPER`: Wrap the result of JsonPath execution in an array. - `WITH CONDITIONAL WRAPPER` or `WITH CONDITIONAL ARRAY WRAPPER`: Wrap the result of JsonPath execution in an array if it isn't the only array or object. 3. If the JsonPath execution result is empty, the returned value depends on the `ON EMPTY` section: - `NULL`: Return an empty `Json?` - `ERROR`: Abort the entire query - `EMPTY ARRAY`: Return an empty JSON array, `[]` - `EMPTY OBJECT`: Return an empty JSON object, `{}` 4. If an error occurs, the returned value depends on the `ON ERROR` section: - `NULL`: Return an empty `Json?` - `ERROR`: Abort the entire query - `EMPTY ARRAY`: Return an empty JSON array, `[]` - `EMPTY OBJECT`: Return an empty JSON object, `{}` 5. Return the result Errors running a `JSON_QUERY`: - Errors evaluating JsonPath - The result of JsonPath execution is a number of values (even after using the `WRAPPER` section) or a scalar value. #### Examples ```yql $json = CAST(@@{ "friends": [ { "name": "James Holden", "age": 35 }, { "name": "Naomi Nagata", "age": 30 } ] }@@ as Json); SELECT JSON_QUERY($json, "$.friends[0]"); -- {"name": "James Holden", "age": 35} SELECT JSON_QUERY($json, "$.friends.name" WITH UNCONDITIONAL WRAPPER); -- ["James Holden", "Naomi Nagata"] SELECT JSON_QUERY($json, "$.friends[0]" WITH CONDITIONAL WRAPPER), -- {"name": "James Holden", "age": 35} JSON_QUERY($json, "$.friends.name" WITH CONDITIONAL WRAPPER); -- ["James Holden", "Naomi Nagata"] ``` # Functions of built-in C++ libraries Many application functions that on the one hand are too specific to become part of the YQL core, and on the other hand might be useful to a wide range of users, are available through built-in C++ libraries. Embedded C++ libraries are linked using UDFs in C++. * [Clickhouse](https://ytsaurus.tech/docs/en/clickhouse.md) * [DateTime](https://ytsaurus.tech/docs/en/datetime.md) * [Digest](https://ytsaurus.tech/docs/en/digest.md) * [Histogram](https://ytsaurus.tech/docs/en/histogram.md) * [Hyperscan](https://ytsaurus.tech/docs/en/hyperscan.md) * [Ip](https://ytsaurus.tech/docs/en/ip.md) * [Math](https://ytsaurus.tech/docs/en/math.md) * [Pcre](https://ytsaurus.tech/docs/en/pcre.md) * [Pire](https://ytsaurus.tech/docs/en/pire.md) * [Protobuf](https://ytsaurus.tech/docs/en/protobuf.md) * [Re2](https://ytsaurus.tech/docs/en/re2.md) * [Streaming](https://ytsaurus.tech/docs/en/streaming.md) * [String](https://ytsaurus.tech/docs/en/string.md) * [Unicode](https://ytsaurus.tech/docs/en/unicode.md) * [Url](https://ytsaurus.tech/docs/en/url.md) * [Yson](https://ytsaurus.tech/docs/en/yson.md) # ClickHouse UDF {% note warning %} This function is not yet available in Query Tracker. We will add it soon. {% endnote %} # Compress UDF и Decompress UDF A set of common data compression and decompression algorithms is listed below: ``` yql Compress::Gzip(String{Flags:AutoMap}, Uint8) -> String Compress::Zlib(String{Flags:AutoMap}, Uint8) -> String Compress::Brotli(String{Flags:AutoMap}, Uint8) -> String Compress::Lzma(String{Flags:AutoMap}, Uint8) -> String Compress::BZip2(String{Flags:AutoMap}, Uint8) -> String Compress::Snappy(String{Flags:AutoMap}) -> String Compress::Zstd(String{Flags:AutoMap}, Uint8) -> String Compress::Xz(String{Flags:AutoMap}) -> String ``` ``` yql Decompress::Gzip(String{Flags:AutoMap}) -> String Decompress::Zlib(String{Flags:AutoMap}) -> String Decompress::Brotli(String{Flags:AutoMap}) -> String Decompress::Lzma(String{Flags:AutoMap}) -> String Decompress::BZip2(String{Flags:AutoMap}) -> String Decompress::Snappy(String{Flags:AutoMap}) -> String Decompress::Zstd(String{Flags:AutoMap}) -> String Decompress::Xz(String{Flags:AutoMap}) -> String ``` ``` yql TryDecompress::Gzip(String{Flags:AutoMap}) -> String? TryDecompress::Zlib(String{Flags:AutoMap}) -> String? TryDecompress::Brotli(String{Flags:AutoMap}) -> String? TryDecompress::Lzma(String{Flags:AutoMap}) -> String? TryDecompress::BZip2(String{Flags:AutoMap}) -> String? TryDecompress::Snappy(String{Flags:AutoMap}) -> String? TryDecompress::Zstd(String{Flags:AutoMap}) -> String? TryDecompress::Xz(String{Flags:AutoMap}) -> String? ``` #### Examples ``` yql SELECT Compress::Brotli("YQL", 8); -- "\x0b\x01\x80\x59\x51\x4c\x03" SELECT Decompress::Brotli("\x0b\x01\x80\x59\x51\x4c\x03"); -- "YQL" SELECT TryDecompress::Brotli("YQL"); -- NULL ``` # DateTime In the DateTime module, there are two internal representation formats: `Resource` for basic types and `Resource` for extended ones. `Resource` and `Resource` store the following date components: * Year (12 bits, unsigned for `Resource`; 19 bits, signed for `Resource`). * Month (4 bits). * Day (5 bits). * Hour (5 bits). * Minute (6 bits). * Second (6 bits). * Microsecond (20 bits). * TimezoneId (16 bits). * DayOfYear (9 bits): Day since the beginning of the year. * WeekOfYear (6 bits): Week since the beginning of the year, January 1 is always in week 1. * WeekOfYearIso8601 (6 bits): Week of the year according to ISO 8601 (the first week is the one that includes January 4). * DayOfWeek (3 bits): Day of the week. If the timezone is not GMT, the components store the local time for the relevant timezone. ## Split {#split} Conversion from a primitive type to an internal representation. It's always successful on a non-empty input. #### List of functions * ```DateTime::Split(Date/TzDate/Datetime/TzDatetime/Timestamp/TzTimestamp{Flags:AutoMap}) -> Resource``` * ```DateTime::Split(Date32/TzDate32/Datetime64/TzDatetime64/Timestamp64/TzTimestamp64{Flags:AutoMap}) -> Resource``` Functions that accept `Resource` or `Resource` as input, can be called directly from the primitive date/time type. An implicit conversion will be made in this case by calling a relevant `Split` function. ## Make... {#make} Making a primitive type from an internal representation. It's always successful on a non-empty input. #### List of functions * `DateTime::MakeDate(Resource{Flags:AutoMap}) -> Date` * `DateTime::MakeTzDate(Resource{Flags:AutoMap}) -> TzDate` * `DateTime::MakeDate32(Resource{Flags:AutoMap}) -> Date32` * `DateTime::MakeTzDate32(Resource{Flags:AutoMap}) -> TzDate32` * `DateTime::MakeDatetime(Resource{Flags:AutoMap}) -> Datetime` * `DateTime::MakeTzDatetime(Resource{Flags:AutoMap}) -> TzDatetime` * `DateTime::MakeDatetime64(Resource{Flags:AutoMap}) -> Datetime64` * `DateTime::MakeTzDatetime64(Resource{Flags:AutoMap}) -> TzDatetime64` * `DateTime::MakeTimestamp(Resource{Flags:AutoMap}) -> Timestamp` * `DateTime::MakeTzTimestamp(Resource{Flags:AutoMap}) -> TzTimestamp` * `DateTime::MakeTimestamp64(Resource{Flags:AutoMap}) -> Timestamp64` * `DateTime::MakeTzTimestamp64(Resource{Flags:AutoMap}) -> TzTimestamp64` #### Examples ```yql SELECT DateTime::MakeTimestamp(DateTime::Split(Datetime("2019-01-01T15:30:00Z"))), -- 2019-01-01T15:30:00.000000Z DateTime::MakeDate(Datetime("2019-01-01T15:30:00Z")), -- 2019-01-01 DateTime::MakeTimestamp(DateTime::Split(TzDatetime("2019-01-01T00:00:00,Europe/Moscow"))), -- 2018-12-31T21:00:00Z (conversion to UTC) DateTime::MakeDate(TzDatetime("2019-01-01T12:00:00,GMT")) -- 2019-01-01 (Datetime -> Date with implicit Split)> ``` ## Get... {#get} Extracting a component from an internal representation. #### List of functions * `DateTime::GetYear(Resource{Flags:AutoMap}) -> Uint16` * `DateTime::GetYear(Resource{Flags:AutoMap}) -> Int32` * `DateTime::GetDayOfYear(Resource/Resource{Flags:AutoMap}) -> Uint16` * `DateTime::GetMonth(Resource/Resource{Flags:AutoMap}) -> Uint8` * `DateTime::GetMonthName(Resource/Resource{Flags:AutoMap}) -> String` * `DateTime::GetWeekOfYear(Resource/Resource{Flags:AutoMap}) -> Uint8` * `DateTime::GetWeekOfYearIso8601(Resource/Resource{Flags:AutoMap}) -> Uint8` * `DateTime::GetDayOfMonth(Resource/Resource{Flags:AutoMap}) -> Uint8` * `DateTime::GetDayOfWeek(Resource/Resource{Flags:AutoMap}) -> Uint8` * `DateTime::GetDayOfWeekName(Resource/Resource{Flags:AutoMap}) -> String` * `DateTime::GetHour(Resource/Resource{Flags:AutoMap}) -> Uint8` * `DateTime::GetMinute(Resource/Resource{Flags:AutoMap}) -> Uint8` * `DateTime::GetSecond(Resource/Resource{Flags:AutoMap}) -> Uint8` * `DateTime::GetMillisecondOfSecond(Resource/Resource{Flags:AutoMap}) -> Uint32` * `DateTime::GetMicrosecondOfSecond(Resource/Resource{Flags:AutoMap}) -> Uint32` * `DateTime::GetTimezoneId(Resource/Resource{Flags:AutoMap}) -> Uint16` * `DateTime::GetTimezoneName(Resource/Resource{Flags:AutoMap}) -> String` #### Examples ```yql $tm = DateTime::Split(TzDatetime("2019-01-09T00:00:00,Europe/Moscow")); SELECT DateTime::GetDayOfMonth($tm) as Day, -- 9 DateTime::GetMonthName($tm) as Month, -- "January" DateTime::GetYear($tm) as Year, -- 2019 DateTime::GetTimezoneName($tm) as TzName, -- "Europe/Moscow" DateTime::GetDayOfWeekName($tm) as WeekDay; -- "Wednesday" ``` ## Update {#update} Updating one or more components in the internal representation. Returns either an updated copy or NULL, if an update produces an invalid date or other inconsistencies. #### List of functions ```yql DateTime::Update(Resource{Flags:AutoMap}, [ Year:Uint16?, Month:Uint8?, Day:Uint8?, Hour:Uint8?, Minute:Uint8?, Second:Uint8?, Microsecond:Uint32?, Timezone:String? ]) -> Resource? DateTime::Update(Resource{Flags:AutoMap}, [ Year:Int32?, Month:Uint8?, Day:Uint8?, Hour:Uint8?, Minute:Uint8?, Second:Uint8?, Microsecond:Uint32?, Timezone:String? ]) -> Resource? ``` #### Examples ```yql $tm = DateTime::Split(Timestamp("2019-01-01T01:02:03.456789Z")); SELECT DateTime::MakeDate(DateTime::Update($tm, 2012)), -- 2012-01-01 DateTime::MakeDate(DateTime::Update($tm, 2000, 6, 6)), -- 2000-06-06 DateTime::MakeDate(DateTime::Update($tm, NULL, 2, 30)), -- NULL (February 30) DateTime::MakeDatetime(DateTime::Update($tm, NULL, NULL, 31)), -- 2019-01-31T01:02:03Z DateTime::MakeDatetime(DateTime::Update($tm, 15 as Hour, 30 as Minute)), -- 2019-01-01T15:30:03Z DateTime::MakeTimestamp(DateTime::Update($tm, 999999 as Microsecond)), -- 2019-01-01T01:02:03.999999Z DateTime::MakeTimestamp(DateTime::Update($tm, "Europe/Moscow" as Timezone)), -- 2018-12-31T22:02:03.456789Z (conversion to UTC) DateTime::MakeTzTimestamp(DateTime::Update($tm, "Europe/Moscow" as Timezone)); -- 2019-01-01T01:02:03.456789,Europe/Moscow ``` ## From... {#from} Getting a Timestamp from the number of seconds/milliseconds/microseconds since the UTC epoch. When the Timestamp limits are exceeded, NULL is returned. #### List of functions * `DateTime::FromSeconds(Uint32{Flags:AutoMap}) -> Timestamp` * `DateTime::FromSeconds64(Int64{Flags:AutoMap}) -> Timestamp64` * `DateTime::FromMilliseconds(Uint64{Flags:AutoMap}) -> Timestamp` * `DateTime::FromMilliseconds64(Int64{Flags:AutoMap}) -> Timestamp64` * `DateTime::FromMicroseconds(Uint64{Flags:AutoMap}) -> Timestamp` * `DateTime::FromMicroseconds64(Int64{Flags:AutoMap}) -> Timestamp64` ## To... {#to} Getting a number of seconds/milliseconds/microseconds since the UTC Epoch from a primitive type. #### List of functions * ```DateTime::ToSeconds(Date/Datetime/Timestamp/TzDate/TzDatetime/TzTimestamp{Flags:AutoMap}) -> Uint32``` * ```DateTime::ToSeconds(Date32/Datetime64/Timestamp64/TzDate32/TzDatetime64/TzTimestamp64{Flags:AutoMap}) -> Int64``` * ```DateTime::ToMilliseconds(Date/Datetime/Timestamp/TzDate/TzDatetime/TzTimestamp{Flags:AutoMap}) -> Uint64``` * ```DateTime::ToMilliseconds(Date32/Datetime64/Timestamp64/TzDate32/TzDatetime64/TzTimestamp64{Flags:AutoMap}) -> Int64``` * ```DateTime::ToMicroseconds(Date/Datetime/Timestamp/TzDate/TzDatetime/TzTimestamp{Flags:AutoMap}) -> Uint64``` * ```DateTime::ToMicroseconds(Date32/Datetime64/Timestamp64/TzDate32/TzDatetime64/TzTimestamp64{Flags:AutoMap}) -> Int64``` #### Examples ```yql SELECT DateTime::FromSeconds(1546304523), -- 2019-01-01T01:02:03.000000Z DateTime::ToMicroseconds(Timestamp("2019-01-01T01:02:03.456789Z")); -- 1546304523456789 ``` ## Interval... {#interval} Conversions between `Interval` and various time units. #### List of functions * `DateTime::ToDays(Interval{Flags:AutoMap}) -> Int32` * `DateTime::ToDays(Interval64{Flags:AutoMap}) -> Int32` * `DateTime::ToHours(Interval{Flags:AutoMap}) -> Int32` * `DateTime::ToHours(Interval64{Flags:AutoMap}) -> Int64` * `DateTime::ToMinutes(Interval{Flags:AutoMap}) -> Int32` * `DateTime::ToMinutes(Interval64{Flags:AutoMap}) -> Int64` * `DateTime::ToSeconds(Interval{Flags:AutoMap}) -> Int32` Until [2025.03](https://ytsaurus.tech/changelog/2025.03.md) * `DateTime::ToSeconds(Interval{Flags:AutoMap}) -> Int64` Since [2025.03](https://ytsaurus.tech/changelog/2025.03.md) * `DateTime::ToSeconds(Interval64{Flags:AutoMap}) -> Int64` * `DateTime::ToMilliseconds(Interval{Flags:AutoMap}) -> Int64` * `DateTime::ToMilliseconds(Interval64{Flags:AutoMap}) -> Int64` * `DateTime::ToMicroseconds(Interval{Flags:AutoMap}) -> Int64` * `DateTime::ToMicroseconds(Interval64{Flags:AutoMap}) -> Int64` * `DateTime::IntervalFromDays(Int32{Flags:AutoMap}) -> Interval` * `DateTime::Interval64FromDays(Int32{Flags:AutoMap}) -> Interval64` * `DateTime::IntervalFromHours(Int32{Flags:AutoMap}) -> Interval` * `DateTime::Interval64FromHours(Int64{Flags:AutoMap}) -> Interval64` * `DateTime::IntervalFromMinutes(Int32{Flags:AutoMap}) -> Interval` * `DateTime::Interval64FromMinutes(Int64{Flags:AutoMap}) -> Interval64` * `DateTime::IntervalFromSeconds(Int32{Flags:AutoMap}) -> Interval` — before version [2025.03](https://ytsaurus.tech/changelog/2025.03.md) * `DateTime::IntervalFromSeconds(Int64{Flags:AutoMap}) -> Interval` — starting from version [2025.03](https://ytsaurus.tech/changelog/2025.03.md) * `DateTime::Interval64FromSeconds(Int64{Flags:AutoMap}) -> Interval64` * `DateTime::IntervalFromMilliseconds(Int64{Flags:AutoMap}) -> Interval` * `DateTime::Interval64FromMilliseconds(Int64{Flags:AutoMap}) -> Interval64` * `DateTime::IntervalFromMicroseconds(Int64{Flags:AutoMap}) -> Interval` * `DateTime::Interval64FromMicroseconds(Int64{Flags:AutoMap}) -> Interval64` AddTimezone doesn't affect the output of ToSeconds() in any way, because ToSeconds() always returns GMT time. You can also create an Interval from a string literal in the format [ISO 8601](https://en.wikipedia.org/wiki/ISO_8601%23Durations). {% note warning %} At the moment, only literals for deterministic intervals are supported. All components greater than week -- M (month) and Y (year) -- are not fixed, since the interval can be defined only considering the particular context (month, year). {% endnote %} #### Examples ```yql SELECT DateTime::ToDays(Interval("PT3000M")), -- 2 DateTime::IntervalFromSeconds(1000000), -- 11 days 13 hours 46 minutes 40 seconds DateTime::ToDays(cast('2018-01-01' as date) - cast('2017-12-31' as date)); --1 ``` ## StartOf... / EndOf... / TimeOfDay {#startof} Get the start (end) of the period including the date/time. If the result is invalid, NULL is returned. If the timezone is different from GMT, then the period start (end) is in the specified time zone. #### List of functions * `DateTime::StartOfYear(Resource{Flags:AutoMap}) -> Resource?` * `DateTime::StartOfYear(Resource{Flags:AutoMap}) -> Resource?` * `DateTime::EndOfYear(Resource{Flags:AutoMap}) -> Resource?` * `DateTime::EndOfYear(Resource{Flags:AutoMap}) -> Resource?` * `DateTime::StartOfQuarter(Resource{Flags:AutoMap}) -> Resource?` * `DateTime::StartOfQuarter(Resource{Flags:AutoMap}) -> Resource?` * `DateTime::EndOfQuarter(Resource{Flags:AutoMap}) -> Resource?` * `DateTime::EndOfQuarter(Resource{Flags:AutoMap}) -> Resource?` * `DateTime::StartOfMonth(Resource{Flags:AutoMap}) -> Resource?` * `DateTime::StartOfMonth(Resource{Flags:AutoMap}) -> Resource?` * `DateTime::EndOfMonth(Resource{Flags:AutoMap}) -> Resource?` * `DateTime::EndOfMonth(Resource{Flags:AutoMap}) -> Resource?` * `DateTime::StartOfWeek(Resource{Flags:AutoMap}) -> Resource?` * `DateTime::StartOfWeek(Resource{Flags:AutoMap}) -> Resource?` * `DateTime::EndOfWeek(Resource{Flags:AutoMap}) -> Resource?` * `DateTime::EndOfWeek(Resource{Flags:AutoMap}) -> Resource?` * `DateTime::StartOfDay(Resource{Flags:AutoMap}) -> Resource?` * `DateTime::StartOfDay(Resource{Flags:AutoMap}) -> Resource?` * `DateTime::EndOfDay(Resource{Flags:AutoMap}) -> Resource?` * `DateTime::EndOfDay(Resource{Flags:AutoMap}) -> Resource?` * `DateTime::StartOf(Resource{Flags:AutoMap}, Interval{Flags:AutoMap}) -> Resource?` * `DateTime::StartOf(Resource{Flags:AutoMap}, Interval64{Flags:AutoMap}) -> Resource?` * `DateTime::EndOf(Resource{Flags:AutoMap}, Interval{Flags:AutoMap}) -> Resource?` * `DateTime::EndOf(Resource{Flags:AutoMap}, Interval64{Flags:AutoMap}) -> Resource?` The `StartOf`/`EndOf` functions are intended for grouping by an arbitrary period within a day. The result differs from the input value only by time components. A period exceeding one day is treated as a day (an equivalent of `StartOfDay`/`EndOfDay`). If a day doesn't include an integer number of periods, the number is rounded to the nearest time from the beginning of the day that is a multiple of the specified period. When the interval is zero, the output is same as the input. A negative interval is treated as a positive one. The `EndOf...` functions are intended for obtaining the latest moment in the same period of time as the specified one. The functions treat periods longer than one day in a different manner than the same-name functions in the old library. The time components are always reset to zero (this makes sense, because these functions are mainly used for grouping by the period). You can also specify a time period within a day: * `DateTime::TimeOfDay(Resource{Flags:AutoMap}) -> Interval` * `DateTime::TimeOfDay(Resource{Flags:AutoMap}) -> Interval64` #### Examples ```yql SELECT DateTime::MakeDate(DateTime::StartOfYear(Date("2019-06-06"))), -- 2019-01-01 (implicit Split here and below) DateTime::MakeDatetime(DateTime::StartOfQuarter(Datetime("2019-06-06T01:02:03Z"))), -- 2019-04-01T00:00:00Z (time components are reset to zero) DateTime::MakeDate(DateTime::StartOfMonth(Timestamp("2019-06-06T01:02:03.456789Z"))), -- 2019-06-01 DateTime::MakeDate(DateTime::StartOfWeek(Date("1970-01-01"))), -- NULL (the beginning of the epoch is Thursday, the beginning of the week is 1969-12-29 that is beyond the limits) DateTime::MakeTimestamp(DateTime::StartOfWeek(Date("2019-01-01"))), -- 2018-12-31T00:00:00Z DateTime::MakeDatetime(DateTime::StartOfDay(Datetime("2019-06-06T01:02:03Z"))), -- 2019-06-06T00:00:00Z DateTime::MakeTzDatetime(DateTime::StartOfDay(TzDatetime("1970-01-01T05:00:00,Europe/Moscow"))), -- NULL (beyond the epoch in GMT) DateTime::MakeTzTimestamp(DateTime::StartOfDay(TzTimestamp("1970-01-02T05:00:00.000000,Europe/Moscow"))), -- 1970-01-02T00:00:00,Europe/Moscow (the beginning of the day in Moscow) DateTime::MakeDatetime(DateTime::StartOf(Datetime("2019-06-06T23:45:00Z"), Interval("PT7H"))), -- 2019-06-06T21:00:00Z DateTime::MakeDatetime(DateTime::StartOf(Datetime("2019-06-06T23:45:00Z"), Interval("PT20M"))), -- 2019-06-06T23:40:00Z DateTime::TimeOfDay(Timestamp("2019-02-14T01:02:03.456789Z")); -- 1 hour 2 minutes 3 seconds 456789 microseconds ``` ## Shift... {#shift} Add/subtract the specified number of units to/from the component in the internal representation and update the other fields. Returns either an updated copy or NULL, if an update produces an invalid date or other inconsistencies. #### List of functions * `DateTime::ShiftYears(Resource{Flags:AutoMap}, Int32) -> Resource?` * `DateTime::ShiftYears(Resource{Flags:AutoMap}, Int32) -> Resource?` * `DateTime::ShiftQuarters(Resource{Flags:AutoMap}, Int32) -> Resource?` * `DateTime::ShiftQuarters(Resource{Flags:AutoMap}, Int32) -> Resource?` * `DateTime::ShiftMonths(Resource{Flags:AutoMap}, Int32) -> Resource?` * `DateTime::ShiftMonths(Resource{Flags:AutoMap}, Int32) -> Resource?` If the resulting number of the day in the month exceeds the maximum allowed, then the `Day` field will accept the last day of the month without changing the time (see examples). #### Examples ```yql $tm1 = DateTime::Split(Datetime("2019-01-31T01:01:01Z")); $tm2 = DateTime::Split(TzDatetime("2049-05-20T12:34:50,Europe/Moscow")); SELECT DateTime::MakeDate(DateTime::ShiftYears($tm1, 10)), -- 2029-01-31T01:01:01 DateTime::MakeDate(DateTime::ShiftYears($tm2, -10000)), -- NULL (beyond the limits) DateTime::MakeDate(DateTime::ShiftQuarters($tm2, 0)), -- 2049-05-20T12:34:50,Europe/Moscow DateTime::MakeDate(DateTime::ShiftQuarters($tm1, -3)), -- 2018-04-30T01:01:01 DateTime::MakeDate(DateTime::ShiftMonths($tm1, 1)), -- 2019-02-28T01:01:01 DateTime::MakeDate(DateTime::ShiftMonths($tm1, -35)), -- 2016-02-29T01:01:01 ``` ## Format {#format} Get a string representation of a time using an arbitrary formatting string. #### List of functions * ```DateTime::Format(String, [alwaysWriteFractionalSeconds:Bool?, writeOffsetWithColon:Bool?]) -> (Resource{Flags:AutoMap}) -> String``` A set of specifiers is implemented for the formatting string: * `%%`: % character; * `%Y`: Variable length (1-6 digits) year with minus for the dates BC; * `%m`: 2-digit month; * `%d`: 2-digit day; * `%H`: 2-digit hour; * `%M`: 2-digit minutes; * `%S`: 2-digit seconds or `XX.XXXXXX` in the case of non-empty microseconds (only if `alwaysWriteFractionalSeconds` is not set to `True`); * `%z`: `+hhmm` or `-hhmm` by default; `+hh:mm` or `-hh:mm` if `writeOffsetWithColon` (added in version [2025.05](https://ytsaurus.tech/changelog/2025.05.md#datetime-module)) is set to `True`; * `%Z`: IANA name of the timezone (GMT); * `%b`: A short three-letter English name of the month (Jan); * `%B`: A full English name of the month (January). All other characters in the format string are passed on without changes. #### Examples ```yql $format = DateTime::Format("%Y-%m-%d %H:%M:%S %Z"); SELECT $format(DateTime::Split(TzDatetime("2019-01-01T01:02:03,Europe/Moscow"))); -- "2019-01-01 01:02:03 Europe/Moscow" ``` ## Parse/Parse64 {#parse} Parse a string into an internal representation using an arbitrary formatting string. Default values are used for empty fields. If errors are raised, NULL is returned. #### List of functions * `DateTime::Parse(String) -> (String{Flags:AutoMap}) -> Resource?` * `DateTime::Parse64(String) -> (String{Flags:AutoMap}) -> Resource?` Implemented specifiers: * `%%`: the % character; * `%Y`: 4-digit year when using `Parse`, and variable length (1-6 digits) year with minus for the dates BC when using `Parse64`; * `%m`: 2-digit month; * `%d`: 2-digit day; * `%H`: 2-digit hour; * `%M`: 2-digit minutes; * `%S`: Seconds, can also accept microseconds in the formats from `XX` up to `XX.XXXXXX`; * `%z`: Timezone offset in one of the following formats: `±hh:mm`, `±hhmm`, `±hh` — added in version [2025.05](https://ytsaurus.tech/changelog/2025.05.md#datetime-module); * `%Z`: The IANA name of the timezone (GMT); * `%b`: A short three-letter case-insensitive English name of the month (Jan); * `%B`: A full case-insensitive English name of the month (January). #### Examples ```yql $parse1 = DateTime::Parse("%H:%M:%S"); $parse2 = DateTime::Parse("%S"); $parse3 = DateTime::Parse("%m/%d/%Y"); $parse4 = DateTime::Parse("%Z"); $parse5 = DateTime::Parse64("%m/%d/%Y"); SELECT DateTime::MakeDatetime($parse1("01:02:03")), -- 1970-01-01T01:02:03Z DateTime::MakeTimestamp($parse2("12.3456")), -- 1970-01-01T00:00:12.345600Z DateTime::MakeTimestamp($parse3("02/30/2000")), -- NULL (Feb 30) DateTime::MakeTimestamp($parse4("Canada/Central")), -- 1970-01-01T06:00:00Z (conversion to UTC) DateTime::MakeTimestamp64($parse5("02/10/1931")), -- 1931-02-10T00:00:00Z (conversion to UTC) ``` For the common formats, wrappers around the corresponding util methods are supported. You can only get TM with components in the UTC timezone. ## Parse specific formats #### List of functions * `DateTime::ParseRfc822(String{Flags:AutoMap}) -> Resource?` * `DateTime::ParseIso8601(String{Flags:AutoMap}) -> Resource?` * `DateTime::ParseHttp(String{Flags:AutoMap}) -> Resource?` * `DateTime::ParseX509(String{Flags:AutoMap}) -> Resource?` #### Examples ```yql SELECT DateTime::MakeTimestamp(DateTime::ParseRfc822("Fri, 4 Mar 2005 19:34:45 EST")), -- 2005-03-05T00:34:45Z DateTime::MakeTimestamp(DateTime::ParseIso8601("2009-02-14T02:31:30+0300")), -- 2009-02-13T23:31:30Z DateTime::MakeTimestamp(DateTime::ParseHttp("Sunday, 06-Nov-94 08:49:37 GMT")), -- 1994-11-06T08:49:37Z DateTime::MakeTimestamp(DateTime::ParseX509("20091014165533Z")) -- 2009-10-14T16:55:33Z ``` ## Standard scenarios ### Conversions between strings and seconds Converting a string date (in the Moscow timezone) to seconds (in GMT timezone): ```yql $datetime_parse = DateTime::Parse("%Y-%m-%d %H:%M:%S"); $datetime_parse_tz = DateTime::Parse("%Y-%m-%d %H:%M:%S %Z"); SELECT DateTime::ToSeconds(TzDatetime("2019-09-16T00:00:00,Europe/Moscow")) AS md_us1, -- 1568581200 DateTime::ToSeconds(DateTime::MakeDatetime($datetime_parse_tz("2019-09-16 00:00:00" || " Europe/Moscow"))), -- 1568581200 DateTime::ToSeconds(DateTime::MakeDatetime(DateTime::Update($datetime_parse("2019-09-16 00:00:00"), "Europe/Moscow" as Timezone))), -- 1568581200 -- INCORRECT (Date imports time as GMT, but AddTimezone has no effect on ToSeconds that always returns GMT time) DateTime::ToSeconds(AddTimezone(Date("2019-09-16"), 'Europe/Moscow')) AS md_us2, -- 1568592000 ``` Converting a string date (in the Moscow timezone) to seconds (in the Moscow timezone). DateTime::ToSeconds() exports only to GMT. That's why we should put timezones aside for a while and use only GMT (as if we assumed for a while that Moscow is in GMT): ```yql $date_parse = DateTime::Parse("%Y-%m-%d"); $datetime_parse = DateTime::Parse("%Y-%m-%d %H:%M:%S"); $datetime_parse_tz = DateTime::Parse("%Y-%m-%d %H:%M:%S %Z"); SELECT DateTime::ToSeconds(Datetime("2019-09-16T00:00:00Z")) AS md_ms1, -- 1568592000 DateTime::ToSeconds(Date("2019-09-16")) AS md_ms2, -- 1568592000 DateTime::ToSeconds(DateTime::MakeDatetime($date_parse("2019-09-16"))) AS md_ms3, -- 1568592000 DateTime::ToSeconds(DateTime::MakeDatetime($datetime_parse("2019-09-16 00:00:00"))) AS md_ms4, -- 1568592000 DateTime::ToSeconds(DateTime::MakeDatetime($datetime_parse_tz("2019-09-16 00:00:00 GMT"))) AS md_ms5, -- 1568592000 -- INCORRECT (imports the time in the Moscow timezone, but RemoveTimezone doesn't affect ToSeconds in any way) DateTime::ToSeconds(RemoveTimezone(TzDatetime("2019-09-16T00:00:00,Europe/Moscow"))) AS md_ms6, -- 1568581200 DateTime::ToSeconds(DateTime::MakeDatetime($datetime_parse_tz("2019-09-16 00:00:00 Europe/Moscow"))) AS md_ms7 -- 1568581200 ``` Converting seconds (in the GMT timezone) to a string date (in the Moscow timezone): ```yql $date_format = DateTime::Format("%Y-%m-%d %H:%M:%S %Z"); SELECT $date_format(AddTimezone(DateTime::FromSeconds(1568592000), 'Europe/Moscow')) -- "2019-09-16 03:00:00 Europe/Moscow" ``` Converting seconds (in the Moscow timezone) to a string date (in the Moscow timezone). In this case, the %Z timezone is output for reference: usually, it's not needed because it's "GMT" and might mislead you. ```yql $date_format = DateTime::Format("%Y-%m-%d %H:%M:%S %Z"); SELECT $date_format(DateTime::FromSeconds(1568592000)) -- "2019-09-16 00:00:00 GMT" ``` Converting seconds (in the GMT timezone) to three-letter days of the week (in the Moscow timezone): ```yql SELECT SUBSTRING(DateTime::GetDayOfWeekName(AddTimezone(DateTime::FromSeconds(1568581200), "Europe/Moscow")), 0, 3) -- "Mon" ``` ### Date and time formatting Usually a separate named expression is used to format time, but you can do without it: ```yql $date_format = DateTime::Format("%Y-%m-%d %H:%M:%S %Z"); SELECT -- A variant with a named expression $date_format(AddTimezone(DateTime::FromSeconds(1568592000), 'Europe/Moscow')), -- A variant without a named expression DateTime::Format("%Y-%m-%d %H:%M:%S %Z") (AddTimezone(DateTime::FromSeconds(1568592000), 'Europe/Moscow')) ; ``` ### Converting types This way, you can convert only constants: ```yql SELECT TzDatetime("2019-09-16T00:00:00,Europe/Moscow"), -- 2019-09-16T00:00:00,Europe/Moscow Date("2019-09-16") -- 2019-09-16 ``` But this way, you can convert a constant, a named expression, or a table field: ```yql SELECT CAST("2019-09-16T00:00:00,Europe/Moscow" AS TzDatetime), -- 2019-09-16T00:00:00,Europe/Moscow CAST("2019-09-16" AS Date) -- 2019-09-16 ``` ### Converting time to date A CAST to Date or TzDate outputs a GMT date for a midnight, local time (for example, for Moscow time 2019-10-22 00:00:00, the date 2019-10-21 is returned). To get a date in the local timezone, you can use DateTime::Format. ```yql $x = Datetime("2019-10-21T21:00:00Z"); SELECT AddTimezone($x, "Europe/Moscow"), -- 2019-10-22T00:00:00,Europe/Moscow cast($x as TzDate), -- 2019-10-21,GMT cast(AddTimezone($x, "Europe/Moscow") as TzDate), -- 2019-10-21,Europe/Moscow cast(AddTimezone($x, "Europe/Moscow") as Date), -- 2019-10-21 DateTime::Format("%Y-%m-%d %Z")(AddTimezone($x, "Europe/Moscow")), -- 2019-10-22 Europe/Moscow ``` It's worth mentioning that several `TzDatetime` or `TzTimestamp` values with a positive timezone offset cannot be cast to `TzDate`. Consider the example below: ```yql SELECT CAST(TzDatetime("1970-01-01T23:59:59,Europe/Moscow") as TzDate); /* Fatal: Timestamp 1970-01-01T23:59:59.000000,Europe/Moscow cannot be casted to TzDate */ ``` Starting from the Unix epoch, there is no valid value representing midnight on 01/01/1970 for the Europe/Moscow timezone. As a result, such a cast is impossible and fails at runtime. At the same time, values with a negative timezone offset are converted correctly: ```yql SELECT CAST(TzDatetime("1970-01-01T23:59:59,America/Los_Angeles") as TzDate); /* 1970-01-01,America/Los_Angeles */ ``` ### Daylight saving time Please note that daylight saving time depends on the year: ```yql SELECT RemoveTimezone(TzDatetime("2019-09-16T10:00:00,Europe/Moscow")) as DST1, -- 2019-09-16T07:00:00Z RemoveTimezone(TzDatetime("2008-12-03T10:00:00,Europe/Moscow")) as DST2, -- 2008-12-03T07:00:00Z RemoveTimezone(TzDatetime("2008-07-03T10:00:00,Europe/Moscow")) as DST3, -- 2008-07-03T06:00:00Z (DST) ``` # Digest Digest functions calculate hashes (aka fingerprints) of original messages. A hash is usually a short or fixed-length string. Hash length depends on the algorithm. The result is a bit sequence. The module includes both cryptographic and non-cryptographic functions. An important feature of algorithms is that changing the original message also changes the hash output. Also, digest functions are one-way functions, meaning that it's hard to find a message that will produce a given digest. ## List of functions * `Digest::Crc32c(String{Flags::AutoMap}) -> Uint32` * `Digest::Crc64(String{Flags::AutoMap}) -> Uint64` * `Digest::Fnv32(String{Flags::AutoMap}) -> Uint32` * `Digest::Fnv64(String{Flags::AutoMap}) -> Uint64` * `Digest::MurMurHash(String{Flags:AutoMap}) -> Uint64` * `Digest::MurMurHash32(String{Flags:AutoMap}) -> Uint32` * `Digest::MurMurHash2A(String{Flags:AutoMap}) -> Uint64` * `Digest::MurMurHash2A32(String{Flags:AutoMap}) -> Uint32` * `Digest::CityHash(String{Flags:AutoMap}, [Uint64?]) -> Uint64` (the second optional argument defines the seed) * `Digest::CityHash128(String{Flags:AutoMap}) -> Tuple` CityHash is a byte string function that returns a uint128 value. The result is represented by a pair of two uint64 numbers: ``. ```yql SELECT Digest::CityHash128("Who set this ancient quarrel new abroach?"); -- (11765163929838407746,2460323961016211789) ``` * `Digest::NumericHash(Uint64{Flags:AutoMap}) -> Uint64` ```yql SELECT Digest::NumericHash(123456789); -- 1734215268924325803 ``` * `Digest::Md5Hex(String{Flags:AutoMap}) -> String` * `Digest::Md5Raw(String{Flags:AutoMap}) -> String` * `Digest::Md5HalfMix(String{Flags:AutoMap}) -> Uint64` MD5 is a widely used hashing algorithm that results in a 128-bit hash value. Md5Hex returns an ASCII string hash with hex encoding. Md5Raw returns a hash as a byte string. Md5HalfMix is a lightweight MD5 that returns a Uint64 value. ```yql SELECT Digest::Md5Hex("Who set this ancient quarrel new abroach?"), -- "644e98bae764871650f2d93e14c6488d" Digest::Md5Raw("Who set this ancient quarrel new abroach?"), -- Binary String: 64 4e 98 ba e7 64 87 16 50 f2 d9 3e 14 c6 48 8d Digest::Md5HalfMix("Who set this ancient quarrel new abroach?"); -- 17555822562955248004 ``` * `Digest::Argon2(string:String{Flags:AutoMap}, salt:String{Flags:AutoMap}) -> String` Argon2 is a key derivation function aimed at a high memory-filling rate and good utilization of multiple computing units. Parameters: - string — the source string - salt — the string to be used as the salt for the hash function ```yql SELECT Digest::Argon2("Who set this ancient quarrel new abroach?", "zcIvVcuHEIL8"); -- Binary String: fa 50 34 d3 c3 23 a4 de 22 c7 7c e1 9c 65 64 88 25 b3 59 75 c5 b8 8c 73 da 88 eb 79 31 70 e8 f1 SELECT Digest::Argon2("Who set this ancient quarrel new abroach?", "M78P42R8HA=="); -- Binary String: d2 0e f1 3e 72 5a e9 32 65 ed 28 4b 12 1f 39 70 e5 10 aa 1a 15 67 6d 96 5d e8 19 b3 bd d5 04 e9 ``` * `Digest::Blake2B(string:String{Flags:AutoMap},[key:String?]) -> String` BLAKE2 is a cryptographic hash function designed as an alternative to MD5 and SHA-1 algorithms, which have been shown to be vulnerable. Parameters: - string — the source string - key is the key used to encrypt the source string (it can be used as a shared secret for the sender and receiver) ```yql SELECT Digest::Blake2B("Who set this ancient quarrel new abroach?"); -- Binary String: 62 21 91 d8 11 5a da ad 5e 7c 86 47 41 02 7f 8f a8 a6 82 07 47 d8 f8 30 ab b4 c3 00 db 9c 24 2f ``` * `Digest::SipHash(low:Uint64,high:Uint64,string:String{Flags:AutoMap}) -> Uint64` Hashes the source message (`string`) using a 128-bit key. The key is represented by a pair of uint64 numbers: low, high ```yql SELECT Digest::SipHash(0,0,"Who set this ancient quarrel new abroach?"); -- 14605466535756698285 ``` * ```Digest::HighwayHash(key0:Uint64,key1:Uint64,key2:Uint64,key3:Uint64,string:String{Flags:AutoMap}) -> Uint64``` Hashes the source message (`string`) using a 256-bit key. The key has the following structure: - key0 — first 8 bytes of the key - key1 — next 8 bytes of the key - key2 — subsequent 8 bytes of the key - key3 — last 8 bytes of the key * `Digest::FarmHashFingerprint(source:Uint64{Flags:AutoMap}) -> Uint64` * `Digest::FarmHashFingerprint2(low:Uint64{Flags:AutoMap}, high:Uint64{Flags:AutoMap}) -> Uint64` FarmHash is a function for a 128-bit number. This 128-bit number is made by combining bits of two uit64 numbers. * `Digest::FarmHashFingerprint32(string:String{Flags:AutoMap}) -> Uint32` * `Digest::FarmHashFingerprint64(string:String{Flags:AutoMap}) -> Uint64` * `Digest::FarmHashFingerprint128(string:String{Flags:AutoMap}) -> Tuple` FarmHash is a byte string function that returns a uint128 value. The result is represented by a pair of two uint64 numbers: ``. ```yql SELECT Digest::FarmHashFingerprint2(31880,6990065); -- 237693065644851126 SELECT Digest::FarmHashFingerprint128("Who set this ancient quarrel new abroach?"); -- (17165761740714960035, 5559728965407786337) ``` * `Digest::SuperFastHash(String{Flags:AutoMap}) -> Uint32` * `Digest::Sha1(String{Flags:AutoMap}) -> String` * `Digest::Sha256(String{Flags:AutoMap}) -> String` * `Digest::IntHash64(Uint64{Flags:AutoMap}) -> Uint64` * `Digest::XXH3(String{Flags:AutoMap}) -> Uint64` * `Digest::XXH3_128(String{Flags:AutoMap}) -> Tuple` XXH3 is a non-cryptographic hash function that belongs to the xxxHash family. XXH3_128 generates a 128-bit hash represented by a pair of two uint64 numbers: ``. ```yql SELECT Digest::XXH3_128("Who set this ancient quarrel new abroach?"); -- (17117571879768798812, 14282600258804776266) ``` # Dsv UDF Functions that convert strings formatted as `"key1=value1\tkey2=value2"` to dictionaries. ```yql Dsv::ReadRecord(Struct) -> Struct> Dsv::Parse(String,[String]) -> Dict -- the second argument defines the delimiter (tab by default) ``` #### Example ```yql SELECT Dsv::Parse("a=b@@c=d@@e=f", "@@")["c"]; -- "d" ``` # Histogram Set of auxiliary functions for the [`HISTOGRAM` aggregate function](https://ytsaurus.tech/builtins/aggregation.md). In the signature description below, HistogramStruct refers to the result of the aggregate function `HISTOGRAM`, `LinearHistogram` or `LogarithmicHistogram` being a structure of a certain type. ## List of functions {#function-list} * `Histogram::Print(HistogramStruct{Flags:AutoMap}, Byte?) -> String` * `Histogram::Normalize(HistogramStruct{Flags:AutoMap}, [Double?]) -> HistogramStruct`. In the second argument, the desired histogram area is 100 by default. * `Histogram::ToCumulativeDistributionFunction(HistogramStruct{Flags:AutoMap}) -> HistogramStruct` * `Histogram::GetSumAboveBound(HistogramStruct{Flags:AutoMap}, Double) -> Double` * `Histogram::GetSumBelowBound(HistogramStruct{Flags:AutoMap}, Double) -> Double` * `Histogram::GetSumInRange(HistogramStruct{Flags:AutoMap}, Double, Double) -> Double` * `Histogram::CalcUpperBound(HistogramStruct{Flags:AutoMap}, Double) -> Double` * `Histogram::CalcLowerBound(HistogramStruct{Flags:AutoMap}, Double) -> Double` * `Histogram::CalcUpperBoundSafe(HistogramStruct{Flags:AutoMap}, Double) -> Double` * `Histogram::CalcLowerBoundSafe(HistogramStruct{Flags:AutoMap}, Double) -> Double` `Histogram::Print` has an optional numeric argument that sets the maximum length of the histogram columns (the length is in characters, since the histogram is rendered in ASCII art). The default value is 25. First and foremost, this function serves to view histograms on the console. Web interface performs their interactive visualization automatically. # Hyperscan [Hyperscan](https://www.hyperscan.io) is an open source library developed by Intel for searching with regular expressions. The library features 4 implementations with different processor instruction sets (SSE3, SSE4.2, AVX2, and AVX512) that are chosen automatically depending on the current processor. By default, all functions work in the single-byte mode. However, if the regular expression is a valid UTF-8 string but is not a valid ASCII string, the UTF-8 mode is enabled automatically. ## List of functions {#function-list} * `Hyperscan::Grep(pattern:String) -> (string:String?) -> Bool` * `Hyperscan::Match(pattern:String) -> (string:String?) -> Bool` * `Hyperscan::BacktrackingGrep(pattern:String) -> (string:String?) -> Bool` * `Hyperscan::BacktrackingMatch(pattern:String) -> (string:String?) -> Bool` * `Hyperscan::MultiGrep(pattern:String) -> (string:String?) -> Tuple` * `Hyperscan::MultiMatch(pattern:String) -> (string:String?) -> Tuple` * `Hyperscan::Capture(pattern:String) -> (string:String?) -> String?` * `Hyperscan::Replace(pattern:String) -> (string:String?, replacement:String) -> String?` ## Call syntax {#syntax} To avoid compiling a regular expression at each table row at direct call, wrap the function call by a [named expression](https://ytsaurus.tech/syntax/expressions.md#named-nodes). ```yql $re = Hyperscan::Grep("\\d+"); -- we create a called value to test the specific regular expression SELECT * FROM table WHERE $re(key); -- we use the value to filter the table ``` **Please note** escaping of special characters in regular expressions. Be sure to use the second slash, since all the standard string literals in SQL can accept C-escaped strings, and the `\d` sequence is not a valid sequence (even if it were, it wouldn't search for numbers as intended). You can enable the case-insensitive mode by specifying, at the beginning of the regular expression, the flag `(?i)`. ## Grep {#grep} Matches the regular expression with a **part of the string** (arbitrary substring). ## Match {#match} Matches the **whole string** against the regular expression. To get a result similar to that of `Grep` (that matches against a substring), enclose the regular expression in `.*` on both sides (`.*foo.*` instead of `foo`). However, in terms of code readability, it's usually better to change the function. ## BacktrackingGrep / BacktrackingMatch {#backtrackinggrep} The functions are identical to the same-name functions without the `Backtracking` prefix. However, they support a broader range of regular expressions. This is due to the fact that if a specific regular expression is not fully supported by Hyperscan, the library switches to the prefilter mode. In this case, it responds not by "Yes" or "No", but by "Definitely not" or "Maybe yes". The "maybe yes" answers are then automatically rechecked using the slower but more powerful [libpcre](https://www.pcre.org) library. ## MultiGrep / MultiMatch {#multigrep} Hyperscan lets you match against multiple regular expressions in a single pass through the text, and get a separate response for each match. However, if you want to match a string against any of the listed expressions (the results would be joined with "or"), it would be more efficient to combine the query parts in a single regular expression with `|` and match it with regular `Grep` or `Match`. When you call `MultiGrep`/`MultiMatch`, regular expressions are passed one per line using [multiline string literals](https://ytsaurus.tech/syntax/lexer.md#multiline-string-literals): #### Example ```yql $multi_match = Hyperscan::MultiMatch(@@a.* .*x.* .*axa.*@@); SELECT $multi_match("a") AS a, -- (true, false, false) $multi_match("axa") AS axa; -- (true, true, true) ``` ## Capture and Replace {#capture} If a string matches the given regexp, `Hyperscan::Capture` returns the last substring matched by the regexp. `Hyperscan::Replace` replaces all substrings matched by the specified regexp with the given string. Hyperscan offers limited capabilities for such operations. This means that although `Hyperscan::Capture` and `Hyperscan::Replace` are implemented for uniformity, we recommend using same-name functions from Re2 for non-obvious search and replacement operations: * [Re2::Capture](https://ytsaurus.tech/docs/en/re2.md#capture); * [Re2::Replace](https://ytsaurus.tech/docs/en/re2.md#replace). ## Usage example ```yql $value = "xaaxaaXaa"; $match = Hyperscan::Match("a.*"); $grep = Hyperscan::Grep("axa"); $insensitive_grep = Hyperscan::Grep("(?i)axaa$"); $multi_match = Hyperscan::MultiMatch(@@a.* .*a.* .*a .*axa.*@@); $capture = Hyperscan::Capture(".*a{2}.*"); $capture_many = Hyperscan::Capture(".*x(a+).*"); $replace = Hyperscan::Replace("xa"); SELECT $match($value) AS match, -- false $grep($value) AS grep, -- true $insensitive_grep($value) AS insensitive_grep, -- true $multi_match($value) AS multi_match, -- (false, true, true, true) $multi_match($value).0 AS some_multi_match, -- false $capture($value) AS capture, -- "xaa" $capture_many($value) AS capture_many, -- "xa" $replace($value, "b") AS replace -- "babaXaa" ; ``` # Ip The `Ip` module supports both the IPv4 and IPv6 addresses. By default, they are represented as binary strings of 4 and 16 bytes, respectively. ## List of functions * `Ip::FromString(String{Flags:AutoMap}) -> String?` — From a human-readable representation to a binary representation. * `Ip::SubnetFromString(String{Flags:AutoMap}) -> String?` — From a human-readable representation of a subnet to a binary representation. * `Ip::ToString(String{Flags:AutoMap}) -> String?` — From a binary representation to a human-readable representation. * `Ip::SubnetToString(String{Flags:AutoMap}) -> String?` — From a binary representation of a subnet to a human-readable representation. * `Ip::Ipv4FromUint32(Uint32{Flags:AutoMap}) -> String` — From a Uint32 to a binary representation; the integer `A << 24 | B << 16 | C << 8 | D` corresponds to address `A.B.C.D`. * `Ip::Ipv4ToUint32(String{Flags:AutoMap}) -> Uint32?` — From a binary IPv4 representation to Uint32; address `A.B.C.D` corresponds to the integer `A << 24 | B << 16 | C << 8 | D`; IPv6 is not supported. * `Ip::IsIPv4(String?) -> Bool` * `Ip::IsIPv6(String?) -> Bool` * `Ip::IsEmbeddedIPv4(String?) -> Bool` * `Ip::ConvertToIPv6(String{Flags:AutoMap}) -> String` — IPv6 remains unchanged, and IPv4 becomes embedded in IPv6. * `Ip::GetSubnet(String{Flags:AutoMap}, [Uint8?]) -> String` — The second argument is the subnet size; by default, it's 24 for IPv4 and 64 for IPv6. * `Ip::GetSubnetByMask(String{Flags:AutoMap}, String{Flags:AutoMap}) -> String` — The second argument is the subnet bitmask. * `Ip::SubnetMatch(String{Flags:AutoMap}, String{Flags:AutoMap}) -> Bool` — The first argument is the subnet, the second argument is the subnet or address. ## Examples ```yql SELECT Ip::IsEmbeddedIPv4( Ip::FromString("::ffff:77.75.155.3") ); -- true SELECT Ip::ToString( Ip::GetSubnet( Ip::FromString("213.180.193.3") ) ); -- "213.180.193.0" SELECT Ip::SubnetMatch( Ip::SubnetFromString("192.168.0.1/16"), Ip::FromString("192.168.1.14"), ); -- true SELECT Ip::ToString( Ip::GetSubnetByMask( Ip::FromString("192.168.0.1"), Ip::FromString("255.255.0.0") ) ); -- "192.168.0.0" SELECT Ip::ToString( Ip::Ipv4FromUint32(1) ); -- "0.0.0.1" SELECT Ip::Ipv4ToUint32( Ip::FromString("0.0.0.1") ); -- 1 ``` # Math A set of wrappers around the functions from the libm library and the Yandex utilities. ## Constants {#constants} #### List of functions * `Math::Pi() -> Double` * `Math::E() -> Double` * `Math::Eps() -> Double` #### Examples ```yql SELECT Math::Pi(); -- 3.141592654 SELECT Math::E(); -- 2.718281828 SELECT Math::Eps(); -- 2.220446049250313e-16 ``` ## (Double) -> Bool {#double-bool} #### List of functions * `Math::IsInf(Double{Flags:AutoMap}) -> Bool` * `Math::IsNaN(Double{Flags:AutoMap}) -> Bool` * `Math::IsFinite(Double{Flags:AutoMap}) -> Bool` #### Examples ```yql SELECT Math::IsNaN(0.0/0.0); -- true SELECT Math::IsFinite(1.0/0.0); -- false ``` ## (Double) -> Double {#double-double} #### List of functions * `Math::Abs(Double{Flags:AutoMap}) -> Double` * `Math::Acos(Double{Flags:AutoMap}) -> Double` * `Math::Asin(Double{Flags:AutoMap}) -> Double` * `Math::Asinh(Double{Flags:AutoMap}) -> Double` * `Math::Atan(Double{Flags:AutoMap}) -> Double` * `Math::Cbrt(Double{Flags:AutoMap}) -> Double` * `Math::Ceil(Double{Flags:AutoMap}) -> Double` * `Math::Cos(Double{Flags:AutoMap}) -> Double` * `Math::Cosh(Double{Flags:AutoMap}) -> Double` * `Math::Erf(Double{Flags:AutoMap}) -> Double` * `Math::ErfInv(Double{Flags:AutoMap}) -> Double` * `Math::ErfcInv(Double{Flags:AutoMap}) -> Double` * `Math::Exp(Double{Flags:AutoMap}) -> Double` * `Math::Exp2(Double{Flags:AutoMap}) -> Double` * `Math::Fabs(Double{Flags:AutoMap}) -> Double` * `Math::Floor(Double{Flags:AutoMap}) -> Double` * `Math::Lgamma(Double{Flags:AutoMap}) -> Double` * `Math::Rint(Double{Flags:AutoMap}) -> Double` * `Math::Sigmoid(Double{Flags:AutoMap}) -> Double` * `Math::Sin(Double{Flags:AutoMap}) -> Double` * `Math::Sinh(Double{Flags:AutoMap}) -> Double` * `Math::Sqrt(Double{Flags:AutoMap}) -> Double` * `Math::Tan(Double{Flags:AutoMap}) -> Double` * `Math::Tanh(Double{Flags:AutoMap}) -> Double` * `Math::Tgamma(Double{Flags:AutoMap}) -> Double` * `Math::Trunc(Double{Flags:AutoMap}) -> Double` * `Math::Log(Double{Flags:AutoMap}) -> Double` * `Math::Log2(Double{Flags:AutoMap}) -> Double` * `Math::Log10(Double{Flags:AutoMap}) -> Double` #### Examples ```yql SELECT Math::Sqrt(256); -- 16 SELECT Math::Trunc(1.2345); -- 1 ``` ## (Double, Double) -> Double {#doubledouble-double} #### List of functions * `Math::Atan2(Double{Flags:AutoMap}, Double{Flags:AutoMap}) -> Double` * `Math::Fmod(Double{Flags:AutoMap}, Double{Flags:AutoMap}) -> Double` * `Math::Hypot(Double{Flags:AutoMap}, Double{Flags:AutoMap}) -> Double` * `Math::Pow(Double{Flags:AutoMap}, Double{Flags:AutoMap}) -> Double` * `Math::Remainder(Double{Flags:AutoMap}, Double{Flags:AutoMap}) -> Double` #### Examples ```yql SELECT Math::Atan2(1, 0); -- 1.570796327 SELECT Math::Remainder(2.1, 2); -- 0.1 ``` ## (Double, Int32) -> Double {#doubleint32-double} #### List of functions * `Math::Ldexp(Double{Flags:AutoMap}, Int32{Flags:AutoMap}) -> Double` * `Math::Round(Double{Flags:AutoMap}, [Int32?]) -> Double` — the second argument specifies the power of 10 that the number is rounded to (a negative value for decimal places, and a positive for tens, thousands, millions, etc.). 0 by default #### Examples ```yql SELECT Math::Pow(2, 10); -- 1024 SELECT Math::Round(1.2345, -2); -- 1.23 ``` ## (Double, Double, \[Double?\]) -> Bool {#doubledouble-bool} #### List of functions * `Math::FuzzyEquals(Double{Flags:AutoMap}, Double{Flags:AutoMap}, [Double?]) -> Bool` — compares two Doubles with a tolerance specified by the third argument. 1.0e-13 by default. The tolerance is measured in relative units based on the argument whose absolute value is less. {% note alert %} If any compared value equals `0.0`, the function returns `false`. {% endnote %} #### Examples ```yql SELECT Math::FuzzyEquals(1.01, 1.0, 0.05); -- true ``` ## Functions for computing remainders #### List of functions * `Math::Mod(Int64{Flags:AutoMap}, Int64) -> Int64?` * `Math::Rem(Int64{Flags:AutoMap}, Int64) -> Int64?` These functions behave similarly to the built-in % operator in the case of non-negative arguments. The differences are noticeable in the case of negative arguments: * Math::Mod preserves the sign of the second argument (the denominator). * Math::Rem preserves the sign of the first argument (the numerator). Functions return null if the divisor is zero. #### Examples ```yql SELECT Math::Mod(-1, 7); -- 6 SELECT Math::Rem(-1, 7); -- -1 ``` ## Functions for rounding to integer in a given mode #### List of functions * `Math::RoundDownward() -> Tagged` — Rounding toward negative infinity. * `Math::RoundToNearest() -> Tagged` — Rounding toward the nearest representable value. * `Math::RoundTowardZero() -> Tagged` — Rounding toward zero. * `Math::RoundUpward() -> Tagged` — Rounding toward positive infinity. * `Math::NearbyInt(AutoMap, Tagged) -> Optional` — Rounding to the nearest integer. The `Math::NearbyInt` function rounds the first argument to an integer according to the mode specified by the second argument. If the result is beyond a 64-bit integer, it returns NULL. #### Examples ```yql SELECT Math::NearbyInt(1.5, Math::RoundDownward()); -- 1 SELECT Math::NearbyInt(1.5, Math::RoundToNearest()); -- 2 SELECT Math::NearbyInt(2.5, Math::RoundToNearest()); -- 2 SELECT Math::NearbyInt(1.5, Math::RoundTowardZero()); -- 1 SELECT Math::NearbyInt(1.5, Math::RoundUpward()); -- 2 SELECT Math::NearbyInt(-1.5, Math::RoundDownward()); -- -2 SELECT Math::NearbyInt(-1.5, Math::RoundToNearest()); -- -2 SELECT Math::NearbyInt(-2.5, Math::RoundToNearest()); -- -2 SELECT Math::NearbyInt(-1.5, Math::RoundTowardZero()); -- -1 SELECT Math::NearbyInt(-1.5, Math::RoundUpward()); -- -1 ``` # Pcre The Pcre library is currently an alias to [Hyperscan](https://ytsaurus.tech/docs/en/hyperscan.md). If you rely on specific functionality of a certain regular expression engine, you should use a UDF with a particular library inside and treat Pcre as the current recommended option for simple matches, which might change in the future. Currently available engines: * [Hyperscan](https://ytsaurus.tech/docs/en/hyperscan.md) (Intel) * [Pire](https://ytsaurus.tech/docs/en/pire.md) (Yandex) * [Re2](https://ytsaurus.tech/docs/en/re2.md) (Google) All three modules provide nearly the same functionality with an identical interface, which lets you switch between them while minimizing changes to your query. Inside Hyperscan, there are several implementations that use different sets of processor instructions, with the relevant instruction automatically selected based on the current processor. In HyperScan, some functions support backtracking (referencing the previously found part of the string). Those functions are implemented through hybrid use of the two libraries: Hyperscan and libpcre. [Pire](https://github.com/yandex/pire) (Perl Incompatible Regular Expressions) is a very fast regular expression library developed by Yandex. At the lower level, it parses the input string once without backtracking and uses 5 instructions per character (both for x86 and x86_64). However, its development was almost frozen in 2011–2013, so, as the name suggests ("i" stands for incompatible), you might have to adapt the expressions themselves. Hyperscan and Pire are best-suited for Grep and Match. Re2 uses [google::RE2](https://github.com/google/re2), which offers a wide range of options ([see official documentation](https://github.com/google/re2/wiki/Syntax)). The key advantage of Re2 is a broad Capture and Replace functionality. Use this library if you need these capabilities. # Pire ## List of functions * `Pire::Grep(pattern:String) -> (string:String?) -> Bool` * `Pire::Match(pattern:String) -> (string:String?) -> Bool` * `Pire::MultiGrep(pattern:String) -> (string:String?) -> Tuple` * `Pire::MultiMatch(pattern:String) -> (string:String?) -> Tuple` * `Pire::Capture(pattern:String) -> (string:String?) -> String?` * `Pire::Replace(pattern:String) -> (string:String?, replacement:String) -> String?` One of the options to match regular expressions in YQL is to use [Pire](https://github.com/yandex/pire) (Perl Incompatible Regular Expressions). This is a very fast regular expression library developed by Yandex. At the lower level, it parses the input string once without backtracking and uses 5 instructions per character (both for x86 and x86_64). The speed is achieved by using the reasonable restrictions: * Pire is primarily focused at checking whether a string matches a regular expression. * The matching substring can also be returned (by Capture), but with restrictions (a match with only one group is returned). By default, all functions work in the single-byte mode. However, if the regular expression is a valid UTF-8 string but is not a valid ASCII string, the UTF-8 mode is enabled automatically. To enable Unicode mode, you can include one non-ASCII character in the regular expression using the `?` operator, e.g. `\\w+я?`. ## Call syntax {#call-syntax} To avoid compiling a regular expression at each table row, wrap the function call by a [named expression](https://ytsaurus.tech/syntax/expressions.md#named-nodes): ```yql $re = Pire::Grep("\\d+"); -- create a callable value to test the specific regular expression SELECT * FROM table WHERE $re(key); -- use the value to filter a table ``` {% note alert %} When escaping special characters in a regular expression, be sure to use the second slash, since all the standard string literals in SQL can accept C-escaped strings, and the `\d` sequence is not a valid sequence (even if it were, it wouldn't search for numbers as intended). {% endnote %} You can enable the case-insensitive mode by specifying, at the beginning of the regular expression, the flag `(?i)`. ### Examples ```yql $value = "xaaxaaxaa"; $match = Pire::Match("a.*"); $grep = Pire::Grep("axa"); $insensitive_grep = Pire::Grep("(?i)axa"); $multi_match = Pire::MultiMatch(@@a.* .*a.* .*a .*axa.*@@); $capture = Pire::Capture(".*x(a).*"); $capture_many = Pire::Capture(".*x(a+).*"); $replace = Pire::Replace(".*x(a).*"); SELECT $match($value) AS match, -- false $grep($value) AS grep, -- true $insensitive_grep($value) AS insensitive_grep, -- true $multi_match($value) AS multi_match, -- (false, true, true, true) $multi_match($value).0 AS some_multi_match, -- false $capture($value) AS capture, -- "a" $capture_many($value) AS capture_many, -- "aa" $replace($value, "b") AS replace; -- "xaaxaaxba" ``` ## Grep {#grep} Matches the regular expression with a **part of the string** (arbitrary substring). ## Match {#match} Matches the **whole string** against the regular expression. To get a result similar to `Grep` (where substring matching is included), enclose the regular expression in `.*`. For example, use `.*foo.*` instead of `foo`. ## MultiGrep / MultiMatch {#multigrep} Pire lets you match against multiple regular expressions in a single pass through the text and get a separate response for each match. Use the MultiGrep/MultiMatch functions to optimize the query execution speed. Be sure to do it carefully, since the size of the state machine used for matching grows exponentially with the number of regular expressions: * If you want to match a string against any of the listed expressions (the results are joined with "or"), it would be much more efficient to combine the query parts in a single regular expression with `|` and match it using regular Grep or Match. * Pire has a limit on the size of the state machine (YQL uses the default value set in the library). If you exceed the limit, the error is raised at the start of the query: `Failed to glue up regexes, probably the finite state machine appeared to be too large`. When you call MultiGrep/MultiMatch, regular expressions are passed one per line using [multiline string literals](https://ytsaurus.tech/syntax/lexer.md#multiline-string-literals): ### Examples ```yql $multi_match = Pire::MultiMatch(@@a.* .*x.* .*axa.*@@); SELECT $multi_match("a") AS a, -- (true, false, false) $multi_match("axa") AS axa; -- (true, true, true) ``` ## Capture {#capture} If a string matches the specified regular expression, it returns a substring that matches the group enclosed in parentheses in the regular expression. Capture is non-greedy: the shortest possible substring is returned. {% note alert %} The expression must contain only **one** group in parentheses. `NULL` (empty Optional) is returned in case of no match. {% endnote %} If the restrictions and characteristics described above are unacceptable, you should consider using [Re2::Capture](https://ytsaurus.tech/docs/en/re2.md#capture). ## Replace {#replace} Pire doesn't support replace based on a regular expression. `Pire::Replace` implemented in YQL is a simplified emulation using `Capture`. It may run correctly, if the substring occurs more than once in the source string. As a rule, it's better to use [Re2::Replace](https://ytsaurus.tech/docs/en/re2.md#replace) instead. # PostgreSQL module YQL supports PostgreSQL-compatible types, expressions, built-in functions, and syntax when processing data in YTsaurus. PostgreSQL values use the `p` suffix, for example `'text'p` and `1p`. ## Calling PostgreSQL functions from YQL {#callpgfunction} Use `PgCall(, )` to call a PostgreSQL function explicitly: ```yql SELECT PgCall('lower', 'Test'p); -- 'test'p ``` For collation-sensitive functions, pass a named `Collation` argument: ```yql SELECT PgCall('upper', 'straße'p, 'de-DE-x-icu' AS Collation); -- 'STRASSE'p ``` The value must be a collation name from `pg_catalog.pg_collation`: one of `default`, `C`, `POSIX`, `ucs_basic`, or `unicode`, or an ICU locale in the `-x-icu` form. Use `PgRangeCall` for a function returning a set of `pgrecord` values: ```yql SELECT * FROM AS_TABLE(PgRangeCall("json_each", pgjson('{"a":"foo", "b":"bar"}'))); ``` ## PostgreSQL syntax and warnings {#postgresql-syntax} PostgreSQL syntax supports YT cluster-qualified table paths. Replace the placeholders with your cluster and table path: ```sql SELECT * FROM ."//path/to/table"; ``` Starting with version [2026.01](https://ytsaurus.tech/changelog/2026.01.md), `SET` can configure warning handling similarly to [PRAGMA Warning](https://ytsaurus.tech/syntax/pragma/global.md#warning): ```sql SET Warning = "error", "4503"; ``` # Protobuf UDF Converting protobuf messages to YQL structs. Function list: `Protobuf::Parse(String?{Flags:AutoMap}) -> ?` — Unpackages a message into a YQL struct. `Protobuf::TryParse(String?{Flags:AutoMap}) -> ?` — Safely unpackages a message. Returns Null on error. `Protobuf::Serialize(?{Flags:AutoMap}) -> String` — Serializes a struct into a protobuf string. Before using, create metainformation similar to what is written to the `_yql_proto_field` meta attribute. ## Supported descriptor fields {#yql_proto_field_desc} * `name` — The name of the protobuf message. It is comprised of the package name and message name separated by the dot ("package.message"), where: * package stores the proto file. * message is used to collect data. * `meta` — The descriptor of the protobuf message (compressed zlib, base64). * `format` — The protobuf serialization format. Possible values: `protobin` (default) — binary, `prototext` — text, `json`. * `skip` — The number of bytes in each value to be ignored before parsing (0 by default). * `view` — The dictionary defining the view settings for protobuf messages. The following settings are supported: * `enum` — Protobuf enumeration view in YQL. Possible values: `number` (default), `name`, `full_name`. * `recursion` — A method to process recursive messages. Possible values: `fail` (default) — display an error, `ignore`— skip recursive insertions, `bytes` (deprecated) — convert recursive insertions to byte strings, `bytesV2` — same as bytes, but with the output type changed to optional byte strings for optional fields. * `yt_mode` — The `YT Mode` (`false` by default) used to process YT-specific protobuf options. If the message lacks `NYT.default_field_flags` or its value is not `SERIALIZATION_YT`, the message is converted to a byte string. If its value is `SERIALIZATION_YT`, the message is processed as usual (recursive message processing is defined by the `recursion` option). * `lists` — The dictionary that defines view settings for repeated fields in protobuf messages. The following settings are supported: * `optional` — Displays repeated fields as optional lists instead of regular ones (`true` by default). {% note warning "Attention" %} If `lists` `optional=false`, we don't recommend writing the `Protobuf::Parse` result into tables because the table schema becomes incompatible if new repeated fields are introduced. {% endnote %} ## Examples ```yql $config = @@{ "name": "my.service.proto.BufferWrapper", "meta": "H4sIAAAAAAAAA51YT3PbVBCv/9vr2FGUAkE9AG6BtAWnSdrp39AmbUo6JZBpy2SGYaajSM+xJraeR5KdpB+B6ZkjnDlx4RPADF+EM2cOXNi3+yzZabDV3KSn3d/b3bf7232C13m47QTHvche2g/sXnvJtSP7pdftySBaOhR7A++VDJZ6gYzkkhgIP3oZHfdE2KQV80LQbx7bviuOml0RBd6B3TwcrPDHK7/koLKpVF6ghlmDSlf2Q9GVA2GcM6tQotd+z8jE31x56BtZswIFp+M5B0bOBCiGTiA7HSNv1gEOPR9l9jr9wCiYs1Dl95Z0+qFRVHr8WDLLkCepslmCnJAto6I2CUVHOJEnfQMUstO2/X1hVJWi5/f6kTGjpCLZd9pkZk3tSa9hZAeRUTdnoEzvwneNWWUBvTm274iOYZjnwaCFlgwcoeHnTANmUGJgh9siakvXME0T6ryyE8ieCKJjY16Z/ErKrnFemRaI0HsljHfUFl3herZWfdecgxotxJrvKZsPxHEYBfJAGAsK3BUDzxHPZGSTt+8rP1p4sJ3NIJCBYan3th22tYkX9op0ZKvwownX3yod0mSCdfYMa/xUhgIlkXkd8urDQubDzGJ95ZPmhA2bSdrVoYhHty+ihSzqzWE8S63A7oon7kKOFtYAKPdIZSGPa9WVTyeCb8fiW+fM+1DlBGX9AukvTtR/nsgzAGcxAxRTAOwm8giwCfU4rxmjRBhXJxsxpsJ2cDYwRjmFHQ8TeQRY06XC+pUUgXwRi/P+Xcpx1ocU+28n8gjwEGo9XRMMUSWIKxMhdkY1EOQuVFQRMsAMAUzOtO+G0uwC1y2r11K48CyRR4AvYTYu5JBB6gTy2USQp+M6CPQNzI9TAIPNEti1iWCP3tRjyxL+YDAjhWWPx3UYKCEeBppLAbQ1rrN1bqMIeRSzGw2ApCCRyTNHxBAF9XhMRV9oLEN1pOhOF0Jqz/fsfUG0UG7UoDpSZo0dqI9XDJJugZqCBqpim/FdDTWHrUZ9+1q6jKeaVQm/00KebFqD6kj9KLiB3ekzvVWUODYZ50AwZFnxWNtzXeFr+37IACT1Yz6AEvelEAFyGM6llJXXfN7fwzqyrTtQ0o/Yf7Keqw2hWCkTshyrHD3WVKfFFke+ZBt/ZKASF4K5AWVVQ48D2SWQaRkXa9LTjvTIn6LCeCFp7zMgWNfYJIZDezvYWzpkTzZxqpA4VWg8h+pIOSqdQ8+N2vqA1QEIb78dJWes0mWXRPiMMXBqaYvF+JivQnWEphQKs5wOLyYddqcQMXOLlcY1qI0REk4O5SGnaYU4T5QVlcavGZg9Uf5Y/JCwiE6Hu29DIMk7vVpPoT6+MpIhlPm4Hduj/PPUmGNzmioHutL1Wp4IKCCVxhOYP4VipkV7Hqoy8FCQVPSBrcPsCYJR8XRUjXGw8IzEkSN6pJMdxg9L0zkgiErjA5g9QS0KQjEUQ8SD0d9luJl2iun22cw0s1HjdRnK21phZFrhMLC13Z6Owi3mPLJ9WmcbYjappEfGHnUMc9YtKD5CQovE2Fnicy/Qm+Gzf6QzW63bnNEWFntu3XVP6vldHWGWjfX8kA9eYxdofQ2ytpp1VGoup3MDt2yuR5t+FBwrKIfHnIo2s0ymXYbSUERn5WlFcyd7K2NdhOqGQA4T661IBIoGZMJ4OlmsP7G61iM0aa8fCWbrMbefkBtZcmMtpRvjcEOXrO//x/QHo6anDtaIa+TtLjYLcRSd4sIahfKs4NY/GSgxamjehIyt6WZyM4+RdAZeh8yejuLl1MmAkck4mGZK68aZYo/DXkaVQ25qf4kRkiBa30KeKqum7o6opuP5hWrcFA/t0efpkHUQY775uQSraflGNZ1UXPM7Dk07KKzuUkQkmRQTrlJgEsERwZF+pGa2NKdFig9Z4Y17l3UJchvyaArvI02Vv5IOk6MiZhlGujC4NUbSkR1NPDTA6WZcsRahyDPfG6RaH/5TIC8K1l9ZfZRooyud+JJJvBF5UUfzhvpuuy6O+CHvoQqpb2t6Q3sC0RJBIJjkyJ49OxR0pSNlbCsbaqFErfEGmSFwouO71sfTg6kCdhPKA08cqjzQl6yUiveg3NGh1LerKVcjpRgH/z7UPN+LPLvDUcXbVW7q7YYgWF5xaWmYDKMEhFHyMS90iLcB7GGVhrq6b6fOsqTCQ6ZSPOqeHQwv9jSFDzOYzshaHiH4MFXniCv03zzcS1uhgXBk4IrgJduc4pfJWYrfOuuEYp3pn0/jEdQ2+i3M+V1UwyHVXIXiHi3oNnBx4smxbqMPRX5Kph3OjBXIK0t0c7o0+ZeItmD0Ekfjk9Pu+wd0+jPDO1qBLlC/YT4OtZa1FtPhR1PzDe+xt3Gw1UHUBk4uwyHRo+oqFCiMeoxrTP+TxdddFYxh9v0H5WlC0LgVAAA=" }@@; $try_parse = Udf(Protobuf::TryParse, $config As TypeConfig); SELECT $try_parse(data) AS proto, $try_parse('BAD ROW') AS bad_row FROM `//home/yql/protobuf_example`; ``` # Re2 ## List of functions ```yql Re2::Grep(pattern:String, options:Struct<...>?) -> (string:String?) -> Bool Re2::Match(pattern:String, options:Struct<...>?) -> (string:String?) -> Bool Re2::Capture(pattern:String, options:Struct<...>?) -> (string:String?) -> Struct<_1:String?,foo:String?,...> Re2::FindAndConsume(pattern:String, options:Struct<...>?) -> (string:String?) -> List Re2::Replace(pattern:String, options:Struct<...>?) -> (string:String?, replacement:String) -> String? Re2::Count(pattern:String, options:Struct<...>?) -> (string:String?) -> Uint32 Re2::Options([CaseSensitive:Bool?,DotNl:Bool?,Literal:Bool?,LogErrors:Bool?,LongestMatch:Bool?,MaxMem:Uint64?,NeverCapture:Bool?,NeverNl:Bool?,OneLine:Bool?,PerlClasses:Bool?,PosixSyntax:Bool?,Utf8:Bool?,WordBoundary:Bool?]) -> Struct ``` The Re2 module supports regular expressions based on [google::RE2](https://github.com/google/re2) with a wide range of features provided ([see the official documentation](https://github.com/google/re2/wiki/Syntax)). By default, the UTF-8 mode is enabled automatically if the regular expression is a valid UTF-8-encoded string, but is not a valid ASCII string. You can manually control the settings of the re2 library, if you pass the result of the `Re2::Options` function as the second argument to other module functions, next to the regular expression. {% note info "Note" %} All regular expressions passed to functions must be valid. Otherwise, your query may fail. Starting from version [2025.03](https://ytsaurus.tech/changelog/2025.03.md#re2-module), such a query will definitely fail. {% endnote %} {% note warning %} Make sure to double all the backslashes in your regular expressions (if they are within a quoted string): standard string literals are treated as C-escaped strings in SQL. You can also format regular expressions as raw strings `@@regexp@@`: double slashes are not needed in this case. {% endnote %} ## Examples ```yql $value = "xaaxaaxaa"; $options = Re2::Options(false AS CaseSensitive); $match = Re2::Match("[ax]+\\d"); $grep = Re2::Grep("a.*"); $capture = Re2::Capture(".*(?Pxa?)(a{2,}).*"); $replace = Re2::Replace("x(a+)x"); $count = Re2::Count("a", $options); SELECT $match($value) AS match, -- false $grep($value) AS grep, -- true $capture($value) AS capture, -- (_0: 'xaaxaaxaa', _1: 'aa', foo: 'x') $capture($value)._1 AS capture_member, -- "aa" $replace($value, "b\\1z") AS replace, -- "baazaaxaa" $count($value) AS count; -- 6 ``` ## Re2::Grep / Re2::Match {#match} If you leave out the details of implementation and syntax of regular expressions, those functions are totally similar [to the same-name functions](https://ytsaurus.tech/docs/en/pire.md#match) from the Pire module. With other things equal and no specific preferences, we recommend that you use `Pire::Grep or Pire::Match`. You can call the `Re2::Grep` function by using a `REGEXP` expression (see the [basic expression syntax](https://ytsaurus.tech/syntax/expressions.md#regexp)). For example, the following two queries are equivalent (also in terms of computing efficiency): * `$grep = Re2::Grep("b+"); SELECT $grep("aaabccc");` * `SELECT "aaabccc" REGEXP "b+";` ## Re2::Capture {#capture} Unlike [Pire::Capture](https://ytsaurus.tech/docs/en/pire.md#capture), `Re2::Capture` supports multiple and named capturing groups. Result type: a structure with the fields of the type `String?`. * Each field corresponds to a capturing group with the applicable name. * For unnamed groups, the following names are generated: `_1`, `_2`, etc. * The result always includes the `_0` field containing the entire substring matching the regular expression. For more information about working with structures in YQL, see the [section on containers](https://ytsaurus.tech/types/containers.md). ## Re2::FindAndConsume {#findandconsume} Searches for all occurrences of the regular expression in the passed text and returns a list of values corresponding to the parenthesized part of the regular expression for each occurrence. ## Re2::Replace {#replace} Works as follows: * In the input string (first argument), all the non-overlapping substrings matching the regular expression are replaced by the specified string (second argument). * In the replacement string, you can use the contents of capturing groups from the regular expression using back-references in the format: `\\1`, `\\2` etc. The `\\0` back-reference stands for the whole substring that matches the regular expression. ## Re2::Count {#count} Returns the number of non-overlapping substrings of the input string that have matched the regular expression. ## Re2::Options {#options} Notes on Re2::Options from the official [repository](https://github.com/google/re2/blob/main/re2/re2.h#L595-L617) | Parameter | Default | Comments | | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ---------- | ------------------------------------------------------------------------------------- | | CaseSensitive:Bool? | true | match is case-sensitive (regexp can override with (?i) unless in posix_syntax mode) | | DotNl:Bool? | false | let `.` match `\n` (default ) | | Literal:Bool? | false | interpret string as literal, not regexp | | LogErrors:Bool? | true | this option is ignored | | LongestMatch:Bool? | false | search for longest match, not first match | | MaxMem:Uint64? | - | (see below) approx. max memory footprint of RE2 | | NeverCapture:Bool? | false | parse all parents as non-capturing | | NeverNl:Bool? | false | never match \n, even if it is in regexp | | PosixSyntax:Bool? | false | restrict regexps to POSIX egrep syntax | | Utf8:Bool? | true | text and pattern are UTF-8; otherwise Latin-1 | | The following options are only consulted when PosixSyntax == true. When PosixSyntax == false, these features are always enabled and cannot be turned off; to perform multi-line matching in that case, begin the regexp with (?m). | | PerlClasses:Bool? | false | allow Perl's \d \s \w \D \S \W | | WordBoundary:Bool? | false | allow Perl's \b \B (word boundary and not) | | OneLine:Bool? | false | ^ and $ only match beginning and end of text | It is not recommended to use Re2::Options in the code. Most parameters can be replaced with regular expression flags. ### Flag usage examples ```yql $value = "Foo bar FOO"u; -- enable case-insensitive mode $capture = Re2::Capture(@@(?i)(foo)@@); SELECT $capture($value) AS capture; -- ("_0": "Foo", "_1": "Foo") $capture = Re2::Capture(@@(?i)(?PFOO).*(?Pbar)@@); SELECT $capture($value) AS capture; -- ("_0": "Foo bar", "bar": "bar", "foo": "Foo") ``` In both cases, the word FOO will be found. Using the raw string @@regexp@@ lets you avoid double slashes. # SimplePg The SimplePg module provides access to some functions from the PostgreSQL codebase, at the same time the input and output arguments are adapted to [primitive types](https://ytsaurus.tech/types/primitive.md). By default you need to specify the module name as `SimplePg::foo` to call these functions. But if you add the PRAGMA `SimplePg`, you can use them in the global visibility scope as `foo` (it is guaranteed that they will override the built-in YQL function, if any exists). Many functions in this module have analogues among other YQL functions with better performance. ## now ### Signature ```yql SimplePg::now() -> Timestamp? ``` Available since version [2025.04](https://ytsaurus.tech/changelog/2025.04.md#simple-pg-module). Takes no arguments. Returns the current time. Original [documentation](https://www.postgresql.org/docs/16/functions-datetime.html). Analogue — [CurrentUtcTimestamp](https://ytsaurus.tech/builtins/basic.md#current-utc). ### Examples ```yql PRAGMA SimplePg; SELECT now(); ``` ## to_date ### Signature ```yql SimplePg::to_date(Utf8?, Utf8?) -> Date32? ``` Available since version [2025.04](https://ytsaurus.tech/changelog/2025.04.md#simple-pg-module). Arguments: * a string with the date value; * format string. Parses the date according to the format string. Original [documentation](https://www.postgresql.org/docs/16/functions-formatting.html). Analogue — [DateTime::Parse64](https://ytsaurus.tech/docs/en/datetime.md#parse). ### Examples ```yql PRAGMA SimplePg; SELECT to_date('05 Dec 2000', 'DD Mon YYYY'); -- 2000-12-05 ``` ## to_char ### Signature ```yql SimplePg::to_char(AnyPrimitiveType?, Utf8?) -> Utf8? ``` Available since version [2025.04](https://ytsaurus.tech/changelog/2025.04.md#simple-pg-module). Arguments: * primitive type value; * format string. Formats the value according to the format string. Original [documentation](https://www.postgresql.org/docs/16/functions-formatting.html). Analogue — [DateTime::Format](https://ytsaurus.tech/docs/en/datetime.md#format). ### Examples ```yql PRAGMA SimplePg; SELECT to_char(125.8, '999D9'); -- " 125.8" SELECT to_char(Timestamp('2002-04-20T17:31:12.66Z'), 'HH12:MI:SS'); -- 05:31:12 ``` ## date_part ### Signature ```yql SimplePg::date_part(Utf8?, Timestamp64?) -> Double? SimplePg::date_part(Utf8?, Interval64?) -> Double? ``` Available since version [2025.04](https://ytsaurus.tech/changelog/2025.04.md#simple-pg-module). Arguments: * component, possible [values](https://www.postgresql.org/docs/16/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT); * timestamp. Extracts the specified component from the timestamp or interval. Original [documentation](https://www.postgresql.org/docs/16/functions-datetime.html). Analogue — [DateTime::Get](https://ytsaurus.tech/docs/en/datetime.md#get). ### Examples ```yql PRAGMA SimplePg; SELECT date_part('hour', Timestamp('2001-02-16T20:38:40Z')); -- 20 SELECT date_part('minute', Interval('PT01H02M03S')); -- 2 ``` ## date_trunc ### Signature ```yql SimplePg::date_trunc(Utf8?, Timestamp64?) -> Timestamp64? ``` Available since version [2025.04](https://ytsaurus.tech/changelog/2025.04.md#simple-pg-module). Arguments: * scale, possible [values](https://www.postgresql.org/docs/16/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC); * timestamp. Rounds the timestamp to the beginning of the specified scale. Original [documentation](https://www.postgresql.org/docs/16/functions-datetime.html). Analogue — [DateTime::StartOf](https://ytsaurus.tech/docs/en/datetime.md#startof), etc. ### Examples ```yql PRAGMA SimplePg; SELECT date_trunc('hour', Timestamp('2001-02-16T20:38:40Z')); -- 2001-02-16 20:00:00 SELECT date_trunc('year', Timestamp('2001-02-16T20:38:40Z')); -- 2001-01-01 00:00:00 ``` ## floor ### Signature ```yql SimplePg::floor(Double?) -> Double? ``` Available since version [2025.04](https://ytsaurus.tech/changelog/2025.04.md#simple-pg-module). Arguments: * floating-point value. Rounds the value down to the nearest integer. Original [documentation](https://www.postgresql.org/docs/16/functions-math.html). Analogue — [Math::Floor](https://ytsaurus.tech/docs/en/math.md). ### Examples ```yql PRAGMA SimplePg; SELECT floor(42.8); -- 42 SELECT floor(-42.8); -- -43 ``` ## ceil ### Signature ```yql SimplePg::ceil(Double?) -> Double? ``` Available since version [2025.04](https://ytsaurus.tech/changelog/2025.04.md#simple-pg-module). Arguments: * floating-point value. Rounds the value up to the nearest integer. Original [documentation](https://www.postgresql.org/docs/16/functions-math.html). Analogue — [Math::Ceil](https://ytsaurus.tech/docs/en/math.md). ### Examples ```yql PRAGMA SimplePg; SELECT ceil(42.2); -- 43 SELECT ceil(-42.8); -- -42 ``` ## round ### Signature ```yql SimplePg::round(Double?, [Int32?]) -> Double? ``` Available since version [2025.04](https://ytsaurus.tech/changelog/2025.04.md#simple-pg-module). Arguments: * floating-point value; * optional number of decimal places (default is 0). Rounds the value to the specified number of decimal places. Rounds away from zero. Original [documentation](https://www.postgresql.org/docs/16/functions-math.html). Analogue — [Math::Round](https://ytsaurus.tech/docs/en/math.md). ### Examples ```yql PRAGMA SimplePg; SELECT round(42.4382, 2); -- 42.44 SELECT round(1234.56, -1); -- 1230 ``` # Streaming UDF Functions designed to launch an arbitrary script or executable in the streaming mode: ```yql Streaming::Process(List>, String, List) -> List> Streaming::ProcessInline(List>, String, List) -> List> ``` Argument description: 1. Input data (`List>`): * A child process is launched once in the beginning of list processing (or a part of it, see the next section), and each element is fed to it via stdin in separate lines. * If the input list is a large table, many identical processes are launched to process its parts in parallel. Processing of all table data in one process and the order of line processing are not guaranteed. * `Process` and `ProcessInline` expect each line in the input data list to be wrapped in a structure. It simplifies using `SELECT` because this is the type of `SELECT ... AS Data FROM ...`. Also, it helps discourage the user from choosing a very inefficient scenario in which a separate process is launched for each line of the source MapReduce table. * The `Data` name for the list item structure is fixed at the time of writing. 2. The second argument (`String`): * `Streaming::Process` is the program name from `$PATH` (you must ensure that it's installed on the target cluster) or the executable path that can be attached to the query and then receive the path via the built-in `FilePath("file alias")` function. * `Streaming::ProcessInline` is the line with the script that will be launched using the interpreter specified in [shebang](https://en.wikipedia.org/wiki/Shebang_(Unix)) (i.e. the first line of the script must read like `#!/usr/bin/env bash`, for example). Basic interpreters, such as sh, bash, awk, perl, or python, are usually already installed. If you need something unconventional, you can attach it to your query. The script can be defined inline using a multiline string literal (`@@`) or in a separate file (a tab in the web interface). In the second case, you can get a file's content in a line using the built-in `FileContent("file alias")` function. 3. The list of arguments to be passed to the script or executable when launching (`List`, optional argument): * An easy way to create a list of arguments using literals is to use the built-in `AsList` function (accepts 1 or more arguments and creates a list of them). * If you don't need to pass arguments, skip them. If you're interested in a specific interaction with a child process that's different from the line-by-line approach described above, tell us about your use scenario in the [community chat](https://t.me/ytsaurus). Notes on implementation: * `Streaming::ProcessInline` usually uses the interpreter installed on the target cluster, which might lead to unexpected results in case of version conflict. To avoid this, you can attach your interpreter to the query (which isn't always straightforward) or ask the cluster administrator to install the required interpreter or its version (it's better if you contact them directly, but you can also ask us for help). * stderr can be used for troubleshooting issues: some part of it is saved and returned to the client in case of a runtime error when running the query. The size of the saved part of stderr is configured on the cluster side and usually is several kilobytes. #### Examples ```yql $input = [ <| "Data":"a" |>, <| "Data":"b" |>, <| "Data":"c" |>, ]; PROCESS AS_TABLE($input) USING Streaming::Process(TableRows(), "cat", ["-n"]); /* [(Data: ' 1 a'), (Data: ' 2 b'), (Data: ' 3 c')] */ ``` ```yql $ips = (SELECT COALESCE(ip, "") AS Data FROM .`home/yql/tutorial/users`); $script = @@#!/usr/bin/awk -f BEGIN { FS="."; } $2 ~ /170/ { print $0" has 170 in second octet!"; } @@; PROCESS $ips USING Streaming::ProcessInline( TableRows(), $script ); /* "93.170.111.29 has 170 in second octet!" "93.170.111.28 has 170 in second octet!" */ ``` # String Functions for ASCII strings: ## List of functions * `String::Base64Encode(String{Flags:AutoMap}) -> String` * `String::Base64Decode(String) -> String?` * `String::Base64StrictDecode(String) -> String?` * `String::EscapeC(String{Flags:AutoMap}) -> String` * `String::UnescapeC(String{Flags:AutoMap}) -> String` * `String::HexEncode(String{Flags:AutoMap}) -> String` * `String::HexDecode(String) -> String?` * `String::EncodeHtml(String{Flags:AutoMap}) -> String` * `String::DecodeHtml(String{Flags:AutoMap}) -> String` * `String::CgiEscape(String{Flags:AutoMap}) -> String` * `String::CgiUnescape(String{Flags:AutoMap}) -> String` * `String::Strip(String{Flags:AutoMap}) -> String` * `String::Collapse(String{Flags:AutoMap}) -> String` * `String::CollapseText(String{Flags:AutoMap}, Uint64) -> String` * `String::AsciiEqualsIgnoreCase(String?, String) -> Bool` Added in the version [2025.02](https://ytsaurus.tech/changelog/2025.02.md#string-module) * `String::Contains(String?, String) -> Bool` * `String::AsciiContainsIgnoreCase(String?, String) -> Bool` Added in the version [2025.02](https://ytsaurus.tech/changelog/2025.02.md#string-module) * `String::Find(String{Flags:AutoMap}, String, [Uint64?]) -> Int64`: Returns the first position found or -1. The optional argument is the offset from the beginning of the string. * `String::ReverseFind(String{Flags:AutoMap}, String, [Uint64?]) -> Int64`: Returns the last position found or -1. The optional argument is the offset from the beginning of the string. * `String::AsciiStartsWithIgnoreCase(String?, String) -> Bool` Added in the version [2025.02](https://ytsaurus.tech/changelog/2025.02.md#string-module) * `String::HasPrefix(String?, String) -> Bool` * `String::HasPrefixIgnoreCase(String?, String) -> Bool` Removed in the version [2025.02](https://ytsaurus.tech/changelog/2025.02.md#string-module) * `String::StartsWith(String?, String) -> Bool` * `String::StartsWithIgnoreCase(String?, String) -> Bool` Removed in the version [2025.02](https://ytsaurus.tech/changelog/2025.02.md#string-module) * `String::AsciiEndsWithIgnoreCase(String?, String) -> Bool` Added in the version [2025.02](https://ytsaurus.tech/changelog/2025.02.md#string-module) * `String::HasSuffix(String?, String) -> Bool` * `String::HasSuffixIgnoreCase(String?, String) -> Bool` Removed in the version [2025.02](https://ytsaurus.tech/changelog/2025.02.md#string-module) * `String::EndsWith(String?, String) -> Bool` * `String::EndsWithIgnoreCase(String?, String) -> Bool` Removed in the version [2025.02](https://ytsaurus.tech/changelog/2025.02.md#string-module) * `String::Substring(String{Flags:AutoMap}, [Uint64?, Uint64?]) -> String` * `String::AsciiToLower(String{Flags:AutoMap}) -> String`: Changes only Latin characters. For working with other alphabets, see Unicode::ToLower * `String::AsciiToUpper(String{Flags:AutoMap}) -> String`: Changes only Latin characters. For working with other alphabets, see Unicode::ToUpper * `String::AsciiToTitle(String{Flags:AutoMap}) -> String`: Changes only Latin characters. For working with other alphabets, see Unicode::ToTitle * `String::SplitToList( String?, String, [ DelimeterString:Bool?, SkipEmpty:Bool?, Limit:Uint64? ]) -> List` The first argument is the source string The second argument is a delimiter The third argument includes the following parameters: - DelimeterString:Bool? — treating a delimiter as a string (true, by default) or a set of characters "any of" (false) - SkipEmpty:Bool? — whether to skip empty strings in the result, is false by default - Limit:Uint64? — Limits the number of fetched components (unlimited by default); if the limit is exceeded, the raw suffix of the source string is returned in the last item * `String::JoinFromList(List{Flags:AutoMap}, String) -> String` * `String::ToByteList(List{Flags:AutoMap}) -> List` * `String::FromByteList(List) -> String` * `String::ReplaceAll(String{Flags:AutoMap}, String, String) -> String`: Arguments: input, find, replacement * `String::ReplaceFirst(String{Flags:AutoMap}, String, String) -> String`: Arguments: input, find, replacement * `String::ReplaceLast(String{Flags:AutoMap}, String, String) -> String`: Arguments: input, find, replacement * `String::RemoveAll(String{Flags:AutoMap}, String) -> String`: The second argument is interpreted as an unordered set of characters to delete * `String::RemoveFirst(String{Flags:AutoMap}, String) -> String`: An unordered set of characters in the second argument, only the first encountered character from set is deleted * `String::RemoveLast(String{Flags:AutoMap}, String) -> String`: An unordered set of characters in the second argument, only the last encountered character from the set is deleted * `String::ReverseBytes(String{Flags:AutoMap}) -> String` - Added in the version [2025.02](https://ytsaurus.tech/changelog/2025.02.md#string-module) Reverses a string, treating it as a byte sequence. * `String::ReverseBits(String{Flags:AutoMap}) -> String` - Added in the version [2025.02](https://ytsaurus.tech/changelog/2025.02.md#string-module) Reverses a string, treating it as a bit sequence. * `String::IsAscii(String{Flags:AutoMap}) -> Bool` * `String::IsAsciiSpace(String{Flags:AutoMap}) -> Bool` * `String::IsAsciiUpper(String{Flags:AutoMap}) -> Bool` * `String::IsAsciiLower(String{Flags:AutoMap}) -> Bool` * `String::IsAsciiAlpha(String{Flags:AutoMap}) -> Bool` * `String::IsAsciiAlnum(String{Flags:AutoMap}) -> Bool` * `String::IsAsciiHex(String{Flags:AutoMap}) -> Bool` * `String::LevensteinDistance(String{Flags:AutoMap}, String{Flags:AutoMap}) -> Uint64` * `String::LeftPad(String{Flags:AutoMap}, Uint64, [String?]) -> String` * `String::RightPad(String{Flags:AutoMap}, Uint64) -> String` * `String::Hex(Uint64{Flags:AutoMap}) -> String` * `String::SHex(Int64{Flags:AutoMap}) -> String` * `String::Bin(Uint64{Flags:AutoMap}) -> String` * `String::SBin(Int64{Flags:AutoMap}) -> String` * `String::HexText(String{Flags:AutoMap}) -> String` * `String::BinText(String{Flags:AutoMap}) -> String` * `String::HumanReadableDuration(Uint64{Flags:AutoMap}) -> String` * `String::HumanReadableQuantity(Uint64{Flags:AutoMap}) -> String` * `String::HumanReadableBytes(Uint64{Flags:AutoMap}) -> String` * `String::Prec(Double{Flags:AutoMap}, Uint64) -> String` * `String::Reverse(String?) -> String?` Removed in the version [2025.02](https://ytsaurus.tech/changelog/2025.02.md#string-module) {% note alert %} The functions from the String library don't support Cyrillic and can only work with ASCII characters. To work with UTF-8 encoded strings, use functions from [Unicode](https://ytsaurus.tech/docs/en/unicode.md). {% endnote %} ## Examples ```yql SELECT String::Base64Encode("YQL"); -- "WVFM" SELECT String::Strip("YQL "); -- "YQL" SELECT String::SplitToList("1,2,3,4,5,6,7", ",", 3 as Limit); -- ["1", "2", "3", "4,5,6,7"] ``` # Unicode Functions for Unicode strings. ## List of functions * `Unicode::IsUtf(String) -> Bool` Checks whether a string is a valid utf-8 sequence. For example, the string `"\xF0"` isn't a valid utf-8 sequence, while the string `"\xF0\x9F\x90\xB1"` correctly describes a utf-8 cat emoji. * `Unicode::GetLength(Utf8{Flags:AutoMap}) -> Uint64` Returns the utf-8 string's length in characters (unicode code points). Surrogate pairs are counted as one character. ```yql SELECT Unicode::GetLength("жніўня"); -- 6 ``` * `Unicode::Find(string:Utf8{Flags:AutoMap}, subString:Utf8, [pos:Uint64?]) -> Uint64?` * `Unicode::RFind(string:Utf8{Flags:AutoMap}, subString:Utf8, [pos:Uint64?]) -> Uint64?` Searches for the first (or the last in the case of `RFind`) substring occurrence in the string starting from the `pos` position. Returns the first character position from the found string. Null is returned in case of failure. ```yql SELECT Unicode::Find("aaa", "bb"); -- Null ``` * `Unicode::Substring(string:Utf8{Flags:AutoMap}, from:Uint64?, len:Uint64?) -> Utf8` Returns a substring from the `string`, starting from the `from` character, with length equaling `len` characters. If the `len` argument is omitted, the substring is taken to the end of the source string. If `from` is greater than the source string length, an empty `""` string is returned. ```yql SELECT Unicode::Substring("0123456789abcdefghij", 10); -- "abcdefghij" ``` * `Unicode::Normalize...` functions reduce the transmitted utf-8 string to a [normal form](https://unicode.org/reports/tr15/#Norm_Forms): * `Unicode::Normalize(Utf8{Flags:AutoMap}) -> Utf8` -- NFC * `Unicode::NormalizeNFD(Utf8{Flags:AutoMap}) -> Utf8` * `Unicode::NormalizeNFC(Utf8{Flags:AutoMap}) -> Utf8` * `Unicode::NormalizeNFKD(Utf8{Flags:AutoMap}) -> Utf8` * `Unicode::NormalizeNFKC(Utf8{Flags:AutoMap}) -> Utf8` * `Unicode::Translit(string:Utf8{Flags:AutoMap}, [lang:String?]) -> Utf8` Transliterates with Latin letters the words from the passed string, consisting entirely of characters of the alphabet of the language passed by the second argument. If no language is specified, the words are transliterated from Russian. Available languages: "kaz", "rus", "tur", and "ukr". ```yql SELECT Unicode::Translit("Тот уголок земли, где я провел"); -- "Tot ugolok zemli, gde ya provel" ``` * `Unicode::LevensteinDistance(stringA:Utf8{Flags:AutoMap}, stringB:Utf8{Flags:AutoMap}) -> Uint64` Calculates the Levenshtein distance for the passed strings. * `Unicode::Fold(Utf8{Flags:AutoMap}, [ Language:String?, DoLowerCase:Bool?, DoRenyxa:Bool?, DoSimpleCyr:Bool?, FillOffset:Bool? ]) -> Utf8` Performs [case folding](https://www.w3.org/TR/charmod-norm/#definitionCaseFolding) for the transmitted string. Parameters: - `Language` is set according to the same rules as in `Unicode::Translit()`. - `DoLowerCase` converts a string to lowercase letters, `true` by default. - `DoRenyxa` converts diacritical characters to similar Latin characters, `true` by default. - `DoSimpleCyr` converts diacritical Cyrillic characters to similar Latin characters, `true` by default. - `FillOffset` parameter is not used. ```yql SELECT Unicode::Fold("Kongreßstraße", false AS DoSimpleCyr, false AS DoRenyxa); -- "kongressstrasse" SELECT Unicode::Fold("ҫурт"); -- "сурт" SELECT Unicode::Fold("Eylül", "Turkish" AS Language); -- "eylul" ``` * `Unicode::ReplaceAll(input:Utf8{Flags:AutoMap}, find:Utf8, replacement:Utf8) -> Utf8` * `Unicode::ReplaceFirst(input:Utf8{Flags:AutoMap}, find:Utf8, replacement:Utf8) -> Utf8` * `Unicode::ReplaceLast(input:Utf8{Flags:AutoMap}, find:Utf8, replacement:Utf8) -> Utf8` Replaces all/first/last occurrence(s) of the `find` string in `input` through `replacement`. * `Unicode::RemoveAll(input:Utf8{Flags:AutoMap}, symbols:Utf8) -> Utf8` * `Unicode::RemoveFirst(input:Utf8{Flags:AutoMap}, symbols:Utf8) -> Utf8` * `Unicode::RemoveLast(input:Utf8{Flags:AutoMap}, symbols:Utf8) -> Utf8` Removes all/first/last occurrence(s) of characters defined in a `symbols` set from `input`. The second argument is interpreted as an unordered set of characters to be deleted. ```yql SELECT Unicode::ReplaceLast("absence", "enc", ""); -- "abse" SELECT Unicode::RemoveAll("abandon", "an"); -- "bdo" ``` * `Unicode::ToCodePointList(Utf8{Flags:AutoMap}) -> List` Splits the string into a unicode sequence of codepoints. * `Unicode::FromCodePointList(List{Flags:AutoMap}) -> Utf8` Forms a unicode string from codepoints. ```yql SELECT Unicode::ToCodePointList("Щавель"); -- [1065, 1072, 1074, 1077, 1083, 1100] SELECT Unicode::FromCodePointList(AsList(99,111,100,101,32,112,111,105,110,116,115,32,99,111,110,118,101,114,116,101,114)); -- "code points converter" ``` * `Unicode::Reverse(Utf8{Flags:AutoMap}) -> Utf8` Reverses the string. * `Unicode::ToLower(Utf8{Flags:AutoMap}) -> Utf8` * `Unicode::ToUpper(Utf8{Flags:AutoMap}) -> Utf8` * `Unicode::ToTitle(Utf8{Flags:AutoMap}) -> Utf8` Changes the character case in the string to upper, lower, or title case. * `Unicode::SplitToList( string:Utf8?, separator:Utf8, [ DelimeterString:Bool?, SkipEmpty:Bool?, Limit:Uint64? ]) -> List` Splits the string into substrings with a separator. `string` — Source string. `separator` — Separator. Parameters: - DelimeterString:Bool? — treating a separator as a string (`true` by default) or a set of characters "any of" (`false`). - SkipEmpty:Bool? — whether to skip empty strings in the result, `false` by default. - Limit:Uint64? — limits the number of fetched components (unlimited by default); if the limit is exceeded, the raw suffix of the source string is returned in the last item. * `Unicode::JoinFromList(List{Flags:AutoMap}, separator:Utf8) -> Utf8` Concatenates a list of strings into a single string using a `separator`. ```yql SELECT Unicode::SplitToList("One, two, three, four, five", ", ", 2 AS Limit); -- ["One", "two", "three, four, five"] SELECT Unicode::JoinFromList(["One", "two", "three", "four", "five"], ";"); -- "One;two;three;four;five" ``` * `Unicode::ToUint64(string:Utf8{Flags:AutoMap}, [prefix:Uint16?]) -> Uint64` Converts from the string into a number. The second optional argument sets the numeral system. By default, 0 (automatic detection by prefix). Supported prefixes: `0x(0X)` (base-16), `0` (base-8). Default system: base-10. The `-` sign before a number is interpreted as in the C's unsigned arithmetic. For example, `-0x1` -> UI64_MAX. If a string contains invalid characters, or a number is beyond ui64, the function finishes with an error. * `Unicode::TryToUint64(string:Utf8{Flags:AutoMap}, [prefix:Uint16?]) -> Uint64?` Similar to the `Unicode::ToUint64()` function, but returns `NULL` instead of an error. ```yql SELECT Unicode::ToUint64("77741"); -- 77741 SELECT Unicode::ToUint64("-77741"); -- 18446744073709473875 SELECT Unicode::TryToUint64("asdh831"); -- Null ``` * `Unicode::Strip(string:Utf8{Flags:AutoMap}) -> Utf8` Cuts off the string's outermost characters of Space Unicode category. ```yql SELECT Unicode::Strip("\u2002ыкль\u2002"u); -- "ыкль" ``` * `Unicode::IsAscii(string:Utf8{Flags:AutoMap}) -> Bool` Checks whether the utf-8 string consists exclusively of ASCII characters. * `Unicode::IsSpace(string:Utf8{Flags:AutoMap}) -> Bool` * `Unicode::IsUpper(string:Utf8{Flags:AutoMap}) -> Bool` * `Unicode::IsLower(string:Utf8{Flags:AutoMap}) -> Bool` * `Unicode::IsAlpha(string:Utf8{Flags:AutoMap}) -> Bool` * `Unicode::IsAlnum(string:Utf8{Flags:AutoMap}) -> Bool` * `Unicode::IsHex(string:Utf8{Flags:AutoMap}) -> Bool` Checks whether the utf-8 string satisfies the specified condition. * `Unicode::IsUnicodeSet(string:Utf8{Flags:AutoMap}, unicode_set:Utf8) -> Bool` Checks whether the utf-8 `string` consists exclusively of characters indicated in `unicode_set`. Characters in `unicode_set` must be indicated in square brackets. ```yql SELECT Unicode::IsUnicodeSet("ваоао"u, "[вао]"u); -- true SELECT Unicode::IsUnicodeSet("ваоао"u, "[ваб]"u); -- false ``` # Url ## Normalize {#normalize} * `Url::Normalize(String) -> String?` Normalizes the URL in a robot-friendly way: converts the hostname into lowercase, strips out certain fragments, and so on. The normalization result only depends on the URL itself. The normalization **DOES NOT** include operations depending on the external data: transformation based on duplicates, mirrors, etc. Returned value: * Normalized URL. * `NULL`, if the passed string argument can't be parsed as a URL. #### Examples ```yql SELECT Url::Normalize("hTTp://wWw.yDb.TECH/"); -- "http://www.ydb.tech/" SELECT Url::Normalize("http://ydb.tech#foo"); -- "http://ydb.tech/" ``` ## NormalizeWithDefaultHttpScheme {#normalizewithdefaulthttpscheme} * `Url::NormalizeWithDefaultHttpScheme(String?) -> String?` Normalizes similarly to `Url::Normalize`, but inserts the `http://` schema in case there is no schema. Returned value: * Normalized URL. * Source URL, if the normalization has failed. #### Examples ```yql SELECT Url::NormalizeWithDefaultHttpScheme("wWw.yDb.TECH"); -- "http://www.ydb.tech/" SELECT Url::NormalizeWithDefaultHttpScheme("http://ydb.tech#foo"); -- "http://ydb.tech/" ``` ## Encode / Decode {#encode} Encode a UTF-8 string to the urlencoded format (`Url::Encode`) and back (`Url::Decode`). #### List of functions * `Url::Encode(String?) -> String?` * `Url::Decode(String?) -> String?` #### Examples ```yql SELECT Url::Decode("http://ydb.tech/%D1%81%D1%82%D1%80%D0%B0%D0%BD%D0%B8%D1%86%D0%B0"); -- "http://ydb.tech/page" SELECT Url::Encode("http://ydb.tech/page"); -- "http://ydb.tech/%D1%81%D1%82%D1%80%D0%B0%D0%BD%D0%B8%D1%86%D0%B0" ``` ## Parse {#parse} Parses the URL into parts. ```yql Url::Parse(Parse{Flags:AutoMap}) -> Struct< Frag: String?, Host: String?, ParseError: String?, Pass: String?, Path: String?, Port: String?, Query: String?, Scheme: String?, User: String? > ``` #### Examples ```yql SELECT Url::Parse( "https://en.wikipedia.org/wiki/Isambard_Kingdom_Brunel?s=24&g=h-24#Great_Western_Railway"); /* ( "Frag": "Great_Western_Railway", "Host": "en.wikipedia.org", "ParseError": null, "Pass": null, "Path": "/wiki/Isambard_Kingdom_Brunel", "Port": null, "Query": "s=24&g=h-24", "Scheme": "https", "User": null ) */ ``` ## Get... {#get} Get a component of the URL. #### List of functions * `Url::GetScheme(String{Flags:AutoMap}) -> String` * `Url::GetHost(String?) -> String?` * `Url::GetHostPort(String?) -> String?` * `Url::GetSchemeHost(String?) -> String?` * `Url::GetSchemeHostPort(String?) -> String?` * `Url::GetPort(String?) -> String?` * `Url::GetTail(String?) -> String?` -- everything following the host: path + query + fragment * `Url::GetPath(String?) -> String?` * `Url::GetFragment(String?) -> String?` * `Url::GetCGIParam(String?, String) -> String?` -- The second parameter is the name of the intended CGI parameter. * `Url::GetDomain(String?, Uint8) -> String?` -- The second parameter is the required domain level. * `Url::GetTLD(String{Flags:AutoMap}) -> String` * `Url::IsKnownTLD(String{Flags:AutoMap}) -> Bool` -- Registered on [iana.org](http://www.iana.org/). * `Url::IsWellKnownTLD(String{Flags:AutoMap}) -> Bool` -- Belongs to a small whitelist of com, net, org, ru, and so on. * `Url::GetDomainLevel(String{Flags:AutoMap}) -> Uint64` * `Url::GetSignificantDomain(String{Flags:AutoMap}, [List?]) -> String` Returns a second-level domain in most cases and a third-level domain for the hostnames like: `***.XXX.YY`, where `XXX` is com, net, org, co, gov, or edu. You can redefine this list using an optional second argument * `Url::GetOwner(String{Flags:AutoMap}) -> String` Returns the domain that's most likely owned by an individual or organization. Unlike `Url::GetSignificantDomain`, it uses a special whitelist. Besides the `***.co.uk` domains, it can return a third-level domain used by free hosting sites and blogs (for example: something.livejournal.com) #### Examples ```yql SELECT Url::GetScheme("https://ydb.tech"); -- "https://" SELECT Url::GetDomain("http://www.ydb.tech", 2); -- "ydb.tech" ``` ## Cut... {#cut} * `Url::CutScheme(String?) -> String?` Returns the passed URL without the schema (`http://`, `https://`, etc.). * `Url::CutWWW(String?) -> String?` Returns the passed domain without the "www." prefix (if any). * `Url::CutWWW2(String?) -> String?` Returns the passed domain without the prefixes like "www.", "www2.", "wwww777." (if any). * `Url::CutQueryStringAndFragment(String{Flags:AutoMap}) -> String` Returns a copy of the passed URL, stripping out all the CGI parameters and fragments ("?foo=bar" and/or "#baz"). #### Examples ```yql SELECT Url::CutScheme("http://www.ydb.tech"); -- "www.ydb.tech" SELECT Url::CutWWW("www.ydb.tech"); -- "ydb.tech" ``` ## ...Punycode... {#punycode} [Punycode](https://en.wikipedia.org/wiki/Punycode) transformations. #### List of functions * `Url::HostNameToPunycode(String{Flag:AutoMap}) -> String?` * `Url::ForceHostNameToPunycode(String{Flag:AutoMap}) -> String` * `Url::PunycodeToHostName(String{Flag:AutoMap}) -> String?` * `Url::ForcePunycodeToHostName(String{Flag:AutoMap}) -> String` * `Url::CanBePunycodeHostName(String{Flag:AutoMap}) -> Bool` #### Examples ```yql SELECT Url::PunycodeToHostName("xn--80aniges7g.xn--j1aef"); -- "example.com" ``` ## ...Query... {#query} [Query](https://docs.python.org/3/library/urllib.parse.html) transformations. #### List of functions ```yql Url::QueryStringToList(String{Flag:AutoMap}, [ KeepBlankValues:Bool?, -- Empty values in percent-encoded queries are interpreted as empty strings, defaults to false. Strict:Bool?, -- If false, parsing errors are ignored and incorrect fields are skipped, defaults to true. MaxFields:Uint32?, -- The maximum number of fields. If exceeded, an exception is thrown. Defaults to Max. Separator:String? -- A key-value pair separator, defaults to '&'. ]) -> List> Url::QueryStringToDict(String{Flag:AutoMap}, [ KeepBlankValues:Bool?, -- Empty values in percent-encoded queries are interpreted as empty strings, defaults to false. Strict:Bool?, -- If false, parsing errors are ignored and incorrect fields are skipped, defaults to true. MaxFields:Uint32?, -- The maximum number of fields. If exceeded, an exception is thrown. Defaults to Max. Separator:String? -- A key-value pair separator, defaults to '&'. ]) -> Dict> Url::BuildQueryString(Dict>{Flag:AutoMap}, [ Separator:String? -- A key-value pair separator, defaults to '&'. ]) -> String Url::BuildQueryString(Dict{Flag:AutoMap}, [ Separator:String? -- A key-value pair separator, defaults to '&'. ]) -> String Url::BuildQueryString(List>{Flag:AutoMap}, [ Separator:String? -- A key-value pair separator, defaults to '&'. ]) -> String ``` #### Examples ```yql SELECT Url::QueryStringToList("a=1&b=2&a=3"); -- [("a", "1"), ("b", "2"), ("a", "3")] SELECT Url::QueryStringToDict("a=1&b=2&a=3"); -- {"b" : ["2"], "a" : ["1", "3"]} SELECT Url::BuildQueryString([("a", "1"), ("a", "3"), ("b", "2")]); -- "a=1&a=3&b=2" SELECT Url::BuildQueryString({"a" : "1", "b" : "2"}); -- "b=2&a=1" SELECT Url::BuildQueryString({"a" : ["1", "3"], "b" : ["2", "4"]}); -- "b=2&b=4&a=1&a=3" ``` # Yson [YSON](https://ytsaurus.tech/docs/en/user-guide/storage/yson) is a data format similar to JSON developed by Yandex. * Similarities with JSON: * Does not have a strict scheme. * Besides simple data types, it supports dictionaries and lists in arbitrary combinations. * Some differences from JSON: * It also has a binary representation in addition to the text representation. * The text representation uses semicolons instead of commas and equal signs instead of colons. * The concept of "attributes" is supported, that is, named properties that can be assigned to a node in the tree. Implementation specifics and functionality of the module: * Along with YSON, this module also supports standard JSON to expand the application scope in a way. * It works with a DOM representation of YSON in memory that in YQL terms is passed between functions as a "resource" (see the [description of special data types](https://ytsaurus.tech/types/special.md)). Most of the module's functions have the semantics of a query to perform a specified operation with a resource and return an empty [optional](https://ytsaurus.tech/types/optional.md) type if the operation failed because the actual data type mismatched the expected one. * Provides several main classes of functions (find below a complete list and detailed description of functions): * `Yson::Parse***`: Getting a resource with a DOM object from serialized data, with all further operations performed on the obtained resource. * `Yson::From`: Getting a resource with a DOM object from simple YQL data types or containers (lists or dictionaries). * `Yson::ConvertTo***`: Converting a resource to [primitive data types](https://ytsaurus.tech/types/primitive.md) or [containers](https://ytsaurus.tech/types/containers.md). * `Yson::Lookup***`: Getting a single list item or a dictionary with optional conversion to the relevant data type. * `Yson::YPath***`: Getting one element from the document tree based on the relative path specified, optionally converting it to the relevant data type. * `Yson::Serialize***`: Getting a copy of data from the resource and serializing the data in one of the formats. * For convenience, when serialized Yson and Json are passed to functions expecting a resource with a DOM object, implicit conversion using `Yson::Parse` or `Yson::ParseJson` is done automatically. In SQL syntax, the dot or square brackets operator automatically adds a `Yson::Lookup` call. To serialize a resource, you still need to call `Yson::ConvertTo***` or `Yson::Serialize***`. It means that, for example, to get the "foo" element as a string from the Yson column named mycolumn and serialized as a dictionary, you can write: `SELECT Yson::ConvertToString(mycolumn["foo"]) FROM mytable;` or `SELECT Yson::ConvertToString(mycolumn.foo) FROM mytable;`. In the variant with a dot, special characters can be escaped by [general rules for IDs](https://ytsaurus.tech/syntax/expressions.md#escape). The module's functions must be considered as "building blocks" from which you can assemble different structures, for example: * `Yson::Parse*** -> Yson::Serialize***`: Converting from one format to other. * `Yson::Parse*** -> Yson::Lookup -> Yson::Serialize***`: Extracting the value of the specified subtree in the source YSON tree. * `Yson::Parse*** -> Yson::ConvertToList -> ListMap -> Yson::Lookup***`: Extracting items by a key from the YSON list. ## Examples ```yql $node = Json(@@ {"abc": {"def": 123, "ghi": "hello"}} @@); SELECT Yson::SerializeText($node.abc) AS `yson`; -- {"def"=123;"ghi"="\xD0\xBF\xD1\x80\xD0\xB8\xD0\xB2\xD0\xB5\xD1\x82"} ``` ```yql $node = Yson(@@ [ {abc=123; def=456}; {abc=234; xyz=789}; ] @@); $attrs = Yson::YPath($node, "/@"); SELECT ListMap(Yson::ConvertToList($node), ($x) -> { return Yson::LookupInt64($x, "abc") }) AS abcs, Yson::ConvertToStringDict($attrs) AS attrs, Yson::SerializePretty(Yson::Lookup($node, "7", Yson::Options(false AS Strict))) AS miss; /* - abcs: `[123; 234]` - attrs: `{"a"="z";"x"="y"}` - miss: `NULL` */ ``` ## Yson::Parse... {#ysonparse} ```yql Yson::Parse(Yson{Flags:AutoMap}) -> Resource<'Yson2.Node'> Yson::ParseJson(Json{Flags:AutoMap}) -> Resource<'Yson2.Node'> Yson::ParseJsonDecodeUtf8(Json{Flags:AutoMap}) -> Resource<'Yson2.Node'> Yson::Parse(String{Flags:AutoMap}) -> Resource<'Yson2.Node'>? -- accepts YSON in any format Yson::ParseJson(String{Flags:AutoMap}) -> Resource<'Yson2.Node'>? Yson::ParseJsonDecodeUtf8(String{Flags:AutoMap}) -> Resource<'Yson2.Node'>? ``` The result of all three functions is non-serializable: it can only be passed as the input to other function from the Yson library. However, you can't save it to a table or return to the client as a result of the operation: such an attempt results in a typing error. You also can't return it outside [subqueries](https://ytsaurus.tech/syntax/select/index.md): if you need to do this, call [Yson::Serialize](https://ytsaurus.tech/docs/en/#ysonserialize), and the optimizer will remove unnecessary serialization and deserialization if materialization isn't needed in the end. {% note info %} The `Yson::ParseJsonDecodeUtf8` expects that characters outside the ASCII range must be additionally escaped. {% endnote %} ## Yson::From {#ysonfrom} ```yql Yson::From(T) -> Resource<'Yson2.Node'> ``` `Yson::From` is a polymorphic function that converts most primitive data types and containers (lists, dictionaries, tuples, structures, and so on) into a Yson resource. The source object type must be Yson-compatible. For example, in dictionary keys, you can only use the `String` or `Utf8` data types, but not `String?` or `Utf8?` . #### Example ```yql SELECT Yson::Serialize(Yson::From(TableRow())) FROM table1; ``` ## Yson::WithAttributes ```yql Yson::WithAttributes(Resource<'Yson2.Node'>{Flags:AutoMap}, Resource<'Yson2.Node'>{Flags:AutoMap}) -> Resource<'Yson2.Node'>? ``` Adds attributes (the second argument) to the Yson node (the first argument). The attributes must constitute a map node. ## Yson::Equals ```yql Yson::Equals(Resource<'Yson2.Node'>{Flags:AutoMap}, Resource<'Yson2.Node'>{Flags:AutoMap}) -> Bool ``` Checking trees in memory for equality. The operation is tolerant to the source serialization format and the order of keys in dictionaries. ## Yson::GetHash ```yql Yson::GetHash(Resource<'Yson2.Node'>{Flags:AutoMap}) -> Uint64 ``` Calculating a 64-bit hash from an object tree. ## Yson::Is... {#ysonis} ```yql Yson::IsEntity(Resource<'Yson2.Node'>{Flags:AutoMap}) -> bool Yson::IsString(Resource<'Yson2.Node'>{Flags:AutoMap}) -> bool Yson::IsDouble(Resource<'Yson2.Node'>{Flags:AutoMap}) -> bool Yson::IsUint64(Resource<'Yson2.Node'>{Flags:AutoMap}) -> bool Yson::IsInt64(Resource<'Yson2.Node'>{Flags:AutoMap}) -> bool Yson::IsBool(Resource<'Yson2.Node'>{Flags:AutoMap}) -> bool Yson::IsList(Resource<'Yson2.Node'>{Flags:AutoMap}) -> bool Yson::IsDict(Resource<'Yson2.Node'>{Flags:AutoMap}) -> bool Yson::IsUtf8(Resource<'Yson2.Node'>{Flags:AutoMap}) -> bool ``` Checking that the current node has the appropriate type. The Entity is `#`. The `Yson::IsUtf8` function is available starting from version [2026.01](https://ytsaurus.tech/changelog/2026.01.md#yson-module). It returns success only for nodes obtained from `Yson::ParseJson`/`Yson::ParseJsonDecodeUtf8`, or if the string node was built via `Yson::From` from the `Utf8` type. ## Yson::GetLength ```yql Yson::GetLength(Resource<'Yson2.Node'>{Flags:AutoMap}) -> Uint64? ``` Getting the number of elements in a list or dictionary. ## Yson::ConvertTo... {#ysonconvertto} ```yql Yson::ConvertTo(Resource<'Yson2.Node'>{Flags:AutoMap}, Type) -> T Yson::ConvertToBool(Resource<'Yson2.Node'>{Flags:AutoMap}) -> Bool? Yson::ConvertToInt64(Resource<'Yson2.Node'>{Flags:AutoMap}) -> Int64? Yson::ConvertToUint64(Resource<'Yson2.Node'>{Flags:AutoMap}) -> Uint64? Yson::ConvertToDouble(Resource<'Yson2.Node'>{Flags:AutoMap}) -> Double? Yson::ConvertToString(Resource<'Yson2.Node'>{Flags:AutoMap}) -> String? Yson::ConvertToList(Resource<'Yson2.Node'>?) -> List> Yson::ConvertToBoolList(Resource<'Yson2.Node'>{Flags:AutoMap}) -> List Yson::ConvertToInt64List(Resource<'Yson2.Node'>{Flags:AutoMap}) -> List Yson::ConvertToUint64List(Resource<'Yson2.Node'>{Flags:AutoMap}) -> List Yson::ConvertToDoubleList(Resource<'Yson2.Node'>{Flags:AutoMap}) -> List Yson::ConvertToStringList(Resource<'Yson2.Node'>{Flags:AutoMap}) -> List Yson::ConvertToDict(Resource<'Yson2.Node'>?) -> Dict> Yson::ConvertToBoolDict(Resource<'Yson2.Node'>{Flags:AutoMap}) -> Dict Yson::ConvertToInt64Dict(Resource<'Yson2.Node'>{Flags:AutoMap}) -> Dict Yson::ConvertToUint64Dict(Resource<'Yson2.Node'>{Flags:AutoMap}) -> Dict Yson::ConvertToDoubleDict(Resource<'Yson2.Node'>{Flags:AutoMap}) -> Dict Yson::ConvertToStringDict(Resource<'Yson2.Node'>{Flags:AutoMap}) -> Dict ``` {% note warning %} These functions do not do implicit type casting by default, that is, the value in the argument must exactly match the function called. {% endnote %} `Yson::ConvertTo` is a polymorphic function that converts the data type that is specified in the second argument and supports containers (lists, dictionaries, tuples, structures, and so on) into a Yson resource. Starting from version [2025.05](https://ytsaurus.tech/changelog/2025.05.md#yson-module), it's recommended to use [As*/TryAs*](https://ytsaurus.tech/docs/en/#ysonas) functions for casting a Yson node to a specific primitive type, list, or dictionary, and `Yson::ConvertTo` in all other cases. #### Example ```yql $data = Yson(@@{ "name" = "Anya"; "age" = 15u; "params" = { "ip" = "95.106.17.32"; "last_time_on_site" = 0.5; "region" = 213; "user_agent" = "Mozilla/5.0" } }@@); SELECT Yson::ConvertTo($data, Struct< name: String, age: Uint32, params: Dict > ); ``` ## Yson::Contains {#ysoncontains} ```yql Yson::Contains(Resource<'Yson2.Node'>{Flags:AutoMap}, String) -> Bool? ``` Checks for a key in the dictionary. If the object type is a map, then it searches among the keys. If the object type is a list, then the key must be a decimal number, i.e., an index in the list. ## Yson::Lookup... {#ysonlookup} ```yql Yson::Lookup(Resource<'Yson2.Node'>{Flags:AutoMap}, String) -> Resource<'Yson2.Node'>? Yson::LookupBool(Resource<'Yson2.Node'>{Flags:AutoMap}, String) -> Bool? Yson::LookupInt64(Resource<'Yson2.Node'>{Flags:AutoMap}, String) -> Int64? Yson::LookupUint64(Resource<'Yson2.Node'>{Flags:AutoMap}, String) -> Uint64? Yson::LookupDouble(Resource<'Yson2.Node'>{Flags:AutoMap}, String) -> Double? Yson::LookupString(Resource<'Yson2.Node'>{Flags:AutoMap}, String) -> String? Yson::LookupDict(Resource<'Yson2.Node'>{Flags:AutoMap}, String) -> Dict>? Yson::LookupList(Resource<'Yson2.Node'>{Flags:AutoMap}, String) -> List>? ``` The above functions are short notations for a typical use case: `Yson::YPath`: go to a level in the dictionary and then extract the value — `Yson::ConvertTo***`. For all the listed functions, the second argument is a key name from the dictionary (unlike YPath, it has no `/`prefix) or an index from the list (for example, `7`). They simplify the query and produce a small gain in speed. {% note warning %} Starting from version [2025.05](https://ytsaurus.tech/changelog/2025.05.md#yson-module), it's recommended to use `Yson::Lookup` to retrieve a node by key and [As*/TryAs*](https://ytsaurus.tech/docs/en/#ysonas) functions for further type narrowing. In most cases, instead of multiple `Yson::Lookup` calls, consider converting data using `Yson::ConvertTo` into a set of dictionaries, lists, and primitive types. {% endnote %} ## Yson::YPath {#ysonypath} ```yql Yson::YPath(Resource<'Yson2.Node'>{Flags:AutoMap}, String) -> Resource<'Yson2.Node'>? Yson::YPathBool(Resource<'Yson2.Node'>{Flags:AutoMap}, String) -> Bool? Yson::YPathInt64(Resource<'Yson2.Node'>{Flags:AutoMap}, String) -> Int64? Yson::YPathUint64(Resource<'Yson2.Node'>{Flags:AutoMap}, String) -> Uint64? Yson::YPathDouble(Resource<'Yson2.Node'>{Flags:AutoMap}, String) -> Double? Yson::YPathString(Resource<'Yson2.Node'>{Flags:AutoMap}, String) -> String? Yson::YPathDict(Resource<'Yson2.Node'>{Flags:AutoMap}, String) -> Dict>? Yson::YPathList(Resource<'Yson2.Node'>{Flags:AutoMap}, String) -> List>? ``` Lets you get a part of the resource based on the source resource and the part's path in YPath format. {% note warning %} Starting from version [2025.05](https://ytsaurus.tech/changelog/2025.05.md#yson-module), it's recommended to use `Yson::YPath` to retrieve a node by key and [As*/TryAs*](https://ytsaurus.tech/docs/en/#ysonas) functions for further type narrowing. In most cases, instead of multiple `Yson::YPath` calls, consider converting data using `Yson::ConvertTo` into a set of dictionaries, lists, and primitive types. {% endnote %} ## Yson::Attributes {#ysonattributes} ```yql Yson::Attributes(Resource<'Yson2.Node'>{Flags:AutoMap}) -> Dict> ``` Getting all node attributes as a dictionary. ## Yson::Serialize... {#ysonserialize} ```yql Yson::Serialize(Resource<'Yson2.Node'>{Flags:AutoMap}) -> Yson -- A binary representation Yson::SerializeText(Resource<'Yson2.Node'>{Flags:AutoMap}) -> Yson Yson::SerializePretty(Resource<'Yson2.Node'>{Flags:AutoMap}) -> Yson -- To get a text result, wrap it in ToBytes(...) ``` ## Yson::SerializeJson {#ysonserializejson} ```yql Yson::SerializeJson(Resource<'Yson2.Node'>{Flags:AutoMap}, [Resource<'Yson2.Options'>?, SkipMapEntity:Bool?, EncodeUtf8:Bool?, WriteNanAsString:Bool?]) -> Json? ``` * `SkipMapEntity` serializes `#` values in dictionaries. The value of attributes is not affected by the flag. By default, `false`. * `EncodeUtf8` responsible for escaping non-ASCII characters. By default, `false`. * `WriteNanAsString` allows serializing `NaN` and `Inf` values as strings. By default, `false`. The `Yson` and `Json` data types returned by serialization functions are special cases of a string that is known to contain data in the given format (Yson/Json). ## Yson::Options {#ysonoptions} ```yql Yson::Options([AutoConvert:Bool?, Strict:Bool?]) -> Resource<'Yson2.Options'> ``` It's passed in the last optional argument (omitted for brevity) to the methods `Parse...`, `ConvertTo...`, `Contains`, `Lookup...`, and `YPath...` that accept the result of the `Yson::Options` call. By default, all the `Yson::Options` fields are false and when enabled (true), they modify the behavior as follows: * **AutoConvert**: If the value passed to Yson doesn't match the result data type exactly, the value is converted where possible. For example, `Yson::ConvertToInt64` in this mode will convert even Double numbers to Int64. * **Strict**: By default, all functions from the Yson library return an error in case of issues during query execution (for example, an attempt to parse a string that is not Yson/Json, or an attempt to search by a key in a scalar type, or when a conversion to an incompatible data type has been requested, and so on). If you disable the strict mode, `NULL` is returned instead of an error in most cases. When converting to a dictionary or list (`ConvertToDict` or `ConvertToList`), improper items are excluded from the resulting collection. #### Example ```yql $yson = @@{y = true; x = 5.5}@@y; SELECT Yson::LookupBool($yson, "z"); --- null SELECT Yson::LookupBool($yson, "y"); --- true -- SELECT Yson::LookupInt64($yson, "x"); --- Error occurs SELECT Yson::LookupInt64($yson, "x", Yson::Options(false as Strict)); --- null SELECT Yson::LookupInt64($yson, "x", Yson::Options(true as AutoConvert)); --- 5 -- SELECT Yson::ConvertToBoolDict($yson); --- Error occurs SELECT Yson::ConvertToBoolDict($yson, Yson::Options(false as Strict)); --- { "y": true } SELECT Yson::ConvertToDoubleDict($yson, Yson::Options(false as Strict)); --- { "x": 5.5 } ``` If you need to use the same Yson library settings throughout the query, it's more convenient to use [PRAGMA yson.AutoConvert;](https://ytsaurus.tech/syntax/pragma/yson.md#autoconvert) and/or [PRAGMA yson.Strict;](https://ytsaurus.tech/syntax/pragma/yson.md#strict). Only with these `PRAGMA` you can affect implicit calls to the Yson library occurring when you work with Yson/Json data types. {% note warning %} If you explicitly pass a `Yson::Options` object, the default values of its fields may differ from the settings used when no options are passed at all. For example, `Yson::Parse` works in strict mode by default, but if you create and pass an options object without specifying the `Strict` field, it will be set to `false` and the method will work in non-strict mode. {% endnote %} ## Yson::Iterate {#ysoniterate} ```yql Yson::Iterate(Resource<'Yson2.Node'>{Flags:AutoMap}) -> List, 'PreValue':Resource<'Yson2.Node'>, 'Value':Resource<'Yson2.Node'>>> ``` Added in version [2025.05](https://ytsaurus.tech/changelog/2025.05.md#yson-module). Retrieves a full list of events when traversing a Yson tree. Leaf nodes (`Entity`, `Bool`, `Int64`, `Uint64`, `Double`, `String`) are returned as `Value` events. For a `List` node, the output contains the following sequence: * `PreValue` containing the node itself * `BeginList` * `Item` (precedes each `List` element) * Events for the `List` element * `EndList` * `PostValue` containing the node itself For a `Map` node, the output contains the following sequence: * `PreValue` containing the node itself * `BeginMap` * `Key` (precedes each `Map` element) * Events for the `Map` element * `EndMap` * `PostValue` containing the node itself The order of returned keys is arbitrary. For a node with non-empty attributes, the output contains the following sequence: * `PreValue` containing the node itself * `BeginAttributes` * `Key` (precedes each attribute name) * Events for the attribute * `EndAttributes` * Events for the node, without attributes * `PostValue` containing the node itself The order of returned attributes is arbitrary. #### Examples ```yql -- view the all function output Yson::Iterate $dump = ($x) -> ( ( Way($x), $x.Key, Yson::Serialize($x.PreValue), Yson::Serialize($x.Value), Yson::Serialize($x.PostValue) ) ); SELECT ListMap(Yson::Iterate('{a=1;b=[2u;%true;#;-3.2]}'y), $dump); /* Events: PreValue [1] BeginMap Key a Value 1 Key b PreValue [2] BeginAttributes Key c Value foo EndAttributes PreValue [3] BeginList Item Value 2 Item Value %true Item Value # Item Value -3.2 EndList PostValue [3] PostValue [2] EndMap PostValue [1] */ ``` ```yql -- Getting a the list values - opening all the lists $yson = '[[1;2];[3;4]]'y; SELECT ListFlatMap(Yson::Iterate($yson), ($x)->(IF($x.Value IS NOT NULL, $x.Value))); -- [1;2;3;4] ``` ```yql -- Searching a key with a given name of any level $yson = '{a={b={c=1}};e={f=2}}'y; SELECT ListHasItems(ListFilter(Yson::Iterate($yson), ($x)->($x.Key == 'b'))); -- true ``` ```yql -- Searching a string in a values of any level $yson = '{a={b={c="x"}};e={f="y"}}'y; SELECT ListHasItems(ListFilter(Yson::Iterate($yson), ($x)->(Yson::ConvertToString($x.Value) == 'y'))); -- true ``` ```yql -- Getting attributes `name` for all Map nodes without an attribute `children` $yson = @@{ name=foo; children=[ { name=bar } ] }@@y; SELECT ListFlatMap(Yson::Iterate($yson), ($x)->( IF(Yson::IsDict($x.PreValue) and not Yson::Contains($x.PreValue,'children'), Yson::LookupString($x.PreValue, 'name')))); -- [bar] ``` ## Yson::As... and Yson::TryAs... {#ysonas} ```yql Yson::AsString(Resource<'Yson2.Node'>{Flags:AutoMap}) -> String Yson::AsDouble(Resource<'Yson2.Node'>{Flags:AutoMap}) -> Double Yson::AsUint64(Resource<'Yson2.Node'>{Flags:AutoMap}) -> Uint64 Yson::AsInt64(Resource<'Yson2.Node'>{Flags:AutoMap}) -> Int64 Yson::AsBool(Resource<'Yson2.Node'>{Flags:AutoMap}) -> Bool Yson::AsList(Resource<'Yson2.Node'>{Flags:AutoMap}) -> List> Yson::AsDict(Resource<'Yson2.Node'>{Flags:AutoMap}) -> Dict> Yson::AsUtf8(Resource<'Yson2.Node'>{Flags:AutoMap}) -> Utf8 Yson::TryAsString(Resource<'Yson2.Node'>{Flags:AutoMap}) -> String? Yson::TryAsDouble(Resource<'Yson2.Node'>{Flags:AutoMap}) -> Double? Yson::TryAsUint64(Resource<'Yson2.Node'>{Flags:AutoMap}) -> Uint64? Yson::TryAsInt64(Resource<'Yson2.Node'>{Flags:AutoMap}) -> Int64? Yson::TryAsBool(Resource<'Yson2.Node'>{Flags:AutoMap}) -> Bool? Yson::TryAsList(Resource<'Yson2.Node'>{Flags:AutoMap}) -> List>? Yson::TryAsDict(Resource<'Yson2.Node'>{Flags:AutoMap}) -> Dict>? Yson::TryAsUtf8(Resource<'Yson2.Node'>{Flags:AutoMap}) -> Utf8? ``` Added in version [2025.05](https://ytsaurus.tech/changelog/2025.05.md#yson-module). Casts a Yson node to the specified type. If the specified type isn't a valid Yson node type, `TryAs*` functions return `NULL`, while `As*` functions return a query error. To process a node with the `Entity` ('#') type, use the [`IsEntity`](https://ytsaurus.tech/docs/en/#ysonis) function. The `Yson::AsUtf8` and `Yson::TryAsUtf8` functions are available starting from version [2026.01](https://ytsaurus.tech/changelog/2026.01.md#yson-module). See the [`Yson::IsUtf8`](https://ytsaurus.tech/docs/en/#ysonis) function description for information about when a node can be cast to this type. ## In-place Yson node editing {#yson-modify} Starting with version [2025.05](https://ytsaurus.tech/changelog/2025.05.md#yson-module), you can work with a mutable Yson tree based on [linear](https://ytsaurus.tech/types/linear.md) types. Use the [`MutCreate`](https://ytsaurus.tech/docs/en/#mutcreate) function to create a new tree and the [`Mutate`](https://ytsaurus.tech/docs/en/#mutate) function to make an existing immutable tree mutable. Trees should be created/transformed inside the [`Block`](https://ytsaurus.tech/builtins/basic.md#block) function. The resulting mutable Yson tree must ultimately be converted into an immutable one using the [`MutFreeze`](https://ytsaurus.tech/docs/en/#mutfreeze) function. Each instance of a mutable Yson tree has a single current node that you can move while simultaneously creating new nodes as needed. ## Yson::MutCreate {#mutcreate} ```yql Yson::MutCreate() -> Linear> ``` Added in version [2025.05](https://ytsaurus.tech/changelog/2025.05.md#yson-module). Creates a new empty tree with a single node in the `Invalid` state, which corresponds to an undefined type. You can change the value of this node, for example, using the [`MutUpsert`](https://ytsaurus.tech/docs/en/#mutupsert) function. In this case, the root of the resulting tree is set as the current node. Since this function creates an instance of a linear type, the dependent nodes should be specified via a [`Udf`](https://ytsaurus.tech/builtins/basic.md#udf) when using it. #### Examples ```yql SELECT Block(($arg)->{ $m = Udf(Yson::MutCreate, $arg as Depends)(); return Yson::MutFreeze($m); -- Error: Invalid or deleted node is not allowed }) ``` ```yql SELECT Block(($arg)->{ $m = Udf(Yson::MutCreate, $arg as Depends)(); $m = Yson::MutUpsert($m, '1'y); return Yson::MutFreeze($m); -- 1 }); ``` ## Yson::Mutate {#mutate} ```yql Yson::Mutate(Resource) -> Linear> ``` Added in version [2025.05](https://ytsaurus.tech/changelog/2025.05.md#yson-module). Creates a mutable Yson tree from an immutable one. In this case, the root of the resulting tree is set as the current node. Since this function creates an instance of a linear type, the dependent nodes should be specified via a [`Udf`](https://ytsaurus.tech/builtins/basic.md#udf) when using it. #### Examples ```yql SELECT Block(($arg)->{ $m = Udf(Yson::Mutate, $arg as Depends)('1'y); return Yson::MutFreeze($m); -- 1 }); ``` ## Yson::MutFreeze {#mutfreeze} ```yql Yson::MutFreeze(Linear>) -> Resource ``` Added in version [2025.05](https://ytsaurus.tech/changelog/2025.05.md#yson-module). Converts a mutable Yson tree to an immutable one. All nodes in the `Deleted` state are removed from maps and lists. The root node of the mutable tree must not be in the `Invalid` or `Deleted` state, and its child nodes must not be in the `Invalid` state. Otherwise, the conversion will fail. #### Examples ```yql SELECT Block(($arg)->{ $m = Udf(Yson::Mutate, $arg as Depends)('1'y); return Yson::MutFreeze($m); -- 1 }); ``` ```yql SELECT Block(($arg)->{ $m = Udf(Yson::MutCreate, $arg as Depends)(); -- Root node has the state Invalid return Yson::MutFreeze($m); -- Error: Invalid or deleted node is not allowed }) ``` ```yql SELECT Block(($arg)->{ $m = Udf(Yson::Mutate, $arg as Depends)('1'y); $m = Yson::MutRemove($m); -- Chanching the node state to Deleted return Yson::MutFreeze($m); -- Error: Invalid or deleted node is not allowed }) ``` ## Yson::MutUpsert {#mutupsert} ```yql Yson::MutUpsert(Linear>, Resource) -> Linear> ``` Added in version [2025.05](https://ytsaurus.tech/changelog/2025.05.md#yson-module). Replaces the current node in a mutable Yson tree with the specified Yson subtree. #### Examples ```yql SELECT Block(($arg)->{ $m = Udf(Yson::Mutate, $arg as Depends)('1'y); $m = Yson::MutUpsert($m, '2'y); return Yson::MutFreeze($m); -- 2 }); ``` ## Yson::MutInsert {#mutinsert} ```yql Yson::MutInsert(Linear>, Resource) -> Linear> ``` Added in version [2025.05](https://ytsaurus.tech/changelog/2025.05.md#yson-module). Replaces the current node in a mutable Yson tree with the specified Yson subtree if the current node is in the `Invalid` or `Deleted` state. #### Examples ```yql SELECT Block(($arg)->{ $m = Udf(Yson::MutCreate, $arg as Depends)(); $m = Yson::MutInsert($m, '1'y); $m = Yson::MutInsert($m, '2'y); -- has no effect return Yson::MutFreeze($m); -- 1 }); ``` ## Yson::MutUpdate {#mutupdate} ```yql Yson::MutUpdate(Linear>, Resource) -> Linear> ``` Added in version [2025.05](https://ytsaurus.tech/changelog/2025.05.md#yson-module). Replaces the current node in a mutable Yson tree with the specified Yson subtree if the current node isn't in the `Invalid` or `Deleted` state. #### Examples ```yql SELECT Block(($arg)->{ $m = Udf(Yson::MutCreate, $arg as Depends)(); $m = Yson::MutUpdate($m, '1'y); -- has no effect $m = Yson::MutInsert($m, '2'y); $m = Yson::MutUpdate($m, '3'y); return Yson::MutFreeze($m); -- 3 }); ``` ## Yson::MutRemove {#mutremove} ```yql Yson::MutRemove(Linear>) -> Linear> ``` Added in version [2025.05](https://ytsaurus.tech/changelog/2025.05.md#yson-module). Changes the current node to the `Deleted` state. #### Examples ```yql SELECT Block(($arg)->{ $m = Udf(Yson::Mutate, $arg as Depends)('1'y); $m = Yson::MutRemove($m); $m = Yson::MutInsert($m, '2'y); return Yson::MutFreeze($m); -- 2 }); ``` ## Yson::MutRewind {#mutrewind} ```yql Yson::MutRewind(Linear>) -> Linear> ``` Added in version [2025.05](https://ytsaurus.tech/changelog/2025.05.md#yson-module). Moves the current node to the root of the tree. #### Examples ```yql SELECT Block(($arg)->{ $m = Udf(Yson::Mutate, $arg as Depends)('{a=1;b=2}'y); $m = Yson::MutDown($m, 'a'); -- Move to key 'a' in the map $m = Yson::MutRewind($m); -- Move to the root $m = Yson::MutUpsert($m, '1'y); -- Replace the tree starting at the root return Yson::MutFreeze($m); -- 1 }); ``` ## Yson::MutUp {#mutup} ```yql Yson::MutUp(Linear>) -> Linear> ``` Added in version [2025.05](https://ytsaurus.tech/changelog/2025.05.md#yson-module). Moves the current node one level closer to the root of the tree. If the current node is already at the root of the tree, generates an error. #### Examples ```yql SELECT Block(($arg)->{ $m = Udf(Yson::Mutate, $arg as Depends)('{a=1;b=2}'y); $m = Yson::MutDown($m, 'a'); -- Move to key 'a' in the map $m = Yson::MutUp($m); -- Move to the root $m = Yson::MutUpsert($m, '1'y); -- Replace the tree starting at the root return Yson::MutFreeze($m); -- 1 }); ``` ## Yson::MutDown, Yson::MutDownOrCreate, Yson::MutTryDown {#mutdown} ```yql Yson::MutDown(Linear>,location:String) -> Linear> Yson::MutDownOrCreate(Linear>,location:String) -> Linear> Yson::MutTryDown(Linear>,location:String) -> Tuple>, Bool> ``` Added in version [2025.05](https://ytsaurus.tech/changelog/2025.05.md#yson-module). Moves the current node to an element within a map or list. This operation can create a new node in the `Invalid` state. The location string is escaped using a backslash (`\`). If the location string begins with the operator `<`, `=`, or `>`, it must be followed by a specific zero-based list index in decimal format, `first` (index 0), or `last` (last element in the list). Otherwise, location is interpreted as a map key. For an empty list, `first` and `last` correspond to positions outside the list. The `MutDown` function can't create new keys within maps. It can only apply the `=` modifier to lists and generates an error if the current node can't be moved. Unlike `MutDown`, the `MutTryDown` function returns a tuple where the second element is `false` if the current node can't be moved. If the current node is in the `Invalid` or `Deleted` state, the `MutDownOrCreate` function first creates it as an empty list or map, depending on the type of location. If the type of the current node (list or map) doesn't match location, both `MutDown` and `MutDownOrCreate` return an error. #### Examples ```yql SELECT Block(($arg)->{ $m = Udf(Yson::Mutate, $arg as Depends)('{a=1;b=2}'y); $m = Yson::MutDown($m, 'a'); -- moved to the key 'a' in a dict $m = Yson::MutUpsert($m, '3'y); -- replaced the current node return Yson::MutFreeze($m); -- {a=3;b=2} }); ``` ```yql SELECT Block(($arg)->{ $m = Udf(Yson::Mutate, $arg as Depends)('{a=1;b=2}'y); $m = Yson::MutDownOrCreate($m, 'c'); -- moved to the key 'c' in a dict - it was created in the state `Invalid` $m = Yson::MutUpsert($m, '3'y); -- replaced the current node return Yson::MutFreeze($m); -- {a=1;b=2;c=3} }); ``` ```yql SELECT Block(($arg)->{ $m = Udf(Yson::Mutate, $arg as Depends)('{a=1;b=2}'y); $m = Yson::MutDown($m, 'c'); -- removed to the key 'c' in a dict, error, it doesn`t exists return Yson::MutFreeze($m); }); ``` ```yql SELECT Block(($arg)->{ $m = Udf(Yson::Mutate, $arg as Depends)('{a=1;b=2}'y); $m, $ok = Yson::MutTryDown($m, 'c'); -- tried to move to the key 'c' in a dict, returned `false` return (Yson::MutFreeze($m), $ok); -- ({a=1;b=2}, false) }); ``` ```yql SELECT Block(($arg)->{ $m = Udf(Yson::Mutate, $arg as Depends)('[1;2]'y); $m = Yson::MutDownOrCreate($m, '>last'); -- added node to the end of a list in a state `Invalid` $m = Yson::MutUpsert($m, '3'y); -- replaced the current node return Yson::MutFreeze($m); -- [1;2;3] }); ``` ```yql SELECT Block(($arg)->{ $m = Udf(Yson::Mutate, $arg as Depends)('[1;2]'y); $m = Yson::MutDownOrCreate($m, '<1'); -- added node before the node with the index 1 (value '2') $m = Yson::MutUpsert($m, '3'y); -- replaced the current node return Yson::MutFreeze($m); -- [1;3;2] }); ``` ```yql SELECT Block(($arg)->{ $m = Udf(Yson::Mutate, $arg as Depends)('[1;2]'y); $m = Yson::MutDownOrCreate($m, '{ $m = Udf(Yson::Mutate, $arg as Depends)('[1;2]'y); $m = Yson::MutDown($m, '=last'); -- moving to the last node of the list $m = Yson::MutUpsert($m, '3'y); -- replaced the current node return Yson::MutFreeze($m); -- [1;3] }); ``` ```yql SELECT Block(($arg)->{ $m = Udf(Yson::Mutate, $arg as Depends)('[1;2]'y); $m = Yson::MutDownOrCreate($m, 'a'); -- error, current node is not a dict return Yson::MutFreeze($m); }); ``` ```yql SELECT Block(($arg)->{ $m = Udf(Yson::Mutate, $arg as Depends)('[a=1;b=2]'y); $m = Yson::MutDownOrCreate($m, '=0'); -- error, current node is not a list return Yson::MutFreeze($m); }); ``` ## Yson::MutExists {#mutexists} ```yql Yson::MutExists(Linear>) -> Tuple>,Bool> ``` Added in version [2025.05](https://ytsaurus.tech/changelog/2025.05.md#yson-module). Checks whether the current node is in the `Invalid` or `Deleted` state. This function doesn't modify the tree itself or the position of its current node. #### Examples ```yql SELECT Block(($arg)->{ $m = Udf(Yson::MutCreate, $arg as Depends)(); $m, $exists = Yson::MutExists($m); -- current node is Invalid return LinearDestroy($exists, $m); -- retorning the value $exists=false, absorbing linear type $m }); ``` ```yql SELECT Block(($arg)->{ $m = Udf(Yson::Mutate, $arg as Depends)('1'y); $m, $exists = Yson::MutExists($m); -- current node is not Invalid return LinearDestroy($exists, $m); -- returning the value $exists=true, absorbing linear type $m }); ``` ## Yson::MutView {#mutview} ```yql Yson::MutView(Linear>) -> Tuple>, Optional>> ``` Added in version [2025.05](https://ytsaurus.tech/changelog/2025.05.md#yson-module). Returns an immutable Yson tree starting from the current node unless it's in the `Invalid` or `Deleted` state. Otherwise, returns `NULL`. Similar to the `MutFreeze` function, all child nodes of the current node must not be in the `Invalid` state, and `Deleted` nodes are skipped when creating the resulting Yson tree. This function doesn't modify the tree itself or the position of its current node. #### Examples ```yql SELECT Block(($arg)->{ $m = Udf(Yson::MutCreate, $arg as Depends)(); $m, $view = Yson::MutView($m); -- Current node is in the Invalid state return LinearDestroy($view, $m); -- Return the $view=NULL value and consume the linear type $m }); ``` ```yql SELECT Block(($arg)->{ $m = Udf(Yson::Mutate, $arg as Depends)('1'y); $m, $view = Yson::MutView($m); -- Current node isn't Invalid return LinearDestroy($view, $m); -- Return the $view='1'y value and consume the linear type $m }); ``` ## See also * [Accessing values inside JSON with YQL](https://ytsaurus.tech/recipes/accessing-json.md) * [Modifying JSON with YQL](https://ytsaurus.tech/recipes/modifying-json.md) # YQL user-defined functions This section describes YQL user-defined functions for supported languages and how to develop them. - [C++ UDF overview](https://ytsaurus.tech/docs/en/cpp_about_udf.md) - [Developing C++ UDFs](https://ytsaurus.tech/docs/en/cpp.md) - [Python UDFs](https://ytsaurus.tech/docs/en/python.md) - [Preinstalled UDFs](https://ytsaurus.tech/docs/en/preinstalled.md) # Built-in UDFs The list of built-in UDFs is available at [Built-in functions / C++ libraries](https://ytsaurus.tech/docs/en/list/index.md). # Instructions for writing Python user-defined functions (UDFs) for YQL Using a purely declarative, SQL-like approach to describe task results can sometimes be impractical. You may need to specify which data to `JOIN` and how to group it declaratively, while handling the data processing logic imperatively. For these use cases, YQL provides a mechanism for writing and calling user-defined functions (UDFs) in Python. To learn more about UDFs, see [Python user-defined functions (UDFs) in YQL](https://ytsaurus.tech/user-guide/query-tracker/system-python-udf.md). # Developing UDFs in C++ ## Introduction YQL currently supports user-defined functions (UDFs) in C++ and Python. The information below covers only C++. [Python UDFs](https://ytsaurus.tech/docs/en/python.md) are covered on a separate page. YQL offers a set of basic functions, and we recommend viewing the [list](https://ytsaurus.tech/docs/en/list/index.md) of them. ## Terminology * **A callable value** is an entity with a fixed, strictly typed signature that, when called, can return a result based on its arguments. * **A user-defined function** (UDF) is a generator of callable values. * **A module** is a set of closely related functions with a common prefix. All module functions are distributed together in a single shared library (`.so`). If a module is project-specific, it's advisable to reflect this in its name by adding a prefix. * **A registrator** is an interface for adding modules and functions to the general registry. * **A module group** is a group of modules belonging to a structural unit or a large project. It serves only for logical separation of source code (see the next section) and is not visible from YQL/SQL. * **Configuration parameters** are an optional tool for setting up a user-defined function (generator of callable values). You can use configuration parameters to change the behavior of callable values without recompilation. * `Type Config`, `User Type`: They are used by the function to define and declare the signature of the callable value; used when building a computation graph. They must be known at compile time, so `Type Config` must be an atom, and `User Type` must be a certain type (for example, obtained using the TypeOf operation). * `Run Config`: Function uses it to return the callable value with the previously declared signature. The Run Config value type is specified in the UDF code when declaring the signature of the callable value. It differs from a regular argument in that it is passed at initialization, not at each call. ## Examples Examples of ready-to-use modules are available at [https://github.com/ytsaurus/ytsaurus/tree/main/yql/essentials/udfs](https://github.com/ytsaurus/ytsaurus/tree/main/yql/essentials/udfs). See the [examples](https://github.com/ytsaurus/ytsaurus/tree/main/yql/essentials/udfs/examples) subdirectory for very basic examples and other subdirectories (such as [common](https://github.com/ytsaurus/ytsaurus/tree/main/yql/essentials/udfs/common)) for more realistic ones. Before you move on to the next section, we recommend opening one or multiple examples to follow along: the section provides only an overview of the code, without complete listings. ## Interfaces (С++) ### TUnboxedValue (udf\_value.h) Wrapper class for typed data. It provides a set of constructors for creating composite data types from primitive types along with a set of helpers for working with the former. Numbers, intervals, dates (including dates with a timezone), and short strings up to 14 bytes are stored by value; the `IsEmbedded()` method returns `true` for them. Strings larger than 14 bytes are stored by pointer. ### TUnboxedValuePod(udf\_value.h) It is a base class for TUnboxedValue. It does not automatically count references to its value, unlike its subclass, which does it in the constructor and destructor. The key point here is that the TUnboxedValuePod class does not have constructors and destructors. It implements the entire logic of working with typed data. ### IValueBuilder (udf\_value\_builder.h) The Run method must return TUnboxedValue. For numeric types, you can use the type constructor, for example, `return TUnboxedValue(123);`. To create TUnboxedValue with more complex content, you can pass IValueBuilder to the Run method. It can be used to call one of the `New***` methods to get the `I***ValueBuilder` interface, where *** is a String, Struct, Dict, or another type. Use this to add type-specific elements and then call `Build` to get the filled TUnboxedValue. You can also create a Struct or Tuple with preallocation to store the contents. To do this, call the corresponding method `New***(const TType* type, TUnboxedValue*& items)` and fill the items. See [yql/essentials/udfs/examples/structs](https://github.com/ytsaurus/ytsaurus/tree/main/yql/essentials/udfs/examples/structs) for an example with structs. ### TBoxedValue (udf\_value.h) A typical UDF class inherits from TBoxedValue and, in addition to the constructor, overrides the Run method where the main logic for handling one particular call should be placed. The IValueBuilder interface (more on it below below) and the passed arguments in the form of the TUnboxedValue array are passed to the Run arguments. In the constructor, you can store a state in private class variables. Thus, a specific object of our TBoxedValue subclass becomes a callable value with potentially unique behavior. For simple UDFs (without using YQL structures, C++ templates, inheritance, and so on), you can use the `SIMPLE_UDF(udfName, signature)` macro (you can find it in `udf_helpers.h`). `signature` is specified as `returnType(arg1, arg2, ..., argN)`. Example: ```cpp SIMPLE_UDF(TAbs, ui64(i64)) { i64 input = args[0].Get(); ui64 result = static_cast(input >= 0 ? input : -input); return TUnboxedValue(result); } ``` The function will be registered as `Abs`, the argument type is `i64`, and the result is `ui64`. An example where a successful result is not guaranteed: ```cpp SIMPLE_UDF(TParseDuration, TOptional(char*)) { TDuration result; auto input = args[0].AsStringRef(); bool success = TDuration::TryParse(input, result); return success ? TUnboxedValue::Optional(result.Seconds()) : TUnboxedValue(); } ``` `Optional (type)` ([nullable type](https://ytsaurus.tech/docs/types/optional.md)) means that a value of the specified type may be missing (for example, if there is an error). In SQL, you can use the `IS [NOT] NULL` expression to check for this type in the result. ### IUdfModule (udf\_registrator.h) For common modules, you can use the `SIMPLE_MODULE(moduleName, udfs...)` macro (you can find it in `udf_helpers.h`): * The first argument is the name of the module class; when registering, the `T` prefix and the `Module` suffix will be removed. * Next is a list of N constituent UDFs; when registering them, only the `T` prefix will be removed. In the example `SIMPLE_MODULE(TFooModule, TBar, TBaz)`, the `Foo` module with two UDFs (`Bar` and `Baz`) will be available from YQL after registration (more on registration below). #### Manual module implementation A typical module class inherits from IUdfModule and must implement two methods: * `GetAllFunctionNames`: In the passed `IFunctionNamesSink`, you need to call the `Add(name)` method for each UDF included in the module. * `BuildFunctionTypeInfo`: Using the passed UDF name and Type Config, you need to call on the passed `IFunctionTypeInfoBuilder`: * `Args()->Add()[->Add(foo)->Add...].Done()`: * To specify arguments and their types, for numbers and strings, you can specify the argument type using a template. * You can describe a composite type, such as a Struct or Dict, using the same-name `IFunctionTypeInfoBuilder` method, then passing it to the `Add` argument. For example: ```cpp ui32 a = 0; ui32 b = 0; auto foo = builder.Struct()->AddField("A", &a).AddField("B", &b).Build(); builder.Args()->Add(foo); ``` * When describing types like `builder.List()->...`, you should copy examples of others as carefully as possible: calling a chain of builder methods in a wrong sequence or with wrong arguments often compiles fine, but crashes at runtime with a non-trivial backtrace. Example of such a chain: ```cpp // udf returns list of lists of char* auto retType = buider.List()->Item(builder.List()->Item().Build()).Build(); // Correct auto retType = buider.List()->Item().Build(); // Wrong type, coredump with backtrace in casting to string deep in validating auto retType = buider.List()->Build(); // No type at all, coredump with attempt to dereference nullptr deep in type machinery builder.Returns(retType); ``` * The arguments also pass the `ui32 flags` bitmask, which at the time of writing can contain only `(cpp)TFlags::TypesOnly` that, if present, does not require calling `builder.Implementation(new TMyUdf);` for the UDF corresponding to the passed name. ### IRegistrator (udf\_registrator.h) For simplified module registration in the system, we recommend using a macro: ```cpp REGISTER_MODULES( TFooModule, TBarModule, TBarModule ) ``` In this case, the module name is taken from the `Name()` method that must return `TStringRef` by value; the `SIMPLE_MODULE` macro creates it automatically. #### Alternative method for advanced users You need to declare the `Register` function of the following form, then register the module(s) in the passed interface and export the ABI version: ```cpp extern "C" YQL_UDF_API void Register(NKikimr::NUdf::IRegistrator& registrator, ui32 flags) { registrator->AddModule("Foo", new NDetail::TFooModule(flags)); } extern "C" YQL_UDF_API ui32 AbiVersion() { return CurrentAbiVersion(); } ``` You can get the list of flags with `IUdfModule::TFlags`. At the time of writing, only `TypesOnly` is implemented. It is used as an indication that no real computations will be performed yet, because the computation graph is still being built, and only function signatures are required at this stage. ### Exception and error handling To return fatal exceptions to the user, use the following block: ```cpp try { ... } catch (const std::exception& e) { UdfTerminate(e.what()); } ``` Instead of `e.what()`, you can write a human-readable error description if possible. {% note warning "Attention" %} We strongly recommend against completely terminating program execution using `abort`, `exit`, `Y_ABORT_UNLESS`, `Y_ABORT`, and other similar mechanisms from UDF code, because this would limit its scope to environments where a separate process is created for each calculation. The `UdfTerminate` implementation is aware of the current environment and will invoke the error handling mechanism it expects. {% endnote %} ### Overview of UDFs in C++ In C++ code, a UDF is simply a `TBoxedValue` subclass with the mandatory `Run()` method. An easy way to implement a particular UDF inside a UDF module is to use a class with three overridden functions (`Name()`, `DeclareSignature(...)`, and `Run(...)`) to be called within the module as needed. Example of a UDF module class: ```cpp class TSomeYQLModule: public IUdfModule { public: TStringRef Name() const { return TStringRef::Of("SomeModule"); } void GetAllFunctions(IFunctionsSink& sink) const final { sink.Add(TSomeUdfFunction::Name()); sink.Add(TSomeTypeAwareUdfFunction::Name())->SetTypeAwareness(); } void BuildFunctionTypeInfo(const TStringRef& name, TType* userType, const TStringRef& typeConfig, ui32 flags, IFunctionTypeInfoBuilder& builder) const override { try { Y_UNUSED(typeConfig); bool typesOnly = (flags & TFlags::TypesOnly); if (TSomeUdfFunction::Name() == name) { TSomeUdfFunction::DeclareSignature(typesOnly, builder); } else if (TSomeTypeAwareUdfFunction::Name() == name) { TSomeTypeAwareUdfFunction::DeclareSignature( typesOnly, builder, userType); } } catch (const std::exception& e) { builder.SetError(CurrentExceptionMessage()); } } }; ``` Method description: * `static const TStringRef& Name()` A method that returns the function name to be used in YQL. In this implementation, the method is used to add a function to a UDF module. * `static bool DeclareSignature(bool typesOnly, IFunctionTypeInfoBuilder& builder [, TType* userType])` A method that declares the function signature. It is used in the ```BuildFunctionTypeInfo``` method of a UDF module to get information about the indexes of structure fields used inside the class and to create an instance of a particular UDF. * `TUnboxedValue Run(const IValueBuilder* valueBuilder, const TUnboxedValuePod* args) const override` A method that implements all the internal logic of a UDF. When the UDF is called from YQL, it is this particular method that is called. Example of a UDF class: ```cpp class TMyUdfFunction : public ::NKikimr::NUdf::TBoxedValue { public: // Each member struct is a C++ description of a YQL struct and has it's own // constructor that takes in a builder instance and initializes a proper // struct from it. If you want to build your own YQL struct and work with // it in C++, you will need to write the same kind of constructor. These // structs contain indices from the YQL runtime (in C++ you can't use field // names, only indices) and TType* ResultStructType (YQL description of the // struct), which is a description of the struct understandable for YQL and // will be used later when specifying the return type of the UDF. struct TMemberIndices { TMemberIndices(::NKikimr::NUdf::IFunctionTypeInfoBuilder& builder) { ... } }; // Name() function, needed to register UDF in YQL, the function in YQL will // have the name we specify here. In our UDF Module class we have a // GetAllFunctions method, where we add all function names into our module. static const ::NKikimr::NUdf::TStringRef& Name() { static auto name = ::NKikimr::NUdf::TStringRef::Of("MyUdfFunction"); return name; } // This function is also used in the UDF module class in the // BuildFunctionTypeInfo method. Here we use the passed in builder instance // 1) to take information about indices we need for our class to work // 2) to create an instance of this UDF function // This is the only function where we can grab info about indices for our // class constructor, thus this is the only place where constructor is // called static bool DeclareSignature( bool typesOnly, ::NKikimr::NUdf::IFunctionTypeInfoBuilder& builder, ::NKikimr::NUdf::TType* userType) { auto members = buildReturnSignature(builder, userType); // If typesOnly flag is specified, we don't need to register // implementation, just build the signature if (!typesOnly) { builder.Implementation(new TMyUdfFunction(members)); } return true; } // Run() function is derived from TBoxedValue (and we need to override it) // and contains our UDF logic. (When we call it from YQL, this function is // what is gonna happen). // It always has signature // Run(IValueBuilder*, TUnboxedValuePod*) -> TUnboxedValue ::NKikimr::NUdf::TUnboxedValue Run( const ::NKikimr::NUdf::IValueBuilder* valueBuilder, const ::NKikimr::NUdf::TUnboxedValuePod* args) const override { ... } private: TMemberIndices IndicesDescription; /// Constructor accepts YQL struct member descriptions and source code /// position, for logging. /// Instance of this class is created within DeclareSignature method, /// as that is the only method that knows YQL Struct member indices /// For the reasons above, it is made private TMyUdfFunction(const TMemberIndices& indicesDescription, const ::NKikimr::NUdf::TSourcePosition& pos) { ... } /// This function will initialize proper signature on given builder. /// It will also return a struct with indices of YQL Struct members (in C++ /// you have to access YQL Struct members by index, not by name) static TMemberIndices buildReturnSignature( ::NKikimr::NUdf::IFunctionTypeInfoBuilder& builder, ::NKikimr::NUdf::TType* userType) { ... } }; ``` ### Type-aware UDFs Type awareness is implemented using the `TType* userType` argument of the `DeclareSignature(...)` and `buildReturnSignature(...)` methods. You can use this argument to see with which arguments a UDF function was called from YQL and build the desired signature. A user-defined type can be parsed using the `ITypeInfoHelper::TPtr` object, which is returned by the `builder.TypeInfoHelper()` method and which can be used to create TypeInspectors, such as `TTupleTypeInspector`, `TCallableTypeInspector`, `TOptionalTypeInspector`, and others. {% note warning %} For the YQL engine to pass a non-empty userType to `DeclareSignature()` as input, the class of your UDF must contain a subtype definition: ``` typedef bool TTypeAwareMarker; ``` {% endnote %} Using such functions, you can: * Support optional or default arguments: `SomeTypeAwareUdfFunc(Double, Double) -> Bool` `SomeTypeAwareUdfFunc(Double, Double, Double) -> Bool` ```cpp TMemberIndices TSomeTypeAwareUdfFunc::buildReturnSignature(IFunctionTypeInfoBuilder& builder, TType* userType) { ... auto userTypeInspector = TTupleTypeInspector(*typeHelper, userType); auto argsTypeTuple = userTypeInspector.GetElementType(0); auto argsTypeInspector = TTupleTypeInspector(*typeHelper, argsTypeTuple); // Build right signature by number of arguments if (argsTypeInspector.GetElementsCount() == 2) { /* Build one signature */ ... } else if (argsTypeInspector.GetElementsCount() == 3) { /* Build other signature */ ... } else { ... } ... } TUnboxedValue TSomeTypeAwareUdfFunc::Run(IValueBuilder* valueBuilder, TUnboxedValuePod* args) { ... double value = 0.0; // default if (/* hasThirdElement */) { value = args[2].Get(); // non-default } ... } ``` * Support overloading: `SomeTypeAwareUdfFunc(List) -> String` `SomeTypeAwareUdfFunc(Stream) -> String` ```cpp TMemberIndices buildReturnSignature(IFunctionTypeInfoBuilder& builder, TType* userType) { ... auto userTypeInspector = TTupleTypeInspector(*typeHelper, userType); auto argsTypeTuple = userTypeInspector.GetElementType(0); auto argsTypeInspector = TTupleTypeInspector(*typeHelper, argsTypeTuple); auto typeKind = typeHelper->GetTypeKind(argsTypeInspector.GetElementType(0)); if (typeKind == ETypeKind::List) { ... } else if (typeKind == ETypeKind::Stream) { ... } else { ... } ... } ``` * Support changing the response type depending on the type returned by the custom callback: `SomeTypeAwareUdfFunc(Callable<...>->Int)->List` `SomeTypeAwareUdfFunc(Callable<...>->Double)->List` `SomeTypeAwareUdfFunc(Callable<...>->CallbackReturnType)->List` ```cpp TMemberIndices buildReturnSignature(IFunctionTypeInfoBuilder& builder, TType* userType) { ... auto userTypeInspector = TTupleTypeInspector(*typeHelper, userType); auto argsTypeTuple = userTypeInspector.GetElementType(0); auto argsTypeInspector = TTupleTypeInspector(*typeHelper, argsTypeTuple); auto callbackInspector = TCallableTypeInspector(*typeHelper, argsTypeInspector.GetElementType(0)); auto resultType = builder.List()->Item(callbackInspector.GetReturnType()).Build(); builder.Returns(resultType).Args()->Add(argsTypeInspector.GetElementType(0)).Done(); ... } ``` ### Working with YQL-Stream in a C++ UDF YQL-Stream is a single-pass iterator. In YQL, it can be obtained at the ```PROCESS``` or ```REDUCE``` output (see the YQL documentation). In C++, YQL-Stream is a ```TBoxedValue``` subclass with the overridden ```Fetch()``` method. ```cpp class TMyStreamProvider: public TBoxedValue { public: TMyStreamProvider(...) { ... } TMyStreamProvider() = delete; TMyStreamProvider(const TMyStreamProvider&) = delete; virtual ~TMyStreamProvider() = default; void operator=(const TMyStreamProvider&) = delete; TMyStreamProvider(TMyStreamProvider&& other) = default; TMyStreamProvider& operator=(TMyStreamProvider&& other) = default; EFetchStatus Fetch(TUnboxedValue& result) override; }; ``` ```Fetch()``` can return three statuses: * ```EFetchStatus::Ok``` Indicates successful writing of a value to the result. * ```EFetchStatus::Finish``` Indicates the end of the stream, the result remains unchanged. * ```EFetchStatus::Yield``` Special status. If a UDF has no YQL-Stream at the input, your stream will never generate this status. If the ```EFetchStatus::Yield``` status appears when reading the input stream, you can write 0 or more values to the output stream but the ```EFetchStatus::Yield``` status should still be generated and returned. Remember that after the UDF returns a stream in C++ code, whatever was in the ```Run()``` body is destroyed by the destructor. So you need to pass everything through the constructor to the Stream class to make it the owner of all resources. ### Data processing in UDFs To properly use composite data types, you need to process and store information about their structure. This is done at the UDF signature generation stage. Example of a class that contains this information ```cpp struct TInputIndices { // These two fields will contain indexes of fields in the Struct type. Using these indexes, we will be able to obtain the values of the structure fields of a specific object of that structure. ui32 oneInputFieldIndex = 0; ui32 anotherInputFieldIndex = 0; static constexpr const int FieldsCount = 2; NYql::NUdf::TType* ResultStructType = nullptr; NYql::NUdf::TType* StructType() const { return ResultStructType; } TInputIndices(NYql::NUdf::IFunctionTypeInfoBuilder& builder) { auto optionalAnotherInputFieldType = builder.Optional()->Item(TDataType::Id).Build(); // After calling Build, oneInputFieldIndex and anotherInputFieldIndex will contain the index of the corresponding structure fields. ResultStructType = builder.Struct(FieldsCount) -> AddField("oneInputFieldName", &oneInputFieldIndex) . AddField("anotherInputFieldName", optionalAnotherInputFieldType, &anotherInputFieldIndex) . Build(); } TSomeStruct UnpackUnboxedValue(const NYql::NUdf::TUnboxedValue& item) const { TSomeStruct someStruct; // We can get the value of a particular field using the index. auto oneInputFieldIValue = item.GetElement(oneInputFieldIndex); auto anotherInputFieldIValue = item.GetElement(anotherInputFieldIndex); ... return someStruct; } }; ``` You will need `IFunctionTypeInfoBuilder` to generate the input data type. This interface supports generation of composite types, such as structures, lists, or maps. Suppose that we pass a structure containing two fields (`"oneInputField"` of the `Double` type and `"anotherInputField"` of the `TDatetime?` type) as input. Using `IFunctionTypeInfoBuilder`, we create an optional type for the second field and a structure type filled with the required fields. The structure type is declared using the builder by calling the `Struct(num_of_fields)` method. Internally, the structure in a YQL UDF is a regular array of field values. You can add fields using the `AddField` method of the returned interface for working with structures. After executing the Build() method, the `oneInputFieldIndex` and `anotherInputFieldIndex` variables will contain the indexes by which the data of a particular field will be stored in the input data array. You can use them to retrieve the value of a particular field from the input data array as it was done in the `UnpackUnboxedValue` method in the example above. Then you can use this class in the `Run` method of the UDF to unpack input data using the `UnpackUnboxedValue` method. To pack the results of your UDF, you can use classes for storing output data details. For example: ```cpp struct TOutputIndices { ui32 firstOutputFieldIndex = 0; ui32 secondOutputFieldIndex = 0; ui32 thirdOutputFieldIndex = 0; static constexpr const int FieldsCount = 3; NYql::NUdf::TType* ResultStructType = nullptr; NYql::NUdf::TType* StructType() const { return ResultStructType; } TOutputIndices(NYql::NUdf::IFunctionTypeInfoBuilder& builder) { auto thirdOutputFieldType = builder.List()->Item(TDataType::Id).Build() ResultStructType = builder.Struct(FieldsCount) -> AddField("firstOutputFieldName", &firstOutputFieldIndex) . AddField("secondOutputFieldName", &secondOutputFieldIndex) . AddField("thirdOutputFieldName", thirdOutputFieldType, &thirdOutputFieldIndex); . Build(); } NYql::NUdf::TUnboxedValue PackUnboxedValue(const NYql::NUdf::IValueBuilder* valueBuilder, ui32 valueForFirstField, const TString& valueForSecondField, std::vector valuesForThirdField) { // Pay attention to what happens here. There are TWO output parameters: result is what you need to return, while elemItems contains a pointer to the array where you need to enter the field values. TUnboxedValue* elemItems = nullptr; auto result = valueBuilder->NewArray(FieldsCount, elemItems); // Entering field values elemItems[firstOutputFieldIndex] = TUnboxedValuePod(valueForFirstField); elemItems[secondOutputFieldIndex] = valueBuilder->NewString(valueForSecondField); std::vector thirdFieldItems; thirdFieldItems.reserve(valuesForThirdField.size()); for (const auto& value : valuesForThirdField) { TUnboxedValue yqlValue = TUnboxedValuePod(value); if (yqlValue) { thirdFieldItems.push_back(std::move(yqlValue)); } } auto thirdFieldValueHandler = valueBuilder->NewList(thirdFieldItems.data(), thirdFieldItems.size()); elemItems[thirdOutputFieldIndex] = thirdFieldValueHandler; return result; } }; ``` Generating the output data structure type is no different from generating the input data structure type: you need to declare the structure fields and save their indexes to then fill the output data array. In the example above, the structure consists of three fields: the first field is a primitive numeric type, the second one is a string, and the third one is a list. To create a list type, use the `List()` method of the `NYql::NUdf::IFunctionTypeInfoBuilder` interface. This method returns a pointer to the interface that you can use to create a list type. Specify the type of list items using the `Item(type)` method. Thus, in addition to lists, you can create maps, structures, tuples, and more. The output data array is generated using `NYql::NUdf::IValueBuilder` and its `NewArray` method, which accepts a set of output elements and a reference to the pointer. The method returns TUnboxedValue describing the entire object and adds a value to the passed pointer, which in turn helps fill the array. This method returns `NYql::NUdf::TUnboxedValue` that should be returned after filling the output array with the necessary values. Keep in mind that, in order to fill the output array, you should use the pointer that the method fills, not the one it returns. When filling the array with a specific value for a specific field, use the indexes associated with that field at the stage of generating the output data type. If the returned value is a primitive numeric type, you can simply wrap it in `TUnboxedValuePod` and place it according to the appropriate index. If the returned value is a string, use the `NewString` method of the `NYql::NUdf::IValueBuilder` interface where you need to pass the string to. If the returned value is, for example, a list, create a `TUnboxedValue` value array, fill it, and then pass its size and pointer to the `NewList` method of the `NYql::NUdf::IValueBuilder` interface. This method returns `TUnboxedValue`, which you can then place in the output array according to the index that corresponds to that field. # YQL recipes This section contains practical examples of data transformation and metaprogramming in YQL. - [Accessing JSON](https://ytsaurus.tech/docs/en/accessing-json.md) - [Modifying JSON](https://ytsaurus.tech/docs/en/modifying-json.md) - [Metaprogramming](https://ytsaurus.tech/docs/en/metaprogramming.md) - [Code generation](https://ytsaurus.tech/docs/en/codegen.md) - [User-defined aggregate functions](https://ytsaurus.tech/docs/en/udaf.md) # Custom aggregate functions in YQL ## Introduction If the [built-in aggregate functions](https://ytsaurus.tech/docs/builtins/aggregation.md) do not meet your needs for whatever reason, you can define your own function in YQL. This mechanism is referred to as `UDAF` (User Defined Aggregation Functions) and works the same as standard aggregate functions. Here are the peculiarities/benefits of this approach compared to Map/Reduce ([PROCESS](https://ytsaurus.tech/docs/syntax/process.md) + [REDUCE](https://ytsaurus.tech/docs/syntax/reduce.md) or working with YTsaurus directly without YQL): 1. You can easily apply the same logic to several fields or expressions within a single computing run (that is, we calculate different non-related aggregates, reading the data only once). Moreover, you can automatically skip Null values. 2. Support for `DISTINCT`, that is, calling a function only by unique values. 3. Automatic creation of a map-side combiner. Often this allows you to substantially save on the query execution time without having to implement a map-side combiner yourself. {% note info %} If you have built or want to build an aggregate function which, in your opinion, could be useful to many users, write to us in the [Telegram chat](https://t.me/ytsaurus). We'll consider adding it to standard functions. {% endnote %} ## How it works UDAFs operate with the following abstractions: * `Item`: Aggregated value. * `State`: Aggregation state. * `Result`: Aggregation result. * `Serialized` (optional): A serialized representation of State. * `Parent` (optional): A node that should be used as a parent node if the handler is written in lambda. This is needed when the state includes mutable resources. In each specific case, there should be an arbitrary [data type](https://ytsaurus.tech/docs/types/primitive.md) behind each of these abstractions. However, the type corresponding to each abstraction should be the same for different functions. If one function uses `String` as `Item` and another function uses `Int64` as Item, you will get a type mismatch error. UDAF calling procedure in high-level terms: 1. To perform each aggregation step, the user provides from three to seven simple functions (Callable values, to be precise) that operate with the above abstractions. The main methods to get them is by using [lambda](https://ytsaurus.tech/docs/syntax/expressions.md#lambda) or [Python UDF](https://ytsaurus.tech/docs/udf/python.md). Main options: * 3: Minimal. * 4: If State isn't a result. * 6: If State is non-serializable. * 7: If you need to override the default `NULL` value. 2. The user calls a special aggregate `UDAF` function. To this function, as the first argument, the user passes a column or an aggregated expression. Other arguments include callable values. The order of callable values is described in the table below. 3. Benefits. List of callable values. Required values are highlighted in bold. | No. | Name | Description | Signature | | --- | --- | --- | --- | | 1 | **Create** | Initializing State by the first non-Null Item. | `(Item)->State`. Input arguments for lambda (Item, Parent) | | 2 | **Add** | Adding the next Item to State. | `(State,Item)->State`. Input arguments for lambda (State, Item, Parent) | | 3 | **Merge** | Merging of two States. | `(State,State)->State` | | 4 | Get Result | Getting the aggregation result from the final State.
If omitted, the final State is used as a result. | `(State)->Result` | | 5 | Serialize | Serialization of State, if it isn't serializable by itself.
Any combination of basic types and containers is serializable. That's why this case applies only when you use a Resource as a State. | `(State)->Serialized` | | 6 | Deserialize | Recovering a State after serialization. | `(Serialized)->State` | | 7 | Default Value | The result in the situation when the aggregate function hasn't been called (zero rows were input) The default value is `NULL`. | `()->Result` or `(TypeOf(Result))->Result` | They are passed one-by-one as individual arguments after the aggregated value. The aggregate `UDAF` function can have between 4 and 8 arguments. If several values in the query are aggregated in the same manner using `UDAF`, then, to avoid enumerating the callable values every time, use the [aggregate function factory](https://ytsaurus.tech/docs/builtins/basic.md#aggregationfactory). In this case, "UDAF" is passed instead of a data column, and the call is executed using [AGGREGATE_BY](https://ytsaurus.tech/docs/builtins/aggregation.md#aggregateby). ### Examples ```yql -- Emulate the aggregate COUNT function $create = ($item, $parent) -> { return 1ul }; $add = ($state, $item, $parent) -> { return 1ul + $state }; $merge = ($state1, $state2) -> { return $state1 + $state2 }; $get_result = ($state) -> { return $state }; $serialize = ($state) -> { return $state }; $deserialize = ($state) -> { return $state }; $default = 0ul; $udaf_factory = AggregationFactory( "UDAF", $create, $add, $merge, $get_result, $serialize, $deserialize, $default ); SELECT AGGREGATE_BY(value1, $udaf_factory) AS cnt1, AGGREGATE_BY(value2, $udaf_factory) AS cnt2 -- Count non-NULL values -- in the value1 and value2 columns. FROM my_table; ``` # Recipes for code generation ## Introduction When working with tables of many columns, you might spend too much effort listing them repeatedly in different parts of your SQL query. This happens when you can't select all your columns by `*` and, optionally, by `WITHOUT`. Sometimes, column selection may depend both on the column name and its data type. To avoid writing lengthy SQL queries manually, you can use [type introspection](https://ytsaurus.tech/docs/builtins/types.md) and [code generation](https://ytsaurus.tech/docs/builtins/codegen.md). Code will be generated before the main query is executed, and the resulting programs are fully equivalent to those written manually. When you use code generation, keep in mind that as it is executed before the main process starts, so you can only use column types when generating code, but not column values. You can also use constants and expressions that do not depend on table columns, without any limitation. Now let's discuss typical use cases and how to implement them. ## Use cases ### Declaring a UDF type by computations ```yql $tuple = AsTuple(1, "foo", AsList(-1.2, 3.0)); $tupleType = TypeOf($tuple); $reverseTupleType = EvaluateType(TupleTypeHandle( ListReverse(TupleTypeComponents(TypeHandle($tupleType))))); $script = @@ def reverse_any_tuple(t): return tuple(reversed(list(t))) @@; $udfType = CallableType(0, $reverseTupleType, $tupleType); $udf = SystemPython3_8::reverse_any_tuple($udfType, $script); SELECT $udf($tuple); ``` Links to documentation: * [EvaluateType](https://ytsaurus.tech/docs/builtins/types.md#evaluatetype) ### Filtering columns by a list of names ```yql USE ; $makeFieldsSelector = ($fields) -> { RETURN EvaluateCode(LambdaCode(($row) -> { $items = ListMap($fields, ($f) -> { $atom = AtomCode($f); RETURN ListCode($atom, FuncCode("Member", $row, $atom)); }); RETURN FuncCode("AsStruct", $items); })) }; $selectFields1 = $makeFieldsSelector(AsList("age", "name", "region")); $selectFields2 = $makeFieldsSelector(AsList("age", "name")); INSERT INTO @tmp1 SELECT * FROM ( SELECT $selectFields1(TableRow()) FROM `home/yql/tutorial/users` WHERE region != 225 ) FLATTEN COLUMNS; INSERT INTO @tmp2 SELECT * FROM ( SELECT $selectFields2(TableRow()) FROM `home/yql/tutorial/users` WHERE region = 225 ) FLATTEN COLUMNS; COMMIT; SELECT * FROM @tmp1; SELECT * FROM @tmp2; ``` Links to documentation: * [EvaluateCode](https://ytsaurus.tech/docs/builtins/codegen.md#evaluatecode) * [ListMap](https://ytsaurus.tech/docs/builtins/list.md#listmap) * [Lambda functions](https://ytsaurus.tech/docs/syntax/expressions.md#lambda) * [TableRow](https://ytsaurus.tech/docs/builtins/basic.md#tablerow) ### Searching in all string columns ```yql USE ; $containsAnyColumns = ($strValue, $needle) -> { -- Lambda function, a predicate that will be applied to the value of each column $func = ($where) -> { RETURN String::Contains($where, $needle); }; $code = EvaluateCode(LambdaCode(($strCode) -> { -- Select a list of struct fields $members = StructTypeComponents(TypeHandle(TypeOf($strValue))); $filteredMembers = ListFilter($members, ($x) -> { $type = $x.Type; -- Remove the optional top-level type $cleanType = If (TypeKind($type) == "Optional", OptionalItemType($type), $type); -- Leave only String and String? columns RETURN TypeKind($cleanType) == "Data" AND DataTypeComponents($cleanType)[0] == "String"; }); -- Generate an expression in the format $func(column1) OR $func(column2)... RETURN Yql::Fold($filteredMembers, ReprCode(false), ($item, $state) -> { $member = FuncCode("Member", $strCode, AtomCode($item.Name)); $apply = FuncCode("Apply", QuoteCode($func), $member); RETURN FuncCode("Or", $state, $apply); }); })); RETURN $code($strValue); }; SELECT * FROM `home/yql/tutorial/users` WHERE $containsAnyColumns(TableRow(), "comment"); ``` Links to documentation: * [EvaluateCode](https://ytsaurus.tech/docs/builtins/codegen.md#evaluatecode) * [ListFilter](https://ytsaurus.tech/docs/builtins/list.md#listmap) * [Lambda functions](https://ytsaurus.tech/docs/syntax/expressions.md#lambda) * [TableRow](https://ytsaurus.tech/docs/builtins/basic.md#tablerow) ### Merging structures left-to-right ```yql -- Remove the high-level Optional from the data type $removeOptional = ($type) -> { RETURN IF(TypeKind($type) == "Optional", OptionalItemType($type), $type); }; $combineMembersLeft = ($str1, $str2) -> { $code = EvaluateCode(LambdaCode(($str1code, $str2code) -> { -- Get lists of fields from both structures $leftMembers = StructTypeComponents($removeOptional(TypeHandle(TypeOf($str1)))); $rightMembers = StructTypeComponents($removeOptional(TypeHandle(TypeOf($str2)))); -- Generate field retrieval from the left structure $members1 = ListMap($leftMembers, ($x) -> { $atom = AtomCode($x.Name); RETURN ListCode($atom, FuncCode("Member", $str1code, $atom)); }); -- Collect the names of fields in the left structure into a set to facilitate search $leftNames = ToSet(ListExtract($leftMembers, "Name")); -- Generate retrieval of fields from the right structure unless they are already in the left structure $members2 = ListFlatMap($rightMembers, ($x) -> { $atom = AtomCode($x.Name); $member = ListCode($atom, FuncCode("Member", $str2code, $atom)); RETURN IF(DictContains($leftNames, $x.Name), NULL, $member); }); -- Generate a final structure RETURN FuncCode("AsStruct", ListExtend($members1, $members2)); })); RETURN $code($str1, $str2); }; SELECT $combineMembersLeft( Just(AsStruct(1 as a, 2 as b)), AsStruct(3 as c, 4 as b) ); -- (a:1, b:2, c:3) ``` Links to documentation: * [EvaluateCode](https://ytsaurus.tech/docs/builtins/codegen.md#evaluatecode) * [ListMap](https://ytsaurus.tech/docs/builtins/list.md#listmap) * [Lambda functions](https://ytsaurus.tech/docs/syntax/expressions.md#lambda) ### Reading a column by the name written to another column ```yql $input = AsList( AsStruct(1 as key, "value1" as index, "foo" as value1), AsStruct(2 as key, "value2" as index, "bar" as value2), AsStruct(3 as key, "value3" as index) ); -- Build a Lambda function that returns, for the input table row, two columns - a key copied unchanged and a value -- retrieved from a column whose name is written to the index column. $makeDynamicAccessor = ($keyColumn, $indexColumn, $valueColumn) -> { RETURN ($row) -> { RETURN EvaluateCode(LambdaCode(($rowCode) -> { -- Build a list of all fields except the key field and index field $names = ListExtract(StructTypeComponents(TypeHandle(TypeOf($row))), "Name"); $names = ListFilter($names, ($x) -> { return $x != $keyColumn and $x != $indexColumn }); -- Build a dictionary from the column name contained in variant $dict = EvaluateCode(FuncCode("AsDict", ListMap($names, ($x) -> { RETURN ListCode(ReprCode($x), FuncCode("Variant", FuncCode("Void"), AtomCode($x), FuncCode("VariantType", FuncCode("StructType", ListMap($names, ($x) -> { RETURN ListCode(AtomCode($x), FuncCode("VoidType")) }))))) }))); -- Index column value $indexValue = FuncCode("Member", $rowCode, AtomCode($indexColumn)); -- Take the column value unchanged $keyMember = ListCode(AtomCode($keyColumn), FuncCode("Member", $rowCode, AtomCode($keyColumn))); -- Search in the dictionary $enumLookup = FuncCode("Lookup", ReprCode($dict), $indexValue); -- Process the value found in the dictionary $value = FuncCode("FlatMap", $enumLookup, LambdaCode(($x) -> { -- Build a list of handlers for each column name $handlers = ListFlatMap($names, ($name) -> { RETURN AsList(AtomCode($name), LambdaCode(($u) -> { RETURN FuncCode("Member", $rowCode, AtomCode($name)); })); }); -- Apply a visitor to the found value RETURN FuncCode("Visit", $x, $handlers); })); $valueMember = ListCode(AtomCode($valueColumn), $value); -- Build an output structure of the key and value RETURN FuncCode("AsStruct", AsList($keyMember, $valueMember)); }))($row); }; }; SELECT * FROM ( SELECT $makeDynamicAccessor("key","index","value")(TableRow()) FROM AS_TABLE($input) ) FLATTEN COLUMNS ``` Links to documentation: * [EvaluateCode](https://ytsaurus.tech/docs/builtins/codegen.md#evaluatecode) * [ListMap](https://ytsaurus.tech/docs/builtins/list.md#listmap) * [Lambda functions](https://ytsaurus.tech/docs/syntax/expressions.md#lambda) ### Calculate a sum across tuple items ```yql $t = AsTuple(1,2,3); $sumTuple = ($tupleValue) -> { $code = EvaluateCode(LambdaCode(($tupleCode) -> { -- Count the tuple size $count = ListLength(TupleTypeComponents(TypeHandle(TypeOf($tupleValue)))); -- Generate the sum $pair = Yql::Fold(ListFromRange(0ul, $count), AsTuple(ReprCode(0), 0), ($item, $state) -> { -- $state is a pair of the sum and current index taken from the code RETURN AsTuple( FuncCode("+", $state.0, FuncCode("Nth", $tupleCode, AtomCode( CAST($state.1 as string)))), $state.1 + 1 ) }); RETURN $pair.0; })); RETURN $code($tupleValue); }; SELECT $sumTuple($t); ``` Links to documentation: * [EvaluateCode](https://ytsaurus.tech/docs/builtins/codegen.md#evaluatecode) * [Lambda functions](https://ytsaurus.tech/docs/syntax/expressions.md#lambda) ### Aggregating values of all numeric columns Select all the numeric columns in a table, apply several aggregate functions to them, and write the values to output columns, adding the name of the aggregate function to the output column names. ```yql USE ; -- Remove the top-level Optional from the data type $removeOptional = ($type) -> { RETURN IF(TypeKind($type) == "Optional", OptionalItemType($type), $type); }; -- Filter only numeric columns and put them in the nums struct $filterOnlyNumericColumns = ($strValue) -> { $code = EvaluateCode(LambdaCode(($str) -> { $members = StructTypeComponents(TypeHandle(TypeOf($strValue))); $filteredMembers = ListFilter($members, ($x) -> { $type = $x.Type; $cleanType = $removeOptional($type); RETURN TypeKind($cleanType) == "Data" AND DataTypeComponents($cleanType)[0] REGEXP "Int[0-9]+|Uint[0-9]+|Float|Double" }); $list = ListMap($filteredMembers, ($x) -> { RETURN ListCode(AtomCode($x.Name), FuncCode("Member", $str, AtomCode($x.Name))); }); RETURN FuncCode("AsStruct",$list); })); RETURN $code($strValue); }; $nums = (SELECT $filterOnlyNumericColumns(TableRow()) as nums FROM `home/yql/tutorial/users`); SELECT * FROM $nums; -- Apply the set aggregate function to the entire nums struct at once. -- Aggregation results are nested structures for the columns count/min/... $agg = (SELECT MULTI_AGGREGATE_BY(nums, AggregationFactory("count")) as count, MULTI_AGGREGATE_BY(nums, AggregationFactory("min")) as min, MULTI_AGGREGATE_BY(nums, AggregationFactory("max")) as max, MULTI_AGGREGATE_BY(nums, AggregationFactory("avg")) as avg, MULTI_AGGREGATE_BY(nums, AggregationFactory("percentile", 0.9)) as p90 FROM $nums); SELECT * FROM $agg; -- Expand the structures so that the columns have the format originalcolumn_aggrfunc. -- You can't manage with FLATTEN COLUMNS here only, because there are conflicts: in count/min, etc. -- the nested fields are identical. $rotateStruct = ($value) -> { $code = EvaluateCode(LambdaCode(($strValue) -> { -- Retrieve the top-level structure columns $topMembers = StructTypeComponents(TypeHandle(TypeOf($value))); $list = ListFlatMap($topMembers, ($x) -> { $topMember = FuncCode("Member", $strValue, AtomCode($x.Name)); -- Traverse fields in the structures $nestedMembers = StructTypeComponents($removeOptional($x.Type)); RETURN ListMap($nestedMembers, ($y) -> { RETURN ListCode(AtomCode($y.Name || "_" || $x.Name), FuncCode("Member", $topMember, AtomCode($y.Name))); }) }); RETURN FuncCode("AsStruct", $list); })); RETURN $code($value); }; $rotate = (SELECT $rotateStruct(TableRow()) FROM $agg); SELECT * FROM $rotate; ``` Links to documentation: * [EvaluateCode](https://ytsaurus.tech/docs/builtins/codegen.md#evaluatecode) * [ListFilter](https://ytsaurus.tech/docs/builtins/list.md#listfilter) * [ListMap](https://ytsaurus.tech/docs/builtins/list.md#listmap) * [Lambda functions](https://ytsaurus.tech/docs/syntax/expressions.md#lambda) * [MULTI_AGGREGATE_BY](https://ytsaurus.tech/docs/builtins/aggregation.md#multiaggregateby) * [AggregationFactory](https://ytsaurus.tech/docs/builtins/basic.md#aggregationfactory) ### Transposing a table (PIVOT) ```yql -- Sample data $input = AsList( AsStruct("2012" as year, 1 as key, 3.0 as value), AsStruct("2013" as year, 1 as key, 4.0 as value), AsStruct("2013" as year, 2 as key, 5.0 as value), AsStruct("2014" as year, 1 as key, 6.0 as value), AsStruct("2014" as year, 1 as key, 7.0 as value) ); $makePivot = ($nameColumn, $valueColumn, $nameList) -> { RETURN EvaluateCode(LambdaCode(($row) -> { $name = FuncCode("Member", $row, AtomCode($nameColumn)); $value = FuncCode("Member", $row, AtomCode($valueColumn)); $row = FuncCode("RemoveMember", $row, AtomCode($nameColumn)); $row = FuncCode("RemoveMember", $row, AtomCode($valueColumn)); $ensureOptional = ($x) -> { RETURN YQL::MatchType($x, AsAtom("Optional"), () -> { RETURN $x }, () -> { RETURN Just($x) }); }; $structItems = ListMap($nameList, ($item) -> { $adjustedValue = FuncCode("Apply", QuoteCode($ensureOptional), $value); $ifValue = FuncCode("FlatOptionalIf", FuncCode("Coalesce", FuncCode("==", $name, ReprCode($item)), ReprCode(false)), $adjustedValue); RETURN ListCode(AtomCode($item), $ifValue); }); $struct = FuncCode("AsStruct", $structItems); RETURN FuncCode("AddMember", $row, AtomCode($valueColumn), $struct); })); }; -- Build a PIVOT operation: -- 1. The `value` field is populated with the structure where you created the columns from the last list. -- 2. Depending on `year` field value, a certain field in the output `value` structure is populated. -- 3. After that, the `year` field is deleted $pivot = $makePivot("year", "value", AsList("2012", "2013", "2014")); -- Source data SELECT * FROM AS_TABLE($input); $x = (SELECT * FROM ( SELECT $pivot(TableRow()) FROM AS_TABLE($input) ) FLATTEN COLUMNS); -- Result SELECT * FROM ( SELECT AsStruct(key as key), MULTI_AGGREGATE_BY(value, AggregationFactory("sum")) FROM $x GROUP BY key ) FLATTEN COLUMNS; ``` Links to documentation: * [EvaluateCode](https://ytsaurus.tech/docs/builtins/codegen.md#evaluatecode) * [ListMap](https://ytsaurus.tech/docs/builtins/list.md#listmap) * [Lambda functions](https://ytsaurus.tech/docs/syntax/expressions.md#lambda) * [MULTI_AGGREGATE_BY](https://ytsaurus.tech/docs/builtins/aggregation.md#multiaggregateby) * [AggregationFactory](https://ytsaurus.tech/docs/builtins/basic.md#aggregationfactory) ### Merging subquery templates ```yql $combineQueries = ($query, $list) -> { RETURN EvaluateCode(LambdaCode(($world) -> { -- An implicit parameter in the subquery template is the world argument used to pass -- dependencies, for example, visible PRAGMA or COMMIT operations at the subquery template point of use. $queries = ListMap($list, ($arg) -> { -- Pass world further, as the first argument of the subquery template RETURN FuncCode("Apply", QuoteCode($query), $world, ReprCode($arg)) }); -- Merge all the results into a common list (this requires that types are the same). -- For weaker merging, you can use the UnionAll function. RETURN FuncCode("Extend", $queries); })); }; DEFINE SUBQUERY $sub($n) AS SELECT $n; END DEFINE; -- Build a query template on merging -- the results of applying the $sub subquery template for each value between 0 and 9. $fullQuery = $combineQueries($sub, ListFromRange(0, 10)); SELECT * FROM $fullQuery(); ``` Links to documentation: * [EvaluateCode](https://ytsaurus.tech/docs/builtins/codegen.md#evaluatecode) * [ListMap](https://ytsaurus.tech/docs/builtins/list.md#listmap) * [Lambda functions](https://ytsaurus.tech/docs/syntax/expressions.md#lambda) * [Subquery templates](https://ytsaurus.tech/docs/syntax/subquery.md) ### Building a subquery template with sorting by a list of column ```yql USE ; $sorted = ($world, $input, $orderByColumns, $asc) -> { $n = ListLength($orderByColumns); $keySelector = LambdaCode(($row) -> { $items = ListMap($orderByColumns, ($x) -> { RETURN FuncCode("Member", $row, AtomCode($x)); }); RETURN ListCode($items); }); $sort = EvaluateCode(LambdaCode(($x) -> { return FuncCode("Sort", $x, ListCode(ListReplicate(ReprCode($asc), $n)), $keySelector) })); RETURN $sort($input($world)); }; DEFINE SUBQUERY $source() AS PROCESS `home/yql/tutorial/users`; END DEFINE; PROCESS $sorted($source, AsList("name","age"), true); PROCESS $sorted($source, AsList("name"), true); PROCESS $sorted($source, ListCreate(TypeOf("")), true); ``` The same functionality can be achieved using [SubqueryOrderBy](https://ytsaurus.tech/docs/syntax/subquery.md#subquery-order-by). Links to documentation: * [EvaluateCode](https://ytsaurus.tech/docs/builtins/codegen.md#evaluatecode) * [ListMap](https://ytsaurus.tech/docs/builtins/list.md#listmap) * [SubqueryOrderBy](https://ytsaurus.tech/docs/syntax/subquery.md#subquery-order-by) * [Lambda functions](https://ytsaurus.tech/docs/syntax/expressions.md#lambda) * [Subquery templates](https://ytsaurus.tech/docs/syntax/subquery.md) # Metaprogramming practices in YQL ## Introduction YTsaurus doesn't support partitioned tables natively. That's why typical is the situation where a logically single table is stored as a set of physical tables split by date, hour, or the division remainder of some field hash. As a result, the queries run in YQL over YTsaurus often access a huge number of tables (dozens, hundreds, or thousands). Measured by the SQL standard, this is an exclusive situation and there are no generally adopted mechanisms to describe such queries in SQL terms. Rachel offers many extensions for the standard designed for such specific tasks. In the majority of regularly or periodically ran queries, they enable you to totally skip generation of query texts on the client. Each mechanism [is detailed in the main YQL syntax tutorial](https://ytsaurus.tech/docs/syntax/index.md), and the purpose of this article is to demonstrate how you can combine these mechanisms to achieve practically useful results on near-to-real examples. ## Use cases ### Selecting tables by dates Most logs are archived to a flat directory in YTsaurus where an independent table is created for each date. If you want to make a calculation for certain dates, but don't want to add them manually to the query text, use the `FILTER` table function and select the dates with a Lambda function. ```yql USE ; $log_type = "yql-log"; $base_folder = "logs/" || $log_type || "/1d"; $today = CurrentUtcDate(); $yesterday = $today - Interval("P1D"); $a_week_ago = $today - Interval("P1W"); $is_during_last_week = ($table_name) -> { $date = CAST($table_name AS Date); RETURN ($date BETWEEN $a_week_ago AND $yesterday) ?? false; }; SELECT COUNT(*) FROM FILTER($base_folder, $is_during_last_week); ``` Links to documentation: * [CurrentUtcDate](https://ytsaurus.tech/docs/builtins/basic.md#currentutcdate) * [Lambda functions](https://ytsaurus.tech/docs/syntax/expressions.md#lambda) * [FILTER](https://ytsaurus.tech/docs/syntax/select/index.md) ### Processing tables with a cached sliding window Imagine that every day you need to calculate some aggregate of values for the previous week (like in the previous example). By their very nature, most log tables don't change beginning from the night of the next day. Hence, nearly 6/7 of computing resources every day are wasted to repeat calculations from the previous day. YQL offers a mechanism for [automatic caching of intermediate computing results](https://ytsaurus.tech/docs/syntax/pragma/yt#querycachemode). However, as it wholly runs at the MapReduce level, the previous query would have zero cache hit: every day will produce a different set of input tables for the first Map operation (and this set is used to calculate a key in the cache). To give the automatic caching mechanism a chance to succeed (or even guarantee success, if you override [PRAGMA yt.TmpFolder](https://ytsaurus.tech/docs/syntax/pragma/yt#tmpfolder) to your custom directory/quota instead of `//tmp`), you need to calculate an aggregate for each day separately and then combine the aggregates into a single resulting value (fortunately, most aggregate functions support this). ```yql USE ; $log_type = "visit-log"; $base_folder = "logs/" || $log_type || "/1d/"; $today = CurrentUtcDate(); $range = ListFromRange(1, 9); $shift_back_by_days = ($days_count) -> { RETURN Unwrap( $today - $days_count * Interval("P1D"), "Failed to shift today back by " || CAST($days_count AS String) || " days" ); }; $dates = ListMap($range, $shift_back_by_days); DEFINE ACTION $max_income($date) AS $table_path = $base_folder || CAST($date AS String); INSERT INTO @daily_results SELECT MAX(CAST(Income AS Double)) AS MaxIncome FROM $table_path; END DEFINE; EVALUATE FOR $date IN $dates DO $max_income($date); COMMIT; SELECT MAX(MaxIncome) AS MaxIncome FROM @daily_results; DISCARD SELECT Ensure( NULL, COUNT(*) == 7, "Unexpected number of aggregates to be merged: " || CAST(COUNT(*) AS String) || " instead of 7" ) FROM @daily_results; ``` In contrast to the previous example, here we don't select table paths based on the directory contents. Instead, we generate them "out of thin air" based on the table naming conventions configured for this log. We first define an action to process each table separately (`DEFINE ACTION`) and then use a loop to apply this action to each calculated date (`EVALUATE FOR`). This query also demonstrates several ways to terminate a query with a meaningful error if something strange occurs, instead of producing an invalid result: * Going beyond the range of supported values is almost impossible for this use case. However, only in these situations, the date arithmetic can return NULL. In this context, it's quite safe to use Unwrap to convert an Optional value to a non-Optional one (exactly not NULL), producing a runtime error if the Optional type, nevertheless, included NULL. * Using Ensure, you can check any conditions when running a query and return an error if the condition isn't met. You can add it at runtime, but if you combine it with `DISCARD` (calculate something and discard the result), you can make a separate side-check, for example, based on an aggregate of a temporary or summary table as here. In the case with `DISCARD`, the first argument is Ensure, which is usually a result of its call. It doesn't bear much sense, so you can write any constant to it, for example, NULL. Links to documentation: * [Lambda functions](https://ytsaurus.tech/docs/syntax/expressions.md#lambda) * [CurrentUtcDate](https://ytsaurus.tech/docs/builtins/basic.md#currentutcdate) * [ListFromRange](https://ytsaurus.tech/docs/builtins/list.md#listfromrange) / [ListMap](https://ytsaurus.tech/docs/builtins/list.md#listmap) / [ListLength](https://ytsaurus.tech/docs/builtins/list.md#listlength) * [Unwrap](https://ytsaurus.tech/docs/builtins/basic.md#unwrap) * [DEFINE ACTION](https://ytsaurus.tech/docs/syntax/action.md) * [EVALUATE FOR](https://ytsaurus.tech/docs/syntax/action.md#evaluate-for) * [@foo](https://ytsaurus.tech/docs/syntax/select/temporary_table.md) * [COMMIT](https://ytsaurus.tech/docs/syntax/commit.md) * [DISCARD](https://ytsaurus.tech/docs/syntax/discard.md) * [Ensure](https://ytsaurus.tech/docs/builtins/basic.md#ensure) ### Use case with job processing For example, you have an external process that writes tables to a directory. Basically, these tables are jobs to be processed. You need to run a computation on the contents of such table, write the result to another directory, and delete the processed tables: all this in the transactional mode. {% cut "How can you emulate such an external process on YQL for testing purposes?" %} ```yql USE ; $root_folder = "//tmp/tasks/"; $values = ListFromRange(1, 10); DEFINE ACTION $create_task($i) AS $path = $root_folder || CAST($i AS String); INSERT INTO $path SELECT $i AS Task; END DEFINE; EVALUATE FOR $value IN $values DO $create_task($value); ``` {% endcut %} First, start the processing ```yql USE ; $tasks_folder = "tmp/tasks"; $results_folder = "tmp/results"; DEFINE ACTION $process_task($input_path) AS $output_path = String::ReplaceAll($input_path, $tasks_folder, $results_folder); INSERT INTO $output_path WITH TRUNCATE SELECT Task * Task AS Result FROM $input_path; COMMIT; DROP TABLE $input_path; END DEFINE; $tasks = ( SELECT AGGREGATE_LIST(Path) FROM FOLDER($tasks_folder, "row_count") WHERE Type == "table" AND Yson::LookupInt64(Attributes, "row_count") == 1 ); EVALUATE FOR $task IN $tasks ?? ListCreate(TypeOf($tasks)) DO $process_task($task); ``` Here, you also use the mechanism of actions that loop through a list. However, the list of input tables isn't taken "out of thin air", but from the `FOLDER` table function that provides access to the contents of an arbitrary directory in YTsaurus. Since `FOLDER` outputs not only tables but other types of nodes as well, meaning you can't be sure that the directory hosts only tables, we recommend adding a filter by Type or by arbitrary meta attributes of the table (we use row_count here as an example). For this, you'll need to query them in the second `FOLDER` argument (if you have several of them, use a semicolon) and then access the Attributes Yson-type column. ListExtract takes a list of structures that include a single element and converts it to a list of strings with paths. However, it's also possible to retrieve Path from inside the action. Links to documentation: * [String UDF](https://ytsaurus.tech/docs/udf/list/string.md) / [Yson UDF](https://ytsaurus.tech/docs/udf/list/yson.md) * [ListFromRange](https://ytsaurus.tech/docs/builtins/list.md#listfromrange) / [ListExtract](https://ytsaurus.tech/docs/builtins/list.md#listextract) * [FOLDER](https://ytsaurus.tech/docs/syntax/select/folder.md) * [DROP TABLE](https://ytsaurus.tech/docs/syntax/drop_table.md) # Accessing values inside JSON with YQL YQL provides two main methods for extracting values from JSON: - Using [**JSON functions from the SQL standard**](https://ytsaurus.tech/docs/builtins/json.md). This approach is recommended for simple cases and for teams that are familiar with them from other DBMSs. - Using [**YSON UDFs**](https://ytsaurus.tech/docs/udf/list/yson.md), built-in functions for [lists](https://ytsaurus.tech/docs/builtins/list.md), [dicts](https://ytsaurus.tech/docs/builtins/dict.md), and [lambdas](https://ytsaurus.tech/docs/syntax/expressions.md#lambda). This approach is more flexible and is tightly integrated with YQL's data type system, making it a better choice for complex cases. Below are recipes that use the same input JSON to demonstrate how to use each option to check if a key exists, get a specific value, and extract a subtree. ## JSON functions ```yql $json = @@{ "friends": [ { "name": "James Holden", "age": 35 }, { "name": "Naomi Nagata", "age": 30 } ] }@@j; SELECT JSON_EXISTS($json, "$.friends[*].name"), -- True CAST(JSON_VALUE($json, "$.friends[0].age") AS Int32), -- 35 JSON_QUERY($json, "$.friends[0]"); -- {"name": "James Holden", "age": 35} ``` `JSON_*` functions expect the `Json` data type as input. In this example, the string literal has the `j` suffix, marking it as `Json`. Tables can store data either in JSON format or as a string representation. To convert data from `String` to `JSON`, use the `CAST` function: for example, `CAST(my_string AS JSON)`. ## Yson UDF This approach typically combines multiple functions and expressions, so one query can include different strategies. ### Converting the whole JSON into YQL containers ```yql $json = @@{ "friends": [ { "name": "James Holden", "age": 35 }, { "name": "Naomi Nagata", "age": 30 } ] }@@j; $containers = Yson::ConvertTo($json, Struct>>); $has_name = ListAny( ListMap($containers.friends, ($friend) -> { return $friend.name IS NOT NULL; }) ); $get_age = $containers.friends[0].age; $get_first_friend = Yson::SerializeJson(Yson::From($containers.friends[0])); SELECT $has_name, -- True $get_age, -- 35 $get_first_friend; -- {"name": "James Holden", "age": 35} ``` You **don't** have to convert the whole JSON object into a structured combination of containers. Some fields may be omitted if not used, and some subtrees may be left in an unstructured data type like `Json`. ### Working with in-memory representation ```yql $json = @@{ "friends": [ { "name": "James Holden", "age": 35 }, { "name": "Naomi Nagata", "age": 30 } ] }@@j; $has_name = ListAny( ListMap(Yson::ConvertToList($json.friends), ($friend) -> { return Yson::Contains($friend, "name"); }) ); $get_age = Yson::ConvertToInt64($json.friends[0].age); $get_first_friend = Yson::SerializeJson($json.friends[0]); SELECT $has_name, -- True $get_age, -- 35 $get_first_friend; -- {"name": "James Holden", "age": 35} ``` ## See also - [Modifying JSON with YQL](https://ytsaurus.tech/docs/en/modifying-json.md) # Modifying JSON with YQL In memory, YQL works with immutable values. When a query needs to change something inside a JSON value, think of it as constructing a new value from parts of the old one. This sample query takes an input JSON named `$fields`, parses it, substitutes the `a` key with 0, deletes the `d` key, and adds the `c` key with value 3: ```yql $fields = '{"a": 1, "b": 2, "d": 4}'j; $pairs = DictItems(Yson::ConvertToInt64Dict($fields)); $result_pairs = ListExtend(ListNotNull(ListMap($pairs, ($item) -> { $item = if ($item.0 == "a", ("a", 0), $item); return if ($item.0 == "d", null, $item); })), [("c", 3)]); $result_dict = ToDict($result_pairs); SELECT Yson::SerializeJson(Yson::From($result_dict)); ``` ## See also - [Yson](https://ytsaurus.tech/docs/udf/list/yson.md) - [Functions for lists](https://ytsaurus.tech/docs/builtins/list.md) - [Functions for working with dicts](https://ytsaurus.tech/docs/builtins/dict.md) - [Accessing values inside JSON with YQL](https://ytsaurus.tech/docs/en/accessing-json.md) # YQL differences This section describes how YQL differs from other query languages and systems. - [Differences from ANSI SQL](https://ytsaurus.tech/docs/en/ansi.md) - [Differences from ClickHouse](https://ytsaurus.tech/docs/en/clickhouse.md) # Distinctions of YQL SQL dialect from ANSI SQL This section describes distinctions of YQL SQL dialect and the ISO/IEC 9075-2 standard (Part 2: Foundation) in the version SQL-2011. To make the comparison, we took table 38 from the standard ("Feature taxonomy and definition for mandatory features"). ## Distinctions in behavior between YQL and ANSI SQL In this table, we've put together the scenarios where the same query produces different results in YQL and ANSI SQL. | ID | Name | Behavioral distinctions | | --- | --- | --- | | E011-04 | Arithmetic operators | YQL suppresses errors in arithmetic calculations. For example, the expression `1/0` has the type `Int32?` and the value `NULL`. ANSI SQL will throw an error in this case: | | E011-06 | Implicit casting among the numeric data types | YQL doesn't provide implicit casting between NUMERIC and INTEGER/FLOAT. | | E031-01 | Delimited identifiers | In YQL, the expression `SELECT "a" FROM foo` returns a string constant. In ANSI, `"a"` is treated as the ID (the `a` column) | | E071-02 | UNION ALL table operator | In ANSI SQL, columns are joined according to their positions; in YQL, they are joined by names. Adding the `PositionalUnionAll` pragma enables the standard columnar execution for UNION ALL. In this case, column ordering is enabled automatically. | ## ANSI SQL support status | ID | Name | Support | Comment | | --- | --- | --- | --- | | **E011** | **Numeric data types** | **Partial** | | | E011-01 | INTEGER and SMALLINT data types | Yes | Signed and unsigned integral types are additionally supported: `[U]INT{8,16,32,64}` and `TINYINT`. | | E011-02 | REAL, DOUBLE PRECISION and FLOAT data types data types | Partial | `FLOAT()`isn't supported | | E011-03 | DECIMAL and NUMERIC data types | Yes | `DECIMAL(p,s)` is supported | | E011-04 | Arithmetic operators | Yes | | | E011-05 | Numeric comparison | Yes | | | E011-06 | Implicit casting among the numeric data types | Partial | The standard allows for implicit mutual casting between all the numeric types. We only implicitly cast "related" types. | | [**E021**](https://ytsaurus.tech/docs/en/####E021%20Character%20string%20types) | **Character string types** | **Partial** | `CHAR(len)`, `VARCHAR(len)` is not supported | | E021-01 | CHARACTER data type | No | | | E021-02 | CHARACTER VARYING data type | Partial | The closest analog is `String` | | E021-03 | Character literals | Partial | Automatic `'aaa' 'bbb'` concatenation isn't supported | | E021-04 | CHARACTER_LENGTH function | No | | | E021-05 | OCTET_LENGTH function | Partial | The closest analog is `LEN()` | | E021-06 | SUBSTRING | Partial | Distinctions in syntax | | E021-07 | Character concatenation | Yes | Supported for the String type | | E021-08 | UPPER and LOWER functions | No | Available using String UDF | | E021-09 | TRIM function | No | Can be implemented using String UDF | | E021-10 | Implicit casting among the fixed-length and variable-length character string types | No | Relevant types are missing | | E021-11 | POSITION function | Partial | The counterpart is `FIND` | | E021-12 | Character comparison | Partial | String type is compared bitwise | | **E031** | **Identifiers** | **Partial** | | | E031-01 | Delimited identifiers | Partial | YQL uses backticks for IDs rather than double quotes prescribed by the standard | | E031-02 | Lower case identifiers | Yes | | | E031-03 | Trailing underscore | Yes | | | **E051** | **Basic query specification** | **Partial** | | | E051-01 | SELECT DISTINCT | Yes | | | E051-02 | GROUP BY clause | Yes | | | E051-04 | GROUP BY can contain columns not in \

Overview

Data storage

How to try?

Dynamic tables

API and reference

Data processing

Overview

Data storage

How to try?

Dynamic tables

API and reference

Data processing