Hive 快速上手
Hive 快速上手
本人大數(shù)據(jù)專業(yè)學生，本文檔最早是在學校上這門課時候的筆記。后來系統(tǒng)重裝重裝hive補充完善了這個筆記，今天偶然翻到，看格式應該是我當時打算發(fā)布來著，但是后來忘記了。特此補發(fā)。內(nèi)容主要來自于本校老師教學時自己編寫的文檔和網(wǎng)絡資料。(注:發(fā)布時間是2018年9月初)
本文旨在快速學習或者回顧hive常用知識，閱讀本文檔需要二十分鐘，完成后你將上手hive。
外部表和內(nèi)部表
內(nèi)部表（managed table）
外部表（external table）
存儲格式為 Sequencefile時的一個數(shù)據(jù)導入問題
指定存儲格式為 Sequencefile 時，把txt格式的數(shù)據(jù)導入表中，hive 會報文件格式錯，解決方案為先將txt格式傳入hive，然后利用傳入表格插入Sequencefile格式表格
load data local inpath '/home/fonttian/database/hive/students2' overwrite into table students3;# 創(chuàng)建外部表 create external table if not exists students3_orc(name string,age int,sex string,brithday date)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'; # 從其他表格中插入數(shù)據(jù) insert into table students3 select * from students2; insert into table students3_orc select * from students3;分區(qū)
# 創(chuàng)建外部表，利用date字段進行分區(qū) create external table if not exists students4(name string,age int,sex string,brithday date) partitioned by (day date) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';# 導入數(shù)據(jù)進入分區(qū)外表，分區(qū)為 day="2018-3-26" load data local inpath '/home/fonttian/database/hive/students2' into table students4 partition (day="2018-3-26");# 如果查詢無效，可以使用下面的代碼create external table if not exists students5(name string,age int,sex string,brithday date) partitioned by (pt_int int) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';load data local inpath '/home/fonttian/database/hive/students2' into table students5 partition (pt_int=1); load data local inpath '/home/fonttian/database/hive/students2' into table students5 partition (pt_int=2);select * from students5; select * from students5 where pt_int = 1; select * from students5 where pt_int > 0;# 創(chuàng)建外部表 create external table if not exists students3_parquet(name string,age int,sex string,brithday date)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' stored as parquet;insert into table students3_parquet select * from students3;# 查詢 SELECT * FROM students2 WHERE age>30 && Dept=TP;# 查看是否為分區(qū)表 show partitions # 或者使用查勘表結(jié)構(gòu)的命令 describe extended students5; desc formatted students5; # delete partition alter table students5 drop partition(pt_int=2);數(shù)據(jù)的導出
# 導出數(shù)據(jù)-insert方式 insert overwrite local directory "/home/fonttian/database/hive/learnhive" select * from students5;但是這種導出方式不利于直接訪問導出數(shù)據(jù)，分隔符的問題，默認使用“^A（\x01）”分隔符
利用格式化導出自定義我們自己的分隔符，或者流式導出將沒有這個問題
insert overwrite local directory "/home/fonttian/database/hive/learnhive" row format delimited fields terminated by '\t' collection items terminated by '\n' select * from students5;# 流式導出，需要在shell中進行 bin/hive -e "use class;select * from students5;" > /home/fonttian/database/hive/learnhive/students5.txt如果想要導出到HDFS只需要，將“l(fā)ocal”關(guān)鍵字去掉即可
DML
查詢
分組(group by/having)
每個部?門的平均工工資
每個部?門中每個崗位的最高高工工資
查詢出每個部?門的平均工工資超過2000的部?門
表連接(join)
排序
order by
全局排序
對全局數(shù)據(jù)的一一個排序,僅僅只有一一個reduce
sort by
對每一一個reduce內(nèi)部數(shù)據(jù)進行行行排序,對全局結(jié)果集來說不不排序
# 如果有必要需要先進行調(diào)優(yōu) # set hive.exec.reducers.max=<number> # set mapreduce.job.reduces=<number># 按照年齡排序，查詢student5表 select * from students5 sort by age asc;distribute by
類似于MapReduce中分區(qū),對數(shù)據(jù)進行行行分區(qū),結(jié)合sort by進行使用，同樣要注意的是這里我們還是需要進行數(shù)據(jù)的格式化，這樣才可以直接讀取數(shù)據(jù)
insert overwrite local directory '/home/fonttian/database/hive/learnhive/students5_distribute_by' row format delimited fields terminated by '\t' collection items terminated by '\n' select * from students5 distribute by pt_int sort by age asc;注意事項:
distribute by必須在sort by之前
cluster by
當distribute by字段和sort by字段相同時,就可以替代使用用。
join
- Hive只支持等值連接，外連接和左半連接。
首先需要導入一波數(shù)據(jù)備用
# 創(chuàng)建外部表 create external table if not exists score(name string,math int,chinese int,english int)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' stored as textfile;# 導入數(shù)據(jù) load data local inpath '/home/fonttian/database/hive/score' overwrite into table score;# 創(chuàng)建外部表 create external table if not exists job(name string,likes string)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' stored as textfile;# 導入數(shù)據(jù) load data local inpath '/home/fonttian/database/hive/job' overwrite into table job; ```sql - 可以連接兩個以上表select students2.name ,students2.age,score.math,job.likes from students2 join score on(students2.name = score.name) join job on (job.name=score.name);
- 如果連接多個表的join key 是同一個，則被轉(zhuǎn)化為單個map/reduce任務 - join時大表放在最后。因為每次map/reduce任務的邏輯是這樣的：reduce會緩存join序列中最后一個表之外的所有的表額記錄，再通過最后一個表序列化到文件系統(tǒng)中。 - 如果想要限制join的輸出，就需要在where子句中寫過濾條件，或是在join子句寫。建議后者，以避免部分錯誤發(fā)生。```sql select students5.name,score.math from score left outer join students5 on(score.name = students5.name and students5.pt_int = 1);select students5.name,score.math from students5 left outer join score on(score.name = students5.name and students5.pt_int = 1); ```sql - Left SEMI JOIN 是IN/EXISTS子查詢的一種更高效的實現(xiàn)。其限制為：join子句中的右邊表只能在ON自劇中設(shè)置過濾條件，where子句。select子句或其他過濾地方都不行```sql select job.name,job.likes from job where job.name in (select score.name from score); select job.name,job.likes from job left semi join score on (score.name = job.score);正則表達式
regexp 關(guān)鍵字
語法: A REGEXP B
操作類型: strings
描述: 功能與RLIKE相同
select count(*) from students5 where name not regexp '\\d{8}'; # 統(tǒng)計，name開頭不是T的數(shù)據(jù)行數(shù) beelin >select count(*) from students5 where name not regexp 'T.*';regexp_extract 關(guān)鍵字
語法: regexp_extract(string subject, string pattern, int index)
返回值: string
說明：將字符串subject按照pattern正則表達式的規(guī)則拆分，返回index指定的字符。
# 將字符串'IloveYou'按照'(I)(.*?)(You)'拆分，返回第一處字符，結(jié)果為I select regexp_extract('IloveYou','(I)(.*?)(You)',1) from students5 limit 1; # 將字符串'IloveYou'按照'(I)(.*?)(You)'拆分，返回第一處字符，結(jié)果為You select regexp_extract('IloveYou','I(.*?)(You)',2) from students5 limit 1; # 返回全部-結(jié)果‘IloveYou’ select regexp_extract('IloveYou','(I)(.*?)(You)',0) from students5 limit 1;regexp_replace 關(guān)鍵字
語法: regexp_replace(string A, string B, string C)
返回值: string
說明：將字符串A中的符合Java正則表達式B的部分替換為C。注意，在有些情況下要使用轉(zhuǎn)義字符,類似Oracle中的regexp_replace函數(shù)。
# 返回結(jié)果：‘Ilove’ select regexp_replace("IloveYou","You","") from students5 limit 1; # 返回：‘Ilovelili’ select regexp_replace("IloveYou","You","lili") from test1 limit 1;beeline and hivesever2
# 后臺啟動 $ nohup bin/hive --service hiveserver2 & # 查看hive是否啟動 $ ps -aux| grep hiveserver2 # 關(guān)閉 $ kill -9 20670$ bin/beeline # 使用默認賬戶連接hive beeline> !connect jdbc:hive2://localhost:10000 scott tiger # 使用配置中的賬戶密碼連接hive beeline> !connect jdbc:hive2://localhost:10000 fonttian 123456 # 退出 beeline> !quit參考內(nèi)容
總結(jié)
- 上一篇： Lightgbm with Hypero
- 下一篇： Hyperopt 入门指南