一、pom
org.apache.hive hive-jdbc 1.1.0 org.apache.hadoop hadoop-common 2.6.0 org.mybatis mybatis 3.4.6 mysql mysql-connector-java 5.1.38 # 二、目录 ![在这里插入图片描述](https://img-/5365076218b84b1b81f5795227b3ecde.png)三、配置类
mybatis.cfg
<?xml version="1.0" encoding="UTF-8" ?> hive_mapper.xml <?xml version="1.0" encoding="UTF-8" ?> select customer_sk ,product_sk, d_date,recent_num,recent_amount,order_num,order_dailyamount from dm_sales_order_count mysql_mapper.xml <?xml version="1.0" encoding="UTF-8" ?> insert into dm_sales_order_count values (#{ord.d_date}, #{ord.customer_sk}, #{ord.product_sk}, #{ord.order_num}, #{ord.order_dailyamount}, #{ord.recent_amount}, #{ord.recent_num}) # 三、代码 实体类:
public class SaleOrder {
private String d_date ;
private Integer customer_sk ;
private Integer product_sk;
private Long order_num ;
private Double order_dailyamount ;
private Double recent_amount ;
private Long recent_num ;
HiveDao
public interface HiveDao {
List findAll();
}
mysqlDao
public interface MysqlDao {
void saveDate(List lst);
}
ReadHiveTab(测试用的)
import .apache.regexp.internal.RE;
import java.sql.*;
import java.util.List;
public class ReadHiveTab {
public static List readTab(String dbname,String table){
try {
Class.forName(“org.apache.hive.jdbc.HiveDriver”);
Connection con = DriverManager.getConnection(“jdbc:hive2://192.168.56.111:10000/”+dbname,“root”,“root”);
PreparedStatement pstat = con.prepareStatement("select * from "+table);
ResultSet rs = pstat.executeQuery();
while (rs.next()){
System.out.println(rs.getInt(“customer_sk”));
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
}
Tools(main方法):从hive到sql
package com.njbdqn.utils;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.Reader;
import java.util.List;
public class Tools {
public static void main(String[] args) throws Exception {// ReadHiveTab.readTab("dm_sales_source","dm_sales_order_count");Reader reader = Resources.getResourceAsReader("mybatis.cfg.xml");SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader,"cm");SqlSession session = factory.openSession();HiveDao hdao = session.getMapper(HiveDao.class);List<SaleOrder> lst = hdao.findAll();session.close();Reader r1 = Resources.getResourceAsReader("mybatis.cfg.xml");SqlSessionFactory mf = new SqlSessionFactoryBuilder().build(r1, "ms");SqlSession se = mf.openSession();MysqlDao mdao = se.getMapper(MysqlDao.class);mdao.saveDate(lst);mit();se.close();}
}
四、打胖包后运行(因为执行环境没有driver等)
java -jar HiveBatchMysql.jar
=> 验证mysql是否增加了数据