springboot集成hive实战

淡淡的烟草味﹌ 2022-12-25 11:56 331阅读 0赞

springboot集成hive实现基本的api调用

maven坐标

  1. <properties>
  2.             <hadoop.version>3.3.0</hadoop.version>
  3.         </properties>
  4.                 <!-- hadoop -->
  5.                 <dependency>
  6.             <groupId>org.apache.hadoop</groupId>
  7.             <artifactId>hadoop-common</artifactId>
  8.             <version>${hadoop.version}</version>
  9.         </dependency>
  10.         <dependency>
  11.             <groupId>org.apache.hadoop</groupId>
  12.             <artifactId>hadoop-streaming</artifactId>
  13.             <version>${hadoop.version}</version>
  14.         </dependency>
  15.         <dependency>
  16.             <groupId>org.apache.hadoop</groupId>
  17.             <artifactId>hadoop-yarn-common</artifactId>
  18.             <version>${hadoop.version}</version>
  19.             <exclusions>
  20.                 <exclusion>
  21.                     <groupId>com.google.guava</groupId>
  22.                     <artifactId>guava</artifactId>
  23.                 </exclusion>
  24.             </exclusions>
  25.         </dependency>
  26.         <dependency>
  27.             <groupId>org.apache.hadoop</groupId>
  28.             <artifactId>hadoop-distcp</artifactId>
  29.             <version>${hadoop.version}</version>
  30.             <scope>provided</scope>
  31.         </dependency>
  32.         <dependency>
  33.             <groupId>org.apache.hadoop</groupId>
  34.             <artifactId>hadoop-mapreduce-client-core</artifactId>
  35.             <version>${hadoop.version}</version>
  36.         </dependency>
  37.         <dependency>
  38.             <groupId>org.apache.hadoop</groupId>
  39.             <artifactId>hadoop-hdfs</artifactId>
  40.             <version>${hadoop.version}</version>
  41.         </dependency>
  42.         <dependency>
  43.             <groupId>org.apache.hadoop</groupId>
  44.             <artifactId>hadoop-mapreduce-client-jobclient</artifactId>
  45.             <version>${hadoop.version}</version>
  46.             <scope>provided</scope>
  47.         </dependency>
  49.         <dependency>
  50.             <groupId>com.alibaba</groupId>
  51.             <artifactId>druid</artifactId>
  52.             <version>1.2.1</version>
  53.         </dependency>
  55.         <!-- hive依赖 -->
  56.         <dependency>
  57.             <groupId>org.apache.hive</groupId>
  58.             <artifactId>hive-jdbc</artifactId>
  59.             <version>2.3.0</version>
  60.         </dependency>
  62.         <!-- 中文分词器 -->
  63.         <dependency>
  64.             <groupId>cn.bestwu</groupId>
  65.             <artifactId>ik-analyzers</artifactId>
  66.             <version>5.1.0</version>
  67.         </dependency>

配置：

  1. spring:
  2.   application:
  3.     name: hadoop-demo
  4.   datasource:
  5.     hive: #hive数据源
  6.       url: jdbc:hive2://192.168.150.119:10000/default
  7.       type: com.alibaba.druid.pool.DruidDataSource
  8.       username: winterchen
  9.       password: winterchen
  10.       driver-class-name: org.apache.hive.jdbc.HiveDriver
  11.     common-config: #连接池统一配置，应用到所有的数据源
  12.       initialSize: 1
  13.       minIdle: 1
  14.       maxIdle: 5
  15.       maxActive: 50
  16.       maxWait: 10000
  17.       timeBetweenEvictionRunsMillis: 10000
  18.       minEvictableIdleTimeMillis: 300000
  19.       validationQuery: select 'x'
  20.       testWhileIdle: true
  21.       testOnBorrow: false
  22.       testOnReturn: false
  23.       poolPreparedStatements: true
  24.       maxOpenPreparedStatements: 20
  25.       filters: stat
  27. @Data
  28. @NoArgsConstructor
  29. @AllArgsConstructor
  30. @Component
  31. @ConfigurationProperties(prefix = "spring.datasource.hive", ignoreUnknownFields = false)
  32. public class HiveJdbcProperties { 
  34.     private String url;
  36.     private String type;
  38.     private String username;
  40.     private String password;
  42.     private String driverClassName;
  44. }
  46. @Data
  47. @NoArgsConstructor
  48. @AllArgsConstructor
  49. @Component
  50. @ConfigurationProperties(prefix = "spring.datasource.common-config", ignoreUnknownFields = false)
  51. public class DataSourceCommonProperties { 
  53.     private int initialSize = 10;
  54.     private int minIdle;
  55.     private int maxIdle;
  56.     private int maxActive;
  57.     private int maxWait;
  58.     private int timeBetweenEvictionRunsMillis;
  59.     private int minEvictableIdleTimeMillis;
  60.     private String validationQuery;
  61.     private boolean testWhileIdle;
  62.     private boolean testOnBorrow;
  63.     private boolean testOnReturn;
  64.     private boolean poolPreparedStatements;
  65.     private int maxOpenPreparedStatements;
  66.     private String filters;
  68.     private String mapperLocations;
  69.     private String typeAliasPackage;
  70. }
  72. @Slf4j
  73. @Configuration
  74. @EnableConfigurationProperties({ HiveJdbcProperties.class, DataSourceCommonProperties.class})
  75. public class HiveDruidConfiguration { 
  77.     @Autowired
  78.     private HiveJdbcProperties hiveJdbcProperties;
  80.     @Autowired
  81.     private DataSourceCommonProperties dataSourceCommonProperties;
  83.     @Bean("hiveDruidDataSource") //新建bean实例
  84.     @Qualifier("hiveDruidDataSource")//标识
  85.     public DataSource dataSource(){ 
  86.         DruidDataSource datasource = new DruidDataSource();
  88.         //配置数据源属性
  89.         datasource.setUrl(hiveJdbcProperties.getUrl());
  90.         datasource.setUsername(hiveJdbcProperties.getUsername());
  91.         datasource.setPassword(hiveJdbcProperties.getPassword());
  92.         datasource.setDriverClassName(hiveJdbcProperties.getDriverClassName());
  94.         //配置统一属性
  95.         datasource.setInitialSize(dataSourceCommonProperties.getInitialSize());
  96.         datasource.setMinIdle(dataSourceCommonProperties.getMinIdle());
  97.         datasource.setMaxActive(dataSourceCommonProperties.getMaxActive());
  98.         datasource.setMaxWait(dataSourceCommonProperties.getMaxWait());
  99.         datasource.setTimeBetweenEvictionRunsMillis(dataSourceCommonProperties.getTimeBetweenEvictionRunsMillis());
  100.         datasource.setMinEvictableIdleTimeMillis(dataSourceCommonProperties.getMinEvictableIdleTimeMillis());
  101.         datasource.setValidationQuery(dataSourceCommonProperties.getValidationQuery());
  102.         datasource.setTestWhileIdle(dataSourceCommonProperties.isTestWhileIdle());
  103.         datasource.setTestOnBorrow(dataSourceCommonProperties.isTestOnBorrow());
  104.         datasource.setTestOnReturn(dataSourceCommonProperties.isTestOnReturn());
  105.         datasource.setPoolPreparedStatements(dataSourceCommonProperties.isPoolPreparedStatements());
  106.         try { 
  107.             datasource.setFilters(dataSourceCommonProperties.getFilters());
  108.         } catch (SQLException e) { 
  109.             log.error("Druid configuration initialization filter error.", e);
  110.         }
  111.         return datasource;
  112.     }
  113. }

注册jdbcTemplate

  1. @Configuration
  2. public class HiveJdbcConfiguration { 
  4.     @Bean("hiveJdbcTemplate")
  5.     @Qualifier("hiveJdbcTemplate")
  6.     public JdbcTemplate jdbcTemplate(@Qualifier("hiveDruidDataSource") DataSource dataSource) { 
  7.         return new JdbcTemplate(dataSource);
  8.     }
  10. }

基本api调用

  1. package com.winterchen.hadoopdemo.service;
  3. import java.util.List;
  5. public interface HiveService { 
  7.     Object select(String hql);
  9.     List<String> listAllTables();
  11.     List<String> describeTable(String tableName);
  13.     List<String> selectFromTable(String tableName);
  15. }
  17. package com.winterchen.hadoopdemo.service.impl;
  19. import com.winterchen.hadoopdemo.service.HiveService;
  20. import lombok.extern.slf4j.Slf4j;
  21. import org.springframework.beans.factory.annotation.Autowired;
  22. import org.springframework.beans.factory.annotation.Qualifier;
  23. import org.springframework.jdbc.core.JdbcTemplate;
  24. import org.springframework.stereotype.Service;
  25. import org.springframework.util.StringUtils;
  27. import javax.sql.DataSource;
  28. import java.sql.ResultSet;
  29. import java.sql.SQLException;
  30. import java.sql.Statement;
  31. import java.util.ArrayList;
  32. import java.util.Collections;
  33. import java.util.List;
  35. @Slf4j
  36. @Service
  37. public class HiveServiceImpl implements HiveService { 
  39.     @Autowired
  40.     @Qualifier("hiveJdbcTemplate")
  41.     private JdbcTemplate hiveJdbcTemplate;
  43.     @Autowired
  44.     @Qualifier("hiveDruidDataSource")
  45.     private DataSource hiveDruidDataSource;
  47.     @Override
  48.     public Object select(String hql) { 
  49.         return hiveJdbcTemplate.queryForObject(hql, Object.class);
  50.     }
  52.     @Override
  53.     public List<String> listAllTables() { 
  54.         List<String> result = new ArrayList<>();
  55.         try { 
  56.             Statement statement = hiveDruidDataSource.getConnection().createStatement();
  57.             String sql = "show tables";
  58.             log.info("Running: " + sql);
  59.             ResultSet resultSet = statement.executeQuery(sql);
  60.             while (resultSet.next()) { 
  61.                 result.add(resultSet.getString(1));
  62.             }
  63.             return result;
  64.         } catch (SQLException throwables) { 
  65.             log.error(throwables.getMessage());
  66.         }
  68.         return Collections.emptyList();
  69.     }
  71.     @Override
  72.     public List<String> describeTable(String tableName) { 
  73.         if (StringUtils.isEmpty(tableName)){ 
  74.             return Collections.emptyList();
  75.         }
  76.         List<String> result = new ArrayList<>();
  77.         try { 
  78.             Statement statement = hiveDruidDataSource.getConnection().createStatement();
  79.             String sql = "describe " + tableName;
  80.             log.info("Running" + sql);
  81.             ResultSet resultSet = statement.executeQuery(sql);
  82.             while (resultSet.next()) { 
  83.                 result.add(resultSet.getString(1));
  84.             }
  85.             return result;
  86.         } catch (SQLException throwables) { 
  87.             log.error(throwables.getMessage());
  88.         }
  89.         return Collections.emptyList();
  90.     }
  92.     @Override
  93.     public List<String> selectFromTable(String tableName) { 
  94.         if (StringUtils.isEmpty(tableName)){ 
  95.             return Collections.emptyList();
  96.         }
  97.         List<String> result = new ArrayList<>();
  98.         try { 
  99.             Statement statement = hiveDruidDataSource.getConnection().createStatement();
  100.             String sql = "select * from " + tableName;
  101.             log.info("Running" + sql);
  102.             ResultSet resultSet = statement.executeQuery(sql);
  103.             int columnCount = resultSet.getMetaData().getColumnCount();
  104.             String str = null;
  105.             while (resultSet.next()) { 
  106.                 str = "";
  107.                 for (int i = 1; i < columnCount; i++) { 
  108.                     str += resultSet.getString(i) + " ";
  109.                 }
  110.                 str += resultSet.getString(columnCount);
  111.                 log.info(str);
  112.                 result.add(str);
  113.             }
  114.             return result;
  115.         } catch (SQLException throwables) { 
  116.             log.error(throwables.getMessage());
  117.         }
  118.         return Collections.emptyList();
  119.     }
  120. }

hive本身就是基于hadoop的MapReduce的一层封装，所以对于hive的操作都是查询和新增等操作，其他的api请参考jdbctemplate接口

源码地址:

WinterChenS/springboot-learning-experience

发表评论

表情：
评论列表 （有 0 条评论，331人围观）

还没有评论，来说两句吧...

相关阅读