Hive学习之六 《Hive进阶— —hive jdbc》 详解

接Hive学习五

http://www.cnblogs.com/invban/p/5331159.html

一、配置环境变量

　　hive jdbc的开发，在开发环境中，配置Java环境变量

修改/etc/profile
vi /etc/profile
PATH=$PATH:/usr/java/jdk1.6.0_31/bin:/opt/litong/bin
source /etc/profile   立即起效

二、配置文件开发

Hive_sql=select ds,hour,huodong,pv,uv from rpt.rpt_sale_daily where ds='{$date}' and hour='{$hour}' limit 20
Mysql_table=rpt_sale_daily
mysql_columns=id,hours,huodong,pvv,uvv
mysql_delete=delete from rpt_sale_daily where id='{$date}'

三、创建项目实现hive的jdbc接口

　　①配置Connection的Java文件。

import java.sql.Connection;
import java.sql.DriverManager;
public class MyConnection {
    public static Connection getMysqlInstance() throws Exception
    {
        
        Class.forName("com.mysql.jdbc.Driver") ;
        Connection con = 
              DriverManager.getConnection("jdbc:mysql://192.168.0.115:3306/test","root","123456");
        return con; 
    }
    public static Connection getHiveInstance() throws Exception{
        
        Class.forName("org.apache.hive.jdbc.HiveDriver") ;
        Connection con = 
              DriverManager.getConnection("jdbc:hive2://192.168.0.115:10000/default", "root", "");
        return con; 
    }
}

　　②开发Hive2Mysql的Java文件

import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Connection;
import java.util.Properties;

public class Hive2Mysql {
    
    public Hive2Mysql(String propertyName) throws Exception{
        init(propertyName);
    }
    
    Properties prop = new Properties();
    public void init(String propertyName) throws Exception{
        InputStream stream = new FileInputStream(propertyName);
        prop.load(stream);
    }
    
    public static void main(String[] args) {
        try {
            if(args.length<1){
                System.out.println("please set propertyName");
                System.exit(1);
            }
            String propertyName = args[0];
            Hive2Mysql h2m = new Hive2Mysql(propertyName);
            String hive_sql = h2m.prop.get("Hive_sql").toString();
            String mysql_table = h2m.prop.get("Mysql_table").toString();
            String mysql_columns = h2m.prop.getProperty("mysql_columns").toString();
            String mysql_delete = h2m.prop.getProperty("mysql_delete").toString();
            //insert into 
            String mysql_sql = "insert into "+mysql_table+" ("+mysql_columns+") values(" ;
            
            Connection mysqlCon = MyConnection.getMysqlInstance();
            Connection myHiveCon = MyConnection.getHiveInstance();
            
            //进行hive查询
            Statement stHive = myHiveCon.createStatement(); 
            ResultSet rsHive = stHive.executeQuery(hive_sql);
            
            Statement stMysql = mysqlCon.createStatement();
            //删除mysql里此次insert的数据
            stMysql.execute(mysql_delete);
            
            int len = hive_sql.split("from")[0].split("select")[1].trim().split(",").length ;
            System.out.println(len);
            String value = "";
            while(rsHive.next()){
                for(int i=1;i<=len;i++){
                    value += "'"+rsHive.getString(i)+"',";
                }
                //去掉最后一个逗号
                value = value.substring(0, value.length()-1);
                mysql_sql = mysql_sql+value+")";
                stMysql.execute(mysql_sql);
                System.out.println(mysql_sql);
                //重置value
                value = "";
                mysql_sql = "insert into "+mysql_table+" ("+mysql_columns+") values(" ;
            }
            //关闭连接
            rsHive.close();
            stHive.close();
            stMysql.close();
            mysqlCon.close();
            myHiveCon.close();
   
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }
    }
}

　　③在mysql中，创建表rpt_sale_daily

　　④将Java项目打包成jar文件，并上传配置文件aa.property

　　将jar文件传到/opt/litong/lib/下

　　配置文件放在/opt/litong/lib/property/rpt_sale_daily下

　　⑤编写hql文件代码，在/opt/litong/lib/property/rpt_sale_daily目录下创建rpt_sale_daily.hql

add jar /opt/litong/lib/hiveUDF.jar
create temporary function GetCommentNameOrId as 'com.litong.hive.udf.GetCommentNameOrId';

insert overwrite table rpt.rpt_sale_daily partition (ds='2015-08-28',hour='18')
select GetCommentNameOrId(url,"sale") huodong,count(url) pv,count(distinct guid) uv from default.track_log a 
where ds='2015-08-28' and hour='18'
group by ds,GetCommentNameOrId(url,"sale");

insert overwrite table rpt.rpt_sale_daily partition (ds='2015-08-28',hour='19')
select GetCommentNameOrId(url,"sale") huodong,count(url) pv,count(distinct guid) uv from default.track_log a 
where ds='2015-08-28' and hour='19'
group by ds,GetCommentNameOrId(url,"sale");

　　⑥将jar封装成命令hive2mysql。

　　在opt/litong/bin中，vi hive2mysql

java -jar /opt/litong/lib/hive2mysql.jar $*

　　注释$*：传的参数

 

　　⑦编写shell脚本，在/opt/litong/lib/property/rpt_sale_daily目录下创建rpt_sale_daily.sh　

#!/bin/sh
hive -f rpt_sale_daily.hql
hive2mysql /opt/litong/lib/property/rpt_sale_daily/aa.property

　　总结：

　　以后在执行过程中，配置三个文件即可，分别是：

　　aa.property,   rpt_sale_daily.hql,   rpt_sale_daily.sh

　　⑧执行shell脚本文件，rpt_sale_daily.sh

　　  得到的结果为：