java整合hive-jdbc
添加依赖与配置
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.1</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.data</groupId>
<artifactId>spring-data-hadoop</artifactId>
<version>2.5.0.RELEASE</version>
</dependency>
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-jdbc</artifactId>
<version>2.3.3</version>
<exclusions>
<exclusion>
<groupId>org.eclipse.jetty.aggregate</groupId>
<artifactId>*</artifactId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>org.apache.tomcat</groupId>
<artifactId>tomcat-jdbc</artifactId>
</dependency>
<dependency>
<groupId>jdk.tools</groupId>
<artifactId>jdk.tools</artifactId>
<version>1.8</version>
<scope>system</scope>
<systemPath>${JAVA_HOME}/lib/tools.jar</systemPath>
</dependency>
//properties文件中hive配置 #hive hive.url = jdbc.hive2//10.76.148.15:8183/hive hive.driver-class-name = org.apache.hive.jdbc.HiveDriver hive.user = root hive.password = root
配置数据源与JdbcTemplate
我们可以使用SpringBoot默认的 org.apache.tomcat.jdbc.pool.DataSource 数据源，并使用这个数据源装配一个JdbcTemplate。
import com.didichuxing.fe.offline.util.ConfigPropertyUtil; import org.apache.tomcat.jdbc.pool.DataSource; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.jdbc.core.JdbcTemplate; import java.util.ArrayList; import java.util.List; public class HiveConfig { private static final Logger logger = LoggerFactory.getLogger(HiveConfig.class); private static volatile HiveConfig config = null; private static ConfigPropertyUtil propertyUtil = ConfigPropertyUtil.getInstance("hiveConfig.properties"); private DataSource dataSource = null; private JdbcTemplate jdbcTemplate = null; private List<String> sparkTableNameList = null; public static HiveConfig getInstance(){ if(config == null){ synchronized (HiveConfig.class){ if (config == null){ config = new HiveConfig(); } } } return config; } private HiveConfig(){ init(); } private void init() { dataSource = new DataSource() { { try{ setUrl(propertyUtil.getPropertyVal("hive.url")); setDriverClassName(propertyUtil.getPropertyVal("hive.driver-class-name")); setUsername(propertyUtil.getPropertyVal("hive.user")); setPassword(propertyUtil.getPropertyVal("hive.password")); logger.info("hive数据源dataSource初始化完成"); }catch(Exception e){ logger.error(e.getMessage()); } } }; jdbcTemplate = new JdbcTemplate(dataSource); } public DataSource getDataSource() { return dataSource; } public JdbcTemplate getJdbcTemplate() { return jdbcTemplate; } }
执行hive的sql语句操作hive
import com.didichuxing.fe.offline.config.HiveConfig; import com.didichuxing.fe.offline.entity.TableInfo; import com.didichuxing.fe.offline.util.DateUtil; import com.didichuxing.fe.offline.util.ParquetShema; import com.didichuxing.fe.offline.util.SparkTool; import org.apache.hadoop.conf.Configuration; import org.apache.spark.sql.Dataset; import org.apache.spark.sql.Row; import org.apache.spark.sql.SparkSession; import org.apache.tomcat.jdbc.pool.DataSource; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.dao.DataAccessException; import org.springframework.jdbc.core.JdbcTemplate; import org.apache.hadoop.fs.Path; import java.nio.file.Paths; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.List; public class HiveJdbcDao { private static final Logger logger = (Logger) LoggerFactory.getLogger(HiveJdbcDao.class); private static HiveJdbcDao hiveJdbcDao = null; public static HiveJdbcDao getInstance(){ if(hiveJdbcDao == null){ synchronized (MysqlBaseDao.class){ if (hiveJdbcDao == null){ hiveJdbcDao = new HiveJdbcDao(); } } } return hiveJdbcDao; } private HiveJdbcDao(){ } private DataSource jdbcDataSource = HiveConfig.getInstance().getDataSource(); private JdbcTemplate hiveJdbcTemplate = HiveConfig.getInstance().getJdbcTemplate(); /** * 查询hive表中字段名以及类型 * @param abstractSql * @return * @throws SQLException */ public List<TableInfo> selectTableInfoFromHive(String abstractSql){ List<TableInfo> tableInfoList = new ArrayList<TableInfo>(); TableInfo tableInfo = new TableInfo(); Statement statement = null; logger.info("Running sql: " + abstractSql); try { statement = jdbcDataSource.getConnection().createStatement(); ResultSet res = statement.executeQuery(abstractSql); while (res.next()) { tableInfo.setColumnName(res.getString(1)); tableInfo.setColumnType(res.getString(2)); tableInfo.setColumnComment(res.getString(3)); tableInfoList.add(tableInfo); } } catch (SQLException e) { logger.info(e.getMessage()); } return tableInfoList; } /** * 查询hive库中表名 * @param abstractSql * @return * @throws SQLException */ public List<String> selectTableNameFromHive(String abstractSql){ List<String> tableNameList = new ArrayList<String>(); Statement statement = null; logger.info("Running sql: " + abstractSql); try { statement = jdbcDataSource.getConnection().createStatement(); ResultSet res = statement.executeQuery(abstractSql); logger.error( "hive表名String[]: " +res.toString()); while (res.next()) { tableNameList.add(res.getString(1)); } } catch (SQLException e) { logger.info(e.getMessage()); } return tableNameList; } /** * 自动从本地数据加载进入hive * @param filepath */ public void loadIntoHiveTable(String filepath, String tableName) { String dateFileFormat = DateUtil.getYesterdayFileFormat(); String[] dateSplit = dateFileFormat.split("/"); StringBuffer buildSql = new StringBuffer(); buildSql.append("load data inpath " ).append("\'").append(filepath).append("\'") .append(" into table fe.").append(tableName).append(" partition (year = ") .append(dateSplit[0]).append(", month = ").append(dateSplit[1]) .append(",day = ").append(dateSplit[2]).append(")"); // String sql = "load data inpath " + "\'" + filepath + "\'" + // " into table fe." + tableName + " partition (year = " + dateSplit[0] + ", month = " // + dateSplit[1] + ",day = " + dateSplit[2] + ")"; logger.info("将数据加载进入hive表的sql : {}", buildSql.toString()); try { hiveJdbcTemplate.execute(buildSql.toString()); } catch (DataAccessException dae) { logger.error(dae.toString()); } } /** * 对hive表结构进行更新（增加字段） * @param abstractSql */ public void updateHiveTable(String abstractSql) { try { hiveJdbcTemplate.execute(abstractSql); } catch (DataAccessException dae) { logger.error(dae.toString()); } } }
参考：https://blog.csdn.net/pengjunlee/article/details/81838480#查看Hive表%C2%A0%C2%A0 （他是springboot整合hive）
