hivesqlin
⑴ hive sql 怎麼不能這樣寫啊 (mysql 是沒有問題的)
你可以先用hive或者手寫map/rece程序。建議你直接使用hive,hive也像mysql一樣可以建表。表裡的數據就是對應從本地load到hadoop上的文件,格式自己定。然後用JDBC可以像操作mysql一樣操作hive(sql語法稍有不同)
⑵ Hive的left join,left outer join和left semi join三者的區別
貼一直復制過來的。。hive不支持『left join』的寫法(我記得使用的時候好像報錯了,最後都改成了outer join)hive的left outer join:如果右邊有多行和左邊表對應,就每一行都映射輸出;如果右邊沒有行與左邊行對應,就輸出左邊行,右邊表欄位為NULL;hive的left semi join:相當於SQL的in語句,比如上面測試3的語句相當於「select * from table1 where table1.student_no in (table2.student_no)」,注意,結果中是沒有B表的欄位的。
⑶ 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中哪個隊列比較閑
解決方案
MapRece運行隊列的指定是通過配置(Configuration)屬性「maprece.job.queuename」指定的。
大家可能首先想到的是通過「set maprece.job.queuename=queueName」的方式來選取運行隊列,這在手動任務(臨時分析需求)的場景下是不可取的,如前所述,我們為這類似的任務專門分配了相應的隊列資源「hive.temporary」,我們必須能夠保證用戶通過Beeline連接HiveServer2後提交的Hive SQL語句運行在指定的隊列「hive.temporary」中,而且用戶無法隨意更改運行隊列,即無法隨意更改屬性「maprece.job.queuename」。
目前HiveServer2使用的許可權控制策略為SQL Standard Based Hive Authorization和Storage Based Authorization in the Metastore Server。其中SQL Standard Based Hive Authorization會對Hive終端命令「set」做出限制:只能針對白名單(hive.security.authorization.sqlstd.confwhitelist)中列出的屬性進行賦值。白名單默認包含一批屬性,其中就包括「maprece.job.queuename」,我們需要通過配置文件hive-site.xml或者啟動HiveServer2時通過參數「--hiveconf」設置白名單「hive.security.authorization.sqlstd.confwhitelist」的值,排除屬性「maprece.job.queuename」,使得我們的用戶通過Beeline連接至HiveServer2之後,無法隨意更改「maprece.job.queuename」的值。
既然用戶無法更改屬性「maprece.job.queuename」,那麼HiveServer2啟動之後屬性「maprece.job.queuename」必須具有一個默認值,即「hive.temporary」,這樣用戶通過Beeline連接HiveServer2之後提交的Hive SQL就會運行在隊列「hive.temporary」中。那麼,接下來的問題就是如果完成這個默認設定?
一般情況下,我們會這樣認為,HiveServer2的運行至少涉及到兩份配置文件:
(1)Hadoop:core-site.xml、hdfs-site.xml、mapred-site.xml、yarn-site.xml
(2)Hive:hive-site.xml
這些配置文件中的屬性值都會「打包」到MapRece任務的配置屬性中去。我們自然會想到在mapred-site.xml或者hive-site.xml中指定「maprece.job.queuename」即可,然而實際驗證之後發現情況並不是這樣的。
(1)在hive-site.xml(mapred-site.xml)中指定「maprece.job.queuename」;
⑸ hive join支持using么
hive支持』left join』寫; hiveleft outer join:右邊行左邊表應,每行都映射輸;右邊沒行與左邊行應,輸左邊行,右邊表欄位NULL; hiveleft semi join:相於SQLin語句 兩測試數據表建表語句...
⑹ hive not in 通過什麼實現
目前hive不支持 in或not in 中包含查詢子句的語法,所以只能通過left join實現。
假設有一個登陸表login(當天登陸記錄,只有一個uid),和一個用戶注冊表regusers(當天注冊用戶,欄位只有一個uid),這兩個表都包含一個欄位,uid。
in查詢
如果要查詢當天登陸的注冊用戶,需要用in查詢,hive sql如下:
select login.uid from login left outer join regusers on login.uid=regusers.uid where regusers.uid is not null
如果login表和regusers表按天分區,欄位是dt,那麼查詢2013年1月1號當天登陸的注冊用戶,hive sql如下:
select login.uid from login day_login left outer join
(select uid from regusers where dt='20130101') day_regusers
on day_login.uid=day_regusers.uid where day_login.dt='20130101' and day_regusers.uid is not null
not in查詢
如果要查詢當天登陸的老用戶(這里假設非當天注冊用戶就是老用戶),需要用not in查詢,hive sql如下:
select login.uid from login left outer join regusers on login.uid=regusers.uid where regusers.uid is null;
如果login表和regusers表按天分區,欄位是dt,那麼查詢2013年1月1號當天登陸的老用戶,hive sql如下:
select login.uid from login day_login left outer join
(select uid from regusers where dt='20130101') day_regusers
on day_login.uid=day_regusers.uid where day_login.dt='20130101' and day_regusers.uid is null;
Hive join優化
========================================================
由
於 hive 與傳統關系型資料庫面對的業務場景及底層技術架構都有著很大差異,因此,傳統資料庫領域的一些技能放到 Hive 中可能已不再適用。關於
hive 的優化與原理、應用的文章,前面也陸陸續續的介紹了一些,但大多都偏向理論層面,本文就介紹一個實例,從實例中一步步加深對 hive
調優的認識與意識。
1、需求
需求我做了簡化,很簡單,兩張表做個 join,求指定城市,每天的 pv,用傳統的 RDBMS SQL 寫出來就這樣的:
?
1
2
3
4
5
6
7
8
9
10
11
SELECT t.statdate,
c.cname,
count(t.cookieid)
FROM tmpdb.city c
JOIN ecdata.ext_trackflow t ON (t.area1= c.cname
OR t.area2 =c.cname
OR t.area3 = c.cname)
WHERE t.statdate>='20140818' and t.statdate<='20140824'
AND platform='pc'
GROUP BY t.statdate,
c.cname;
怎麼樣?根據 SQL 看懂需求沒問題吧?
2、非等值 join 問題
然後把這條 SQL 貼到 hive 中去執行,然後你會發現報錯了:
?
1
FAILED: SemanticException [Error 10019]: Line 5:32 OR not supported in JOIN currently 'cname'
這是因為 hive 受限於 MapRece 演算法模型,只支持 equi-joins(等值 join),要實現上述的非等值 join,你可以採用笛卡兒積( full Cartesian proct )來實現:
?
1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT t.statdate,
c.cname,
count(t.cookieid)
FROM tmpdb.city c
JOIN ecdata.ext_trackflow t
WHERE t.statdate>='20140818'
AND t.statdate<='20140824'
AND platform='pc'
AND (t.area1= c.cname
OR t.area2 =c.cname
OR t.area3 = c.cname)
GROUP BY t.statdate,
c.cname;
然後再拿著這條語句執行下。
3、優化:rece side join VS Cartesian proct
如果你真的把這條語句放到 Hive 上執行,然後恰好你有張表還非常大,那麼恭喜你。。。集群管理員估計會找你的麻煩了。。。
友情提示:笛卡兒積這種語句在 Hive 下慎用,大數據場景下的 m * n 映射結果你懂的。。。對此,Hive 特意提供了一個環境變數:hive.mapred.mode=strict; 防止笛卡兒積的執行:
?
1
FAILED: SemanticException [Error 10052]: In strict mode, cartesian proct is not allowed. If you really want to perform the operation, set hive.mapred.mode=nonstrict
從 2 中的觀察得知我們在 on 後面跟 join
條件,走的是 rece side join,如果你在 where 後跟則是走 Cartesian proct,但是這里單條 sql
又沒法實現 rece side join,還有沒有其它辦法呢?
4、改寫非等值 join:union all
既然不允許非等值 join,那我們換一下思路,多個子查詢 union all,然後匯總:
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
SELECT dt,
name,
count(cid)
FROM
(SELECT t.statdate dt,
c.cname name,
t.cookieid cid
FROM tmpdb.city c
JOIN ecdata.ext_trackflow t ON t.area1 =c.cname
WHERE t.statdate>='20140818'
AND t.statdate<='20140824'
AND platform='pc'
UNION ALL SELECT t.statdate dt,
c.cname name,
t.cookieid cid
FROM tmpdb.city c
JOIN ecdata.ext_trackflow t ON t.area2 =c.cname
WHERE t.statdate>='20140818'
AND t.statdate<='20140824'
AND platform='pc'
UNION ALL SELECT t.statdate dt,
c.cname name,
t.cookieid cid
FROM tmpdb.city c
JOIN ecdata.ext_trackflow t ON t.area3 =c.cname
WHERE t.statdate>='20140818'
AND t.statdate<='20140824'
AND platform='pc') tmp_trackflow
GROUP BY dt,
name;
5、優化:map side join
上述語句走的是 rece side join,從我們的需求及業務得知,tmpdb.city 是一張字典表,數據量很小,因此我們可以試試把上述的語句改寫成 mapjoin:
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
SELECT dt,
name,
count(cid)
FROM
(SELECT /*+ MAPJOIN(c) */ t.statdate dt,
c.cname name,
t.cookieid cid
FROM tmpdb.city c
JOIN ecdata.ext_trackflow t ON t.area1 =c.cname
WHERE t.statdate>='20140818'
AND t.statdate<='20140824'
AND platform='pc'
UNION ALL SELECT /*+ MAPJOIN(c) */ t.statdate dt,
c.cname name,
t.cookieid cid
FROM tmpdb.city c
JOIN ecdata.ext_trackflow t ON t.area2 =c.cname
WHERE t.statdate>='20140818'
AND t.statdate<='20140824'
AND platform='pc'
UNION ALL SELECT /*+ MAPJOIN(c) */ t.statdate dt,
c.cname name,
t.cookieid cid
FROM tmpdb.city c
JOIN ecdata.ext_trackflow t ON t.area3 =c.cname
WHERE t.statdate>='20140818'
AND t.statdate<='20140824'
AND platform='pc') tmp_trackflow
GROUP BY dt,
name;
6、優化無極限:開啟 parallel 和 控制 rece 個數
上述語句執行時,你可以看到執行計劃和狀態信息,以及結合你的 union all 語句可知,三個 union 語句之間沒有依賴關系,其實是可以並行執行的:
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
explain SQL...
...
STAGE DEPENDENCIES:
Stage-11 is a root stage
Stage-1 depends on stages: Stage-11
Stage-2 depends on stages: Stage-1
Stage-3 depends on stages: Stage-2, Stage-6, Stage-9
Stage-12 is a root stage
Stage-5 depends on stages: Stage-12
Stage-6 depends on stages: Stage-5
Stage-13 is a root stage
Stage-8 depends on stages: Stage-13
Stage-9 depends on stages: Stage-8
Stage-0 is a root stage
...
我們在 SQL 前加上如下環境變數選項:
?
1
2
set mapred.rece.tasks=60;
set hive.exec.parallel=true;
讓執行計劃中的 Stage-11、Stage-12、Stage-13 並行執行,並控制好 rece task 個數。
完整的語句如下:
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
hive -e "
SET mapred.rece.tasks=60;
SET hive.exec.parallel=TRUE;
SELECT dt,
name,
count(cid)
FROM
(SELECT /*+ MAPJOIN(c) */ t.statdate dt,
c.cname name,
t.cookieid cid
FROM tmpdb.city c
JOIN ecdata.ext_trackflow t ON t.area1 =c.cname
WHERE t.statdate>='20140818'
AND t.statdate<='20140824'
AND platform='pc'
UNION ALL SELECT /*+ MAPJOIN(c) */ t.statdate dt,
c.cname name,
t.cookieid cid
FROM tmpdb.city c
JOIN ecdata.ext_trackflow t ON t.area2 =c.cname
WHERE t.statdate>='20140818'
AND t.statdate<='20140824'
AND platform='pc'
UNION ALL SELECT /*+ MAPJOIN(c) */ t.statdate dt,
c.cname name,
t.cookieid cid
FROM tmpdb.city c
JOIN ecdata.ext_trackflow t ON t.area3 =c.cname
WHERE t.statdate>='20140818'
AND t.statdate<='20140824'
AND platform='pc') tmp_trackflow
GROUP BY dt,
name;
" > a1.txt
最後的優化效果是:2 中的語句三個小時沒出結果。。。5 比 4 快 8 倍左右,6 比 5 快 2 倍左右,最終 10min 出結果。
⑺ hiveContext讀取hive非分區表不報錯,讀取hive分區表報null指針異常
首先要從你的分區表結構開始查起,看是否符合你要分區的條件,這個錯誤報的很泛,並不能完全定位出問題,只能根據有可能會影響的地方出手一點點排除,查找問題了。可以在stickoverflow,或者官方論壇中去找找線索了
⑻ 如何每日增量載入數據到Hive分區表
載入數據
數據載入到Hive分區表(兩個分區,日期(20160316)和小時(10))中
每日載入前一天的日誌文件數據到表db_track.track_log
1. 數據存儲
數據日誌文件,放入某個目錄下,每天日誌文件放入同一個目錄
eg: 20160316 - 目錄名稱
日誌文件數據,每個小時生成一個文件,一天總共有二十四個文件
eg: 2016031820
2. shell腳本編寫
負責調度的shell腳本load_tracklogs.sh
註:這里涉及到了兩個點:1)for循環 2) linux下字元串的截取${line:0:4} 3) 傳遞參數到hive的sql腳本
#!/bin/sh
## 環境變數生效
. /etc/profile
## HIVE HOME
HIVE_HOME=/opt/cdh5.3.6/hive-0.13.1-cdh5.3.6
## 日誌目錄
LOG_DIR=/data/tracklogs
## 目錄名稱, 依據日期date獲取
yesterday=`date -d -1days '+%Y%m%d'`
###
for line in `ls $LOG_DIR/${yesterday}`
do
echo "loading $line .............."
#從文件名稱中解析出日期和小時
daily=${line:0:4}${line:4:2}${line:6:2}
hour=${line:8:2}
LOAD_FILE=${LOG_DIR}/${yesterday}/${line}
### echo $daily + $hour
### ${HIVE_HOME}/bin/hive -e "LOAD DATA LOCAL INPATH '${LOAD_FILE}' OVERWRITE INTO TABLE db_track.track_log PARTITION(date = '${daily}', hour = '${hour}') ;"
${HIVE_HOME}/bin/hive --hiveconf LOAD_FILE_PARAM=${LOAD_FILE} --hiveconf daily_param=${daily} --hiveconf hour_param=${hour} -f /home/hadoop/load_data.sql
done
負責載入數據的sql腳本
注: 怎麼在HQL腳本中獲取外接傳遞的參數
LOAD DATA LOCAL INPATH '${hiveconf:LOAD_FILE_PARAM}' OVERWRITE INTO TABLE db_track.track_log PARTITION(date = '${hiveconf:daily_param}', hour = '${hiveconf:hour_param}') ;
制定每天定時執行
可以在當前用戶下直接創建:crontab -e
註:crontab中的五個 *號分別代表分,時,日,月,周,下面的例子就是每天晚上1點30運行任務的例子,注意sh命令前一般需要加上絕對路徑
# LODAD DATA INTO TRACK_LOG
30 1 * * * /bin/sh /home/hadoop/load_tracklogs.sh