Hive操作语法

常用操作三大类：

DDL：数据定义语言，create,drop,alter,....
DML：数据操纵语言，insert,update,delete,....
DQL：数据查询语言,select,....

Hive也有对应的 DCL，也就是数据控制语言，但是这属于运维范畴。

1.DDL

语法结构：

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

语法详解：

CREATE [IF NOT EXISTS] (DATABASE|SCHEMA) 大写，关键字
(DATABASE|SCHEMA) (|)表示多个概念，选其一
[IF NOT EXISTS] 表示可选，用来忽略异常
database_name 类似这种的小写名称，是需要自己根据需求来指定的。
COMMENT 指定表的注释，一般测试学习的时候可以不用加，但是工作环境中，一定要加注释
LOCATION 指定hive你创建的数据库的HDFS存放目录，如果没有指定，就在默认的仓库路径
下/databsae_name.db
[WITH DBPROPERTIES (property_name=property_value, ...)]; 用来添加库的一些属性

举例：
创建普通库

create database dbname;

创建库的时候检查存与否

create databse if not exists dbname;

创建库的时候带注释

create database if not exists dbname comment 'create hivedb named dbname';

创建带属性的库

create database if not exists dbname with dbproperties ('a'='aaa','b'='bbb');

创建库的时候指定存储路径(有这种语法，但是不推荐使用)

create database if not exists myspark location "/data/myspark/"

库操作--查询库相关

查看有哪些数据库

show databases;

显示数据库的详细属性信息

desc database [extended] dbname;
desc database extended myhive;

查看正在使用哪个库

select current_database();

查看创建库的详细语句

show create database mydb;

库操作--删除库

语法和案例：

drop database dbname;
drop database if exists dbname;

默认情况下，hive不允许删除包含表的数据库，有两种解决办法：
1、手动删除库下所有表，然后删除库
2、使用cascade关键字

drop database if exists dbname cascade; // 谨慎操作，否则后悔终生

默认情况下就是restrict
drop database if exists myhive 和 drop database if exists myhive restrict 意义一样
没有删除库的企业需求！

库操作--切换库

语法和示例：

use database_name
use myhive;

表操作--创建表

语法结构

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]

一个完整的全面的HQL创建表的示例

CREATE TABLE page_view
(viewTime INT, userid BIGINT, page_url STRING, referrer_url STRING, ip
STRING COMMENT 'IP Address')
COMMENT 'This is the page view table'
PARTITIONED BY(dt STRING, country STRING)
CLUSTERED BY(userid) SORTED BY(viewTime) INTO 31 BUCKETS
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY ',' //数组多个元素之间的分隔符
MAP KEYS TERMINATED BY ':'
LINES TERMINATED BY '\n'
STORED AS TEXTFILE  //指定文件存储格式
LOCATION '/myhive';

执行命令查看表结构：

desc formatted page_view;

一个完整的全面的HQL创建表的示例：

create table student(id int);

建表语句相关关键字解释

• CREATE TABLE
  创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用IF NOT EXISTS选项来忽略这个异常。
• EXTERNAL
  创建外部表，在建表的同时指定一个指向实际数据的目录（LOCATION），如果该目录不存在，则自动创建该目录。
  Hive创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。
• 字段定义 / 分区定义 / 分桶字段定义
  col_name 表示字段名称，data_type 表示字段数据类型，由于 Hive 是读模式类型的系统，所以
  其实这个data_type 只是一个类型描述，不具备像 MySQL 中的字段类型的约束。
• PARTITIONED BY
  在Hive Select查询中一般会扫描整个表内容，会消耗很多时间做没必要的工作。有时候只需要扫描表中关心的一部分数据，因此建表时引入partition概念。分区表可以拥有一个或者多个分区，每个分区以文件夹的形式单独存在表文件夹的目录下，分区是以字段的形式在表结构中存在，通过desc table命令可以查看到字段存在，但是该字段不存放实际的数据内容，仅仅是分区的表示。
• CLUSTERED BY ... SORTED BY ... INTO ... BUCKETS
  对于每一个表（table）或者分区，Hive 可以进一步组织成桶，也就是说桶是更为细粒度的数据范围划分。Hive 也是针对某一列进行桶的组织。Hive 采用对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。
  cluster by 用来指定分桶字段
  sorted by 用来指定每个分桶按照什么字段排序
  into ... buckets 用来指定分桶的个数
• ROW FORMAT
  后面可以接两种形式：DELIMITED 和 SERDE
  DELIMITED 形式为：指定分隔符，使用切割的方式解析文本文件：split

DELIMITED
[FIELDS TERMINATED BY char]
[COLLECTION ITEMS TERMINATED BY char]
[MAP KEYS TERMINATED BY char]
[LINES TERMINATED BY char]

id favors score
101 lanqiu,zuqiu,pingpong math:88,yuwen:97,yingyu:30
203 lanqiu,zuqiu,pingpong math:88,yuwen:97
LINES: "\n" 换行符
FIELDS："\t" 字段分隔符
COLLECTION ITEMS：","集合元素分隔符
MAP KEYS：":", map中的一个key-value之间的key和value的分隔符

SERDE 形式为：序列化组件，就是一种解析数据的方式，非切割方式，比如正则

SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value,
property_name=property_value, ...)]

用户在建表的时候可以自定义SerDe或者使用自带的SerDe。如果没有指定ROW FORMAT或者ROW FORMAT DELIMITED，将会使用自带的SerDe。在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的SerDe，Hive通过SerDe确定表的具体的列的数据。

• STORED AS TEXTFILE | SEQUENCEFILE | RCFILE | PARQUETFILE
  如果文件数据是纯文本，可以使用 STORED AS TEXTFILE，默认也是TextFile格式，可以通过执行
  命令：
  set hive.default.fileformat;
  进行查看
  如果数据需要压缩，使用 STORED AS SEQUENCEFILE。支持的数据格式有：SequenceFile，
  RCFile，PaquetFile 等
• COMMENT
  可以为表与字段增加描述信息，企业环境中推荐每个字段加注释。
• LOCATION
  指定数据文件存放的 HDFS 目录，不管内部表还是外表，都可以指定。不指定就在默认的仓库路径。
  最佳实践：
  如果创建内部表请最好不要指定location，就存储在默认的仓库路径
  如果创建表时要指定location，请创建外部表。
• LIKE
  允许用户复制现有的表结构，但是不复制数据。（不会复制是内部表还是外部表这个属性）
  示例：create table tableA like tableB（创建一张 tableA 空表复制 tableB 的结构）

具体实例
创建普通内部表（managed_table）

create table student_mng (id int, name string, sex string, age int, department
string) row format delimited fields terminated by ',' stored as textfile;

创建外部表（external_table）

create external table student_ext (id int, name string, sex string, age int,
department string) row format delimited fields terminated by ',' location
'/hive_data_ext/student_ext';

创建分区表（partition_table）

create table student_ptn(id int, name string, sex string, age int, department
string) partitioned by(city string) row format delimited fields terminated by
',' stored as textfile;

创建分桶表（bucket_table）

create table student_bck(id int, name string, sex string, age int, department
string) clustered by(department) sorted by(age DESC) into 4 buckets row format
delimited fields terminated by ',';

复制表（like语法）

// 不管老表是内部表还是外部表，new_table 都是内部表
create table student_mng_like1 like student_mng;
// 不管老表是内部表还是外部表，如果加external关键字，student_ext_like2 都是外部表
create external table if not exists student_ext_like2 like student_ptn;

表操作--修改表

重命名表

// 语法
ALTER TABLE table_name RENAME TO new_table_name
// 实例
alter table student_mng rename to student_mng_new;

修改表属性

// 语法
ALTER TABLE table_name SET TBLPROPERTIES table_properties;
table_properties: (property_name = property_value, property_name =
property_value, ... )
// 实例
ALTER TABLE table_name SET TBLPROPERTIES ('comment' = 'my new students table');

修改SERDE信息

// 语法
ALTER TABLE table_name [PARTITION partition_spec] SET SERDE serde_class_name
[WITH SERDEPROPERTIES serde_properties];
// 实例
ALTER TABLE table_name [PARTITION partition_spec] SET SERDEPROPERTIES
serde_properties;
// 语法
serde_properties:
: (property_name = property_value, property_name = property_value, ... )
// 实例：更改列分隔符
ALTER TABLE student SET SERDEPROPERTIES ('field.delim' = '-');

增加/删除/改变/替换字段

// 语法结构（注意：ADD是代表新增一字段，字段位置在所有列后面(partition列前)，REPLACE则是表示
替换表中所有字段）
ALTER TABLE name ADD COLUMNS (col_spec[, col_spec ...])
ALTER TABLE name CHANGE c_name new_name new_type [FIRST|AFTER c_name]
ALTER TABLE name REPLACE COLUMNS (col_spec[, col_spec ...])
// 实例
alter table student add columns(course string);
alter table student change column id ids int;
alter table student replace columns(id int, name string, address string);

增加分区：

// 语法结构
ALTER TABLE table_name ADD [IF NOT EXISTS] partition_spec [ LOCATION 'location1'
] partition_spec [ LOCATION 'location2' ] ...
// 语法结构
partition_spec
: PARTITION (partition_col1 = partition_col_value1, partition_col2 =
partiton_col_value2, ...)
// 实例
ALTER TABLE student_ptn ADD partition(part='a') partition(part='b');
ALTER TABLE student_ptn ADD IF NOT EXISTS partition(part='bb') location
'/myhive_bb'
partition(part='cc') location
'/myhive_cc';
ALTER TABLE student_ptn partition (part='bb') SET location '/myhive_bbbbb';
// 修改分区路径

删除分区

// 语法结构
ALTER TABLE table_name DROP partition_spec, partition_spec, ...
// 实例
ALTER TABLE student_ptn DROP if exists partition(part='aa');
ALTER TABLE student_ptn DROP if exists partition(part='aa')
if exists partition(part='bb');
// 实例
alter table student_ptn partition (part='aa') enable no_drop; // 防止分区被删除
alter table student_ptn partition (part='aa') enable offline; // 防止分区被查询

表操作--删除表

语法结构
DROP TABLE [IF EXISTS] table_name;
实例：
drop table if exists student;

表操作--清空表

语法结构
TRUNCATE TABLE table_name [PARTITION partition_spec];
实例：
truncate table student; // 清空表
truncate table student_ptn partition(city=’beijing’); // 清空表的city=beijing分
区

2.DML

load方式装载数据

语法结构：

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION
(partcol1=val1, partcol2=val2 ...)]

说明：
1、 LOAD 操作只是单纯的复制或者移动操作，将数据文件移动到Hive表对应的位置。如果从本地导入数
据，那就是复制/上传操作，如果从HDFS导入数据到Hive表，则是移动数据，谨慎操作。
2、LOCAL关键字
如果指定了LOCAL， LOAD命令会去查找本地文件系统中的filepath。如果没有指定 LOCAL关键字，则根据
inpath中的uri 查找文件
注意：uri是指hdfs上的路径，分简单模式和完整模式两种，例如：
简单模式：/user/hive/project/data1
完整模式：hdfs://namenode_host:9000/user/hive/project/data1
3、FilePath：
相对路径，例如：project/data1
绝对路径，例如：/user/home/project/data1
包含模式的完整 URI，列如：hdfs://namenode_host:9000/user/home/project/data1
注意：inpath子句中的文件路径下，不能再有文件夹。
4、OVERWRITE关键字
如果使用了OVERWRITE 关键字，则目标表（或者分区）中的内容会被删除，然后再将 filepath 指向的文
件/目录中的内容添加到表/分区中。 如果目标表（分区）已经有一个文件，并且文件名和 filepath 中的
文件名冲突，那么现有的文件会被新文件所替代。
实例：
加载本地相对路径数据

load data local inpath "./student.txt" into table student;

加载本地绝对路径数据

load data local inpath "/home/bigdata/student.txt" into table student;

使用HDFS的shell命令直接往Hive的student表中的HDFS目录导入数据

hadoop fs -put student.txt /user/hive/warehouse/hive_test.db/student/

加载HDFS上完整模式数据

load data inpath "hdfs://hadoop277ha/student/student.txt" into table student;

overwrite关键字使用

load data local inpath "/home/bigdata/stu.txt" overwrite into table student;

insert方式插入数据

第一：单条记录，多条记录

// 语法：
INSERT INTO TABLE table_name VALUES(XX,YY,ZZ);
//示例：
insert into table student(id,name,sex,age,department) values
(1,”huangbo”,”F”,18,”CS”), (2,”xuzheng”,”F”,28,”MS”)

第二：单重插入
insert ....select ...

// 语法：
INSERT OVERWRITE [INTO] TABLE table_name [PARTITION (partcol1=val1,
partcol2=val2 ...)] select_statement1 FROM from_statement
// 示例：
insert into table student_copy select * from student where age <= 18;

记住一个要点：select 查询语句中的字段的名称， 必须要和之前的 student_copy 表字段名称一致
第三：多重插入
最大的好处，就是可以将多条相类似的HQL语句合并成一条来处理，这样from语句扫描hive表数据的操
作就只会做一次，提高效率。

// 语法结构：
FROM from_statement
INSERT OVERWRITE TABLE table_name1 [PARTITION (partcol1=val1, partcol2=val2
...)] select_statement1
INSERT OVERWRITE TABLE table_name2 [PARTITION (partcol1=val1, partcol2=val2
...)] select_statement2]
....
// 实例：
from student
insert into table student_ptn partition(city='MA') select
id,name,sex,age,department where department='MA'
insert into table student_ptn partition(city='IS') select
id,name,sex,age,department where department='IS'
insert into table student_ptn partition(city='CS') select
id,name,sex,age,department where department='CS';

如果没有多重语法的加持，那么该HQL语句则要改写成3个同样形式的单重模式的插入语句：

insert into table student_ptn partition(city='CS') select
id,name,sex,age,department where department='CS' from student;
insert into table student_ptn partition(city='MA') select
id,name,sex,age,department where department='MA' from student;
insert into table student_ptn partition(city='IS') select
id,name,sex,age,department where department='IS' from student;

多重语法的优点：多个同种模式的HQL语句合并成，主要省略了from table的扫描一次，如果是三个语句独立执行，该表需要被扫描三次，但是如果是多重模式的HQL语句，那么只需要扫描一次。必然提高了效率
第四：分区插入
分区插入有两种，一种是静态分区，另一种是动态分区。如果混合使用静态分区和动态分区，则静态分区必须出现在动态分区之前。现分别介绍这两种分区插入。
静态分区：
A)、创建静态分区表
B)、从查询结果中导入数据
C)、查看插入结果
静态分区插入实例：

load data local inpath "/home/bigdata/student_shanghai.txt" into table
student_ptn(city="shanghai");
insert into table student_ptn partition(city="chongqing") select
id,name,sex,age,department from student;

动态分区：
静态分区需要创建非常多的分区，那么用户就需要写非常多的SQL！Hive提供了一个动态分区功能，其可以基于查询参数推断出需要创建的分区名称。
A)、创建分区表，和创建静态分区表是一样的
B)、参数设置

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

注意：动态分区默认情况下是开启的。但是却以默认是 "strict" 模式执行的，在这种模式下要求至少有一列分区字段是静态的。这有助于阻止因设计错误导致查询产生大量的分区。但是此处我们不需要静态分区字段，估将其设为 "nonstrict"。
对应还有一些参数可设置：

set hive.exec.max.dynamic.partitions.pernode=100; //每个节点生成动态分区最大个数
set hive.exec.max.dynamic.partitions=1000; //生成动态分区最大个数，如果
自动分区数大于这个参数，将会报错
set hive.exec.max.created.files=100000; //一个任务最多可以创建的文件数目
set dfs.datanode.max.xcievers=4096; //限定一次最多打开的文件数
set hive.error.on.empty.partition=false; //表示当有空分区产生时，是否抛出异常

补充小技巧：如果某些参数被更改了，想还原，则直接使用 reset 命令执行恢复
动态分区插入实例：注意：查询语句select查询出来的动态分区 age 和 zipcode 必须放最后，和分区字段对应，不然结果会出错

# 一个分区字段
insert into table test2 partition (age) select name,address,school,age from
students;
# 两个分区字段
insert into table student_ptn2 partition(city='sa',zipcode) select id, name,
sex, age, department, department as zipcode from studentss;

第五：分桶插入
创建分桶表：

create table student_bck(id int, name string, sex string, age int, department
string) clustered by(department) sorted by(age DESC) into 4 buckets row format
delimited fields terminated by ',';

clustered by(department)：表示按照department字段进行分桶，分桶的逻辑就是MapReduce的
HashPartitioner的逻辑。
sorted by(age DESC)：每个桶的数据按照age字段降序排序
into 4 buckets： 指定分成多少个桶
往分桶表中插入数据：请注意不能使用 load 方式直接往分分桶表中导入数据。只能通过
insert....select.... 语法来进行

# 设置分桶操作的开关，默认是打开的
set hive.enforce.bucketing = true;
set mapreduce.job.reduces = 2;
# insert...select....方式导入数据
insert into table student_bck select id,name,sex,age,department from student
distribute by age sort by age desc, id asc;

分桶的作用：
1、获得更高的查询处理效率。桶为表加上了额外的结构，Hive在处理有些查询时能利用这个结构。具体而言，连接两个在（包含连接列的）相同列上划分了桶的表，可以使用Map端连接（Map-side join）高效的
实现。比如JOIN操作。对于JOIN操作两个表有一个相同的列，如果对这两个表都进行了桶操作。那么将保存相同列值的桶进行JOIN操作就可以，可以大大较少JOIN的数据量。
2、使取样（sampling）更高效。在处理大规模数据集时，在开发和修改查询的阶段，如果能在数据集的一小部分数据上试运行查询，会带来很多方便。
关于Hive的分桶，其实跟MapReduce的分区是一样的，Hive的分区，其实，主要是手动粗粒度的进行数据划分。
Hive分区：按照指定字段，每个分区，只有这个字段值的其中一个，该分区只包含了该值，一定不会包含该字段的其他值的记录。
Hive分桶：关注的重点是桶的个数，不在乎那些数据进入到那个桶。只是按照一定的规则，分散成我想要的桶数。
第六：CTAS语法
在实际情况中，表的输出结果可能太多，不适于显示在控制台上，这时候，将Hive的查询输出结果直接存在一个新的表中是非常方便的，我们称这种情况为CTAS
展示：

CREATE TABLE mytest AS SELECT name, age FROM test;

注意：CTAS操作是原子的，因此如果select查询由于某种原因而失败，新表是不会创建的！

hadoop fs命令插入数据

上面讲述的使用 load 方式导入数据到表中，其实效果类似于直接使用 hdfs 的上传数据的命令来进行导入

hadoop fs -put /home/bigdata/student.txt
/user/hive/warehouse/bigdata_hive.db/student/

insert方式导出数据

在Hive中，不仅可以使用insert来导入数据，还可以使用insert来导出数据
语法结构：

# 单模式导出：
INSERT OVERWRITE [LOCAL] DIRECTORY directory1 select_statement
# 多模式导出：
FROM from_statement
INSERT OVERWRITE [LOCAL] DIRECTORY directory1 select_statement1
[INSERT OVERWRITE [LOCAL] DIRECTORY directory2 select_statement2] ...

实例：

# 导出数据到本地
insert overwrite local directory '/home/bigdata/student.txt' select * from
studentss;
# 注意：数据写入到文件系统时进行文本序列化，且每列用^A来区分，\n为换行符。
# 用more命令查看时不容易看出分割符，可以使用: sed -e 's/\x01/\t/g' filename 来查看。
# 如果要指定行列分隔符，使用创建表中的指定行列分隔的方式就行
insert overwrite local directory '/home/bigdata/hive_insert_out' row format
delimited fields terminated by "," select * studentss;
# 导出数据到HDFS
insert overwrite directory 'hdfs://bigdata02:9000/user/hive/warehouse/mystudent'
select * from studentss;
insert overwrite directory '/student' select * from studentss where age >= 20;
# 如果是下头这种简写形式，一定要注意在hive的$HIVE_HOME/conf目录下，必须放入：coresite.
xml和hdfs-site.xml配置文件

3.DQL

基础select查询

Hive 中的 SELECT 基础语法和标准 SQL 语法基本一致，支持WHERE、DISTINCT、GROUP BY、ORDER BY、HAVING、LIMIT、子查询等
1、select * from db.table1
2、select count(distinct uid) from db.table1
3、支持select、union all、join（left、right、full join）、like、where、having、各种聚
合函数、支持json解析
4、UDF（User Defined Function）/ UDAF/UDTF
5、不支持update和delete
6、hive虽然支持in/exists（老版本是不支持的），但是hive推荐使用semi join的方式来代替实现，而且效率更高。
7、支持case … when …
总结：

select...from....join ...on ...where.....group by.....having....order by...limit
union, in/exists, case....when....

Select查询的语法结构为：

SELECT [ALL | DISTINCT] select_ condition, select_ condition, ...
FROM table_name a
[JOIN table_other b ON a.id = b.id]
[WHERE where_condition]
[GROUP BY col_list
[HAVING condition]]
[CLUSTER BY col_list | [DISTRIBUTE BY col_list] [SORT BY col_list | ORDER BY
col_list] ]
[LIMIT number]

语法结构中的关键字说明：
1、select_ condition查询字段
2、table_name 表名
3、order by(字段) 全局排序，因此只有一个reducer，只有一个reduce task的结果，比如文件名是000000_0，会导致当输入规模较大时，需要较长的计算时间。
4、sort by(字段) 局部排序，不是全局排序，其在数据进入reducer前完成排序。因此，如果用sort by进行排序，并且设置mapred.reduce.tasks>1，则sort by只保证每个reducer的输出有序，不保证全局有序。那万一，我要对我的所有处理结果进行一个综合排序，而且数据量又非常大，那么怎么解决？我们不适用order by进行全数据排序，我们适用sort by对数据进行局部排序，完了之后，再对所有的局部排序结果做一个归并排序
5、distribute by(字段) 根据指定的字段将数据分到不同的reducer，且分发算法是hash散列。
6、cluster by(字段) 除了具有Distribute by的功能外，还会对该字段进行排序。因此，如果分桶和sort字段是同一个时，此时，cluster by = distribute by + sort by，如果我们要分桶的字段和要
排序的字段不一样，那么我们就不能使用clustered by
查询SQL的实例：

# 获取年龄最大的三个学生 order by
select * from student order by age desc limit 3;
# 查询学生年龄按降序排序 sort by
set mapred.reduce.tasks=3; 或者 set mapreduce.job.reduces=3;
select id, age, name,sex, department from student sort by age desc;
# 分桶查询 distribute by
set hive.enforce.bucketing = true; // 在旧版本中需要开启分桶查询的开关
set mapreduce.job.reduces=3; // 指定ReduceTask数量，也就是指定桶的数量
select id, age, name, sex, department from student distribute by age;
# 分桶排序查询 Distribute By + Sort By
set mapred.reduce.tasks=3; // 设置桶的数量，如果不设置，则就是创建分桶表时指定的桶的
数量
insert overwrite local directory '/home/bigdata/output_ds/' select * from
student distribute by id sort by age desc, id desc; // 这是分桶和排序的组合操
作，对id进行分桶，对age，id进行降序排序
# 分桶查询 cluster by
insert overwrite local directory '/home/bigdata/output_c/' select * from student
cluster by id;
# 聚合查询 group by
# 求出每个部门的总人数和平均年龄
select department, count(*) as total, avg(age) as avg_age from student group by
department;

Hive Join查询

语法结构：

join_table：
table_reference JOIN table_factor [join_condition]
| table_reference {LEFT|RIGHT|FULL} [OUTER] JOIN table_reference
join_condition
| table_reference LEFT SEMI JOIN table_reference join_condition

Hive支持等值连接（equality join）、外连接（outer join）和（left/right join）。Hive不支持非等值的连接，因为非等值连接非常难转化到map/reduce任务。另外，Hive支持多于2个表的连接。
写查询时要注意以下几点：
第一：只支持等值链接，支持and，不支持or

SELECT a.* FROM a JOIN b ON (a.id = b.id)
SELECT a.* FROM a JOIN b ON (a.id = b.id AND a.department = b.department)

然而下面这个SQL是错误的：

SELECT a.* FROM a JOIN b ON (a.id > b.id);

2、可以join多于2个表

SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key =
b.key2)

如果join中多个表的 join key 是同一个，则 join 会被转化为单个 map/reduce 任务，例如上述HQL就会被转化为单个 map/reduce 任务，因为 join 中只使用了 b.key1 作为 join key。
而下面这个HQL语句则会执行成多个MapReduce，因为 b.key1 用于第一次 join 条件，而 b.key2 用于第二次 join

SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key =
b.key2)

3、Join时，每次map/reduce任务的逻辑
Reducer 会缓存 join 序列中除了最后一个表的所有表的记录，再通过最后一个表将结果序列化到文件
系统。这一实现有助于在 reduce 端减少内存的使用量。实践中，应该把 最大的那个表写在最后（否则
会因为缓存浪费大量内存）。例如：

SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key =
b.key1)

所有表都使用同一个 Join key（使用 1 次 map/reduce 任务计算）。Reduce 端会缓存 a 表和 b 表的记录，然后每次取得一个 c 表的记录就计算一次 Join 结果，类似的还有：

SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key =
b.key2)

这里用了 2 次 map/reduce 任务：第一次缓存a表，用b表序列化；第二次缓存第一次 map/reduce 任务的结果，然后用c表序列化。
4、Hive Join分类详解
Hive Join分三种：inner join, outer join, semi join，其中：outer join包括left join，right join 和 full outer join，主要用来处理 join 中空记录的情况。因为hive不支持in/exists操作（新版本的hive支持in的操作），所以用该semi join操作实现，并且是in/exists的高效实现
实例演示：
创建两张表：

create table tablea (id int, name string) row format delimited fields terminated
by ',';
create table tableb (id int, age int) row format delimited fields terminated by
',';

准备两份数据：
tablea.txt 文件数据如下：

1,huangbo
2,xuzheng
4,wangbaoqiang
6,huangxiaoming
7,fengjie
10,liudehua

tableb.txt 文件数据如下：

2,20
4,50
7,80
10,22
12,33
15,44

导入数据到对应的表中：

load data local inpath '/home/bigdata/tablea.txt' into table tablea;
load data local inpath '/home/bigdata/tableb.txt' into table tableb;

请看 Hive 的五种 Join 的实现：
inner join

select * from tablea a inner join tableb b on a.id=b.id;

left join

select * from tablea a left join tableb b on a.id=b.id;

right join

select * from tablea a right join tableb b on a.id=b.id;

left semi join

select * from tablea a left semi join tableb b on a.id=b.id;

full join

select * from tablea a full outer join tableb b on a.id=b.id;

4.其他辅助命令

查看数据库列表

show databases;
show databases like 'my*';

查看数据表

show tables;
show tables in db_name;

查看数据表的建表语句

show create table table_name;

查看hive函数列表

show functions;

查看某函数（比如：substring）的详细使用方式

desc function extended substring;

查看hive表的分区

show partitions table_name;
show partitions table_name partition(city='beijing');

查看表的详细信息（元数据信息）

desc table_name;
desc extended table_name;
desc formatted table_name;

查看数据库的详细属性信息

desc database db_name;
desc database extended db_name;

其它

1、进入到用户的主目录，使用命令可以查看到hive执行的历史命令

cat /home/bigdata/.hivehistory

2、执行查询时若想显示表头信息时，请执行命令：

set hive.cli.print.header=true;

3、hive的执行日志的存储目录在 {user.name}/hive.log 中，假如使用
bigdata用户操作的hive，那么日志文件的存储路径为：

/tmp/bigdata/hive.log

HQL语句会被转换成MapReduce程序执行，但是上面的例子可以看出部分HQL语句并不会
转换成MapReduce，那么什么情况下可以避免转换呢？

1、select * from student; // 简单读取表中文件数据时不会，直接暴力全表扫描
2、select * from student where ptn_column = ""; // 过滤条件中只是分区字段时不会转换成
MapReduce
3、set hive.exec.mode.local.auto=true; // hive会尝试使用本地模式执行，这也可以作为一
种优化手段

否则，其他情况都会被转换成MapReduce程序执行。
在执行一些会转成MapReduce程序来执行的HQL语句的时候，执行log中，会有如下三个重要的信息：

In order to change the average load for a reducer (in bytes):
set hive.exec.reducers.bytes.per.reducer=<number>
In order to limit the maximum number of reducers:
set hive.exec.reducers.max=<number>
In order to set a constant number of reducers:
set mapreduce.job.reduces=<number>

解释：

参数	解释	默认值
hive.exec.reducers.bytes.per.reducer	一次hive查询中，每一个reduce任务，它处理的平均数据量	256000000
hive.exec.reducers.max	一次hive查询中，最多使用的reduce，task的数量	1009
mapreduce.job.reduces	设置的reducetask数量	-1

set hive.exec.reducers.bytes.per.reducer= 每个reduce task的平均负载数据量
Hive会估算总数据量，然后用该值除以上述参数值，就能得出需要运行的reduceTask数
set hive.exec.reducers.max=设置reduce task数量的上限
set mapreduce.job.reduces= 指定固定的reduce task数量
但是，这个参数在必要时<业务逻辑决定只能用一个reduce task> hive会忽略，比如在设置了set
mapreduce.job.reduces = 3，但是HQL语句当中使用了order by 的话，那么就会忽略该参数的设置，因为order by 是全局排序，所以会使用一个 reduceTask 进行排序。
如果需要上述这些参数的值，使用命令：

set mapreduce.job.reduces = 3;

如果要查询当前这个参数的值，则使用命令：

set mapreduce.job.reduces;