1.日期格式转换（将yyyymmdd转换为yyyy-mm-dd）
select from_unixtime(unix_timestamp('20180905','yyyymmdd'),'yyyy-mm-dd')
2.hive修改库名、表名注释、表属性
----修改库名注释
alter database db_name set dbproperties('comment'='ISIC-BRMS-CORE');
----修改表名注释
alter table tbl_name set tblproperties ('comment'='各组织人员流失率统计表');
---将外部表修改为内部表，需要删除表的数据文件时，可以将外部表转换成内部表，再直接删除表，比较安全
alter table tbl_name set TBLPROPERTIES('EXTERNAL'='FALSE')
3..hive去掉字段中除字母和数字外的其它字符
select regexp_replace(a, '[^0-9a-zA-Z]', '') from tbl_name
4.hive解析json字段
content字段存储json {"score":"100","name":"zhou","class":''math"}，若要对json进行解析，则可用以下方式
---解析单个字段
select get_json_object(content,'$.score') ,
get_json_object(content,'$.name),
get_json_object(content,'$.class')
from tbl_name
---解析多个字段可以用json_tuple
select a.*
,b.score
,b.name
,b.class
from tbl a
lateral view outer json_tuple(a.content,'score', 'name', 'class') b as score,name,class
5.hive 导入数据
若从本地文件系统上传，需要加上local关键字;如果直接从hdfs路径上传，则不加local
load data [local] inpath '/data/monthcard.csv' overwrite into table tbl_name;
6.hive 避免科学计数法
select printf("%.2f",3.428777027500007E7)
7.hive collect_set和lateral view explode用法
原始数据
id1 id2 name
1 1 A
1 1 B
1 1 C
1 2 X
1 2 Y
(1)collect_set
select id1,id2,
collect_set(name) as new_name1,
collect_set(case when id2>1 then name end) as new_name2,
count(name) as cnt
from default.zql_test
group by id1,id2;
---输出结果
OK
id1 id2 new_name1 new_name2 cnt
1 1 ["C","A","B"] [] 3
1 2 ["X","Y"] ["X","Y"] 2
(2)lateral view explode
select *
from
(
select id1,id2,
collect_set(name) as new_name1,
collect_set(case when id2>1 then name end) as new_name2,
count(name) as cnt
from default. zql_test
group by id1,id2
)t
lateral view explode(new_name1) t as new_type1
lateral view explode(new_name2) t as new_type2
----输出结果
OK
t.id1 t.id2 t.new_name1 t.new_name2 t.cnt t.new_type1 t.new_type2
1 2 ["Y","X"] ["Y","X"] 2 Y Y
1 2 ["Y","X"] ["Y","X"] 2 Y X
1 2 ["Y","X"] ["Y","X"] 2 X Y
1 2 ["Y","X"] ["Y","X"] 2 X X
(3)lateral view explode outer ，加上outer会保留所有记录，两者差异可以参考之前的专题
select *
from
(
select id1,id2,
collect_set(name) as new_name1,
collect_set(case when id2>1 then name end) as new_name2,
count(name) as cnt
from default. zql_test
group by id1,id2
)t
lateral view outer explode(new_name1) t as new_type1
lateral view outer explode(new_name2) t as new_type2
;
----输出结果
OK
t.id1 t.id2 t.new_name1 t.new_name2 t.cnt t.new_type1 t.new_type2
1 1 ["B","A","C"] [] 3 B NULL
1 1 ["B","A","C"] [] 3 A NULL
1 1 ["B","A","C"] [] 3 C NULL
1 2 ["X","Y"] ["X","Y"] 2 X X
1 2 ["X","Y"] ["X","Y"] 2 X Y
1 2 ["X","Y"] ["X","Y"] 2 Y X
1 2 ["X","Y"] ["X","Y"] 2 Y Y
8.hive取前百分之几
---分组内将数据分成两片
ntile(2)over(partition by id order by create_tm)
9.hive返回星期几的方法
---2012-01-01刚好星期日
select pmod(datediff(from_unixtime(unix_timestamp()),'2012-01-01'),7) from default.dual;
--返回值0-6
--其中0代表星期日
10.hive产生uuid
select regexp_replace(reflect("java.util.UUID", "randomUUID"), "-", "");
11.hive中匹配中文
select regexp '[\\u4e00-\\u9fa5]';
12.hive中regexp_extract的用法
regexp_extract(string subject, string regex_pattern, string index)
说明：抽取字符串subject中符合正则表达式regex_pattern的第index个部分的字符串
第一参数： 要处理的字段
第二参数: 需要匹配的正则表达式
第三个参数:
0是显示与之匹配的整个字符串
1 是显示第一个括号里面的
2 是显示第二个括号里面的字段...
举例：
--取一个连续17位为数字的字符串，且两端为非数字
select regexp_extract('1、非订单号(20位):00123456789876543210；
2、订单号(17位):12345678987654321；
3、其它文字','[^\\d](\\d{17})[^\\d]',0) as s1
, substr(regexp_extract('1、非订单号(20位):01234567898765432100；
2、订单号(17位):12345678987654321；
3、其它文字','[^\\d](\\d{17})[^\\d]',0),2,17) as s2
,regexp_extract('1、非订单号(20位):00123456789876543210；
2、订单号(17位):12345678987654321；
3、其它文字','[^\\d](\\d{17})[^\\d]',1) as s3;
13.hive中快速复制一张分区表及数据
- CREATE TABLE new_table LIKE old_table;
- 使用hadoop fs -cp 命令，把old_table对应的HDFS目录的文件夹全部拷贝到new_table对应的HDFS目录下；
hadoop fs -cp /hivedata/warehouse/liuxiaowen.db/t1/* /hivedata/warehouse/liuxiaowen.db/t2/ - 使用MSCK REPAIR TABLE new_table;修复新表的分区元数据；
14.hive中排序方法
ORDER BY用于全局排序，就是对指定的所有排序键进行全局排序，使用ORDER BY的查询语句，最后会用一个Reduce Task来完成全局排序。
SORT BY用于分区内排序，即每个Reduce任务内排序。
distribute by：按照指定的字段或表达式对数据进行划分，输出到对应的Reduce或者文件中。
cluster by：除了兼具distribute by的功能，还兼具sort by的排序功能。
15.hive读写模式
1.Hive处理的数据是大数据，在保存表数据时不对数据进行校验，而是在读数据时校验，所以，hive这种读的模式，加载数据很快，减少延迟。而在数据具体使用的时候，再去处理，极高地提高了效率。如果补数据不符合建表规范，比如：表字段是int类型，而导入的是string类型，则hive会保存为null。读时模式的优点是，加载数据库快。
2.传统的数据库如mysql、oracle是写时模式，不符合格式的数据写不进去。RDBMS是写模式Hive是读模式我们传统的关系型数据库RDBMS是写模式。在RDBMS里，我们对表进行数据操作时候，RDBMS会用数据库的第一第二第三范式去检查数据的规范性，如果不符合规范，数据库就拒绝数据的加载和操作。这个验证过程消耗资源，在数据量大的时候，会影响效率。因为日常的关系型数据库处理的数据不是很多，效率慢点没关系。
大数据在对数据的加载的时候不进行校验，如果校验将降低效率，导致消息阻塞，影响数据库的读取效率。
关系型数据库RDBMS主要处理的是结构化的数据，对数据源本身就有很好的规范，加载结构化有规范的数据时候，可以进行校验。而hadoop等的大数据平台保存的数据源有些是结构化的，有些不是结构化的，无规律的，没联系的数据。无法进行校验，检测。如果检测，将导致大量不符合第一第二第三范式的数据不能保存
16.hive开窗函数
（链接地址这位作者写的非常好，值得学习http://lxw1234.com/archives/2015/04/181.htm）
17.find_in_set
-----返回str在strlist第一次出现的位置，strlist是用逗号分割的字符串。如果没有找该str字符，则返回0（只能是逗号分隔，不然返回0）
select find_in_set(‘ab’,'ef,ab,de’) ;
18.hive中查询函数的用法
desc function concat
----查看某个函数怎么使用的例子
desc function extended concat
