借鑒hive官網(wǎng)：https://hive.apache.org/
經(jīng)典語句
[WITH CommonTableExpression (, CommonTableExpression)*] (Note: Only available starting with Hive 0.13.0)
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[ORDER BY col_list]
[CLUSTER BY col_list
| [DISTRIBUTE BY col_list] [SORT BY col_list]
]
[LIMIT [offset,] rows]
獲取當前數(shù)據(jù)庫
SELECT current_database()
show databases like '*gdw*'
查表名
use 庫名;
show tables like '*XXX*';
where子句
where條件表達式是一個布爾表達式
SELECT * FROM sales WHERE amount > 10 AND region = "US"
ALL和DISTINCT選項指定是否應(yīng)重復(fù)行。
若沒有給出這些選項，則默認值為ALL（返回所有匹配的行）疑枯。DISTINCT指定從結(jié)果集中刪除重復(fù)的行。注意千诬，Hive支持從版本1.1.0[HIVE-9194]開始的SELECT DISTINCT *
SELECT DISTINCT col1, col2 FROM t1
基于分區(qū)的查詢
一般來說，SELECT查詢掃描整個表（除了進行抽樣）。如果使用PARTITIONED BY子句創(chuàng)建表挪蹭，則查詢可以執(zhí)行分區(qū)修剪鲸湃，并僅掃描與查詢指定的分區(qū)相關(guān)的表的一小部分赠涮。如果在WHERE子句或JOIN中的ON子句中指定了分區(qū)謂詞，則Hive目前正在進行分區(qū)修剪暗挑。例如笋除，如果表page_views在列日期分區(qū)，則以下查詢將在2008-03-01和2008-03-31之間的幾天內(nèi)檢索行炸裆。
##where分區(qū)
SELECT page_views.*
FROM page_views
WHERE page_views.date >= '2008-03-01' AND page_views.date <='2008-03-31'
##on分區(qū)
SELECT page_views.*
FROM page_views JOIN dim_users
ON (page_views.user_id = dim_users.id AND page_views.date >= '2008-03-01' AND page_views.date <= '2008-03-31')
HAVING子句垃它，若有g(shù)roup by 子句的話，having子句要放到group by 之后晒衩。
SELECT col1 FROM t1 GROUP BY col1 HAVING SUM(col2) > 10
#同理
SELECT col1 FROM (SELECT col1, SUM(col2) AS col2sum FROM t1 GROUP BY col1) t2 WHERE t2.col2sum > 10
LIMIT子句
LIMIT子句可用于約束SELECT語句返回的行數(shù)嗤瞎。LIMIT需要一個或兩個數(shù)字參數(shù)，它們必須都是非負整數(shù)常量听系。
第一個參數(shù)指定要返回的第一行的偏移量贝奇，第二個參數(shù)指定要返回的最大行數(shù)。
當給定一個參數(shù)時靠胜，它代表最大行數(shù)掉瞳，而指定要返回的第一行的偏移量默認值為0。
#查詢返回5個任意客戶
SELECT * FROM customers LIMIT 5
#查詢返回創(chuàng)建的前5個客戶
SELECT * FROM customers ORDER BY create_date LIMIT 5
#查詢返回第3到第7個創(chuàng)建的客戶
SELECT * FROM customers ORDER BY create_date LIMIT 2,5
正則表達書浪漠，可以使用基于正則表達式的列規(guī)范
SELECT `(ds|hr)?+.+` FROM sales
group by 子句
SELECT pv_users.gender, count (DISTINCT pv_users.userid)
FROM pv_users
GROUP BY pv_users.gender;
Order, Sort, Cluster, and Distribute By
oder by
Hive中的order by跟傳統(tǒng)的sql語言中的order by作用是一樣的陕习，會對查詢的結(jié)果做一次全局排序，所以說址愿，只有hive的sql中制定了order by所有的數(shù)據(jù)都會到同一個reducer進行處理（不管有多少map该镣，也不管文件有多少的block只會啟動一個reducer）。但是對于大量數(shù)據(jù)這將會消耗很長的時間去執(zhí)行响谓。
這里跟傳統(tǒng)的sql還有一點區(qū)別：如果指定了hive.mapred.mode=strict（默認值是nonstrict）,這時就必須指定limit來限制輸出條數(shù)损合，原因是：所有的數(shù)據(jù)都會在同一個reducer端進行，數(shù)據(jù)量大的情況下可能不能出結(jié)果娘纷，那么在這樣的嚴格模式下嫁审，必須指定輸出的條數(shù)。
sort by
Hive中指定了sort by赖晶，那么在每個reducer端都會做排序律适，也就是說保證了局部有序（每個reducer出來的數(shù)據(jù)是有序的，但是不能保證所有的數(shù)據(jù)是有序的，除非只有一個reducer）捂贿，好處是：執(zhí)行了局部排序之后可以為接下去的全局排序提高不少的效率（其實就是做一次歸并排序就可以做到全局排序了）纠修。
“order by”和“sort by”之間的區(qū)別是前者保證輸出中的總順序，而后者僅保證在reducer中的行的排序厂僧。
Hive使用SORT BY中的列對行進行排序分瘾，然后將行提供給reducer。排序順序?qū)⒁蕾囉诹蓄愋陀跸怠Ｈ绻惺菙?shù)字類型，則排序順序也以數(shù)字順序排列白魂。如果列是字符串類型汽纤，則排序順序?qū)⑹亲值漤樞颉?/p>
distribute by和sort by一起使用
ditribute by是控制map的輸出在reducer是如何劃分的
select mid, money, name
from store
distribute by mid sort by mid asc, money asc
我們所有的mid相同的數(shù)據(jù)會被送到同一個reducer去處理，這就是因為指定了distribute by mid福荸，這樣的話就可以統(tǒng)計出每個商戶中各個商店盈利的排序了
cluster by的功能就是distribute by和sort by相結(jié)合
cluster by指定的列只能是降序蕴坪，不能指定asc和desc。
select mid, money, name from store cluster by mid
#等價
select mid, money, name from store distribute by mid sort by mid
注意
select mid, money, name
from store
cluster by mid sort by money
#等價
select mid, money, name
from store
distribute by mid sort by mid , money
JSON查詢
select get_json_object(字段,'$.Name')
衍生1：pg用法
select each_json_text(字段->>'Name')
衍生2：生成json字段
select
user_id,
concat_ws(';',collect_set(ddd_id,':',name)) 特征
from table_name
group by user_id ;