转载

HQL之函数使用

在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 <function_name>; -- Detail for the function> 
DESCRIBE FUNCTION EXTENDED <function_name>; -- More details

集合函数(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)

原文 https://studygolang.com/articles/24293

正文到此结束