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 。
好啦，這節課的內容就是這些。以上優化技巧需要大家在平時的練習和使用中有意識地去注意自己的語句，不斷改進，就能掌握最優的寫法。