显示函数SHOW FUNCTIONS

应用可选 regex 模式后,返回函数列表。Returns the list of functions after applying an optional regex pattern. Spark 支持的给定数量的函数非常大,此语句与 描述函数 结合使用,可快速查找函数并了解其用法。Given number of functions supported by Spark is quite large, this statement in conjunction with describe function may be used to quickly find the function and understand its usage. LIKE子句是可选的,并且仅支持与其他系统兼容。The LIKE clause is optional and supported only for compatibility with other systems.

语法Syntax

SHOW [ function_kind ] FUNCTIONS [ [ LIKE ] { function_name | regex_pattern } ]

参数Parameters

  • function_kindfunction_kind

    指定要搜索的函数的名称空间。Specifies the name space of the function to be searched upon. 有效的名称空间为:The valid name spaces are :

    • 用户 -查找用户定义函数中 (s) 的函数。USER - Looks up the function(s) among the user defined functions.
    • 系统 -查找) 系统定义函数之间 (的函数。SYSTEM - Looks up the function(s) among the system defined functions.
    • 所有 -在用户和系统定义函数之间 () 查找函数。ALL - Looks up the function(s) among both user and system defined functions.
  • function_namefunction_name

    指定系统中现有函数的名称。Specifies a name of an existing function in the system. 可以选择使用数据库名称来限定函数名称。The function name may be optionally qualified with a database name. 如果 function_name 使用数据库进行限定,则会从用户指定的数据库解析该函数,否则将从当前数据库解析该函数。If function_name is qualified with a database then the function is resolved from the user specified database, otherwise it is resolved from the current database.

    语法:[ database_name. ] function_nameSyntax: [ database_name. ] function_name

  • regex_patternregex_pattern

    指定用于筛选语句结果的正则表达式模式。Specifies a regular expression pattern that is used to filter the results of the statement.

    • 除了 *| 字符,模式的工作方式类似于正则表达式。Except for * and | character, the pattern works like a regular expression.
    • * 单独匹配0个或多个字符, | 用于分隔多个不同正则表达式,其中的任何一个可匹配。* alone matches 0 or more characters and | is used to separate multiple different regular expressions, any of which can match.
    • 在处理之前,将在输入模式中修整前导空格和尾随空格。The leading and trailing blanks are trimmed in the input pattern before processing. 模式匹配不区分大小写。The pattern match is case-insensitive.

示例Examples

-- List a system function `trim` by searching both user defined and system
-- defined functions.
SHOW FUNCTIONS trim;
+--------+
|function|
+--------+
|    trim|
+--------+

-- List a system function `concat` by searching system defined functions.
SHOW SYSTEM FUNCTIONS concat;
+--------+
|function|
+--------+
|  concat|
+--------+

-- List a qualified function `max` from database `salesdb`.
SHOW SYSTEM FUNCTIONS salesdb.max;
+--------+
|function|
+--------+
|     max|
+--------+

-- List all functions starting with `t`
SHOW FUNCTIONS LIKE 't*';
+-----------------+
|         function|
+-----------------+
|              tan|
|             tanh|
|        timestamp|
|          tinyint|
|           to_csv|
|          to_date|
|          to_json|
|     to_timestamp|
|to_unix_timestamp|
| to_utc_timestamp|
|        transform|
|   transform_keys|
| transform_values|
|        translate|
|             trim|
|            trunc|
|           typeof|
+-----------------+

-- List all functions starting with `yea` or `windo`
SHOW FUNCTIONS LIKE 'yea*|windo*';
+--------+
|function|
+--------+
|  window|
|    year|
+--------+

-- Use normal regex pattern to list function names that has 4 characters
-- with `t` as the starting character.
SHOW FUNCTIONS LIKE 't[a-z][a-z][a-z]';
+--------+
|function|
+--------+
|    tanh|
|    trim|
+--------+