To further manipulate data, we can also use operators, expressions, and functions in HQL to transform data. The Hive wiki (https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF) offers specifications for all supported expressions and functions, so we do not want to repeat all of them here, except a few important tips in this chapter.

Hive has defined relational operators, arithmetic operators, logical operators, complex type operators, and complex type constructors. For relational, arithmetic, and logical operators, they are similar to standard operators in SQL/Java. For operators on a complex data type, we have already introduced them in the Understanding Hive data types Section in Chapter 3, Data Definition and Description, as well as the example of inserting data into dynamic partitions earlier in this chapter. Functions in HQL are categorized as follows:

• Mathematical functions: They are mainly used to perform mathematical calculations, such as rand(...) and pi(...)
• Collection functions: They are used to find the size, keys, and values for complex types, such as size(...)
• Type conversion functions: These are mainly cast(...) and binary(...) functions to convert one type to another
• Date functions: They are used to perform date-related calculations, such as year(...) and month(...)
• Conditional functions: They are used to check specific conditions with a defined value returned, such as coalesce(...), if(...), and case when then else end
• String functions: They are used to perform string-related operations, such as upper(...) and trim(...)
• Aggregate functions: They are used to perform aggregation (introduced in the next chapter), such as sum(...) and count(*)
• Table-generating functions: These functions transform a single input row into multiple output rows, such as explode(...) and json_tuple(...)
• Customized functions: These functions are created by Java as extensions, and are introduced in Chapter 8, Extensibility Considerations

To list all operators, built-in functions, and user-defined functions, we can use the SHOW FUNCTIONS commands. For more details of a specific function, we can use DESC [EXTENDED] function_name as follows:

> SHOW FUNCTIONS; -- List all functions
> DESCRIBE FUNCTION <function_name>; -- Detail for the function
> DESCRIBE FUNCTION EXTENDED <function_name>; -- More details 

The following are some tips and best practices using HQL functions with examples.