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

　　  得到的結果為：