前言:为什么会写这个程序:因为生产环境下有一张表需要和hdfs上的数据关联(10年的数据),然后就创建了一个外部表,增加分区的话手动太复杂,所以就有了这个程序,写之前也想百度白嫖一下,发现没有,然后下边的它就来了
一、先来pom看看
4.0.0 com.dtstack hive_add_partition1.0-SNAPSHOT 8 8 org.apache.hive hive-jdbc1.1.0 org.apache.hadoop hadoop-common2.6.0 org.apache.hive hive-exec1.1.0 org.scala-tools maven-scala-plugin2.15.2 compile testCompile maven-compiler-plugin 3.6.0 1.8 1.8 org.apache.maven.plugins maven-assembly-plugin2.3 jar-with-dependencies org.apache.maven.plugins maven-surefire-plugin2.19 true org.apache.maven.plugins maven-shade-plugin2.4.3 package shade *:* META-INF public class GetHiveConn { private static final Logger LOG = LoggerFactory.getLogger(GetHiveConn.class); public static Connection getHiveConn(String url, String userName, String passWord){ //驱动 String driverName = "org.apache.hive.jdbc.HiveDriver"; Connection conn = null; try { //加载驱动 Class.forName(driverName); //连接对象,连接hive的本机ip,username,pwd预留参数 conn = DriverManager.getConnection("jdbc:hive2://" + url, userName, passWord); } catch (ClassNotFoundException | SQLException e) { LOG.error(e.getMessage()); } // return conn; } }
三、获取时间分区
package com.dtstack.utils;
import com.dtstack.app.AddHivePartitons;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
public class GetHivePartitionsList {
private static final Logger LOG = LoggerFactory.getLogger(GetHivePartitionsList.class);
public static void main(String[] args) {
String starTime = "20220601";
String endTime = "20220701";
List times = getpartitionsList(starTime, endTime);
for (int i = 0; i < times.size(); i++) {
System.out.println(times.get(i));
}
}
public static List getpartitionsList(String starTime, String endTime) {
//时间格式化
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyyMMdd");
DateFormat dateFormat = new SimpleDateFormat("yyyyMMdd");
List list = null;
try {
//获取开始时间
Date starTimeParse = simpleDateFormat.parse(starTime);
Date endTimeParse = simpleDateFormat.parse(endTime);
//定义一个集合用来存放时间
list = new ArrayList();
//重新实例化两个calender示例,将开始时间和结束时间传进去
Calendar tempStart = Calendar.getInstance();
tempStart.setTime(starTimeParse);
Calendar temEnd = Calendar.getInstance();
temEnd.setTime(endTimeParse);
//判断开始时间是否在结束时间之前
while (tempStart.before(temEnd)) {
//如果开始时间一直在结束时间之前,那么就将这个时间添加到list中
list.add(dateFormat.format(tempStart.getTime()));
//每天加一
tempStart.add(Calendar.DAY_OF_YEAR, 1);
}
} catch (ParseException e) {
LOG.error(e.getMessage());
}
list.add(endTime);
return list;
}
}
四、主函数
package com.dtstack.app;
import com.dtstack.utils.GetHiveConn;
import groovy.json.JsonOutput;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
import static com.dtstack.utils.GetHiveConn.getHiveConn;
import static com.dtstack.utils.GetHivePartitionsList.getpartitionsList;
public class AddHivePartitons {
private static final Logger LOG = LoggerFactory.getLogger(AddHivePartitons.class);
public static void main(String[] args) {
//添加分区的表
String tableName = args[0];//xxx
String url = args[1];//"xxx:8191/xxx"
String startTime = args[2];//20220601
String endTime = args[3];//20220701
//获取hive的连接
Connection hiveConn = getHiveConn(url, "", "");
//获取想要生成分区数量的list
List times = getpartitionsList(startTime, endTime);
//循环便利list,对添加分区SQL进行拼接
for (int i = 0; i < times.size(); i++) {
//拼接添加分区的SQL
String sql = "alter table " + tableName + " add if not exists PARTITION (pt=" + times.get(i) + ") " +
"LOCATION 'hdfs://xxx/user/hive/warehouse/xxx.db/"+ tableName + "/pt=" + times.get(i) + "'";
// String delSql = "alter table xxx drop partition(pt=" + times.get(i) + ")";
// System.out.println(times.get(i));
// System.out.println(sql);
try {
//执行SQL
PreparedStatement preparedStatement = hiveConn.prepareStatement(sql);
//返回结果值
boolean execute = preparedStatement.execute();
if (execute) {
System.out.println("新增分区:" + times.get(i) + "成功");
}else {
System.out.println("新增分区:" + times.get(i) + "失败");
}
} catch (SQLException e) {
LOG.error(e.getMessage());
}
finally {
try {
hiveConn.close();
} catch (SQLException e) {
LOG.error(e.getMessage());
}
}
}
}
}
五、注意事项
1、请修改对应的SQL语句;
2、入参分别是tableName、url、startTime、endTime;
3、url只需要ip:port/database;
4、startTime传入的是yyyyMMdd,如果想要传别的格式请修改GetHivePartitionsList类;
5、说下GetHivePartitionsList,传20220501,20220505,就会返回(20220501,20220502,20220503,...),我直接添加的是2014-2022,很稳
6、其他的有什么问题可以联系我



