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

hive是給了我們很多內(nèi)置函數(shù)的，比如轉(zhuǎn)大小寫命雀，截取字符串等，具體的都在官方文檔里面斩箫。但是并不是所有的函數(shù)都能滿足我們的需求吏砂，所以hive提供了給我們自定義函數(shù)的功能。
1乘客、至于怎么測(cè)試hive為我們提供的函數(shù)
因?yàn)閙ysql或者oracle中都可以使用偽表狐血，但是hive不行，所以可以使用以下方法
1）易核、創(chuàng)建表dual,create table dual(id string)
2）匈织、在本地創(chuàng)建文件dual.data,內(nèi)容為空格或者空一行
3）、將dual.data文件load到表dual
進(jìn)行測(cè)試牡直，比如：字符串截取

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

當(dāng)然也可以直接使用 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
     * 訪問(wèn)限制必須是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í)函數(shù)饵史，指定完整類名

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

到這就可以使用自定義臨時(shí)函數(shù)tolower()了满钟，測(cè)試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ù)電話號(hào)碼顯示歸屬地信息
java類

/**
 * 根據(jù)電話號(hào)碼前三位獲取歸屬地
 * @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;
    }
    //測(cè)試
    public static void main(String[] args) {
        String string = evaluate(136666);
        System.out.println(string);
    }
}

將工程打包上傳到linux胜榔，注意：如果名字還是跟上面一樣，那么需要重新連接hive服務(wù)端了零远，否則jar包是不會(huì)覆蓋的苗分，建議打的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í)函數(shù)牵辣，測(cè)試

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)在需要將它展示輸出到表中择浊，并解析對(duì)應(yīng)為4個(gè)字段。

{"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對(duì)象
        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;//評(píng)分
    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)還有不足的地方，就是沒(méi)顯示字段师脂〉？祝可以使用函數(shù)來(lái)實(shí)現(xiàn)重寫建表來(lái)命名。

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表一致，但是第三個(gè)字段時(shí)間戳要改為輸出周幾酌心。
Hive的 TRANSFORM 關(guān)鍵字提供了在SQL中調(diào)用自寫腳本的功能
適合實(shí)現(xiàn)Hive中沒(méi)有的功能又不想寫UDF的情況拌消。

1、編寫python腳本(先看看機(jī)器有沒(méi)有python)安券，用來(lái)將表時(shí)間戳轉(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)容合作請(qǐng)聯(lián)系作者

人面猴
序言：七十年代末鹦筹，一起剝皮案震驚了整個(gè)濱河市，隨后出現(xiàn)的幾起案子壳鹤，更是在濱河造成了極大的恐慌盛龄，老刑警劉巖，帶你破解...
沈念sama閱讀 222,946評(píng)論 6贊 518
死咒
序言：濱河連續(xù)發(fā)生了三起死亡事件芳誓，死亡現(xiàn)場(chǎng)離奇詭異余舶，居然都是意外死亡，警方通過(guò)查閱死者的電腦和手機(jī)锹淌，發(fā)現(xiàn)死者居然都...
沈念sama閱讀 95,336評(píng)論 3贊 399
救了他兩次的神仙讓他今天三更去死
文/潘曉璐我一進(jìn)店門匿值，熙熙樓的掌柜王于貴愁眉苦臉地迎上來(lái)，“玉大人赂摆，你說(shuō)我怎么就攤上這事挟憔≈有” “怎么了？”我有些...
開封第一講書人閱讀 169,716評(píng)論 0贊 364
道士緝兇錄：失蹤的賣姜人
文/不壞的土叔我叫張陵绊谭，是天一觀的道長(zhǎng)政恍。經(jīng)常有香客問(wèn)我，道長(zhǎng)达传，這世上最難降的妖魔是什么篙耗？我笑而不...
開封第一講書人閱讀 60,222評(píng)論 1贊 300
?港島之戀（遺憾婚禮）
正文為了忘掉前任，我火速辦了婚禮宪赶，結(jié)果婚禮上宗弯，老公的妹妹穿的比我還像新娘。我一直安慰自己搂妻，他們只是感情好蒙保，可當(dāng)我...
茶點(diǎn)故事閱讀 69,223評(píng)論 6贊 398
惡毒庶女頂嫁案：這布局不是一般人想出來(lái)的
文/花漫我一把揭開白布。她就那樣靜靜地躺著欲主，像睡著了一般邓厕。火紅的嫁衣襯著肌膚如雪。梳的紋絲不亂的頭發(fā)上扁瓢，一...
開封第一講書人閱讀 52,807評(píng)論 1贊 314
城市分裂傳說(shuō)
那天邑狸，我揣著相機(jī)與錄音，去河邊找鬼涤妒。笑死，一個(gè)胖子當(dāng)著我的面吹牛赚哗，可吹牛的內(nèi)容都是我干的她紫。我是一名探鬼主播，決...
沈念sama閱讀 41,235評(píng)論 3贊 424
雙鴛鴦連環(huán)套：你想象不到人心有多黑
文/蒼蘭香墨我猛地睜開眼屿储，長(zhǎng)吁一口氣：“原來(lái)是場(chǎng)噩夢(mèng)啊……” “哼贿讹！你這毒婦竟也來(lái)了？” 一聲冷哼從身側(cè)響起够掠，我...
開封第一講書人閱讀 40,189評(píng)論 0贊 277
萬(wàn)榮殺人案實(shí)錄
序言：老撾萬(wàn)榮一對(duì)情侶失蹤民褂，失蹤者是張志新（化名）和其女友劉穎，沒(méi)想到半個(gè)月后疯潭，有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體赊堪，經(jīng)...
沈念sama閱讀 46,712評(píng)論 1贊 320
?護(hù)林員之死
正文獨(dú)居荒郊野嶺守林人離奇死亡，尸身上長(zhǎng)有42處帶血的膿包…… 初始之章·張勛以下內(nèi)容為張勛視角年9月15日...
茶點(diǎn)故事閱讀 38,775評(píng)論 3贊 343
?白月光啟示錄
正文我和宋清朗相戀三年竖哩，在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了哭廉。大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片。...
茶點(diǎn)故事閱讀 40,926評(píng)論 1贊 353
活死人
序言：一個(gè)原本活蹦亂跳的男人離奇死亡相叁，死狀恐怖遵绰，靈堂內(nèi)的尸體忽然破棺而出辽幌，到底是詐尸還是另有隱情，我是刑警寧澤椿访，帶...
沈念sama閱讀 36,580評(píng)論 5贊 351
?日本核電站爆炸內(nèi)幕
正文年R本政府宣布乌企，位于F島的核電站，受9級(jí)特大地震影響成玫，放射性物質(zhì)發(fā)生泄漏加酵。R本人自食惡果不足惜，卻給世界環(huán)境...
茶點(diǎn)故事閱讀 42,259評(píng)論 3贊 336
男人毒藥：我在死后第九天來(lái)索命
文/蒙蒙一梁剔、第九天我趴在偏房一處隱蔽的房頂上張望虽画。院中可真熱鬧，春花似錦荣病、人聲如沸码撰。這莊子的主人今日做“春日...
開封第一講書人閱讀 32,750評(píng)論 0贊 25
一樁弒父案个盆，背后竟有這般陰謀
文/蒼蘭香墨我抬頭看了看天上的太陽(yáng)脖岛。三九已至，卻和暖如春颊亮，著一層夾襖步出監(jiān)牢的瞬間柴梆，已是汗流浹背。一陣腳步聲響...
開封第一講書人閱讀 33,867評(píng)論 1贊 274
情欲美人皮
我被黑心中介騙來(lái)泰國(guó)打工终惑，沒(méi)想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留绍在，地道東北人。一個(gè)月前我還...
沈念sama閱讀 49,368評(píng)論 3贊 379
代替公主和親
正文我出身青樓雹有，卻偏偏與公主長(zhǎng)得像偿渡，于是被迫代替她去往敵國(guó)和親。傳聞我的和親對(duì)象是個(gè)殘疾皇子霸奕，可洞房花燭夜當(dāng)晚...
茶點(diǎn)故事閱讀 45,930評(píng)論 2贊 361

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

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