[大数据]hive SQL优化

1、多次INSERT单次扫描表

使用场景：

--当需要多次从一张分区表查出某分区的数据放到一张新表时，下边SQL会多次扫描表。
INSERT INTO temp_table_20201115 SELECT * FROM my_table WHERE dt ='2020-11-15';
INSERT INTO temp_table_20201116 SELECT * FROM my_table WHERE dt ='2020-11-16';

使用方法：

--只用扫描一次表。
FROM my_table
INSERT INTO temp_table_20201115 SELECT * WHERE dt ='2020-11-15'
INSERT INTO temp_table_20201116 SELECT * WHERE dt ='2020-11-16'

2、使用分区表

分区表基本的法则：选择低基数属性作为“分区键”，比如“地区”或“日期”等。

--创建一个映射到/user/tmp/part目录的外部分区表：
CREATE EXTERNAL TABLE IF NOT EXISTS part_test_2(
c1 string
,c2 string
,c3 string
,c4 string
)PARTITIONED BY (month_id string,day_id string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘,’
STORED AS TEXTFILE
LOCATION ‘/user/tmp/part’;

3、使用分桶表
与分区表类似，分桶表的组织方式是将HDFS上的文件分割成多个文件。分桶可以加快数据采样，也可以提升join的性能(join的字段是分桶字段)，因为分桶可以确保某个key对应的数据在一个特定的桶内(文件)，所以巧妙地选择分桶字段可以大幅度提升join的性能。通常情况下，分桶字段可以选择经常用在过滤操作或者join操作的字段。

--启用分桶
set.hive.enforce.bucketing = true;
--使用分桶表时，最好将bucketmapjoin标志设置为true
set hive.optimize.bucketmapjoin = true
--创建分桶表，分四个桶
create table   students_bucket1 (
    id string,
    name string,
    score1 float,
    score2 float,
    score3 float,
    score float
 ) 
clustered by ( id )  sorted by ( score  asc ) into  4 buckets
row format delimited 
fields terminated by ',';

4、谓词下推

使用背景：
当执行以下SQL，完成JOIN处理之后将执行过滤条件**(a.col1> 15和b.col2> 16)**。因此，在这种情况下，JOIN将首先发生，并且可能产生更多的行，然后在进行过滤操作

select
    a.*,
    b.* 
from 
    a join b on (a.col1 = b.col1)
where a.col1 > 15 and b.col2 > 16

使用方法：
使用谓词下推，这两个谓词**(a.col1> 15和b.col2> 16)**将在JOIN之前被处理，因此它可能会从a和b中过滤掉连接中较早处理的大部分数据行

--启用谓词下推
SET hive.optimize.ppd=true

创作打卡挑战赛

赢取流量/现金/CSDN周边激励大奖