从具有Kerberos认证的hive中获取表中的count的值对mysql的记录进行更新的java实现

pom.xml文件的依赖的包如下：

  <dependencies>
    <!-- https://mvnrepository.com/artifact/org.apache.hadoop/hadoop-common -->
    <dependency>
        <groupId>org.apache.hadoop</groupId>
        <artifactId>hadoop-common</artifactId>
        <version>3.1.1</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.apache.hadoop/hadoop-client -->
    <dependency>
        <groupId>org.apache.hadoop</groupId>
        <artifactId>hadoop-client</artifactId>
        <version>3.1.0</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.apache.hive/hive-jdbc -->
    <dependency>
        <groupId>org.apache.hive</groupId>
        <artifactId>hive-jdbc</artifactId>
        <version>1.2.1</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/org.apache.hive/hive-metastore -->
    <dependency>
        <groupId>org.apache.hive</groupId>
        <artifactId>hive-metastore</artifactId>
        <version>3.1.0</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/org.apache.hive/hive-metastore -->
    <dependency>
        <groupId>org.apache.hive</groupId>
        <artifactId>hive-exec</artifactId>
        <version>3.1.0</version>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.38</version>
    </dependency>

</dependencies>

接下来是java实现的hive工具类

package com.lm;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;

import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;

import comm.IOUtils;

import org.apache.hadoop.conf.Configuration;
import org.apache.hadoop.security.UserGroupInformation;



public class hiveJdbcUtils {
	static Logger LOG = LogManager.getLogger(hiveJdbcUtils.class);

	public static void getHiveCount(Connection sqlConn) throws IOException {
		ArrayList<HiveCountDomain> hiveCountDomains_sql = selectData(sqlConn);
		LOG.info("hiveCountDomains_sql is"+hiveCountDomains_sql.toString());
		ResultSet res = null;
		Statement stmt = null;
		Connection hiveConn = getHiveConnect();
		int hashCode = hiveConn.hashCode();
		LOG.info("hiveConn hascode is "+hashCode);
		
		try {
			if (null != hiveConn) {
				LOG.info("enter count method!");
				stmt = hiveConn.createStatement();
				for (HiveCountDomain hiveCountDomain : hiveCountDomains_sql) {
					// 此处应该传进hive的表名称
					String hiveDatabase = hiveCountDomain.getDatabaseName();
					String hivetable = hiveCountDomain.getTableName();
					// 此处需要加上hive的库名称例如scistor.hivetest
					LOG.info("now count hivedatabase is "+hiveDatabase);
					LOG.info("now count hivetable is "+hivetable);
					long begtime = System.currentTimeMillis();
					String sql = "select count(1) from " + hiveDatabase + "." + hivetable;
					res = stmt.executeQuery(sql);
					long endtime = System.currentTimeMillis();
					long spentTime=endtime-begtime;
					LOG.info("count " + hivetable +" spent time is "+spentTime);
					while (res.next()) {
						//System.out.println("res.toString is "+res.toString());
						//HiveCountDomain Domain_hive = new HiveCountDomain();
						//hiveCountDomain.setId(res.getString(1));
						hiveCountDomain.setCount(res.getLong(1));
						//hiveCountDomains_hive.add(hiveCountDomain);
						LOG.info("从hive中查询到的记录，需要对mysql进行更新的记录为" + hiveCountDomain);
						//hive中查到一条记录就进行更新
						update(sqlConn,hiveCountDomain);
						
					}
				}

			}
		} catch (Exception e) {
			LOG.error("getHiveCount has error "+e);
			//e.printStackTrace();
		} finally {
			try {
				if (res != null) {
					res.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
			try {
				if (stmt != null) {
					stmt.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
			try {
				if (stmt != null) {
					stmt.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
			try {
				if (hiveConn != null) {
					hiveConn.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}

	}




	public static Connection getHiveConnect() {
		// 创建hive连接
		Connection hiveConn = null;
		try {

			String proKey = "java.security.krb5.conf";
			///把etc/krb5.conf改成自己的配置文件路径
			String proValue = "/etc/krb5.conf";
			//下面配置文件要改成自己keytab的路径
			String keyTab = "/home/scistor/keytab/hd40/test01.keytab";
			//下面配置文件要改成自己的用户
			String krbPrincipal = "test01@HADOOP.COM";
			String confKey = "hadoop.security.authentication";
			String confValue = "Kerberos";
			//String hivePrincipal = "hive/hadoop.hadoop.com@HADOOP.COM";
			//String connUrl = "jdbc:hive2://109.200.106.102:21066/;principal=" + hivePrincipal;
			String connUrl = "jdbc:hive2://lm93:10000/;principal=hive/lm93@HADOOP.COM";
			if (!connUrl.contains("jdbc:hive2:")){
				connUrl = "jdbc:hive2://" + connUrl;
			}
			System.setProperty(proKey,proValue);
			//口令认证，不需要账号密码
			Configuration configuration = new Configuration();
			configuration.set(confKey,confValue);
			UserGroupInformation.setConfiguration(configuration);
			UserGroupInformation.loginUserFromKeytab(krbPrincipal, keyTab);
			Class.forName("org.apache.hive.jdbc.HiveDriver");
			hiveConn = DriverManager.getConnection(connUrl);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return hiveConn;
	}
	
	}
	
}

以上就是从具有Kerberos认证的hive中获取表中的count的值对mysql的记录进行更新的java实现。