hive3与hive1语法 hive语法

HQL语法详解

• 一、DDL 数据定义

• 1、创建数据库
• 2、查询数据库

• （1）查看当前正在使用的数据库
• （2）显示全部数据库
• （3）过滤显示数据库列表
• （4）显示数据库信息
• （5）显示数据库详细信息（extended）

• 3、切换数据库
• 4、修改数据库
• 5、删除数据库
• 6、创建表

• （1）建表语法
• （2）管理表（内部表）与外部表
• （3）管理表（内部表）与外部表转换
• （4）复制表结构创建表
• （5）复制表结构与数据创建表（as select）

• 2、查看系统中的表
• 3、查看表结构
• 4、查看表详情信息（表字段、表类型、表位置、表输入输出类型）
• 5、修改表

• （1）重命名表
• （2）增加/修改/替换列信息
• （3）修改字段分隔符

• 6、清除表中数据
• 7、删除表

• 二、DML 数据操作

• 1、数据导入

• （1）直接将数据put到表目录下
• （2） 向表中装载数据（Load）
• （3） 通过查询语句向表中插入数据（Insert）
• （4） 查询语句创建表并加载数据（As Select）
• （5） 创建表时通过 Location 指定加载数据路径（表位置）
• （6） Import 将文件数据导入到指定 Hive 表中

• 2、数据导出

• （1）Insert导出
• （2）Hadoop命令导出到本地
• （3）hive Shell 命令导出
• （4） Export导出到HDFS上
• （5）导出工具 sqoop/dataX

• 3、查询语句

• （1）查询语句执行流程
• （2）常用聚合函数
• （3）Limit 语句
• （4）比较运算符
• （5）Like和RLike
• （6）分组group by
• （7）having

• 4、表的别名，列的别名
• 5、join语句

• （1）内连接 join
• （2）左外连接 left join
• （3）右外连接 right join
• （4）满外连接 full join
• （5）左半连接 left semi join
• （6）笛卡尔积
• （7）union
• （8）union all

• 6、with as 的使用

• 三、排序

• 1、全局排序（Order By）
• 2、每个 Reduce 内部排序（Sort By）
• 3、分区（Distribute By）
• 3、Cluster By 分区并排序

• 四、分区表

• 1、查看语句执行计划
• 2、一级静态分区表

• （1）创建一级静态分区表
• （2）加载数据到一级分区表中
• （3）加载数据时不指定分区
• （4）查询一级分区表中数据

• 3、对分区的增删查

• （1）增加分区
• （2）删除分区
• （3）查看分区表有多少分区
• （4）查看分区表结构

• 4、二级静态分区表

• （1）创建二级静态分区表
• （2）加载数据到二级分区表中
• （3）查询二级分区表中数据

• 5、静态分区表加载数据的几种方式

• （1）正常的加载数据（系统自己建分区目录）
• （2）自己手动创建分区目录

• 6、动态一级分区表
• （1）开启动态分区功能（默认 true，开启）
• （2）设置为非严格模式（动态分区的模式，默认 strict，表示必须指定至少一个分区为静态分区，nonstrict 模式表示允许所有的分区字段都可以使用动态分区。）
• （3）创建普通表 ，导入数据
• （4）创建分区表
• （5）导入数据到分区表中，并设置动态分区
• （6）导入数据时不写 partition （Hive3.0新增功能）
• （7）查看分区表的分区情况
• （8）动态分区表的缺点
• 7、动态二级分区表

• 五、分桶表

• 1、创建分桶表
• 2、查看表结构
• 3、导入数据到分桶表中

• （1）load 的方式
• （2）insert 方式

• 4、分桶规则
• 5、分桶表操作需要注意的事项
• 6、分桶抽样查询

一、DDL 数据定义

1、创建数据库

CREATE DATABASE [IF NOT EXISTS] database_name
[COMMENT database_comment]
[LOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value, ...)];

• [IF NOT EXISTS]：是否不存在，是的话创建。避免要创建的数据库已经存在错误
• [COMMENT database_comment]：表注释
• [LOCATION hdfs_path]：指定数据库在 HDFS 上存放的位置，默认存储路径是/user/hive/warehouse/*.db

2、查询数据库

（1）查看当前正在使用的数据库

hive> select current_database() ;

（2）显示全部数据库

hive> show databases;

（3）过滤显示数据库列表

hive> show databases like 'db_hive*';
OK
db_hive
db_hive_1

（4）显示数据库信息

hive> desc database db_hive;

（5）显示数据库详细信息（extended）

hive> desc database extended db_hive;

3、切换数据库

hive> use db_hive;

4、修改数据库

hive> alter database db_hive set dbproperties('createtime'='20170830');

5、删除数据库

删除空数据库

hive> drop database db_hive2;

强制删除非空数据库

hive> drop database db_hive cascade;

6、创建表

（1）建表语法

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
[(col_name data_type [COMMENT col_comment], ...)]
[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, ...)]
[AS select_statement]

字段说明

• CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；
  用户可以用 IF NOT EXISTS 选项来忽略这个异常。
• EXTERNAL 关键字可以让用户创建一个外部表，在建表的同时可以指定一个指向实
  际数据的路径（LOCATION），在删除表的时候，内部表的元数据和数据会被一起删除，而外
  部表只删除元数据，不删除数据。
• COMMENT：为表和列添加注释。
• PARTITIONED BY 创建分区表
• CLUSTERED BY 创建分桶表
• SORTED BY 不常用，对桶中的一个或多个列另外排序
• ROW FORMAT
  DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char]
  [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
  | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value,
  property_name=property_value, …)]
  用户在建表的时候可以自定义 SerDe 或者使用自带的 SerDe。如果没有指定 ROW
  FORMAT 或者 ROW FORMAT DELIMITED，将会使用自带的 SerDe。在建表的时候，用户还需
  要为表指定列，用户在指定表的列的同时也会指定自定义的 SerDe，Hive 通过 SerDe 确定表
  的具体的列的数据。
  SerDe 是 Serialize/Deserilize 的简称， hive 使用 Serde 进行行对象的序列与反序列化。
  row format delimited fields terminated by “分隔符”：指定文件是以什么分割成列的
• STORED AS 指定存储文件类型
  常用的存储文件类型：SEQUENCEFILE（二进制序列文件）、TEXTFILE（文本）、RCFILE（列
  式存储格式文件）
  如果文件数据是纯文本，可以使用STORED AS TEXTFILE。如果数据需要压缩，使用 STORED
  AS SEQUENCEFILE。
• LOCATION ：指定表在 HDFS 上的存储位置。
• AS：后跟查询语句，根据查询结果创建表。
• LIKE 允许用户复制现有的表结构，但是不复制数据。

（2）管理表（内部表）与外部表

默认创建的表使用的是ManagerTable ，都是所谓的管理表，有时也被称为内部表。因为这种表，Hive会（或多或少地）控制着数据的生命周期。Hive默认情况下会将这些表的数据存储在由配置项hive.metastore.warehouse.dir(例如，/user/hive/warehouse)所定义的目录的子目录下。 当我们删除一个管理表时，Hive也会删除这个表中数据（将表文件删除）。管理表不适合和其他工具共享数据。

创建表时用 external 修饰的表都是外部表。因为表是外部表，所以Hive并非认为其完全拥有这份数据。删除该表并不会删除掉这份数据，不过描述表的元数据信息会被删除掉。

CREATE [EXTERNAL] TABLE：有EXTERNAL即为外部表，没有即为内部表

无论是管理表还是外部表 不指定location就会在hive.metastore.warehouse.dir配置的hive工作目录下创建以自己的表名为名的工作目录，指定locaition后则以指定位置为工作目录

如果数据是共享数据，数据很重要， 建议使用外部表

管理表和外部表的使用场景

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

（3）管理表（内部表）与外部表转换

• 查询表的类型

hive (default)> desc formatted student2;
Table Type:       MANAGED_TABLE

• 修改内部表student2为外部表

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

• 查询表的类型

hive (default)> desc formatted student2;
Table Type:       EXTERNAL_TABLE

• 修改外部表student2为内部表

alter table student2 set tblproperties('EXTERNAL'='FALSE'); -- 要求KV的大小写

• 查询表的类型

hive (default)> desc formatted student2;
Table Type:       MANAGED_TABLE

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

（4）复制表结构创建表

create table tb_user2 like tb_user;

（5）复制表结构与数据创建表（as select）

create  table  tb_emp3  as  select eno , name , job from tb_emp ;

2、查看系统中的表

查看当前数据库的所有表

show  tables ;

查看当前数据库以tb开头的所有表

show tables like 'tb.*' 或 show tables like 'tb*';

查看指定数据库的表

show tables in  数据库名;  或者 show tables like '数据库名.*' ;

3、查看表结构

desc 表名

4、查看表详情信息（表字段、表类型、表位置、表输入输出类型）

desc formatted 表名

5、修改表

（1）重命名表

ALTER TABLE table_name RENAME TO new_table_name

（2）增加/修改/替换列信息

更新列

ALTER TABLE table_name CHANGE [COLUMN] column_old_name column_new_name 
column_type [COMMENT col_comment] [FIRST|AFTER column_name]

增加和替换列

ALTER TABLE table_name ADD|REPLACE COLUMNS (column_name data_type [COMMENT col_comment], ...)

注：ADD 是代表新增一字段，字段位置在所有列后面(partition 列前)，REPLACE 则是表示替换表中所有字段。

对于分区表来说，这样增加列只会对新分区生效，旧分区依旧无法更新新增的列。可以在新增字段时加上cascade关键字，cascade为“级联”，不仅变更新分区的表结构（metadata），同时也变更旧分区的表结构。

alter table  industry_db.product add columns(industry_id string comment ‘行业id) cascade;

如果已经执行添加操作，并且没有带cascade，可以尝试下面的方法：

1、使用replace 恢复表结构，这样历史的分区数据都不会消失
alter table industry_db.product replace columns(product_name string comment ‘产品名’);
2、然后再使用带cascade添加字段

（3）修改字段分隔符

alter table test01 set serdeproperties('field.delim'='\t');

6、清除表中数据

truncate table student;

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

7、删除表

drop table table_name;

二、DML 数据操作

1、数据导入

（1）直接将数据put到表目录下

hdfs dfs -put  user.txt  /user/hive/warehouse/db_doit25.db/tb_manage_user/b.txt
-- 将HDFS的文件移动到表目录下  cp  mv 
hdfs dfs -mv  /user.txt  /user/hive/warehouse/db_doit25.db/tb_manage_user/b.txt

在hive的客户端也提供了操作hdfs文件系统的命令

0: jdbc:hive2://linux01:10000> dfs  -ls  /user/hive/warehouse ;
+----------------------------------------------------+
|                     DFS Output                     |
+----------------------------------------------------+
| Found 4 items                                      |
| drwxrwxrwx   - root supergroup          0 2021-08-05 22:14 /user/hive/warehouse/db_doit25.db |
| drwxrwxrwx   - root supergroup          0 2021-08-05 03:18 /user/hive/warehouse/tb_demo1 |
| drwxrwxrwx   - root supergroup          0 2021-08-05 06:01 /user/hive/warehouse/tb_emp2 |
| drwxrwxrwx   - root supergroup          0 2021-08-05 06:03 /user/hive/warehouse/tb_emp3 |
+----------------------------------------------------+

（2） 向表中装载数据（Load）

hive> load data [local] inpath '数据的 path' [overwrite] into table 
tb_name [partition (partcol1=val1,…)];

• load data:表示加载数据
• local:表示从本地加载数据到 hive 表；否则从 HDFS 加载数据到 hive 表
  从本地load相当于将本地文件put到HDFS的Hive表文件夹下，本地文件还在；从HDFS上load则是将文件移动到了Hive表文件夹下（实际上只是修改NameNode存储的元数据信息，文件的磁盘位置并没有动），原来的文件就没有了
• inpath:表示加载数据的路径
• overwrite:表示覆盖表中已有数据（会将原表中所有数据全部删掉），否则表示追加
• into table:表示加载到哪张表
• student:表示具体的表
• partition:表示上传到指定分区

load与put的区别：load会修改元数据信息，put不会修改

（3） 通过查询语句向表中插入数据（Insert）

• 使用insert into values

insert into tb_orders  values('oid010','uid003',98),(oid011','uid002',38) ;

• 使用 insert into select
  将查询结果保存在已经存在的一张表中 增量保存处理结果

insert into tb_orders_back  select  * from tb_orders   where oid > 'oid003' ;

• 覆盖插入insert overwrite table tb_name，会覆盖表中已存在的数据
  覆盖插入注意要写table关键字

insert overwrite table tb_orders_back  select  * from tb_orders   where oid <= 'oid003';

insert 不支持插入部分字段，字段必须全且对应的上
底层就是MR程序写数据到表目录下，效率低，每次insert都会在HDFS中生成小文件, 影响性能 ! 不建议使用。HDFS每次生成小文件都要向NameNode插入元数据，会增加NameNode的压力。MR也不适合处理小文件，每个文件会生成一个MapTask

（4） 查询语句创建表并加载数据（As Select）

create  table  tb_emp3  as  select eno , name , job from tb_emp ;

（5） 创建表时通过 Location 指定加载数据路径（表位置）

hive (default)> create external table if not exists student5(
 id int, name string
 )
 row format delimited fields terminated by '\t'
 location '/student;

创建表，并指定表在 hdfs 上的位置。location 后面必须是Hdfs上的目录
Hive会自动加载目录中的文件，根据规定的分隔符（row format delimited fields terminated by ‘\t’）生成表数据

（6） Import 将文件数据导入到指定 Hive 表中

用于数据的备份和迁移 , 导入的数据必须是export导出的数据

-- 1导出数据
export table tb_orders to '/user/hive/warehouse/output/orders';
-- 2 建表
create table tb_orders_import  like tb_orders ;
-- 3 导入
import table tb_orders_import from '/user/hive/warehouse/output/orders';

2、数据导出

（1）Insert导出

将a表的数据导出到b表中

insert into b select  * from a; 
create table b as select * from a

将表数据导出到本地文件夹中

-- 将查询的结果保存在文件夹中
insert overwrite local directory '/data/output'
select * from tb_orders;

导出的数据默认使用隐藏分隔符 , 可以自己指定数据属性的分隔符

insert overwrite local directory '/data/output2'
row format delimited fields terminated by ',' 
select * from tb_orders;

insert overwrite local directory '/data/output3'
row format delimited fields terminated by '-' 
select * from tb_orders;

可以将数据导出到HDFS上(没有 local)

insert overwrite  directory '/data/output3'
row format delimited fields terminated by '-' 
select * from tb_orders;

（2）Hadoop命令导出到本地

hive (default)> dfs -get /user/hive/warehouse/student/student.txt
/opt/apps/data/export/student3.txt;

（3）hive Shell 命令导出

基本语法：（hive -e/-f 执行语句或者脚本 > file）

[root@linux1 hive]$ bin/hive -e 'select * from default.student;' >
/opt/apps/hive/data/export/student4.txt;

（4） Export导出到HDFS上

(defahiveult)> export table default.student 
to '/user/hive/warehouse/export/student';

（5）导出工具 sqoop/dataX

数据迁移工具 结构化数据的迁移

3、查询语句

在Hive中，单表简单查询语句如select * from Table;和 select * from Table limit n; 不产生MR任务。若是涉及到分组或排序，才会走MR

（1）查询语句执行流程

select
*        -- 4
from
tb_name  -- 1
where    -- 2
group by -- 3
having   -- 5
order by -- 6
limit    -- 7

（2）常用聚合函数

• count：求总行
• max：求最大值
• min：求最小值
• sum：求总和
• avg：求平均值

（3）Limit 语句

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

• 取前5条
  hive (default)> select * from emp limit 5;
• 参数一 起始的行数 0开始计数 参数2 取多少条
  hive (default)> select * from emp limit 2, 5;

（4）比较运算符

操作符	支持的数据类型	描述
A=B	基本数据类型	如果A等于B则返回TRUE，反之返回FALSE
A<=>B	基本数据类型	如果A和B都为NULL，则返回TRUE，其他的和等号（=）操作符的结果一致，如果任一为NULL则结果为NULL
A<>B, A!=B	基本数据类型	A或者B为NULL则返回NULL；如果A不等于B，则返回TRUE，反之返回FALSE
A<B	基本数据类型	A或者B为NULL，则返回NULL；如果A小于B，则返回TRUE，反之返回FALSE
A<=B	基本数据类型	A或者B为NULL，则返回NULL；如果A小于等于B，则返回TRUE，反之返回FALSE
A>B	基本数据类型	A或者B为NULL，则返回NULL；如果A大于B，则返回TRUE，反之返回FALSE
A>=B	基本数据类型	A或者B为NULL，则返回NULL；如果A大于等于B，则返回TRUE，反之返回FALSE
A [NOT] BETWEEN B AND C	基本数据类型	如果A，B或者C任一为NULL，则结果为NULL。如果A的值大于等于B而且小于或等于C，则结果为TRUE，反之为FALSE。如果使用NOT关键字则可达到相反的效果。
A IS NULL	所有数据类型	如果A等于NULL，则返回TRUE，反之返回FALSE
A IS NOT NULL	所有数据类型	如果A不等于NULL，则返回TRUE，反之返回FALSE
IN(数值1, 数值2)	所有数据类型	使用 IN运算显示列表中的值
A [NOT] LIKE B	STRING 类型	B是一个SQL下的简单正则表达式，如果A与其匹配的话，则返回TRUE；反之返回FALSE。B的表达式说明如下：‘x%’表示A必须以字母‘x’开头，‘%x’表示A必须以字母’x’结尾，而‘%x%’表示A包含有字母’x’,可以位于开头，结尾或者字符串中间。如果使用NOT关键字则可达到相反的效果。
A RLIKE B, A REGEXP B	STRING 类型	B是一个正则表达式，如果A与其匹配，则返回TRUE；反之返回FALSE。匹配使用的是JDK中的正则表达式接口实现的，因为正则也依据其中的规则。例如，正则表达式必须和整个字符串A相匹配，而不是只需与其字符串匹配。

（5）Like和RLike

• 使用LIKE运算匹配类似的值，% 任意个任意字符。_ 代表一个任意字符。
• RLIKE子句是Hive中这个功能的一个扩展，其可以通过Java的正则表达式这个更强大的语言来指定匹配条件

（6）分组group by

GROUP BY 语句通常会和聚合函数一起使用，按照一个或者多个列队结果进行分组，然后对每个组执行聚合操作。
根据哪几个字段分组，查询字段就只能是这几个字段和聚合函数。

（7）having

having与where不同点

• where针对表中的列发挥作用，查询数据；having针对查询结果中的列发挥作用，筛选数据。
• where后面不能写分组函数，而having后面可以使用分组函数。
• having只用于group by分组统计语句。

4、表的别名，列的别名

紧跟列名、表名，也可以在列名 / 表名和别名之间加入关键字‘AS’

好处

• 使用别名可以简化查询。
• 使用表名前缀可以提高执行效率

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

注意：别名如果想起成中文，需要用 ` ` 包裹

select id as `男` from tb_user;

5、join语句

在hive 2以后的版本支持join不等值连接，2版本以前不支持

（1）内连接 join

内连接：只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。

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

（2）左外连接 left join

左外连接：JOIN 操作符左边表中符合 WHERE 子句的所有记录将会被返回。右表返回符合连接条件的数据，其余用null

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

（3）右外连接 right join

右外连接：JOIN 操作符右边表中符合 WHERE 子句的所有记录将会被返回。左表返回符合连接条件的数据，其余用null

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

（4）满外连接 full join

满外连接：将会返回所有表中符合 WHERE 语句条件的所有记录。如果任一表的指定字
段没有符合条件的值的话，那么就使用 NULL 值替代。

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

（5）左半连接 left semi join

left semi join 只传递表的 join key 给 map 阶段，因此left semi join 中最后 select 的结果只出现左表的那些列。

left semi join 遇到右表重复记录，左表会跳过，而 join 则会一直遍历。这就导致右表有重复值的情况下 left semi join 只产生一条，join 会产生多条，也会导致 left semi join 的性能更高

select * from emp e left semi join 
dept d on e.deptno = d.deptno;

（6）笛卡尔积

笛卡尔集会在下面条件下产生

• 省略连接条件
• 连接条件无效
• 所有表中的所有行互相连接

（7）union

将两次查询结果拼接并去重
sql1 union sql2

（8）union all

将两次查询结果拼接，不会去重
sql1 union all sql2
sql1查回M行，sql2查回N行，返回M+N行

注意：sql1与sql2要想使用union拼接查询必须保证两个子句查询的字段数量一致，否则会报错

两次查询的字段数量一致，但字段不一样时，返回的字段名以前面的sql为准，但内容却是各自查询的东西

6、with as 的使用

with as短语，也叫做子查询部分(subquery factoring)，可以将一次查询结果存储在一个临时表空间中。有的时候，是为了让sql语句的可读性更高些，也有可能是在union all的不同部分，作为提供数据的部分。

WITH语句的优点:

• SQL可读性增强。比如对于特定with子查询取个有意义的名字等。
• with子查询只执行一次，将结果存储在用户临时表空间中，可以引用多次，增强性能。

WITH语句的用法:

• 创建一个临时表tmp

with tmp as (select * from tb_name)

• 创建多个临时表，中间用逗号隔开

with
tmp as (select * from tb_name),
tmp2 as (select * from tb_name2),
tmp3 as (select * from tb_name3),
…

三、排序

1、全局排序（Order By）

Order By：全局排序，只有一个 Reducer。ORDER BY 子句在 SELECT 语句的结尾

• Order By 字段 ASC（ascend）: 升序（默认）
• Order By 字段 DESC（descend）: 降序

查询员工信息按工资升序排列

hive (default)> select * from emp order by sal;

查询员工信息按工资降序排列

hive (default)> select * from emp order by sal desc;

2、每个 Reduce 内部排序（Sort By）

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

Sort by 为每个 reducer 产生一个排序文件。每个 Reducer 内部进行排序，对全局结果集来说不是排序。

需要设置reduce个数，否则还是一个reduce

• 设置 reduce 个数

hive (default)> set mapreduce.job.reduces=3;

• 查看设置的 reduce 个数

hive (default)> set mapreduce.job.reduces;

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

hive (default)> select * from emp sort by deptno desc;

• 将查询结果导入到文件中（按照部门编号降序排序）

hive (default)> insert overwrite local directory 
'/opt/module/data/sortby-result'
select * from emp sort by deptno desc;

3、分区（Distribute By）

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

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

distribute by只是设置根据某个字段分区（字段值的hash值%reduce个数），必须得手动设置reduce个数，否则只是在一个reduce里，没有分区效果。

在实际生产环境中，需要避免全局排序，因为数据量特别大，全局排序会耗费系统资源，最好先分区在排序。
如查最高3个数据，可以先分三个区排序，再在每个区拿最高的3个数据（极限情况下，可能最高的3个数据都在一个区中），再对这9个数据排序，拿最高3个

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

hive (default)> set mapreduce.job.reduces=3;
hive (default)> insert overwrite local directory 
'/opt/module/data/distribute-result' select * from emp distribute by 
deptno sort by empno desc;

注意，Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前

3、Cluster By 分区并排序

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

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

• 以下两种写法等价

hive (default)> select * from emp cluster by deptno;
hive (default)> select * from emp distribute by deptno sort by deptno;

四、分区表

分区表是将表根据某个查询维度分为多个文件夹。分区表的每个分区实际上对应表目录下的每个文件夹，相当于将表文件夹里的数据又根据分区规则分到对应的文件夹里。在查询时通过 WHERE 子句中的表达式扫描查询所需要的指定的分区，不再需要扫描全表，这样的查询效率会提高很多

分区表的建表语句是一样的，是静态分区还是动态分区由导入数据的不同方式而定

1、查看语句执行计划

通过查看语句的执行计划，可以比对出分区表与非分区表在sql执行上的不同

explain sql语句

加上关键字extended,则显示抽象语法树

explain extended sql语句

2、一级静态分区表

（1）创建一级静态分区表

create table dept_partition(
deptno int, dname string, loc string
)
partitioned by (day string) --定义分区字段名
row format delimited fields terminated by '\t';

注意：分区字段不能是表中已经存在的数据，可以将分区字段看作表的伪列。

（2）加载数据到一级分区表中

load data local inpath 
'/opt/module/hive/datas/dept_20200401.log' into table dept_partition 
partition(day='20200401');

这样就会在 dept_partition 表目录下创建一个 day=‘20200401’ 分区文件夹，并把数据文件加载到分区文件夹里

（3）加载数据时不指定分区

若在加载数据时不指定分区，则数据会被加载到一个默认分区内

load data local inpath '/a/a.txt' into table tb_partition; #数据被加载到下图分区

在加载本地数据时，若数据只在本地服务器上，而不在HDFS上，则有可能报错。报错是因为Hive生成的MapReduce程序是运行在Yarn上的，Yarn可能会把任务分配给其他服务器，其他服务器加载不到本地服务器的数据，所以报错。
可以通过set hive.exec.mode.local.auto=true;设置Hive为本地模式运行

（4）查询一级分区表中数据

select * from dept_partition where day='20200401';

3、对分区的增删查

（1）增加分区

• 创建单个分区

hive (default)> alter table dept_partition add partition(day='20200404');

创建分区并指定分区文件路径

hive (default)> alter table dept_partition add partition(day='20200404') location '/ss/aa';

• 同时创建多个分区，分区中间用空格隔开

hive (default)> alter table dept_partition add partition(day='20200405') 
partition(day='20200406');

（2）删除分区

• 删除单个分区

hive (default)> alter table dept_partition drop partition (day='20200406');

• 同时删除多个分区，分区之间用逗号隔开

hive (default)> alter table dept_partition drop partition 
(day='20200404'), partition(day='20200405');

（3）查看分区表有多少分区

hive> show partitions dept_partition;

（4）查看分区表结构

hive> desc formatted dept_partition;

4、二级静态分区表

（1）创建二级静态分区表

create table dept_partition2(
 deptno int, dname string, loc string
 )
 partitioned by (day string, hour string)
 row format delimited fields terminated by '\t';

注意：分区字段不能是表中已经存在的数据，可以将分区字段看作表的伪列。

（2）加载数据到二级分区表中

load data local inpath 
'/opt/module/hive/datas/dept_20200401.log' into table dept_partition 
partition(day='20200401');

这样就会在 dept_partition 表目录下创建一个 day=‘20200401’ 分区文件夹，并把数据文件加载到分区文件夹里

（3）查询二级分区表中数据

select * from dept_partition2 where day='20200401' and 
hour='12';

5、静态分区表加载数据的几种方式

（1）正常的加载数据（系统自己建分区目录）

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

（2）自己手动创建分区目录

dfs -mkdir -p
/user/hive/warehouse/mydb.db/dept_partition2/day=20200401/hour=13;

dfs -mkdir -p
/user/hive/warehouse/mydb.db/dept_partition2/day=20200401/hour=14;

dfs -mkdir -p
/user/hive/warehouse/mydb.db/dept_partition2/day=20200401/hour=15;

此时元数据中没有记录分区信息，有三种方式可以让分区表和数据产生关联

• 上传数据后修复

#上传数据
dfs -put /opt/module/datas/dept_20200401.log 
/user/hive/warehouse/mydb.db/dept_partition2/day=20200401/hour=13;

#此时还查不到分区数据，可以执行修复分区信息
msck repair table dept_partition2;

• 上传数据后添加分区

#上传数据
dfs -put /opt/module/hive/datas/dept_20200401.log 
/user/hive/warehouse/mydb.db/dept_partition2/day=20200401/hour=14;

#此时还查不到分区数据，可以执行添加分区
alter table dept_partition2 add 
partition(day='20200401',hour='14');

• load 数据到分区

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

原因：load会修改元数据信息，put不会修改，只要元数据关联上，Hive就会自己加载文件

6、动态一级分区表

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

set hive.exec.dynamic.partition=true

（2）设置为非严格模式（动态分区的模式，默认 strict，表示必须指定至少一个分区为静态分区，nonstrict 模式表示允许所有的分区字段都可以使用动态分区。）

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

（3）创建普通表 ，导入数据

drop table tb_dynamic_partition_source ;
create table tb_dynamic_partition_source(
    id string ,
    name string ,
    city string 
)
row format delimited fields terminated by ','  ;
load data local inpath '/data/city.txt' into table  tb_dynamic_partition_source ;

（4）创建分区表

create table tb_dynamic_partition_demo(
    id string ,
    name string ,
    city string 
)
partitioned by (ct string) 
row format delimited fields terminated by ','

分区字段不能是表中已经存在的数据。也可以直接将某个表字段提出来当分区字段

（5）导入数据到分区表中，并设置动态分区

insert  into  table  tb_dynamic_partition_demo partition(ct)
select id , name ,city , city as  ct1 from tb_dynamic_partition_source ;

注意：select语句查询的字段必须和分区表的字段对应的上，分区表会将分区字段作为最后一个字段（伪列），所以要做动态分区的那个字段必须放最后，字段名可以不和 partition 定义的字段名一致，因为此处只是导数据。

动态分区表建表和导数据时，partition后括号里的字段名必须一样

（6）导入数据时不写 partition （Hive3.0新增功能）

导数据时也可不写 partition 如：

insert into tb_partition select id,name,datetime,name as n from tb_partition1;

这种情况可以不设置为非严格模式，也是以最后一个字段作为动态分区字段。

（7）查看分区表的分区情况

show partitions tb_dynamic_partition_demo;

（8）动态分区表的缺点

动态分区缺点：容易生成大量分区，生成大量小文件

7、动态二级分区表

与动态一级分区表类似，只是倒数第二个字段为一级分区，倒数第一个字段为二级分区

五、分桶表

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

1、创建分桶表

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

2、查看表结构

hive (default)> desc formatted stu_buck;
Num Buckets: 4

3、导入数据到分桶表中

（1）load 的方式

hive (default)> load data inpath '/student.txt' 
into table stu_buck;

（2）insert 方式

hive(default)>insert into table stu_buck 
select * from student_insert;

4、分桶规则

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

5、分桶表操作需要注意的事项

• reduce 的个数设置为-1,让 Job 自行决定需要用多少个 reduce 或者将 reduce 的个数设置为大于等于分桶表的桶数
• 从 hdfs 中 load 数据到分桶表中，避免本地文件找不到问题
• 不要使用本地模式（本地模式只能有一个reduce）

6、分桶抽样查询

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

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

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

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

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

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