HQL之函数使用
2025-05-28 15:43:25
在HQL中，我们可以使用关系操作符、数学操作符、逻辑操作符、复合类型操作符以及复合类型构建器。其中，关系操作符、数学操作符和逻辑操作符这三个操作符在HQL的使用方法和大多数高级编程语言，如SQL/Java/Golang，类似。HQL的函数类型总体分为：
数学函数(Mathematical functions)
集合函数(Collection functions)
类型转换函数(Type conversion functions)
日期函数(Date functions)
条件函数(Conditional functions)
字符串函数(String functions)
聚集函数(Aggregate functions)
表生成函数(Table-generating functions)
定制化函数(Customized functions)
为了显示所有操作符，内置函数以及用户自定义函数，我可以使用SHOW FUNCTIONS命令。
> SHOW FUNCTIONS; -- List all functions>
DESCRIBE FUNCTION
DESCRIBE FUNCTION EXTENDED
集合函数(Collection functions)
1.1 Size函数
用于计算MAP, ARRAY,或者内嵌MAP或的大小。当时集合为空时，返回0；当集合为NULL时，返回-1。
> SELECT
> SIZE(work_place) as array_size,
> SIZE(skills_score) as map_size,
> SIZE(depart_title) as complex_size,
> SIZE(depart_title["Product"]) as nest_size
> FROM employee;
+-------------+-----------+---------------+------------+
| array_size | map_size | complex_size | nest_size |
+-------------+-----------+---------------+------------+
| 2 | 1 | 1 | 2 |
| 1 | 1 | 2 | 1 |
| 1 | 1 | 2 | -1 |
| 1 | 2 | 1 | -1 |
+-------------+-----------+---------------+------------+
4 rows selected (0.062 seconds)
> SELECT size(null), size(array(null)), size(array());
+-----+-----+-----+
| _c0 | _c1 | _c2 |
+-----+-----+-----+
| -1 | 1 | 0 |
+-----+-----+-----+
1 row selected (11.453 seconds)
1.2 array_contains函数, 用于检查某个ARRAY是否包含某个值，返回TRUE/FALSE值。
1.3 sort_array函数，用于将某个ARRAY以ASC方式排序。
> SELECT
> array_contains(work_place, 'Toronto') as is_Toronto,
> sort_array(work_place) as sorted_array
> FROM employee;
+-------------+-------------------------+
| is_toronto | sorted_array |
+-------------+-------------------------+
| true | ["Montreal","Toronto"] |
| false | ["Montreal"] |
| false | ["New York"] |
| false | ["Vancouver"] |
+-------------+-------------------------+
日期函数(Date functions)
to_date函数从一个日期类型的数据中移除时分秒。
> SELECT TO_DATE(FROM_UNIXTIME(UNIX_TIMESTAMP())) as currentdate;
+---------------+
| currentdate |
+---------------+
| 2018-05-15 |
+---------------+
1 row selected (0.153 seconds)
字符串函数（String functions)
reverse函数把一个字符串倒序返回。split函数使用特定分词器将一个字符串切分为若干个子字符串。
> SELECT
> reverse(split(reverse('/home/user/employee.txt'),'/')[0])
> as linux_file_name;
+------------------+
| linux_file_name |
+------------------+
| employee.txt |
+------------------+
1 row selected (0.1 seconds)
explode函数把一个ARRAY/MAP的每一个元素输出成一行
collect_set函数把多行记录中的某指定字段列的数据返回为一个ARRAY，其中如有重复，会去重。
collect_list函数把多行记录中的某指定字段列的数据返回为一个ARRAY，其中如有重复，不会去重。
> 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)
虚拟列函数
目前只有两个函数，分别为INPUT__FILE__NAME和BLOCK__OFFSET__INSIDE__FILE。其中INPUT__FILE__NAME函数返回一个MAPPER任务的输入文件名，BLOCK__OFFSET__INSIDE__FILE函数返回当前全局文件的位置，如果文件是压缩过的，则返回当前block的文件相对位置。
> SELECT
> INPUT__FILE__NAME,BLOCK__OFFSET__INSIDE__FILE as OFFSIDE
> FROM employee;
+-----------------------------------------------------------------------+
| input__file__name | offside |
+-----------------------------------------------------------------------+
| hdfs://localhost:9000/user/hive/warehouse/employee/000000_0 | 0 |
| hdfs://localhost:9000/user/hive/warehouse/employee/000000_0 | 62 |
| hdfs://localhost:9000/user/hive/warehouse/employee/000000_0 | 115 |
| hdfs://localhost:9000/user/hive/warehouse/employee/000000_0 | 176 |
+-------------------------------------------------------------+---------+
4 rows selected (0.47 seconds)