本文共 7297 字，大约阅读时间需要 24 分钟。

目录

Hadoop_Hive

1.Java操作Hive

代码

package com.lius.hive.javaOperation;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.sql.SQLException;import java.sql.Statement;import org.apache.curator.RetryPolicy;import org.apache.log4j.Logger;/** * hive-Client * @author Administrator * */public class hiveJdbcCli {	private static String driverName = "org.apache.hive.jdbc.HiveDriver";	private static String url = "jdbc:hive2://hadoop1:10000";	private static String user = "root";	private static String password ="123456";	private static String sql = "";	private static Connection conn;	private static Statement stmt;	private static ResultSet rs;//	private static final Logger log = Logger.getLogger(hiveJdbcCli.class);	private static String tableName = "t_hive3";	public static void main(String[] args) throws SQLException {				try {			conn = getConn();			//第一步.存在就先删除表			dropTable(conn);			//第二步.不存在就创建			createTable(conn,tableName);			//第三步.查看创建的表			showTables(conn,tableName);			//第四步.执行describe table操作			describeTables(conn,tableName);			//第五步.执行load data into table操作//			loadData(conn,tableName,"/usr/lius/hive/nums/20200105/nums.txt");			loadLocalData(conn,tableName,"/root/nums.txt");			//第六步.执行select * query操作			selectData(conn,tableName);			//第七步.执行regular hive query统计操作			countData(conn,tableName);		}catch (Exception e) {			// TODO: handle exception			e.printStackTrace();		}finally {			if(conn!=null) {				conn.close();			}					}		}	/**	 * 第七步.执行regular hive query统计操作	 * @param conn	 * @param tableName	 * @throws SQLException	 */private static void countData(Connection conn, String tableName) throws SQLException {		// TODO Auto-generated method stub		sql = String.format("select count(1) from %s  ", tableName);		rs = executeQuery();		printResultSet(rs);}/** * 第六步.执行select * query操作 * @param conn * @param tableName * @throws SQLException */private static void selectData(Connection conn, String tableName) throws SQLException {		// TODO Auto-generated method stub		sql = String.format("select * from %s", tableName);		rs = executeQuery();		printResultSet(rs);}/** * 第五步.执行load data into table操作 * @param conn * @param tableName * @param hdfPath * @throws SQLException */private static void loadData(Connection conn, String tableName,String hdfPath) throws SQLException {		// TODO Auto-generated method stub		sql = String.format("load data  inpath '%s' into table %s",hdfPath,tableName);		execute();	}/** * 第五步.执行load data into table操作 * @param conn * @param tableName * @param localPath * @throws SQLException */private static void loadLocalData(Connection conn, String tableName,String localPath) throws SQLException {	// TODO Auto-generated method stub//	sql = String.format("load data  inpath '%s' into table %s",hdfPath,tableName);	sql = String.format("load data local inpath '%s' into table %s",localPath,tableName);	execute();}/** * 第四步.执行describe table操作 * @param conn * @param tableName * @throws SQLException */private static void describeTables(Connection conn, String tableName) throws SQLException {		// TODO Auto-generated method stub		sql = String.format("describe  %s", tableName);		rs = executeQuery();		printResultSet(rs);}/** * 第三步.查看创建的表 * @param conn * @param tableName * @throws SQLException */private static void showTables(Connection conn, String tableName) throws SQLException {		// TODO Auto-generated method stub		sql = String.format("show tables '%s'", tableName);		rs = executeQuery();		printResultSet(rs);	}/** * 第二步.不存在就创建 * @param conn * @param tableName * @throws SQLException */private static void createTable(Connection conn, String tableName) throws SQLException {		// TODO Auto-generated method stub	sql = String.format("create table %s(a int,b int,c int) row format delimited fields TERMINATED by '.'", tableName);	execute();}/** * 第一步.存在就先删除表 * @param conn * @return * @throws SQLException */private static String dropTable(Connection conn) throws SQLException {		// TODO Auto-generated method stub	sql = String.format("drop table if exists %s", "t_hive3");	execute();	return tableName;	}/** * 执行SQL不返回结果 * @throws SQLException */private static void execute() throws SQLException {	stmt = conn.createStatement();	printSQL();	stmt.execute(sql);	stmt.close();}/** * 执行SQL返回结果 * @return * @throws SQLException */private static ResultSet executeQuery() throws SQLException {	stmt = conn.createStatement();	printSQL();	rs = stmt.executeQuery(sql);	return rs;}/** * 打印返回结果 * @param rs * @throws SQLException */private static void printResultSet(ResultSet rs) throws SQLException {		ResultSetMetaData md = rs.getMetaData();	int counts = md.getColumnCount();	int x =1;	while(rs.next()){		StringBuffer sbf = new StringBuffer();		for(int i =1;i<=counts;i++) {			sbf.append(" "+rs.getObject(md.getColumnName(i)));		}		System.out.println("row"+(x++)+" ==> "+sbf);	};	stmt.close();}/** * 打印SQL */private static void printSQL() {	System.out.println("Running SQL:"+sql);}/** * 获取连接 * @return * @throws ClassNotFoundException * @throws SQLException */private static Connection getConn() throws ClassNotFoundException, SQLException {	// TODO Auto-generated method stub	Class.forName(driverName);	Connection conn = DriverManager.getConnection(url, user, password);	return conn;}}

打印结果

 SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".

SLF4J: Defaulting to no-operation (NOP) logger implementation

SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details.

Running SQL:drop table if exists t_hive3

Running SQL:create table t_hive3(a int,b int,c int) row format delimited fields TERMINATED by '.'

Running SQL:show tables 't_hive3'

row1 ==>  t_hive3

Running SQL:describe  t_hive3

row1 ==>  a int 

row2 ==>  b int 

row3 ==>  c int 

Running SQL:load data local inpath '/root/nums.txt' into table t_hive3

Running SQL:select * from t_hive3

row1 ==>  1 2 3

row2 ==>  4 5 6

row3 ==>  7 8 9

Running SQL:select count(1) from t_hive3  

row1 ==>  3

2. hive笔记

--建表语句

create external table 

people

(

id int,

sex string,

name string

) 

partitioned by (logdate string,hour string) 

row format delimited fields terminated by ','

||

create table t_hive(a int,b int,c int) row format delimited fields terminited by '.';

--追加数据

    --hdfs

             alter table people add if  not exists partition (logdate=20200105,hour=00) location '/usr/lius/hive/people/20200105'

            ||

            load data inpath '/usr/lius/hive/nums/20200105/nums.txt' overwrite into table t_hive;    

    --本地导入数据

            load data local inpath '../path' overwrite into table t_hive;

--条件查询

select * from people where logdate = '20200105';

--UDF函数

class xxx extends UDF{

public string evaluate(String name,String sex){...}

} 

add jar xx.jar;

create temporary function xxx as 'com.lius.hive.helloUDF';

select helloworld(people.sex,people.name) from people;

--查询结果保存到本地

insert overwrite local directory '../path'  select * from people;

--正则匹配表名

show tables '*t*';

--增加字段

alter table t_hive add columns(d int);

--获取表结构描述

describe t_hive;

--重命名表名

alter table t_hive t_hive to t_hive1;

--创建表并从其他表导入数据

create table t_hive2 as select * from t_hive;

--复制表结构不导入数据

create table t_hive3 like t_hive;

--从其他表导入数据

insert overwrite table t_hive3 select * from t_hive2;

--hive查询HiveQL

from (select b,c as c2 from t_hive3) t select t.b,t.c2 limit 2;

select * from t_hive3 limit 2;