快速入门Hive语法_hive null defined

---

1、DDL

1.1 数据库—创建

若所创的数据库已存在，[if not exists]可确保不报错

creat database [if not exists] database_name;
1

1.2 数据库—显示

show databases							//显示全部的数据库列表
show databases [like 'str*'];   		//显示正则匹配成功的数据库列表
desc database databaseName;            //显示数据库信息
desc database extended databaseName;   //显示更详细的数据库信息
1
2
3
4

1.3 数据库—修改

修改创建时间

alter database databaseName set dbproperties('createtime'='20170830'); 
1

1.4 数据库—删除

• 删除空数据库

drop database [if exists] databaseName 
1

• 强制删除非空数据库

drop database databaseName cascade;
1

1.5 表—创建

1.5.1 建表参数

  CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name   
  #Note: TEMPORARY available in Hive 0.14.0 and later

  [(col_name data_type [COMMENT col_comment], ... [constraint_specification])]

  [COMMENT table_comment]

  [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]

  [CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
  
  [ROW FORMAT row_format] 
  
  [STORED AS file_format]
     
  [LOCATION hdfs_path]
  
  [TBLPROPERTIES (property_name=property_value, ...)] 
  #Note: Available in Hive 0.6.0 and later
  
  [AS select_statement];  
  #Note: Available in Hive 0.5.0 and later; not supported for external tables

  [like tableName]

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

1.5.2 row format

Hive将HDFS上的文件映射成表结构，通过分隔符来区分列（比如’,’ ‘;’ or ‘^’ 等），row format就是用于指定序列化和反序列化的规则。

• fields terminated by char。设置列分隔符
• collection items terminated by char 。集合内的元素分隔符
• map keys terminated by char。map的kv分隔符
• lines terminated by char。行分隔符，默认是换行符
• null defined as char。空值替换

1.5.3 三种创建表的方法

• 直接建表法

  create table table_name(col_name data_type);
  1

  一个复杂的例子，主要按照上面的定义的格式顺序去进行编写

  create external table if not exist dmp_clearlog (        #external，创建外部表
    date_log string COMMENT 'date in file', 
    hour int COMMENT 'hour', 
    imei_orgin string COMMENT 'origin value of imei', 
    mac_orgin string COMMENT 'origin value of mac', 
    mac_md5 string COMMENT 'mac after md5 encrypt', 
    android_id string COMMENT 'androidid', 
    os string  COMMENT 'operating system', 
    ip string COMMENT 'remote real ip', 
    app string COMMENT 'appname' )
    
    COMMENT 'cleared log of origin log'   #表的描述
  
    PARTITIONED BY ( date String COMMENT 'date used by partition' )     #以date进行分区
    ROW FORMAT delimited fileds terminated by ','  lines terminated by '\n' #列分隔符、行分隔符
  
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16

• 查询建表法
  通过AS 查询语句完成建表：将子查询的结果存在新表里，有数据。
  常用语中间表

  create table new_key_value_store (
  id string, name string)
  as
  select * from key_value_store
  sort by new_key, key_value_pair;
  1
  2
  3
  4
  5

• like建表法
  创建结构完全相同的表，但是没有数据。
  常用语中间表

  CREATE TABLE empty_key_value_store
  LIKE key_value_store;
  1
  2

1.5.4 内部表和外部表的转换

• 查看表的信息，看表的类型

  desc formatted test;
  1

  

• 修改内部表 student2 为外部表

  alter table student2 set tblproperties('EXTERNAL'='TRUE'); 
  1

  

• 修改外部表 student2 为内部表

  alter table student2 set tblproperties('EXTERNAL'='FALSE');
  1

  
  注意：(‘EXTERNAL’=‘TRUE’)和(‘EXTERNAL’=‘FALSE’)为固定写法，区分大小写！

1.6 表—修改

1.6.1 重命名表

alter table table_name rename to new_table_name 	
1

1.6.2 增加、删除表分区

• 增加分区

  ALTER TABLE table_name ADD  
    partition_spec [ LOCATION 'location1' ]   
    partition_spec [ LOCATION 'location2' ] ...
  1
  2
  3

• 删除分区

  ALTER TABLE table_name drop partition (partcol1[=value1]);
  1

1.6.3 增加/修改/替换列信息

修改列名
不能只改列名，但所改的类型可以和原来的一样

ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] 
1

增加列

ALTER TABLE table_name ADD COLUMNS (col_name data_type [COMMENT col_comment], ...)
1

1.7 表—删除

drop table tableName;
1

---

2、DML

2.1 表 ---- 数据导入

第一类：往已存在的表插入数据 （用得较多）

• load data
  往已存在的表插入文件数据

  load data [local] inpath 'filePath' [overwrite] into table tableName [partition (partcol1=val1,…)];
  1

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

• insert
  往已存在的表插入查询结果

  Insert overwrite/into table tableName select * from table1;
  1

  参数	功能
  Insert	表示插入数据
  overwrite	表示覆盖表中已有数据
  into	表示在尾部追加数据
  tableName	表示具体的表
  select * from table1	表示查询结果

第二类：在创建一张表的同时插入数据

• as select
  查询语句中创建表并加载数据，不用定义列

  Create table tableName [row format delimited fields terminated by ‘\t’] as select id,name from tableName1
  1

• location
  创建表时通过 Location 指定加载HDFS数据路径，要指定列

  create external table if not exists student5( id int, name string)
  	[row format delimited fields terminated by '\t'] location '/flowName';
  1
  2

  注意

  • 当所要加载的文件就在 /user/hive/warehouse 中，可以不用写 location filePath，因为默认路径就是这个；而不在这里面时，就只能通过 location filePath 指定地址了。
  • 生产环境当中使用 location 时，往往要使用外部表。因为该数据往往是共享的。

第三类：import，本地文件导入

要求

• 导入的文件必须是通过 export 导出的（因为export导出的文件有metastore）

• 表不存在或表示空的

  import table student2 from '/user/hive/warehouse/export/student';
  1

2.2 表 ---- 数据导出

• Insert 导出

  参数	功能
  local	表示导出到本地，否则导出到HDFS

  hive (default)> insert overwrite [local] directory filePath select * from student;
  1

• Hadoop 命令导出到本地

  dfs -get start_filePath end_filePath;
  1

• Hive Shell 命令导出到本地
  
  一个 > 表覆盖；两个 > 表追加

  bin/hive -e 'select * from default.student;' > /opt/module/hive/data/export/student4.txt;
  1

• sqoop导出

  待续。。
  。。。。。
  。。。。。。

• export导出到本地

  export table student2 to ‘user/hive/warehouse/export/student’;
  1

export 和 import 主要用于两个 Hadoop 平台集群之间 Hive 表迁移。

2.3 表 ---- 清空表中数据

 truncate table student; 
1

注意：Truncate 只能删除管理表，不能删除外部表中数据

3、查询

数据准备

dept：

10	ACCOUNTING	1700
20	RESEARC	1800
30	SALE	1900
40	OPERATIONS	1700
1
2
3
4

emp：

7369	SMITH	CLERK	7902	1980-12-17	800.00		20
7499	ALLEN	SALESMAN	7698	1981-2-20	1600.00	300.00	30
7521	WARD	SALESMAN	7698	1981-2-22	1250.00	500.00	30
7566	JONES	MANAGER	7839	1981-4-2	2975.00		20
7654	MARTIN	SALESMAN	7698	1981-9-28	1250.00	1400.00	30
7698	BLAKE	MANAGER	7839	1981-5-1	2850.00		30
7782	CLARK	MANAGER	7839	1981-6-9	2450.00		10
7788	SCOTT	ANALYST	7566	1987-4-19	3000.00		20
7839	KING	PRESIDENT		1981-11-17	5000.00		10
7844	TURNER	SALESMAN	7698	1981-9-8	1500.00	0.00	30
7876	ADAMS	CLERK	7788	1987-5-23	1100.00		20
7900	JAMES	CLERK	7698	1981-12-3	950.00		30
7902	FORD	ANALYST	7566	1981-12-3	3000.00		20
7934	MILLER	CLERK	7782	1982-1-23	1300.00		10
1
2
3
4
5
6
7
8
9
10
11
12
13
14

创建表

create table if not exists dept(
deptno int,dname string, loc int
)
row format delimited fields terminated by '\t';

1
2
3
4
5

create table if not exists emp( empno int,
ename string, job string, mgr int,
hiredate string, sal double,
comm double, deptno int)
row format delimited fields terminated by '\t';

1
2
3
4
5
6

注意
（1）SQL 语言大小写不敏感。
（2）SQL 可以写在一行或者多行
（3）关键字不能被缩写也不能分行
（4）各子句一般要分行写。
（5）使用缩进提高语句的可读性。

3.1 基本查询

3.1.1 全表查询和特定列查询

select * from emp; 
1

select empno, ename from emp; 
1

3.1.2 列别名

• 重命名一个列
• 便于计算
• 紧跟列名，也可以在列名和别名之间加入关键字‘AS’

select ename AS name, deptno dn from emp; 
1

3.1.3 算术运算符

select sal +1 from emp;
1

3.1.4 常用函数

select count(*) cnt from emp;
1

select max(sal) max_sal from emp;
1

select min(sal) min_sal from emp;
1

select avg(sal) avg_sal from emp;
1

select sum(sal) sum_sal from emp;
1

3.1.5 Limit 语句

典型的查询会返回多行数据。LIMIT 子句用于限制返回的行数。

select ename AS name, deptno dn from emp limit 5;
1

3.1.6 Where 语句

使用 WHERE 子句，将不满足条件的行过滤掉。

select * from sal money where sal >1000; 
1

注：where 子句中不能使用字段别名。

3.1.7 比较运算符（Between / In / Is Null）

3.1.8 Like 和 RLike

1. 使用 LIKE 运算选择类似的值
2. 选择条件可以包含字符或数字:
   • % 代表零个或多个字符(任意个字符)。
   • _ 代表一个字符。
3. RLIKE 子句
   RLIKE 子句是 Hive 中这个功能的一个扩展，其可以通过 Java 的正则表达式这个更强大 的语言来指定匹配条件。

（1）查找名字以 A 开头的员工信息

select * from emp where ename LIKE 'A%'; 	
1

（2）查找名字中第二个字母为 A 的员工信息

select * from emp where ename LIKE '_A%'; 	
1

（3）查找名字中带有 A 的员工信息

select * from emp where ename RLIKE '[A]'; 
1

3.1.8 逻辑运算符（And/Or/Not）

3.2 分组语句

3.2.1 Group By 语句

GROUP BY 语句通常会和聚合函数一起使用，按照一个或者多个列队结果进行分组，然后对每个组执行聚合操作。

（1）计算 emp 表每个部门的平均工资

select t.deptno, avg(t.sal) avg_sal from emp t group by t.deptno;
1

（2）计算 emp 每个部门中每个岗位的最高薪水

select t.deptno, t.job, max(t.sal) max_sal from emp t group by t.deptno, t.job;
1

3.2.2 Having 语句

having 只用于 group by 分组统计语句。（进行分组数据筛选）

1. 求每个部门的平均薪水大于 2000 的部门 求每个部门的平均工资

 select deptno, avg(sal) from emp group by deptno;             
1

2. 求每个部门的平均薪水大于 2000 的部门

select deptno, avg(sal) avg_sal from emp group by deptno having avg_sal > 2000;
1

3.3 Join 语句

数据准备

1001,name1
1002,name2
1003,name3
1007,name7
1008,name8
1009,name9
1111,name1111
2222,name2222

1001,11
1002,22
1003,33
1010,10
1011,11
1012,12
1111,111
2222,222
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

create table stu_name(id_num string, name string)
row format delimited fields terminated by ',';
load data inpath '/test/stu_name.txt' into table stu_name;

create table stu_score(id_num string, score int)
row format delimited fields terminated by ',';
load data inpath '/test/stu_score.txt' into table stu_score;
1
2
3
4
5
6
7

3.3.1 表的别名

1）好处
（1）使用别名可以简化查询。
（2）使用表名前缀可以提高执行效率。
2）案例实操
合并员工表和部门表

select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno = d.deptno;
1

3.3.2 union (all)

纵向连接
注意：
1）若union后的结果镶嵌在内层，要起别名；
2）使用union，表之间的字段类型要一致，必要时要进行补充；
3）Union：对两个结果集进行并集操作，有去重，同时进行默认规则的排序；
4）Union All：对两个结果集进行并集操作，无去重，不进行排序；

select 
  *
from
(
    select 
      id_num,
      name,
      'Nu_11',
      0
    from stu_name
    union 
    select
      'Nu_22',
      'Nu_22',
      id_num,
      score
    from stu_score
) aa;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

1009	name9	Nu_11	0
1002	name2	Nu_11	0
Nu_22	Nu_22	1004	44
Nu_22	Nu_22	1003	33
Nu_22	Nu_22	1005	55
Nu_22	Nu_22	1006	66
Nu_22	Nu_22	1002	22
1007	name7	Nu_11	0
1003	name3	Nu_11	0
1008	name8	Nu_11	0
1001	name1	Nu_11	0
1
2
3
4
5
6
7
8
9
10
11

3.3.3 (inner) join

内连接，inner可以省略

1. 有on 条件，两边关联只取交集。

   select 
       *
   from stu_name
   join stu_score on stu_name.id_num=stu_score.id_num;
   1
   2
   3
   4

   1001	name1	1001	11
   1002	name2	1002	22
   1003	name3	1003	33
   1
   2
   3

2. 没有on条件，左表和右表的列通过笛卡尔积的形式表达出来

   select 
       *
   from stu_name
   inner join stu_score;
   1
   2
   3
   4

   1001	name1	1001	11
   1001	name1	1006	66
   1001	name1	1005	55
   1001	name1	1004	44
   1001	name1	1003	33
   1001	name1	1002	22
   1002	name2	1001	11
   1002	name2	1006	66
   1002	name2	1005	55
   1002	name2	1004	44
   1002	name2	1003	33
   1002	name2	1002	22
   1
   2
   3
   4
   5
   6
   7
   8
   9
   10
   11
   12

3.3.4 left (outer) join

left (outer) join ,在两张表进行连接查询时，会返回左表所有的行，即使在右表中没有匹配的记录。
左外连接，有无 outer，效果一样

1. left join
   

   select 
       *
   from stu_name
   left join stu_score on stu_name.id_num=stu_score.id_num;
   1
   2
   3
   4

   1001	name1	1001	11
   1002	name2	1002	22
   1003	name3	1003	33
   1007	name7	NULL	NULL
   1008	name8	NULL	NULL
   1009	name9	NULL	NULL
   1
   2
   3
   4
   5
   6

2. left outer join
   

   select 
       *
   from stu_name
   left outer join stu_score on stu_name.id_num=stu_score.id_num;
   1
   2
   3
   4

   1001	name1	1001	11
   1002	name2	1002	22
   1003	name3	1003	33
   1007	name7	NULL	NULL
   1008	name8	NULL	NULL
   1009	name9	NULL	NULL
   1
   2
   3
   4
   5
   6

3.3.5 right (outer) join

right (outer) join 在两张表进行连接查询时，会返回右表所有的行，即使在左表中没有匹配的记录。
右外连接，有无 outer，效果一样

1. right join
   

   select 
       *
   from stu_name
   right join stu_score on stu_name.id_num=stu_score.id_num;
   1
   2
   3
   4

   1001	name1	1001	11
   1002	name2	1002	22
   1003	name3	1003	33
   NULL	NULL	1004	44
   NULL	NULL	1005	55
   NULL	NULL	1006	66
   1
   2
   3
   4
   5
   6

2. right outer join
   

   select 
       *
   from stu_name
   right outer join stu_score on stu_name.id_num=stu_score.id_num;
   1
   2
   3
   4

   1001	name1	1001	11
   1002	name2	1002	22
   1003	name3	1003	33
   NULL	NULL	1004	44
   NULL	NULL	1005	55
   NULL	NULL	1006	66
   1
   2
   3
   4
   5
   6

3.3.6 full (outer) join

full (outer) join：返回所有表中符合条件的所有记录。如果任一表的指定字 段没有符合条件的值的话，那么就使用 NULL 值替代。
全外连接，有无 outer，效果一样

1. full join

   select 
       *
   from stu_name
   full join stu_score on stu_name.id_num=stu_score.id_num;
   1
   2
   3
   4

   1001	name1	1001	11
   1002	name2	1002	22
   1003	name3	1003	33
   NULL	NULL	1004	44
   NULL	NULL	1005	55
   1007	name7	NULL	NULL
   NULL	NULL	1006	66
   1008	name8	NULL	NULL
   1009	name9	NULL	NULL
   1
   2
   3
   4
   5
   6
   7
   8
   9

2. full outer join

   select 
       *
   from stu_name
   full outer join stu_score on stu_name.id_num=stu_score.id_num;
   1
   2
   3
   4

   1001	name1	1001	11
   1002	name2	1002	22
   1003	name3	1003	33
   NULL	NULL	1004	44
   NULL	NULL	1005	55
   1007	name7	NULL	NULL
   NULL	NULL	1006	66
   1008	name8	NULL	NULL
   1009	name9	NULL	NULL
   1
   2
   3
   4
   5
   6
   7
   8
   9

3.3.7 多表连接

注意：连接 n 个表，至少需要 n-1 个连接条件。例如：连接三个表，至少需要两个连接条件。

数据准备

170	Beijin
0	g
180	London
1
2
3

1）创建位置表

create table if not exists location( loc int,
loc_name string
)
row format delimited fields terminated by '\t';
1
2
3
4

2）导入数据

hive (default)> load data local inpath '/opt/module/hive-3.1.2/test_data/location.txt' into table location;
1

3）多表连接查询

SELECT e.ename, d.dname, l.loc_name from emp e
JOIN dept d ON	 d.deptno = e.deptno 
JOIN location l ON	 d.loc = l.loc;

1
2
3
4

3.3.8 笛卡尔积

1）笛卡尔集会在下面条件下产生
（1）省略连接条件
（2）连接条件无效
（3）所有表中的所有行互相连接
2）案例实操

select empno, dname from emp, dept;
1

3.4 排序

3.4.1 全局排序（Order By）

Order By：全局排序，只有一个 Reducer

• ASC（ascend）: 升序（默认） ； DESC（descend）：降序
• ORDER BY 子句在 SELECT 语句的结尾

select ename, sal from emp order by sal; 
1

select ename, sal from emp order by sal desc; 
1

3.4.2 按照别名排序

按照别名排序，可以看出 ordor by 是在查询结果上进行的。

select ename, sal*2 twosal from emp order by twosal;
1

3.4.3 多个列排序

按照多个属性进行排序

select ename, deptno, sal from emp order by deptno, sal;
1

3.4.4 每个 Reduce 内部排序（Sort By）

Sort By：对于大规模的数据集 order by 的效率非常低。在很多情况下，并不需要全局排 序，此时可以使用 sort by。

sort by对每个reduce内部排序，常和 Distribute By 一起用；每个 Reducer 内部进行排序，对全局结果集来说一般不是有序的。

设置reduce的个数

set mapreduce.job.reduces=3;
1

查看reduce的个数

set mapreduce.job.reduces; 
1

根据部门编号降序查看员工信息

select deptno from emp sort by deptno desc; 
1

3.4.5 分区（Distribute By）

Distribute By： 在有些情况下，我们需要控制某个特定行应该到哪个 reducer，通常是为 了进行后续的聚集操作。distribute by 子句可以做这件事。distribute by 类似 MR 中 partition（自定义分区），进行分区，结合 sort by 使用。

对于 distribute by 进行测试，一定要分配多 reduce 进行处理，否则无法看到 distribute by 的效果。

设置reduce的个数

set mapreduce.job.reduces=3;
1

先按照部门编号分区，再按照员工编号降序排序。

select deptno,empno from emp distribute by deptno sort by empno desc;
1

3.4.6 Cluster By

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

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

以下两种写法等价

select * from emp cluster by deptno;
1

select * from emp distribute by deptno sort by deptno;
1

4、分区表和分桶表

4.1 分区表

当然，如果数据量比较小，可能看不出优势，甚至可能时间还更久；数据量比较大时，优势就会很明显。

假如有个10分区，当使用分区属性来查询时，程序直接根据分区属性值定位到该分区，查询数据量下降到了原先的10%！！！！！

1. 数据准备

   dept_20200401.log

   10	ACCOUNTING	1700
   20	RESEARCH	1800
   1
   2

   dept_20200402.log

   30      SALES   1900
   40      OPERATIONS      1700
   1
   2

   dept_20200403.log

   50      TEST    2000
   60      DEV     1900
   1
   2

2. 建表

   • 注意：除了表中的三个属性外，还要用 partitioned by (day string)，这是一个用于分区的属性。

   create table dept_partition( deptno int, dname string, loc string)
   partitioned by (day string)
   row format delimited fields terminated by '\t';
   1
   2
   3

3. 导入数据

   • 每次导入的数据都会被分配到同一个分区；导入数据时，要在最后指明该分区属性。

   load data local inpath '/opt/module/hive-3.1.2/test_data/dept_20200401.log' into table dept_partition partition(day='20200401');
   load data local inpath '/opt/module/hive-3.1.2/test_data/dept_20200402.log' into table dept_partition partition(day='20200402');
   load data local inpath '/opt/module/hive-3.1.2/test_data/dept_20200403.log' into table dept_partition partition(day='20200403');
   1
   2
   3

   查看分区表的目录结构
   

4. 查询分区表中数据 单分区查询

   select * from dept_partition where day='20200401'; 
   1

   

5. 多分区联合查询

   select * from dept_partition where day='20200401' 
   union
   select * from dept_partition where day='20200402' 
   union
   select * from dept_partition where day='20200403';
   1
   2
   3
   4
   5

   select * from dept_partition where day='20200401' or day='20200402' or day='20200403'; 
   1

   
   优先使用第二种，union太耗时间了。

6. 增加分区
   创建单个分区

   alter table dept_partition add partition(day='20200404');
   1

   创建多个分区

   alter table dept_partition add partition(day='20200405') partition(day='20200406');
   1

7. 删除分区
   删除单个分区

   alter table dept_partition drop partition (day='20200406');
   1

   删除多个分区

   alter table dept_partition drop partition (day='20200404'), partition(day='20200405');
   1

8. 查看分区表有多少分区

   show partitions dept_partition;
   1

   

9. 查看分区表结构

   desc formatted dept_partition;
   1

4.2 分区表----二级分区

思考: 如何一天的日志数据量也很大，如何再将数据拆分?

1. 创建二级分区表

create table dept_partition2( deptno int, dname string, loc string)
partitioned by (day string, hour string)
1
2

2. 导入数据

load data local inpath '/opt/module/hive-3.1.2/test_data/dept_20200401.log' into table dept_partition2 partition(day='20200401', hour='12');
load data local inpath '/opt/module/hive-3.1.2/test_data/dept_20200402.log' into table dept_partition2 partition(day='20200401', hour='13');
load data local inpath '/opt/module/hive-3.1.2/test_data/dept_20200403.log' into table dept_partition2 partition(day='20200403', hour='12');
1
2
3

3. 把数据直接上传到分区目录上，让分区表和数据产生关联的三种方式

通过 hadoop fs 命令，创建分区目录和上传数据的分区数据，是不能被查到的。普通表可以查询到，而分区表中元数据比较的复杂（分区目录、二级分区目录），hadoop fs 命令并不会对元数据进行更新，自然也找不到该分区了。

• 上传数据后 修复上传数据

  根据HDFS的目录信息，补充mysql的元数据的分区信息。

  msck repair table dept_partition2; 
  1

• 上传数据后 添加分区上传数据

  alter table dept_partition2 add partition(day='201709',hour='14');
  1

• 【创建文件夹后】 load数据到分区创建目录

  load data local inpath '/opt/module/hive/datas/dept_20200401.log' into table dept_partition2 partition(day='20200401',hour='15');
  1

4. load数据时不指定分区
   不指定分区，不写partition

load data local inpath '/opt/module/hive/datas/dept_20200401.log' into table dept_partition2;
1

可能出现现象：MR可以运行，但中途报错

原因：不指定分区时，将会走MR，而不是本地载入数据。当任务被另一台主机分配到时，要导入的数据只有本地有，那台机子没有，所以会报错。之前load成功，是因为一直没有走MR

此时，将数据进行分发，再进行load，成功，且自动生成一个又臭又硬的分区目录

4.3 动态分区调整

参数设置

1. 开启动态分区功能（默认 true，开启）

   hive.exec.dynamic.partition=true 
   1

2. 设置为非严格模式（动态分区的模式，默认 strict，表示必须指定至少一个分区为 静态分区，nonstrict 模式表示允许所有的分区字段都可以使用动态分区。）该参数需要根据实际 的数据来设定。比如：源数据中包含了一年的数据，即 day 字段有 365 个值，那么该参数就需要设置成大于 365，如果使用默认值 100，则会报错。

   hive.exec.dynamic.partition.mode=nonstrict 
   1

3. 在所有执行 MR 的节点上，最大一共可以创建多少个动态分区。默认 1000

   hive.exec.max.dynamic.partitions=1000
   1

4. 在每个执行 MR 的节点上，最大可以创建多少个动态分区。

   hive.exec.max.dynamic.partitions.pernode=100 
   1

5. 整个 MR Job 中，最大可以创建多少个 HDFS 文件。默认 100000

   hive.exec.max.created.files=100000 
   1

6. 当有空分区生成时，是否抛出异常。一般不需要设置。默认 false

   hive.error.on.empty.partition=false
   1

一般情况，只要设置 非严格模式 一个参数即可

需求：将 dept 表中的数据按照地区（loc 字段），插入到目标表 dept_partition 的相应 分区中。

• 创建分区表

  create table dept_partition_dy(id int, name string) partitioned by (loc int) row format delimited fields terminated by '\t';
  1

• 设置为非严格模式

  set hive.exec.dynamic.partition.mode = nonstrict;
  1

• 导入

  insert into table dept_partition_dy partition(loc) select deptno, dname, loc from dept;
  1

4.4 分桶表 （极大数据集时）

分区提供一个隔离数据和优化查询的便利方式。不过，并非所有的数据集都可形成合理 的分区。对于一张表或者分区，Hive 可以进一步组织成桶，也就是更为细粒度的数据范围 划分。

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

load data inpath '/student.txt' into table stu_buck; 
1

分桶规则
Hive 的分桶采用对分桶字段的值进行哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中

5、函数

5.1 系统内置函数

1）查看系统自带的函数

show functions;
1

2）显示自带的函数的用法

desc function min; 
1

3）详细显示自带的函数的用法

desc function extended max;
1

4）函数的分类

1. udf:一进一出 普通函数 -------------》可以任意嵌套
2. udaf:多进一出 聚合函数
3. udtf:一进多出 炸裂函数

5.2 常用内置函数

5.1 空字段赋值

• 函数说明
  NVL：给值为 NULL 的数据赋值，它的格式是 NVL( value，default_value)。它的功能是如果 value 为NULL，则 NVL 函数返回 default_value 的值，否则返回 value 的值，如果两个参数 都为 NULL ，则返回 NULL。

• 查询：如果员工的 comm 为 NULL，则用-1 代替

select comm,nvl(comm, -1) from emp;
1

• 查询：如果员工的 comm 为 NULL，则用对应领导 id 代替

select comm, mgr, nvl(comm,mgr) from emp; 
1

5.2 case when then else end

Case …（字段） when …（字段值） then … else … end
用于分支处理

• 数据准备

  悟空	A	男
  大海	A	男
  宋宋	B	男
  凤姐	A	女
  婷姐	B	女
  婷婷	B	女
  1
  2
  3
  4
  5
  6

• 建表和数据导入

  create table emp_sex( name string,dept_id string, sex string)
  row format delimited fields terminated by "\t";
  load data local inpath '/opt/module/hive-3.1.2/test_data/emp_sex.txt' into table emp_sex;
  1
  2
  3

• 需求：求出不同部门男女各多少人。

• 查询数据

  select dept_id,
  sum(case sex when '男' then 1 else 0 end) count_man,
  sum(case sex when '女' then 1 else 0 end) count_men
  from emp_sex
  group by dept_id;
  1
  2
  3
  4
  5

  结果
  

5.3 行转列

• 1、concat(str1 / col, str2 B / col…)
  多个str，连接在一起形成一个str；或将多个列的同一行的数据连接成一个str。

  注：string、int、double都可以

  desc function concat;
  1

  
  eg：

  select concat(empno,'--',ename) from emp;
  1

  

• 2、concat_ws(separator, str1, str2, …)
  实际上是concat的一个特殊形式，separator表示每个str之间的分隔符。

  只能是string 或 array(string)

  
  eg：

  	      select concat_ws('****', ename, job ) from emp;
  1

  

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

  注：collect_set去重；collect_list不去重

• 数据准备

  孙悟空	白羊座	A
  大海	射手座	A
  宋宋	白羊座	B
  猪八戒	白羊座	A
  凤姐	射手座	A
  花老师	白羊座	B
  1
  2
  3
  4
  5
  6

• 建表和数据导入

  create table person_info( name string, constellation string, blood_type string)
  row format delimited fields terminated by "\t";
  load data local inpath "/opt/module/hive-3.1.2/test_data/person_info.txt" into table person_info;
  1
  2
  3

• 需求：把星座和血型一样的人归类到一起。

• 查询数据

   select a,concat_ws('|', collect_list(name)) from 
   (select concat_ws('--', constellation, blood_type)as a, name from person_info) 
   t1 group by a;
  1
  2
  3

总结：行转列，看起来就像是 将多列变成一列，也就相当于把同一行变成了一列（尤其是concat[_ws]很明显）。

5.4 列转行

• explode(col)
  将 hive 一列中复杂的 Array 或者 Map 结构拆分成多行。

• lateral view
  用于和 split, explode 等 UDTF 一起使用，它能够将一列数据拆成多行数据。在此基础上可以对拆分后的数据进行聚合；（将explode炸裂出的数据和原数据进行关联）！！！！

• 数据准备

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

• 创建表和数据导入

  create table movie_info( movie string, category string)
  row format delimited fields terminated by "\t";
  load data local inpath "/opt/module/hive-3.1.2/test_data/movie.txt" into table movie_info;
  1
  2
  3

• 需求
  

• 查询数据

  select
  movie, category_name
  from movie_info
  lateral view
  explode(split(category,",")) movie_info_tmp AS category_name;
  1
  2
  3
  4
  5

  解释：movie_info_tmp AS category_name 中，as前面是侧写表别名，后面是炸裂出字段别名；as不能少
  

5.5 窗口函数（开窗函数）

函数说明

1. subtring（str, pos[, len] ）取一个str的一部分。pos表示起点，len表示长度；不写长度表示从起点读到最后；pos 的 0 或 1 都表示第一个位置。

2. over() ，指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化。 配合sum()、count()、lag()、lead()、ntile()等函数一起使用

   over()函数中参数介绍

   参数	说明
   current row	当前行
   n preceding	往前n行数据
   n following	往后n行数据
   unbounded	表示起点，unbounded preceding表示从前面的起点，unbounded following 表示到后面的终点
   lag(col,n,default)	获取往前第n行数据 ，配合over()使用
   lead(col,n)	获取往后第n行数据 ，配合over()使用
   ntile(n)	把有序窗口的行分发到指定数据的组中，各个组有编号，每行会返回一个编号，配合over()使用

   over() 函数 的基本使用案例
   over ( [partition by col1, col2, … ] [order by col [rows between …unbounded and …unbounded] ])
   
   注意：

   • partition by 用于设置整个窗口的大小
   • order by 用于对每个窗口进行排序
   • rows between …unbound and …unbound 设置每行的窗口范围（必须和 order by 一起使用）
   • order by 默认后面接的是 rows between unbound preceding and unbound following
   • over()不加参数，默认所有行都在一个窗口中

• 准备数据

  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
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14

• 创建表和导入数据

  create table business(name string, orderdate string, cost int)
  ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
  load data local inpath "/opt/module/hive-3.1.2/test_data/business.txt" into table business;
  1
  2
  3

• 需求一
  查询在 2017 年 4 月份购买过的顾客及总人数

  over()

  不用over()窗口函数时，结果不符合

  select name,count(*)
  from business
  where substring(orderdate,1,7)="2017-04"
  group by name;
  1
  2
  3
  4

  

  使用over()窗口函数，结果符合

  select name, count(*) over()
  from business
  where substring(orderdate,1,7)='2017-04'
  group by name;
  1
  2
  3
  4

  

• 需求二
  查询顾客的购买明细及月购买总额

  over(parition by)

  先求顾客的购买明细及购买总额

  select name,orderdate,sum(cost) over(partition by name) from business;
  1

  
  顾客的购买明细及月购买总额

  select name,orderdate,sum(cost) over(partition by name, month(orderdate)) from business;
  1

  

总结： partition by( name, orderdate ) 对整个窗口作出一定的限制（窗口打开的范围）

• 需求三
  将每个顾客的 cost 按照日期进行累加

  over(partition by col1 order by col2 rows between … and … )

  select name,orderdate,sum(cost) over(partition by name order by orderdate) from business;
  1

  

  select name,orderdate,sum(cost) over(partition by name order by  orderdate rows between unbounded preceding and current row ) from business;
  
  1
  2

  

  两种方法效果一样，order by col 默认从 preceding 到 current

  每笔交易和相邻的前后两笔交易的cost

  select name,orderdate,sum(cost) over(partition by name order by  orderdate rows between 1 preceding and 1 following ) from business;
  1

  

总结： order by col 和 rows between … and … 结合起来用，十分的灵活。若排序时值一样，两行的窗口是一样大的，结果也会是一样。

• 需求四
  查看顾客上次的购买时间

  lag(col,n,default)

  select name, orderdate, lag(orderdate,1,null) over(partition by name) from business;
  1

  

• 需求五
  查询前 20%时间的订单信息

  ntile(n)

  select name, orderdate, cost from 
  (select name, orderdate, cost, ntile(5) over(order by orderdate) groupId from business) t1
  where groupId=1;
  1
  2
  3

  

5.6 RANK函数

• rank() 排序相同时会重复，总数不会变
• dense_rank() 排序相同时会重复，总数会减少
• row_number() 会根据顺序计算

后面必须接over()一起使用。

• 准备数据

  孙悟空	语文	87
  孙悟空	数学	95
  孙悟空	英语	68
  大海	语文	94
  大海	数学	56
  大海	英语	84
  宋宋	语文	64
  宋宋	数学	86
  宋宋	英语	84
  婷婷	语文	65
  婷婷	数学	85
  婷婷	英语	78
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12

• 创建表和导入数据

  create table score(name string, subject string, score int)
  row format delimited fields terminated by "\t"; 
  load data local inpath '/opt/module/hive-3.1.2/test_data/score.txt' into table score;
  1
  2
  3

• 需求
  计算每门学科成绩排名。
  rank()

• 查询实现

  select name,subject,score, 
  rank() over(partition by subject order by score desc) rp,
  dense_rank() over(partition by subject order by score desc) drp,
  row_number() over(partition by subject order by score desc) rnp
  from score;
  1
  2
  3
  4
  5

  

5.7 其他常用函数

1. unix_timestamp:返回当前或指定时间的时间戳

select unix_timestamp();
select unix_timestamp("2020-10-28",'yyyy-MM-dd');
1
2

2. from_unixtime：将时间戳转为日期格式

select from_unixtime(1603843200);
1

3. current_date：当前日期

select current_date;
1

4. current_timestamp：当前的日期加时间

select current_timestamp;
1

5. to_date：抽取日期部分

select to_date('2020-10-28 12:12:12');
1

6. 获取年、月、日、时、分秒

select year('2020-10-28 12:12:12');
select month('2020-10-28 12:12:12');
select day('2020-10-28 12:12:12');
select hour('2020-10-28 12:12:12');
select minute('2020-10-28 12:12:12');
select second('2020-10-28 12:12:12');
1
2
3
4
5
6

7. weekofyear：当前时间是一年中的第几周

select weekofyear('2020-10-28 12:12:12');
1

7. dayofmonth：当前时间是一个月中的第几天

select dayofmonth('2020-10-28 12:12:12');
1

8. months_between： 两个日期间的月份

select months_between('2020-04-01','2020-10-28');
1

9. add_months：日期加减月

select add_months('2020-10-28',-3);
1

10. datediff：两个日期相差的天数

select datediff('2020-11-04','2020-10-28');
1

11. date_add：日期加天数

select date_add('2020-10-28',4);
1

12. date_sub：日期减天数

date_sub：日期减天数
select date_sub('2020-10-28',-4);
1
2

13. last_day：日期的当月的最后一天

select last_day('2020-02-30');
1

14. date_format(): 格式化日期

select date_format('2020-10-28 12:12:12','yyyy/MM/dd HH:mm:ss');
1

15. 常用取整函数

round： 四舍五入
select round(3.14);
select round(3.54);

ceil：  向上取整
select ceil(3.14);
select ceil(3.54);

floor： 向下取整
select floor(3.14);
select floor(3.54);
1
2
3
4
5
6
7
8
9
10
11

16. 常用字符串操作函数

upper： 转大写
select upper('low');

lower： 转小写
select lower('low');

length： 长度
select length("atguigu");

trim：  前后去空格
select trim(" atguigu ");

lpad： 向左补齐，到指定长度
select lpad('atguigu',9,'g');

rpad：  向右补齐，到指定长度
select rpad('atguigu',9,'g');

regexp_replace：使用正则表达式匹配目标字符串，匹配成功后替换！
SELECT regexp_replace('2020/10/25', '/', '-');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

17. 集合操作

size： 集合中元素的个数
select size(friends) from test3;

map_keys： 返回map中的key
select map_keys(children) from test3;

map_values: 返回map中的value
select map_values(children) from test3;

array_contains: 判断array中是否包含某个元素
select array_contains(friends,'bingbing') from test3;

sort_array： 将array中的元素排序
select sort_array(friends) from test3;

grouping_set:多维分析 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

6、实战

• 数据简介
  

• 需求
  

• 创建表和数据导入

  创建原始数据表

  gulivideo_ori

  create table gulivideo_ori(
  videoId string, uploader string, age int,
  category array<string>, length int,
  views int, rate float, ratings int, comments int,
  relatedId array<string>)
  row format delimited fields terminated by "\t"
  collection items terminated by "&"
  stored as textfile;
  1
  2
  3
  4
  5
  6
  7
  8

  gulivideo_user_ori

  create table gulivideo_user_ori( uploader string,
  videos int, friends int)
  row format delimited fields terminated by "\t" 
  stored as textfile;
  1
  2
  3
  4

  创建 orc 存储格式带 snappy 压缩的表

  gulivideo_orc

  create table gulivideo_orc( videoId string, uploader string,
  age int,
  category array<string>, length int,
  views int, rate float, ratings int, comments int,
  relatedId array<string>)
  stored as orc
  tblproperties("orc.compress"="SNAPPY");
  1
  2
  3
  4
  5
  6
  7

  gulivideo_user_orc

  create table gulivideo_user_orc( uploader string,
  videos int, friends int)
  row format delimited fields terminated by "\t"
  stored as orc
  tblproperties("orc.compress"="SNAPPY");
  1
  2
  3
  4
  5

  导入数据

  load data local inpath '/opt/module/hive-3.1.2/test_data/video' into table gulivideo_ori;
  load data local inpath '/opt/module/hive-3.1.2/test_data/user.txt' into table gulivideo_user_ori;
  1
  2

  insert into table gulivideo_orc select * from gulivideo_ori;
  insert into table gulivideo_user_orc select * from gulivideo_user_ori;
  1
  2

• 查询
  

  select videoId, views from gulivideo_orc order by desc views limit 10;
  1

  

  

  select name, count(name) hot
  from (select name from gulivideo_orc lateral view explode(category) gulivideo_orc_tmp as name) t1
  group by name order by hot desc limit 10;
  1
  2
  3

  

  

  select videoId, category from gulivideo_orc order by views desc limit 20;
  
  //注意*******：order by 在select之后，order by要使用views进行排序，所以select中要有views
  select cate, count(cate) count_Top20 from (select videoId, cate 
  from (select videoId, category,views from gulivideo_orc order by views desc limit 20) 
  t1 lateral view explode(category) category_tmp as cate)
  t2 group by cate order by count_Top20;
  
  1
  2
  3
  4
  5
  6
  7
  8

  

  

  //1、找出Top50视频的类别数组
  select category,views from gulivideo_orc order by views desc limit 50;
  
  //2、炸裂类别数组、分组、排序
  select cate,count(cate) hot from () t1 lateral view explode(category) t1_tmp as cate group by cate order by hot desc;
  
  
  select cate,count(cate) hot from (select category,views from gulivideo_orc order by views desc limit 50)  t1 lateral view explode(category) t1_tmp as cate group by cate order by hot desc;
  1
  2
  3
  4
  5
  6
  7
  8

  

  

  //炸裂category、得到music对应的视频和views进行排序
  select videoId, cate,views from gulivideo_orc lateral view explode(category) t1_tmp as cate where cate='Music' order by views desc limit 10;
  1
  2

  

  

  //1、炸裂category数组每种cate局部开窗排序、rank()得到排序序号
  select cate,videoId,views from gulivideo_orc lateral view explode(category) t1_tmp as cate;
  
  //2、每种cate局部开窗排序、rank()得到排序序号、利用排序序号
  select cate,videoId,views,rank() over(partition by cate order by views desc) rk 
  from (select cate,videoId,views from gulivideo_orc lateral view explode(category) t1_tmp as cate) t1;
  
  //3、取TOP10   注意******：不能在用rank得到的rk进行when过滤
  select cate,videoId,views,rk
  from(select cate,videoId,views,rank() over(partition by cate order by views desc) rk
  from (select cate,videoId,views from gulivideo_orc lateral view explode(category) t1_tmp as cate)
   t1 ) t2
  where rk<=10;
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13

  

  

  //1、获取前上传视频前10的用户
  select uploader from gulivideo_user_orc order by videos desc limit 10;
  
  //2、Joingulivideo_orc表，获取前10用户上传的所有视频
  select uploader, videoId, views
  from () t1 
  join gulivideo_orc t2 on t1.uploader = t2.uploader;
  
  //3、排名
  select uploader, videoId, views, rank() over(partition by uploader order by views desc) rk
  from (select t1.uploader, videoId, views
  from (select uploader from gulivideo_user_orc order by videos desc limit 10) t1
  join gulivideo_orc g on t1.uploader = g.uploader) t2;
  
  //4
  select uploader, videoId, views,rk
  from (select uploader, videoId, views, rank() over(partition by uploader order by views desc) rk
  from (select t1.uploader, videoId, views
  from (select uploader from gulivideo_user_orc order by videos desc limit 10) t1
  join gulivideo_orc g on t1.uploader = g.uploader) t2) t3
  where rk<=20;
  
  
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19
  20
  21
  22
  23