hive与Oracle

**一、hive底层逻辑 **

GROUP BY 原理

select name,count(num) from tb group by name

map 阶段，将 group by 后的字段组合作为 key，如果 group by 单字段那么 key 就一个。
将 group by 之后要进行的聚合操作字段作为值，如要进行 count，则 value 是 1；如要 sum 另一个字段，则 value 就是该字段。
shuffle 阶段，按照 key 的不同分发到不同的 reducer。注意此时可能因为 key 分布不均匀而出现数据倾斜的问题。
reduce 阶段，将相同 key 的值累加或作其他需要的聚合操作，得到结果。

JOIN原理

count(distinct) 原理

select num,count(distinct name) as count_name from tb

MapReduce过程详解及其性能优化

Hadoop作业称为Job，Job分为Map、Shuffle和Reduce阶段，MAP和Reduce的Task都基于JVM进程运行的。
MAP阶段：从HDFS读取数据，split文件产生task，通过对应数量的Map处理，map输出的每一个键值对通过key的hash值计算一个partition，数据通过环形缓冲区，sort、spill、merge生成data和index文件；
Reduce阶段：reduce通过Shuffle Copy属于自己的那部分数据，然后sort、spill、merge形成一个大文件，调用reduce函数进行处理.

二、数据倾斜场景以及解决方法

常见的数据倾斜场景有group by , join , count(distnct)

1 group by

原因:默认情况下,map阶段相同的 key 会分发到同一个reduce 端聚合,热点 key 导致 group by 聚合倾斜

解决办法:

---开启map 端聚合: set hive.map.merge.aggr= true; 一般是默认开启 ,减少数据传输及在reduce阶段的处理时间.

set hive.groupby.mapaggr.checkinterval=10000; 设置在map端聚合操作的条数.当大于该值时才会在map端聚合

---开启负载均衡: **set hive.groupby.skewindata=true; **该设置会在数据出现倾斜时,将一个group by 的执行计划拆分成2个阶段MR,第一个阶段将key 随机分发到不同的reduce进行聚合,如何第二阶段MR会基于前一阶段预处理的结果,将相同的key分发到同一个reduce算子,执行聚合操作

---小文件过多时,合并小文件: set hive.merge.mapredfiles=true;

**2 join **

a .小表 join 大表

原因:主要是热点key 导致,在shuffle 过程中,相同的key 会基于hash 分发到同一个 reduce 算子上,导致join 倾斜

解决办法:

---开启map 端** join: set hive.auto.convert.join=true;** 一般默认是打开的

开启mapjoin后,join时会在map端将小表缓存到内存中(缓存为hash table) ,在map 端执行 join.

小表默认是1000行或者25MB大小,可以设置参数提高小表的阈值:

set hive.mapjoin.smalltable.filesize=25000000;

b 大表 join 大表

原因:跟大表join 下表倾斜原因差不多,存在热点key ,或者大量无用的空key,导致倾斜

解决办法:

---运行时优化:** set hive.optimize.skewjoin=true;** 开启倾斜关联开启对倾斜数据join的优化

设置join 倾斜阈值: set hive.skewjoin.key=100000; 当join对应的key记录的条数超过10000,就可以人为这个key 发生了数据倾斜,会对其进行分拆优化

---编译时优化: set hive.optimize.skewjoin.compiletime=true; 可以在编译SQL时将计划优化完成,但是需要在建表时指定数据倾斜元数据信息

---union 优化:** set hive.optimize.union.remove=true;**

一般情况下,这三种优化都要同时开启

除了以上参数外,还可以通过 子查询剔除空key, 空key 转为随机字符串,大表拆分成小表 ,分别join 再 union 等方式进行优化

3 **count(distinct) **

原因:所有key 会分发到同一个reduce 进行去重统计个数,数据量较大时会导致运行时间过长

解决办法:改成group by

SELECT COUNT(DISTINCT id)
FROM table;
改成
SELECT COUNT(t1.id) 
FROM ( SELECT id 
       FROM tabe
       GROUP BY id
      ) t1
;

补充:**4 map和reduce **

原因1:当出现小文件时,需要合并小文件

解决办法:

reduce端输出合并: set hive.merge.mapredfiles=true;

原因2:输入数据存在大块和小块的严重问题

解决办法:

任务输入前做文件合并,将小文件合并成大文件:** set hive.merge.mapredfiles=true;**

原因3:单个文件大小稍大于配置的block 块的大小

解决办法:

此时需要增加map task的个数: set mapred.map.tasks=25;

原因4:文件大小适中,但是map端计算量非常大

解决办法:

增加map和reduce个数:set mapre.map.tasks=250;

** set mapre.reduce.tasks=250;**

**三、hive调优 **

1.建表优化

** a 分区优化**

建分区表,使用这个字段做分区字段,在查询的时候,使用分区字段来过滤就可以避免全表扫描,只需要扫描这张表的一个分区的数据即可

1.分区表定义

分区表实际上就是对应一个hdfs文件系统上的独立的文件夹,该文件夹下是该分区所有的数据文件.hive中的分区就是分目录,把一个大的数据集根据业务需要分割成小的数据集,在查询时通过where子句中的表达式选择查询所需要的指定的分区,这样查询效率会提高很多.(谓词下推)

表分区就是会在表目录下面针对不同的分区创建一个子目录,如果有二级分区,那么会在一级子目录下面计息创建子目录.hive中的分区字段是表外字段,mysql中的分区字段是表内字段

2.创建分区表语法

create table dept_partition(deptno int,dname string,loc string)
partitioned by (month string)---按月分组
row format delimited fields

分区字段是 month,分区的数据类型是string,分区字段不能是表里的字段

3.创建二级分区语法

create table dept_partition(deptno int,dname string,loc string)
partitioned by (month string,year string)---按月和年分组
row format delimited fields

create table if not exists employee_partitioned (
  `name` string,
  `employee_id` int,
  `number` string)
partitioned by (
  `year` int,
  `month` int)
row format delimited fields terminated by '|'
stored as textfile;

year 为一级分区，month为二级分区

4.静态分区与动态分区

4.1 静态分区(默认)

4.1.1静态分区创建表

create table ods_users(
userid string,
username string,
birthday string,
sex string 
)
row format delimited fields terminated by ',' 
location '/pw';

4.1.2静态分区操作：增加或删除分区语法

-- 创建分区
//单个分区创建
alter table employee_partitioned add partition(year='2019',month='6') ;
//多个分区创建
alter table employee_partitioned add partition(year='2020',month='6') partition(year='2020',month='7');
-- 删除分区
//单个分区删除
alter table employee_partitioned drop partition (year='2019',month='6');
//多个分区删除
alter table employee_partitioned partition (year='2020','month=6'), partition (year='2020',month='7');

4.1.3静态分区加载插入数据

load data local inpath '/文件路径' into table '表名' partition'(分区信息)'

hive> load data local inpath '/usr/local/wyh/test_data/user_partition1.txt' 
into table part-demo1 partition(testdate='2022-04-28');

-- 静态分区加载二级分区

load data local inpath '/文件路径' into table '表名' partition'(分区信息1,分区信息2)'

hive> load data local inpath '/usr/local/wyh/test_data/user_partition2.txt' 
into table part_demo2 partition(year='2022',month='03');

静态分区加载插入数据

insert into 将数据添加到表中
insert overwrite 会覆盖表中已有的数据

insert into userinfos partition(sex='male') 
select userid,username,birthday from ods_users where sex='male';

insert into userinfos partition(sex='female') 
select userid,username,birthday from ods_users where sex='female';

//将本地文件传入hdfs中
Hadoop dfs -put /data/employee_hr.txt /tmp/employee
//将文件映射为相应的表
create table if not exists employee_hr(
    name string,
    employee_id int,
    number string,
    start_date date)
    row format delimited fields terminated by '|'
    location '/data/employee_hr.txt';

//根据查询所得的结果传入相应的文件夹中
insert overwrite table employee_partitioned  partition(year=2020,month=8)
select name,employee_id,number from employee_hr 
where year(start_date)=2020 and month(start_date)=8;

4.2动态分区

4.2.1开启动态分区

动态分区默认是没有开启。开启后默认是以严格模式执行的，在这种模式下需要至少一个分区字段是静态的

关闭严格分区模式

动态分区模式时是严格模式，也就是至少有一个静态分区。
set hive.exec.dynamic.partition.mode=nostrict //分区模式，默认nostrict
set hive.exec.dynamic.partition=true //开启动态分区,默认true
set hive.exec.max.dynamic.partitions=1000 //最大动态分区数,默认1000

    set hive.exec.max.dynamic.partitions.pernode =100 ：单个节点上的mapper/reducer允许创建的最大分区

strict：最少需要有一个是静态分区

nostrict：可以全部是动态分区

4.2.2动态分区的操作

（1）开启动态分区功能（默认true，开启）
set hive.exec.dynamic.partition=true
（2）设置为非严格模式（动态分区的模式，默认strict，表示必须指定至少一个分区为静态分区，nonstrict模式表示允许所有的分区字段都可以使用动态分区。）
set hive.exec.dynamic.partition.mode=nonstrict
（3）在所有执行MR的节点上，最大一共可以创建多少个动态分区。默认1000
set hive.exec.max.dynamic.partitions=1000
（4）在每个执行MR的节点上，最大可以创建多少个动态分区。该参数需要根据实际的数据来设定。比如：源数据中包含了一年的数据，即day字段有365个值，那么该参数就需要设置成大于365，如果使用默认值100，则会报错。
set hive.exec.max.dynamic.partitions.pernode=100
（5）整个MR Job中，最大可以创建多少个HDFS文件。默认100000
set hive.exec.max.created.files=100000
（6）当有空分区生成时，是否抛出异常。一般不需要设置。默认false
set hive.error.on.empty.partition=false

4.2.2创建动态分区表

create table if not exists dyp1

(uid int,

commentid bigint,

recommentid bigint)

partitioned by(year int,month int,day int)

row format delimited fields terminated by '\t';

create table myusers( 
userid string,
username string,
birthday string
) 
partitioned by (sex string)
row format delimited fields terminated by ','
 stored as textfile;

--创建临时表

create table if not exists tmp
(uid int,
commentid bigint,
recommentid bigint,
year int,
month int,
day int)
row format delimited fields terminated by '\t';

4.2.3动态分区加载数据

load data local inpath '/root/Desktop/comm' into table tmp;

4.2.4插入数据(不指定分区值,该字段相同值就会分为一个分区)

insert into myusers partition(sex) select * from ods_users;

--严格模式动态分区插入数据

insert into table dyp1 partition(year=2016,month,day)

select uid,commentid,recommentid,month,day from tmp;

--为非严格模式动态分区插入数据

insert into table dyp2 partition(year,month,day)

select uid,commentid,recommentid,year,month,day from tmp;

5.静态分区和动态分区的区别及应用场景

5.1静态分区和动态分区的区别(静态分区的列是在编译时期，通过用户传递来决定的；动态分区只有在SQL执行时才能决定)
静态分区动态分区①静态分区是在编译期间指定的指定分区名①根据分区字段的实际值，动态进行分区②支持load和insert两种插入方式②是在sql执行的时候进行分区load方式
会将分区字段的值全部修改为指定的内容
一般是确定该分区内容是一致的时候才会使用③需要先将动态分区设置打开insert方式
必须先将数据放在一个没有设置分区的普通表中
该方式可以在一个分区内存储一个范围的内容
从普通表中选出的字段不能包含分区字段④只能用insert方式③适用于分区数少，分区名可以明确的数据⑤通过普通表选出的字段包含分区字段，分区字段放置在最后，多个分区字段按照分区顺序放置
5.2静态分区与动态分区的应用场景

静态分区：适合已经确认分区的文件，分区相对较少的，适合增量导入的应用场景

动态分区：适合根据时间线做分区，分区比较多的，适合全量导入的场景

6.查看分区

show table dept_partition;

7.显示分区

show partitions iteblog;

7.删除分区

alter  table dept_partition drop partition(month='2017-07') ,
partition(month='2017-08') ,
partition(month='2017-09');----注意逗号

8.增加分区

alter  table dept_partition 
add partition(month='2017-07') 
partition(month='2017-08') 
partition(month='2017-09');---直接空格,没有逗号

9.修改分区

ALTER TABLE iteblog PARTITION (dt='2008-08-08') 
SET LOCATION "new location";

ALTER TABLE iteblog PARTITION (dt='2008-08-08') 
RENAME TO PARTITION (dt='20080808');

10.修复分区

修复分区就是重新同步hdfs上的分区信息

msck repair table userinfos;

11.分区表的注意事项

--让分区表和数据产出关联的三种方式

先创建hdfs目录，再上传文件，然后刷新分区表的元数据（MSCK REPAIR TABLE 表名）

先创建hdfs目录，再上传文件，然后创建分区（alter table 表名 add partition。。。）

先创建hdfs目录，然后通过load语句加载数据文件。

--分区注意细节

（1）、尽量不要用动态分区，因为动态分区的时候，将会为每一个分区分配reducer数量，当分区数量多的时候，reducer数量将会增加，对服务器是一种灾难。

（2）、动态分区和静态分区的区别，静态分区不管有没有数据都将会创建该分区，动态分区是有结果集将创建，否则不创建。

（3）、hive动态分区的严格模式和hive提供的hive.mapred.mode的严格模式。

b 分桶优化

---

​​​​​​​

#创建分桶表
create table stu_buck(id int, name string)
clustered by(id)
into 4 buckets
row format delimited fields terminated by '\t';#创建中间表create table stu(id int, name string) row format delimited fields terminated by '\t';#导入数据到中间表load data local inpath '/opt/module/datas/student.txt' into table stu;#开启分桶set hive.enforce.bucketing=true;#设置reduce数量为-1set mapreduce.job.reduces=-1;#向分桶表中导入数据insert into table stu_buck select id, name from stu;

---

1.创建一个分桶表

create table test_buck(id int, name string)
clustered by(id) ----按照id分桶
into 6 buckets----分6个桶
row format delimited fields terminated by '\t';
注意：CLUSTERED BY来指定划分桶所用列和划分桶的个数。

2.导入数据---严格模式

开启分桶表的功能

set hive.enforce.bucketing=true;

设置reduce的个数

set mapreduce.job.reduces=6;

创建一个临时表,并导入数据

create table temp_buck(id int, name string)
row format delimited fields terminated by '\t';
load data local inpath '/tools/test_buck.txt' into table temp_buck;

使用insert select 来间接把数据导入到分桶表

insert into table test_buck select id, name from temp_buck;

3.查询分桶表的数据

select * from test_buck

4.分桶抽样:

select * 
from  student
tablesmaple (bucket 3 out of 32);

随机采样:

select *
from student
distribute by rang()
sort by rand()
limit 100
;

数据块抽样:

select *
from student 
tablesmaple(10 percent);
--百分比

select *
from student 
tablesmaple(10 rows);
--行数

select *
from student 
tablesmaple(10 M);
--大小块

c 存储优化

textfile: 行存储 .每行都以'\n'结尾,数据不做压缩时,磁盘开销较大

可以使用Gzip压缩算法，但压缩后的文件不支持split

在反序列化过程中，必须逐个字符判断是不是分隔符和行结束符，因此反序列化开销会比SequenceFile高几十倍。

建表代码

${建表语句}
stored as textfile;
##########################################插入数据########################################
set hive.exec.compress.output=true; --启用压缩格式
set mapred.output.compression.codec=org.apache.hadoop.io.compress.GzipCodec; 
 --指定输出的压缩格式为Gzip 
set mapred.output.compress=true;   
set io.compression.codecs=org.apache.hadoop.io.compress.GzipCodec;     
insert overwrite table textfile_table select * from T_Name;

sequence file:二进制文件,以key-value的形式序列化到文件中,

存储方式：行存储

,可分割的文件格式，支持Hadoop的block级压缩。

sequencefile支持三种压缩选择：NONE，RECORD，BLOCK。Record压缩率低，RECORD是默认选项，通常BLOCK会带来较RECORD更好的压缩性能。

优势是文件和hadoop api中的MapFile是相互兼容的

建表代码

${建表语句}
SORTED AS SEQUENCEFILE;    --将Hive表存储定义成SEQUENCEFILE
##########################################插入数据########################################
set hive.exec.compress.output=true; --启用压缩格式
set mapred.output.compression.codec=org.apache.hadoop.io.compress.GzipCodec; 
--指定输出的压缩格式为Gzip 
set mapred.output.compression.type=BLOCK;   --压缩选项设置为BLOCK
set mapred.output.compress=true; 
set io.compression.codecs=org.apache.hadoop.io.compress.GzipCodec; 
insert overwrite table textfile_table select * from T_Name;

RC file: 数据块按行分块,列式存储.

首先，RCFile 保证同一行的数据位于同一节点，因此元组重构的开销很低
其次，像列存储一样，RCFile 能够利用列维度的数据压缩，并且能跳过不必要的列读取
数据追加：RCFile不支持任意方式的数据写操作，仅提供一种追加接口，这是因为底层的 HDFS当前仅仅支持数据追加写文件尾部。
行组大小：行组变大有助于提高数据压缩的效率，但是可能会损害数据的读取性能，因为这样增加了 Lazy 解压性能的消耗。而且行组变大会占用更多的内存，这会影响并发执行的其他MR作业。 考虑到存储空间和查询效率两个方面，Facebook 选择 4MB 作为默认的行组大小，当然也允许用户自行选择参数进行配置。

建表代码

${建表语句}
stored as rcfile;
-插入数据操作：
set hive.exec.compress.output=true; 
set mapred.output.compression.codec=org.apache.hadoop.io.compress.GzipCodec; 
set mapred.output.compress=true; 
set io.compression.codecs=org.apache.hadoop.io.compress.GzipCodec; 
insert overwrite table rcfile_table select * from T_Name;

ORC file: 数据块按行分块,列式存储.RC file的升级版,压缩快,快速列存取

parquet file:列式存储. 对于大型查询是高效的,但是对于扫描特定表格中的特定列查询,特别有用,默认snappy压缩,Parquet文件是以二进制方式存储的，所以是不可以直接读取的，文件中包括该文件的数据和元数据，因此Parquet格式文件是自解析的

hive表的数据存储格式一般选择：orc或parquet。压缩方式一般选择snappy，lzo。

d 压缩优化

压缩方式
压缩方式压缩后大小压缩速度GZIP中中BZIP2小慢LZO大快Snappy大快
Hadoop编码解码器
压缩格式对应的编码解码器DEELATEorg.apache.hadoop.io.compress.DefaultCodecGZIPorg.apache.hadoop.io.compress.GzipCodecBZIP2org.apache.hadoop.io.compress.BZip2CodecLZOcom.hadoop.compression.lzo.LzopCodec---中间输出使用Snappyorg.apache.hadoop.io.compress.SnappyCodec--中间输出使用
hive压缩设置的方法

(1). Hive中间数据压缩

hive.exec.compress.intermediate：默认该值为false，设置为true为激活中间数据压缩功能

开启Hive的中间数据压缩功能，就是在MapReduce的shuffle阶段对mapper产生的中间结果数据压缩。在这个阶段，优先选择一个低CPU开销的算法。

mapred.map.output.compression.codec：该参数是具体的压缩算法的配置参数，SnappyCodec比较适合在这种场景中编解码器，该算法会带来很好的压缩性能和较低的CPU开销。设置如下：

set hive.exec.compress.intermediate=true;

set mapred.map.output.compression.codec

=org.apache.hadoop.io.compress.SnappyCodec;

set mapred.map.output.compression.codec

**=com.hadoop.compression.lzo.LzoCodec; **

(2). Hive最终数据压缩

hive.exec.compress.output：用户可以对最终生成的Hive表的数据通常也需要压缩。该参数控制这一功能的激活与禁用，设置为true来声明将结果文件进行压缩。
mapred.output.compression.codec：将hive.exec.compress.output参数设置成true后，然后选择一个合适的编解码器，如选择SnappyCodec。设置如下：
set hive.exec.compress.output=true ;

set mapred.output.compression.codec

=org.apache.hadoop.io.compress.SnappyCodec;

压缩模式说明

(1). 压缩模式评价

压缩比：压缩比越高，压缩后文件越小，所以压缩比越高越好。
压缩时间：越快越好。
已经压缩的格式文件是否可以再分割：可以分割的格式允许单一文件由多个Mapper程序处理，可以更好的并行化。

压缩格式: bzip2, gzip, lzo, snappy等
压缩比：bzip2>gzip>lzo bzip2最节省存储空间
解压速度：lzo>gzip>bzip2 lzo解压速度是最快的

(2). 压缩模式对比

BZip2有最高的压缩比但也会带来更高的CPU开销，Gzip较BZip2次之。如果基于磁盘利用率和I/O考虑，这两个压缩算法都是比较有吸引力的算法。
LZO和Snappy算法有更快的解压缩速度，如果更关注压缩、解压速度，它们都是不错的选择。 LZO和Snappy在压缩数据上的速度大致相当，但Snappy算法在解压速度上要较LZO更快。
Hadoop的会将大文件分割成HDFS block(默认64MB)大小的splits分片，每个分片对应一个Mapper程序。在这几个压缩算法中 BZip2、LZO、Snappy压缩是可分割的，Gzip则不支持分割。

2.sql优化

a. 查看hive执行计划

增加关键词 extended 可以查看更加详细的执行计划

explain [extended] query

b. 列裁剪

取数值取查询需要用到的列,默认是true

set hive.optimize.cp=true

c. 分区裁剪

对应SQL解析中逻辑优化器columnpruner

select * from student where department='AAAA';

set hive.optimize.pruner=true

d. 谓词下推

where 语句提前执行,减少下游处理的数据量,对应的逻辑优化器是 predicatepushdown

select a.*,b.*
from a 
join b
on a.id=b.id
where b.age>20;

改成

select a*,c.*
from a
join (select * 
      from b
      where age>20
      ) c
on a.id=c.id

e. 合并小文件

map端输入合并,合并文件之后按照block的大小分割

set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;

map和reduce 输出合并

--合并map 输出文件,默认值为true

set hive.merge.mapfiles=true;

--合并reduce 端输出文件,默认值是false

set hive.merge.maprefiles=true;

--合并文件的大小

set hive.merge.size.per.task=256000000;

--每个map 最大分割大小

set mapred.max.size.split=256000000;

--一个节点上spilt 的最少值

set mapred.min.spilt.size.per.node=1;

--一个机架上的spilt的最少值

set mapred.min.spilt.size.per.rack=1

f. map task 并行度机制

一个MR job 的map task 数量由输入分片 inputspilt决定的,输入分片由 fileinputformat.getspilt()决定的计算方法是 long spiltsize =math.max(minsize,math.min(maxsize,blocksize))

合理控制maptask数量

--减少maptask数量可以通过合并小文件来实现

--增加maptask 数量可以通过控制上一个reduce task 默认的maptask 个数

--块数据列式存储有利于数据压缩和快速的列存取

--可以通过设置mapred.min,spilt.size 来设置每个任务处理的文件的大小,这个大小只有在大于dfs_block_size的时候才会生效

spilt_size=max(mapred.min.spilt.size,dfs_block_size)

spilt_num=total_size/spilt_size

compute_map_num=

math.min(spilt_num,math.max(default_mapper_num,mapred.map.tasks))

--复杂文件增加map数量

增加map的方法为：根据computeSliteSize(Math.max(minSize,Math.min(maxSize,blocksize)))=blocksize=128M公式，调整maxSize最大值。
让maxSize最大值低于blocksize就可以增加map的个数。
set mapreduce.input.fileinputformat.split.maxsize=100;

控制 mapper 数量的方法

set mapred.map.tasks=10;

##默认是2

--如果想增加maptask 个数,可以设置mapred.map.tasks为一个较大的值

--如果想减少maptask 个数,可以设置apred.min,spilt.size为一个较大的值

--如果输入大量小文件,减少mapper个数合并小文件,可以设置set hive.input.format

g. reduce task 并行度

reduce task数量极大的影响执行效率,

基于以下设定

参数1:hive.exec.reducers.bytes.per.reducer(默认256M)

参数2:hive.exec.reducers.max(默认为1009)

参数3:mapreduce.job.reduces(默认值为-1,表示没有设置,那就按照参数1,参数2进行设置)

N=Math.min(参数2,总输入数据大小/参数1)

通过改变上述两个参数的值可以控制reduce task 的数量

set mapred.map.tasks=10;

**set mapreduce.job.reduces=10; **

h. join优化

优先过滤数据,能用分区的用分区,最大限度的减少join 参与的数据量

小表join 大表原则,启用mapjoin

--开启mapjoin

set hive.auto.convert.join=true;

--刷入内存表的大小

set hive.mapjoin.samlltable.filesize=25000000;

--hive会基于表的size自动将普通join 转换成 mapjoin

set hive.auto.convert.join.noconditionaltask=true;

--多大的表可以自动触发到内层 LocalTask中,默认大小为10M

set hive.auto.convert.join.noconditionaltask.size=10000000;

使用相同的连接键,多表连接时

使用原子操作,多使用中间表

大表join大表,空值过滤,空值随机转换

--空key过滤:空key对应的数据无意义

select n.*
from (select * from nullidtable where id is not null ) n  
left join ori o on n.id = o.id;

​​​​​​​

--空key过滤:空key对应的数据无意义

#为空key赋予随机值，在进入reduce的时候防止空key太多而造成数据倾斜
select n.* from nullidtable n full join ori o on 
case when n.id is null then concat('hive', rand()) else n.id end = o.id;

--join的键对应的记录条数超过这个值就会进行分拆,

set hive.skewjoin.key=100000;

--如果是join过程出现倾斜应该设置为true

set hive.optimize.skewjoin=true;

i.group by优化

---开启map 端聚合: set hive.map.merge.aggr= true; 一般是默认开启 ,减少数据传输及在reduce阶段的处理时间.

set hive.groupby.mapaggr.checkinterval=10000; 设置在map端聚合操作的条数.当大于该值时才会在map端聚合

---开启负载均衡: **set hive.groupby.skewindata=true; **该设置会在数据出现倾斜时,将一个group by 的执行计划拆分成2个阶段MR,第一个阶段将key 随机分发到不同的reduce进行聚合,如何第二阶段MR会基于前一阶段预处理的结果,将相同的key分发到同一个reduce算子,执行聚合操作

---小文件过多时,合并小文件: set hive.merge.mapredfiles=true;

j. order by 优化

order by :全局排序,只能使用一个reduce

sort by:单机排序,

cluster by :对同一个字段分桶并排序,不能和sort by 连用

distribute by +sort by :分桶,保证同一字段值只存在一个结果文件中

全局排序的方式

--使用distribute by =sort by ,多个reducetask ,每个reducetask分布有序

set mapreduce.job.reduces=3;

drop table student_orderby_result;

--范围分桶 0<18<1<20<2

create table student_orderby_result as
select *
from student 
distribute by ( case when age>20 then 0
                     when age>18 then 2
                else 1
                end
              )
sort by (age desc)

k. count(dictinct) 优化

改成group by

SELECT COUNT(DISTINCT id)
FROM table;
改成
SELECT COUNT(t1.id) 
FROM ( SELECT id 
       FROM tabe
       GROUP BY id
      ) t1
;

数据量小的时候无所谓，数据量大的情况下，由于COUNT DISTINCT的全聚合操作，即使设定了reduce task个数，set mapred.reduce.tasks=100；hive也只会启动一个reducer，这就造成一个Reduce处理的数据量太大，导致整个Job很难完成，一般COUNT DISTINCT使用先GROUP BY再COUNT的方式替换：

#设置reduce个数为5
set mapreduce.job.reduces = 5;
#采用distinct去重
select count(distinct id) from bigtable;
#采用group by去重
select count(id) from (select id from bigtable group by id) a;

3.架构调优

a. fetch 启用本地抓取

--查看hive的抓取策略

set hive.fetch.task.conversion

--设置hive的抓取策略

set hive.fetch.task.conversion =more

只有在more模式时,select*, 在分区字段上where 过滤,有limit 这三种场景下才启用直接抓取的方式

b. 本地执行优化

数据量小的情况下

--打开hive自动判断是否启动本地模式的开馆

set hive.exec.mode.local.auto=true;

--map任务数最大值,不启用本地模式的task最大个数

set hive.exec.mode.local.auto,input.files.max=4;

--map输入文件最大大小,不启动本地模式的最大输入文件大小

set hive.exec.mode.local.auto,inputbytes.max=12325635;

c. JVM重用

默认情况下,mapreduce中的一个maptask或者reduce task就会启动一个jvm进程,一个task执行完毕后就会退出jvm程序,如果任务花费时间短,又要多起启动jvm的情况下,就要启动jvm来解决

set mapred.job.reuse.jvm.nun.tasks=6;

d. 并行执行

有的查询语句,hive会将其转换为一个或者多个阶段,包括mapreduce阶段,抽样阶段,合并阶段,limit阶段等,默认情况下, 一次只执行一个阶段,但是如果某些阶段不是互相依赖,是可以并行执行的,多节点并行是比较耗系统资源的

--开启并发执行

set hive.exec.parallel=true;

--同一个sql允许最大并行度,默认为8

set hive.exec.parallel.thread.number=16;

e.推测执行

当有bug出现时,或者数据倾斜时,hadoop要推测出拖后腿任务

--启用mapper 阶段的推测执行机制

set mapreduce.map.speculative=true;

--启用reduce 阶段的推测执行机制

set mapreduce.reduce.speculative=true;

f.hive严格模式

--设置hive的严格模式

set hive.mapred.mode=strict;

set hive.exec.dynamic.partition.mode=nostrict;

设置hive严格模式之后有以下限制

--对于分区表,必须添加where对于分区字段的条件过滤

--order by 语句必须包含limit 输出限制

--限制执行笛卡尔积的查询

--在hive动态分区模式下,如果为严格模式,必须需要一个分区列式静态分区

四、hive组件及其功能

1.用户接口:client

CLI(hive shell) , JDBC/ODBC (Java 访问hive) , WEBUI(浏览器访问hive)

2.元数据(metastore)

元数据包括:表名,表所属的数据库(默认时default),表的拥有者,列/分区字段,表的类型(是否是外部表),表的数据所在目录等

默认存储在自带的derby 数据库中,推荐使用MYSQL存储Metastore

3.Hadoop

使用HDFS进行存储,使用mapreduce 进行计算

4.驱动器:Driver

(1)解析器(sql parser):将sql字符串转换成抽象语法树AST,这一步一般都用第三方工具库完成,比如antlr ;对AST 进行语法分析,比如表是否存在,字段是否存在,sql语义是否有误

(2)编译器(physical plan):将AST 编译生成逻辑执行计划

(3)优化器(query optimizer):对逻辑执行计划进行优化

(4)执行器(execution):将逻辑执行计划转化成可以运行的物理计划,对于hive来说就是MR/Spark

五、hive与yarn ,hdfs,zookeeper,mapreduce之间的关系

1.hive与yarn

hive执行程序在yarn上

2.hive与hdfs

hive处理的数据存储在HDFS

3.hive与mapreduce

hive分析数据底层的默认实现是mapreduce

4.hive与zookeeper

hive可以开启zookeeper客户端,分布式应用程序协调服务

六、hive函数与Oracle函数

函数hive函数Oracle函数数学函数 round(10.235)---10.0 四舍五入取整数round(10.235)---10.0 四舍五入取数floor(3.142)---3 向下取整数trunc(45.962,2)----45.96截取指定小数位ceiling(3.142)---4 向上取整数mod(1600,300)----100 返回除数的余数rand()---啥数都可能,随机,当里面有数据的话,随机数就固定不变了greatest(2,5,6)---6 取最大值greatest(2,5,6)---6 取最大值least(2,5,6)---2 取最小值least(2,5,6)---2 取最小值日期函数from_unixtime(202203300,'YYYY-MM-DD HH:MM:SS')---2022-06-30 18:48:20 把unix时间戳转日期函数
sysdate----返回当前数据库的日期
unix_timestamp()---15946483 获取当前时区的unix时间戳unix_timestamp('2022-01-10 15:12:22')---1651515 把日期字符串转成长整数时间戳unix_timestamp('2022-01-10 ' yyyy-mm-dd)---指定格式日期转unix时间戳from_utc_timestamp(1656456000,'GMT+8')---转换成指定时区下的时间戳函数to_date('2021-12-25 15:56:22')---2021-12-25 返回日期字段中的日期部分year('2021-12-25 15:56:22')---2021 返回日期中的年datediff('2022-01-10','2022-01-08')----2 返回结束日期减去开始日期的天数,结束日期在前data_add('2022-01-10',10)----2022-01-20 日期增加10天+data_sub('2022-01-11',10)----2022-01-01 日期减少10天-add_month('2022-01-10',10)----2022-11-10 月份增加10月add_month('2022-01-10',10)----2022-11-10 月份增加10月current_date---2022-07-12 返回当前日期current_timestamp----2022-07-12 18:12:44返回当前时间last_day(2022-07-12)---2022-07-31 返回当前月份最后一天的日期last_day(2022-07-12)---2022-07-31 返回当前月份最后一天的日期trunc('2022-12-02','MM')----2022-12-01 时间的最开始年份或者月份,截断时域trunc('2022-12-02','YYYY')----2022 按照指定的日期截断months_between('2022-01-10','2022-5-08')---4 返回相差的月份months_between('2022-01-10','2022-5-08')---4 返回相差的月份date_format(to_date('2021-12-25 '),'YYYY/MM/dd')----2021-12-25 将时间按照给定的模式格式化字符串
round('2022-12-02','day')---2022-12-01

最近星期日

round('2022-12-02','year')---2023-01-01

最近年初日期

round('2022-12-02','month')---2022-12-01 最近月初

四舍五入后的期间第一天

extract(year from sysdate)---2022

抽取当前日期的年月日
条件函数if(a>5,1,2)---条件满足就返回1,否则返回2nvl(name,nickname)---优先返回第一个值,如果是null则返回第二个值,只有2个参数,类型可以不一样
nvl(name,nickname)---转换空值位一个实际值

nvl(cosson ,1)---将cosson里面的空值换成1
coalesce(name,city,sexs)---返回第一个不为null的值coalesce(name,city,sexs)---返回第一个不为null的值
case when a>b then 1

    when a>c then 2 

else 3

end

case when a>b then 1

    when a>c then 2 

else 3

end

decode( a>b,true,1,2)---如果a>b 是true 则输出1,否则输出2

decode(1+2,4,1,2)---输出2
nvl2(expr1,expr2,expr3)---如果1是非空就返回2,如果1为空,返回3字符串函数ascii('abcde')--97 返回字符串的第一个字符的asii码ascii('abcde')--97 返回字符串的第一个字符的asii码initcap(auhd)---Auhd 字符串的第一个字母大写,其余规范化initcap(auhd)---Auhd 字符串的第一个字母大写,其余规范化cocat('a','b')--ab 连接字符串cocat('a','b')--ab 连接字符串,只能连接2个concat_ws(':','a','b')---a:b 连接字符串,带符号concat_ws(':','a','b')---a:b 连接字符串,带符号find_in_set('ab','abc,bb,ab,acb,ab') ==> 3 在一个列表字符串中查找第一个子串的位置json解析函数 get_json_object locate(‘aa’,‘aabbedfaad’,2);---8 查找字符串aa在字符串aabbedfaad第2次出现的位置
instr(原字符串,查找内容,开始位置,第几次出现)--字符串指定字符的位置

instr('aabbedfaad','aa',1,2)---8
lower(‘abSEd’);----absed 字符串转小写函数 lower(‘abSEd’);----absed 字符串转小写函数upper(‘abSEd’);----ABSED 字符串转大写函数upper(‘abSEd’);----ABSED 字符串转大写函数lpad(‘abc’,10,‘td’);----tdtdtdtabc 补足函数lpad(‘abc’,10,‘td’);----tdtdtdtabc 补足函数rtrim(' aaa ') ==> ' aaa' 去掉右边的空格rtrim(' aaa ') ==> ' aaa' 去掉右边的空格regexp_extract(‘foothebar’, ‘foo(.*?)(bar)’, 2);----bar 正则表达式抽取解析函数repeat(‘abc’,5);----abcabcabcabcabc 返回重复5次之后的abcreverse(‘abc’);----cba 字符串反转函数split(string str, string pat)----按照pat字符串分割str，会返回分割后的字符串数组substr('aabbccddeeff',2,3);---abb从第2个字符串开始的3个字符 字符串截取substr('aabbccddeeff',2,3);---abb从第2个字符串开始的3个字符 字符串截取replace(原字符串,需要替换的子串,替换的内容)---替换字符串聚合函数个数统计函数---count个数统计函数---count总和统计函数---sum总和统计函数---sum平均值统计函数---avg平均值统计函数---avg最小值统计函数---min最小值统计函数---min最大值统计函数---max最大值统计函数---max类型转换函数binary(string|binary)--转换成二进制函数
to_char(1256,'$9999')---$1256

转换成字符串类型
例如CAST('1' AS INT) 将字符串1转换成整数1 expr转换成type类型,强制转换
to_date('2022-11-12','YYYY-MM-dd')---2022-11-12

转换成日期类型

to_number('1234.5')---1234.5

转换成数值类型
窗口函数/分析函数
sum()over(partition by shop_id order by month rows between unbounded preceding and current row)---把当前行和当前行之前的所有行相加在一起

补充:rows between 行范围

起始行的表达式

n preceding 当前行的前n行

unbound preceding 分区的最前行

结束行的表达式

current row 到当前行

n following 到当前行的后n行

unbounded following 当前分区的最后行

sum()over(partition by shop_id order by month rows between unbounded preceding and current row)---把当前行和当前行之前的所有行相加在一起

补充:rows between 行范围

起始行的表达式

n preceding 当前行的前n行

unbound preceding 分区的最前行

结束行的表达式

current row 到当前行

n following 到当前行的后n行

unbounded following 当前分区的最后行
row_number()over()----连续编号123456row_number()over()----连续编号123456
rank()over()----按名次编号12245
rank()over()----按名次编号12245
dense_rank()over()----12234

排序类开窗函数注意点:分子函数内不能含有任何内容,分析子句内必须添加order by,且不能指定窗口
dense_rank()over()----12234
count()over()---计数前n行之和 跨行计数

count(*),count(1),count(id) --判断某行是否需要纳入计数,就是看count 函数中传入的参数是否为nulll

count()over()---计数前n行之和 跨行计数

count(*),count(1),count(id) --判断某行是否需要纳入计数,就是看count 函数中传入的参数是否为nulll
lead()over()---上拉行,把下n行数据拿到当前行lead()over()---上拉行,把下n行数据拿到当前行
lag()over()---下拉行,把上n行数据拿到当前行

偏移类开窗函数;lag(要分析的字段,偏移量,返回默认值)

注意点:分析函数名内必须包含要分析的内容,其他两项参数可以默认,

分析子句内必须添加order by 且不能指定窗口

若不再有可供偏移的行,则返回默认值

偏移量不允许写负数

默认返回值数据类型与分析的字段要保持一致
lag()over()---下拉行,把上n行数据拿到当前行first_value()over()----取分区中第一行的指定字段的值到当前行first_value()over()----取分区中第一行的指定字段的值到当前行last_value()over()----取分区中最后一行的指定字段的值到当前行,要加入行范围,不然就是当前行last_value()over()----取分区中最后一行的指定字段的值到当前行,要加入行范围,不然就是当前行

七、hive与Oracle差异

1.底层逻辑差异

Oraecle是数据库,而hive是数据仓库,二者最大的区别是存储和计算,Oracle数据库是支持存储计算功能的,hive是基于Hadoop的数据仓库工具,本身不具备计算和存储功能,本质是将hql语句转换成mr程序运行

2.建表区别

hive字段名和表名都可以用as别名

Oracle表名不可以使用as 别名,会报错

3.存储区别

hive存储在HDFS上

Oracle 存储在本地

4.sql代码区别

hive不能更新数据,upset 不能使用

hive 使用join时不支持where进行join关联

5函数区别

6.数据类型

hive不支持函数decode、rownum、to_char、replace、||、nvl、months_between、add_months、rollup、cube、rank() over、dense_rank() over、row_number() over
hiveoracleDDL数据定义1、创建数据库 CREATE DATABASE [IF NOT EXISTS] database_name2、查询数据库显示数据库：show databases;过滤显示查询的数据库：show databases like ‘db_hive’;查看数据库详情：desc database db_hive；或 desc database extended db_hive;3、删除数据库1,DROP TABLE：删除表drop database db_hive2;drop table t_depart;如果数据库不为空，可以采用cascade命令，强制删除：drop database db_hive cascade;4、创建表2, CREATE TABLE：创建表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] -- eg：row format delimited fields terminated by '\t'。按照‘\t’分隔
[STORED AS file_format] -- 指定存储文件类型 SEQUENCEFILE（二进制序列文件）、TEXTFILE（文本）、RCFILE（列式存储格式文件）
[LOCATION hdfs_path] -- 指定表在HDFS上的存储位置。
[TBLPROPERTIES (property_name=property_value, ...)]
[AS select_statement] -- 后跟查询语句，根据查询结果创建表。create table t_user(
userid number(30) not null primary key,
username varchar(20) not null,
age number(3),
sex varchar(2),
departid number(30) not null,
foreign key(departid) references t_depart(departid)
);alter table student add primary key(userid)这样用alter也可以创建关联主外键。5,修改列 alter3,alter 修改增加列 ALTER TABLE table_name ADD (col_name data_type [COMMENT col_comment],...)
ADD COLUMNS允许在当前列之后，分区列之前添加新的列，REPLACE COLUMS删除当前的列，加入新的列。只有在使用native 的SerDe时才能这样做（1）在表中添加新列
alter table t_user
add birthday date;（2）删除表中的列
alter table t_user
drop column birthday;（3）给表中添加约束
alter table t_user
add constraint user_name_ununique(name);（4）删除表中的约束
alter table t_user
drop constraint user_name_un;表重命名 alter table table_nameold rename table_namenew（5）修改表名
rename t_user to mytest;
rename mytest to t_user;修改列信息 ALTER TABLE table_name CHANGE [colname]
col_old_name---名字 col_new_name col_type----类型
[COMMENT col_comment]----注释
[FIRST|AFTER col_name]----位置（6）修改列的数据类型
alter table t_user
modify (name varchar2(500));更新列 ALTER TABLE table_name REPLACE COLUMNS (col_name data_type [COMMENT col_comment],...)
ADD COLUMNS允许在当前列之后，分区列之前添加新的列，REPLACE COLUMS删除当前的列，加入新的列。只有在使用native 的SerDe时才能这样做(7)修改列的字段名：
ALTER TABLE 表名
RENAME COLUMN 现在的字段名 TO 修改后的字段名；修改表属性 ALTER TABLE table_name SET TBLPROPERTIES table_properties
table_properties::(properties_name=property_value,properties_name=property_value,...)
----使用这个命令向表中增加元数据修改存储属性 ALTER TABLE table_name SET FILEFORMAT new_fileformat
ALTER TABLE table_name CLUSTERED BY (col_name,col_name,...) [SORTED BY (col_name,...)] INTO number BUCKETS------本命令修改了表的物理存储属性。只是修改了元数据，不能重组或格式化现有的数据。6,清除表数据 truncate table student;4、truncate 用来截断表中的数据，表示先删除，再提交。7,查看表格式化数据：desc formatted 表名truncate table t_user;DML数据操作1、数据导入1,INSERT：添加数据到数据库中向表中装载数据（Load）
load data [local] inpath '数据的path' [overwrite] into table 表名 [partition (partcol1=val1,…)];INSERT INTO(添加数据语句)
插入一行数据(最基础的插入数据)
格式：insert into 表名 (列名)values （对应的列数据）
例子：
INSERT into user (ID,NAME,SALARY)
VALUES(1,'中国移动','100000000');通过查询语句向表中插入数据（Insert）
insert overwrite table student
select id, name from student where month='201709';2,UPDATE：修改数据库中的数据查询语句中创建表并加载数据（As Select）
-- 根据查询结果创建表（查询的结果会添加到新创建的表中）
create table if not exists student3
as select id, name from student;UPDATE(更新数据语句)
修改数据(最基础的插入数据)
格式：update 表名
set id=5 //修改成为id=5的结果
where id=4;
（说明：where是筛选条件，筛选id=4；）
例子：
UPDATE zqx
SET id=5
WHERE id=4;
意思：修改某表中的id=4的数据，将id=4的修改成为id=5创建表时通过Location指定加载数据路径
create external table if not exists student5(
id int, name string
)
row format delimited fields terminated by '\t'
location '/student; -- 创建表，并指定在hdfs上的位置3,DELETE：删除数据库中的数据Import数据到指定Hive表中
import table student2 from
'/user/hive/warehouse/export/student';
-- 注意：先用export导出后，再将数据导入。清空表数据(最基础的插入数据)
格式 delete from 表名 （说明：from可以省略，可尝试）
例子：DELETE FROM zqx;2、数据导出4,SELECT：选择（查询）数据Insert导出
-- 将查询的结果格式化导出到本地
insert overwrite local directory '/opt/module/hive/datas/export/student1'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
select * from student;
-- 将查询的结果导出到HDFS上(没有local)
insert overwrite directory '/user/atguigu/student2'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
select * from student;查看表（最基础的查询）
格式：Select 列名 from 表名 --> 直接写出来的是函数关键字
例子：
SELECT * FROM ZQX;(说明：号代表表中的全部列名)Export导出到HDFS上
export table default.student to
'/user/hive/warehouse/export/student';DQL语法1. 基本查询（Select…From）1,SELECT全表和特定列查询SELECT 字段 FROM 表 [WHERE 条件] [GROUP BY 字段[HAVING 条件]] [ORDER BY 字段 ASC/DESC]SQL 语言大小写不敏感。2,WHERE 子句
字符和日期要包含在单引号中。
字符大小写敏感 ， 日期格式敏感SQL 可以写在一行或者多行2.1 运算符关键字不能被缩写也不能分行算术运算符：+ - * /各子句一般要分行写。比较运算符：> < >= <= = <> !=使用缩进提高语句的可读性。逻辑运算符：and or not1.1 算术运算符2.2 模糊查询算术运算符：+ - * /like not like 用于字符串的模糊查找。1.2 常用函数_:任意一个字符#求总行数（count）
select count(*) cnt from emp;%：任意多个字符#求工资的最大值（max）
select max(sal) max_sal from emp;2.3、范围查询#求工资的最小值（min）
select min(sal) min_sal from emp;IN：判断值是否在集合中#求工资的总和（sum）
select sum(sal) sum_sal from emp;EXIST：判断值是否在集合中#求工资的平均值（avg）
select avg(sal) avg_sal from emp;SOME ：判断值是否为集合中某一个1.3 比较运算符（Between/In/ Is Null）ANY ：判断值是否大于集合中的某一个或者小于集合中的某一个> < >= <= = <> !=ALL：判断值是否大于集合中的所有元素或者小于集合中的所有元素1.4 Like 和 和 RLikeBETWEEN 值1 AND 值2 ：判断值是否在值1和值2范围内使用 like 运算选择类似的值3、GROUP BY选择条件可以包含字符或数字SELECT DEPTNO, MAX(SAL), MIN(SAL), AVG(SAL), SUM(SAL) FROM EMP GROUP BY DEPTNO;% 代表零个或多个字符(任意个字符)。4、HAVING_ 代表一个字符。SELECT ENAME, SAL WHERE DEPTNO IN (
SELECT DEPTNO FROM EMP GROUP BY DEPTNO HAVING MIN(SAL) < 2000
);rlike子句是 Hive 中这个功能的一个扩展，其可以通过 Java 的正则表达式这个更强大的语言来指定匹配条件。5、ORDER BY1.5 逻辑运算符（And/Or/Not )DESC：倒序2. 分组查询SELECT ENAME, TO_CHAR(HIREDATE, 'yyyy'), TO_CHAR(HIREDATE, 'mm')
FROM EMP
ORDER BY TO_NUMBER(TO_CHAR(HIREDATE, 'yyyy')), TO_NUMBER(TO_CHAR(HIREDATE, 'mm'));2.1 Group By 语句 group by 语句通常会和聚合函数一起使用#计算 emp 表每个部门的平均工资
select t.deptno,avg(t.sal) avg_sal
from emp t
group by t.deptno;2.2 Having 语句 having 只用于 group by 分组统计语句。#求每个部门的平均薪水大于 2000 的部门
select deptno,avg(sal) avg_sal
from emp
group by deptno
having avg_sal > 2000;3. Join 语句Hive 支持通常的 SQL JOIN 语句，但是只支持等值连接，不支持非等值连接。#根据员工表和部门表中的部门编号相等，查询员工编号、员工名称和部门名称；
select e.empno,e.ename,d.deptno,d.dname
from emp e
join dept d
on e.deptno = d.deptno;3.1 LEFT SEMI JOINLEFT SEMI JOIN是IN/EXISTS 子查询的一种更高效的实现LEFT SEMI JOIN 的限制是， JOIN 子句中右边的表只能在 ON 子句中设置过滤条件，在 WHERE 子句、SELECT 子句或其他地方过滤都不行SELECT a.key, a.val FROM a LEFT SEMI JOIN b on (a.key = b.key)3.2 LEFT，RIGHT和FULL OUTER关键字用于处理join中空记录的情况4. 排序4.1 全局排序（Order By ）Order By：全局排序，一个 ReducerASC（ascend）: 升序（默认）DESC（descend）: 降序order by 子句在 select 语句的结尾4.2 按多个列排序#按照部门和工资升序排序
select ename,deptno,sal
from emp
order by deptno,sal;4.3 每个 MapReduce 内部排序（Sort By )Sort By：每个 Reducer 内部进行排序，对全局结果集来说不是排序。#将查询结果导入到文件中（按照部门编号降序排序）
insert overwrite local directory '/opt/module/data/sortby-result'
select * from emp
sort by deptno desc;4.4 分区排序（Distribute By ）Distribute By：类似 MR 中 partition，进行分区，结合 sort by 使用。
注意，Hive 要求 DISTRIBUTE BY 语句要写在 SORT BY 语句之前。对于 distribute by 进行测试，一定要分配多 reduce 进行处理，否则无法看到 distribute by的效果。#先按照部门编号分区，再按照员工编号降序排序。
set mapreduce.job.reduces=3;

insert overwrite local directory '/opt/module/data/distribute-result'
select * from emp
distribute by deptno
sort by empno desc;4.5 Cluster By当 distribute by 和 sorts by 字段相同时，可以使用 cluster by 方式。
cluster by 除了具有 distribute by 的功能外还兼具 sort by 的功能。但是排序只能是升序排序，不能指定排序规则为 ASC 或者 DESC。5. 分桶及抽样查询6.函数TCL授权： GRANT回收权限：REVOKE提交： COMMIT回滚： ROLLBACK保存点 ：SAVE POINT事务控制语句:一组逻辑操作单元 使数据从一种状态变换成另一种状态