Hive基本语法和使用

Hive 语法

建表语句

第一种常用新建原始表：

create [EXTERNAL] table vv_stat_fact
(
userid  string,
stat_date string,
tryvv int,
sucvv int,
ptime float
)
 PARTITIONED BY ( 非必选；创建分区表
  dt string)
clustered by (userid) into 3000 buckets // 非必选；分桶子
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'  // 必选;指定列之间的分隔符 
STORED AS rcfile   // 非必选；指定文件的读取格式，默认textfile格式
location '/testdata/'; //非必选；指定文件在hdfs上的存储路径，如果已经有文件，会自动加载 ，默认在hive的warehouse下

第二种关联建表

create table dianxin_as_S AS select * from dianxin_503 limit 10;

注意新建表不允许是外部表。select后面表需要是已经存在的表，建表同时会加载数据。会启动mapreduce任务去读取源表数据写入新表。

CREATE EXTERNAL TABLE IF NOT EXISTS dianxin_like LIKE dianxin_503;

第三种，创建分区表
分区表指的是在创建表时指定分区空间，实际上就是在hdfs上表的目录下再创建子目录。在使用数据时如果指定了需要访问的分区名称，则只会读取相应的分区，避免全表扫描，提高查询效率。

CREATE TABLE page_view(viewTime INT, ip STRING ) PARTITIONED BY (dt STRING, country STRING) ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\001'

加载数据

• 使用load data 命令
  从hdfs导入数据，路径可以是目录，会将目录下所有文件导入，但是文件格式必须一致。
  load data inpath ‘/test/’ into table dianxin_test;

• 从本地文件系统导入
  load data local inpath ‘/test/’ into table dianxin_test;

• 表对表加载：
  create table IF NOT EXISTS dianxin_test2 as select * from dianxin_test
  insert [overwrite] into table dianxin_test2 select * from dianxin_test;

注意：
1，如果建表语句没有指定存储路径，不管是外部表还是内部表，存储路径都是会默认在hive/warehouse/xx.db/表名的目录下。加载的数据也会移动到该表的存储目录下。注意是移动，移动，移动。不是复制
2，删除外部表，文件不会删除，对应目录也不会删除

Hive的DDL语句

• 创建数据库 create database xxxxx;
• 查看数据库 show databases；删除数据库 drop database tmp;
• 强制删除数据库：drop database tmp cascade;
• 查看表：SHOW TABLES；
• 查看表的元信息：
  desc test_table;
  describe extended test_table;
  describe formatted test_table;
• 查看建表语句：show create table table_XXX
• 重命名表：
  alter table test_table rename to new_table;
• 修改列数据类型：alter table lv_test change column colxx string;
• 增加、删除分区：
  alter table test_table add partition (pt=xxxx)
  alter table test_table drop if exists partition(…);

Hive的DML语句

Hive函数

常见的函数

• if函数 if（，，）
• case when 函数：case when 。。。end
• 日期函数：to_date…
• 字符串函数：concat，concat_ws
• 聚合函数：sum，count。。。
• null值判断：is null ，is not null

高级函数
窗口函数（开窗函数）：用户分组中开窗
row_number() 等
select * from (select name,date_time,row_number() over(partition by name order by cost desc) as rn from window_t)a where rn=1;
一般用于分组中求 TopN

Hive-自定义函数UDF

• UDF函数可以直接应用于select语句，对查询结构做格式化处理后，再输出内容。
• 编写UDF函数的时候需要注意一下几点：
  • 自定义UDF需要继承org.apache.hadoop.hive.ql.exec.UDF
  • 需要evaluate函数。
• 步骤
  • 把程序打包放到目标机器上去；
  • 进入hive客户端，添加jar包： add jar /usr/local/testdata/hive_UP.jar;
  • 创建临时函数：hive>CREATE TEMPORARY FUNCTION f_up as ‘hive_demo.hive_udf’;
• 查询HQL语句：
  select f_up(line) from wc_test;
  销毁临时函数：hive> DROP TEMPORARY FUNCTION f_up;
  注：UDF只能实现一进一出的操作，如果需要实现多进一出，则需要实现UDAF

Hive-jdbc的连接操作

• 首先开启 metastore：hive --service metastore
• 开启 hiveserver2：hive --service hiveserver2
• 添加maven依赖

 <dependency>
             <groupId>org.apache.hive</groupId>
             <artifactId>hive-jdbc</artifactId>
             <version>1.2.1/version>
 </dependency>

Java代码

package hive;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class hive_jdbc {
	public static void main(String[] args) throws Exception {
		Class.forName("org.apache.hive.jdbc.HiveDriver");
		Connection con = DriverManager.getConnection("jdbc:hive2://master:10000/zhangsan");
		Statement cs = con.createStatement();
		//查询sql，query
		ResultSet rs = cs.executeQuery("select * from zhangsan.air_id");
		//通常用于ddl操作
		//cs.execute("create table testxx");
		while(rs.next()) {
			String word = rs.getString(1);
			String count = rs.getString(2);
			System.out.println(word + ", " + count);
		}
		rs.close();
		cs.close();
		con.close();
	}
}