一丶建带有数据的表 :
1.create table(建表) ; load data(传数据) ; --先建表,在用load加载数据到表中,形成映射
2.create table ;
- insert into table + 存在的表名 + select from-插入的数据来自于后面是查询语句返回结果, 建表收插入数据
3.create external table + location HDFS_path(指定位置) ;–不常见,但是有
4.create table 表名 as select --创建表的字段类型顺序数据完全取决于后续的select查询语句返回的结果
5.unio all :
- UNION All将多个SELECT语句的结果集合并为一个独立的结果集 , 一般配合create as select使用
1.修复分区
-- 在把数据加载到分区的表中,在查看之前需要进行分区修复
msck repair table 表名;
二丶hive高阶函数
-- 获取hive中函数列表
show functions ;
-- 显示函数
1.UDTF函数
-
lateral view为侧视图,意义是为了配合UDTF来使用,把某一行数据拆分成多行数据.不加lateral view的UDTF只能提取单个字段拆分,并不能塞会原来数据表中.加上lateral view就可以将拆分的单个字段数据与原始表数据关联上.
-
语法:
tabel 表A lateral view UDTF(字段) 视图别名(虚拟表名) as a,b,c-----as 后面是爆炸出来的新字段名
-
例如 : lateral view + explode函数
select name,subview.* from test_message lateral view explode(字段名) subview as lc;
explode 函数是 hive 内置的 UDTF 函数。
展开的字段只能是array 或者 map类型:
其中 explode(array)使得结果中将 array 列表里的每个元素生成一行;
explode(map)使得结果中将 map 里的每一对元素作为一行,key 为一列,value 为一列
2.UDF函数(user define function)
- 大小写转化问题
3.行列转换函数
- 多行转单列:
数据表 row2col_1:
col1 col2 col3
a b 1
a b 2
a b 3
c d 4
c d 5
c d 6
将其转化为:
col1 col2 col3
a b 1-2-3
c d 4-5-6
此时需要两个内置的函数:
a)concat_ws(参数1,参数2),用于进行字符的拼接
参数1—指定分隔符
参数2—拼接的内容
b)collect_set(col3),它的主要作用是将某字段的值进行去重汇总,产生array类型字段
如果不想去重可用collect_list()
select col1, col2, concat_ws('-', collect_set(cast(col3 as string))) as col3
from row2col_1
group by col1, col2;
最终sql:
select col1, col2, concat_ws('-', collect_set(cast(col3 as string))) as col3
from row2col_1
group by col1, col2;
- 单列转多行
加载数据:
load data local inpath '/root/hivedata/col2row_2.txt' into table col2row_2;
a b 1,2,3
c d 4,5,6
--使用lateral view(侧视图) + explode函数
select col1, col2, lv.col3 as col3
from col2row_2
lateral view explode(split(col3, ',')) lv as col3;
- 多行转多列
数据表 row2col
col1 col2 col3
a c 1
a d 2
a e 3
b c 4
b d 5
b e 6
现在要将其转化为:
col1 c d e
a 1 2 3
b 4 5 6
此时需要使用到max(case … when … then … else 0 end),仅限于转化的字段为数值类型且为正值的情况
--最终sql:
select col1,
max(case col2 when 'c' then col3 else 0 end) as c,
max(case col2 when 'd' then col3 else 0 end) as d,
max(case col2 when 'e' then col3 else 0 end) as e
from row2col
group by col1;
- 多列转多行
数据表 col2row:
col1 c d e
a 1 2 3
b 4 5 6
现要将其转化为:
col1 col2 col3
a c 1
a d 2
a e 3
b c 4
b d 5
b e 6
这里需要使用union进行拼接。
union 可以结合多个select语句 返回共同的结果集
保证每个select语句返回的数据类型个数是一致的。
--最终sql:
select col1, 'c' as col2, c as col3 from col2row
UNION
select col1, 'd' as col2, d as col3 from col2row
UNION
select col1, 'e' as col2, e as col3 from col2row
order by col1, col2;
-
条件判断函数: CASE
-
语法 : case a when b then c [when d then e]* [else f] end ;
返回值 : T
说明:如果 a 等于 b ,那么返回 c ;如果 a 等于 d ,那么返回 e ;否则返回 f
举例:
hive> Select case 100 when 50 then ‘tom’ when 100 then ‘mary’ else ‘tim’ end from dual;
4.json数据解析函数
selcet get_json_object (t.json, '$.id') from table t ;
5.reflect函数
- reflect反射函数可以调用java中的工具类的函数(就是hive的lib目录下的各种jar包中的)
select reflect("工具类的全路径名","方法名",要判断列名) from 表
6.正则替换问题 Regexp_replace
7.窗口函数–over(partition order by)
使用 : 聚合函数+窗口函数
排序函数+窗口函数
-
应用 : 分组内topN问题,也可以不分组
-
row_number() over(partition by xxx order by xx desc|asc) --顺序排序 1 2 3 4
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-db0Qky1n-1592753670993)(C:\Users\13309\AppData\Roaming\Typora\typora-user-images\image-20200526201255958.png)]
-
rank() over(partition by xxx order by xx desc|asc) -- 跳跃函数 1 2 2 4
-
dense_rank() over(partition by xxx order by xx desc|asc) -- 重复排序 1 2 2 3
NTILE(数字) over(parttion by xxx order by xxx) --关注整体的几分之几 --数字是几就代表分成几个部分,按order by字段名的顺序 --如果不能平均分,小的部分多,各部分之间相差不超过1 --如果7条数据分3部分: 3条 2条 2条 --想要取出其中的几分之几就是用嵌套 select * from (NTILE(数字) over(parttion by xxx order by xxx)as rn from 表名) tmp where rn =1;
8.条件转换函数 --case when
9.url数据的解析的内置函数
-
parse_url_tuple : 拆分URL字段的内置函数.(parse解析的意思)
-
–针对来访url字段 如何抽取?http_referer
–本质:如何在hive中去解析url格式数据
–hive内置一个UDTF函数(parse_url_tuple) 专门用于解析标准url格式数据 从中提取出指定的属性值select parse_url_tuple("http://www.itcast.cn/bigdata/1.html?id=18&name=allenwoon",'HOST','PATH','QUERY'); +----------------+------------------+-----------------------+--+ | c0 | c1 | c2 | +----------------+------------------+-----------------------+--+ | www.itcast.cn | /bigdata/1.html | id=18&name=allenwoon | +----------------+------------------+-----------------------+--+ --出现了技术问题 数据中url不是标准的 左右两边有“”存在 想法把其替换成为空 select b.* from ods_weblog_origin a lateral view parse_url_tuple(regexp_replace(http_referer,"\"",""),'HOST') b limit 10; --需要配和lateral view parse_url_tuple(url字段,'HOST','PATH',...),才能将分开的数据和表中原来的数据联系在一起. --注意格式,起别名必须一样否则报错!!! SELECT a.valid,a.remote_addr,a.remote_user,a.time_local, a.request,a.status,a.body_bytes_sent,a.http_referer,a.http_user_agent,b.ref_host,b.ref_path,b.ref_query,b.ref_query_id FROM ods_weblog_origin a LATERAL VIEW parse_url_tuple(regexp_replace(http_referer, "\"", ""), 'HOST', 'PATH','QUERY', 'QUERY:id') b as ref_host, ref_path, ref_query, ref_query_id;
10.对时间解析的内置函数
–针对time_local字段 如何抽取?
–方式1:substring截取
–方式2:把时间变成timestamp类型 使用hive内置的日期函数处理
--substring(字段,数字,数字); 含头不含尾
第一个数字 : 截取开始的索引位置(索引是从0开始的)
第二个数字 : 截取的个数
select substring(time_local,6,2) as month,substring(time_local,12,2) as hour from ods_weblog_origin limit 1;
--substring(字段,数字); 括号里只有一个数字代表开始截取的索引,并一致截取到最后
select substring(time_local,12) from ods_weblog_origin limit 1;--截取时间
-- 格式化时间的函数:
date_fotmat("原来的格式","想解析成的格式")