Hive基礎實戰
文章分享Hive在工作中常用的語法，函式，以及最佳化方法，實用性更強，將持續更新。
1。資料庫檢視篇
檢視資料庫
show databases
；
切換資料庫
use databases_name
；
檢視當前資料庫
select current_database（）
；
2。表定義操作篇
2。1表屬性檢視
查看錶結構
desc table_name
；
查看錶屬性資訊
desc formatted table_name
；
查看錶分割槽情況
show partitions table_name；
2。2表定義create
查看錶定義語句
show create table table_name
建立表create table
create
temporary
/
external
table
db_name
。
table_name
（
）
partitioned
by
（
）
stored
as
［
json
，
sco
，
textfile
，
parquet
］
；
create
table
L_ITC
。
VT_MID_TEMP
/* 中間過程表 */
（
var_01
string
comment
‘變數1’
/* 變數1 */
，
var_02
string
comment
‘變數2’
/* 變數2 */
，
var_03
string
comment
‘變數3’
/* 變數2 */
）
COMMENT
‘中間過程表’
PARTITIONED
BY
（
prt_dt
string
）
STORED
AS
ORC
；
2。3表插入insert
insert into ……values……
insert into table L_ITC。VT_MID_TEMP partition（prt_dt） values（‘Jom’，‘boy’，‘teacher’，‘2020-03-17’）；
2。insert into ……select……
insert into table L_ITC。VT_MID_TEMP partition（prt_dt）
select
a。var_01
，a。var_02
，a。var_03
，‘2020-03-18’
from L_ITC。VT_MID_TEMP
where prt_dt = ‘2020-03-17’
；
3。insert overwrite…… values……
insert overwrite table L_ITC。VT_MID_TEMP partition （prt_dt） values （‘a’，‘b’，‘c’，‘2020-03-19’）；
4。insert overwrite……select……
insert overwrite table L_ITC。VT_MID_TEMP partition （prt_dt）
select
a。var_01
，a。var_02
，a。var_03
，‘2020-03-18’
from L_ITC。VT_MID_TEMP
where prt_dt = ‘2020-03-17’
；
PS: insert into 和 insert overwrite的區別是into 是追加，overwrite是覆蓋重寫。
2。3表查詢 select
1。檢視變數值
select current_date ，current_timestamp
；
2。欄位命名別名 Hive中命名別名的符號用‘`’，不能用單引號
select ‘Jame’ as `name`；
select ‘Jame’ as name；
select ‘Jame’ name；
ps：如果是命名英文欄位名這三種方式都可以，如果是中文欄位名只能用第一種方式.
select ‘Jame’ as `姓名` ——ok
select ‘Jame’ 姓名 ——error
select ‘Jame’ as ‘姓名’ ——error
select ‘Jame’ as “姓名” ——error
3。關鍵字
3。1 limit
提高查詢效率
select * from table limit 50
3。2 with儲存臨時結果
with temp_data as （select cust_id from VT_CUST_INF group by cust_id）；
4。函式使用篇
替換變數
select replace（‘cmbchina’，‘cmb’，‘xxx’）
；
字串拼接
select concat（‘a’，‘b’，‘c’）
；
locate（）查詢字元位置
select locate（‘|’，‘42。65|，52。65’，1）； ——字元‘|’第一次出現為位置
4。 substr（）擷取子字串
select substr（‘hello，world’，5，7）；
——從第五位開始擷取7個字串，不加擷取長度則擷取第五位後所有字元；
5。 get_json_object（） 在string型別欄位中json資料提取
select get_json_object（‘{“TranCacheID”：“00305095”，“TxsSet”：“K05731U304AACBA”，“PayeeAccId”：“6210721000002026745”}’，‘$。TxsSet’）；
——擷取名稱為‘TxsSet’的值；
/* 欄位［lgn_adt］ 資料型別［string］ 中文名稱：［登入附加資訊］
欄位取值 lgn_adt = {“TranCacheID”：“00305095”，“TxsSet”：“1U304AACBA”*/
select get_json_object（a。lgn_adt，‘$。TxsSet’）
from P_VC。VT_TRX_INF as a；
6。 map型別資料提取
map資料的格式為
提取方法：
map_value = map_name['key_name']
/*欄位［unionpay_trx_inf］ 資料型別map
/*欄位取值unionpay_trx_inf={
“isscode”：“49990010”
，“trnamt”：“6。05”
，“mchname”：“歐陽喬峰”
，“appid”：“1E1000002019”} */
select
pay_trx_inf
，pay_trx_inf［‘isscode’］ idcode /* 交易號 */
，pay_trx_inf［‘trnamt’］ trnamt /* 交易金額 */
，pay_trx_inf［‘mchname’］ mchname /* 使用者號 */
from P_VC。VT_TRX_INF as a
；
7。 url解析
select parse_url（url，partToExtract）
——從網址中擷取部分內容
——partToExtract的取值包括［HOST，PATH，QUERY，REF，PROTOCOL，FILE，AUTHORITY，USERINFO］
select parse_url（‘http：//facebook。com/path/p1。php？query=1’，‘HOST’） ——facebook
select parse_url（‘http：//facebook。com/path/p1。php？query=1’，‘QUERY’） ——query=1
select parse_url（‘http：//facebook。com/path/p1。php？query=1’，‘QUERY’，‘query’） ——1
5。效能最佳化
5。1 表關聯
在Hive中的JOIN操作中，連線條件並不支援非等值連線。
具體為各種join語句後on關鍵字的連線條件，並不支援形如a like ‘b%’的非等值連線，請儘量使用等值關聯操作後在where條件中處理相關邏輯。
5。2 查詢最佳化
禁止對分割槽表進行整表掃描，查詢語句必須指定分割槽範圍；
在使用order by進行順序輸出時，必須攜帶limit關鍵字；
count（distinct）是個效能殺手；
使用explain命令觀察任務計劃進行效能調優；
