Hive脚本、Hive API、Hive 自定义函数

1、脚本

./hive -e 'select * from t_student';

hive -e "hql"
编写hive脚本
vi weblog.sh
#!/bin/bash
day_str="2017-09-17"
#day_str=`date +"%Y-%m-%d"`

hive_exec=/opt/apache-hive-1.2.2-bin/bin/hive

HQL_user_active_day="insert into weblog.t_activity_user partition(day='$day_str') select tmp.ip ip,tmp.uname uname,tmp.access_time access_time,tmp.url url from (select ip,uname,access_time,url,row_number() over(partition by uname order by access_time) rk from weblog.t_web_log where day='$day_str') tmp where rk = 1"

HQL_user_new_day="insert into weblog.t_new_user partition(day='$day_str') select tau.ip,tau.uname,tau.access_time,tau.url from weblog.t_activity_user tau left join weblog.t_history_user thu on tau.uname = thu.uname where tau.day = '$day_str' and thu.uname is null"

HQL_user_history_day="insert into weblog.t_history_user select uname from weblog.t_new_user where day = '$day_str'"

$hive_exec -e "$HQL_user_active_day"
$hive_exec -e "$HQL_user_new_day"
$hive_exec -e "$HQL_user_history_day"

还可以把hql语句写在一个sql脚本里
用hive -f sqlscript.sql

2、JAVA-API

<dependency>
    <groupId>org.apache.hive</groupId>
    <artifactId>hive-jdbc</artifactId>
    <version>1.2.2</version>
    <scope>provided</scope>
</dependency>

package com.bawei.hive;

import java.sql.*;

public class ClientDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("org.apache.hive.jdbc.HiveDriver");
        Connection conn = DriverManager.getConnection("jdbc:hive2://hdp1:10000", "root", "");

        Statement stat = conn.createStatement();

        stat.execute("load data local inpath '/root/09-17.log' into table weblog.t_web_log partition(day='09-17')");

        stat.execute("use weblog");

        stat.execute("insert into t_activity_user partition(day='09-17')\n" +
                "select\n" +
                "tmp.ip ip,tmp.uname uname,tmp.access_time access_time,tmp.url url\n" +
                "from\n" +
                "(select\n" +
                "ip,uname,access_time,url,row_number() over(partition by uname order by access_time) rk\n" +
                "from\n" +
                "t_web_log\n" +
                "where day='09-17') tmp\n" +
                "where rk = 1");

        stat.execute("insert into t_new_user partition(day='09-17')\n" +
                "select\n" +
                "tau.ip,tau.uname,tau.access_time,tau.url\n" +
                "from\n" +
                "t_activity_user tau left join t_history_user thu on tau.uname = thu.uname\n" +
                "where tau.day = '09-17' and thu.uname is null");

        stat.execute("insert into t_history_user\n" +
                "select uname from t_new_user where day = '09-17'");

        ResultSet rs = stat.executeQuery("select * from t_activity_user where day = '09-17'");

        System.out.println("ip      |      uname     |     access_time     |      url");
        while (rs.next()){
            String ip = rs.getString(1);
            String uname = rs.getString(2);
            String accessTime = rs.getString(3);
            String url = rs.getString(4);

            System.out.println(ip + " | " + uname + " | " + accessTime + " | " + url);
        }

        System.out.println("");
        System.out.println("=============================================================");
        System.out.println("");

        ResultSet rs2 = stat.executeQuery("select * from t_new_user where day = '09-17'");
        System.out.println("ip      |      uname     |     access_time     |      url");
        while (rs2.next()){
            String ip = rs2.getString(1);
            String uname = rs2.getString(2);
            String accessTime = rs2.getString(3);
            String url = rs2.getString(4);

            System.out.println(ip + " | " + uname + " | " + accessTime + " | " + url);
        }

        System.out.println("");
        System.out.println("=============================================================");
        System.out.println("");

        ResultSet rs3 = stat.executeQuery("select * from t_history_user");
        System.out.println("uname");
        while (rs3.next()){
            System.out.println(rs3.getString(1));
        }

    }
}

3、Hive自定义函数

package com.bawei.hive;

import com.alibaba.fastjson.JSONObject;
import org.apache.hadoop.hive.ql.exec.UDF;

public class JsonToString extends UDF {

    public String evaluate(String json,String param){
        JSONObject jsonObject = JSONObject.parseObject(json);
        return jsonObject.getString(param);
    }
}

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.bawei</groupId>
    <artifactId>hive</artifactId>
    <version>1.0-SNAPSHOT</version>
    <dependencies>
        <!-- https://mvnrepository.com/artifact/org.apache.hive/hive-jdbc -->
        <!--<dependency>
            <groupId>org.apache.hive</groupId>
            <artifactId>hive-jdbc</artifactId>
            <version>1.2.2</version>
            <scope>provided</scope>
        </dependency>-->
        <!-- https://mvnrepository.com/artifact/org.apache.hadoop/hadoop-client -->
        <!--<dependency>
            <groupId>org.apache.hadoop</groupId>
            <artifactId>hadoop-client</artifactId>
            <version>2.7.3</version>
            <scope>provided</scope>
        </dependency>-->

        <!-- https://mvnrepository.com/artifact/org.apache.hive/hive-exec -->
        <dependency>
            <groupId>org.apache.hive</groupId>
            <artifactId>hive-exec</artifactId>
            <version>1.2.2</version>
            <scope>provided</scope>
        </dependency>

        <!-- https://mvnrepository.com/artifact/com.alibaba/fastjson -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.47</version>
        </dependency>

    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-shade-plugin</artifactId>
                <version>2.4.3</version>
                <executions>
                    <execution>
                        <phase>package</phase>
                        <goals>
                            <goal>shade</goal>
                        </goals>
                        <configuration>
                            <filters>
                                <filter>
                                    <artifact>*:*</artifact>
                                    <excludes>
                                        <exclude>META-INF/*.SF</exclude>
                                        <exclude>META-INF/*.DSA</exclude>
                                        <exclude>META-INF/*.RSA</exclude>
                                    </excludes>
                                </filter>
                            </filters>
                        </configuration>
                    </execution>

                </executions>
            </plugin>
        </plugins>
    </build>
</project>

1.test范围是指测试范围有效,在编译和打包时都不会使用这个依赖
2.compile范围是指编译范围内有效,在编译和打包时都会将依赖存储进去
3.provided依赖,在编译和测试过程中有效,最后生成的war包时不会加入 例如:
servlet-api,因为servlet-api tomcat服务器已经存在了,如果再打包会冲突
4.runtime在运行时候依赖,在编译时候不依赖
默认依赖范围是compile

创建永久函数

方法一：

add jar /opt/apache-hive-1.2.2-bin/lib/hive-udf2.jar; 
create function getjson AS 'com.bawei.hive.JsonToString';

方法二：
在hive-site.xml里添加jar包

<property>
    <name>hive.aux.jars.path</name>
    <value>file:///opt/apache-hive-1.2.2-bin/lib/hive-udf2.jar</value>
</property>

create function getjson AS 'com.bawei.hive.JsonToString';

方法三：

create function getjson as 'com.bawei.hive.JsonToString' using jar 'hdfs://hiveudf/hive-udf2.jar';

drop function getjson

创建临时函数：temporary

create temporary function ……