数据仓库----Hive进阶篇 二

上回说到内置函数

---

四、hive的表连接

学习前准备三张表：

1、等值连接

如果连接条件中是一个等号这是等值连接

2、不等值连接

如果连接条件中不是一个等号这是不等值连接

3、外连接

例题：按部门统计员工人数：部门号，部门名称，人数
1）、使用等值连接：

结果为：

查询部门表得到：

可以看出通过直接连接查询的结果少了一个部门，这是因为在empno表中没有operations这个部门，导致where语句不成立，所以就没有输出。
2）、使用外连接：
通过外连接可以将对于连接条件不成立的记录仍然包含在最后的结果中。
a.左外连接：当连接条件不成立的时候连接左面的表依然可以保存在最后的结果当中
b.右外连接：当连接条件不成立的时候连接右面的表依然可以保存在最后的结果当中

注意：所有没有在聚合函数中的列都需要在group by 后面！

4、自连接

案例：查看员工的姓名和员工老板的姓名
自连接的核心：通过表的别名将同一张表视为多张表

---

五、Hive中的子查询

Hive只支持：from和where子句中的子查询

实例：查询部门名称是SALES和ACCOUNTING的员工姓名

hive> select e.ename from emp e where e.deptno in (select d.deptno from dept d where d.dname='SALES' or d.dname='ACCOUNTING');

注意的问题：

1. 语法中的括号
2. 合理的书写风格
3. Hive中只支持where和from子句中的子查询
4. 主查询和子查询可以不是同一张表
5. 子查询中的空值问题：当子查询返回值中有空值时不能使用 not in逻辑运算符。
   实例：查询不是老板的员工：
   如果使用 not in 如下图，则没有查询结果

hive> select * from emp e where e.empno not in (select e1.mgr from emp e1);

修正：

hive> select * from emp e where e.empno not in (select e1.mgr from emp e1 where e1.mgr is not null);

---

六、Hive的Java客户端操作

1、启动Hive远程服务：

# hive --service hiveserver

两种方式操作Hive客户端中的数据：

1、JDBC

1).步骤：
    a.获取连接
    b.创建运行环境
    c.执行HQL
    d.处理结果
    e.释放资源
2).代码演示：

工具类

package com.sfd.jdbcutils;

import java.sql.Connection;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
 * 1.在运行之前 先启动 hiveserver （hive --service hiveserver）
 * 2.导入依赖jar包时 需要在hive和hadoop中同时查找
 * 3.url的赋值时，指不指定hive中的数据库都一样，默认进入的是default数据库
 * @author qiqi
 *
 */
public class JDBCUtils {


    private static String drive="org.apache.hadoop.hive.jdbc.HiveDriver";
        private static String url="jdbc:hive://192.168.3.18:10000";

    static {
        try {
            Class.forName(drive);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static  Connection getConnection(){
        Connection conn=null;
        try {
            System.out.println("sgfdsger");
            conn = DriverManager.getConnection(url);
            System.out.println(conn);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

    public static void release(Connection conn,Statement st,ResultSet rs){

        if (rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }finally{
                rs=null;
            }
        }
        if(st!=null){
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }finally{
                st=null;
            }
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }finally{
                conn=null;
            }
        }   
    }
}

客户端：

package com.sfd.jdbc;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;


import com.sfd.jdbcutils.JDBCUtils;

public class JDBCClient {
    private static Connection conn;
    private static Statement st;
    private static ResultSet rs;

    public static void main(String[] args) {
        String sql="select * from sfd.employees";

        try {
            conn = JDBCUtils.getConnection();
            st = conn.createStatement();
            rs = st.executeQuery(sql);
            while(rs.next()){
                System.out.println(rs.getString(4));
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            JDBCUtils.release(conn, st, rs);
        }

    }
}

2、Thrift Client

我们可以直接使用Thrift Client程序直接操作服务器端：

package com.sfd.thriftclient;


import java.util.List;

import org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.stringLiteralSequence_return;
import org.apache.hadoop.hive.service.HiveClient;
import org.apache.thrift.protocol.TBinaryProtocol;
import org.apache.thrift.protocol.TProtocol;
import org.apache.thrift.transport.TSocket;
import org.apache.thrift.transport.TTransportException;



public class ThriftClient {
    public static void main(String[] args) throws Exception {
        //创建socket：链接
        TSocket socket=new TSocket("192.168.3.18", 10000);
        //创建一个协议
        TProtocol prot=new TBinaryProtocol(socket);
        //创建hive client
        HiveClient client=new HiveClient(prot);
        //打开socket
        socket.open();
        //执行HQL
        client.execute("show tables");
        //处理结果
        List<String> list=client.fetchAll();
        for(String s:list){
            System.out.println(s);
        }

    }
}

---

七、Hive自定义函数（UDF）：User Defined Function

可以字节应用与select语句，对查询结果做格式化处理后，再输出结果。

1、Hive自定义函数的实现细节

1).自定义UDF需要继承：org.apache.hadoop.hive.ql.UDF
2).需要evaluate函数，evaluate函数支持重载。

2、Hive自定义函数的部署运行

1).把程序打包放到目标机器上去
2).进入hive客户端，添加jar包：

hive> add jar /home/sfd/udf_test.jar

3).创建临时函数：

hive> create temporary function <函数名>
    >　as 'java全类名';

4).销毁临时函数：

hive> drop temporary function <函数名>;

3、Hive自定义函数的使用

hive> select <函数名> from table;

实例：实现函数content（’hello‘，’world‘）结果为：hellow*****world。

1.函数代码很简单：

package com.sfd.UDF;

import org.apache.hadoop.hive.ql.exec.UDF;
import org.apache.hadoop.io.Text;

public class ConcatString extends UDF{

    public Text evaluate(Text a,Text b){
        return new Text(a.toString()+"*****"+b.toString());
    }   
}

2.打成jar包(我起的名字为UTFC.jar);并添加到hive中去;

hive> add jar /home/sfd/UDFC.jar; 

3.定义临时函数content：

hive> create temporary function content as 'com.sfd.UDF.ConcatString';

4.使用：

hive> select content('hello','world');

结果为：
hello*****world