干货-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