Hive - 内建函数

  Hive中带有一些函数，方便Hive SQL做出一些聚合、转换、分割操作。最常见的就是聚合函数（SUM，COUNT，AVG）。当然这些属于内建函数，就是当部署好Hive以后，这些函数都在，可以随时调用。查看一个函数的信息，比如查看SUM函数的信息，在Hive CLI中“desc function sum;”，如果说要详细信息：“show function extended sum;”。

一、准备

  使用内建函数得有一个表做例子，这样才能了解函数的输出与传入参数。
  创建一张表，这张表带上整数、时间，字符串字段。同时传入一些数据。

1、创建一个测试数据库
CREATE DATABASE IF NOT EXISTS database_test;
2、创建一张测试表
CREATE TABLE dual
(id int  comment 'this is id', name string  comment 'this is name',
 age int  comment 'this is age',date  string  comment 'this is date')
comment 'this is test'
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t';
3、创造一条数据
INSERT INTO TABLE dual VALUES(1,"heheman",34,"2018-06-13 20:00:00");

二、常用函数

2.1 upper/lower

  这个函数的意思就是将字段所有的字符转化成大写。

SELECT upper('Facebook'), lower('Facebook') FROM dual;
返回结果
FACEBOOK    facebook

2.2 unix_timestamp

  获取当前Unix的时间戳

SELECT unix_timestamp(), unix_timestamp("2018-01-01 20:00:00",'yyyy-MM-dd HH:mm:ss')  FROM dual;
返回结果
1528976551 (->这个时间就是2018/6/14 19:42:31)   1514808000

2.3 current_date/current_timestamp

  获取当前日期和Unix的时间戳的时间

SELECT current_date, current_timestamp FROM dual;
返回结果
2018-06-14  2018-06-14 19:47:48.175

2.4 to_date

  将字符串转化成date日期类型

SELECT to_date("2018-01-01 20:00:00") FROM dual;
返回结果
2018-01-01

2.5 year/month/day/hour/minute/second

  将字符串转化成date日期类型，提取年、月、日、时、分、秒。

SELECT year("2018-01-02 20:18:19"), month("2018-01-02 20:18:19"), day("2018-01-02 20:18:19"), hour("2018-01-02 20:18:19"), minute("2018-01-02 20:18:19"), second("2018-01-02 20:18:19") FROM dual;
返回结果
2018    1   2   20  18  19

2.6 cast

  是用来做类型转换，将字符串转成数值，或者字符串转换成date类型。

SELECT cast("5" as int), cast("2018-10-11" as date) FROM dual;
返回结果
5   2018-10-11

2.6 substr

  截取字符串，第一个参数表示要截取的字符串，第二个是从哪里开始截取，第三个，截取长度。

SELECT substr('Facebook', 5), substr('Facebook', -5), substr('Facebook', 5,1) FROM dual;
返回结果
book    ebook   b

2.7 concat

  连接多个小字符串合成一个长字符串。

SELECT concat('Face', 'book') FROM dual;
返回结果
Facebook

2.8 concat_ws

  用第一个字符串连接多个小字符串合成一个长字符串。

SELECT concat_ws('.','www', 'Facebook', 'com') FROM dual;
返回结果
www.Facebook.com

2.9 split

  将字符串按照第二个参数分割成多个子字符串。

SELECT split("192.168.100.2", "\\.") FROM dual;  ->需要转义
返回结果
["192","168","100","2"]

2.10 explode

  将参数中的数组或者Map转化成多行数据，常用于结合group by做计数统计。这就是简单的WordCount程序。

先创建一些数据：
INSERT INTO TABLE dual VALUES(2,"hello,word,welcome",34,"2018-06-13 20:00:00");
INSERT INTO TABLE dual VALUES(2,"hello,welcome",34,"2018-06-13 20:00:00");
例程：
SELECT split(name,",") FROM dual;  
返回结果
["heheman"]
["hello","word","welcome"]
["hello","welcome"]

例程：
SELECT explode(split(name,",")) FROM dual;  
返回结果
heheman
hello
word
welcome
hello
welcome


例程：
SELECT word, count(word) as c FROM (
SELECT explode(split(name,",")) as word FROM dual ) t group by word;  
返回结果
heheman 1
hello   2
welcome 2
word    1

2.11 json_tuple

  转换Json字符串，获取键值。

先创建一些数据：
INSERT INTO TABLE dual VALUES(101,'{"movice":"1192","time":"978300760"}',34,"2018-06-13 20:00:00");

例程：
SELECT 
json_tuple(name,"movice", "time") as (movice, time)
FROM dual where id = 101;  
返回结果
1192    978300760

2.12 row_number

  窗口函数，有一个命题：每种名字，取年龄最大的两个记录。这个命题需要分组，组内排序。排序是对全局的排序，做不到分组内排序。组内排序，就要涉及到窗口分析函数。

先插入一些数据
INSERT INTO TABLE dual VALUES 
(1,"hehe",1,"2018-06-13 20:00:00"),
(1,"hehe",2,"2018-06-13 20:00:00"),
(1,"hehe",3,"2018-06-13 20:00:00"),
(1,"meme",1,"2018-06-13 20:00:00"),
(1,"meme",2,"2018-06-13 20:00:00"),
(1,"meme",3,"2018-06-13 20:00:00");

例程：
SELECT id, name, age, date,
row_number() over(partition by name order by age desc) as rank 
FROM dual; 
返回结果
1   hehe    3   2018-06-13 20:00:00 1
1   hehe    2   2018-06-13 20:00:00 2
1   hehe    1   2018-06-13 20:00:00 3
1   meme    3   2018-06-13 20:00:00 1
1   meme    2   2018-06-13 20:00:00 2
1   meme    1   2018-06-13 20:00:00 3

例程：
SELECT id, name, age, date 
FROM (
    SELECT id, name, age, date,
    row_number() over(partition by name order by age desc) as rank 
    FROM dual ) t
WHERE t.rank <= 2; 
返回结果
1   hehe    3   2018-06-13 20:00:00 
1   hehe    2   2018-06-13 20:00:00 
1   meme    3   2018-06-13 20:00:00 
1   meme    2   2018-06-13 20:00:00