⑴ Hive或sql 建立數組（高級查詢）
延慶南菜園方位在百泉路北面，京新高速南面，康張路東面，京銀路西面
⑵ hive sql里，幫我描述一個簡單的sql的原理
select a.id,a.info,b.num from a join b on a.id=b.id and where b.num>=10
兩個表做關聯，首先where會過濾掉不需要的數據。
至於表怎麼做map和rece操作，在hive里的表是虛擬的，其實還是對hdfs文件進行操作，你可以在hdfs:///user/hive/warehouse路徑下找到以表名來命名的文件，裡面就是表的內容，可以執行-cat命令查看。所以，它的map操作很簡單，就是按行讀文件，然後會根據hive的默認分隔符\001對每行進行切分。切分完成後就會按照你SQL指定的邏輯進行合並，最後再輸出成hdfs文件，只不過在hive裡面看它是以表的形式展現的。
job數會在你執行sql語句之後緊接著有相應的日誌記錄，
Total MapRece jobs = 2
Launching Job 1 out of 2
Number of rece tasks not specified. Estimated from input data size: 2
In order to change the average load for a recer (in bytes):
set hive.exec.recers.bytes.per.recer=<number>
In order to limit the maximum number of recers:
set hive.exec.recers.max=<number>
In order to set a constant number of recers:
這樣就是有兩個job，正在執行第一個job。
Hadoop job information for Stage-1: number of mappers: 5; number of recers: 2
而這個就會告訴你有多少個mapper和recer。
像你寫的這個sql有join操作，而且是hiveSQL裡面最普通的join，那麼一定會有recer參與，如果數據量很大，比如上千萬條記錄，join就會特別慢，job進度就會一直卡在rece操作。可以改成mapjoin或者sort merge bucket mapjoin。
其實hive效率不高，不適合實時查詢，即使一個表為空，用hive進行查詢也會很耗時，因為它要把sql語句翻譯成MR任務。雖然簡化了分布式編程，但是效率上就會付出代價。
你的這句sql應該會翻譯成一個JOB來執行，就是簡單地map和rece。
maprece就是按行讀文件，然後切分，合並，輸出成文件。
⑶ hive 分頁sql語句
分頁實現的方式比較多了 下面舉個例子 比如 獲取前10條數據
註：同時需要記錄這10條中最大的id為preId，作為下一頁的條件。
select * from table order by id asc limit 10;
select * from table where id >preId order by id asc limit 10;
⑷ 數據分析課程筆記 - 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 。
好啦，這節課的內容就是這些。以上優化技巧需要大家在平時的練習和使用中有意識地去注意自己的語句，不斷改進，就能掌握最優的寫法。
⑸ hivesql怎麼獲取上一個月月份
hivesql sql — 獲取指定hive表或指定文件所hive表DDL按區則默認執行近7區DDL同table支持符合sql語則表達式表匹配則提示用戶選擇(使用file則自關閉該交互功能)。
hive是基於Hadoop的一個數據倉庫工具，可以將結構化的數據文件映射為一張資料庫表，並提供完整的sql查詢功能，可以將sql語句轉換為MapRece任務進行運行。其優點是學習成本低，可以通過類SQL語句快速實現簡單的MapRece統計，不必開發專門的MapRece應用，十分適合數據倉庫的統計分析。另外一個是Windows注冊表文件。
⑹ hive sql的語法幫助在哪
Hive 是基於Hadoop 構建的一套數據倉庫分析系統，它提供了豐富的SQL查詢方式來分析存儲在Hadoop 分布式文件系統中的數據，可以將結構化的數據文件映射為一張資料庫表，並提供完整的SQL查詢功能，可以將SQL語句轉換為MapRece任務進行運行，通過自己的SQL 去查詢分析需要的內容，這套SQL 簡稱Hive SQL，使不熟悉maprece 的用戶很方便的利用SQL 語言查詢，匯總，分析數據。而maprece開發人員可以把己寫的mapper 和recer 作為插件來支持Hive 做更復雜的數據分析。
它與關系型資料庫的SQL 略有不同，但支持了絕大多數的語句如DDL、DML 以及常見的聚合函數、連接查詢、條件查詢。HIVE不適合用於聯機online)事務處理，也不提供實時查詢功能。它最適合應用在基於大量不可變數據的批處理作業。
HIVE的特點：可伸縮（在Hadoop的集群上動態的添加設備），可擴展，容錯，輸入格式的鬆散耦合。
Hive 的官方文檔中對查詢語言有了很詳細的描述，請參考：http://wiki.apache.org/hadoop/Hive/LanguageManual ，本文的內容大部分翻譯自該頁面，期間加入了一些在使用過程中需要注意到的事項。
1. DDL 操作
DDL
?建表
?刪除表
?修改表結構
?創建／刪除視圖
?創建資料庫
?顯示命令
建表：
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
[(col_name data_type [COMMENT col_comment], ...)]
[COMMENT table_comment]
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
[CLUSTERED BY (col_name, col_name, ...)
[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
[ROW FORMAT row_format]
[STORED AS file_format]
[LOCATION hdfs_path]
?CREATE TABLE 創建一個指定名字的表。如果相同名字的表已經存在，則拋出異常；用戶可以用 IF NOT EXIST 選項來忽略這個異常
?EXTERNAL 關鍵字可以讓用戶創建一個外部表，在建表的同時指定一個指向實際數據的路徑（LOCATION）
?LIKE 允許用戶復制現有的表結構，但是不復制數據
?COMMENT可以為表與欄位增加描述
?ROW FORMAT
DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char]
[MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
| SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]
用戶在建表的時候可以自定義 SerDe 或者使用自帶的 SerDe。如果沒有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED，將會使用自帶的 SerDe。在建表的時候，用戶還需要為表指定列，用戶在指定表的列的同時也會指定自定義的 SerDe，Hive 通過 SerDe 確定表的具體的列的數據。
?STORED AS
SEQUENCEFILE
| TEXTFILE
| RCFILE
| INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname
如果文件數據是純文本，可以使用 STORED AS TEXTFILE。如果數據需要壓縮，使用 STORED AS SEQUENCE 。
創建簡單表：
hive> CREATE TABLE pokes (foo INT, bar STRING);
創建外部表：
CREATE EXTERNAL TABLE page_view(viewTime INT, userid BIGINT,
page_url STRING, referrer_url STRING,
ip STRING COMMENT 'IP Address of the User',
country STRING COMMENT 'country of origination')
COMMENT 'This is the staging page view table'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\054'
STORED AS TEXTFILE
LOCATION '';
建分區表
CREATE TABLE par_table(viewTime INT, userid BIGINT,
page_url STRING, referrer_url STRING,
ip STRING COMMENT 'IP Address of the User')
COMMENT 'This is the page view table'
PARTITIONED BY(date STRING, pos STRING)
ROW FORMAT DELIMITED 『\t』
FIELDS TERMINATED BY '\n'
STORED AS SEQUENCEFILE;
建Bucket表
CREATE TABLE par_table(viewTime INT, userid BIGINT,
page_url STRING, referrer_url STRING,
ip STRING COMMENT 'IP Address of the User')
COMMENT 'This is the page view table'
PARTITIONED BY(date STRING, pos STRING)
CLUSTERED BY(userid) SORTED BY(viewTime) INTO 32 BUCKETS
ROW FORMAT DELIMITED 『\t』
FIELDS TERMINATED BY '\n'
STORED AS SEQUENCEFILE;
創建表並創建索引欄位ds
hive> CREATE TABLE invites (foo INT, bar STRING) PARTITIONED BY (ds STRING);
復制一個空表
CREATE TABLE empty_key_value_store
LIKE key_value_store;
例子
create table user_info (user_id int, cid string, ckid string, username string)
row format delimited
fields terminated by '\t'
lines terminated by '\n';
導入數據表的數據格式是：欄位之間是tab鍵分割，行之間是斷行。
及要我們的文件內容格式：
100636 100890 c5c86f4cddc15eb7 yyyvybtvt
100612 100865 97cc70d411c18b6f gyvcycy
100078 100087 ecd6026a15ffddf5 qa000100
顯示所有表：
hive> SHOW TABLES;
按正條件（正則表達式）顯示表，
hive> SHOW TABLES '.*s';
修改表結構
?增加分區、刪除分區
?重命名表
?修改列的名字、類型、位置、注釋
?增加/更新列
?增加表的元數據信息
表添加一列 ：
hive> ALTER TABLE pokes ADD COLUMNS (new_col INT);
添加一列並增加列欄位注釋
hive> ALTER TABLE invites ADD COLUMNS (new_col2 INT COMMENT 'a comment');
更改表名：
hive> ALTER TABLE events RENAME TO 3koobecaf;
刪除列：
hive> DROP TABLE pokes;
增加、刪除分區
?增加
ALTER TABLE table_name ADD [IF NOT EXISTS] partition_spec [ LOCATION 'location1' ] partition_spec [ LOCATION 'location2' ] ...
partition_spec:
: PARTITION (partition_col = partition_col_value, partition_col = partiton_col_value, ...)
?刪除
ALTER TABLE table_name DROP partition_spec, partition_spec,...
重命名表
?ALTER TABLE table_name RENAME TO new_table_name
修改列的名字、類型、位置、注釋：
?ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]
?這個命令可以允許改變列名、數據類型、注釋、列位置或者它們的任意組合
表添加一列 ：
hive> ALTER TABLE pokes ADD COLUMNS (new_col INT);
添加一列並增加列欄位注釋
hive> ALTER TABLE invites ADD COLUMNS (new_col2 INT COMMENT 'a comment');
增加/更新列
?ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)
? ADD是代表新增一欄位，欄位位置在所有列後面(partition列前)
REPLACE則是表示替換表中所有欄位。
增加表的元數據信息
?ALTER TABLE table_name SET TBLPROPERTIES table_properties table_properties:
:[property_name = property_value…..]
?用戶可以用這個命令向表中增加metadata
改變表文件格式與組織
?ALTER TABLE table_name SET FILEFORMAT file_format
?ALTER TABLE table_name CLUSTERED BY(userid) SORTED BY(viewTime) INTO num_buckets BUCKETS
附上出處鏈接：http://blog.itpub.net/26613085/viewspace-1224043/
⑺ 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間隔，超過時間間隔則窗口關閉。
⑻ 如何查看hivesql里執行了哪些查詢
思路
當我們在終端下執行命令「hive」後，會看到有如下輸出：
Hive有會話（Session）的概念，而這次會話中的所有日誌消息將會輸出到這個日誌文件中，包含SQL語句的執行日誌，查看這個日誌文件可以看到以下信息：