The to_date(...) function removes hours, minutes, and seconds from a date. This is useful when we need to check whether the values of date/time type columns are within the data range, such as to_date(update_datetime) between 2014-11-01 and 2014-11-31. to_date(...) can be used as follows:

> SELECT TO_DATE(FROM_UNIXTIME(UNIX_TIMESTAMP())) as currentdate;
+---------------+
| currentdate   |
+---------------+
| 2018-05-15    |
+---------------+
1 row selected (0.153 seconds)

The reverse(...) function reverses the order of each letter in a string. The split(...) function tokenizes the string using a specified tokenizer. Here is an example of using both of them to get the filename from a path:

> SELECT
> reverse(split(reverse('/home/user/employee.txt'),'/')[0])
> as linux_file_name;
+------------------+
| linux_file_name  |
+------------------+
| employee.txt     |
+------------------+
1 row selected (0.1 seconds)

Whereas explode(...) outputs each element in an array or map as separate rows, collect_set(...) and collect_list(...) do the opposite by returning a set/list of elements from each group. The collect_set(...) statement will remove duplications from the result, but collect_list(...) does not:

> SELECT 
> collect_set(gender_age.gender) as gender_set,
> collect_list(gender_age.gender) as gender_list
> FROM employee;
+-------------------+-----------------------------------+
| gender_set        | gender_list                       |
+-------------------+-----------------------------------+
| ["Male","Female"] | ["Male","Male","Female","Female"] |
+-------------------+-----------------------------------+
1 row selected (24.488 seconds)