Hive 学习笔记①

1.内部表与外部表区别

①未被external修饰的是内部表[managed table],被external修饰的是外部表[external table]；
②内部表由hive管理,外部表由hdfs管理；
③内部表数据存储在hive.metastore.warehose.dir[默认user/hive/warehouse],外部表数据存储位置由用户自己决定；（如果没有LOCATION，Hive将在HDFS上的/user/hive/warehouse文件夹下以外部表的表名创建一个文件夹，并将属于这个表的数据存放在这里）；
④删除内部表会直接删除元数据[metadata]以及存储数据,删除外部表仅仅会删除元数据,存储在hdfs上的文件不会被删除；
⑤对内部表修改会直接同步到元数据,而对外部表的结构和分区进行修改则需要修改[MSCK REPAIR TABLE table_name]；
⑥创建外部表:create external table XX；
⑦创建内部表:create table XX；
 

2.分区域分桶的区别

①分区针对的是数据的存储路径；分桶针对的是数据文件。

②分区提供一个隔离数据和优化查询的便利方式。不过，并非所有的数据集都可形成合理的分区；

③分桶是将数据集分解成更容易管理的若干部分的另一个技术；

 

3.分桶抽样查询

对于非常大的数据集，有时用户需要使用的是一个具有代表性的查询结果而不是全部结果。Hive可以通过对表进行抽样来满足这个需求。

hive (default)> select * from stu_buck tablesample(bucket 1 out of 4 on id);

注：tablesample是抽样语句，语法：TABLESAMPLE(BUCKET x OUT OF y) 。

y必须是table总bucket数的倍数或者因子。hive根据y的大小，决定抽样的比例。例如，table总共分了4份，当y=2时，抽取(4/2=)2个bucket的数据，当y=8时，抽取(4/8=)1/2个bucket的数据。

x表示从哪个bucket开始抽取，如果需要取多个分区，以后的分区号为当前分区号加上y。例如，table总bucket数为4，tablesample(bucket 1 out of 2)，表示总共抽取（4/2=）2个bucket的数据，抽取第1(x)个和第3(x+y)个bucket的数据。

注意：x的值必须小于等于y的值，否则

FAILED: SemanticException [Error 10061]: Numerator should not be bigger than denominator in sample clause for table stu_buck

 

若z表示具体bucket数，计算最后一个桶的抽样情况：

x+(z/y -1)*y=x+z-y   如果x>y,则上述公式>z，即x不能大于y

 

4.常用查询函数

①空字段赋值：NVL

NVL( string1, replace_with)

用法：如果string1为NULL，则NVL函数返回replace_with的值，否则返回string1的值，如果两个参数都为NULL ，则返回NULL。(replace_with,该字段，可以为一固定值，也可以是某列值)；

②时间函数：

to_date：日期时间转日期函数
select to_date('2015-04-02 13:34:12');
输出：2015-04-02


from_unixtime：转化unix时间戳到当前时区的时间格式
select from_unixtime(1323308943,’yyyyMMdd’);
输出：20111208


unix_timestamp：获取当前unix时间戳
select unix_timestamp();
输出：1430816254
select unix_timestamp('2015-04-30 13:51:20');
输出：1430373080


year：返回日期中的年
select year('2015-04-02 11:32:12');
输出：2015


month：返回日期中的月份
select month('2015-12-02 11:32:12');
输出：12


day：返回日期中的天
select day('2015-04-13 11:32:12');
输出：13


hour：返回日期中的小时
select hour('2015-04-13 11:32:12');
输出：11


minute：返回日期中的分钟
select minute('2015-04-13 11:32:12');
输出：32


second：返回日期中的秒
select second('2015-04-13 11:32:56');
输出：56


weekofyear：返回日期在当前周数
select weekofyear('2015-05-05 12:11:1');
输出：19


datediff：返回开始日期减去结束日期的天数
select datediff('2015-04-09','2015-04-01');
输出：8


date_sub：返回日期前n天的日期
select date_sub('2015-04-09',4);
输出：2015-04-05


date_add：返回日期后n天的日期
select date_add('2015-04-09',4);
输出：2015-04-13


from_unixtime+ unix_timestamp Hive中yyyymmdd和yyyy-mm-dd日期之间的切换：先转换成时间戳，再由时间戳转换为对应格式。
--20171205转成2017-12-05 
select from_unixtime(unix_timestamp('20171205','yyyymmdd'),'yyyy-mm-dd') from dual;

--2017-12-05转成20171205
select from_unixtime(unix_timestamp('2017-12-05','yyyy-mm-dd'),'yyyymmdd') from dual;


datediff：计算两个日期之间的天数
select datediff('2017-09-15','2017-09-01')
14

Hive中取最近30天数据
datediff(CURRENT_TIMESTAMP ,gmt_create)<=30 


Hive中 两个日期相差多少小时
select (unix_timestamp('2018-05-25 12:03:55') - unix_timestamp('2018-05-25 11:03:55'))/3600
输出：1


Hive中 两个日期相差多少分钟
select (unix_timestamp('2018-05-25 12:03:55') - unix_timestamp('2018-05-25 11:03:55'))/60

输出：60


hive 计算某一个日期属于星期几，如2018-05-20 是星期日
SELECT IF(pmod(datediff('2018-05-20', '1920-01-01') - 3, 7)='0', 7, pmod(datediff('2018-05-20', '1920-01-01') - 3, 7)) 
输出：7


hive返回上个月第一天和最后一天
--上个月第一天
select trunc(add_months(CURRENT_TIMESTAMP,-1),'MM')

select concat(substr(add_months(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),-1),1,7),'-01'); 

--上个月最后一天
select date_sub(trunc(CURRENT_TIMESTAMP,'MM'),1);


--获取当前日期: current_date
select current_date;
2019-07-16

计算一年中的第几天：
select date_format('2011-12-08 10:03:01', 'D');
342

③case when

select 
dept_id,
sum(case sex when '男' then '1' else 0 end) male_count,
sum(case sex when '女' then '1' else 0 end) famale_count 
from emp_sex 
group by dept_id

等价于

select 
dept_id,
sum(if(sex='男',1,0)) male_count,
sum(if(sex='女',1,0)) famale_count 
from emp_sex 
group by dept_id

④ 行转列

（1）concat

CONCAT(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字符串;

select concat(‘abc’,'def’,'gh’) from iteblog;

abcdefgh

(2)CONCAT_WS

CONCAT_WS(separator, str1, str2,...)：
它是一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。
如果分隔符是 NULL，返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。
分隔符将被加到被连接的字符串之间;

select concat_ws(',','abc','def','gh') from iteblog;

abc,def,gh

(3)collect_set&collect_list

COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段。

collect_list不去重；

两者通常都是需要结合groupby进行使用；

案例：

⑤列转行（参考）

（1）explode：

将hive一列中复杂的array或者map结构拆分成多行。

 (2)LATERAL VIEW

用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias
lateral view 用于和UDTF相结合使用。他会将UDTF生成的结果放在一张虚拟表中(即lateral view里)。虚拟表相当于再和主表关联, 从而达到添加“UDTF生成的字段“以外的字段, 即主表里的字段或者主表运算后的字段。

select 
movie,
category_name 
from movie_info 
lateral view explode(category) table_tmp as category_name