干货-SQL日常使用经验积累

一.背景

记录日常工作中使用到的Hive-SQL相关知识，不是基础的SQL语法介绍，一般是坑比较多的语句或者优化技巧。

二.语法

2.1hive简介

Hive的内置四大组件（Driver, Compiler, Optimizer, Executor）完成HQL到MapReduce的转换，在Hive执行HQL编译过程中，会从元数据库获取表结构和数据存储目录等相关信息，Hive只是完成对存储在HDFS上的结构化数据的管理，并提供一种类SQL的操作方式来进行海量数据运行，底层支持多种分布式计算引擎，影响 Hive 效率的几乎从不是数据量过大，而是数据倾斜、数据冗余、Job或I/O过多、MapReduce 分配不合理等等。

2.2hive常用

#1.分区和分桶
PARTITIONED BY(dt STRING, country STRING)
CLUSTERED BY(userid) SORTED BY(viewTime) INTO 32 BUCKETS
#2.hive的抽样
#分桶抽样
select * from student tablesample(bucket 3 out of 32);
#随机采样：rand() 函数
select * from student order by rand() limit 100; // 效率低
select * from student distribute by rand() sort by rand() limit 100; // 推荐使用这种
#数据块抽样：tablesample()函数
select * from student tablesample(10 percent); # 百分比
select * from student tablesample(5 rows); # 行数
select * from student tablesample(5 M); # 大小
#3.in/exists实现和semi join
select a.id, a.name from a where a.id in (select b.id from b);
select a.id, a.name from a where exists (select id from b where a.id = b.id);
#可以使用join来改写：
select a.id, a.namr from a join b on a.id = b.id;
#应该转换成left semi join 实现
select a.id, a.name from a left semi join b on a.id = b.id;

2.2常用SQL语法记录

2.2.1日期相关

#取某一天是周几，返回0-6 周日-周六
select pmod(datediff('2020-12-06','1920-01-01')-3,7) as week
#若想返回 1-7