【HIVE系列】01-HIVE 常用操作

参考资料：
https://blog.csdn.net/wisgood/article/details/17376393
http://ju.outofmemory.cn/entry/176408

1. 数据库操作（增删改查）

1.1. 创建表

create table page_view  (  
    page_id bigint comment '页面ID',  
    page_name string comment '页面名称',  
    page_url string comment '页面URL'  
)  
comment '页面视图'  
partitioned by (ds string comment '当前时间，用于分区字段')  
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001' 
stored as rcfile  
location '/user/hive/test';  

1.2. 数据加载与插入

1.2.1. 文件导入数据

hive> load data local inpath 'xiong.txt' into table table_name;

1.2.2. 其他表导入

create table table_name as
select * from a left join b on a.1=b.1

2. 字符类型转换

Hive也包括 隐式转换（implicit conversions）和显式转换（explicitly conversions）。

　　Hive在需要的时候将会对numeric类型的数据进行隐式转换。比如我们对两个不同数据类型的数字进行比较，假如一个数据类型是INT型，另一个 是SMALLINT类型，那么SMALLINT类型的数据将会被隐式转换地转换为INT类型，这个到底和Java中的一样；但是我们不能隐式地将一个INT类型的数据转换成SMALLINT或TINYINT类型的数据，这将会返回错误，除非你使用了CAST操作。

　　任何整数类型都可以隐式地转换成一个范围更大的类型。TINYINT,SMALLINT,INT,BIGINT,FLOAT和STRING都可以隐式 地转换成DOUBLE；是的你没看出，STRING也可以隐式地转换成DOUBLE！但是BOOLEAN类型不能转换为其他任何数据类型！

我们可以用CAST来显式的将一个类型的数据转换成另一个数据类型。

3. 字符串操作

3.1. 文本替换

regexp_replace('2016-06-05 00:00:00.0', '-', '')  --第二个参数为正则表达式
translate(description,'\\t','')         --

3.2. 字符串拼接

3.2.1. 拼接函数

concat(STRING|BINARY a, STRING|BINARY b…)

select concat('abc','def','gh',123)
abcdefgh123  

concat_ws(STRING sep, STRING a, STRING b…) concat_ws(STRING sep, Array)

和concat()一样，将多个字符串连接成一个字符串，但是可以一次性指定分隔符～（concat_ws就是concat with separator）

语法：concat_ws(separator, str1, str2, …)

说明：第一个参数指定分隔符。需要注意的是分隔符不能为null，如果为null，则返回结果为null。

select concat_ws('abc','def','gh')
defabcgh

3.2.2. 分组拼接

group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator ‘分隔符’] )

select userid,bankid,group_concat(cast(creditlimit as string))
from vdm_fin.cc_user_bill_0724
group by userid,bankid
limit 100;

concat_ws('|', collect_set(str))

hive 实现相同功能（只返回不重复的值）：

SELECT id,
concat_ws('|', collect_set(str)) 
FROM t  
GROUP BY id; 

3.3. 字符串补全

3.3.1.

4. 时间计算

4.1. 获取当前时间及格式转换

from_unixtime(bigint unixtime[, string format])
select from_unixtime(unix_timestamp())   --获取当前时间
select from_unixtime(unix_timestamp(), 'yyyyMMdd HH:mm:ss')   --获取当前时间，限定格式 
select from_unixtime(unix_timestamp('2013-01-01 10:10:10'), 'yyyyMMdd')  --转换格式
select from_unixtime(unix_timestamp('2013-01-01', 'yyyy-MM-dd'), 'yyyyMMdd') 
select to_date(’2011-12-08 10:03:01′) from dual;    --转成日期格式
2011-12-08

-- 获取年月日
select year(’2011-12-08 10:03:01′) from dual;
2011
select month(’2011-08-08′) from dual; 
8
select day(’2011-12-08 10:03:01′) from dual;
10
--获取时分秒
select hour(’2011-12-08 10:03:01′) from dual; 
8
select minute(’2011-12-08 10:03:01′) from dual;
3
select second(’2011-12-08 10:03:01′) from dual; 
1
-- 获取周数
select weekofyear(’2011-12-08 10:03:01′) from dual;
49

4.2. 日期运算

--日期比较函数: datediff
--语法: datediff(string enddate, string startdate)
--返回值: int
--说明:返回结束日期减去开始日期的天数。
select datediff('2012-12-08','2012-05-09') ;
213

--日期增加函数: date_add
--语法: date_add(string startdate, int days)
--返回值: string
--说明:返回开始日期startdate增加days天后的日期。
select date_add('2012-12-08',10);
2012-12-18

--日期减少函数: date_sub
--语法: date_sub (string startdate, int days)
--返回值: string
--说明:返回开始日期startdate减少days天后的日期。
select date_sub('2012-12-08',10);
2012-11-28

4.3. 计算时间差

-- 方法一：时间戳的差值直接按照单位换算
select  post_date, gqdate, 
(unix_timestamp(post_date) - unix_timestamp(gqdate))/60  diff_min,
(unix_timestamp(post_date) - unix_timestamp(gqdate))  diff_second
from testdata limit 10;

-- 方法二：计算小时差
(hour(from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:ss'))-hour(create_time)+(datediff(from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:ss'), create_time))*24) as hour_dValue 

--计算日期差
datediff(date1, date2) 

4.4. 时间加减

5. 数值处理

5.1. 保留小数

5.2. 取整函数

5.2.1. 四舍五入取整 round

round(double a)
返回值: BIGINT
说明:返回double类型的整数值部分（遵循四舍五入）

select round(3.1415926);
3
hive> select round(3.5);
4

5.2.2. 向下取整 floor

select floor(3.1415926);
3

5.2.3. 向上取整函数: ceil/ceiling

hive> select ceil(3.1415926) from lxw_dual;
4
hive> select ceiling(3.1415926) from lxw_dual;
4

5.3. 指定精度

5.3.1. round(double a, int d)

注：发现小数位用round(col1,2)在hue不起作用

5.3.2. 强制转换 cast

小数位： CAST(zl_30 AS DECIMAL(20,2))

5.3.3. 保留固定位数的精度转换

format_number(3.4, 2)  
> 3.40(String)