hivesqlin查詢
❶ sql中的in、exists和join的查詢原理
Hive中的謂詞下推是一種優化技術，其目標是在查詢過程中盡可能地將過濾條件下推到數據源中執行，以減少數據的掃描和處理。然而，這一優化技術有時會導致查詢結果的不一致性。
讓我們通過四個典型的SQL查詢來探討這一問題及其原因。在這四個查詢中，結果的數值有所不同，雖然它們的基本邏輯一致，但條件的放置位置有所不同，從而導致了結果的不一致。
SQL1和SQL3的結果為：20672和9721，這表明它們在處理數據時採用了不同的謂詞下推策略。SQL2和SQL4的結果為：9721和184125，這進一步說明了條件放置位置對查詢結果的影響。
Hive謂詞下推的核心概念是將過濾條件從SQL語句中下推到數據源執行，從而減少了不必要的數據處理。謂詞下推在Join操作中的應用主要遵循以下規則：
1. 對於Inner Join和Full outer Join，條件應寫在ON語句後或WHERE語句後，謂詞下推的性能影響不大。然而，Join操作的性能主要取決於謂詞下推的效率。
2. 對於Left outer Join，當左側表的條件寫在WHERE語句中，而右側表的條件寫在ON語句中時，謂詞下推可以提高性能。
3. 對於Right outer Join，情況相反，左側表條件寫在ON語句中，而右側表條件寫在WHERE語句中時，謂詞下推同樣可以提升性能。
這一策略的影響在於控制了數據處理的順序，從而影響了最終的查詢結果。在使用謂詞下推時，需要根據查詢的預期結果和數據的結構來靈活調整條件的位置，以確保優化的同時不影響查詢的正確性。
總結而言，謂詞下推是一種強大的查詢優化工具，但其應用需要結合具體的數據結構和查詢需求來調整。理解謂詞下推的規則和限制，有助於在查詢優化過程中做出更明智的選擇，從而實現更高效、准確的數據查詢。
❷ 大數據的核心技術有哪些
大數據技術的體系龐大且復雜，基礎的技術包含數據的採集、數據預處理、分布式存儲、資料庫、數據倉庫、機器學習、並行計算、可視化等。
1、數據採集與預處理：FlumeNG實時日誌收集系統，支持在日誌系統中定製各類數據發送方，用於收集數據；Zookeeper是一個分布式的，開放源碼的分布式應用程序協調服務，提供數據同步服務。
2、數據存儲：Hadoop作為一個開源的框架，專為離線和大規模數據分析而設計，HDFS作為其核心的存儲引擎，已被廣泛用於數據存儲。HBase，是一個分布式的、面向列的開源資料庫，可以認為是hdfs的封裝，本質是數據存儲、NoSQL資料庫。
3、數據清洗：MapRece作為Hadoop的查詢引擎，用於大規模數據集的並行計算。
4、數據查詢分析：Hive的核心工作就是把SQL語句翻譯成MR程序，可以將結構化的數據映射為一張資料庫表，並提供HQL(HiveSQL)查詢功能。Spark啟用了內存分布數據集，除了能夠提供互動式查詢外，它還可以優化迭代工作負載。
5、數據可視化：對接一些BI平台，將分析得到的數據進行可視化，用於指導決策服務。
❸ 資料庫有哪些
目前比較常見的資料庫：
SQL是用於訪問和處理資料庫的標準的計算機語言。
MySQL是小型的開源的關系型資料庫管理系統。
SQL Server 是 Microsoft 開發的關系資料庫管理系統。
Oracle資料庫系統是目前世界上流行的關系資料庫管理系統。
DB2是關系型資料庫平台，其採用多進程多線索的結構，支持多用戶或應用程序在同一條SQL 語句中查詢不同資料庫和數據。
PostgreSQL 是一個對象-關系資料庫伺服器，號稱 "世界上最先進的開源關系型資料庫"。
Hadoop是個很流行的分布式計算解決方案，Hive是基於hadoop的數據倉庫工具，hive 構建在基於靜態批處理的Hadoop 之上。
GreenPlum採用了MPP(大規模並行處理)，是一個由多個獨立的資料庫服務組合成關系型資料庫集群。
ECharts 是一個使用 JavaScript 實現的開源可視化庫，涵蓋各行業圖表。
R是一種集統計分析與圖形顯示為一體的統計分析軟體，具有很強的互動性。
目前，這些資料庫都在樹懶學堂有相關教程，可以跟著一步一步學習
❹ 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 出結果。
❺ HIVE：查詢語句：有異常拋出，執行結束。 求大神解決
你的SQL不規范，寫有的問題，缺少GROUP關鍵字。
SQL結尾需要加上 group by user_id