3.1 Hive_UDF

落日映苍穹つ 2023-03-01 06:23 31阅读 0赞

### 文章目录 ###

*  1 安装
 *  2 JDBC链接Hive
 *   *  pom
     *  JDBCApp.java
 *  3 UDF
 *   *  3.1 继承UDF
     *  3.2 打包上传
     *  3.3创建UDF
     *   *  临时函数
         *  永久函数
     *  3.4 查看函数帮助
     *   *  添加自己的desc
         *  Hive内置函数
         *  启动Hive时重新编译

# 1 安装 #

首先需要安装hadoop 、 MySQL  
[MySQL安装][MySQL]

4.hive部署启动
    4.1 部署MySQL及创建hive用户， 参考CDH部署文档和视频
    mysql> grant all privileges on *.* to hive@'%' identified by 'ifeng';
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    
    mysql> flush privileges;
    Query OK, 0 rows affected (0.00 sec)
    
    4.2 配置
    https://docs.cloudera.com/documentation/enterprise/5-16-x/topics/cdh_ig_hive_metastore_configure.html
    
    [ruoze@ifeng conf]$ vi hive-site.xml 
    <?xml version="1.0" encoding="UTF-8"?>
    <?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
    <configuration>
    <property>
      <name>javax.jdo.option.ConnectionURL</name>
      <value>jdbc:mysql://ifeng:3306/hive?createDatabaseIfNotExist=true&useSSL=false</value>
      <description>the URL of the MySQL database</description>
    </property>
    
    <property>
      <name>javax.jdo.option.ConnectionDriverName</name>
      <value>com.mysql.jdbc.Driver</value>
    </property>
    
    <property>
      <name>javax.jdo.option.ConnectionUserName</name>
      <value>hive</value>
    </property>
    </configuration>
    
    4.3 启动metastore + hiveserver2服务
    nohup hive --service  metastore > ~/log/metastore.log 2>&1 &
    
    nohup  hiveserver2  > ~/log/hiveserver2.log 2>&1 &
    
    4.4 测试hiveserver2服务是否ok
    [ruoze@ifeng ~]$ beeline
    which: no hbase in (/home/ruoze/app/hadoop/bin:/home/ruoze/app/hadoop/sbin:/home/ruoze/app/hive/bin:/usr/java/jdk1.8.0_121/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/home/ruoze/.local/bin:/home/ruoze/bin)
    Beeline version 1.1.0-cdh5.16.2 by Apache Hive
    beeline> !connect jdbc:hive2://ifeng:10000/default
    
    
    scan complete in 1ms
    Connecting to jdbc:hive2://ifeng:10000/default
    Enter username for jdbc:hive2://ifeng:10000/default: ruoze   输入hiveserver2进程启动的用户名称
    Enter password for jdbc:hive2://ifeng:10000/default: 无需输入密码
    
    0: jdbc:hive2://ifeng:10000/default> show databases;
    +----------------+--+
    | database_name  |
    +----------------+--+
    | default        |
    +----------------+--+
    1 row selected (0.782 seconds)
    0: jdbc:hive2://ifeng:10000/default>

# 2 JDBC链接Hive #

## pom ##

<dependency>
                <groupId>org.apache.hadoop</groupId>
                <artifactId>hadoop-client</artifactId>
                <version>2.6.0-cdh5.16.2</version>
            </dependency>
            <dependency>
                <groupId>org.apache.hive</groupId>
                <artifactId>hive-jdbc</artifactId>
                <version>1.1.0-cdh5.16.2</version>
            </dependency>
            <dependency>
                <groupId>org.apache.hive</groupId>
                <artifactId>hive-exec</artifactId>
                <version>1.1.0-cdh5.16.2</version>
            </dependency>

## JDBCApp.java ##

package ifeng;
    
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.Statement;
    
    
    public class JDBCApp {
        
        private static String driverName = "org.apache.hive.jdbc.HiveDriver";
    
        public static void main(String[] args) throws Exception {
        
            Class.forName(driverName);
            Connection con = DriverManager.getConnection("jdbc:hive2://10.103.66.15:10000/default", "ifeng", "");
            Statement stmt = con.createStatement();
            String tableName = "web3";
            String sql = "select * from " + tableName;
            System.out.println("Running: " + sql);
            ResultSet res = stmt.executeQuery(sql);
            while (res.next()) {
        
                System.out.println(res.getString("domain") + "\t" + res.getString("time") + res.getString("traffic"));
            }
        }
    }

create table if not exists web3(
            domain string,
            time string,
            traffic int) row format delimited fields terminated by ',';
    
    
    
    load data local inpath '/data/testData/data01.txt' overwrite into table web3;

# 3 UDF #

## 3.1 继承UDF ##

**pom**

<dependency>
                <groupId>org.apache.hive</groupId>
                <artifactId>hive-jdbc</artifactId>
                <version>1.1.0-cdh5.16.2</version>
            </dependency>
            <dependency>
                <groupId>org.apache.hive</groupId>
                <artifactId>hive-exec</artifactId>
                <version>1.1.0-cdh5.16.2</version>
            </dependency>

**udfTest.java**

package UDF;
    
    import org.apache.hadoop.hive.ql.exec.UDF;
    
    public class udfTest extends UDF {
        
        public String evaluate(String input){
        
            return "hello:" + input;
        }
    }

## 3.2 打包上传 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTM4MTgzMw_size_16_color_FFFFFF_t_70]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTM4MTgzMw_size_16_color_FFFFFF_t_70 1]

![在这里插入图片描述][20200727212446973.png]

## 3.3创建UDF ##

### 临时函数 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTM4MTgzMw_size_16_color_FFFFFF_t_70 2]  
报错找不到jar  
![在这里插入图片描述][20200727213010363.png]  
需要将jar加到缓存中

add jar /home/ifeng/app/hive/lib/ifeng-hadoop-1.0.jar

![在这里插入图片描述][20200727213511881.png]

![在这里插入图片描述][20200727213655460.png]

CREATE TEMPORARY FUNCTION hello AS "UDF.udfTest";

![在这里插入图片描述][20200727214708613.png]  
![在这里插入图片描述][20200727214755492.png]  
以上操作仅在当前session下有效

**永久有效的办法**

/home/ifeng/app/hive/auxlib

放在这目录下即可不用add jar

### 永久函数 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTM4MTgzMw_size_16_color_FFFFFF_t_70 3]  
**1 上传到HDFS**

**2 永久添加jar**

CREATE FUNCTION sayhello AS "UDF.udfTest"
    USING JAR "hdfs://ifeng:9000/hdfsapi/ifeng-hadoop-1.0.jar"

![在这里插入图片描述][20200727221601789.png]

## 3.4 查看函数帮助 ##

desc function extended upper;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTM4MTgzMw_size_16_color_FFFFFF_t_70 4]  
查看自己的sayhello  
![在这里插入图片描述][20200727230302496.png]

### 添加自己的desc ###

查看upper的code  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTM4MTgzMw_size_16_color_FFFFFF_t_70 5]

@Description(name = "upper,ucase",
        value = "_FUNC_(str) - Returns str with all characters changed to uppercase",
        extended = "Example:\n"
        + "  > SELECT _FUNC_('Facebook') FROM src LIMIT 1;\n" + "  'FACEBOOK'")

### Hive内置函数 ###

添加自己的UDF进入到内置函数  
在FunctionRegistry.java下添加并重新编译即可  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTM4MTgzMw_size_16_color_FFFFFF_t_70 6]

### 启动Hive时重新编译 ###

**创建.sql**

vi hive-init.sql
    
    add jar /home/ifeng/app/hive/lib/ifeng-hadoop-1.0.jar;
    CREATE TEMPORARY FUNCTION hello AS "UDF.udfTest";

**hive -i hive-init.sql**  
启动便会自动创建hive  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTM4MTgzMw_size_16_color_FFFFFF_t_70 7]

[MySQL]: https://blog.csdn.net/weixin_39381833/article/details/105379643
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTM4MTgzMw_size_16_color_FFFFFF_t_70]: /images/20230209/4826e72914cd4295a74d7430a34f003c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTM4MTgzMw_size_16_color_FFFFFF_t_70 1]: /images/20230209/e077a8d58bd9413f8d3e63d03c707876.png
[20200727212446973.png]: /images/20230209/2e3dcf4b1da54b588ec2cb0d572a0c6c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTM4MTgzMw_size_16_color_FFFFFF_t_70 2]: /images/20230209/41b5e8a1789e4ab4a8cf6cc0baff650c.png
[20200727213010363.png]: /images/20230209/e922a59d3c98411a9cea5db925716e58.png
[20200727213511881.png]: /images/20230209/8350a5e2ca034a85a6dfcf504f30a7ee.png
[20200727213655460.png]: /images/20230209/a6b377a6c7be41bb8d5a8eb220d8274a.png
[20200727214708613.png]: /images/20230209/5303b49510fa4056955d0e66af4d3600.png
[20200727214755492.png]: /images/20230209/7d655c828a074b1db188d12fda001e26.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTM4MTgzMw_size_16_color_FFFFFF_t_70 3]: /images/20230209/7750159a84514ff7be66dbb15ecb8912.png
[20200727221601789.png]: /images/20230209/7755ca178b914dc09e088e97b79dde97.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTM4MTgzMw_size_16_color_FFFFFF_t_70 4]: /images/20230209/b243d71a948940c0bca57ac8053f8a2e.png
[20200727230302496.png]: /images/20230209/62d6489c4eec4a77a67f6fb9e2049be5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTM4MTgzMw_size_16_color_FFFFFF_t_70 5]: /images/20230209/42cf06aa695f41b697ce5c8a9c639c9b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTM4MTgzMw_size_16_color_FFFFFF_t_70 6]: /images/20230209/f721db44848f403aae2e23b438c12026.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTM4MTgzMw_size_16_color_FFFFFF_t_70 7]: /images/20230209/31d0bc12e99944ae89db867c40ada9fc.png