hive編譯器將查詢字元串

⑴ 關於在Hive中將特定字元分隔的字元串拆分成多行的應用

Subject ：關於在 Hive 中將特定字元分隔的字元串拆分成多行的應用

Keys ： lateral view 、 split 、 explode 、 HQL 、 Hive 、數據拆分

1、案例描述

 假設：

有問卷p1,p2,p3,每個問卷含有問題q1,q2,q3...,每個問題對應答案a11,a21,a31，問題與答案之間,用':'分隔,每個問題之間以','分隔.例如:q1:a11,q2:a21,q3:a31:a32

問題：

將問題與答案拆分成獨立的列，如：

P1 q1 a11

P1 q2 a21

…

解決方案：

使用lateral View結合Explode實現數據拆分。

2、小知識：

lateral view用於和split、explode等UDTF一起使用的，能將一行數據拆分成多行數據，在此基礎上可以對拆分的數據進行聚合，lateral view首先為原始表的每行調用UDTF，UDTF會把一行拆分成一行或者多行，lateral view在把結果組合，產生一個支持別名表的虛擬表。

1）. Lateral View語法

lateral View: LATERAL VIEW udtf(expression) tableAlias AS columnAlias (',' columnAlias)*

from Clause: FROM baseTable(lateralView)* 

2). Lateral View用於UDTF(user-defined table generating functions)中銀斗將行轉成列，例如explode().

3). 目前Lateral View不支持有上而下的優化。如果使用Where子句，查詢可能將不被編譯。解決方法見：此時，在查詢之前執行et hive.optimize.ppd=false;

3、實現步驟:

2.1 創建測試數據

drop table temp_bigdata.test_p1;

create table temp_bigdata.test_p1 as

select 'p1' as p,'q1:a11,q2:a21,q3:a31:a32' as qa from default.al union all

select 'p2' as p,'q1:a11,q2:a21:a22,q3:a31:a32'塌碰 as qa from default.al union all

select 'p3' as p,'q1:a11,q2:a21,q3:' as qa from default.al;

2.2 查看數據內容

select * from temp_bigdata.test_p1;

 

  p   qa

  p3   q1:a11,q2:a21,q3

  p2   q1:a11,q2:a21:a22,q3:a31:a32

  p1   q1:a11,q2:a21,q3:a31:a32

2.3 測試explode函數

select explode(split(qa,',')) as qa1 from temp_bigdata.test_p1;

  q1:a11

  q2:a21

  q3:

  q1:a11

  q2:a21:a22

  q3:a31:a32

  q1:a11

  q2:a21

  q3:a31:a32

2.4 開始處理,先將問題拆分成獨立行

drop table temp_bigdata.test_p1_adid;

create table temp_bigdata.test_p1_adid as

select row_number() over(order by p,adid) rid,p, adid

  from temp_bigdata.test_p1 LATERAL VIEW explode(split(qa,',')) adtable AS adid;

select * from temp_bigdata.test_p1_adid;

rid    p     adid

1    p1    q1:a11

2    p1 鋒衫磨   q2:a21

3    p1    q3:a31:a32

4    p2    q1:a11

5    p2    q2:a21:a22

6    p2    q3:a31:a32

7    p3    q1:a11

8    p3    q2:a21

9    p3    q3:

2.5 再將每個問題中的問題及答案拆分成多行

create table temp_bigdata.test_p1_adid2 as

select rid,

       p,

       adid, --拆分後的問題和答案

       split(adid,':')[0] as q,  --取出問題

       adid2 --拆分答案為行

  from temp_bigdata.test_p1_adid

  LATERAL VIEW explode(split(adid,':')) adttable2 as adid2;

select * from temp_bigdata.test_p1_adid2;

rid     p    adid    q     adid2

1    p1    q1:a11    q1    q1

1    p1    q1:a11    q1    a11

2    p1    q2:a21    q2    q2

2    p1    q2:a21    q2    a21

3    p1    q3:a31:a32    q3    q3

3    p1    q3:a31:a32    q3    a31

3    p1    q3:a31:a32    q3    a32

4    p2    q1:a11    q1    q1

4    p2    q1:a11    q1    a11

5    p2    q2:a21:a22    q2    q2

5    p2    q2:a21:a22    q2    a21

5    p2    q2:a21:a22    q2    a22

6    p2    q3:a31:a32    q3    q3

6    p2    q3:a31:a32    q3    a31

6    p2    q3:a31:a32    q3    a32

7    p3    q1:a11    q1    q1

7    p3    q1:a11    q1    a11

8    p3    q2:a21    q2    q2

8    p3    q2:a21    q2    a21

9    p3    q3:    q3    q3

9    p3    q3:    q3

2.6 取出結果,將多餘行過濾,及問題列=拆分後的答案列

select * from temp_bigdata.test_p1_adid2 where q<>adid2 order by rid,adid;

  rid     p    adid    q     adid2

1     p1     q1:a11     q1     a11

2     p1     q2:a21     q2     a21

3     p1     q3:a31:a32     q3     a32

3     p1     q3:a31:a32     q3     a31

4     p2     q1:a11     q1     a11

5     p2     q2:a21:a22     q2     a22

5     p2     q2:a21:a22     q2     a21

6     p2     q3:a31:a32     q3     a32

6     p2     q3:a31:a32     q3     a31

7     p3     q1:a11     q1     a11

8     p3     q2:a21     q2     a21

9     p3     q3:     q3     Null

OK ，得到了想要的結果，說明成功了，可以看到最後一條記錄，也能正確的被處理。

4、總結:

1、寫法有點怪，一定不要寫錯；

2、不要忘記起別名，要不select沒列可寫。

⑵ hive中的字元串提取

  在進行數據分析時，尤其要對網頁進行分析時，我們往往要對其中部分的數據進行抽取，這個就需要靠hive的函數來完成了。

  首先要講的是split函數，這個函數的作用是對字元竄進行分割，基本用法為：split(string str, string pat) ，返回值為一個數組array，因此要取值得話需要用到切片，即[數字]，其中第一個str是要切分的字元串基逗棗，第二個pat是以什麼字元進行切割。來看案例吧。

  有的時候切割不是一下就能完成，那麼就多去嵌套幾次split就好了。

  返回字元串從某個位置開始固定長度的子串，和substring功能相同，基本用法為substr(string A, int start, int len )，還是來看例子。值得注意的是搏拆，substr(str,0,2)和substr(str,1,2)的功能都是一樣的，都是從第一個位置開始。

  這個函數是個神器，可以解析url結構，返回我們想要的東西。基本用法為parse_url(string urlString, string partToExtract [, string keyToExtract])，其中partToExtract的有效值包括HOST，PATH， QUERY， REF， PROTOCOL， AUTHORITY，FILE和USERINFO等，具體我就不一一解釋了，大家使用時可以自行網路。重點說一下，當第二個參數是QUERY時，第三個參數就可以使用了，這個是參數提取最有用的方法了，還是以指攔案例來說明。

  這個函數是最終的大殺器了，以上都解決不了你的問題的時候，有了這個一切都可以解決，使用這個函數的基礎是正則表達式基礎要會一些。這個函數的基本用法是regexp_extract(string subject, string pattern, int index)，第一個參數是待處理的字元串，第二個參數是寫好的正則，第三個表達式一般用不上可以忽略掉。來看例子：

  有了以上函數，相信應該能滿足大家對於hive進行字元串提取的一切要求了。

⑶ Hive中常用的字元串操作

創建虛表：

語法: length(string A)

返回值: int

說明：返回字元串A的長度

語法: reverse(string A)

返回值: string

說明：返回字元串A的反轉結果

語法: concat(string A, string B…)

返回值: string

說明：返回輸入字元串連接後的結果，支持任意個輸入字元串

語法: concat_ws(string SEP, string A, string B…)

返回值: string

說明：返回輸入字元串連接後的結果，SEP表示各個字元串間的分隔符

語法: substr(string A, int start),substring(string A, int start)

返回值: string

說明：返回字元串A從start位置到結尾的字元串租談

語法: substr(string A, int start, int len),substring(string A, int start, int len)

返回值: string

說明：返回字元串A從start位置開始，長度為len的字元串

語法: upper(string A) ucase(string A)

返回值: string

說明：返回字元串A的大寫格式

語法: lower(string A) lcase(string A)

返回值: string

說明：返回字元串A的小寫格式

語法: trim(string A)

返回值: string

說明：去除字元串兩邊的空格

語法: ltrim(string A)

返回值: string

說明：去除字元串左邊的空格

語法: rtrim(string A)

返回值: string

說明：去除字元串右邊的空格

語法: regexp_replace(string A, string B, string C)

返回值: string

說明：將字元串A中的符合java正則表達式B的部分替換為C。注意，在有些梁型蘆情況下要使用轉義字元,類似oracle中的regexp_replace函數。

語法: regexp_extract(string subject, string pattern, int index)

返回值: string

說明：將字元串subject按照pattern正則表達式的橡帶規則拆分，返回index指定的字元。

第三個參數:

0 是顯示與之匹配的整個字元串

1 是顯示第一個括弧裡面的

2 是顯示第二個括弧裡面的欄位

語法: parse_url(string urlString, string partToExtract [, string keyToExtract])

返回值: string

說明：返回URL中指定的部分。partToExtract的有效值為：HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO.

語法: get_json_object(string json_string, string path)

返回值: string

說明：解析json的字元串json_string,返回path指定的內容。如果輸入的json字元串無效，那麼返回NULL。

語法: space(int n)

返回值: string

說明：返回長度為n的空字元串

語法: repeat(string str, int n)

返回值: string

說明：返回重復n次後的str字元串

語法: ascii(string str)

返回值: int

說明：返回字元串str第一個字元的ascii碼

語法: lpad(string str, int len, string pad)

返回值: string

說明：將str進行用pad進行左補足到len位

語法: rpad(string str, int len, string pad)

返回值: string

說明：將str進行用pad進行右補足到len位

語法: split(string str, string pat)

返回值: array

說明: 按照pat字元串分割str，會返回分割後的字元串數組

語法: find_in_set(string str, string strList)

返回值: int

說明: 返回str在strlist第一次出現的位置，strlist是用逗號分割的字元串。如果沒有找該str字元，則返回0

返回:int。substr在str中第一次出現的位置，若任何參數為null返回null，若substr不在str中返回0，Str中第一個字元的位置為1

說明：C1 被搜索的字元串

C2 希望搜索的字元串

I 搜索的開始位置,默認為1

J 出現的位置,默認為1

24、使用兩個分隔符將文本拆分為鍵值對：str_to_map(text[, delimiter1, delimiter2])

返回：map

Delimiter1將文本分成K-V對，Delimiter2分割每個K-V對。對於delimiter1默認分隔符是','，對於delimiter2默認分隔符是'='

25、unix_timestamp() 返回當前時間戳。另外，current_timestamp() 也有同樣作用。

unix_timestamp(string date) 返回 date 對應的時間戳，date 格式必須為 yyyy-MM-dd HH:mm:ss。

unix_timestamp(string date, string format) 返回 date 對應的時間戳，date 格式由 format 指定。

26、from_unixtime(int/bigint timestamp) 返回 timestamp 時間戳對應的日期，格式為 yyyy-MM-dd HH:mm:ss。

from_unixtime(int/bigint timestamp, string format) 返回 timestamp 時間戳對應的日期，格式由 format 指定。