How to configure Hive in Springboot? This blog may help you!

Recently, I have just completed a project about big data, and the framework used in this project includes spring boot. Because it is an offline data analysis, Hive is also selected for component selection (Spark or HBase may be used for real-time ) This blog is about how to configure Hive in the spring boot project.

Change Pom file

After creating the project, open the pom.xml file and add the following content.

 <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <maven.compiler.source>1.7</maven.compiler.source>
        <maven.compiler.target>1.7</maven.compiler.target>
    </properties>

    <dependencies>


        <!-- Support web project
                       SpringBoot Simplifies project import coordinates
                       stay SpringBoot Just import starter starter,It will help you. N Multiple jar package

                       spring-boot-starter-web :  Contains Spring,Spring MVC,SpringBoot What is needed jar package

         <!- Support use jsp  -->
        <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>


        <!-- Support web project,integration SpringMVC -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>


        <!-- Support use jsp  -->
        <!--<dependency>-->
            <!--<groupId>org.apache.tomcat.embed</groupId>-->
            <!--<artifactId>tomcat-embed-jasper</artifactId>-->
        <!--</dependency>-->

        <!-- mybatis Support-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.2</version>
        </dependency>

        <!--currency mapper starter -->

        <dependency>
            <groupId>tk.mybatis</groupId>
            <artifactId>mapper-spring-boot-starter</artifactId>
            <version>2.0.2</version>
        </dependency>

        <!--Database connection pool initiator-->

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.10</version>
        </dependency>

        <!-- mysql drive -->

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.46</version>
        </dependency>

        <!-- jdbc starter -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>

        <!--    stay springboot Use in JSTL library     -->
        <dependency>
            <groupId>jstl</groupId>
            <artifactId>jstl</artifactId>
            <version>1.2</version>
        </dependency>
        <dependency>
            <groupId>taglibs</groupId>
            <artifactId>standard</artifactId>
            <version>1.1.2</version>
        </dependency>
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.2.5</version>
        </dependency>


        <!-- Add to hadoop rely on -->
        <dependency>
            <groupId>org.apache.hadoop</groupId>
            <artifactId>hadoop-common</artifactId>
            <version>2.6.0</version>
        </dependency>

        <dependency>
            <groupId>org.apache.hadoop</groupId>
            <artifactId>hadoop-mapreduce-client-core</artifactId>
            <version>2.6.0</version>
        </dependency>

        <dependency>
            <groupId>org.apache.hadoop</groupId>
            <artifactId>hadoop-mapreduce-client-common</artifactId>
            <version>2.6.0</version>
        </dependency>

        <dependency>
            <groupId>org.apache.hadoop</groupId>
            <artifactId>hadoop-hdfs</artifactId>
            <version>2.6.0</version>
        </dependency>

        <dependency>
            <groupId>jdk.tools</groupId>
            <artifactId>jdk.tools</artifactId>
            <version>1.8</version>
            <scope>system</scope>
            <systemPath>G:/jdk1.8/jdk/lib/tools.jar</systemPath>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-configuration-processor</artifactId>
            <optional>true</optional>
        </dependency>

        <!-- Add to hive rely on -->
        <dependency>
            <groupId>org.apache.hive</groupId>
            <artifactId>hive-jdbc</artifactId>
            <version>1.1.0</version>
            <exclusions>
                <exclusion>
                    <groupId>org.eclipse.jetty.aggregate</groupId>
                    <artifactId>*</artifactId>
                </exclusion>
            </exclusions>
        </dependency>


    </dependencies>

Add configuration file application.yml

Under the resources package of your parent module, add the configuration file application.yml

# Configure mybatis
mybatis:
  mapperLocations: classpath:/com/springboot/sixmonth/dao/mapper/**/*.xml

#Configure multiple data source properties (only two are configured here, which can be added by yourself if necessary)
spring:
  datasource:
    mysqlMain: #mysql master data source, which can be associated with mybatis
      type:
        com.alibaba.druid.pool.DruidDataSource
      url: jdbc:mysql://127.0.0.1:3306/youtobe_bigdata?useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&useSSL=true&rewriteBatchedStatements=true
      username: root
      password: root
      driver-class-name: com.mysql.jdbc.Driver
    hive:  # hive data source
      url: jdbc:hive2://192.168.100.100:10000/youtube
      type: com.alibaba.druid.pool.DruidDataSource
      username: root
      password: 123456
      driver-class-name: org.apache.hive.jdbc.HiveDriver
    commonConfig:  # Unified configuration of connection pool, applied to all data sources
      initialSize: 1
      minIdle: 1
      maxIdle: 5
      maxActive: 50
      maxWait: 10000
      timeBetweenEvictionRunsMillis: 10000
      minEvictableIdleTimeMillis: 300000
      validationQuery: select 'x'
      testWhileIdle: true
      testOnBorrow: false
      testOnReturn: false
      poolPreparedStatements: true
      maxOpenPreparedStatements: 20
      filters: stat

In the above configuration file, only hive and mysql are added. You can increase or decrease them according to your needs.

Add configuration class

I suggest adding it to the dao layer of the project, that is, the layer connected to the database.

The directory structure is as follows

HiveDruidConfig

@Configuration
@EnableConfigurationProperties({DataSourceProperties.class, DataSourceCommonProperties.class})//Inject the configuration class into the bean container for the ConfigurationProperties annotation class to take effect
public class HiveDruidConfig {

	    private static Logger logger = LoggerFactory.getLogger(HiveDruidConfig.class);

	    @Autowired
	    private DataSourceProperties dataSourceProperties;
	    
	    @Autowired
	    private DataSourceCommonProperties dataSourceCommonProperties;
 
	    @Bean("hiveDruidDataSource") //New bean instance
	    @Qualifier("hiveDruidDataSource")//Identification
	    public DataSource dataSource(){
	        DruidDataSource datasource = new DruidDataSource();

	        //Configure data source properties
	        datasource.setUrl(dataSourceProperties.getHive().get("url"));
	        datasource.setUsername(dataSourceProperties.getHive().get("username"));
	        datasource.setPassword(dataSourceProperties.getHive().get("password"));
	        datasource.setDriverClassName(dataSourceProperties.getHive().get("driver-class-name"));

	        //Configure unified properties
	        datasource.setInitialSize(dataSourceCommonProperties.getInitialSize());
	        datasource.setMinIdle(dataSourceCommonProperties.getMinIdle());
	        datasource.setMaxActive(dataSourceCommonProperties.getMaxActive());
	        datasource.setMaxWait(dataSourceCommonProperties.getMaxWait());
	        datasource.setTimeBetweenEvictionRunsMillis(dataSourceCommonProperties.getTimeBetweenEvictionRunsMillis());
	        datasource.setMinEvictableIdleTimeMillis(dataSourceCommonProperties.getMinEvictableIdleTimeMillis());
	        datasource.setValidationQuery(dataSourceCommonProperties.getValidationQuery());
	        datasource.setTestWhileIdle(dataSourceCommonProperties.isTestWhileIdle());
	        datasource.setTestOnBorrow(dataSourceCommonProperties.isTestOnBorrow());
	        datasource.setTestOnReturn(dataSourceCommonProperties.isTestOnReturn());
	        datasource.setPoolPreparedStatements(dataSourceCommonProperties.isPoolPreparedStatements());
	        try {
	            datasource.setFilters(dataSourceCommonProperties.getFilters());
	        } catch (SQLException e) {
	            logger.error("Druid configuration initialization filter error.", e);
	        }
	        return datasource;
	    }
	    
}

MysqlMainDruidConfig

@Configuration
@EnableConfigurationProperties({DataSourceProperties.class, DataSourceCommonProperties.class})//Inject the configuration class into the bean container for the ConfigurationProperties annotation class to take effect
public class MysqlMainDruidConfig {

	    private static Logger logger = LoggerFactory.getLogger(MysqlMainDruidConfig.class);

	    @Autowired
	    private DataSourceProperties dataSourceProperties;
	    
	    @Autowired
	    private DataSourceCommonProperties dataSourceCommonProperties;

	    @Primary //Indicates the primary data source. Only one primary data source can be identified. mybatis is connected to the default primary data source
	    @Bean("mysqlDruidDataSource") //New bean instance
	    @Qualifier("mysqlDruidDataSource")//Identification
	    public DataSource dataSource(){
	        DruidDataSource datasource = new DruidDataSource();

	        //Configure data source properties
	        datasource.setUrl(dataSourceProperties.getMysqlMain().get("url"));
	        datasource.setUsername(dataSourceProperties.getMysqlMain().get("username"));
	        datasource.setPassword(dataSourceProperties.getMysqlMain().get("password"));
	        datasource.setDriverClassName(dataSourceProperties.getMysqlMain().get("driver-class-name"));

	        //Configure unified properties
	        datasource.setInitialSize(dataSourceCommonProperties.getInitialSize());
	        datasource.setMinIdle(dataSourceCommonProperties.getMinIdle());
	        datasource.setMaxActive(dataSourceCommonProperties.getMaxActive());
	        datasource.setMaxWait(dataSourceCommonProperties.getMaxWait());
	        datasource.setTimeBetweenEvictionRunsMillis(dataSourceCommonProperties.getTimeBetweenEvictionRunsMillis());
	        datasource.setMinEvictableIdleTimeMillis(dataSourceCommonProperties.getMinEvictableIdleTimeMillis());
	        datasource.setValidationQuery(dataSourceCommonProperties.getValidationQuery());
	        datasource.setTestWhileIdle(dataSourceCommonProperties.isTestWhileIdle());
	        datasource.setTestOnBorrow(dataSourceCommonProperties.isTestOnBorrow());
	        datasource.setTestOnReturn(dataSourceCommonProperties.isTestOnReturn());
	        datasource.setPoolPreparedStatements(dataSourceCommonProperties.isPoolPreparedStatements());
	        try {
	            datasource.setFilters(dataSourceCommonProperties.getFilters());
	        } catch (SQLException e) {
	            logger.error("Druid configuration initialization filter error.", e);
	        }
	        return datasource;
	    }
	    
}

DataSourceCommonProperties

@ConfigurationProperties(prefix = "spring.datasource.commonconfig", ignoreUnknownFields = false)
public class DataSourceCommonProperties {
//	final static String DS = "spring.datasource.commonConfig";
 
	private int initialSize = 10;
	private int minIdle;
	private int maxIdle;
	private int maxActive;
	private int maxWait;
	private int timeBetweenEvictionRunsMillis;
	private int minEvictableIdleTimeMillis;
	private String validationQuery;
	private boolean testWhileIdle;
	private boolean testOnBorrow;
	private boolean testOnReturn;
	private boolean poolPreparedStatements;
	private int maxOpenPreparedStatements;
	private String filters;
 
	private String mapperLocations;
	private String typeAliasPackage;
 
 
	//In order to save space, set and get methods are omitted here. You can add them by yourself
//
//	public static String getDS() {
//		return DS;
//	}

	public int getInitialSize() {
		return initialSize;
	}

	public void setInitialSize(int initialSize) {
		this.initialSize = initialSize;
	}

	public int getMinIdle() {
		return minIdle;
	}

	public void setMinIdle(int minIdle) {
		this.minIdle = minIdle;
	}

	public int getMaxIdle() {
		return maxIdle;
	}

	public void setMaxIdle(int maxIdle) {
		this.maxIdle = maxIdle;
	}

	public int getMaxActive() {
		return maxActive;
	}

	public void setMaxActive(int maxActive) {
		this.maxActive = maxActive;
	}

	public int getMaxWait() {
		return maxWait;
	}

	public void setMaxWait(int maxWait) {
		this.maxWait = maxWait;
	}

	public int getTimeBetweenEvictionRunsMillis() {
		return timeBetweenEvictionRunsMillis;
	}

	public void setTimeBetweenEvictionRunsMillis(int timeBetweenEvictionRunsMillis) {
		this.timeBetweenEvictionRunsMillis = timeBetweenEvictionRunsMillis;
	}

	public int getMinEvictableIdleTimeMillis() {
		return minEvictableIdleTimeMillis;
	}

	public void setMinEvictableIdleTimeMillis(int minEvictableIdleTimeMillis) {
		this.minEvictableIdleTimeMillis = minEvictableIdleTimeMillis;
	}

	public String getValidationQuery() {
		return validationQuery;
	}

	public void setValidationQuery(String validationQuery) {
		this.validationQuery = validationQuery;
	}

	public boolean isTestWhileIdle() {
		return testWhileIdle;
	}

	public void setTestWhileIdle(boolean testWhileIdle) {
		this.testWhileIdle = testWhileIdle;
	}

	public boolean isTestOnBorrow() {
		return testOnBorrow;
	}

	public void setTestOnBorrow(boolean testOnBorrow) {
		this.testOnBorrow = testOnBorrow;
	}

	public boolean isTestOnReturn() {
		return testOnReturn;
	}

	public void setTestOnReturn(boolean testOnReturn) {
		this.testOnReturn = testOnReturn;
	}

	public boolean isPoolPreparedStatements() {
		return poolPreparedStatements;
	}

	public void setPoolPreparedStatements(boolean poolPreparedStatements) {
		this.poolPreparedStatements = poolPreparedStatements;
	}

	public int getMaxOpenPreparedStatements() {
		return maxOpenPreparedStatements;
	}

	public void setMaxOpenPreparedStatements(int maxOpenPreparedStatements) {
		this.maxOpenPreparedStatements = maxOpenPreparedStatements;
	}

	public String getFilters() {
		return filters;
	}

	public void setFilters(String filters) {
		this.filters = filters;
	}

	public String getMapperLocations() {
		return mapperLocations;
	}

	public void setMapperLocations(String mapperLocations) {
		this.mapperLocations = mapperLocations;
	}

	public String getTypeAliasPackage() {
		return typeAliasPackage;
	}

	public void setTypeAliasPackage(String typeAliasPackage) {
		this.typeAliasPackage = typeAliasPackage;
	}
}

DataSourceProperties

@ConfigurationProperties(prefix = DataSourceProperties.DS, ignoreUnknownFields = false)
public class DataSourceProperties {
	final static String DS = "spring.datasource";

	private Map<String,String> mysqlMain;
	
	private Map<String,String> hive;
	
	private Map<String,String> commonConfig;
	
    
	//In order to save space, set and get methods are omitted here. You can add them by yourself


	public static String getDS() {
		return DS;
	}

	public Map<String, String> getMysqlMain() {
		return mysqlMain;
	}

	public void setMysqlMain(Map<String, String> mysqlMain) {
		this.mysqlMain = mysqlMain;
	}

	public Map<String, String> getHive() {
		return hive;
	}

	public void setHive(Map<String, String> hive) {
		this.hive = hive;
	}

	public Map<String, String> getCommonConfig() {
		return commonConfig;
	}

	public void setCommonConfig(Map<String, String> commonConfig) {
		this.commonConfig = commonConfig;
	}
}

Encapsulate Hive query code

Create a class, such as TestHiveDao, write a method to encapsulate the code that we return the query result collection through the variable of String type (sql).

/**
	 * The general tool class returns the result of query in hive through the specified sql passed in, and returns the result set
	 * @param sql    sql Sentence
	 * @return  Result set
	 * @throws SQLException
	 */
	public List<String> splicer(String sql) throws SQLException {

		// Create a new collection and save the final result
		ArrayList<String> list = new ArrayList<>();

		Statement statement = druidDataSource.getConnection().createStatement();

		// Query statement
		ResultSet res = statement.executeQuery(sql);

		// Get the number of query result columns
		int count = res.getMetaData().getColumnCount();

		System.out.println("count:"+count);

		String str = null;

		while (res.next()) {
			str = "";

			for (int i = 1; i < count; i++) {

                // Information about a row of data, separated by spaces between each field
				str += res.getString(i) + "\t";

			}
			// Store the results of a row of queries into the collection
			str += res.getString(count);

			// Add to set
			list.add(str);

		}

		return list;

	}

Then when we want to use hive for hql query, we can call this method and get the desired result easily.

The result returned is similar to what is shown in the following console

By passing in an hql statement of string type, the returned collection contains every element of the queried row. Is it easy~

This is the end of the sharing. All the benefited friends remember to leave a comment. Those who are interested in big data technology can pay attention to small bacteria ~ (✪ω✪)

188 original articles published, 1511 praised, 400000 visitors+
Private letter follow

Tags: Spring hive Hadoop Mybatis

Posted on Wed, 12 Feb 2020 10:19:54 -0800 by mrmom