Apache-Hive实践

发表于 | 分类于

Apache-Hive实践

数据类型

基本数据类型

集合数据类型

  • STRUCT:例如: address struct<street:string, city:string> 可以通过address.street 来访问

  • ARRAY:例如:friends array 可以通过 friend[index] 来访问

  • MAP:例如:children map<string, int>, 可以通过children[key]来访问

类型的转换

  • INT不会自动转换为TINYINT类型，它会返回错误，除非使用CAST操作

  • 任何整数类型都可以隐式地转换为一个范围更广的类型，如TINYINT可以转换成INT，INT可以转换成BIGINT。

  • 所有整数类型、FLOAT和STRING类型都可以隐式地转换成DOUBLE。

  • TINYINT、SMALLINT、INT都可以转换为FLOAT。

  • BOOLEAN类型不可以转换为任何其它的类型

1
2
3
4
5
6
7
8
9
10
11
12
13
//一行数据如下
{
    "MobilePlatform":"apple",
    "APP":["jianshu","csdn"],
      "name":{
              "xiaoming":19,
              "xioahuang":17
         },
     "address":{
                "street":"1000A",
                "city":"zhongguo"
             }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
#建表语句
create table test(
    MobilePlatform string,
    APP array<string>,
    name map<string,int>,
    address  struct<street:string, city:string>
)
row format delimited fields terminated by ','
collection items terminated by '_'
map keys terminated by ':'
lines terminated by '\n';
#语句的解释
1.行列分隔符
2.MAP STRUCT 和 ARRAY 的分隔符(数据分割符号)
3.MAP中的key与value的分隔符
4.行分隔符
1
2
3
4
5
6
txt数据与导入hive的语句
apple,jianshu_weibo,xiaoming:19_xiaohua:17,1000A_zhongguo
songsong,bingbing_lili,xiao song:18_xiaoxiao song:19,hui long guan_beijing

load data local inpath "/root/test.txt" into table test

1
2
3
4
5
6
7
8
9
10
11
12
13
#查询语句和结果

select * from test;

apple	["jianshu","weibo"]	{"xiaoming":19,"xiaohua":17}	{"street":"1000A","city":"zhongguo"}
songsong	["bingbing","lili"]	{"xiao song":18,"xiaoxiao song":19}	{"street":"hui long guan","city":"beijing"}

select APP[1],name["xiaoming"], address.city from  test;

weibo	19	    zhongguo
lili	null	beijing


DDL 数据定义语言详解

库

  1. 数据库创建的标准语法
1
2
3
create database if not exists  db_demo1; 
#创建一个数据库指定在HDFS位置
create database if not exists  db_demo1  "/path";
  1. 查询数据库的语法
1
2
3
4
5
6
7
8
9
10
#1.显示 支持模糊查询
show databases;
show databases like "db_*"

#2.查询 显示数据库详细信息 extended 
desc database db_demo1;
desc database extended db_demo1;

#3.切换
use db_demo1

  1. 修改数据库的语法与注意事项

    用户可以使用ALTER DATABASE命令为某个数据库的DBPROPERTIES设置键-值对属性值，来描述这个数据库的属性信息。数据库的其他元数据信息都是不可更改的，包括数据库名和数据库所在的目录位置。

1
alter database db_demo1 set dbproperties('createtime'='20201114');
  1. 删除数据库的标准语法
1
2
3
4
#删除
drop database if exists db_demo1 ;
#强制删除
drop database db_demo1  cascade;

表

  1. 建表语法的描述

    CREATE TABLE:建表

    EXTERNA:外部表

    COMMENT:为表和列添加注释

    PARTITIONED BY:分区

    CLUSTERED BY:分桶

    SORTED BY:

    ROW FORMAT:在建表的时候可以自定义SerDe或者使用自带的SerDe

    STORED AS :设置存储的类型

    LOCATION :指定表在HDFS上的存储位置

    LIKE:复制现有表的结构,但不复制内容

1
2
3
4
5
6
7
8
#普通建表

#查询建表(查询结果添加到新表中)
create table if not exists student1 as select id, name from student;
#根据已经存在的表结构建表
create table if not exists student2 like student;
#查询类型
desc formatted student2;
  1. 内部表和外部表

  • 默认创建的表都是所谓的管理表，有时也被称为内部表,当我们删除一个管理表时，Hive也会删除这个表中数据。管理表不适合和其他工具共享数据

  • 表是外部表，所以Hive并非认为其完全拥有这份数据。删除该表并不会删除掉这份数据，不过描述表的元数据信息会被删除掉

  • 删除内部表会直接删除元数据（metadata）及存储数据；删除外部表仅仅会删除元数据，HDFS上的文件并不会被删除；

  • 对内部表的修改会将修改直接同步给元数据，而对外部表的表结构和分区进行修改，则需要修复（MSCK REPAIR TABLE
    table_name;）

  • 适用的场合:每天将收集到的网站日志定期流入HDFS文本文件。在外部表（原始日志表）的基础上做大量的统计分析，用到的中间表、结果表使用内部表存储，数据通过SELECT+INSERT进入内部表。

1
2
3
4
5
6
#内部表和外部表的转换
#把内部表转换为外部表
alter table student2 set tblproperties('EXTERNAL'='TRUE');
##把外部表转换为内部表
alter table student2 set tblproperties('EXTERNAL'='FALSE');
##注意 'EXTERNAL'='TRUE'  'EXTERNAL'='FALSE' 是固定写法区分大小写

  1. 分区表

    • 概念

      • 分区表实际上就是对应一个HDFS文件系统上的独立的文件夹，该文件夹下是该分区所有的数据文件。Hive中的分区就是分目录，把一个大的数据集根据业务需要分割成小的数据集。在查询时通过WHERE子句中的表达式选择查询所需要的指定的分区，这样的查询效率会提高很多。

      • 查询时Hive自动过滤掉不用于提高性能的分区

      • 分为静态分区和动态分区

    • 操作 (一级分区、二级分区、)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
#没有分区
create table  tb_order2(
 oid int ,
dt string ,
cost double
)
row format delimited fields terminated by "," ;

load data local inpath "/root/6.18.txt" into table  tb_order2 ;
load data local inpath "/root/6.19.txt" into table  tb_order2 ;

#查询
select * from tb_order2 where dt='2020-06-18'

#一级分区
create table  tb_p_order(
oid int ,
dt string ,
cost double
)
partitioned  by (dy string)
row format delimited fields terminated by "," ;

load data local inpath "/root/6.18.txt" into table  tb_p_order  partition(dy="06-18");
load data local inpath "/root/6.19.txt" into table  tb_p_order  partition(dy="06-19");

#查询
select * from tb_p_order where  dy="06-18";

#删除分区
alter table tb_p_order drop  partition (dy = "06-18")

#二级分区
create table tb_partition2(
id int ,
name string ,
gender string ,
birthday string
)
partitioned  by (y string , m string)
row format delimited fields terminated by "," ;

load data local  inpath "/root/a.txt"  into  table tb_partition2 partition(y='90',m='01');
load data local  inpath "/root/b.txt"  into  table tb_partition2 partition(y='90',m='02');
load data local  inpath "/root/c.txt"  into  table tb_partition2 partition(y='95',m='02');
load data local  inpath "/root/d.txt"  into  table tb_partition2 partition(y='95',m='03');

#查询
select * from tb_partition2  where y='90' and m='01';
  1. 修改表和删除表
1
2
3
4
#重命名
ALTER TABLE table_name RENAME TO new_table_name
#删除表
drop table dept_partition;

DML 数据操纵语言详解

数据导入

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
#向表中装载数据

#语法
hive> load data [local] inpath '/opt/module/datas/student.txt' overwrite | into table student [partition (partcol1=val1,…)];
#字段的解释

load data:表示加载数据
local:表示从本地加载数据到hive表；否则从HDFS加载数据到hive表
inpath:表示加载数据的路径
overwrite:表示覆盖表中已有数据，否则表示追加
into table:表示加载到哪张表
student:表示具体的表
partition:表示上传到指定分区

#通过查询语句向表中插入数据
insert overwrite table student partition(month='201708') select id, name from student where month='201709';

#查询语句中创建表并加载数据
create table if not exists student3 as select id, name from student;


数据导出

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
#查询结果导入到本地
insert overwrite local directory '/opt/module/datas/export/student' select * from student;

#查询结果格式化导入本地
insert overwrite local directory '/opt/module/datas/export/student1'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'             
select * from student;

#查询结果导入到HDFS
insert overwrite  directory '/opt/module/datas/export/student1'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'             
select * from student;

#Truncate只能删除管理表，不能删除外部表中数据
truncate table student;

DQL 数据查询语言详解

基本查询

  • 常用函数

  • 算术运算符

  • Limit

  • 空值的处理

1
2
3
4
5
6
# count( ) count(*) 区别
select count( ) from Company --包括空值
select count(*) from Company --不包括空值

# 只有count不忽略空值,例如avg函数把空值设置为0
 avg(IsNull(score，0)） as ’Average Score‘

where语句

  • between and

  • in is null exist

  • like rlike

    • % 代表零个或多个字符(任意个字符)。
    • _ 代表一个字符。
    • rlike支持正则表达式

  • 逻辑运算符

分组查询

  • group by

  • having

join连接

  • Hive支持通常的SQL JOIN语句，但是只支持等值连接，不支持非等值连接

  • join连接谓词中不支持or

基础SQL案例

1
#SQL互联网50题hive实现

排序语法

  • 全局排序(order by )

    1. Order By：全局排序，一个Reducer

    2. ASC（ascend）: 升序（默认）

    3. DESC（descend）: 降序

  • 每个MapReduce内部排序（Sort By）

    1. set mapreduce.job.reduces=3;(设置reduce数目)

    2. set mapreduce.job.reduces;(查看reduce数目)

  • 分区排序（Distribute By）

    1. 类似MR中partition，进行分区，结合sort by使用

    2. Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前

    3. 要分配多reduce进行处理，否则无法看到distribute by的效果。

  • Cluster By

    1. 当distribute by和sorts by字段相同时，可以使用cluster by方式。

    2. cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是升序排序，不能指定排序规则为ASC或者DESC。

分桶与抽样查询

  • 概念:

    1. 分区针对的是数据的存储路径；分桶针对的是数据文件,

    2. 分区提供一个隔离数据和优化查询的便利方式。不过，并非所有的数据集都可形成合理的分区，特别是之前所提到过的要确定合适的划分大小这个疑虑

    3. 分桶对数据的处理比分区更加细粒度化；

    4. 分桶和分区两者不干扰，可以把分区表进一步分桶；

1
2
3
4
5
6
7
8
9
10
11

create table stu_buck(id int, name string)
clustered by(id)
into 4 buckets
row format delimited fields terminated by '\t';

set hive.enforce.bucketing = true;

load data local inpath '/root/e.txt' into table
stu_buck;

  • 分桶抽样查询
1
2
3
4
5
6
7
8
9
10
#示例
select * from stu_buck tablesample(bucket 1 out of 4 on id);

#含义的说明

tablesample是抽样语句，语法：TABLESAMPLE(BUCKET x OUT OF y) 
y必须是table总bucket数的倍数或者因子,例如:例如，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的数据。


动态分区和静态分区详解

首先是要创建静态分区表；然后将表设置为非严格模式；再次创建动态分区表，并加载数据。

加载数据的时候，是按照静态分区的模式，将数据加载到动态分区中去。

注意事项要开启yarn不然报错:org.apache.hadoop.hive.ql.exec.mr.MapRedTask

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
#案例
#数据user.txt
u001 ZSS 23 M beijing
u002 YMM 33 F nanjing
u003 LSS 43 M beijing
u004 ZY 23 F beijing
u005 ZM 23 M beijing
u006 CL 23 M dongjing
u007 LX 23 F beijing
u008 YZ 23 M beijing
u009 YM 23 F nanjing
u010 XM 23 M beijing
u011 XD 23 F beijing
u012 LH 23 M dongjing

#建普通表语句
create  table  if not exists  tb_user(
uid string ,
name  string ,
age int ,
gender string ,
address string
)
row format delimited fields  terminated by  " " ;

##加载数据
load data local  inpath  "/root/user.txt"  into table  tb_user ;

##创建目标表
create  table  if not exists  tb_p_user(
uid string ,
name  string ,
age int ,
gender string ,
address string
)
partitioned  by (addr string)
row format delimited fields  terminated by  " " ;

#开启动态分区设置
set hive.exec.dynamic.partition=true ;
set hive.exec.dynamic.partition.mode= nonstrict;  可以从普通表中导入数据

#插入数据(这里是全部插入,也可以插入普通表的部分字段)
普通表5个字段
分区表 5个主字段 1 个分区字段
插入数据的时候字段个数类型一致  最后一个字段就是分区字段 
insert into tb_p_user partition(addr) select uid , name , age , gender , address,address  from  tb_user ;

窗口函数总结

窗口函数

1
2
3
4
5
6
7
#窗口函数的语法

over关键字用来指定函数执行的窗口范围，若后面括号中什么都不写，则意味着窗口包含满足WHERE条件的所有行，窗口函数基于所有行进行计算；如果不为空，则支持以下4中语法来设置窗口。
①window_name：给窗口指定一个别名。如果SQL中涉及的窗口较多，采用别名可以看起来更清晰易读；
②PARTITION BY 子句：窗口按照哪些字段进行分组，窗口函数在不同的分组上分别执行；
③ORDER BY子句：按照哪些字段进行排序，窗口函数将按照排序后的记录顺序进行编号；
④FRAME子句：FRAME是当前分区的一个子集，子句用来定义子集的规则，通常用来作为滑动窗口使用
  1. 偏移量函数:lag lead

​ lag(列名,往前的行数,[行数为null时的默认值，不指定为null])，可以计算用户上次购买时间，或者用户下次购买时间。

​ lead(列名,往后的行数,[行数为null时的默认值，不指定为null])

  1. first_value 和 last_value

​ first_value取分组内排序后，截止到当前行，第一个值
​ last_value取分组内排序后，截止到当前行，最后一个值

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#sql
select name,orderdate,cost,
first_value(orderdate) over(partition by name order by orderdate) as time1,
last_value(orderdate) over(partition by name order by orderdate) as time2
from order
#展示
name    orderdate   cost    time1   time2
jack    2015-01-01  10  2015-01-01  2015-01-01
jack    2015-01-05  46  2015-01-01  2015-01-05
jack    2015-01-08  55  2015-01-01  2015-01-08
jack    2015-02-03  23  2015-01-01  2015-02-03
jack    2015-04-06  42  2015-01-01  2015-04-06
mart    2015-04-08  62  2015-04-08  2015-04-08
mart    2015-04-09  68  2015-04-08  2015-04-09
mart    2015-04-11  75  2015-04-08  2015-04-11
mart    2015-04-13  94  2015-04-08  2015-04-13
neil    2015-05-10  12  2015-05-10  2015-05-10
neil    2015-06-12  80  2015-05-10  2015-06-12
tony    2015-01-02  15  2015-01-02  2015-01-02
tony    2015-01-04  29  2015-01-02  2015-01-04
tony    2015-01-07  50  2015-01-02  2015-01-07

序列函数

  • rank():1,2,2,2,5,6

  • dense_rank():1,2,2,3,4,4,5

  • row_number():1,2,3,4,5,6

  • ntile(n):用于将分组数据按照顺序切分成n片，返回当前切片值

1
#ntile(n) 支持over()

控制窗口大小的使用

PRECEDING：往前
FOLLOWING：往后
CURRENT ROW：当前行
UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED FOLLOWING：表示到后面的终点

1
2
3
4
5
6
7
8
9
10
11
#例如
select name,orderdate,cost,
sum(cost) over() as fullagg, --所有行相加
sum(cost) over(partition by name) as fullaggbyname, --按name分组，组内数据相加
sum(cost) over(partition by name order by orderdate) as fabno, --按name分组，组内数据累加 
sum(cost) over(partition by name order by orderdate rows between unbounded preceding and current row) as mw1   --和fabno一样,由最前面的起点到当前行的聚合 
sum(cost) over(partition by name order by orderdate rows between 1 preceding and current row) as mw2,   --当前行和前面一行做聚合 
sum(cost) over(partition by name order by orderdate rows between 1 preceding and 1 following) as mw3,   --当前行和前边一行及后面一行 
sum(cost) over(partition by name order by orderdate rows between current row and unbounded following) as mw4  --当前行及后面所有行 
over (order by score range between 2 preceding and 2 following) 窗口范围为当前行的数据幅度减2加2后的范围内的数据求和。
from order;

SQL习题-窗口函数

topN案例
1
2
3
4
5
6
7
8
9
10
11
12
13
#SQL
有表score
想知道学生成绩排名前几的科目
select 
*
from 
(
select 
*,
row_number() over(partition by subject order by score desc) rmp
from score
) t
where t.rmp<=3;
连续登陆案例
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
#sql
#数据
1,2020-01-01
1,2020-01-02
1,2020-01-03
1,2020-01-04
1,2020-01-07
1,2020-01-08
9,2020-01-08
7,2020-01-15
3,2020-01-09
4,2020-01-12
1,2020-01-09
2,2020-02-09
2,2020-02-10
2,2020-02-11
2,2020-02-12
2,2020-02-14
2,2020-02-15
4,2020-01-11
4,2020-01-13
4,2020-01-15

#建表与SQL
create table sigin1(
userid int,
sigindate string
)row format delimited
fields terminated by ",";
load data local inpath '/root/user.txt' into table sigin1;

#方法一,使用序列函数,date_sub()查询连续登陆天数大于三天的用户(没有去重,假设同一个用户在同一天登陆俩次,分组解决此问题)
select
userid,result
from
(
select
userid,sigindate,date_sub(sigindate,number) as result
from
(
select
userid,sigindate,
dense_rank() over (partition by userid order by sigindate) as number
from sigin1
order by  userid,sigindate )t1)t2
group by result,userid
having  count(result) >=3
order by userid;

#方法二,使用偏移函数,lag函数 偏移3个单位如果  date_sub()日期偏移3相等那么用户登录的天数就是连续3天
#这个方法可以求但是在这个题目中是不行的,因为lag是偏移函数,开始的三个日期前面没有日期,得到的结果偏移为0
select
* ,date_sub(sigindate,3) as `date`
from
(
select
userid,sigindate,lag(sigindate,3,0) over (partition by userid order by sigindate) as `date`
from
(
select
userid,sigindate
from sigin1
group by userid, sigindate)t1)t2
where sigindate is not null  and date_sub(sigindate,3) = `date`;

基础SQL案例
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
#SQL
#测试数据
jack,2017-01-01,10
tony,2017-01-02,15
jack,2017-02-03,23
tony,2017-01-04,29
jack,2017-01-05,46
jack,2017-04-06,42
tony,2017-01-07,50
jack,2017-01-08,55
mart,2017-04-08,62
mart,2017-04-09,68
neil,2017-05-10,12
mart,2017-04-11,75
neil,2017-06-12,80
mart,2017-04-13,94

#建表与需求
create table business
(
name string, 
orderdate string,
cost int
)ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

#加载数据
load  data local inpath "/root/business.txt" into table business;

1、查询在2017年4月份购买过的顾客及总人数
select
collect_set(name) as people,
count(name) as number
from
(
select
name,substr(orderdate,1,7) as result
from business
where  substr(orderdate,1,7) = '2017-04'
) t1
group by t1.result;

2、查询顾客的月购买总额,查询月购买明细
select
name,total_amount
from
(
select
*,
row_number() over (partition by name ,substr(orderdate,1,7)) as rank
from
(
select
*,
sum(cost) over(partition by name,substr(orderdate,1,7) ) total_amount
from
business) t1 )t2
where rank =1;

#月购买明细
select
*,
sum(cost) over (partition by name,substr(orderdate,1,7)) as total_amount
from business;

3、查询顾客的购买明细及到目前为止每个顾客购买总金额
select *,
sum(cost) over (partition by name order by orderdate rows between unbounded preceding  and current row )
from business;

4、查询顾客上次的购买时间----lag()over()偏移量分析函数的运用
select
*,
lag(orderdate,1,0) over (partition by name order by orderdate) as Thelastimetobuy
from business;

5、查询前20%时间的订单信息

select
name,orderdate,cost
from
(
select
*,
ntile(5) over (order by  orderdate) as portion
from business) t1
where portion =1;

列转行、行转列

列转行

  • 函数的说明:
    • CONCAT(STRING A ,STRINFG B):返回字符串的连接后的结果
    • CONCAT_WS(separator, str1, str2,…):这是一个特殊的CONCAT(),第一个参数是分隔符
    • COLLECT_SET():函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段
    • CONCAT_WS(SEPARATOR ,COLLECT_SET(column)) ===>GROUP_CONCAT（）函数
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
#SQL
#数据
孙尚香,白羊座,A
司马懿,射手座,A
吕布,白羊座,B
貂蝉,白羊座,A
许褚,射手座,A

#需求
射手座,A    司马懿|许褚
白羊座,A    孙尚香|貂蝉
白羊座,B    吕布

#建表和SQL
create table person_info(
name string,
constellation string,
blood_type string)
row format delimited fields terminated by ",";
load data local inpath "/root/f.txt" into table person_info;


select
//concat(constellation,",",blood_type),
concat_ws(",",constellation,blood_type) as list,
concat_ws("|",collect_set(name)) as name
from
person_info
group by  constellation,blood_type;

行转列

  • 函数的说明
    • EXPLODE(col)：将hive一列中复杂的array或者map结构拆分成多行。
    • LATERAL VIEW:用于和split, explode等UDTF一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合
    • split(str , 分隔符):返回一个数组
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
#SQL
#数据
《疑犯追踪》 悬疑,动作,科幻,剧情
《Lie to me》	悬疑,警匪,动作,心理,剧情
《战狼2》 战争,动作,灾难

#需求
《疑犯追踪》      悬疑
《疑犯追踪》      动作
《疑犯追踪》      科幻
《疑犯追踪》      剧情
《Lie to me》   悬疑
《Lie to me》   警匪
《Lie to me》   动作
《Lie to me》   心理
《Lie to me》   剧情
《战狼2》        战争
《战狼2》        动作
《战狼2》        灾难

#建表与SQL
create table movie_info(
movie string,
category array<string>)
row format delimited fields terminated by "|"
collection items terminated by ",";

load data local inpath "/root/g.txt" into table movie_info;

#lateral view explode(category) table_emp相当于一个虚拟表
select
movie
result
from movie_info
lateral view
explode(category) result as result

#另一种建表,使用split()切分为数组形式

case when语法、if语法

case when的语法

  • CASE 字段 WHEN 值1 THEN 值1 [WHEN 值2 THEN 值2] [ELSE 值] END
  • CASE WHEN 条件表达式 THEN 值1 [WHEN 条件表达式 [and or] 条件表达式THEN 值2] [ELSE 值] END

if的语法

  • if( , , )
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
#SQL
#数据
悟空,A,男
娜娜,A,男
宋宋,B,男
凤姐,A,女
热巴,B,女
慧慧,B,女

#需求
dpt   男      女
A     2       1
B     1       2

#建表与SQL(一)
create table tb_case_when_demo(
name string ,
dname string ,
gender string
)
row format delimited fields terminated by "," ;

load data local inpath "/root/h.txt" into table tb_case_when_demo;

select
dname,
sum(case gender when '男' then 1 else 0 end) as  nan,
sum(case gender when '女' then 1 else 0 end ) as nv
from tb_case_when_demo
group by  dname;

#SQL(二)
select
dname,
sum(case when gender ='男' then 1 else 0 end) as nan,
sum(case when gender = '女' then  1 else 0 end ) as nv
from tb_case_when_demo
group by dname;

#SQL(三)
select
dname,
sum(`if`(gender='男',1,0)) as nan,
sum(`if`(gender = '女',1,0)) as nv
from tb_case_when_demo
group by  dname;

#SQL练习题
#数据与需求
https://blog.csdn.net/qq_37933018/article/details/106878128
#一个SQL项目
https://blog.csdn.net/BeiisBei/article/details/103672522

基础SQL案例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
#SQL
#数据与需求
(用户工资组成表,基本工资,基金,提成)
1,2000,3000,1500,1
2,5000,500,1000,2
3,1500,1000,3000,2
4,3000,6000,8000,3
5,1500,2000,1800,1
6,2500,1000,1900,1
(部门表)
1,销售
2,技术
3,行政
(员工信息表)
1,zs,M,28
2,ww,F,36
3,zl,F,48
4,pp,M,44
5,wb,M,32
6,TQ,F,32

create table gz(
uid int,
jb int,
jj int,
tc int,
deptno int
)
row format delimited fields terminated by ",";
load data local inpath "/root/gz.txt" into table gz;

create table bm(
deptno string ,
name string
)
row format delimited fields terminated by ",";
load data local inpath "/root/bm.txt" into table bm;

create table yg(
uid int,
name string,
gender string,
age int
)
row format delimited fields terminated by ",";
load data local inpath "/root/yg.txt" into table yg;

1.求出公司中每个员工的姓名 和 三类收入中最高的那种收入的类型(greatest()函数)
with x as (
select
uid, greatest(jb, jj, tc)as `max`,
//case  when   greatest(jb, jj, tc) = jb then 'jb'  when greatest(jb, jj, tc) = jj then 'jj' when  greatest(jb, jj, tc) = tc then 'tc' else '_' end  as  gz_category,
case   greatest(jb, jj, tc)  when jb then 'jb' when  jj then 'jj' when tc then 'tc' else '_' end  as gz_category1
from gz )
select  yg.uid,max,gz_category1
from  yg  join  x on yg.uid = x.uid;

2.求出公司中每个岗位的薪资总和
with x as (
select
deptno,
sum(jj+tc+jb)  sum_gz
from
gz
group by deptno)
select
bm.name,x.sum_gz
from bm
join  x on x.deptno = bm.deptno;

3.求出公司中不同性别、不同年龄阶段（20-30,31-40,41-50）的员工薪资总和
with x as
(
select
uid,
gender,
case when age>20 and age <=30 then '20-30' when age>30 and age<=40 then '31-40' when age > 41 and age <=50 then '41-50' else '_' end  as stage
from yg )
select
gender,stage,sum(jb+jj+tc) as `max`
from  gz  join  x  on gz.uid = x.uid
group by  x.gender,x.stage
order by stage desc ;

内置函数集合

数学函数

字符函数

  • substr()

    substr(a,b)：从字符串a中，第b位开始取，取右边所有的字符

    substr(a,b,c)：从字符串a中，第b为开始取，取c个字符,b可为负数从后面数

  • 填充

    lpad（左填充）rpad（右填充）

    例如:select lpad(‘abc’,10,’‘); 往左填充10个\

集合函数

  • str_to_map(‘a:1,b:2,c:3’);字符串转map
  • select size(str_to_map(‘a:1,b:2,c:3’));返回map的元素个数
  • map_keys(str_to_map(‘a:1,b:2’));返回key

转换函数

  • cast():select cast(‘2018-06-28’ as date);字符串转日期类型

日期函数

  • unix_timestamp() 日期转换为当前时间戳
  • from_unixtime(t1,yyyy-MM-dd HH:mm:ss)时间戳转变为日期格式
  • from_unixtime(unix_timestamp(date_created),yyyy-MM-dd HH:mm:ss)来规范时间的格式

Hive自定义函数详解

（UDF、UDAF、UDTF）

MACRO (宏) 的使用

执行流程分析

优化策略

常用优化策略

  • 减少job数量
  • 数据量较大的情况下，慎用 count(distinct)，group by 容易产生倾斜问题
  • 合并小文件
  • 解决数据倾斜

笛卡尔积与MapJoin的使用

怎么写in、exists

  • hive 的一个高效替代方案：left semi join

小文件的合并

1
2
3
4
5
6
7
#常用设置
set hive.merge.mapfiles = true ##在 map only 的任务结束时合并小文件
set hive.merge.mapredfiles = false ## true 时在 MapReduce 的任务结束时合并小文件
set hive.merge.size.per.task = 256*1000*1000 ##合并文件的大小
set mapred.max.split.size=256000000; ##每个 Map 最大分割大小
set mapred.min.split.size.per.node=1; ##一个节点上 split 的最少值
set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat; ##执行 Map 前进行小文件合并

解决小文件过多的方案

产生小文件的来源和影响

  • 来源
    • 源数据本身有很多小文件
    • 动态分区会产生大量小文件
    • reduce个数越多, 小文件越多
    • 按分区插入数据的时候会产生大量的小文件, 文件个数 = maptask个数 * 分区数
  • 影响
    • 从Hive的角度看，小文件会开很多map，一个map开一个JVM去执行，所以这些任务的初始化，启动，执行会浪费大量的资源，严重影响性能。
    • HDFS存储太多小文件, 会导致namenode元数据特别大, 占用太多内存, 制约了集群的扩展。

解决的方法

  • 调参进行合并
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
#每个Map最大输入大小(这个值决定了合并后文件的数量)
set mapred.max.split.size=256000000;

#一个节点上split的至少的大小(这个值决定了多个DataNode上的文件是否需要合并)
set mapred.min.split.size.per.node=100000000;

#一个交换机下split的至少的大小(这个值决定了多个交换机上的文件是否需要合并)
set mapred.min.split.size.per.rack=100000000;

#执行Map前进行小文件合并
set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;

#===设置map输出和reduce输出进行合并的相关参数：

#设置map端输出进行合并，默认为true
set hive.merge.mapfiles = true

#设置reduce端输出进行合并，默认为false
set hive.merge.mapredfiles = true

#设置合并文件的大小
set hive.merge.size.per.task = 256*1000*1000

#当输出文件的平均大小小于该值时，启动一个独立的MapReduce任务进行文件merge。
set hive.merge.smallfiles.avgsize=16000000
  • distribute by rand() 将数据随机分配给 reduce
1
2
3
4
5
6
7
# 设置每个reducer处理的大小为5个G
set hive.exec.reducers.bytes.per.reducer=5120000000;

# 使用distribute by rand()将数据随机分配给reduce, 避免出现有的文件特别大, 有的文件特别小
insert overwrite table test partition(dt)
select * from iteblog_tmp
DISTRIBUTE BY rand();
  • sequencefile 作为表存储格式，不要用 textfile，在一定程度上可以减少小文件
  • 使用hadoop的archive归档
1
2
3
4
5
6
7
8
9
10
11
12
13
14
#用来控制归档是否可用
set hive.archive.enabled=true;
#通知Hive在创建归档时是否可以设置父目录
set hive.archive.har.parentdir.settable=true;
#控制需要归档文件的大小
set har.partfile.size=1099511627776;

#使用以下命令进行归档
ALTER TABLE srcpart ARCHIVE PARTITION(ds='2020-04-08', hr='12');

#对已归档的分区恢复为原文件
ALTER TABLE srcpart UNARCHIVE PARTITION(ds='2020-04-08', hr='12');

#注意，归档的分区不能够INSERT OVERWRITE，必须先unarchive
  • hadoop自带的三种小文件处理方案

电子商务消费行为分析(未找到数据)

1
#数据与需求

textfile、sequencefile 和 rcfile

文件存储编码格式 建表时如何指定 优点弊端
textfile 文件存储就是正常的文本格式，将表中的数据在hdfs上 以文本的格式存储，下载后可以直接查看，也可以使用cat命令查看 1.无需指定，默认2.显示指定stored as textfile 1. 行存储使用textfile存储文件默认每一行就是一条记录，2.可以使用任意的分隔符进行分割。3.但无压缩，所以造成存储空间大。可结合Gzip、Bzip2、Snappy等使用（系统自动检查，执行查询时自动解压），但使用这种方式，hive不会对数据进行切分，从而无法对数据进行并行操作。3.但无压缩，所以造成存储空间大。可结合Gzip、Bzip2、Snappy等使用（系统自动检查，执行查询时自动解压），但使用这种方式，hive不会对数据进行切分，从而无法对数据进行并行操作。
sequencefile 在hdfs上将表中的数据以二进制格式编码，并且将数据压缩了，下载数据以后是二进制格式，不可以直接查看，无法可视化。 1.stored as sequecefile 1.sequencefile存储格有压缩，存储空间小，有利于优化磁盘和I/O性能2.同时支持文件切割分片，提供了三种压缩方式：none,record,block（块级别压缩效率跟高）.默认是record(记录)
rcfile 在hdfs上将表中的数据以二进制格式编码，并且支持压缩。下载后的数据不可以直接可视化。 1.stored as rcfile 1.行列混合的存储格式，基于列存储。

面试题摘要

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
#SQL
#数据与需求
现有这么一批数据，现要求出：  
每个用户截止到每月为止的最大单月访问次数和累计到该月的总访问次数  
三个字段的意思：  
用户名，月份，访问次数 
A,2015-01,5
A,2015-01,15
B,2015-01,5
A,2015-01,8
B,2015-01,25
A,2015-01,5
A,2015-02,4
A,2015-02,6
B,2015-02,10
B,2015-02,5
A,2015-03,16
A,2015-03,22
B,2015-03,23
B,2015-03,10
B,2015-03,11
create table if not exists infos(name string, date string, ftime int) 
row format delimited fields terminated by ",";
load data local inpath "/root/j.txt" into table infos;

#sql(窗口写法)
select
name,mydate,
max(t1.ints) over (partition by name order by mydate),
t1.ints,
sum(ints) over (partition by name order by mydate rows  between  unbounded preceding and  current row )
from
(
select
name,mydate,sum(ints) as ints
from infos1
group by  name,mydate) t1;

sql(考虑使用自连接实现)
//核心where datea <= dateb
select nameb, dateb, visitb,
max(visita) as max_visit,
sum(visita) as sum_visit
from (
select
a.name as namea,
a.mydate as datea,
a.visit as visita,
b.name as nameb,
b.mydate as dateb,
b.visit as visitb
from (select name,mydate,sum(ints) as visit from infos1 group by name,mydate) a join
(select name,mydate,sum(ints) as visit from infos1 group by name,mydate)  b
on a.name = b.name
 ) t1
where datea <= dateb
group by nameb, dateb, visitb;

//SQL实现dense_rank()排序,sum()的窗口的实现