Reloading, viewing, and filtering functions

To determine which Hive functions and operators are available, you reload functions, and then use the SHOW FUNCTIONS statement. An optional pattern in the statement filters the list of functions returned by the statement.

In this task, you first reload functions to make available any user-defined functions that were registered in Hive session after your session started. The syntax is:

RELOAD (FUNCTION|FUNCTIONS);

Next, you use the SHOW FUNCTIONS statement. The syntax of this statement is:

SHOW FUNCTIONS [LIKE "<pattern>"];

<pattern> represents search characters that can include regular expression wildcards.

Finally, you get more information about use by issuing the DESCRIBE FUNCTION statement.

Open the Hive shell.

Open a client connection, for example:

beeline -u jdbc:hive2://mycloudhost-3.com:10000 -n <your user name> -p

Reload functions to ensure all registered UDFs are available in your session.
```
RELOAD FUNCTIONS;
```
Use the plural form of the command. RELOAD FUNCTION is for backward compatibility.

Generate a list of available built-in and user-defined functions (UDFs).

SHOW FUNCTIONS;

The list of built-in functions, operators, and UDFs appear.

+------------------------------+
|           tab_name           |
+------------------------------+
| !                            |
| !=                           |
| $sum0                        |
| %                            |  
...

Generate a filtered list of functions using the regular expression wildcard %.

SHOW FUNCTIONS LIKE "a%";

All available functions that begin with the character a appear.

+------------------------------+
|           tab_name           |
+------------------------------+                   
| abs                          |
| acos                         |
| add_months                   |
...

Get more information about a particular function.

DESCRIBE FUNCTION abs;

+-------------------------------------------+
|                 tab_name                  |
+-------------------------------------------+
| ABS(x) - returns the absolute value of x  |
+-------------------------------------------+

Get more information about the function.

DESCRIBE FUNCTION EXTENDED abs;

+----------------------------------------------------+
|                      tab_name                      |
+----------------------------------------------------+
| ABS(x) - returns the absolute value of x           |
| Synonyms: abs                                      |
| Example:                                           |
|   > SELECT ABS(0) FROM src LIMIT 1;                |
|   0                                                |
|   > SELECT ABS(-5) FROM src LIMIT 1;               |
|   5                                                |
| Function class:org.apache.hadoop.hive.ql.udf.generic.GenericUDFAbs |
| Function type:BUILTIN                              |
+----------------------------------------------------+