四、HQL操作之DDL命令
官方文档：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL
DDL（data definition language）： 主要的命令有CREATE、ALTER、DROP等
DDL 主要是用在定义、修改数据库对象的结构 或 数据类型
Hive的结构如下图所示
1. 数据库操作
- Hive 有一个默认的数据库default，在操作HQL时，如果不明确的指定要使用哪个库，则使用默认数据库
- Hive 的数据库名、表名均不区分大小写
- 名字不能使用数字开头
- 不能使用关键字，尽量不使用特殊符号
创建数据库语法
CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name[COMMENT database_comment][LOCATION hdfs_path][MANAGEDLOCATION hdfs_path][WITH DBPROPERTIES (property_name=property_value, ...)];
-- 创建数据库，在HDFS上存储路径为 /user/hive/warehouse/*.db（这个数据存储路径是通过hive-site.xml配置的）hive (default)> create database mydb;-- 查看数据库文件hive (default)> dfs -ls /user/hive/warehouse;-- 避免数据库已经存在时报错，使用 if not exists 进行判断【标准写法】hive (default)> create database if not exists mydb;-- 创建数据库。添加备注，指定数据库在存放位置hive (default)> create database if not exists mydb2> comment 'this is mydb2'> location '/user/hive/mydb2.db';
查看数据库
-- 查看所有数据库
show databases;
-- 查看数据库信息
desc database mydb2;
desc database extended mydb2;
describe database extended mydb2;
使用数据库
— 如果数据库不为空，使用 cascade 强制删除（慎用！！！） drop database databasename cascade;
<a name="piYVl"></a>
## 2. 建表语法
`create [external] table [IF NOT EXISTS] table_name`<br />`[(colName colType [comment 'comment'], ...)]`<br />`[comment table_comment]`<br />`[partitioned by (colName colType [comment col_comment], ...)]`<br />`[clustered BY (colName, colName, ...)`<br />`[sorted by (col_name [ASC|DESC], ...)] into num_buckets buckets]`<br />`[row format row_format]`<br />`[stored as file_format]`<br />`[LOCATION hdfs_path]`<br />`[TBLPROPERTIES (property_name=property_value, ...)]`<br />`[AS select_statement];`<br />`CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name`<br />`LIKE existing_table_or_view_name`<br />`[LOCATION hdfs_path];`
**注释：**
1. CREATE TABLE。按给定名称创建表，如果表已经存在则抛出异常。可使用if not exists 规避。
1. EXTERNAL关键字。创建外部表，否则创建的是内部表(管理表)。
删除内部表时，数据和表的定义同时被删除；<br />删除外部表时，仅仅删除了表的定义，数据保留；<br />**在生产环境中，多使用外部表**；
3. comment。表的注释
3. partition by。对表中数据进行分区，指定表的分区字段
3. clustered by。创建分桶表，指定分桶字段
3. sorted by。对桶中的一个或多个列排序，较少使用
3. 设置分隔符。
`ROW FORMAT DELIMITED`<br />`[FIELDS TERMINATED BY char]`<br />`[COLLECTION ITEMS TERMINATED BY char]`<br />`[MAP KEYS TERMINATED BY char]`<br />`[LINES TERMINATED BY char] | SERDE serde_name`<br />`[WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value,`<br />`...)]`
- 建表时可指定 SerDe 。如果没有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED，将会使用默认的SerDe。建表时还需要为表指定列，在指定列的同时也会指定自定义的 SerDe。Hive通过 SerDe 确定表的具体的列的数据。
- **SerDe是 Serialize/Deserilize 的简称， hive使用Serde进行行对象的序列与反序列化**
8. stored as SEQUENCEFILE|TEXTFILE|RCFILE。如果文件数据是纯文本，可以使用 STORED AS TEXTFILE（缺省）；如果数据需要压缩，使用 STORED AS SEQUENCEFILE（二进制序列文件）。
8. LOCATION。表在HDFS上的存放位置
8. TBLPROPERTIES。定义表的属性
8. **AS。后面可以接查询语句，表示根据后面的查询结果创建表（与like不同，既复制表又复制数据）**
8. ** LIKE。like 表名，允许用户复制现有的表结构，但是不复制数据**
<a name="uyd91"></a>
## 3. 内部表 & 外部表
- 在创建表的时候，可指定表的类型。表有两种类型，分别是内部表(管理表)、外部表。
- **默认情况下，创建内部表**。如果要创建外部表，需要使用关键字 **external**
- 在删除内部表时，表的定义(元数据) 和 数据 同时被删除
- 在删除外部表时，仅删除表的定义，数据被保留
- **在生产环境中，多使用外部表**
<a name="Mew51"></a>
### 内部表
- t1.dat 文件内容
- 注：使用的分隔符是 **` ;`** **` ,`** `** :**`
2;zhangsan;book,TV,code;beijing:chaoyang,shagnhai:pudong 3;lishi;book,code;nanjing:jiangning,taiwan:taibei 4;wangwu;music,book;heilongjiang:haerbin
- 创建表 SQL
```sql
-- 创建内部表
create table t1(
id int,
name string,
hobby array<string>,
addr map<string, string>
)
row format delimited
fields terminated by ";"
collection items terminated by ","
map keys terminated by ":";
-- 显示表的定义，显示的信息较少
desc t1;
-- 显示表的定义，显示的信息多，格式友好
desc formatted t1;
-- 加载数据
load data local inpath '/home/hadoop/data/t1.dat' into table t1;
-- 查询数据
select * from t1;
-- 查询数据文件
dfs -ls /user/hive/warehouse/mydb.db/t1;
-- 删除表。表和数据同时被删除
drop table t1;
-- 再次查询数据文件，已经被删除
外部表
-- 创建外部表
create external table t2(
id int,
name string,
hobby array<string>,
addr map<string, string>
)
row format delimited
fields terminated by ";"
collection items terminated by ","
map keys terminated by ":";
-- 显示表的定义
desc formatted t2;
-- 加载数据
load data local inpath '/home/hadoop/data/t1.dat' into table t2;
-- 查询数据
select * from t2;
-- 删除表。表删除了，目录仍然存在
drop table t2;
-- 再次查询数据文件，仍然存在
内部表和外部表转换
-- 创建内部表，加载数据，并检查数据文件和表的定义
create table t1(
id int,
name string,
hobby array<string>,
addr map<string, string>
)
row format delimited
fields terminated by ";"
collection items terminated by ","
map keys terminated by ":";
load data local inpath '/home/hadoop/data/t1.dat' into table t1;
dfs -ls /user/hive/warehouse/mydb.db/t1;
desc formatted t1;
-- 内部表转外部表
alter table t1 set tblproperties('EXTERNAL'='TRUE');
-- 查询表信息，是否转换成功
desc formatted t1;
-- 外部表转内部表。EXTERNAL 大写，false 不区分大小(建议都使用大写)
alter table t1 set tblproperties('EXTERNAL'='FALSE');
-- 查询表信息，是否转换成功
desc formatted t1;
小结
- 建表时
- 如果不指定external关键字，创建的是内部表（即默认创建内部表）；
- 指定external关键字，创建的是外部表；
- 删表时
- 删除外部表时，仅删除表的定义，表的数据不受影响
- 删除内部表时，表的数据和定义同时被删除
外部表的使用场景
Hive在执行查询时，一般会扫描整个表的数据。由于表的数据量大，全表扫描消耗时间长、效率低。
- 有时候，查询只需要扫描表中的一部分数据即可，Hive引入了分区表的概念，将表的数据存储在不同的子目录中，每一个子目录对应一个分区。只查询部分分区数据时，可避免全表扫描，提高查询效率。
- 在实际中，通常根据时间、地区等信息进行分区
分区表创建与数据加载
- 注：分区字段不是表中已经存在的数据，可以将分区字段看成 **伪列**
```sql
— 创建表
create table if not exists t3(
id int,
name string,
hobby array
, addr map
— 加载数据 load data local inpath “/home/hadoop/data/t1.dat” into table t3 partition(dt=”2020-06-01”);
load data local inpath “/home/hadoop/data/t1.dat” into table t3 partition(dt=”2020-06-02”);
<a name="B1fSQ"></a>
### 查看分区
- `show partitions t3;`
<a name="zDQol"></a>
### 新增分区并设置数据
```sql
-- 增加一个分区，不加载数据
alter table t3 add partition(dt='2020-06-03');
-- 增加多个分区，不加载数据
alter table t3
add partition(dt='2020-06-05') partition(dt='2020-06-06');
-- 增加多个分区。准备好数据，并加载数据到分区中
dfs -cp /user/hive/warehouse/mydb.db/t3/dt=2020-06-01 /user/hive/warehouse/mydb.db/t3/dt=2020-06-07;
dfs -cp /user/hive/warehouse/mydb.db/t3/dt=2020-06-01 /user/hive/warehouse/mydb.db/t3/dt=2020-06-08;
alter table t3 add
partition(dt='2020-06-07') location '/user/hive/warehouse/mydb.db/t3/dt=2020-06-07'
partition(dt='2020-06-08') location '/user/hive/warehouse/mydb.db/t3/dt=2020-06-08';
-- 查询数据
select * from t3;
修改分区的hdfs路径
- 修改分区路径后，文件会被保存在新设置的分区中
alter table t3 partition(dt='2020-06-01') set location '/user/hive/warehouse/t3/dt=2020-06-03';删除分区
可以删除一个或多个分区，用逗号隔开
- 删除分区后，分区中的文件不会被删除
alter table t3 drop partition(dt='2020-06-03'), partition(dt='2020-06-04');
5. 分桶表
- 当单个的分区或者表的数据量过大，分区不能更细粒度的划分数据，就需要使用分桶技术将数据划分成更细的粒度。将数据按照指定的字段进行分成多个桶中去，即将数据按照字段进行划分，数据按照字段划分到多个文件当中去。
- 分桶的原理：
- Hive中：分桶字段.hashCode % 分桶个数（数字的hashcode等于其本身）
- 类似于MR中：key.hashCode % reductTask
注：分桶表不支持 load data 插入数据
测试数据
1 java 90 1 c 78 1 python 91 1 hadoop 80 2 java 75 2 c 76 2 python 80 2 hadoop 93 3 java 98 3 c 74 3 python 89 3 hadoop 91 5 java 93 6 c 76 7 python 87 8 hadoop 88```sql — 创建分桶表 create table course( id int, name string, score int ) clustered by (id) into 3 buckets row format delimited fields terminated by “\t”;
— 创建普通表 create table course_common( id int, name string, score int ) row format delimited fields terminated by “\t”;
— 普通表加载数据 load data local inpath ‘/home/hadoop/data/course.dat’ into table course_common;
— 通过 insert … select … 给桶表加载数据 insert into table course select * from course_common;
— 观察分桶数据。数据按照：(分区字段.hashCode) % (分桶数) 进行分区 dfs -ls /user/hive/warehouse/test1.db/course dfs -cat /user/hive/warehouse/test1.db/course/000000_0; dfs -cat /user/hive/warehouse/test1.db/course/000001_0; dfs -cat /user/hive/warehouse/test1.db/course/000002_0;
**注：**
- 分桶规则：分桶字段.hashCode % 分桶数
- 分桶表加载数据时，使用 `insert... select ... `方式进行
- 网上有资料说要使用分区表需要设置 hive.enforce.bucketing=true，那是Hive 1.x 以前的版本；
- Hive 2.x 中，删除了该参数，始终可以分桶；
<a name="RkQB0"></a>
## 6. 修改表 & 删除表
```sql
-- 修改表名。rename
alter table course_common
rename to course_common1;
-- 修改列名。change column
alter table course_common1
change column id cid int;
-- 修改字段类型。change column
alter table course_common1
change column cid cid string;
-- The following columns have types incompatible with the existing columns in their respective positions
-- 修改字段数据类型时，要满足数据类型转换的要求。如int可以转为string，但是string不能转为int
-- 增加字段。add columns
alter table course_common1
add columns (common string);
-- 删除字段：replace columns
-- 这里仅仅只是在元数据中删除了字段，并没有改动hdfs上的数据文件
alter table course_common1
replace columns(id string, cname string, score int);
-- 删除表
drop table course_common1;
DDL命令小结
- 主要对象：数据库、表
- 表的分类：
- 内部表。删除表时，同时删除元数据和表数据
- 外部表。删除表时，仅删除元数据，保留表中数据；生产环境多使用外部表
- 分区表。按照分区字段将表中的数据放置在不同的目录中，提高SQL查询的性能
- 分桶表。按照分桶字段，将表中数据分开。 分桶字段.hashCode % 分桶数据
- 主要命令：create、alter 、drop
五、HQL操作之数据操作
1. 数据导入
装载数据（load）
基本语法
LOAD DATA [LOCAL] INPATH 'filepath'[OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)];
LOCAL：
- LOAD DATA LOCAL … 从本地文件系统加载数据到Hive表中。本地文件会拷贝到Hive表指定的位置
- LOAD DATA … 从HDFS加载数据到Hive表中。HDFS文件移动到Hive表指定的位置
- INPATH：加载数据的路径
- OVERWRITE：覆盖表中已有数据；否则表示追加数据
PARTITION：将数据加载到指定的分区
准备工作 ```sql — 创建表 CREATE TABLE tabA ( id int, name string, area string ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘,’ ;
— 数据文件（~/data/sourceA.txt）： 1,fish1,SZ 2,fish2,SH 3,fish3,HZ 4,fish4,QD 5,fish5,SR
— 拷贝文件到 HDFS hdfs dfs -put sourceA.txt /user/hive/data/
- 开始装载数据
```sql
-- 加载本地文件到hive(tabA)
LOAD DATA LOCAL INPATH '/home/hadoop/data/sourceA.txt'
INTO TABLE tabA;
-- 检查本地文件还在
-- 加载hdfs文件到hive(tabA)
LOAD DATA INPATH '/user/hive/data/sourceA.txt'
INTO TABLE tabA;
-- 检查HDFS文件，已经被转移
-- 加载数据覆盖表中已有数据
LOAD DATA LOCAL INPATH '/home/hadoop/data/sourceA.txt'
OVERWRITE INTO TABLE tabA;
-- 创建表时加载数据(亲测：只能从hdfs文件系统的目录中读取数据)
hdfs dfs -mkdir /user/hive/tabB
hdfs dfs -put sourceA.txt /user/hive/tabB
CREATE TABLE tabB (
id INT,
name string,
area string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
Location '/user/hive/tabB';
插入数据（insert）
-- 创建分区表
CREATE TABLE tabC (
id INT,
name string,
area string
)
partitioned by (month string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
-- 插入数据
insert into table tabC
partition(month='202001')
values (5, 'wangwu', 'BJ'), (4, 'lishi', 'SH'), (3, 'zhangsan', 'TJ');
-- 插入查询的结果数据
insert into table tabC partition(month='202002')
select id, name, area from tabC where month='202001';
-- 多表（多分区）插入模式
from tabC
insert overwrite table tabC partition(month='202003')
select id, name, area where month='202002'
insert overwrite table tabC partition(month='202004')
select id, name, area where month='202002';
创建表并插入数据（as select）
-- 根据查询结果创建表
create table if not exists tabD
as select * from tabC;
2. 数据导出
-- 将查询结果导出到本地
insert overwrite local directory '/home/hadoop/data/tabC'
select * from tabC;
-- 将查询结果格式化输出到本地
insert overwrite local directory '/home/hadoop/data/tabC2'
row format delimited fields terminated by ' '
select * from tabC;
-- 将查询结果导出到HDFS
insert overwrite directory '/user/hadoop/data/tabC3'
row format delimited fields terminated by ' '
select * from tabC;
-- dfs 命令导出数据到本地。本质是执行数据文件的拷贝
dfs -get /user/hive/warehouse/mydb.db/tabc/month=202001 /home/hadoop/data/tabC4
-- hive 命令导出数据到本地。执行查询将查询结果重定向到文件
-- 注意：要指定表tabC所在数据库，否则报错
hive -e "select * from mydb.tabC" > a.log
-- export 导出数据到HDFS。使用export导出数据时，不仅有数据还有表的元数据信息
export table tabC to '/user/hadoop/data/tabC4';
-- export 导出的数据，可以使用 import 命令导入到 Hive 表中
-- 使用 like tname创建的表结构与原表一致。create ... as select ... 结构可能不一致
create table tabE like tabc;
import table tabE from '/user/hadoop/data/tabC4';
-- 截断表，清空数据。(注意：仅能操作内部表)
truncate table tabE;
-- 以下语句报错，外部表不能执行 truncate 操作
alter table tabC set tblproperties("EXTERNAL"="TRUE");
truncate table tabC;
小结
- 数据导入
load data/insert/create table ... as select .../import table
- 数据导出
insert overwrite ... diretory .../hdfs dfs -get/hive -e "select ..." > a.log/export table ...- Hive的数据导入与导出还可以使用其他工具：Sqoop(apache)、DataX(alibaba)等；
六、HQL操作之DQL命令【重点】
DQL —> Data Query Language，数据查询语言
select 语法
SELECT [ALL | DISTINCT] select_expr, select_expr, ... FROM table_reference [WHERE where_condition] [GROUP BY col_list] [ORDER BY col_list] [CLUSTER BY col_list | [DISTRIBUTE BY col_list] [SORT BY col_list]] [LIMIT [offset,] rows]SQL（与HQL一样）语句书写注意事项
- SQL语句对大小写不敏感
- SQL语句可以写一行（简单SQL）也可以写多行(复杂SQL)
- 关键字不能缩写，也不能分行
- 各子句一般要分行
- 使用缩进格式，提高SQL语句的可读性(重要)
- 创建表，加载数据 ```sql — 测试数据 /home/hadoop/data/emp.dat 7369,SMITH,CLERK,7902,2010-12-17,800,,20 7499,ALLEN,SALESMAN,7698,2011-02-20,1600,300,30 7521,WARD,SALESMAN,7698,2011-02-22,1250,500,30 7566,JONES,MANAGER,7839,2011-04-02,2975,,20 7654,MARTIN,SALESMAN,7698,2011-09-28,1250,1400,30 7698,BLAKE,MANAGER,7839,2011-05-01,2850,,30 7782,CLARK,MANAGER,7839,2011-06-09,2450,,10 7788,SCOTT,ANALYST,7566,2017-07-13,3000,,20 7839,KING,PRESIDENT,,2011-11-07,5000,,10 7844,TURNER,SALESMAN,7698,2011-09-08,1500,0,30 7876,ADAMS,CLERK,7788,2017-07-13,1100,,20 7900,JAMES,CLERK,7698,2011-12-03,950,,30 7902,FORD,ANALYST,7566,2011-12-03,3000,,20 7934,MILLER,CLERK,7782,2012-01-23,1300,,10
— 建表并加载数据 CREATE TABLE emp ( empno int, ename string, job string, mgr int, hiredate DATE, sal int, comm int, deptno int ) row format delimited fields terminated by “,”;
— 加载数据 LOAD DATA LOCAL INPATH ‘/home/hadoop/data/emp.dat’ INTO TABLE emp;
<a name="40iOZ"></a>
## 1. 基本查询
```sql
-- 省略from子句的查询
select 8*888 ;
select current_date ;
-- 使用列别名
select 8*888 product;
select current_date as currdate;
-- 全表查询
select * from emp;
-- 选择特定列查询
select ename, sal, comm from emp;
-- 使用函数
select count(*) from emp; # 或者count(1)，均将null统计在内
-- count(colname) 按字段进行count，不统计NULL，因此数量可能不全
select sum(sal) from emp;
select max(sal) from emp;
select min(sal) from emp;
select avg(sal) from emp;
-- 使用limit子句限制返回的行数
select * from emp limit 3;
2. where 子句
- WHERE子句 紧随 FROM子句，使用WHERE子句，过滤不满足条件的数据
where 子句中不能使用列的别名
官方文档：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF
备注
and、or、not
- 比较运算符，null参与运算
select null=null;—> nullselect null==null;—> nullselect null<=>null;—> true
- 使用 is null 判空
select * from emp where comm is null;
- 使用 in
select * from emp where deptno in (20, 30);
- 使用 between … and …
select * from emp where sal between 1000 and 2000;
- 使用 like
- 简单正则表达式
select ename, sal from emp where ename like '%L%';
使用 rlike
GROUP BY 语句通常与 聚组函数 一起使用，按照一个或多个列对数据进行分组，对每个组进行聚合操作
- Hive 不允许直接访问 非group by 字段
- 对于 非group by字段，可以用 Hive 的
**collect_set**函数收集这些字段，返回一个数组 - 使用数字下标，可以直接访问数组中的元素
- 关于学习 group by 时的疑问：
- 有同学说这种类似多课程多成绩的问题，可以通过窗口函数（后面课程会讲）解决，所以我先继续往下学习 ```sql — 计算emp表每个部门的平均工资 select deptno, collect_list(ename), max(sal) max_sal from emp group by deptno;
- 对于 非group by字段，可以用 Hive 的
— 计算emp每个部门中每个岗位的最高薪水 select deptno, job, max(sal) from emp group by deptno, job;
— 求每个部门的平均薪水大于2000的部门 select deptno, avg(sal) from emp group by deptno having avg(sal) > 2000;
- **备注**
- **where子句不能有分组函数**；having子句可以有分组函数
- having只用于group by 分组之后
- where子句针对**表中的数据**发挥作用；having子句针对**查询结果**（聚组之后的结果）发挥作用
- 举个例子：#筛选出北京西城、东城、海淀三个区中学校数量超过10所的区及各区学校数量。
SELECT region,count(school)<br />FROM T02_Bejing_school<br />WHERE region IN ('海淀' , '西城' , '东城')<br />GROUP BY region HAVING count(school) > 10;<br />注意！我们不能用where来筛选超过学校数量超过10的区，因为表中不存在这样一条记录。而HAVING子句可以让我们筛选成组后的各组数据
- where 和 having 的执行顺序
- where 早于 group by 早于 having
- where子句在聚合前先筛选记录（表中的记录），也就是说作用在group by 子句和having子句前，而 having子句在聚合后对组记录进行筛选
<a name="SFHes"></a>
## 4. 表连接
- Hive支持通常的SQL JOIN语句。默认情况下，仅支持**等值连接**（A==B），不支持**非等值连接**（A!=B）。
- JOIN 语句中经常会使用表的别名。使用别名可以简化SQL语句的编写，使用表名前缀可以提高SQL的解析效率。
- 连接查询操作分为两大类：内连接和外连接，而外连接可进一步细分为三种类型：
1. 内连接: `[inner] join`
1. 外连接 (outer join)
- 左外连接。 `left [outer] join`，左表的数据全部显示
- 右外连接。 `right [outer] join`，右表的数据全部显示
- 全外连接。 `full [outer] join`，两张表的数据都显示
<a name="T4jNz"></a>
### 多表连接
- 连接 n 张表，至少需要 n-1 个连接条件
- 例如：多表连接查询，查询老师对应的课程，以及对应的分数，对应的学生
```sql
select *
from techer t left join course c on t.t_id = c.t_id
left join score s on s.c_id = c.c_id
left join student stu on s.s_id = stu.s_id;
- Hive总是按照从左到右的顺序执行，Hive会对每对 JOIN 连接对象启动一个 MapReduce 任务
上面的例子中会首先启动一个 MapReduce job 对表 t 和表 c 进行连接操作；然后再启动一个 MapReduce job 将第一个 MapReduce job 的输出和表 s 进行连接操作；然后再继续直到全部操作
满足以下条件将会产生笛卡尔集
- 没有连接条件
- 连接条件无效
- 所有表中的所有行互相连接
- 如果表A、B分别有M、N条数据，其笛卡尔积的结果将有 M*N 条数据
缺省条件下hive不支持笛卡尔积运算，可以手动打开（慎用）
order by 子句出现在 select 语句的结尾；
- order by子句对最终的结果进行排序；
- 默认使用升序(ASC)；可以使用DESC，跟在字段名之后表示降序；
- 排序字段要出现在select子句中
- ORDER BY 执行全局排序，只有一个reduce，效率很低！。 ```sql — 普通排序 select * from emp order by deptno;
— 按别名排序 select empno, ename, job, mgr, sal + nvl(comm, 0) salcomm, deptno from emp order by salcomm desc;
— 多列排序，其中nvl(comm,0)表示 当comm为null时，赋值为0 select empno, ename, job, mgr, sal + nvl(comm, 0) salcomm, deptno from emp order by deptno, salcomm desc;
— 排序字段要出现在select子句中。以下语句无法执行（因为select子句中缺少deptno）： select empno, ename, job, mgr, sal + nvl(comm, 0) salcomm from emp order by deptno, salcomm desc;
<a name="wDw0E"></a>
### 局部排序（sort by）
- sort by：每个MR内部排序
- **对于大规模数据而言 order by 效率低**
- 在很多业务场景，我们并不需要全局有序的数据，此时可以使用 sort by
- sort by 为每个 reduce 产生一个排序文件，在 reduce 内部进行排序，得到局部有序的结果
```sql
-- 在hive命令行中设置reduce个数（此处是因为数据量比较小，但是一般由hive自己计算决定）
set mapreduce.job.reduces=2;
-- 按照工资降序查看员工信息
select * from emp sort by sal desc;
-- 将查询结果导入到文件中（按照工资降序）。生成两个输出文件，每个文件内部数据按工资降序排列
insert overwrite local directory '/home/hadoop/output/sortsal'
select * from emp sort by sal desc;
分区排序（distribute by）
- distribute by 将特定的行发送到特定的reducer中，便于后继的聚合 与 排序操作；
- distribute by 类似于MR中的分区操作，可以结合 sort by 操作，使分区数据有序；
- distribute by 要写在sort by之前，即先分区后排序； ```sql — 启动2个reducer task；先按 deptno 分区，在分区内按 sal+comm 排序 set mapreduce.job.reduces=2;
— 将结果输出到文件，观察输出结果 insert overwrite local directory ‘/home/hadoop/output/distBy’ select empno, ename, job, deptno, sal + nvl(comm, 0) salcomm from emp distribute by deptno sort by salcomm desc; — 上例中，由于reduc有2个（分区规则跟分桶表类似），因此数据被分到了同一个区，看不出分区的结果
— 将数据分到3个区中，每个分区都有数据 set mapreduce.job.reduces=3; insert overwrite local directory ‘/home/hadoop/output/distBy1’ select empno, ename, job, deptno, sal + nvl(comm, 0) salcomm from emp distribute by deptno sort by salcomm desc; ```
Cluster By
- 当distribute by 与 sort by是同一个字段时，可使用 cluster by 简化语法
cluster by 只能是升序，不能指定排序规则
以下两句的语法是等价的
order by。（MySQL中只有order by一种排序方法）执行全局排序，效率低。生产环境中慎用
- sort by。 使数据局部有序(在reduce内部有序)
- distribute by。按照指定的条件将数据分组，常与sort by联用，使数据局部有序
- cluster by。当distribute by 与 sort by是同一个字段时，可使用cluster by简化语法
若有收获，就点个赞吧
0 人点赞
