OpenSearch:Introduction to TVFs

rankTvf

The rankTvf function works like the SQL rank window function to filter data after grouping. By default, it executes on each data node. Syntax:

rankTvf("group_key", "sort_key", "reserved_count", (sql))

group_key: The fields used for grouping. You can specify multiple fields separated by commas (,). This parameter is optional.

sort_key: The fields used for sorting. You can specify multiple fields. Use a plus sign (+) for ascending order and a minus sign (-) for descending order. Ascending order is the default. This parameter is required.

reserved_count: The number of records to retain in each group. Use a negative value to retain all records.

SQL: The SQL statement to execute.

rankTvf processes and filters SQL query results, preserving the original row order while removing rows that do not meet the filter criteria.

Example:

select * from table (
  rankTvf('brand','-size','1', (SELECT brand, size FROM phone))
) 
order by brand 
limit 100 

sortTvf

The sortTvf function provides local TopK sorting. You can sort and retrieve TopK results on a searcher node before a join. If you use an ORDER BY clause, the sort is pushed to the QRS node for a global sort. Syntax:

sortTvf("sort_key", "reserved_count", (sql))

sort_key: The fields used for sorting. You can specify multiple fields. Use a plus sign (+) for ascending order and a minus sign (-) for descending order. Ascending order is the default. This parameter is required.

reserved_count: The number of records to retain in each group.

sql: The SQL statement to sort.

Difference between sortTvf and rankTvf: Unlike rankTvf, the sortTvf function changes the row order of the original SQL statement.

Example:

select * from table (
  sortTvf('-size','3', (SELECT brand, size FROM phone))
)

topKTvf

The topKTvf function also provides local TopK sorting on a searcher node before a join. If you use an ORDER BY clause, the sort is pushed to the QRS node for a global sort. Syntax:

topKTvf("sort_key", "reserved_count", (sql))

sort_key: The fields used for sorting. You can specify multiple fields. Use a plus sign (+) for ascending order and a minus sign (-) for descending order. Ascending order is the default. This parameter is required.

reserved_count: The number of records to retain in each group.

sql: The SQL statement to sort.

Difference between topKTvf and sortTvf: Unlike sortTvf, the final result set from topKTvf is unordered.

Example:

select * from table (
  topKTvf('-size','3', (SELECT brand, size FROM phone))
)

enableShuffleTvf

The enableShuffleTvf function forces the outer TVF to run on the QRS node. For example, rankTvf is pushed down to a searcher node by default, but wrapping the inner SQL in enableShuffleTvf makes rankTvf run only on the QRS node. Syntax:

enableShuffleTvf((sql))

Example:

select * from table (
 enableShuffleTvf((SELECT brand, size FROM phone))
)