Hive自定義函數(shù)與transform的使用

hive是給了我們很多內(nèi)置函數(shù)的，比如轉(zhuǎn)大小寫毛俏，截取字符串等，具體的都在官方文檔里面饲窿。但是并不是所有的函數(shù)都能滿足我們的需求煌寇，所以hive提供了給我們自定義函數(shù)的功能。
1逾雄、至于怎么測試hive為我們提供的函數(shù)
因為mysql或者oracle中都可以使用偽表阀溶，但是hive不行，所以可以使用以下方法
1）鸦泳、創(chuàng)建表dual,create table dual(id string)
2）银锻、在本地創(chuàng)建文件dual.data,內(nèi)容為空格或者空一行
3）、將dual.data文件load到表dual
進行測試做鹰，比如：字符串截取

0: jdbc:hive2://localhost:10000> select substr('sichuan',1,3) from dual;
+------+--+
| _c0  |
+------+--+
| sic  |
+------+--+

當然也可以直接使用 select substr(‘sichuan’,1,3)击纬，但是還是習(xí)慣用from dual;

2、自定義內(nèi)置函數(shù)
添加maven依賴

  <dependency>
      <groupId>org.apache.hive</groupId>
      <artifactId>hive-exec</artifactId>
      <version>1.2.1</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.apache.hive/hive-metastore -->
    <dependency>
      <groupId>org.apache.hive</groupId>
      <artifactId>hive-metastore</artifactId>
      <version>1.2.1</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.apache.hive/hive-common -->
    <dependency>
      <groupId>org.apache.hive</groupId>
      <artifactId>hive-common</artifactId>
      <version>1.2.1</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.apache.hive/hive-service -->
    <dependency>
      <groupId>org.apache.hive</groupId>
      <artifactId>hive-service</artifactId>
      <version>1.2.1</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.apache.hive/hive-jdbc -->
    <dependency>
      <groupId>org.apache.hive</groupId>
      <artifactId>hive-jdbc</artifactId>
      <version>1.2.1</version>
    </dependency>

1）钾麸、大寫轉(zhuǎn)小寫
可以先創(chuàng)建java類繼承UDF,重載evaluate方法更振。

/**
 * 大寫轉(zhuǎn)小寫
 * @author 12706
 */
public class UpperToLowerCase extends UDF {
    /*
     * 重載evaluate
     * 訪問限制必須是public
     */
    public String evaluate(String word) {
        String lowerWord = word.toLowerCase();
        return lowerWord;
    }
}

打包上傳到hadoop集群（打的jar包名字為hive.jar）。

0: jdbc:hive2://localhost:10000> select * from t5;
+--------+-----------+--+
| t5.id  |  t5.name  |
+--------+-----------+--+
| 13     | BABY      |
| 1      | zhangsan  |
| 2      | lisi      |
| 3      | wangwu    |
| 4      | furong    |
| 5      | fengjie   |
| 6      | aaa       |
| 7      | bbb       |
| 8      | ccc       |
| 9      | ddd       |
| 10     | eee       |
| 11     | fff       |
| 12     | ggg       |
+--------+-----------+--+
13 rows selected (0.221 seconds)

將jar包放到hive的classpath下

0: jdbc:hive2://localhost:10000> add jar /root/hive.jar;

創(chuàng)建臨時函數(shù)喂走，指定完整類名

0: jdbc:hive2://localhost:10000> create temporary function tolower as 'com.scu.hive.UpperToLowerCase';

到這就可以使用自定義臨時函數(shù)tolower()了，測試t5表中的name輸出小寫

0: jdbc:hive2://localhost:10000> select id,tolower(name) from t5;
+-----+-----------+--+
| id  |    _c1    |
+-----+-----------+--+
| 13  | baby      |
| 1   | zhangsan  |
| 2   | lisi      |
| 3   | wangwu    |
| 4   | furong    |
| 5   | fengjie   |
| 6   | aaa       |
| 7   | bbb       |
| 8   | ccc       |
| 9   | ddd       |
| 10  | eee       |
| 11  | fff       |
| 12  | ggg       |
+-----+-----------+--+

根據(jù)電話號碼顯示歸屬地信息
jave類

/**
 * 根據(jù)電話號碼前三位獲取歸屬地
 * @author 12706
 *
 */
public class PhoneNumParse extends UDF{

    static HashMap<String, String> phoneMap = new HashMap<String, String>();

    static{
        phoneMap.put("136", "beijing");
        phoneMap.put("137", "shanghai");
        phoneMap.put("138", "shenzhen");
    }

    public static String evaluate(int phoneNum) {

        String num = String.valueOf(phoneNum);
        String province = phoneMap.get(num.substring(0, 3));
        return province==null?"foreign":province;
    }
    //測試
    public static void main(String[] args) {
        String string = evaluate(136666);
        System.out.println(string);
    }
}

將工程打包上傳到linux谋作，注意：如果名字還是跟上面一樣芋肠，那么需要重新連接hive服務(wù)端了，否則jar包是不會覆蓋的遵蚜，建議打的jar包名字別一樣

編輯文件vi prov.data
創(chuàng)建表flow(phonenum int,flow int)
將文件load到flow表

[root@mini1 ~]# vi prov.data;
1367788,1
1367788,10
1377788,80
1377788,97
1387788,98
1387788,99
1387788,100
1555118,99

0: jdbc:hive2://localhost:10000> create table flow(phonenum int,flow int)
0: jdbc:hive2://localhost:10000> row format delimited fields terminated by ',';
No rows affected (0.143 seconds)
0: jdbc:hive2://localhost:10000> load data local inpath '/root/prov.data' into table flow;
INFO  : Loading data to table myhive3.flow from file:/root/prov.data
INFO  : Table myhive3.flow stats: [numFiles=1, totalSize=88]
No rows affected (0.316 seconds)
0: jdbc:hive2://localhost:10000> select * from flow;
+----------------+------------+--+
| flow.phonenum  | flow.flow  |
+----------------+------------+--+
| 1367788        | 1          |
| 1367788        | 10         |
| 1377788        | 80         |
| 1377788        | 97         |
| 1387788        | 98         |
| 1387788        | 99         |
| 1387788        | 100        |
| 1555118        | 99         |
+----------------+------------+--+

classpath下加入jar包帖池，創(chuàng)建臨時函數(shù)奈惑，測試

0: jdbc:hive2://localhost:10000> add jar /root/hive.jar;
INFO  : Added [/root/hive.jar] to class path
INFO  : Added resources: [/root/hive.jar]
No rows affected (0.236 seconds)
0: jdbc:hive2://localhost:10000> create temporary function getprovince as 'com.scu.hive.PhoneNumParse';
No rows affected (0.038 seconds)
0: jdbc:hive2://localhost:10000> select phonenum,getprovince(phonenum),flow from flow;
+-----------+-----------+-------+--+
| phonenum  |    _c1    | flow  |
+-----------+-----------+-------+--+
| 1367788   | beijing   | 1     |
| 1367788   | beijing   | 10    |
| 1377788   | shanghai  | 80    |
| 1377788   | shanghai  | 97    |
| 1387788   | shenzhen  | 98    |
| 1387788   | shenzhen  | 99    |
| 1387788   | shenzhen  | 100   |
| 1555118   | foreign   | 99    |
+-----------+-----------+-------+--+

Json數(shù)據(jù)解析UDF開發(fā)
有文件，內(nèi)容一部分如下睡汹，里面都是json串肴甸，現(xiàn)在需要將它展示輸出到表中，并解析對應(yīng)為4個字段囚巴。

{"movie":"1193","rate":"5","timeStamp":"978300760","uid":"1"}
{"movie":"661","rate":"3","timeStamp":"978302109","uid":"1"}
{"movie":"914","rate":"3","timeStamp":"978301968","uid":"1"}
{"movie":"3408","rate":"4","timeStamp":"978300275","uid":"1"}
{"movie":"2355","rate":"5","timeStamp":"978824291","uid":"1"}
{"movie":"1197","rate":"3","timeStamp":"978302268","uid":"1"}

java類

public class JsonParse extends UDF{
    //{"movie":"1193","rate":"5","timeStamp":"978300760","uid":"1"}
    //輸出字符串 1193 5 978300760 1
    public static String evaluate(String line){
        ObjectMapper objectMapper = new ObjectMapper();
        //json串轉(zhuǎn)java對象
        String json = "";
        try {
            MovieRateBean bean = objectMapper.readValue(line,MovieRateBean.class);
            json = bean.toString();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return json;
    }

}

public class MovieRateBean {
    private String movie;
    private String rate;//評分
    private String timeStamp;
    private String uid;
    @Override
    public String toString() {
        return  this.movie+"\t"+this.rate+"\t"+this.timeStamp+"\t"+this.uid;
    }
    get原在、set方法
}

工程打包上傳到linux下。
創(chuàng)建表json

create table json(line string);

將文件導(dǎo)入到j(luò)son表

load data local inpath ‘/root/json.data’ into table json;

0: jdbc:hive2://localhost:10000> select * from json limit 10;
+----------------------------------------------------------------+--+
|                           json.line                            |
+----------------------------------------------------------------+--+
| {"movie":"1193","rate":"5","timeStamp":"978300760","uid":"1"}  |
| {"movie":"661","rate":"3","timeStamp":"978302109","uid":"1"}   |
| {"movie":"914","rate":"3","timeStamp":"978301968","uid":"1"}   |
| {"movie":"3408","rate":"4","timeStamp":"978300275","uid":"1"}  |
| {"movie":"2355","rate":"5","timeStamp":"978824291","uid":"1"}  |
| {"movie":"1197","rate":"3","timeStamp":"978302268","uid":"1"}  |
| {"movie":"1287","rate":"5","timeStamp":"978302039","uid":"1"}  |
| {"movie":"2804","rate":"5","timeStamp":"978300719","uid":"1"}  |
| {"movie":"594","rate":"4","timeStamp":"978302268","uid":"1"}   |
| {"movie":"919","rate":"4","timeStamp":"978301368","uid":"1"}   |
+----------------------------------------------------------------+--+

0: jdbc:hive2://localhost:10000> add jar /root/hive3.jar;
INFO  : Added [/root/hive3.jar] to class path
INFO  : Added resources: [/root/hive3.jar]
No rows affected (0.023 seconds)
0: jdbc:hive2://localhost:10000> create temporary function parsejson as 'com.scu.hive.JsonParse';
No rows affected (0.07 seconds)
0: jdbc:hive2://localhost:10000> select parsejson(line) from json limit 10;
+---------------------+--+
|         _c0         |
+---------------------+--+
| 1193  5       978300760       1  |
| 661   3       978302109       1   |
| 914   3       978301968       1   |
| 3408  4       978300275       1  |
| 2355  5       978824291       1  |
| 1197  3       978302268       1  |
| 1287  5       978302039       1  |
| 2804  5       978300719       1  |
| 594   4       978302268       1   |
| 919   4       978301368       1   |
+---------------------+--+

到這里發(fā)現(xiàn)還有不足的地方彤叉，就是沒顯示字段庶柿。可以使用函數(shù)來實現(xiàn)重寫建表來命名秽浇。

0: jdbc:hive2://localhost:10000> create table t_rating as
0: jdbc:hive2://localhost:10000> select split(parsejson(line),'\t')[0]as movieid,
0: jdbc:hive2://localhost:10000> split(parsejson(line),'\t')[1] as rate,
0: jdbc:hive2://localhost:10000> split(parsejson(line),'\t')[2] as timestring,
0: jdbc:hive2://localhost:10000> split(parsejson(line),'\t')[3] as uid 
0: jdbc:hive2://localhost:10000> from json limit 10;

0: jdbc:hive2://localhost:10000> select * from t_rating;
+-------------------+----------------+----------------------+---------------+--+
| t_rating.movieid  | t_rating.rate  | t_rating.timestring  | t_rating.uid  |
+-------------------+----------------+----------------------+---------------+--+
| 919               | 4              | 978301368            | 1             |
| 594               | 4              | 978302268            | 1             |
| 2804              | 5              | 978300719            | 1             |
| 1287              | 5              | 978302039            | 1             |
| 1197              | 3              | 978302268            | 1             |
| 2355              | 5              | 978824291            | 1             |
| 3408              | 4              | 978300275            | 1             |
| 914               | 3              | 978301968            | 1             |
| 661               | 3              | 978302109            | 1             |
| 1193              | 5              | 978300760            | 1             |
+-------------------+----------------+----------------------+---------------+--+

transform關(guān)鍵字使用
需求浮庐，創(chuàng)建新表，內(nèi)容與t_rating表一致柬焕，但是第三個字段時間戳要改為輸出周幾审残。
Hive的 TRANSFORM 關(guān)鍵字提供了在SQL中調(diào)用自寫腳本的功能
適合實現(xiàn)Hive中沒有的功能又不想寫UDF的情況。

1斑举、編寫python腳本(先看看機器有沒有python)搅轿，用來將表時間戳轉(zhuǎn)為周幾
2、加入編寫的py文件
3懂昂、創(chuàng)建新表介时，字段值為t_rating表傳入py函數(shù)后輸出的字段值

[root@mini1 ~]# python
Python 2.6.6 (r266:84292, Feb 21 2013, 23:54:59) 
[GCC 4.4.7 20120313 (Red Hat 4.4.7-3)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>>  print 'hello';
hello
>>> quit()
[root@mini1 ~]# vi weekday_mapper.py;
#import sys
import datetime

for line in sys.stdin:
  line = line.strip()
  movieid, rating, unixtime,userid = line.split('\t')
  weekday = datetime.datetime.fromtimestamp(float(unixtime)).isoweekday()
  print '\t'.join([movieid, rating, str(weekday),userid])

切換到hive客戶端

0: jdbc:hive2://localhost:10000> add FILE /root/weekday_mapper.py;
1
0: jdbc:hive2://localhost:10000> create TABLE u_data_new as
0: jdbc:hive2://localhost:10000> SELECT
0: jdbc:hive2://localhost:10000>   TRANSFORM (movieid, rate, timestring,uid)
0: jdbc:hive2://localhost:10000>   USING 'python weekday_mapper.py'
0: jdbc:hive2://localhost:10000>   AS (movieid, rate, weekday,uid)
0: jdbc:hive2://localhost:10000> FROM t_rating;
...
0: jdbc:hive2://localhost:10000> select * from u_data_new;
+---------------------+------------------+---------------------+-----------------+--+
| u_data_new.movieid  | u_data_new.rate  | u_data_new.weekday  | u_data_new.uid  |
+---------------------+------------------+---------------------+-----------------+--+
| 919                 | 4                | 1                   | 1               |
| 594                 | 4                | 1                   | 1               |
| 2804                | 5                | 1                   | 1               |
| 1287                | 5                | 1                   | 1               |
| 1197                | 3                | 1                   | 1               |
| 2355                | 5                | 7                   | 1               |
| 3408                | 4                | 1                   | 1               |
| 914                 | 3                | 1                   | 1               |
| 661                 | 3                | 1                   | 1               |
| 1193                | 5                | 1                   | 1               |
+---------------------+------------------+---------------------+-----------------+--+

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者

人面猴
序言：七十年代末，一起剝皮案震驚了整個濱河市凌彬，隨后出現(xiàn)的幾起案子沸柔，更是在濱河造成了極大的恐慌，老刑警劉巖铲敛，帶你破解...
沈念sama閱讀 222,946評論 6贊 518
死咒
序言：濱河連續(xù)發(fā)生了三起死亡事件褐澎，死亡現(xiàn)場離奇詭異，居然都是意外死亡伐蒋，警方通過查閱死者的電腦和手機工三，發(fā)現(xiàn)死者居然都...
沈念sama閱讀 95,336評論 3贊 399
救了他兩次的神仙讓他今天三更去死
文/潘曉璐我一進店門，熙熙樓的掌柜王于貴愁眉苦臉地迎上來先鱼，“玉大人俭正，你說我怎么就攤上這事”号希” “怎么了掸读？”我有些...
開封第一講書人閱讀 169,716評論 0贊 364
道士緝兇錄：失蹤的賣姜人
文/不壞的土叔我叫張陵，是天一觀的道長。經(jīng)常有香客問我儿惫，道長澡罚，這世上最難降的妖魔是什么？我笑而不...
開封第一講書人閱讀 60,222評論 1贊 300
?港島之戀（遺憾婚禮）
正文為了忘掉前任肾请，我火速辦了婚禮留搔，結(jié)果婚禮上，老公的妹妹穿的比我還像新娘铛铁。我一直安慰自己隔显，他們只是感情好，可當我...
茶點故事閱讀 69,223評論 6贊 398
惡毒庶女頂嫁案：這布局不是一般人想出來的
文/花漫我一把揭開白布避归。她就那樣靜靜地躺著荣月，像睡著了一般。火紅的嫁衣襯著肌膚如雪梳毙。梳的紋絲不亂的頭發(fā)上哺窄，一...
開封第一講書人閱讀 52,807評論 1贊 314
城市分裂傳說
那天，我揣著相機與錄音账锹，去河邊找鬼萌业。笑死，一個胖子當著我的面吹牛奸柬，可吹牛的內(nèi)容都是我干的生年。我是一名探鬼主播，決...
沈念sama閱讀 41,235評論 3贊 424
雙鴛鴦連環(huán)套：你想象不到人心有多黑
文/蒼蘭香墨我猛地睜開眼廓奕，長吁一口氣：“原來是場噩夢啊……” “哼抱婉！你這毒婦竟也來了？” 一聲冷哼從身側(cè)響起桌粉，我...
開封第一講書人閱讀 40,189評論 0贊 277
萬榮殺人案實錄
序言：老撾萬榮一對情侶失蹤蒸绩，失蹤者是張志新（化名）和其女友劉穎，沒想到半個月后铃肯，有當?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體患亿，經(jīng)...
沈念sama閱讀 46,712評論 1贊 320
?護林員之死
正文獨居荒郊野嶺守林人離奇死亡，尸身上長有42處帶血的膿包…… 初始之章·張勛以下內(nèi)容為張勛視角年9月15日...
茶點故事閱讀 38,775評論 3贊 343
?白月光啟示錄
正文我和宋清朗相戀三年押逼，在試婚紗的時候發(fā)現(xiàn)自己被綠了步藕。大學(xué)時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片。...
茶點故事閱讀 40,926評論 1贊 353
活死人
序言：一個原本活蹦亂跳的男人離奇死亡挑格，死狀恐怖咙冗，靈堂內(nèi)的尸體忽然破棺而出，到底是詐尸還是另有隱情漂彤，我是刑警寧澤雾消，帶...
沈念sama閱讀 36,580評論 5贊 351
?日本核電站爆炸內(nèi)幕
正文年R本政府宣布瞬逊，位于F島的核電站，受9級特大地震影響仪或，放射性物質(zhì)發(fā)生泄漏。R本人自食惡果不足惜士骤，卻給世界環(huán)境...
茶點故事閱讀 42,259評論 3贊 336
男人毒藥：我在死后第九天來索命
文/蒙蒙一范删、第九天我趴在偏房一處隱蔽的房頂上張望。院中可真熱鬧拷肌，春花似錦到旦、人聲如沸。這莊子的主人今日做“春日...
開封第一講書人閱讀 32,750評論 0贊 25
一樁弒父案添忘，背后竟有這般陰謀
文/蒼蘭香墨我抬頭看了看天上的太陽。三九已至若锁，卻和暖如春搁骑，著一層夾襖步出監(jiān)牢的瞬間，已是汗流浹背又固。一陣腳步聲響...
開封第一講書人閱讀 33,867評論 1贊 274
情欲美人皮
我被黑心中介騙來泰國打工仲器，沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留，地道東北人仰冠。一個月前我還...
沈念sama閱讀 49,368評論 3贊 379
代替公主和親
正文我出身青樓乏冀，卻偏偏與公主長得像，于是被迫代替她去往敵國和親洋只。傳聞我的和親對象是個殘疾皇子辆沦，可洞房花燭夜當晚...
茶點故事閱讀 45,930評論 2贊 361

Hive自定義函數(shù)與transform的使用

推薦閱讀更多精彩內(nèi)容