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
  1. 集合函数(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"]           |
+-------------+-------------------------+
  1. 日期函数(Date functions)

    to_date函数从一个日期类型的数据中移除时分秒。

> SELECT TO_DATE(FROM_UNIXTIME(UNIX_TIMESTAMP())) as currentdate;
+---------------+
| currentdate   |
+---------------+
| 2018-05-15    |
+---------------+
1 row selected (0.153 seconds)
  1. 字符串函数(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)
  1. 虚拟列函数

    目前只有两个函数,分别为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)
我来评几句
登录后评论

已发表评论数()

相关站点

热门文章