修改/etc/profile

vi /etc/profile

PATH=$PATH:/usr/java/jdk1..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}'

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;

    }

}

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();

        }

    }

}

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='')

select GetCommentNameOrId(url,"sale") huodong,count(url) pv,count(distinct guid) uv from default.track_log a

where ds='2015-08-28' and hour=''

group by ds,GetCommentNameOrId(url,"sale");

insert overwrite table rpt.rpt_sale_daily partition (ds='2015-08-28',hour='')

select GetCommentNameOrId(url,"sale") huodong,count(url) pv,count(distinct guid) uv from default.track_log a

where ds='2015-08-28' and hour=''

group by ds,GetCommentNameOrId(url,"sale");

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

#!/bin/sh

hive -f rpt_sale_daily.hql

hive2mysql /opt/litong/lib/property/rpt_sale_daily/aa.property