1.日期格式轉(zhuǎn)換（將yyyymmdd轉(zhuǎn)換為yyyy-mm-dd）
select from_unixtime(unix_timestamp('20180905','yyyymmdd'),'yyyy-mm-dd')
2.hive修改庫名赌厅、表名注釋利赋、表屬性
----修改庫名注釋
alter database db_name set dbproperties('comment'='ISIC-BRMS-CORE');
----修改表名注釋
alter table tbl_name set tblproperties ('comment'='各組織人員流失率統(tǒng)計(jì)表');
---將外部表修改為內(nèi)部表徒蟆，需要刪除表的數(shù)據(jù)文件時击孩，可以將外部表轉(zhuǎn)換成內(nèi)部表涮母，再直接刪除表，比較安全
alter table tbl_name set TBLPROPERTIES('EXTERNAL'='FALSE')
3..hive去掉字段中除字母和數(shù)字外的其它字符
select regexp_replace(a, '[^0-9a-zA-Z]', '') from tbl_name
4.hive解析json字段
content字段存儲json {"score":"100","name":"zhou","class":''math"}携添，若要對json進(jìn)行解析嫁盲，則可用以下方式
---解析單個字段
select get_json_object(content,'$.score') ,
get_json_object(content,'$.name),
get_json_object(content,'$.class')
from tbl_name
---解析多個字段可以用json_tuple
select a.*
,b.score
,b.name
,b.class
from tbl a
lateral view outer json_tuple(a.content,'score', 'name', 'class') b as score,name,class
5.hive 導(dǎo)入數(shù)據(jù)
若從本地文件系統(tǒng)上傳，需要加上local關(guān)鍵字;如果直接從hdfs路徑上傳薪寓，則不加local
load data [local] inpath '/data/monthcard.csv' overwrite into table tbl_name;
6.hive 避免科學(xué)計(jì)數(shù)法
select printf("%.2f",3.428777027500007E7)
7.hive collect_set和lateral view explode用法
原始數(shù)據(jù)
id1 id2 name
1 1 A
1 1 B
1 1 C
1 2 X
1 2 Y
(1)collect_set
select id1,id2,
collect_set(name) as new_name1,
collect_set(case when id2>1 then name end) as new_name2,
count(name) as cnt
from default.zql_test
group by id1,id2;
---輸出結(jié)果
OK
id1 id2 new_name1 new_name2 cnt
1 1 ["C","A","B"] [] 3
1 2 ["X","Y"] ["X","Y"] 2
(2)lateral view explode
select *
from
(
select id1,id2,
collect_set(name) as new_name1,
collect_set(case when id2>1 then name end) as new_name2,
count(name) as cnt
from default. zql_test
group by id1,id2
)t
lateral view explode(new_name1) t as new_type1
lateral view explode(new_name2) t as new_type2
----輸出結(jié)果
OK
t.id1 t.id2 t.new_name1 t.new_name2 t.cnt t.new_type1 t.new_type2
1 2 ["Y","X"] ["Y","X"] 2 Y Y
1 2 ["Y","X"] ["Y","X"] 2 Y X
1 2 ["Y","X"] ["Y","X"] 2 X Y
1 2 ["Y","X"] ["Y","X"] 2 X X
(3)lateral view explode outer 亡资，加上outer會保留所有記錄澜共，兩者差異可以參考之前的專題
select *
from
(
select id1,id2,
collect_set(name) as new_name1,
collect_set(case when id2>1 then name end) as new_name2,
count(name) as cnt
from default. zql_test
group by id1,id2
)t
lateral view outer explode(new_name1) t as new_type1
lateral view outer explode(new_name2) t as new_type2
;
----輸出結(jié)果
OK
t.id1 t.id2 t.new_name1 t.new_name2 t.cnt t.new_type1 t.new_type2
1 1 ["B","A","C"] [] 3 B NULL
1 1 ["B","A","C"] [] 3 A NULL
1 1 ["B","A","C"] [] 3 C NULL
1 2 ["X","Y"] ["X","Y"] 2 X X
1 2 ["X","Y"] ["X","Y"] 2 X Y
1 2 ["X","Y"] ["X","Y"] 2 Y X
1 2 ["X","Y"] ["X","Y"] 2 Y Y
8.hive取前百分之幾
---分組內(nèi)將數(shù)據(jù)分成兩片
ntile(2)over(partition by id order by create_tm)
9.hive返回星期幾的方法
---2012-01-01剛好星期日
select pmod(datediff(from_unixtime(unix_timestamp()),'2012-01-01'),7) from default.dual;
--返回值0-6
--其中0代表星期日
10.hive產(chǎn)生uuid
select regexp_replace(reflect("java.util.UUID", "randomUUID"), "-", "");
11.hive中匹配中文
select regexp '[\\u4e00-\\u9fa5]';
12.hive中regexp_extract的用法
regexp_extract(string subject, string regex_pattern, string index)
說明：抽取字符串subject中符合正則表達(dá)式regex_pattern的第index個部分的字符串
第一參數(shù)： 要處理的字段
第二參數(shù): 需要匹配的正則表達(dá)式
第三個參數(shù):
0是顯示與之匹配的整個字符串
1 是顯示第一個括號里面的
2 是顯示第二個括號里面的字段...
舉例：
--取一個連續(xù)17位為數(shù)字的字符串向叉，且兩端為非數(shù)字
select regexp_extract('1、非訂單號(20位):00123456789876543210嗦董；
2母谎、訂單號(17位):12345678987654321；
3京革、其它文字','[^\\d](\\d{17})[^\\d]',0) as s1
, substr(regexp_extract('1奇唤、非訂單號(20位):01234567898765432100；
2匹摇、訂單號(17位):12345678987654321咬扇；
3、其它文字','[^\\d](\\d{17})[^\\d]',0),2,17) as s2
,regexp_extract('1廊勃、非訂單號(20位):00123456789876543210懈贺；
2、訂單號(17位):12345678987654321坡垫；
3梭灿、其它文字','[^\\d](\\d{17})[^\\d]',1) as s3;
13.hive中快速復(fù)制一張分區(qū)表及數(shù)據(jù)
- CREATE TABLE new_table LIKE old_table;
- 使用hadoop fs -cp 命令，把old_table對應(yīng)的HDFS目錄的文件夾全部拷貝到new_table對應(yīng)的HDFS目錄下冰悠；
hadoop fs -cp /hivedata/warehouse/liuxiaowen.db/t1/* /hivedata/warehouse/liuxiaowen.db/t2/ - 使用MSCK REPAIR TABLE new_table;修復(fù)新表的分區(qū)元數(shù)據(jù)堡妒；
14.hive中排序方法
ORDER BY用于全局排序，就是對指定的所有排序鍵進(jìn)行全局排序溉卓，使用ORDER BY的查詢語句皮迟，最后會用一個Reduce Task來完成全局排序搬泥。
SORT BY用于分區(qū)內(nèi)排序，即每個Reduce任務(wù)內(nèi)排序伏尼。
distribute by：按照指定的字段或表達(dá)式對數(shù)據(jù)進(jìn)行劃分佑钾，輸出到對應(yīng)的Reduce或者文件中。
cluster by：除了兼具distribute by的功能烦粒，還兼具sort by的排序功能休溶。
15.hive讀寫模式
1.Hive處理的數(shù)據(jù)是大數(shù)據(jù)，在保存表數(shù)據(jù)時不對數(shù)據(jù)進(jìn)行校驗(yàn)扰她，而是在讀數(shù)據(jù)時校驗(yàn)兽掰，所以，hive這種讀的模式徒役，加載數(shù)據(jù)很快孽尽，減少延遲。而在數(shù)據(jù)具體使用的時候忧勿，再去處理杉女，極高地提高了效率。如果補(bǔ)數(shù)據(jù)不符合建表規(guī)范鸳吸，比如：表字段是int類型熏挎，而導(dǎo)入的是string類型，則hive會保存為null晌砾。讀時模式的優(yōu)點(diǎn)是坎拐，加載數(shù)據(jù)庫快。
2.傳統(tǒng)的數(shù)據(jù)庫如mysql养匈、oracle是寫時模式哼勇，不符合格式的數(shù)據(jù)寫不進(jìn)去。RDBMS是寫模式Hive是讀模式我們傳統(tǒng)的關(guān)系型數(shù)據(jù)庫RDBMS是寫模式呕乎。在RDBMS里积担，我們對表進(jìn)行數(shù)據(jù)操作時候，RDBMS會用數(shù)據(jù)庫的第一第二第三范式去檢查數(shù)據(jù)的規(guī)范性猬仁，如果不符合規(guī)范帝璧，數(shù)據(jù)庫就拒絕數(shù)據(jù)的加載和操作。這個驗(yàn)證過程消耗資源逐虚，在數(shù)據(jù)量大的時候聋溜，會影響效率。因?yàn)槿粘５年P(guān)系型數(shù)據(jù)庫處理的數(shù)據(jù)不是很多叭爱，效率慢點(diǎn)沒關(guān)系撮躁。
大數(shù)據(jù)在對數(shù)據(jù)的加載的時候不進(jìn)行校驗(yàn)，如果校驗(yàn)將降低效率买雾，導(dǎo)致消息阻塞把曼，影響數(shù)據(jù)庫的讀取效率杨帽。
關(guān)系型數(shù)據(jù)庫RDBMS主要處理的是結(jié)構(gòu)化的數(shù)據(jù)，對數(shù)據(jù)源本身就有很好的規(guī)范嗤军，加載結(jié)構(gòu)化有規(guī)范的數(shù)據(jù)時候注盈，可以進(jìn)行校驗(yàn)。而hadoop等的大數(shù)據(jù)平臺保存的數(shù)據(jù)源有些是結(jié)構(gòu)化的叙赚，有些不是結(jié)構(gòu)化的老客，無規(guī)律的，沒聯(lián)系的數(shù)據(jù)震叮。無法進(jìn)行校驗(yàn)胧砰，檢測。如果檢測苇瓣，將導(dǎo)致大量不符合第一第二第三范式的數(shù)據(jù)不能保存
16.hive開窗函數(shù)
（鏈接地址這位作者寫的非常好尉间，值得學(xué)習(xí)http://lxw1234.com/archives/2015/04/181.htm）
17.find_in_set
-----返回str在strlist第一次出現(xiàn)的位置，strlist是用逗號分割的字符串击罪。如果沒有找該str字符哲嘲，則返回0（只能是逗號分隔，不然返回0）
select find_in_set(‘a(chǎn)b’,'ef,ab,de’) ;
18.hive中查詢函數(shù)的用法
desc function concat
----查看某個函數(shù)怎么使用的例子
desc function extended concat
