3.1 Hive_UDF

1 安装

首先需要安装hadoop 、 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&amp;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 打包上传

3.3创建UDF

临时函数

报错找不到jar

需要将jar加到缓存中

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

CREATE TEMPORARY FUNCTION hello AS "UDF.udfTest";

以上操作仅在当前session下有效

永久有效的办法

/home/ifeng/app/hive/auxlib

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

永久函数

1 上传到HDFS

2 永久添加jar

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

3.4 查看函数帮助

desc function extended upper;

查看自己的sayhello

添加自己的desc

查看upper的code

@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下添加并重新编译即可

启动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