大数据项目实战之电信信号诊断(三)

浅沫记忆

目标:写个hiveJDBC脚本,将分区完成的数据加载到hive表中

package com.czxy.telecom.mapreduce;
import org.apache.hadoop.conf.Configuration;
import org.apache.hadoop.fs.FileSystem;
import org.apache.hadoop.fs.LocatedFileStatus;
import org.apache.hadoop.fs.Path;
import org.apache.hadoop.fs.RemoteIterator;
import java.net.URI;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class HiveJdbcTest {
public static void main(String[] args) throws Exception {
//注册驱动
try {
String driverName = "org.apache.hive.jdbc.HiveDriver";
Class.forName(driverName);
} catch (ClassNotFoundException e) {
e.printStackTrace();
System.exit(1);
}
//获取连接
Connection con = DriverManager.getConnection("jdbc:hive2://node09:10000/telecom", "root", "123456");
//获取执行对象
Statement stmt = con.createStatement();
//实例fs
 Configuration configuration = new Configuration();
FileSystem fileSystem = FileSystem.get(new URI("hdfs://192.168.100.109:8020"), configuration, "root");
//获取目录下所有的文件
RemoteIterator<LocatedFileStatus> files = fileSystem.listFiles(new Path("/workspace/01_telecom/data/device"), true);
while (files.hasNext()){
LocatedFileStatus next = files.next();
      String name = next.getPath().getName();
      //获取年月日
      String yyyy = name.split("\\.")[0].split("-")[0];
      String MM = name.split("\\.")[0].split("-")[1];
      String dd = name.split("\\.")[0].split("-")[2];
      String path = next.getPath().toString();
      System.out.println(path);
      //装填hql语句
      setHIveFromHDFS(path, stmt,yyyy,MM,dd);
    }
}
/**
 * 装填hql语句
 * @param path 文件路径
 * @param stmt 执行对象
 * @param yyyy 年
 * @param MM 月 
 * @param dd 日
 * @throws SQLException
 */
private static void setHIveFromHDFS(String path,Statement stmt,String yyyy,String MM,String dd) throws SQLException {
execSQL("LOAD DATA inpath '"+path+"' OVERWRITE INTO TABLE device PARTITION (yyyy='"+yyyy+"',MM='"+MM+"',dd='"+dd+"')",stmt);
}
/**
 * 执行hql语句
 * @param sqlStr hql语句
 * @param stmt 执行对象
 * @throws SQLException
 */
private static void execSQL(String sqlStr,Statement stmt) throws SQLException {
    int i = stmt.executeUpdate(sqlStr);
    System.out.println(i==0?"插入成功!恭喜你!":"失败了");
}
}