sql分桶
① 数据库DDL操作
创建库
create databases if not exists 库名;
删除库
drop databases 库名 restrict; restrict 默认关键字（可不写）只能删除空库,里边没有表的;
drop databases if exists 库名
drop databases 库名 cascade; cascade强制删除
查看库的列表信息
show databases;
使用库
use databases;
查看正在使用的库
select current_databases();
查看库信息
desc databases 库名;
可使用模糊查询 show databases 库名 like 'my%'
修改库
基本不用
创建表示例（hive sql）
create [external] table [if not exists] table_name (clo_name type comment "字段描述") comment '表描述'
[partitioned by (col_name type [comment '描述'])]
[clustered by (col_name,col_name,...)]
[sorted by (col_name [asc|desc],...)into num_buckets buskets]
[row format row_format]
[stored as file_format]
[location hdfs_path]
关键字讲解如下：
1）external 关键字：是否创建外部表，不加时是创建内部表，加上external关键字创建的是外部表
2）partitioned by (col_name type [comment '描述'])：指定分区字段
分区表存的是不同的目录
分区表在添加数据之前先添加分区
alter table table_name add if not exists partition(col_name ='一个分区')
3）clustered by (col_name,col_name,...)指定分桶字段clustered by
sorted by (col_name [asc|desc],...) into num_buckets buskets 指定排序字段
排序规则指定的是在同一个分桶内的排序规则
into num_buckets buskets;指定分桶个数num_buckets
判断依据：根据建表语句模板字段后边是否跟type类型，因为一个字段在建表语句中不会建两次
4）[row format row_format] 指定分隔符
delimited fields terminated by '' 指定列分隔符
lines terminated by '' 指定行分隔符
5）[stored as file_format] 指定最终表数据的存储格式
textfile 文本格式 默认的格式
rcfile 行列结合的格式
parquet 压缩格式
6）[location hdfs_path] 指定hive 上表的hdfs上的存储路径
不指定的话，存储在配置的路径下
没指定没配置，默认在 /user /hive /warehouse
复制表，复制表结构不复制数据
create table t1 like t2;
查看表
show tables;
show tables in 库名;
show tables like 's%';
查看表信息
desc 表名：显示表字段
desc extended 表名：显示表的详细信息（由左至右，连续在一起）
desc formatted 表名：格式化显示表的详细信息（规整的，一行一行的）
删除表
drop table if exists 表名;
清空表
truncate table 表名;清空表中的数据，保留表结构
修改表 alter
修改表名称
alter table 表名 rename to 新表名;
修改表字段信息
1）添加字段
alter table 表名 add columns (col_name type,col_name type);
所有新的字段一定要指定类型
2）修改字段，修改字段类型
alter table 表名 chage 原始字段 新字段 新字段类型;
string 类型相当于数值类型属于大类型
hive1.2.2中没有限制，各种类型之间都可以进行修改
3）替换列
alter table table_name replacr columns (in int,name string);由原始表全部字段替换成两个
修改表分区信息
1）添加分区
alter table table_name add if not exists partition(city='beijing')
添加分区的过程中，还可以指定分区的存储路径
alter table table_name add if not exists partition(city='beijing') location '/user/beijing'
2）修改分区 修改分区的存储路径
alter table table_name set partition(city='shenzhen') location '/user/shenzhen'
3）删除分区
alter table table_name drop if exists partition(city='beijing')
查看分区信息,针对分区表
show partitions table_name;
show partitions table_name partiton(city='beijing')
DDL和DML操作如下
https://blog.csdn.net/u010082526/article/details/89087286?utm_medium=distribute.pc_aggpage_search_result.none-task-blog-2~all~first_rank_v2~rank_v25-1-89087286.nonecase
② Hive sql及窗口函数
hive函数：
1、根据指定条件返回结果：case when then else end as
2、基本类型转换：CAST（）
3、nvl：处理空字段：三个str时，是否为空可以指定返回不同的值
4、sql通配符： https://www.w3school.com.cn/sql/sql_wildcards.asp
5、count(1)与COUNT(*)：返回行数
如果表没有主键，那么count（1）比count（*）快；
如果有主键，那么count（主键，联合主键）比count（*）快；
count(1)跟count(主键)一样，只扫描主键。count(*)跟count(非主键)一样，扫描整个表。明显前者更快一些。
性能问题：
1.任何情况下SELECT COUNT(*) FROM tablename是最优选择,(指没有where的情况）；
2.尽量减少SELECT COUNT(*) FROM tablename WHERE COL = ‘value’ 这种查询；
3.杜绝SELECT COUNT(COL) FROM tablename WHERE COL2 = ‘value’ 的出现。
count(expression)：查询 is_reply=0 的数量： SELECT COUNT(IF(is_reply=0,1,NULL)) count FROM t_iov_help_feedback;
6、distinct与group by
distinct去重所有distinct之后所有的字段，如果有一个字段值不一致就不作为一条
group by是根据某一字段分组，然后查询出该条数据的所需字段，可以搭配 where max(time)或者Row_Number函数使用，求出最大的一条数据
7、使用with 临时表名 as() 的形式,简单的临时表直接嵌套进枝段sql中，复杂的和需要复用的表写到临猛行誉时表中，关联的时候先找到关联字段，过滤条件最好在临时表中先过滤后关联
处理json的函数：
split(json_array_string(schools), '\\|\\|') AS schools
get_json_object(school, '$.id') AS school_id,
字符串函数：
1、instr（’源字符串’ , ‘目标字符串’ ,’开始位置’,’第几次出现’）
instr(sourceString,destString,start,appearPosition)
1.sourceString代表源字符串； destString代表要从源字符串中查找的子串；
2.start代表查找的开始位置，这个参数可选的，默认为1；
3.appearPosition代表想从源字符中查找出第几次出现的destString，这个参数也是可选的， 默认为1
4.如果start的值为负数，则代表从右往左进行查找，但是位置数据仍然从左向右计带枯算。
5.返回值为：查找到的字符串的位置。如果没有查找到，返回0。
最简单例子： 在abcd中查找a的位置，从第一个字母开始查，查找第一次出现时的位置
select instr(‘abcd’,’a’,1,1) from al; —1
应用于模糊查询：instr(字段名/列名, ‘查找字段’)
select code,name,dept,occupation from staff where instr(code, ‘001’)> 0;
等同于 select code, name, dept, occupation from staff where code like ‘%001%’ ;
应用于判断包含关系：
select ccn,mas_loc from mas_loc where instr(‘FH,FHH,FHM’,ccn)>0;
等同于 select ccn,mas_loc from mas_loc where ccn in (‘FH’,’FHH’,’FHM’);
2、substr（string A，int start，int len）和 substring（string A，int start，int len），用法一样
substr(time,1,8) 表示将time从第1位开始截取，截取的长度为8位
第一种用法：
substr（string A，int start）和 substring（string A，int start），用法一样
功效：返回字符串A从下标start位置到结尾的字符串
第二种用法：
substr（string A，int start，int len）和 substring（string A，int start，int len），用法一样
功效：返回字符串A从下标start位置开始，长度为len的字符串
3、get_json_object(form_data,'$.学生姓名') as student_name
json_tuple 函数的作用：用来解析json字符串中的多个字段
4、split(full_name, '\\.') [5] AS zq; 取的是数组里的第六个
日期(时间)函数：
1、to_date(event_time) 返回日期部分
2、date_sub：返回当前日期的相对时间
当前日期：select curdate()
当前日期前一天：select date_sub(curdate(),interval 1 day)
当前日期后一天：select date_sub(curdate(),interval -1 day)
date_sub(from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss'), 14) 将现在的时间总秒数转为标准格式时间，返回14天之前的时间
时间戳>>>>日期：
from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss') 将现在的时间总秒数转为标准格式时间
from_unixtime(get_json_object(get_json_object(form_data,'$.挽单时间'),'$.$date')/1000) as retain_time
unix_timestamp('2019-08-15 16:40:00','yyyy-MM-dd HH:mm:ss') --1565858400
日期>>>>时间戳：unix_timestamp()
date_format：yyyy-MM-dd HH:mm:ss 时间转格式化时间
select date_format('2019-10-07 13:24:20', 'yyyyMMdd000000')-- 20191007000000select date_format('2019-10-07', 'yyyyMMdd000000')-- 20191007000000
1.日期比较函数: datediff语法: datediff(string enddate,string startdate)
返回值: int
说明: 返回结束日期减去开始日期的天数。
举例： hive> select datediff('2016-12-30','2016-12-29'); 1
2.日期增加函数: date_add语法: date_add(string startdate, intdays)
返回值: string
说明: 返回开始日期startdate增加days天后的日期。
举例： hive>select date_add('2016-12-29',10); 2017-01-08
3.日期减少函数: date_sub语法: date_sub (string startdate,int days)
返回值: string
说明: 返回开始日期startdate减少days天后的日期。
举例： hive>select date_sub('2016-12-29',10); 2016-12-19
4.查询近30天的数据
select * from table where datediff(current_timestamp,create_time)<=30；
create_time 为table里的字段，current_timestamp 返回当前时间 2018-06-01 11:00:00
3、trunc()函数的用法：当前日期的各种第一天,或者对数字进行不四舍五入的截取
日期：
1.select trunc(sysdate) from al --2011-3-18 今天的日期为2011-3-18
2.select trunc(sysdate, 'mm') from al --2011-3-1 返回当月第一天.
上月1号 trunc(add_months(current_date(),-1),'MM')
3.select trunc(sysdate,'yy') from al --2011-1-1 返回当年第一天
4.select trunc(sysdate,'dd') from al --2011-3-18 返回当前年月日
5.select trunc(sysdate,'yyyy') from al --2011-1-1 返回当年第一天
6.select trunc(sysdate,'d') from al --2011-3-13 (星期天)返回当前星期的第一天
7.select trunc(sysdate, 'hh') from al --2011-3-18 14:00:00 当前时间为14:41
8.select trunc(sysdate, 'mi') from al --2011-3-18 14:41:00 TRUNC()函数没有秒的精确
数字：TRUNC（number,num_digits） Number 需要截尾取整的数字。Num_digits 的默认值为 0。TRUNC()函数截取时不进行四舍五入
11.select trunc(123.458,1) from al --123.4
12.select trunc(123.458,-1) from al --120
4、round()：四舍五入：
select round(1.455, 2) #结果是：1.46，即四舍五入到十分位，也就是保留两位小数
select round(1.5) #默认四舍五入到个位，结果是：2
select round(255, -1) #结果是：260，即四舍五入到十位，此时个位是5会进位
floor()：地板数
ceil()天花板数
5、
6.日期转年函数: year语法: year(string date)
返回值: int
说明: 返回日期中的年。
举例：
hive> select year('2011-12-08 10:03:01') from al;
2011
hive> select year('2012-12-08') fromal;
2012
7.日期转月函数: month语法: month (string date)
返回值: int
说明: 返回日期中的月份。
举例：
hive> select month('2011-12-08 10:03:01') from al;
12
hive> select month('2011-08-08') fromal;
8
8.日期转天函数: day语法: day (string date)
返回值: int
说明: 返回日期中的天。
举例：
hive> select day('2011-12-08 10:03:01') from al;
8
hive> select day('2011-12-24') fromal;
24
9.日期转小时函数: hour语法: hour (string date)
返回值: int
说明: 返回日期中的小时。
举例：
hive> select hour('2011-12-08 10:03:01') from al;
10
10.日期转分钟函数: minute语法: minute (string date)
返回值: int
说明: 返回日期中的分钟。
举例：
hive> select minute('2011-12-08 10:03:01') from al;
3
11.日期转秒函数: second语法: second (string date)
返回值: int
说明: 返回日期中的秒。
举例：
hive> select second('2011-12-08 10:03:01') from al;
1
12.日期转周函数: weekofyear语法: weekofyear (string date)
返回值: int
说明: 返回日期在当前的周数。
举例：
hive> select weekofyear('2011-12-08 10:03:01') from al;
49
查看hive表在hdfs中的位置：show create table 表名;
在hive中hive2hive，hive2hdfs：
HDFS、本地、hive -----> Hive：使用 insert into | overwrite、loaddata local inpath "" into table student;
Hive ----> Hdfs、本地：使用：insert overwrite | local
网站访问量统计：
uv：每用户访问次数
ip：每ip(可能很多人)访问次数
PV:是指页面的浏览次数
VV:是指你访问网站的次数
sql：
基本函数：
count、max、min、sum、avg、like、rlike（'2%'、'_2%'、%2%'、'[2]'）（java正则）
and、or、not、in
where、group by、having、{ join on 、full join} 、order by（desc降序）
sort by需要与distribut by集合结合使用：
hive (default)> set maprece.job.reces=3; //先设置rece的数量
insert overwrite local directory '/opt/mole/datas/distribute-by'
row format delimited fields terminated by '\t'
先按照部门编号分区，再按照员工编号降序排序。
select * from emp distribute by deptno sort by empno desc;
外部表 create external table if not exists dept
分区表：create table dept_partition ( deptno int, dname string, loc string ) partitioned by ( month string )
load data local inpath '/opt/mole/datas/dept.txt' into table default.dept_partition partition(month='201809');
alter table dept_partition add/drop partition(month='201805') ,partition(month='201804');
多分区联合查询：union
select * from dept_partition2 where month='201809' and day='10';
show partitions dept_partition;
desc formatted dept_partition;
二级分区表：create table dept_partition2 ( deptno int, dname string, loc string ) partitioned by (month string, day string) row format delimited fields terminated by '\t';
分桶抽样查询：分区针对的是数据的存储路径；分桶针对的是数据文件
create table stu_buck(id int, name string) clustered by(id) into 4 bucketsrow format delimited fields terminated by '\t';
设置开启分桶与rece为1：
set hive.enforce.bucketing=true;
set maprece.job.reces=-1;
分桶抽样：select * from stu_bucktablesample(bucket x out of y on id);
抽取，桶数/y，x是从哪个桶开始抽取，y越大 抽样数越少，y与抽样数成反比，x必须小于y
给空字段赋值：
如果员工的comm为NULL，则用-1代替或用其他字段代替 ：select nvl(comm,-1) from emp;
case when:如何符合记为1，用于统计、分组统计
select dept_id, sum(case sex when '男' then 1 else 0 end) man , sum(case sex when '女' then 1 else 0 end) woman from emp_sex group by dept_id;
用于组合归类汇总(行转列)：UDAF：多转一
concat：拼接查询结果
collect_set(col)：去重汇总，产生array类型字段，类似于distinct
select t.base, concat_ws('|',collect_set(t.name)) from (select concat_ws(',',xingzuo,blood_type) base,name from person_info) t group by t.base;
解释：先第一次查询得到一张没有按照（星座血型）分组的表，然后分组，使用collect_set将名字组合成数组，然后使用concat将数组变成字符串
用于拆分数据：(列转行)：UDTF：一转多
explode(col)：将hive一列中复杂的array或者map结构拆分成多行。
lateral view 侧面显示：用于和UDTF一对多函数搭配使用
用法：lateral view udtf(expression) tablealias as cate
cate：炸开之后的列别名
temptable ：临时表表名
解释：用于和split, explode等UDTF一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。
开窗函数：
Row_Number,Rank，Dense_Rank over：针对统计查询使用
Row_Number：返回从1开始的序列
Rank:生成分组中的排名序号，会在名词s中留下空位。3 3 5
dense_rank：生成分组中的排名序号，不会在名词中留下空位。3 3 4
over：主要是分组排序，搭配窗口函数使用
结果：
SUM、AVG、MIN、MAX、count
preceding：往前
following：往后
current row：当前行
unbounded：unbounded preceding 从前面的起点， unbounded following：到后面的终点
sum：直接使用sum是总的求和，结合over使用可统计至每一行的结果、总的结果、当前行+之前多少行/之后多少行、当前行到往后所有行的求和。
over(rowsbetween 3/current ) 当前行到往后所有行的求和
ntile：分片，结合over使用，可以给数据分片，返回分片号
使用场景：统计出排名前百分之或n分之一的数据。
lead,lag,FIRST_VALUE,LAST_VALUE
lag与lead函数可以返回上下行的数据
lead(col,n,dafault) 用于统计窗口内往下第n行值
第一个参数为列名，第二个参数为往下第n行（可选，默认为1），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）
LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值
第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）
使用场景：通常用于统计某用户在某个网页上的停留时间
FIRST_VALUE:取分组内排序后，截止到当前行，第一个值
LAST_VALUE:取分组内排序后，截止到当前行,最后一个值
范围内求和: https://blog.csdn.net/happyrocking/article/details/105369558
cume_dist，percent_rank
–CUME_DIST :小于等于当前值的 行数 / 分组内总行数
–比如，统计小于等于当前薪水的人数，占总人数的比例
percent_rank:分组内当前行的RANK值-1/分组内总行数-1
总结：
在Spark中使用spark sql与hql一致，也可以直接使用sparkAPI实现。
HiveSql窗口函数主要应用于求TopN，分组排序TopN、TopN求和，前多少名前百分之几。
与Flink窗口函数不同。
Flink中的窗口是用于将无线数据流切分为有限块处理的手段。
window分类：
CountWindow：按照指定的数据条数生成一个 Window，与时间无关。
TimeWindow：按照时间生成 Window。
1. 滚动窗口（Tumbling Windows）：时间对齐，窗口长度固定，不重叠：：常用于时间段内的聚合计算
2.滑动窗口（Sliding Windows）：时间对齐，窗口长度固定，可以有重叠：：适用于一段时间内的统计（某接口最近 5min 的失败率来报警）
3. 会话窗口（Session Windows）无时间对齐，无长度，不重叠：：设置session间隔，超过时间间隔则窗口关闭。
③ Elasticsearch 聚合查询（aggs）基本概念 --- 2022-04-03
ES中的聚合查询，类似SQL的SUM/AVG/COUNT/GROUP BY分组查询，主要用于统计分析场景。
下面先介绍ES聚合查询的核心流程和核心概念。
分组
组内聚合
对查询的数据首先进行一轮分组，可以设置分组条件，例如：新生入学，把所有的学生按专业分班，这个分班的过程就是对学生进行了分组。
组内聚合，就是对组内的数据进行统计，例如：计算总数、求平均值等等，接上面的例子，学生都按专业分班了，那么就可以统计每个班的学生总数， 这个统计每个班学生总数的计算，就是组内聚合计算。
提示：分组类似SQL的group by语句设定的条件，组内聚合，就是在select编写的avg、sum、count统计函数；熟悉SQL语句都知道sum、count这些统计函数不一定要跟group by语句配合使用，单独使用统计函数等同于将所有数据分成一个组，直接对所有数据进行统计。
2.1. 桶
满足特定条件的文档的集合，叫做桶。
桶的就是一组数据的集合，对数据分组后，得到一组组的数据，就是一个个的桶。
提示：桶等同于组，分桶和分组是一个意思，ES使用桶代表一组相同特征的数据。
ES中桶聚合，指的就是先对数据进行分组，ES支持多种分组条件，例如：支持类似SQL的group by根据字段分组，当然ES比SQL更强大，支持更多的分组条件，以满足各种统计需求。
2.2. 指标
指标指的是对文档进行统计计算方式，又叫指标聚合。
桶内聚合，说的就是先对数据进行分组（分桶），然后对每一个桶内的数据进行指标聚合。
说白了就是，前面将数据经过一轮桶聚合，把数据分成一个个的桶之后，我们根据上面计算指标对桶码段内的数据进行统计。
常用的指标有：SUM、COUNT、MAX等统计函数。
借助SQL的统计语句理解桶和指标：
说明：
COUNT( ) 相当于指标, 也叫统计指标。
GROUP BY shop_id 相当于分桶的条件，也可以叫分组条件，相同shop_id的数据都分到一个桶内。
这条SQL语句的作用就是统计每一个店铺的订单数，所以SQL统计的第一步是根据group by shop_id这个条件，把shop_id（店铺ID）相同的数据分到一个组（桶）里面，然后每一组数据使用count( )统计函数（指标）计算总数，最终得到每一个店铺的订单总数，ES也是类似的过程。
3.ES聚合查询语法
大家可以先大致了解下ES聚合查询的基本语法结构，后面的章节会介绍具体的用法。
说明：
aggregations - 代表聚合查询语句，可以简写为aggs
<aggregation_name> - 代表一个聚合计算的名字，可以随意命名，因为ES支持一次进行多次统计分析查询，后面需要通过这个名字在查询结果中找到我们想要的计算结果。
<aggregation_type> - 聚合类型，代表我们想要怎么统计数据，主要有两大类聚合类型，桶聚合和指标聚合，这两类聚合又包括多种聚合类型，例如：指标聚合：sum、avg， 桶聚合：terms、Date histogram等等。
<aggregation_body> - 聚合类型的参数，选择不同的聚合类型，有不同的参数。
aggregation_name_2 - 代表其他拦亏聚合计算的名字，意思就是可以一次进行多种类型的统计。
下面看个简单的聚合查询的例子：
假设存在一个order索引，存储了每一笔汽车销售订单，里面包含了汽车颜色字段color.
上面使用了terms桶聚合，而且没有明确指定指标聚合函数，默认使用的是Value Count聚合指标统迟衡誉计文档总数， 整个统计的意思是统计每一种汽车颜色的销量。
等价SQL如下：
查询结果如下
④ MySQL窗口函数_聚合函数
还是想快点写完，进入下一个PowerBI专题了，应该后面再写五篇左右，就开始PowerBI系列了，然后会加一些SQL的综合使用案例。窗口函数写三篇就差不多了，后面应该还会加一些综合案例。这一篇主要介绍一下聚合函数和NTH_VALUE、NTILE函数，聚合函数和配上窗口函数使用场景还是很多，陵颤这个可以稍微 关注一下。
【NTH_VALUE】
NTH_VALUE(EXP,N)，返回窗口中第N个EXP的值，EXP可以是表达式，也可以是列名。继续用之前的数据，取出每个用户的第二次购买的日期，SQL语句如下数汪橘
从结果很明显看出，第一次购买日期是空值，从第二个薯团购买日期开始，都是第二条购买日期记录。
【NTLIE】
NTILE(N)，将分区中的有序数据分为n个桶，记录桶号。现在需要根据金额高低，把每个分区分成3组，SQL和结果如下
【聚合函数】
聚合函数参考这一篇， 《MySQL聚合函数》 。通过聚合函数作为窗口函数运算，可以动态计算在指定的窗口内的各种聚合函数值。计算每个用户，按时间排序，截止每个时间的订单数量、订单金额、平均金额、最大金额、最小金额。SQL语句和结果如下
End
⑤ 深入理解Hive分区与分桶
目前，在利用hive建设数据仓库的过程中，总会遇见分区分桶的，跟传统的DBMS系统一样，通过表分区能够在特定的区域检索数据，减少扫描成本，在一定程度上提高查询效率。比如我们要收集某个大型网站的日志数据，一个网站每天的日志数据存在同一张表上，由于每天会生成大量的日志，导致数据表的内容巨大，在查询时进行全表扫描耗费的资源非常多。那其实这个情况下，我们可以按照日期对数据表进行分区，不同日期的数据存放在不同的分区，在查询时只要指定分区字段的值就可以直接从该分区查找。在物理上分区表会将数据按照分区键的列值存储在表仿迅目录的子目录中，目录名=“分区键=键值”。其中需要注意的是分区键的值不一定要基于表的某一列（字段），它可以指定任意值，只要查询的时候指定相应的分区键来查询即可。
分桶与分区有所不同，它指定分桶表的某一列，让该列数据按照哈希取模的方式随机、均匀地分发到各个桶文件中。因为分桶操作需要根据某一列具体数据来进行哈希取模操梁隐作，故指定的分桶列必须基于表中的某一列（字段）。因为分桶改变了数据的存储方式，它会把哈希取模相同或者橡大厅在某一区间的数据行放在同一个桶文件中。如此一来便可提高查询效率，比如我们要对两张在同一列上进行了分桶操作的表进行JOIN操作的时候，只需要对保存相同列值的桶进行JOIN操作即可。同时分桶也可以提高采样率。
分区是为了对表进行合理的管理以及提高查询效率，Hive可以将表组织成“分区”。一个分区实际上就是表下的一个目录，一个表可以在多个维度上进行分区，分区之间的关系就是目录树的关系。Hive(Inceptor)分区分为静态分区跟动态分区，详细介绍如下：
静态分区在插入或者导入的时候需要指定具体的分区
[if !supportLists]1、 [endif]静态分区创建
需要在PARTITIONED BY后面跟上分区键，类型。例如：
CREATE TABLE p_table1(
id int
,name
string
)
PARTITIONED BY(date_day string)
stored as orc
;
这是一级分区，当然也可以创建多级分区。例如：
CREATE TABLE p_table1(
id int
,name string
)
PARTITIONED BY(date_day string, company
string,emp_no string)
stored as orc
;
下面的实例都是以一级分区为例。
[if !supportLists]2、 [endif]静态分区插入数据
insert overwrite table p_table1 partition(date_day='2019-07-14')
values(1,'lucy');
或者insert overwrite table p_table1 partition(date_day='2019-07-15')
select 2 as id,'lily' as name;
上面两个例子都是覆盖的形式，也就是插入这个分区之前，会将该分区数据删除，再插入新的数据，也可以写成追加的形式：
insert into p_table1
partition(date_day='2019-07-14') values(1,'lucy');
或者insert into e p_table1 partition(date_day='2019-07-15') select 2 as
id,'lily' as name;
[if !supportLists]3、 [endif]静态分区查看分区
查看所有分区show partitions p_table1
结果如下：
date_day=2019-07-14
date_day=2019-07-15
查看某个分区show partitions p_table1 partition(date_day='2019-07-14');
结果如下：
date_day=2019-07-14
[if !supportLists]4、 [endif]静态分区删除分区
删除某个分区alter table p_table1 drop partition(date_day='2019-07-14');
或者删除范围内的分区alter table p_table1 drop partition(date_day>='2019-07-14');
1、动态分区创建
创建方式与静态分区表完全一样，一张表可同时被静态和动态分区键分区，只是动态分区键需要放在静态分区建的后面（因为HDFS上的动态分区目录下不能包含静态分区的子目录）。
CREATE TABLE p_table2(
id int
,name string
)
PARTITIONED BY(date_day string,emp_no
string)
stored as orc
;
这是创建了二级分区表。
2、动态分区插入数据
插入数据时需要开启动态数据支持：
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nostrict;
插入数据（覆盖）insert overwrite table p_table2 partition(date_day，emp_no)
select 2 as id,'lily' as name,'2019-07-14' as date_day, ‘a’ as emp_no;
分区并没有写死，而是根据查询到的值动态创建的两级分区。
3、动态分区查看分区、删除分区与静态分区操作完全一致不再重述。
分桶字段是表内字段，默认是对分桶的字段进行hash值，然后模总的桶数，得到的值则是分区桶数，主要有以下两点好处：
（1）获得更高的查询处理效率。桶为表加上了额外的结构，Hive 在处理有些查询时能利用这个结构。具体而言，连接两个在（包含连接列的）相同列上划分了桶的表，可以使用 Map 端连接（Map-side join）高效的实现。比如JOIN操作。对于JOIN操作两个表有一个相同的列，如果对这两个表都进行了桶操作。那么将保存相同列值的桶进行JOIN操作就可以，可以大大较少JOIN的数据量。
（2）使取样（sampling）更高效。在处理大规模数据集时，在开发和修改查询的阶段，如果能在数据集的一小部分数据上试运行查询，会带来很多方便。
创建分桶表
先看一下创建分桶表的创建，分桶表的建表有三种方式：直接建表，CREATE TABLE LIKE 和 CREATE TABLE AS SELECT ，单值分区表不能用 CREATETABLE
AS SELECT 建表。这里以直接建表为例：
create table b_table1(id int,name string)
clustered by (id) sorted by(id) into 4 buckets stored as textfile;
使用CLUSTERED BY 子句来指定划分桶所用的列和要划分的桶的个数，当表分区时，每个分区下都会有4个桶。对于map端连接的情况，两个表以相同方式划分桶。处理左边表内某个桶的 mapper知道右边表内相匹配的行在对应的桶内。因此，mapper只需要获取那个桶 (这只是右边表内存储数据的一小部分)即可进行连接。这一优化方法并不一定要求两个表必须桶的个数相同，两个表的桶个数是倍数关系也可以。用HiveQL对两个划分了桶的表进行连接。
桶中的数据可以根据一个或多个列另外进行排序。由于这样对每个桶的连接变成了高效的归并排序(merge-sort), 因此可以进一步提升map端连接的效率。
向分桶表写入数据
如何保证表中的数据都划分成桶了呢？把在Hive外生成的数据加载到划分成桶的表中，当然是可以的。其实让Hive来划分桶更容易。这一操作通常针对已有的表。
Hive并不检查数据文件中的桶是否和表定义中的桶一致(无论是对于桶的数量或用于划分桶的列）。如果两者不匹配，在査询时可能会碰到错误或未定义的结果。因此，建议让Hive来进行划分桶的操作。
要向分桶表中填充成员，需要将 hive.enforce.bucketing 属性设置为 true。这样Hive 就知道用表定义中声明的数量来创建桶。
下面有个未分桶的用户表b_user_test，数据如下：
1 a
2 b
3 c
4 d
5 e
6 f
7 g
插入语句
INSERT OVERWRITE TABLE b_table1 SELECT *
FROM b_user_test;
查看文件结构
dfs -ls/user/hive/warehouse/bucketed_users;
文件结构如下所示：
/user/hive/warehouse/b_table1/000000_0
/user/hive/warehouse/b_table1/000001_0
/user/hive/warehouse/b_table1/000002_0
/user/hive/warehouse/b_table1/000003_0
查看文件000000_0
dfs -cat /user/hive/warehouse/bucketed_users/000000_0;
值为4 d说明文件000000_0存的是对分桶数求余等于0的那部分数据。
对桶中的数据进行采样
对分桶进行查询 tablesample(bucket x out of y on id)：
x:表示查询那个桶
y:表示建表指定的桶的总数,如果不是建表时指定的桶的总数，则会重新分桶。
x不能大于y。
取第一个桶的数据：
Sql：SELECT * FROM
b_table1 TABLESAMPLE(BUCKET 2 OUT OF 4 ON id);
结果：
5 e
[if !supportLists]1 [endif]a
当桶数不等于建表指定的桶的总数时
Sql：SELECT * FROM
b_table1 TABLESAMPLE(BUCKET 2 OUT OF 3 ON id);
结果：
4 d
1 a
7 g
由结果可知，进行了重新分桶，分成了三个桶，取出第二个桶的数据，也就是hash值对3求余等于1的那部分数据。
分桶比分区粒度更细，在每个分区了可以将数据进行分桶操作。
⑥ sql如何等距分箱
向上取整ceil。sql数据库简介内容得知拍慎等距分箱是芦郑需要向上取整ceil进行的，为最基袭哗敬础的知识点。sql是一种计算机语言，用来存储、检索和修改关系型数据库中存储的数据。
⑦ hql视图有没有分区分桶
没有。hql视图不具备分区分桶的功能，因此没有。HQL是提供更加丰富灵活散逗帆冲雹、更为强大的查询能力；HQL更接近SQL语指卖句查询语法。
⑧ 数据分析课程笔记 - 19 - HiveSQL 常用优化技巧
大家好呀，这节课学习 HiveSQL 的常用优化技巧。由于 Hive 主要用来处理非常大的数据，运行过程由于通常要经过 MapRece 的过程，因此不像 MySQL 一样很快出结果。而使用不同方法写出来的 HiveSQL 语句执行效率也是不一样的，因此为了减少等待的时间，提高服务器的运行效率，我们需要在 HiveSQL 的语句上进行一些优化。
本节课的主要内容 ：
引言
1、技巧一：列裁剪和分区裁剪
（1）列裁剪
（2）分区裁剪
2、技巧二：排序技巧——sort by代替order by
3、技巧三：去重技巧——用group by来替换distinct
4、技巧四：聚合技巧——grouping sets、cube、rollup
（1）grouping sets
（2）cube
（3）rollup
5、技巧五：换个思路解题
6、技巧六：union all时可以开启并发执行
7、技巧七：表连接优化
8、技巧八：遵循严格模式
Hive 作为大数据领域常用的数据仓库组件，在平时设计和查询时要特别注意效率。影响Hive效率的几乎从不是数据量过大，而是数据倾斜、数据冗余、job 或 I/O 过多、MapRece 分配不合理等等。对 Hive 的调优既包含对HiveSQL 语句本身的优化，也包含 Hive 配置项和 MR 方面的调整。
列裁剪就是在查询时只读取需要的列。当列很多或者数据量很大时，如果select 所有的列或者不指定分区，导致的全表扫描和全分区扫描效率都很低。Hive中与列裁剪优化相关的配置项是 hive.optimize.cp ，默认是 true 。
分区裁剪就是在查询时只读需要的分区。Hive中与分区裁剪优化相关的则是 hive.optimize.pruner ，默认是 true 。
HiveSQL中的 order by 与其他 SQL 语言中的功能一样，就是将结果按某个字段全局排序，这会导致所有map端数据都进入一个 rece 中，在数据量大时可能会长时间计算不完。
如果使用 sort by ，那么就会视情况启动多个 recer 进行排序，并且保证每个 recer 内局部有序。为了控制 map 端数据分配到 rece 的 key，往往还要配合 distribute by 一同使用。如果不加 distribute by 的话，map 端数据就会随机分配给 recer。
这里需要解释一下， distribute by 和 sort by 结合使用是如何相较于 order by 提升运行效率的。
假如我们要对一张很大的用户信息表按照年龄进行分组，优化前的写法是直接 order by age 。使用 distribute by 和 sort by 结合进行优化的时候， sort by 后面还是 age 这个排序字段， distribute by 后面选择一个没有重复值的均匀字段，比如 user_id 。
这样做的原因是，通常用户的年龄分布是不均匀的，比如20岁以下和50岁以上的人非常少，中间几个年龄段的人又非常多，在 Map 阶段就会造成有些任务很大，有些任务很小。那通过 distribute by 一个均匀字段，就可以让系统均匀地进行“分桶”，对每个桶进行排序，最后再组合，这样就能从整体上提升 MapRece 的效率。
取出 user_trade 表中全部支付用户：
原有写法的执行时长：
优化写法的执行时长：
考虑对之前的案例进行优化：
注意： 在极大的数据量（且很多重复值）时，可以先 group by 去重，再 count() 计数，效率高于直接 count(distinct **) 。
如果我们想知道用户的性别分布、城市分布、等级分布，你会怎么写？
通常写法：
缺点 ：要分别写三次SQL，需要执行三次，重复工作，且费时。
那该怎么优化呢？
注意 ：这个聚合结果相当于纵向地堆在一起了（Union all），分类字段用不同列来进行区分，也就是每一行数据都包含 4 列，前三列是分类字段，最后一列是聚合计算的结果。
GROUPING SETS() ：在 group by 查询中，根据不同的维度组合进行聚合，等价于将不同维度的 group by 结果集进行 union all。聚合规则在括号中进行指定。
如果我们想知道用户的性别分布以及每个性别的城市分布，你会怎么写？
那该怎么优化呢？
注意： 第二列为NULL的，就是性别的用户分布，其余有城市的均为每个性别的城市分布。
cube：根据 group by 维度的所有组合进行聚合
注意 ：跑完数据后，整理很关键！！！
rollup：以最左侧的维度为主，进行层级聚合，是cube的子集。
如果我想同时计算出，每个月的支付金额，以及每年的总支付金额，该怎么办？
那应该如何优化呢？
条条大路通罗马，写SQL亦是如此，能达到同样效果的SQL有很多种，要学会思路转换，灵活应用。
来看一个我们之前做过的案例：
有没有别的写法呢？
Hive 中互相没有依赖关系的 job 间是可以并行执行的，最典型的就是
多个子查询union all。在集群资源相对充足的情况下，可以开启并
行执行。参数设置： set hive.exec.parallel=true;
时间对比：
所谓严格模式，就是强制不允许用户执行3种有风险的 HiveSQL 语句，一旦执行会直接报错。
要开启严格模式，需要将参数 hive.mapred.mode 设为 strict 。
好啦，这节课的内容就是这些。以上优化技巧需要大家在平时的练习和使用中有意识地去注意自己的语句，不断改进，就能掌握最优的写法。