Hive學習筆記：Hive JDBC+Java API

環境：CentOS7

hive-1.1.0-cdh5.14.0

hadoop-2.6.0-cdh5.14.0

Hive JDBC配置與實作

修改$HIVE_HOIME/conf下的hive-site.xml，添加以下内容

<property>
        <name>hive.server2.thrift.port</name>
        <value>10000</value>
</property>
<property>
        <name>hive.server2.thrift.bind.host</name>
        <value>0.0.0.0</value>
</property>

設定Hiveserver2 Thrift的port和host（host設定成0.0.0.0，來接收未知來源的ip）

nohup啟動metastore和hiveserver2（當然是要先起hdfs和yarn）

nohup hive --service metastore > metastore.log 2>&1 &

nohup hive --service hiveserver2 > hiveserver2.log 2>&1 &

（可以寫在一個shell檔案裡，然後在測試環境要起hive的時候就可以偷懶了233）

jps看一下有兩個runjar那就說明沒有問題啦，或者看一下生成的日志資訊保證沒有報錯

然後使用beeline連接配接hive

[[email protected] bin]# beeline
Beeline version 1.1.0-cdh5.14.0 by Apache Hive
beeline> !connect jdbc:hive2://centos:10000
scan complete in 1ms
Connecting to jdbc:hive2://centos:10000
Enter username for jdbc:hive2://centos:10000: root
Enter password for jdbc:hive2://centos:10000: ******
Connected to: Apache Hive (version 1.1.0-cdh5.14.0)
Driver: Hive JDBC (version 1.1.0-cdh5.14.0)
Transaction isolation: TRANSACTION_REPEATABLE_READ
0: jdbc:hive2://centos:10000>

Java API 操作

建立一個Maven Project，這是用到的pom.xml，隻需要将<build>到</project>間的複制到自己的project，update一下maven project就可以了

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>06HiveJDBCJava</groupId>
  <artifactId>06HiveJDBCJava</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  
  <build> 
    <pluginManagement>
    	<plugins>
    		<!--This plugin's configuration is used to store Eclipse m2e settings only. It has no influence on the Maven build itself.-->
    		<plugin>
    			<groupId>org.eclipse.m2e</groupId>
    			<artifactId>lifecycle-mapping</artifactId>
    			<version>1.0.0</version>
    			<configuration>
    				<lifecycleMappingMetadata>
    					<pluginExecutions>
    						<pluginExecution>
    							<pluginExecutionFilter>
    								<groupId>
    									org.apache.maven.plugins
    								</groupId>
    								<artifactId>
    									maven-compiler-plugin
    								</artifactId>
    								<versionRange>[3.1,)</versionRange>
    								<goals>
    									<goal>compile</goal>
    								</goals>
    							</pluginExecutionFilter>
    							<action>
    								<ignore></ignore>
    							</action>
    						</pluginExecution>
    					</pluginExecutions>
    				</lifecycleMappingMetadata>
    			</configuration>
    		</plugin>
    	</plugins>
    </pluginManagement>
  </build>
  
  <repositories>
    <repository>
      <id>cloudera</id>
      <url>https://repository.cloudera.com/artifactory/cloudera-repos/</url>
    </repository>
    <repository>
      <id>maven</id>
      <url>http://central.maven.org/maven2/</url>
    </repository>
    <repository>
    <id>alimaven</id>  
  			<url>http://maven.aliyun.com/nexus/content/groups/public/</url>
    </repository>  
     </repositories>

<dependencies>
		<dependency>  
		    <groupId>org.apache.maven.plugins</groupId>  
		    <artifactId>maven-resources-plugin</artifactId>  
		    <version>2.5</version>  
		</dependency>  
	  <dependency>
		<groupId>org.apache.hadoop</groupId>
		<artifactId>hadoop-common</artifactId>
		<version>2.6.0-cdh5.14.0</version>
		</dependency>
		<dependency>
		<groupId>org.apache.hadoop</groupId>
		<artifactId>hadoop-hdfs</artifactId>
		<version>2.6.0-cdh5.14.0</version>
		</dependency>
		<dependency>
		<groupId>org.apache.hadoop</groupId>
		<artifactId>hadoop-maven-plugins</artifactId>
		<version>2.6.0-cdh5.14.0</version>
		</dependency>
		 <dependency>
            <groupId>org.apache.hadoop</groupId>
            <artifactId>hadoop-client</artifactId>
            <version>2.6.0-cdh5.14.0</version>
        </dependency>
		 <dependency>
		    <groupId>org.apache.hadoop</groupId>
		    <artifactId>hadoop-yarn-server-resourcemanager</artifactId>
		    <version>2.6.0-cdh5.14.0</version>
		 </dependency>
		
		 <dependency>
		    <groupId>org.apache.hadoop</groupId>
		    <artifactId>hadoop-yarn-server-nodemanager</artifactId>
		    <version>2.6.0-cdh5.14.0</version>
		 </dependency>
		        
		 <dependency>
		    		<groupId>org.apache.hadoop</groupId>
		    		<artifactId>hadoop-yarn-common</artifactId>
		   		<version>2.6.0-cdh5.14.0</version>
		 </dependency>
		 <dependency>
    <groupId>org.apache.hadoop</groupId>
    <artifactId>hadoop-mapreduce-client-app</artifactId>
    <version>2.6.0-cdh5.14.0</version>
		</dependency>
		 <dependency>
		    <groupId>org.apache.hadoop</groupId>
		    <artifactId>hadoop-mapreduce-client-common</artifactId>
		    <version>2.6.0-cdh5.14.0</version>
		</dependency>
		<dependency>
		    <groupId>org.apache.hadoop</groupId>
		    <artifactId>hadoop-mapreduce-client-core</artifactId>
		    <version>2.6.0-cdh5.14.0</version>
		</dependency>
		 <dependency>
		    <groupId>org.apache.hadoop</groupId>
		    <artifactId>hadoop-mapreduce-client-jobclient</artifactId>
		    <version>2.6.0-cdh5.14.0</version>
		</dependency>
		<dependency>
		     <groupId>org.apache.hadoop</groupId>
		     <artifactId>hadoop-yarn-api</artifactId>
		    <version>2.6.0-cdh5.14.0</version>
		 </dependency>
		<dependency>
		    <groupId>org.apache.hadoop</groupId>
		    <artifactId>hadoop-yarn-client</artifactId>
		    <version>2.6.0-cdh5.14.0</version>
		</dependency>
	<dependency>
        <groupId>org.apache.hive</groupId>
        <artifactId>hive-exec</artifactId>
        <version>1.1.0-cdh5.14.0</version>
     </dependency>
      <dependency>
    <groupId>org.apache.hive</groupId>
    <artifactId>hive-common</artifactId>
    <version>1.1.0-cdh5.14.0</version>
</dependency>
 <dependency>
    <groupId>org.apache.hive</groupId>
    <artifactId>hive-jdbc</artifactId>
    <version>1.1.0-cdh5.14.0</version>
</dependency>

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

<!-- https://mvnrepository.com/artifact/org.apache.hive/hive-exec -->
<dependency>
    <groupId>org.apache.hive</groupId>
    <artifactId>hive-exec</artifactId>
    <version>1.1.0-cdh5.14.0</version>
</dependency>
	  <dependency>
			<groupId>org.apache.mrunit</groupId>
			<artifactId>mrunit</artifactId>
			<version>1.1.0</version>
			<classifier>hadoop2</classifier>
			<scope>test</scope>
		</dependency>
		<dependency>
			<groupId>org.mockito</groupId>
			<artifactId>mockito-all</artifactId>
			<version>1.9.5</version>
			<scope>test</scope>
		</dependency>
		<dependency>
			<groupId>junit</groupId>
			<artifactId>junit</artifactId>
			<version>4.10</version>
			<scope>test</scope>
		</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>  
 </dependencies>
</project>

具體實作

package com.jdbc.test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class HiveJDBCTest 
{
	//驅動，URL，使用者名，密碼，資料庫
	public static final String HIVE_DRIVER="org.apache.hive.jdbc.HiveDriver";
	public static final String HIVE_URL="jdbc:hive2://centos:10000";
	public static final String USER_NAME="root";
	public static final String PASSWORD="123456";
	
	public static void main(String[] args) throws SQLException 
	{
		Connection conn=null;
		PreparedStatement pstmt=null,pstmt1=null;
		ResultSet rs=null;
		
		try {
			//加載驅動，建立連接配接
			Class.forName(HIVE_DRIVER);
			conn = DriverManager.getConnection(HIVE_URL, USER_NAME, PASSWORD);
			
			//使用PreparedStatement,這裡注意，查詢的時候使用的是executeQuery，而且會有結果集傳回其他的加載資料，創表創庫等操作用的是execute方法
			pstmt1=conn.prepareStatement("load data local inpath"+"/student.txt"+"overwrite into table student");
			pstmt1.execute();
			
			pstmt = conn.prepareStatement("select * from student");
			rs=pstmt.executeQuery();
			
			//周遊輸出每一行查詢結果，注意get方法對應的字段的類型
			while(rs.next()!=false)
			{
				System.out.println(rs.getInt(1)+" "+rs.getString(2)+" "+rs.getString(3)+" "+rs.getInt(4));
			}
			
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		//釋放資源
		rs.close();
		pstmt.close();
		pstmt1.close();
		conn.close();
		
	}
}

這裡值得一提的是，這裡使用了PreparedStatement，關于這個有一篇文章寫的很詳細，這裡轉個連結

http://www.cnblogs.com/zhizhuwang/p/3513372.html

Hive學習筆記：Hive JDBC+Java API

Hive JDBC配置與實作

Java API 操作

具體實作

繼續閱讀

關于Gradle配置的小結

Java小案例——随機數猜測随機數猜測

nginx location中斜線的位置的重要性

27 Best Free Eclipse Plug-ins for Java Developer to be ProductiveCode Quality PluginsText Editor PluginsDependency ManagementVersion Control Integration PluginsFramework Development Continuous Integration Related PluginsOther Utility Plugins

Java String.format方法的簡單使用

neo4j之cypher使用文檔

GitHub連夜封殺！這份阿裡 10W 字内部 Java 字面試手冊到底有多強？

spark/scala關于【資源檔案】加載方法概述外部檔案加載方案測試資源檔案打包入jar包中小結

mybatis_入門程式Mybatis入門

AOP程式設計_Android優雅權限架構(1)概念基礎，2021金三銀四前言正文大綱正文

Effective Java 8:通用程式設計

OOM三種類型

工廠模式-三種類型

【遞歸】高效率求2的n次幂

win10本地scala和spark安裝安裝scala安裝spark

scala (3) Function 和 Method