Hive基础讲解

一、Hive背景介绍

Hive最初是Facebook为了满足对海量社交网络数据的管理和机器学习的需求而产生和发展的。马云在退休的时候说互联网现在进入了大数据时代，大数据是现在互联网的趋势，而hadoop就是大数据时代里的核心技术，但是hadoop的mapreduce操作专业性太强，所以facebook在这些基础上开发了hive框架，毕竟世界上会sql的人比会java的人多的多，hive可以说是学习hadoop相关技术的一个突破口，那些志立于投身hadoop技术开发的童鞋们，可以先从hive开始哦。那么，hive是什么呢?

二、Hive简介

简单的说：hive是基于hadoop的数据仓库。

那么为什么说hive是基于Hadoop的呢？

之所以说hive是构建在Hadoop之上的数据仓库，简单的说是因为：

①数据存储在hdfs上

②数据计算用mapreduce

下面我们来深入分析一下：

Hive是一种建立在Hadoop文件系统上的数据仓库架构，并对存储在HDFS中的数据进行分析和管理；它可以将结构化的数据文件映射为一张数据库表，并提供完整的 SQL 查询功能，可以将 SQL 语句转换为 MapReduce 任务进行运行，通过自己的 SQL 去 查询分析需要的内容，这套 SQL 简称 Hive SQL（HQL），使不熟悉 MapReduce 的用户也能很方便地利用 SQL 语言对数据进行查询、汇总、分析。同时，这个语言也允许熟悉 MapReduce 开发者们开发自定义的mappers和reducers来处理内建的mappers和reducers无法完成的复杂的分析工作。Hive还允许用户编写自己定义的函数UDF，用来在查询中使用。Hive中有3种UDF：User Defined Functions（UDF）、User Defined Aggregation Functions（UDAF）、User Defined Table Generating Functions（UDTF）。也就是说对存储在HDFS中的数据进行分析和管理，我们不想使用手工，我们建立一个工具吧，那么这个工具就可以是hive。

三、Hive常见的应用场景

(1)日志分析：大部分互联网公司使用hive进行日志分析，包括百度、淘宝等。

1)统计网站一个时间段内的pv、uv

2)多维度数据分析

(2)海量结构化数据离线分析

四、Hive的特点（优缺点）

（一）hive的优点

(1)简单容易上手：提供了类SQL查询语言HQL

(2)可扩展：为超大数据集设计了计算/扩展能力（MR作为计算引擎，HDFS作为存储系统）

一般情况下不需要重启服务Hive可以自由的扩展集群的规模。

(3)提供统一的元数据管理

(4)延展性：Hive支持用户自定义函数，用户可以根据自己的需求来实现自己的函数

(5)容错：良好的容错性，节点出现问题SQL仍可完成执行

（二）hive的缺点（局限性）

(1)hive的HQL表达能力有限

1)迭代式算法无法表达，比如pagerank

2)数据挖掘方面，比如kmeans

(2)hive的效率比较低

1)hive自动生成的mapreduce作业，通常情况下不够智能化

2)hive调优比较困难，粒度较粗

3)hive可控性差

五、Hive 工作原理

Hive 工作原理如下图所示。

Hive构建在Hadoop之上

（1）HQL中对查询语句的解释、优化、生成查询计划是由Hive完成的

（2）所有的数据都是存储在Hadoop中

（3）查询计划被转化为MapReduce任务，在Hadoop中执行（有些查询没有MR任务，如：select * from table）

（4）Hadoop和Hive都是用UTF-8编码的

Hive编译器的组成：

Hive编译流程如下

六、Hive和数据库的异同

由于Hive采用了SQL的查询语言HQL，因此很容易将Hive理解为数据库。其实从结构上来看，Hive和数据库除了拥有类似的查询语言，再无类似之处。数据库可以用在Online的应用中，但是Hive是为数据仓库而设计的，清楚这一点，有助于从应用角度理解Hive的特性。

Hive和数据库的比较如下表：

MapReduce 开发人员可以把自己写的 Mapper 和 Reducer 作为插件支持 Hive 做更复杂的数据分析。 它与关系型数据库的 SQL 略有不同，但支持了绝大多数的语句（如 DDL、DML）以及常见的聚合函数、连接查询、条件查询等操作。

Hive 不适合用于联机(online) 事务处理，也不提供实时查询功能。它最适合应用在基于大量不可变数据的批处理作业。Hive 的特点是可 伸缩（在Hadoop 的集群上动态的添加设备），可扩展、容错、输入格式的松散耦合。Hive 的入口是DRIVER ，执行的 SQL 语句首先提交到 DRIVER 驱动，然后调用 COMPILER 解释驱动， 最终解释成 MapReduce 任务执行，最后将结果返回。

七、Hive 数据类型

Hive 提供了基本数据类型和复杂数据类型，复杂数据类型是 Java 语言所不具有的。本课程介绍 Hive 的两种数据类型以及数据类型之间的转换。

（一）基本数据类型

由上表我们看到hive不支持日期类型，在hive里日期都是用字符串来表示的，而常用的日期格式转化操作则是通过自定义函数进行操作。

hive是用java开发的，hive里的基本数据类型和java的基本数据类型也是一一对应的，除了string类型。有符号的整数类型：TINYINT、SMALLINT、INT和BIGINT分别等价于java的byte、short、int和long原子类型，它们分别为1字节、2字节、4字节和8字节有符号整数。Hive的浮点数据类型FLOAT和DOUBLE,对应于java的基本类型float和double类型。而hive的BOOLEAN类型相当于java的基本数据类型boolean。

对于hive的String类型相当于数据库的varchar类型，该类型是一个可变的字符串，不过它不能声明其中最多能存储多少个字符，理论上它可以存储2GB的字符数。

（二）复杂数据类型

Hive 有三种复杂数据类型 ARRAY、MAP 和 STRUCT。ARRAY 和 MAP 与 Java 中的 Array 和 Map 类似，而STRUCT 与 C语言中的 Struct 类似，它封装了一个命名字段集合，复杂数据类型允许任意层次的嵌套。

复杂数据类型的声明必须使用尖括号指明其中数据字段的类型。定义三列，每列对应一种复杂的数据类型，如下所示。

CREATE TABLE complex(
col1 ARRAY< INT>,
col2 MAP< STRING,INT>,
col3 STRUCT< a:STRING,b:INT,c:DOUBLE>
)

（三）类型转化

Hive 的原子数据类型是可以进行隐式转换的，类似于 Java 的类型转换，例如某表达式使用 INT 类型，TINYINT 会自动转换为 INT 类型， 但是 Hive 不会进行反向转化，例如，某表达式使用 TINYINT 类型，INT 不会自动转换为 TINYINT 类型，它会返回错误，除非使用 CAST 操作。

（1）隐式类型转换规则如下。

1）、任何整数类型都可以隐式地转换为一个范围更广的类型，如 TINYINT 可以转换成 INT，INT 可以转换成 BIGINT。

2）、所有整数类型、FLOAT 和 String 类型都可以隐式地转换成 DOUBLE。

3）、TINYINT、SMALLINT、INT 都可以转换为 FLOAT。

4）、BOOLEAN 类型不可以转换为任何其它的类型。

（2）可以使用 CAST 操作显示进行数据类型转换，例如 CAST('1' AS INT) 将把字符串'1' 转换成整数 1；如果强制类型转换失败，如执行 CAST('X' AS INT)，表达式返回空值 NULL。

八、Hive 安装

Hive 安装依赖 Hadoop 的集群，它是运行在 Hadoop 的基础上。 所以在安装 Hive 之前，保证 Hadoop 集群能够成功运行。Hive的安装详细流程如下所示。

1、 下载Hive

Hive是Apache 的一个顶级开源项目，我们可以直接到官网下载需要的版本即可。课程中我们使用的是apache-hive-1.0.0-bin.tar.gz版本，大家可以点击下载

2、把Hive安装包apache-hive-1.0.0-bin.tar.gz移动到/home/hadoop/app/目录下并解压，然后将文件名称改为 hive。

[hadoop@djt01 app]$ tar -zxvf apache-hive-1.0.0-bin.tar.gz
[hadoop@djt01 app]$ mv apache-hive-1.0.0-bin hive

3、 添加hive环境变量

在/etc/profile文件中增加如下内容：

[root@djt01 ~]$ vi /etc/profile
HIVE_HOME=/home/hadoop/app/hive
PATH=$JAVA_HOME/bin:$HADOOP_HOME/bin:$HIVE_HOME/bin:$PATH
export HIVE_HOME

保存退出后，通过source命令使配置文件生效

[root@djt01 ~]$ source /etc/profile

4、 启动 Hive

此时切换用户至 hadoop 用户，在命令行输入“hive”命令进行测试。

[hadoop@djt01]$ hive
hive>

1) 测试 Hive 是否安装成功。

hive> show tables;
OK
Time taken: 0.043 seconds

2) 创建表。

hive> create table test_table (id int ,name string,no int);
OK
Time taken: 0.5 seconds

3) 查询表。

hive> select * from test_table;
OK
Time taken: 0.953 seconds

如果创建表和查询表操作没有出错，就说明 Hive 安装成功。

九、Hive 架构

下面是Hive的架构图。

Hive的体系结构可以分为以下几部分

（1）用户接口主要有三个：CLI，Client 和 WUI。其中最常用的是CLI，Cli启动的时候，会同时启动一个Hive副本。Client是Hive的客户端，用户连接至Hive Server。在启动 Client模式的时候，需要指出Hive Server所在节点，并且在该节点启动Hive Server。 WUI是通过浏览器访问Hive。

（2）Hive将元数据存储在数据库中，如mysql、derby。Hive中的元数据包括表的名字，表的列和分区及其属性，表的属性（是否为外部表等），表的数据所在目录等。

（3）解释器、编译器、优化器完成HQL查询语句从词法分析、语法分析、编译、优化以及查询计划的生成。生成的查询计划存储在HDFS中，并在随后有MapReduce调用执行。

（4）Hive的数据存储在HDFS中，大部分的查询、计算由MapReduce完成（包含*的查询，比如select * from tbl不会生成MapRedcue任务）。

（一）用户接口

Hive 对外提供了三种服务模式，即 Hive 命令行模式（CLI），Hive 的 Web 模式（WUI），Hive 的远程服务（Client）。下面介绍这些服务的用法。

1、 Hive 命令行模式

Hive 命令行模式启动有两种方式。执行这条命令的前提是要配置 Hive 的环境变量。

1) 进入 /home/hadoop/app/hive 目录，执行如下命令。

./hive

2) 直接执行命令。

hive --service cli

Hive 命令行模式用于 Linux 平台命令行查询，查询语句基本跟 MySQL 查询语句类似，运行结果如下所示。

[hadoop@djt01 hive]$ hive
hive> show tables;
OK
stock
stock_partition
tst
Time taken: 1.088 seconds, Fetched: 3 row(s)
hive> select * from tst;
OK
Time taken: 0.934 seconds
hive> exit;
[hadoop@djt01 hive]$

2、Hive Web 模式

Hive Web 界面的启动命令如下。

hive --service hwi

通过浏览器访问 Hive，默认端口为 9999。

3、 Hive 的远程服务

远程服务（默认端口号 10000）启动方式命令如下，“nohup...&” 是 Linux 命令，表示命令在后台运行。

nohup hive --service hiveserver &//在Hive 0.11.0版本之前，只有HiveServer服务可用
nohup hive --service hiveserver2 &//在Hive 0.11.0版本之后，提供了HiveServer2服务

Hive 远程服务通过 JDBC 等访问来连接 Hive ，这是程序员最需要的方式。

本课程我们安装的是hive1.0版本，所以启动 hive service 命令如下。

hive --service hiveserver2 &//默认端口10000
hive --service hiveserver2 --hiveconf hive.server2.thrift.port 10002 &//可以通过命令行直接将端口号改为10002

hive的远程服务端口号也可以在hive-default.xml文件中配置，修改hive.server2.thrift.port对应的值即可。

< property>
< name>hive.server2.thrift.port< /name>
< value>10000< /value>
< description>Port number of HiveServer2 Thrift interface when hive.server2.transport.mode is 'binary'.< /description>
< /property>

Hive 的 JDBC 连接和 MySQL 类似，如下所示。

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class HiveJdbcClient {
private static String driverName = "org.apache.hive.jdbc.HiveDriver";//hive驱动名称 hive0.11.0之后的版本
//private static String driverName = "org.apache.hadoop.hive.jdbc.HiveDriver";//hive驱动名称 hive0.11.0之前的版本
public static void main(String[] args) throws SQLException {try{
Class.forName(driverName);
}catch(ClassNotFoundException e){
e.printStackTrace();
System.exit(1);
}
//第一个参数：jdbc:hive://djt01:10000/default 连接hive2服务的连接地址
//第二个参数：hadoop 对HDFS有操作权限的用户
//第三个参数：hive 用户密码 在非安全模式下，指定一个用户运行查询，忽略密码
Connection con = DriverManager.getConnection("jdbc:hive://djt01:10000/default", "hadoop", "");
System.out.print(con.getClientInfo());
}
}

（二）元数据存储。

Hive将元数据存储在RDBMS中，有三种模式可以连接到数据库：

（1） 单用户模式。此模式连接到一个In-memory 的数据库Derby，一般用于Unit Test。

（2）多用户模式。通过网络连接到一个数据库中，是最经常使用到的模式。

（3） 远程服务器模式。用于非Java客户端访问元数据库，在服务器端启动MetaStoreServer，客户端利用Thrift协议通过MetaStoreServer访问元数据库。

对于数据存储，Hive没有专门的数据存储格式，也没有为数据建立索引，用户可以非常自由的组织Hive中的表，只需要在创建表的时候告诉Hive数据中的列分隔符和行分隔符，Hive就可以解析数据。Hive中所有的数据都存储在HDFS中，存储结构主要包括数据库、文件、表和视图。Hive中包含以下数据模型：Table内部表，External Table外部表，Partition分区，Bucket桶。Hive默认可以直接加载文本文件，还支持sequence file 、RCFile。

Hive 将元数据存储在 RDBMS 中，一般常用 MySQL 和 Derby。默认情况下，Hive 元数据保存在内嵌的 Derby 数据库中，只能允许一个会话连接，只适合简单的测试。实际生产环境中不适用， 为了支持多用户会话，则需要一个独立的元数据库，使用 MySQL 作为元数据库，Hive 内部对 MySQL 提供了很好的支持，配置一个独立的元数据库需要增加以下步骤。

本课程用的是 centos 系统。

1) 在线安装 mysql 数据库。

[root@djt01 app]# yum install mysql-server
Loaded plugins: fastestmirror
Determining fastest mirrors
* base: mirrors.yun-idc.com
* extras: mirrors.btte.net
* updates: mirrors.163.com

2) 启动 mysql 服务。

[root@djt01 app]# service mysqld start
Initializing MySQL database: Installing MySQL system tables...
OK
Filling help tables...
OKTo start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

3) 设置 mysql 的 root 密码。

MySQL在刚刚被安装的时候，它的 root 用户是没有被设置密码的。首先来设置 MySQL 的 root 密码。

[root@djt01 app]# mysql -u root -p
Enter password: //默认密码为空，输入后回车即可
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.73 Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
mysql>set password for root@localhost=password('root'); 密码设置为root

4) 为 Hive 建立相应的 Mysql 账户，并赋予足够的权限。

[root@djt01 app]# mysql -u root -p root
mysql>create user 'hive' identified by 'hive'; //创建一个账号：用户名为hive，密码为hive
mysql> grant all on *.* to 'hive'@'djt01' identified by 'hive'; //将权限授予host为djt01的hive用户
Query OK, 0 rows affected (0.00 sec)mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

如果 hive 账户无法登陆。为hive@djt01 设置密码。

[root@djt01 ~]#mysql -u root -p root
mysql>set password for hive@djt01=password('hive');

5) 建立 Hive 专用的元数据库，记得用刚才创建的 “hive” 账号登录，命令如下。

[root@djt01 ~]#mysql -u hive -p //用hive用户登录，密码hive
Enter password:
mysql> create database hive; //创建数据库的名称为hive
Query OK, 1 row affected (0.00 sec)

6) 找到Hive安装目录 conf/下的 hive-site.xml文件，修改以下几个属性。

[hadoop@djt01 conf]$ vi hive-site.xml
< property>
< name>javax.jdo.option.ConnectionDriverName< /name>
< value>com.mysql.jdbc.Driver< /value>
< description>Driver class name for a JDBC metastore< /description>
< /property>< property>
< name>javax.jdo.option.ConnectionURL< /name>
< value>jdbc:mysql://djt01:3306/hive?characterEncoding=UTF-8< /value>
< description>JDBC connect string for a JDBC metastore< /description>
< /property>< property>
< name>javax.jdo.option.ConnectionUserName< /name>
< value>hive< /value>
< description>Username to use against metastore database< /description>
< /property>< property>
< name>javax.jdo.option.ConnectionPassword< /name>
< value>hive< /value>
< description>password to use against metastore database< /description>
< /property>

如果conf/目录下没有 hive-site.xml文件，则需要拷贝一个名为hive-site.xml的文件。

[hadoop@djt01 conf]$ cp hive-default.xml.template hive-site.xml

7)将mysql-connector-java-5.1.21.jar驱动包，拷贝到 $HIVE_HOME/lib 目录下。可点击下载 mysql驱动包

[hadoop@djt01 lib]#rz //回车，选择已经下载好的mysql驱动包即可
[hadoop@djt01 lib]$ ls
mysql-connector-java-5.1.21.jar

8) 启动 Hive Shell，测试运行。

[hadoop@djt01 hive]$ hive
hive> show databases;

hive切换到mysql元数据库之后，hive启动时如果遇到以下错误：

Exception in thread "main"java.lang.RuntimeException: java.lang.IllegalArgumentException:java.net.URISyntaxException: Relative path in absolute URI:${system:java.io.tmpdir%7D/$%7Bsystem:user.name%7D

解决方案：

在hive 安装目录下，创建一个临时的IO文件iotmp

[hadoop@djt01 hive]$ mkdir iotmp
[hadoop@djt01 hive]$ ls
bin derby.log hcatalog lib metastore_db README.txt scripts
conf examples iotmp LICENSE NOTICE RELEASE_NOTES.txt

然后将路径配置到hive-site.xml文件的以下参数中：

[hadoop@djt01 conf]$ vi hive-site.xml
< property>
< name>hive.querylog.location< /name>
< value>/home/hadoop/app/hive/iotmp< /value>
< description>Location of Hive run time structured log file< /description>
< /property>< property>
< name>hive.exec.local.scratchdir< /name>
< value>/home/hadoop/app/hive/iotmp< /value>
< description>Local scratch space for Hive jobs< /description>
< /property>< property>
< name>hive.downloaded.resources.dir< /name>
< value>/home/hadoop/app/hive/iotmp< /value>
< description>Temporary local directory for added resources in the remote file system.< /description>
< /property>

保存，重启hive即可。

[hadoop@djt01 hive]$ hive
hive> show databases;
OK
default
Time taken: 3.684 seconds, Fetched: 1 row(s)
hive>

（三）解释器、编译器、优化器。

1)解析器（parser）：将查询字符串转化为解析树表达式。

2)语义分析器（semantic analyzer）：将解析树表达式转换为基于块（block-based）的内部查询表达式。

3)逻辑策略生成器（logical plan generator）：将内部查询表达式转换为逻辑策略，这些策略由逻辑操作树组成。

4)优化器（optimizer）：通过逻辑策略构造多途径并以不同方式重写。

十、Hive 文件格式

hive文件存储格式包括以下几类：

1、TEXTFILE

2、SEQUENCEFILE

3、RCFILE

4、ORCFILE(0.11以后出现)

其中TEXTFILE为默认格式，建表时不指定默认为这个格式，导入数据时会直接把数据文件拷贝到hdfs上不进行处理。

SEQUENCEFILE，RCFILE，ORCFILE格式的表不能直接从本地文件导入数据，数据要先导入到textfile格式的表中， 然后再从表中用insert导入SequenceFile,RCFile,ORCFile表中。

（一）TEXTFILE 格式

默认格式，数据不做压缩，磁盘开销大，数据解析开销大。 可结合Gzip、Bzip2使用(系统自动检查，执行查询时自动解压)，但使用这种方式，hive不会对数据进行切分， 从而无法对数据进行并行操作。

示例：

create table if not exists textfile_table(
site string,
url string,
pv bigint,
label string)
row format delimited
fields terminated by '\t'
stored as textfile;
插入数据操作：
set hive.exec.compress.output=true;
set mapred.output.compress=true;
set mapred.output.compression.codec=org.apache.hadoop.io.compress.GzipCodec;
set io.compression.codecs=org.apache.hadoop.io.compress.GzipCodec;
insert overwrite table textfile_table select * from textfile_table;

（二）SEQUENCEFILE 格式

SequenceFile是Hadoop API提供的一种二进制文件支持，其具有使用方便、可分割、可压缩的特点。 SequenceFile支持三种压缩选择：NONE，RECORD，BLOCK。Record压缩率低，一般建议使用BLOCK压缩。

示例：

create table if not exists seqfile_table(
site string,
url string,
pv bigint,
label string)
row format delimited
fields terminated by '\t'
stored as sequencefile;
插入数据操作：
set hive.exec.compress.output=true;
set mapred.output.compress=true;
set mapred.output.compression.codec=org.apache.hadoop.io.compress.GzipCodec;
set io.compression.codecs=org.apache.hadoop.io.compress.GzipCodec;
SET mapred.output.compression.type=BLOCK;
insert overwrite table seqfile_table select * from textfile_table;

（三）RCFILE 文件格式

RCFILE是一种行列存储相结合的存储方式。首先，其将数据按行分块，保证同一个record在一个块上，避免读一个记录需要读取多个block。其次，块数据列式存储，有利于数据压缩和快速的列存取。

RCFILE文件示例：

create table if not exists rcfile_table(
site string,
url string,
pv bigint,
label string)
row format delimited
fields terminated by '\t'
stored as rcfile;
插入数据操作：
set hive.exec.compress.output=true;
set mapred.output.compress=true;
set mapred.output.compression.codec=org.apache.hadoop.io.compress.GzipCodec;
set io.compression.codecs=org.apache.hadoop.io.compress.GzipCodec;
insert overwrite table rcfile_table select * from textfile_table;

（四）再看TEXTFILE、SEQUENCEFILE、RCFILE三种文件的存储情况：

[hadoop@djt01 ~]$ hadoop dfs -dus /user/hive/warehouse/*
hdfs://hadoop@djt01:19000/user/hive/warehouse/hbase_table_1 0
hdfs://hadoop@djt01:19000/user/hive/warehouse/hbase_table_2 0
hdfs://hadoop@djt01:19000/user/hive/warehouse/orcfile_table 0
hdfs://hadoop@djt01:19000/user/hive/warehouse/rcfile_table 102638073
hdfs://hadoop@djt01:19000/user/hive/warehouse/seqfile_table 112497695
hdfs://hadoop@djt01:19000/user/hive/warehouse/testfile_table 536799616
hdfs://hadoop@djt01:19000/user/hive/warehouse/textfile_table 107308067
[hadoop@djt01 ~]$ hadoop dfs -ls /user/hive/warehouse/*/
-rw-r--r-- 2 hadoop supergroup 51328177 2014-03-20 00:42 /user/hive/warehouse/rcfile_table/000000_0
-rw-r--r-- 2 hadoop supergroup 51309896 2014-03-20 00:43 /user/hive/warehouse/rcfile_table/000001_0
-rw-r--r-- 2 hadoop supergroup 56263711 2014-03-20 01:20 /user/hive/warehouse/seqfile_table/000000_0
-rw-r--r-- 2 hadoop supergroup 56233984 2014-03-20 01:21 /user/hive/warehouse/seqfile_table/000001_0
-rw-r--r-- 2 hadoop supergroup 536799616 2014-03-19 23:15 /user/hive/warehouse/testfile_table/weibo.txt
-rw-r--r-- 2 hadoop supergroup 53659758 2014-03-19 23:24 /user/hive/warehouse/textfile_table/000000_0.gz
-rw-r--r-- 2 hadoop supergroup 53648309 2014-03-19 23:26 /user/hive/warehouse/textfile_table/000001_1.gz

十一、Hive 操作

（一）表操作

Hive 和 Mysql 的表操作语句类似，如果熟悉 Mysql，学习Hive 的表操作就非常容易了，下面对 Hive 的表操作进行深入讲解。

(1)先来创建一个表名为student1的内部表

hive> CREATE TABLE IF NOT EXISTS student1
> (sno INT,sname STRING,age INT,sex STRING)
> ROW FORMAT DELIMITED
> FIELDS TERMINATED BY '\t'
> STORED AS TEXTFILE;

建表规则如下：

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
[(col_name data_type [COMMENT col_comment], ...)]
[COMMENT table_comment]
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
[CLUSTERED BY (col_name, col_name, ...)
[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
[ROW FORMAT row_format]
[STORED AS file_format]
[LOCATION hdfs_path]

•CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXIST 选项来忽略这个异常

•EXTERNAL 关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION）

•LIKE 允许用户复制现有的表结构，但是不复制数据

•COMMENT可以为表与字段增加描述

•ROW FORMAT DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char]

[MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]

| SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]

用户在建表的时候可以自定义 SerDe 或者使用自带的 SerDe。如果没有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED，将会使用自带的 SerDe。在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的 SerDe，Hive 通过 SerDe 确定表的具体的列的数据。

•STORED AS

SEQUENCEFILE

| TEXTFILE

| RCFILE

| INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname

如果文件数据是纯文本，可以使用 STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCE 。

（2）创建外部表

hive> CREATE EXTERNAL TABLE IF NOT EXISTS student2
> (sno INT,sname STRING,age INT,sex STRING)
> ROW FORMAT DELIMITED
> FIELDS TERMINATED BY '\t'
> STORED AS TEXTFILE
> LOCATION '/user/external';
OK
Time taken: 0.331 seconds

hive> show tables;
OK
student1
student2
Time taken: 0.06 seconds, Fetched: 12 row(s)

（3）删除表

首先创建一个表名为test1的表

hive> CREATE TABLE IF NOT EXISTS test1
> (id INT,name STRING);
OK
Time taken: 0.07 seconds

然后查看一下是否有test1表

hive> SHOW TABLES;
OK
student1
student2
test1
Time taken: 0.042 seconds, Fetched: 12 row(s)

用命令删test1表

hive> DROP TABLE test1;
OK
Time taken: 0.191 seconds

查看test1表是否删除

hive> SHOW TABLES;
OK
student1
student2
Time taken: 0.027 seconds, Fetched: 11 row(s)

（4）修改表的结构，比如为表增加字段

首先看一下student1表的结构

hive> DESC student1;
OK
sno int
sname string
age int
sex string
Time taken: 0.886 seconds, Fetched: 4 row(s)

为表student1增加两个字段

hive> ALTER TABLE student1 ADD COLUMNS
> (address STRING,grade STRING);
OK
Time taken: 0.241 seconds

再查看一下表的结构，看是否增加

hive> DESC student1;
OK
sno int
sname string
age int
sex string
address string
grade string
Time taken: 0.154 seconds, Fetched: 6 row(s)

（5）修改表名student1为student3

hive> ALTER TABLE student1 RENAME TO student3;
OK
Time taken: 0.172 seconds

查看一下

hive> SHOW TABLES;
OK
student2
student3
Time taken: 0.088 seconds, Fetched: 11 row(s)

下面我们再改回来

hive> ALTER TABLE student3 RENAME TO student1;
OK
Time taken: 0.153 seconds

查看一下

hive> SHOW TABLES;
OK
student1
student2
Time taken: 0.064 seconds, Fetched: 11 row(s)

（6）创建和已知表相同结构的表

hive> CREATE TABLE copy_student1 LIKE student1;
OK
Time taken: 1.109 seconds

查看一下

hive> SHOW TABLES;
OK
copy_student1
student1
student2
Time taken: 0.083 seconds, Fetched: 12 row(s)

2、加入导入数据的方法，（数据里可以包含重复记录），只有导入了数据，才能供后边的查询使用

（1）加载本地数据load

首先看一下表的结构

hive> DESC student1;
OK
sno int
sname string
age int
sex string
address string
grade string
Time taken: 1.018 seconds, Fetched: 6 row(s)

创建/home/hadoop/data目录，并在该目录下创建student1.txt文件，添加如下内容

201501001 张三 22 男 北京 大三
201501003 李四 23 男 上海 大二
201501004 王娟 22 女 广州 大三
201501010 周王 24 男 深圳 大四
201501011 李红 23 女 北京 大三

加载数据到student1表中

hive> LOAD DATA LOCAL INPATH '/home/hadoop/data/student1.txt' INTO TABLE student1;
Loading data to table default.student1
Table default.student1 stats: [numFiles=1, numRows=0, totalSize=191, rawDataSize=0]
OK
Time taken: 0.766 seconds

查看是否加载成功

hive> SELECT * FROM student1;
OK
201501001张三22男北京大三
201501003李四23男上海大二
201501004王娟22女广州大三
201501010周王24男深圳大四
201501011李红23女北京大三
Time taken: 0.512 seconds, Fetched: 5 row(s)

（2）加载hdfs中的文件

首先将文件student1.txt上传到hdfs文件系统对应目录上

[hadoop@djt01 hadoop]$ hadoop fs -put /home/hadoop/data/student1.txt /user/hive
16/05/16 17:15:43 WARN util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
[hadoop@djt01 hadoop]$ hadoop fs -ls /user/hive
16/05/16 17:16:15 WARN util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
Found 2 items
-rw-r--r-- 3 hadoop supergroup 191 2016-05-19 03:27 /user/hive/student1.txt
drwxr-xr-x - hadoop supergroup 0 2016-05-19 02:46 /user/hive/warehouse

加载hdfs中的文件数据到copy_student1表中

hive> LOAD DATA INPATH '/user/hive/student1.txt' INTO TABLE copy_student1;
Loading data to table default.copy_student1
Table default.copy_student1 stats: [numFiles=1, totalSize=191]
OK
Time taken: 1.354 seconds

查看是否加载成功

hive> SELECT * FROM copy_student1;
OK
201501001张三22男北京大三
201501003李四23男上海大二
201501004王娟22女广州大三
201501010周王24男深圳大四
201501011李红23女北京大三
Time taken: 0.44 seconds, Fetched: 5 row(s)

（3）表插入数据（单表插入、多表插入）

1）单表插入

首先创建一个表copy_student2,表结构和student1相同

hive> CREATE TABLE copy_student2 LIKE student1;
OK
Time taken: 0.586 seconds

查看一下是否创建成功

hive> SHOW TABLES;
OK
copy_student1
copy_student2
student1
student2
Time taken: 0.073 seconds, Fetched: 13 row(s)

看一下copy_student2表的表结构

hive> DESC copy_student2;
OK
sno int
sname string
age int
sex string
address string
grade string
Time taken: 0.121 seconds, Fetched: 6 row(s)

把表student1中的数据插入到copy_student2表中

hive> INSERT OVERWRITE TABLE copy_student2 SELECT * FROM student1;

查看数据是否插入

hive> SELECT * FROM copy_student2;
OK
201501001张三22男北京大三
201501003李四23男上海大二
201501004王娟22女广州大三
201501010周王24男深圳大四
201501011李红23女北京大三
Time taken: 0.107 seconds, Fetched: 5 row(s)

2）多表插入

先创建两个表

hive> CREATE TABLE copy_student3 LIKE student1; OK
Time taken: 0.622 seconds
hive> CREATE TABLE copy_student4 LIKE student1;
OK
Time taken: 0.162 seconds

向多表插入数据

hive> FROM student1
> INSERT OVERWRITE TABLE copy_student3
> SELECT *
> INSERT OVERWRITE TABLE copy_student4
> SELECT *;

查看结果

hive> SELECT * FROM copy_student3;
OK
201501001张三22男北京大三
201501003李四23男上海大二
201501004王娟22女广州大三
201501010周王24男深圳大四
201501011李红23女北京大三
Time taken: 0.103 seconds, Fetched: 5 row(s)

hive> SELECT * FROM copy_student4;
OK
201501001张三22男北京大三
201501003李四23男上海大二
201501004王娟22女广州大三
201501010周王24男深圳大四
201501011李红23女北京大三
Time taken: 0.071 seconds, Fetched: 5 row(s)

3、有关表的内容的查询

（1）查表的所有内容

hive> SELECT * FROM student1;
OK
201501001张三22男北京大三
201501003李四23男上海大二
201501004王娟22女广州大三
201501010周王24男深圳大四
201501011李红23女北京大三
Time taken: 1.201 seconds, Fetched: 5 row(s)

（2）查表的某个字段的属性

hive> SELECT sname FROM student1;
OK
张三
李四
王娟
周王
李红
Time taken: 1.22 seconds, Fetched: 5 row(s)

（3）where条件查询

hive> SELECT * FROM student1 WHERE sno>201501004 AND address="北京";
OK
201501011李红23女北京大三
Time taken: 0.873 seconds, Fetched: 1 row(s)

（4）all和distinct的区别（这就要求表中要有重复的记录，或者某个字段要有重复的数据）

hive> SELECT ALL age,grade FROM student1;
OK
22大三
23大二
22大三
24大四
23大三
Time taken: 0.448 seconds, Fetched: 5 row(s)

hive> SELECT age,grade FROM student1;
OK
22大三
23大二
22大三
24大四
23大三
Time taken: 0.072 seconds, Fetched: 5 row(s)

hive> SELECT DISTINCT age,grade FROM student1;
OK
22大三
23大三
23大二
24大四
Time taken: 127.397 seconds, Fetched: 4 row(s)

hive> SELECT DISTINCT age FROM student1;
OK
22
23
24
Time taken: 106.21 seconds, Fetched: 3 row(s)

（5）limit限制查询

pre class="html">hive> SELECT * FROM student1 LIMIT 4; OK 201501001 张三 22 男 北京 大三 201501003 李四 23 男 上海 大二 201501004 王娟 22 女 广州 大三 201501010 周王 24 男 深圳 大四 Time taken: 0.253 seconds, Fetched: 4 row(s)

(6) GROUP BY 分组查询

group by 分组查询在数据统计时比较常用，接下来讲解 group by 的使用。

1) 创建一个表 group_test，表的内容如下。

hive> create table group_test(uid STRING,gender STRING,ip STRING) row format delimited fields terminated by '\t' STORED AS TEXTFILE;

向 group_test 表中导入数据。

hive> LOAD DATA LOCAL INPATH '/home/hadoop/djt/user.txt' INTO TABLE group_test;

2) 计算表的行数命令如下。

hive> select count(*) from group_test;

3) 根据性别计算去重用户数。

首先创建一个表 group_gender_sum

hive> create table group_gender_sum(gender STRING,sum INT);

将表 group_test 去重后的数据导入表 group_gender_sum。

hive> insert overwrite table group_gender_sum select group_test.gender,count(distinct group_test.uid) from group_test group by group_test.gender;

同时可以做多个聚合操作，但是不能有两个聚合操作有不同的 distinct 列。下面正确合法的聚合操作语句。

首先创建一个表 group_gender_agg

hive> create table group_gender_agg(gender STRING,sum1 INT,sum2 INT,sum3 INT);

将表 group_test 聚合后的数据插入表 group_gender_agg。

hive> insert overwrite table group_gender_agg select group_test.gender,count(distinct group_test.uid),count(*),sum(distinct group_test.uid) from group_test group by group_test.gender;

但是，不允许在同一个查询内有多个 distinct 表达式。下面的查询是不允许的。

hive> insert overwrite table group_gender_agg select group_test.gender,count(distinct group_test.uid),count(distinct group_test.ip) from group_test group by group_test.gender;

这条查询语句是不合法的，因为 distinct group_test.uid 和 distinct group_test.ip 操作了uid 和 ip 两个不同的列。

(7) ORDER BY 排序查询

ORDER BY 会对输入做全局排序，因此只有一个 Reduce（多个 Reduce 无法保证全局有序）会导致当输入规模较大时，需要较长的计算时间。使用 ORDER BY 查询的时候，为了优化查询的速度，使用 hive.mapred.mode 属性。

hive.mapred.mode = nonstrict;(default value/默认值)
hive.mapred.mode=strict;

与数据库中 ORDER BY 的区别在于，在 hive.mapred.mode=strict 模式下必须指定limit ，否则执行会报错。

hive> set hive.mapred.mode=strict;
hive> select * from group_test order by uid limit 5;
Total jobs = 1
..............
Total MapReduce CPU Time Spent: 4 seconds 340 msec
OK
01 male 192.168.1.2
01 male 192.168.1.32
01 male 192.168.1.26
01 male 192.168.1.22
02 female 192.168.1.3
Time taken: 58.04 seconds, Fetched: 5 row(s)

(8) SORT BY 查询

sort by 不受 hive.mapred.mode 的值是否为 strict 和 nostrict 的影响。sort by 的数据只能保证在同一个 Reduce 中的数据可以按指定字段排序。

使用 sort by 可以指定执行的 Reduce 个数（set mapred.reduce.tasks=< number>）这样可以输出更多的数据。对输出的数据再执行归并排序，即可以得到全部结果。

hive> set hive.mapred.mode=strict;
hive> select * from group_test sort by uid ;
Total MapReduce CPU Time Spent: 4 seconds 450 msec
OK
01 male 192.168.1.2
01 male 192.168.1.32
01 male 192.168.1.26
01 male 192.168.1.22
02 female 192.168.1.3
03 male 192.168.1.23
03 male 192.168.1.5
04 male 192.168.1.9
05 male 192.168.1.8
05 male 192.168.1.29
06 female 192.168.1.201
06 female 192.168.1.52
06 female 192.168.1.7
07 female 192.168.1.11
08 female 192.168.1.21
08 female 192.168.1.62
08 female 192.168.1.88
08 female 192.168.1.42
Time taken: 77.875 seconds, Fetched: 18 row(s)

(9) DISTRIBUTE BY 排序查询

按照指定的字段对数据划分到不同的输出 Reduce 文件中，操作如下。

hive> insert overwrite local directory '/home/hadoop/djt/test' select * from group_test distribute by length(gender);

此方法根据 gender 的长度划分到不同的 Reduce 中，最终输出到不同的文件中。length 是内建函数，也可以指定其它的函数或者使用自定义函数。

hive> insert overwrite local directory '/home/hadoop/djt/test' select * from group_test order by gender distribute by length(gender);

(10) CLUSTER BY 查询

cluster by 除了具有 distribute by 的功能外还兼具 sort by 的功能。

(二)视图操作

1) 创建一个测试表。

hive> create table test(id int,name string);
OK
Time taken: 0.385 seconds
hive> desc test;
OK
id int
name string
Time taken: 0.261 seconds, Fetched: 2 row(s)

2) 基于表 test 创建一个 test_view 视图。

hive> create view test_view(id,name_length) as select id,length(name) from test;

3) 查看 test_view 视图属性。

hive> desc test_view;

4) 查看视图结果。

hive> select * from test_view;

（三）索引操作

1) Hive 创建索引。

hive> create index user_index on table user(id) as 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler' with deferred rebuild IN TABLE user_index_table;

2) 更新数据。

hive> alter index user_index on user rebuild;

3) 删除索引

hive> drop index user_index on user;

4) 查看索引

hive> show index on user;

5) 创建表和索引案例

hive> create table index_test(id INT,name STRING) PARTITIONED BY (dt STRING) ROW FORMAT DELIMITED FILEDS TERMINATED BY ',';

创建一个索引测试表 index_test，dt作为分区属性，“ROW FORMAT DELIMITED FILEDS TERMINATED BY ','” 表示用逗号分割字符串，默认为‘\001’。

6) 创建一个临时索引表 index_tmp。

hive> create table index_tmp(id INT,name STRING,dt STRING) ROW FORMAT DELIMITED FILEDS TERMINATED BY ',';

7) 加载本地数据到 index_tmp 表中。

hive> load data local inpath '/home/hadoop/djt/test.txt' into table index_tmp;

设置 Hive 的索引属性来优化索引查询，命令如下。

hive> set hive.exec.dynamic.partition.mode=nonstrict;----设置所有列为 dynamic partition
hive> set hive.exec.dynamic.partition=true;----使用动态分区

8) 查询index_tmp 表中的数据，插入 table_test 表中。

hive> insert overwrite table index_test partition(dt) select id,name,dt from index_tmp;

9) 使用 index_test 表，在属性 id 上创建一个索引 index1_index_test 。

hive> create index index1_index_test on table index_test(id) as 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler' WITH DEFERERD REBUILD;

10) 填充索引数据。

hive> alter index index1_index_test on index_test rebuild;

11) 查看创建的索引。

hive> show index on index_test

12) 查看分区信息。

hive> show partitions index_test;

13) 查看索引数据。

$ hadoop fs -ls /usr/hive/warehouse/default_index_test_index1_index_test_

14) 删除索引。

hive> drop index index1_index_test on index_test;
show index on index_test;

15) 索引数据也被删除。

$ hadoop fs -ls /usr/hive/warehouse/default_index_test_index1_index_test_
no such file or directory

&16) 修改配置文件信息。

< property>
< name>hive.optimize.index.filter< /name>
< value>true< /value>
< /property>
< property>
< name>hive.optimize.index.groupby< /name>
< value>true< /value>
< /property>
< property>
< name>hive.optimize.index.filter.compact.minsize< /name>
< value>5120< /value>
< /property>

hive.optimize.index.filter 和 hive.optimize.index.groupby 参数默认是 false。使用索引的时候必须把这两个参数开启，才能起到作用。

hive.optimize.index.filter.compact.minsize 参数为输入一个紧凑的索引将被自动采用最小尺寸、默认5368709120（以字节为单位）。

（四）分区操作

Hive 的分区通过在创建表时启动 PARTITION BY 实现，用来分区的维度并不是实际数据的某一列，具体分区的标志是由插入内容时给定的。当要查询某一分区的内容时可以采用 WHERE 语句， 例如使用 “WHERE tablename.partition_key>a” 创建含分区的表。创建分区语法如下。

CREATE TABLE table_name(
...
)
PARTITION BY (dt STRING,country STRING)

1、 创建分区

Hive 中创建分区表没有什么复杂的分区类型（范围分区、列表分区、hash 分区，混合分区等）。分区列也不是表中的一个实际的字段，而是一个或者多个伪列。意思是说，在表的数据文件中实际并不保存分区列的信息与数据。

创建一个简单的分区表。

hive> create table partition_test(member_id string,name string) partitioned by (stat_date string,province string) row format delimited fields terminated by ',';

这个例子中创建了 stat_date 和 province 两个字段作为分区列。通常情况下需要预先创建好分区，然后才能使用该分区。例如：

hive> alter table partition_test add partition (stat_date='2015-01-18',province='beijing');

这样就创建了一个分区。这时会看到 Hive 在HDFS 存储中创建了一个相应的文件夹。

$ hadoop fs -ls /user/hive/warehouse/partition_test/stat_date=2015-01-18
/user/hive/warehouse/partition_test/stat_date=2015-01-18/province=beijing----显示刚刚创建的分区

每一个分区都会有一个独立的文件夹，在上面例子中，stat_date 是主层次，province 是 副层次。

2、 插入数据

使用一个辅助的非分区表 partition_test_input 准备向 partition_test 中插入数据，实现步骤如下。

1) 查看 partition_test_input 表的结构，命令如下。

hive> desc partition_test_input;

2) 查看 partition_test_input 的数据，命令如下。

hive> select * from partition_test_input;

3) 向 partition_test 的分区中插入数据，命令如下。

insert overwrite table partition_test partition(stat_date='2015-01-18',province='jiangsu') select member_id,name from partition_test_input where stat_date='2015-01-18' and province='jiangsu';

向多个分区插入数据，命令如下。

hive> from partition_test_input
insert overwrite table partition_test partition(stat_date='2015-01-18',province='jiangsu') select member_id,name from partition_test_input where stat_date='2015-01-18' and province='jiangsu'
insert overwrite table partition_test partition(stat_date='2015-01-28',province='sichuan') select member_id,name from partition_test_input where stat_date='2015-01-28' and province='sichuan'
insert overwrite table partition_test partition(stat_date='2015-01-28',province='beijing') select member_id,name from partition_test_input where stat_date='2015-01-28' and province='beijing';

3、 动态分区

按照上面的方法向分区表中插入数据，如果数据源很大，针对一个分区就要写一个 insert ，非常麻烦。使用动态分区可以很好地解决上述问题。动态分区可以根据查询得到的数据自动匹配到相应的分区中去。

动态分区可以通过下面的设置来打开：

set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;

动态分区的使用方法很简单，假设向 stat_date='2015-01-18' 这个分区下插入数据，至于 province 插到哪个子分区下让数据库自己来判断。stat_date 叫做静态分区列，province 叫做动态分区列。

hive> insert overwrite table partition_test partition(stat_date='2015-01-18',province)
select member_id,name province from partition_test_input where stat_date='2015-01-18';

hive.exec.max.dynamic.partitions.pernode：每一个 MapReduce Job 允许创建的分区的最大数量，如果超过这个数量就会报错（默认值100）。

hive.exec.max.dynamic.partitions：一个 dml 语句允许创建的所有分区的最大数量（默认值100）。

hive.exec.max.created.files：所有 MapReduce Job 允许创建的文件的最大数量（默认值10000）。

尽量让分区列的值相同的数据在同一个 MapReduce 中，这样每一个 MapReduce 可以尽量少地产生新的文件夹，可以通过 DISTRIBUTE BY 将分区列值相同的数据放到一起，命令如下。

hive> insert overwrite table partition_test partition(stat_date,province)
select memeber_id,name,stat_date,province from partition_test_input distribute by stat_date,province;

（五）桶操作

Hive 中 table 可以拆分成 Partition table 和 桶（BUCKET），桶操作是通过 Partition 的 CLUSTERED BY 实现的，BUCKET 中的数据可以通过 SORT BY 排序。

BUCKET 主要作用如下。

1)数据 sampling；

2)提升某些查询操作效率，例如 Map-Side Join。

需要特别主要的是，CLUSTERED BY 和 SORT BY 不会影响数据的导入，这意味着，用户必须自己负责数据的导入，包括数据额分桶和排序。 'set hive.enforce.bucketing=true' 可以自动控制上一轮 Reduce 的数量从而适配 BUCKET 的个数，当然，用户也可以自主设置 mapred.reduce.tasks 去适配 BUCKET 个数，推荐使用：

hive> set hive.enforce.bucketing=true;

操作示例如下。

1) 创建临时表 student_tmp，并导入数据。

hive> desc student_tmp;
hive> select * from student_tmp;

2) 创建 student 表。

hive> create table student(id int,age int,name string)
partitioned by (stat_date string)
clustered by (id) sorted by(age) into 2 bucket
row format delimited fields terminated by ',';

3) 设置环境变量。

hive> set hive.enforce.bucketing=true;

4) 插入数据。

hive> from student_tmp
insert overwrite table student partition(stat_date='2015-01-19')
select id,age,name where stat_date='2015-01-18' sort by age;

5) 查看文件目录。

$ hadoop fs -ls /usr/hive/warehouse/student/stat_date=2015-01-19/

6) 查看 sampling 数据。

hive> select * from student tablesample(bucket 1 out of 2 on id);

tablesample 是抽样语句，语法如下。

tablesample(bucket x out of y)

y 必须是 table 中 BUCKET 总数的倍数或者因子。

十二、Hive 复合类型

hive提供了复合数据类型：

1)Structs： structs内部的数据可以通过DOT（.）来存取。例如，表中一列c的类型为STRUCT{a INT; b INT}，我们可以通过c.a来访问域a。

2)Map（K-V对）：访问指定域可以通过["指定域名称"]进行。例如，一个Map M包含了一个group-》gid的kv对，gid的值可以通过M['group']来获取。

3)Array：array中的数据为相同类型。例如，假如array A中元素['a','b','c']，则A[1]的值为'b'

1、Struct使用

1) 建表

hive> create table student_test(id INT, info struct< name:STRING, age:INT>)
> ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
> COLLECTION ITEMS TERMINATED BY ':';

'FIELDS TERMINATED BY' ：字段与字段之间的分隔符。'COLLECTION ITEMS TERMINATED BY' ：一个字段各个item的分隔符。

2) 导入数据

$ cat test5.txt
1,zhou:30
2,yan:30
3,chen:20
4,li:80
hive> LOAD DATA LOCAL INPATH '/home/hadoop/djt/test5.txt' INTO TABLE student_test;

3) 查询数据

hive> select info.age from student_test;

2、Array使用

1) 建表

hive> create table class_test(name string, student_id_list array< INT>)
> ROW FORMAT DELIMITED
> FIELDS TERMINATED BY ','
> COLLECTION ITEMS TERMINATED BY ':';

2) 导入数据

$ cat test6.txt
034,1:2:3:4
035,5:6
036,7:8:9:10
hive> LOAD DATA LOCAL INPATH '/home/work/data/test6.txt' INTO TABLE class_test ;

3) 查询

hive> select student_id_list[3] from class_test;

3、Map使用

1) 建表

hive> create table employee(id string, perf map< string, int>)
> ROW FORMAT DELIMITED
> FIELDS TERMINATED BY '\t'
> COLLECTION ITEMS TERMINATED BY ','
> MAP KEYS TERMINATED BY ':';

‘MAP KEYS TERMINATED BY’ ：key value分隔符

2) 导入数据

$ cat test7.txt
1 job:80,team:60,person:70
2 job:60,team:80
3 job:90,team:70,person:100
hive> LOAD DATA LOCAL INPATH '/home/work/data/test7.txt' INTO TABLE employee;

3) 查询

hive> select perf['person'] from employee;

十三、Hive 的 JOIN 用法

hive只支持等连接，外连接，左半连接。hive不支持非相等的join条件（通过其他方式实现，如left outer join），因为它很难在map/reduce job实现这样的条件。而且，hive可以join两个以上的表。

1、等连接

只有等连接才允许

hive> SELECT a.* FROM a JOIN b ON (a.id = b.id)
hive> SELECT a.* FROM a JOIN b ON (a.id = b.id AND a.department = b.department)

2、多表连接

同个查询，可以join两个以上的表

hive> SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)

3、join的缓存和任务转换

hive转换多表join时，如果每个表在join字句中，使用的都是同一个列，只会转换为一个单独的map/reduce。

hive> SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)

被转换为两个map/reduce任务，因为b的key1列在第一个join条件使用，而b表的key2列在第二个join条件使用。第一个map/reduce任务join a和b。第二个任务是第一个任务的结果join c。

hive> SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)

在join的每个map/reduce阶段，序列中的最后一个表，当其他被缓存时，它会流到reducers。所以，reducers需要缓存join关键字的特定值组成的行，通过组织最大的表出现在序列的最后，有助于减少reducers的内存。

hive> SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)

三个表，在同一个独立的map/reduce任务做join。a和b的key对应的特定值组成的行，会缓存在reducers的内存。然后reducers接受c的每一行，和缓存的每一行做join计算。

hive> SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)

这里有两个map/reduce任务在join计算被调用。第一个是a和b做join，然后reducers缓存a的值，另一边，从流接收b的值。第二个阶段，reducers缓存第一个join的结果，另一边从流接收c的值。

在join的每个map/reduce阶段，通过关键字，可以指定哪个表从流接收。

hive> SELECT /*+ STREAMTABLE(a) */ a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)

三个表的连接，会转换为一个map/reduce任务，reducer会把b和c的key的特定值缓存在内存里，然后从流接收a的每一行，和缓存的行做join。

4、join的结果

LEFT，RIGHT，FULL OUTER连接存在是为了提供ON语句在没有匹配时的更多控制。例如，这个查询：

hive> SELECT a.val, b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key)

将会返回a的每一行。如果b.key等于a.key,输出将是a.val,b.val,如果a没有和b.key匹配，输出的行将是 a.val,NULL。如果b的行没有和a.key匹配上，将被抛弃。语法"FROM a LEFT OUTER JOIN b"必须写在一行，为了理解它如何工作——这个查询，a是b的左边，a的所有行会被保持；RIGHT OUTER JOIN将保持b的所有行， FULL OUTER JOIN将会保存a和b的所有行。OUTER JOIN语义应该符合标准的SQL规范。

5、join的过滤

Joins发生在where字句前，所以，如果要限制join的输出，需要写在where字句，否则写在JOIN字句。现在讨论的一个混乱的大点，就是分区表

hive> SELECT a.val, b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key) WHERE a.ds='2009-07-07' AND b.ds='2009-07-07'

将会连接a和b，产生a.val和b.val的列表。WHERE字句，也可以引用join的输出列，然后过滤他们。 但是，无论何时JOIN的行找到a的key，但是找不到b的key时，b的所有列会置成NULL，包括ds列。这就是说，将过滤join输出的所有行，包括没有合法的b.key的行。然后你会在LEFT OUTER的要求扑空。 也就是说，如果你在WHERE字句引用b的任何列，LEFT OUTER的部分join结果是不相关的。所以，当外连接时，使用这个语句

hive> SELECT a.val, b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key AND b.ds='2009-07-07' AND a.ds='2009-07-07';

join的输出会预先过滤，然后你不用对有a.key而没有b.key的行做过滤。RIGHT和FULL join也是一样的逻辑。

6、join的顺序

join是不可替换的，连接是从左到右，不管是LEFT或RIGHT join。

hive> SELECT a.val1, a.val2, b.val, c.val FROM a JOIN b ON (a.key = b.key) LEFT OUTER JOIN c ON (a.key = c.key)

首先，连接a和b，扔掉a和b中没有匹配的key的行。结果表再连接c。这提供了直观的结果，如果有一个键都存在于A和C，但不是B：完整行(包括 a.val1,a.val2,a.key)会在"a jOIN b"步骤，被丢弃，因为它不在b中。结果没有a.key，所以当它和c做LEFT OUTER JOIN,c.val也无法做到，因为没有c.key匹配a.key(因为a的行都被移除了)。类似的，RIGHT OUTER JOIN(替换为LEFT),我们最终会更怪的效果,NULL, NULL, NULL, c.val。因为尽管指定了join key是a.key=c.key，我们已经在第一个JOIN丢弃了不匹配的a的所有行。

为了达到更直观的效果，相反，我们应该从

hive> FROM c LEFT OUTER JOIN a ON (c.key = a.key) LEFT OUTER JOIN b ON (c.key = b.key).

LEFT SEMI JOIN实现了相关的IN / EXISTS的子查询语义的有效途径。由于Hive目前不支持IN / EXISTS的子查询，所以你可以用 LEFT SEMI JOIN 重写你的子查询语句。LEFT SEMI JOIN 的限制是， JOIN 子句中右边的表只能在 ON 子句中设置过滤条件，在 WHERE 子句、SELECT 子句或其他地方过滤都不行。

hive> SELECT a.key, a.value FROM a WHERE a.key in (SELECT b.key FROM B);

可以重写为

hive> SELECT a.key, a.val FROM a LEFT SEMI JOIN b on (a.key = b.key)

7、map 端 join

但如果所有被连接的表是小表，join可以被转换为只有一个map任务。查询是

hive> SELECT /*+ MAPJOIN(b) */ a.key, a.value FROM a join b on a.key = b.key

不需要reducer。对于每一个mapper,A和B已经被完全读出。限制是a FULL/RIGHT OUTER JOIN b不能使用。

如果表在join的列已经分桶了，其中一张表的桶的数量，是另一个表的桶的数量的整倍，那么两者可以做桶的连接。如果A有4个桶，表B有4个桶，下面的连接：

hive> SELECT /*+ MAPJOIN(b) */ a.key, a.value FROM a join b on a.key = b.key

只能在mapper工作。为了为A的每个mapper完整抽取B。对于上面的查询，mapper处理A的桶1，只会抽取B的桶1，这不是默认行为，要使用以下参数：

hive> set hive.optimize.bucketmapjoin = true;

如果表在join的列经过排序，分桶，而且他们有相同数量的桶，可以使用排序-合并 join。每个mapper，相关的桶会做连接。如果A和B有4个桶

hive> SELECT /*+ MAPJOIN(b) */ a.key, a.value FROM A a join B b on a.key = b.key

只能在mapper使用。使用A的桶的mapper，也会遍历B相关的桶。这个不是默认行为，需要配置以下参数：

hive> set hive.input.format=org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat;
hive> set hive.optimize.bucketmapjoin = true;
hive> set hive.optimize.bucketmapjoin.sortedmerge = true;

十四、Hive 内置操作符与函数

字符串函数

1)字符串长度函数：length

语法: length(string A)
返回值: int
说明：返回字符串A的长度
举例：
hive> select length(‘abcedfg’) from dual;
7

2)字符串反转函数：reverse

语法: reverse(string A)
返回值: string
说明：返回字符串A的反转结果
举例：
hive> select reverse(‘abcedfg’) from dual;
gfdecba

3)字符串连接函数：concat

语法: concat(string A, string B…)
返回值: string
说明：返回输入字符串连接后的结果，支持任意个输入字符串
举例：
hive> select concat(‘abc’,'def’,'gh’) from dual;
abcdefgh

4)带分隔符字符串连接函数：concat_ws

语法: concat_ws(string SEP, string A, string B…)
返回值: string
说明：返回输入字符串连接后的结果，SEP表示各个字符串间的分隔符
举例：
hive> select concat_ws(‘,’,'abc’,'def’,'gh’) from dual;
abc,def,gh

5)字符串截取函数：substr,substring

语法: substr(string A, int start),substring(string A, int start)
返回值: string
说明：返回字符串A从start位置到结尾的字符串
举例：
hive> select substr(‘abcde’,3) from dual;
cde
hive> select substring(‘abcde’,3) from dual;
cde
hive> select substr(‘abcde’,-1) from dual; （和ORACLE相同）
e

6)字符串截取函数：substr,substring

语法: substr(string A, int start, int len),substring(string A, int start, int len)
返回值: string
说明：返回字符串A从start位置开始，长度为len的字符串
举例：
hive> select substr(‘abcde’,3,2) from dual;
cd
hive> select substring(‘abcde’,3,2) from dual;
cd
hive>select substring(‘abcde’,-2,2) from dual;
de

7)字符串转大写函数：upper,ucase

语法: upper(string A) ucase(string A)
返回值: string
说明：返回字符串A的大写格式
举例：
hive> select upper(‘abSEd’) from dual;
ABSED
hive> select ucase(‘abSEd’) from dual;
ABSED

8)字符串转小写函数：lower,lcase

语法: lower(string A) lcase(string A)
返回值: string
说明：返回字符串A的小写格式
举例：
hive> select lower(‘abSEd’) from dual;
absed
hive> select lcase(‘abSEd’) from dual;
absed

9)去空格函数：trim

语法: trim(string A)
返回值: string
说明：去除字符串两边的空格
举例：
hive> select trim(‘ abc ‘) from dual;
abc

10)左边去空格函数：ltrim

语法: ltrim(string A)
返回值: string
说明：去除字符串左边的空格
举例：
hive> select ltrim(‘ abc ‘) from dual;
abc

11)右边去空格函数：rtrim

语法: rtrim(string A)
返回值: string
说明：去除字符串右边的空格
举例：
hive> select rtrim(‘ abc ‘) from dual;
abc

集合统计函数

1) 个数统计函数 count。

语法：count(*),count(expr),count(distinct expr)
返回值 int。
count(*)统计检索出行的个数，包括 NULL 值的行；
count(expr)返回指定字段的非空值的个数；
count(distinct expr)返回指定字段的不同的非空值的个数。
举例：
hive> select count(*) from user;
hive> select count(distinct age);

2) 总和统计函数 sum。

语法：sum(col),sum(distinct col)
返回值 double。
sum(col) 统计结果集中 col 的相加的结果；
sum(distinct col) 统计结果中 col 不同值相加的结果。
举例：
hive> select sum(age) from user;
hive> select sum(distinct age) from user;

3) 平均值统计函数avg。

语法：avg(col),avg(distinct col)
返回值 double。
avg(col) 统计结果集中的平均值；
avg(distinct col) 统计结果中 col 不同值相加的平均值。
举例：
hive> select avg(mark) from user;
select avg(distinct mark) from user;

4) 最小值统计函数 min。统计结果集中 col 字段的最小值。

语法：min(col)
返回值double。
举例：
hive>select min(mark) from user;

5) 最大值统计函数 max。统计结果集中 col 字段的最大值。

语法：max(col)
返回值 double。
举例：
hive> select max(mark) from user;

复合类型操作

1) Map 类型构建。根据输入的 Key-Value 对构建 Map 类型。

语法：map(key1, value1, key2, value2,...)
举例：
hive>create table map_test as select map('100','jay','200','liu') from student;
hive>describe map_test;
hive>select map_test from student;

2) Struct 类型构建。根据输入的参数构建结构体 Struct 类型。

语法：struct(val1, val2, val3, ...)
举例：
hive>create table struct_test as select struct('jay','liu','gang') from student;
hive>describe struct_test;
hive>select struct_test from student;

3) Array 类型构建。根据输入的参数构建数组 Array 类型。

语法：array(val1,val2, ...)
举例：
hive> create table array_test as select array('jay','liu','gang') from student;
hive> describe array_test;
hive> select array_test from array_test;

用户自定义函数 UDF

UDF(User Defined Function,用户自定义函数) 对数据进行处理。UDF 函数可以直接应用于 select 语句，对查询结构做格式化处理后，再输出内容。

Hive可以允许用户编写自己定义的函数UDF，来在查询中使用。Hive中有3种UDF：

1)UDF：操作单个数据行，产生单个数据行。

2)UDAF：操作多个数据行，产生一个数据行。

3)UDTF：操作一个数据行，产生多个数据行一个表作为输出。

用户构建的UDF使用过程如下：

第一步：继承UDF或者UDAF或者UDTF，实现特定的方法。

第二步：将写好的类打包为jar。如hivefirst.jar。

第三步：进入到Hive外壳环境中，利用add jar /home/hadoop/hivefirst.jar 注册该jar文件。

第四步：为该类起一个别名，create temporary function mylength as 'com.whut.StringLength';这里注意UDF只是为这个Hive会话临时定义的。

第五步：在select中使用mylength()。

自定义UDF

package whut;
import org.apache.commons.lang.StringUtils;
import org.apache.hadoop.hive.ql.exec.UDF;
import org.apache.hadoop.io.Text;
//UDF是作用于单个数据行，产生一个数据行
//用户必须要继承UDF，且必须至少实现一个evalute方法，该方法并不在UDF中
//但是Hive会检查用户的UDF是否拥有一个evalute方法
public class Strip extends UDF{
private Text result=new Text();
//自定义方法
public Text evaluate(Text str)
{
if(str==null)
return null;
result.set(StringUtils.strip(str.toString()));
return result;
}
public Text evaluate(Text str,String stripChars)
{
if(str==null)
return null;
result.set(StringUtils.strip(str.toString(),stripChars));
return result;
}
}

自定义UDAF找到最大值

package whut;
import org.apache.hadoop.hive.ql.exec.UDAF;
import org.apache.hadoop.hive.ql.exec.UDAFEvaluator;
import org.apache.hadoop.io.IntWritable;
//UDAF是输入多个数据行，产生一个数据行
//用户自定义的UDAF必须是继承了UDAF，且内部包含多个实现了exec的静态类
public class MaxiNumber extends UDAF{
public static class MaxiNumberIntUDAFEvaluator implements UDAFEvaluator{
//最终结果
private IntWritable result;
//负责初始化计算函数并设置它的内部状态，result是存放最终结果的
@Override
public void init() {
result=null;
}
//每次对一个新值进行聚集计算都会调用iterate方法
public boolean iterate(IntWritable value)
{
if(value==null)
return false;
if(result==null)
result=new IntWritable(value.get());
else
result.set(Math.max(result.get(), value.get()));
return true;
}//Hive需要部分聚集结果的时候会调用该方法
//会返回一个封装了聚集计算当前状态的对象
public IntWritable terminatePartial()
{
return result;
}
//合并两个部分聚集值会调用这个方法
public boolean merge(IntWritable other)
{
return iterate(other);
}
//Hive需要最终聚集结果时候会调用该方法
public IntWritable terminate()
{
return result;
}
}
}

十五、Hive 的权限控制

Hive从0.10可以通过元数据控制权限。但是Hive的权限控制并不是完全安全的。基本的授权方案的目的是防止用户不小心做了不合适的事情。

为了使用Hive的授权机制，有两个参数必须在hive-site.xml中设置：

< property>
< name>hive.security.authorization.enabled< /name>
< value>true< /value>
< description>enable or disable the hive client authorization< /description>
< /property> < property>
< name>hive.security.authorization.createtable.owner.grants< /name>
< value>ALL< /value>
< description>the privileges automatically granted to the owner whenever a table gets created. An example like "select,drop" will grant select and drop privilege to the owner of the table< /description>
< /property>

hive.security.authorization.enabled //参数是开启权限验证，默认为 false。

hive.security.authorization.createtable.owner.grants //参数是指表的创建者对表拥有所有权限。

角色的创建和删除

Hive 中的角色定义与关系型数据库中角色的定义类似，它是一种机制，给予那些没有适当权限的用户分配一定的权限。

1) 创建角色。

语法：hive> create role role_name;
示例：hive> create role role_tes1;

2) 删除角色。

语法：drop role role_name
示例：drop role role_test1;

角色的授权和撤销

1) 把 role_test1 角色授权给 xiaojiang 用户，命令如下。

hive> grant role role_test1 to user xiaojiang;

2) 查看 xiaojiang 用户被授权的角色，命令如下。

show role grant user xiaojiang;

3) 取消 xiaojiang 用户的 role_test1 角色，命令如下。

hive> revoke role role_test1 from user xiaojiang;

Hive 支持的权限控制。

1) 把 select 权限授权给 xiaojiang 用户，命令如下。

hive> grant select on database default to user xiaojiang;

2) 查看 xiaojiang 被授予那些操作权限，命令如下。

hive> show grant user xiaojiang on database default;

3) 收回 xiaojiang 的 select 权限，操作如下。

hive> revoke select on database default from user xiaojiang;

4) 查看 xiaojiang 用户拥有哪些权限，命令如下。

hive> show grant user xiaojiang on database default;

超级管理权限

HIVE本身有权限管理功能，需要通过配置开启。

< property>
< name>hive.metastore.authorization.storage.checks< /name>
< value>true< /value>
< /property>< property>
< name>hive.metastore.execute.setugi< /name>
< value>false< /value>
< /property>< property>
< name>hive.security.authorization.enabled< /name>
< value>true< /value>
< /property>< property>
< name>hive.security.authorization.createtable.owner.grants< /name>
< value>ALL< /value>
< /property>

其中hive.security.authorization.createtable.owner.grants设置成ALL表示用户对自己创建的表是有所有权限的（这样是比较合理地）。

开启权限控制有Hive的权限功能还有一个需要完善的地方，那就是“超级管理员”。 Hive中没有超级管理员，任何用户都可以进行Grant/Revoke操作，为了完善“超级管理员”，必须添加hive.semantic.analyzer.hook配置，并实现自己的权限控制类。

编写权限控制类，代码如下所示。

package com.xxx.hive;
import org.apache.hadoop.hive.ql.parse.ASTNode;
import org.apache.hadoop.hive.ql.parse.AbstractSemanticAnalyzerHook;
import org.apache.hadoop.hive.ql.parse.HiveParser;
import org.apache.hadoop.hive.ql.parse.HiveSemanticAnalyzerHookContext;
import org.apache.hadoop.hive.ql.parse.SemanticException;
import org.apache.hadoop.hive.ql.session.SessionState;
/**
* 设置Hive超级管理员 *
* @author
* @version $Id: AuthHook.java,v 0.1 2013-6-13 下午3:32:12 yinxiu Exp $
*/
public class AuthHook extends AbstractSemanticAnalyzerHook {
private static String admin = "admin";
@Override 27 public ASTNode preAnalyze(HiveSemanticAnalyzerHookContext context, 28 ASTNode ast) throws SemanticException {
switch (ast.getToken().getType()) {
case HiveParser.TOK_CREATEDATABASE:
case HiveParser.TOK_DROPDATABASE:
case HiveParser.TOK_CREATEROLE:
case HiveParser.TOK_DROPROLE:
case HiveParser.TOK_GRANT:
case HiveParser.TOK_REVOKE:
case HiveParser.TOK_GRANT_ROLE:
case HiveParser.TOK_REVOKE_ROLE:
String userName = null;
if (SessionState.get() != null && SessionState.get().getAuthenticator() != null) {
userName = SessionState.get().getAuthenticator().getUserName();
}
if (!admin.equalsIgnoreCase(userName)) {
throw new SemanticException(userName + " can't use ADMIN options, except " + admin + ".");
}
break;
default:
break;
}
return ast;
}
}

添加了控制类之后还必须添加下面的配置：

< property>
< name>hive.semantic.analyzer.hook< /name>
< value>com.xxx.AuthHook< /value>
< /property>

若有使用hiveserver，hiveserver必须重启。

至此，只有admin用户可以进行Grant/Revoke操作。
 权限操作示例：

grant select on database default to user xiaojiang;
revoke all on database default from user xiaojiang;
show grant user xiaojiang on database default;

Hive与JDBC示例

在使用 JDBC 开发 Hive 程序时, 必须首先开启 Hive 的远程服务接口。使用下面命令进行开启:

hive -service hiveserver & //Hive低版本提供的服务是：hiveserver
hive --service hiveserver2 &//Hive0.11.0以上版本提供了的服务是：hiveserver2

本课程我们使用的hive1.0版本，故我们使用hiveserver2服务，下面我使用 Java 代码通过JDBC连接Hiveserver。

1) 测试数据

本地目录/home/hadoop/下的djt.txt文件内容(每行数据之间用tab键隔开)如下所示:

1dajiangtai
2hadoop
3hive
4hbase
5spark

2) 程序代码

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class Hive {
private static String driverName = "org.apache.hive.jdbc.HiveDriver";//hive驱动名称
private static String url = "jdbc:hive2://djt11:10000/default";//连接hive2服务的连接地址，Hive0.11.0以上版本提供了一个全新的服务：HiveServer2
private static String user = "hadoop";//对HDFS有操作权限的用户
private static String password = "";//在非安全模式下，指定一个用户运行查询，忽略密码
private static String sql = "";
private static ResultSet res;
public static void main(String[] args) {
try {
Class.forName(driverName);//加载HiveServer2驱动程序
Connection conn = DriverManager.getConnection(url, user, password);//根据URL连接指定的数据库
Statement stmt = conn.createStatement(); //创建的表名
String tableName = "testHiveDriverTable"; /** 第一步:表存在就先删除 **/
sql = "drop table " + tableName;
stmt.execute(sql); /** 第二步:表不存在就创建 **/
sql = "create table " + tableName + " (key int, value string) row format delimited fields terminated by '\t' STORED AS TEXTFILE";
stmt.execute(sql); // 执行“show tables”操作
sql = "show tables '" + tableName + "'";
res = stmt.executeQuery(sql);
if (res.next()) {
System.out.println(res.getString(1));
} // 执行“describe table”操作
sql = "describe " + tableName;
res = stmt.executeQuery(sql);
while (res.next()) {
System.out.println(res.getString(1) + "\t" + res.getString(2));
} // 执行“load data into table”操作
String filepath = "/home/hadoop/djt.txt";//hive服务所在节点的本地文件路径
sql = "load data local inpath '" + filepath + "' into table " + tableName;
stmt.execute(sql); // 执行“select * query”操作
sql = "select * from " + tableName;
res = stmt.executeQuery(sql);
while (res.next()) {
System.out.println(res.getInt(1) + "\t" + res.getString(2));
} // 执行“regular hive query”操作，此查询会转换为MapReduce程序来处理
sql = "select count(*) from " + tableName;
res = stmt.executeQuery(sql);
while (res.next()) {
System.out.println(res.getString(1));
}
conn.close();
conn = null;
} catch (ClassNotFoundException e) {
e.printStackTrace();
System.exit(1);
} catch (SQLException e) {
e.printStackTrace();
System.exit(1);
}
}
}

3) 运行结果(右击-->Run as-->Run on Hadoop)

执行“show tables”运行结果:

testhivedrivertable

执行“describe table”运行结果:

key int
value string

执行“select * query”运行结果:

1dajiangtai
2hadoop
3hive
4hbase
5spark

执行“regular hive query”运行结果:

5

十六、hive性能调优

(一)Hadoop 计算框架的特性

什么是数据倾斜

由于数据的不均衡原因，导致数据分布不均匀，造成数据大量的集中到一点，造成数据热点

Hadoop框架的特性

不怕数据大，怕数据倾斜

jobs数比较多的作业运行效率相对比较低，比如即使有几百行的表，如果多次关联多次汇总，产生十几个jobs，耗时很长。原因是map reduce作业初始化的时间是比较长的

sum,count,max,min等UDAF，不怕数据倾斜问题,hadoop在map端的汇总合并优化，使数据倾斜不成问题

count(distinct ),在数据量大的情况下，效率较低，因为count(distinct)是按group by 字段分组，按distinct字段排序，一般这种分布方式是很倾斜的

(二)优化的常用手段

解决数据倾斜问题

减少job数

设置合理的map reduce的task数，能有效提升性能。

了解数据分布，自己动手解决数据倾斜问题是个不错的选择

数据量较大的情况下，慎用count(distinct)。

对小文件进行合并，是行至有效的提高调度效率的方法。

优化时把握整体，单个作业最优不如整体最优。

(三)Hive的数据类型方面的优化

优化原则

按照一定规则分区（例如根据日期）。通过分区，查询的时候指定分区，会大大减少在无用数据上的扫描, 同时也非常方便数据清理。

合理的设置Buckets。在一些大数据join的情况下，map join有时候会内存不够。如果使用Bucket Map Join的话，可以只把其中的一个bucket放到内存中，内存中原来放不下的内存表就变得可以放下。这需要使用buckets的键进行join的条件连结，并且需要如下设置

set hive.optimize.bucketmapjoin = true

(四)Hive的操作方面的优化

(1)全排序

Hive的排序关键字是SORT BY，它有意区别于传统数据库的ORDER BY也是为了强调两者的区别–SORT BY只能在单机范围内排序

(2)怎样做笛卡尔积

当Hive设定为严格模式（hive.mapred.mode=strict）时，不允许在HQL语句中出现笛卡尔积

MapJoin是的解决办法

MapJoin，顾名思义，会在Map端完成Join操作。这需要将Join操作的一个或多个表完全读入内存

MapJoin的用法是在查询/子查询的SELECT关键字后面添加/*+ MAPJOIN(tablelist) */提示优化器转化为MapJoin（目前Hive的优化器不能自动优化MapJoin）

其中tablelist可以是一个表，或以逗号连接的表的列表。tablelist中的表将会读入内存，应该将小表写在这里

在大表和小表做笛卡尔积时，规避笛卡尔积的方法是，给Join添加一个Join key，原理很简单：将小表扩充一列join key，并将小表的条目复制数倍，join key各不相同；将大表扩充一列join key为随机数

(3)控制Hive的Map数

通常情况下，作业会通过input的目录产生一个或者多个map任务

主要的决定因素有： input的文件总个数，input的文件大小，集群设置的文件块大小(目前为128M, 可在hive中通过set dfs.block.size;命令查看到，该参数不能自定义修改)

是不是map数越多越好

答案是否定的。如果一个任务有很多小文件（远远小于块大小128m）,则每个小文件也会被当做一个块，用一个map任务来完成，而一个map任务启动和初始化的时间远远大于逻辑处理的时间，就会造成很大的资源浪费。而且，同时可执行的map数是受限的。

是不是保证每个map处理接近128m的文件块，就高枕无忧了？

答案也是不一定。比如有一个127m的文件，正常会用一个map去完成，但这个文件只有一个或者两个小字段，却有几千万的记录，如果map处理的逻辑比较复杂，用一个map任务去做，肯定也比较耗时。

针对上面的问题3和4，我们需要采取两种方式来解决：即减少map数和增加map数；

举例

a) 假设input目录下有1个文件a,大小为780M,那么hadoop会将该文件a分隔成7个块（6个128m的块和1个12m的块），从而产生7个map数

b)假设input目录下有3个文件a,b,c,大小分别为10m，20m，130m，那么hadoop会分隔成4个块（10m,20m,128m,2m）,从而产生4个map数

即如果文件大于块大小(128m),那么会拆分，如果小于块大小，则把该文件当成一个块

(4)怎样决定reducer个数

Hadoop MapReduce程序中，reducer个数的设定极大影响执行效率

不指定reducer个数的情况下，Hive会猜测确定一个reducer个数，基于以下两个设定：

参数1：hive.exec.reducers.bytes.per.reducer（默认为1G)

参数2 ：hive.exec.reducers.max（默认为999）

计算reducer数的公式

N=min(参数2，总输入数据量/参数1)

依据Hadoop的经验，可以将参数2设定为0.95*(集群中TaskTracker个数)

reduce个数并不是越多越好

同map一样，启动和初始化reduce也会消耗时间和资源；

另外，有多少个reduce,就会有多少个输出文件，如果生成了很多个小文件，那么如果这些小文件作为下一个任务的输入，则也会出现小文件过多的问题

什么情况下只有一个reduce

很多时候你会发现任务中不管数据量多大，不管你有没有设置调整reduce个数的参数，任务中一直都只有一个reduce任务；

其实只有一个reduce任务的情况，除了数据量小于

hive.exec.reducers.bytes.per.reducer参数值的情况外，还有以下原因：

a)没有group by的汇总

b)用了Order by

(5)合并 MapReduce 操作

Multi-group by

Multi-group by是Hive的一个非常好的特性，它使得Hive中利用中间结果变得非常方便

FROM log

insert overwrite table test1 select log.id group by log.id

insert overwrite table test2 select log.name group by log.name

上述查询语句使用了Multi-group by特性连续group by了2次数据，使用不同的group by key。这一特性可以减少一次MapReduce操作。

Bucket 与 Sampling

Bucket是指将数据以指定列的值为key进行hash，hash到指定数目的桶中。这样就可以支持高效采样了

Sampling可以在全体数据上进行采样，这样效率自然就低，它还是要去访问所有数据。而如果一个表已经对某一列制作了bucket，就可以采样所有桶中指定序号的某个桶，这就减少了访问量。

如下例所示就是采样了test中32个桶中的第三个桶。

SELECT * FROM test 、、、TABLESAMPLE(BUCKET 3 OUT OF 32);

(6)JOIN 原则

在使用写有 Join 操作的查询语句时有一条原则：应该将条目少的表/子查询放在 Join 操作符的左边

原因是在 Join 操作的 Reduce 阶段，位于 Join 操作符左边的表的内容会被加载进内存，将条目少的表放在左边，可以有效减少发生 OOM 错误的几率

Map Join

Join 操作在 Map 阶段完成，不再需要Reduce，前提条件是需要的数据在 Map 的过程中可以访问到

例如：

INSERT OVERWRITE TABLE phone_traffic

SELECT /*+ MAPJOIN(phone_location) */ l.phone,p.location,l.traffic from phone_location p join log l on (p.phone=l.phone)

相关的参数为：

hive.join.emit.interval = 1000 How many rows in the right-most join operand Hive should buffer before emitting the join result.

hive.mapjoin.size.key = 10000

hive.mapjoin.cache.numrows = 10000

(7)Group By

Map 端部分聚合

并不是所有的聚合操作都需要在 Reduce 端完成，很多聚合操作都可以先在 Map 端进行部分聚合，最后在 Reduce 端得出最终结果

基于 Hash

参数包括：

hive.map.aggr = true 是否在 Map 端进行聚合，默认为 True

hive.groupby.mapaggr.checkinterval = 100000 在 Map 端进行聚合操作的条目数目

有数据倾斜的时候进行负载均衡

hive.groupby.skewindata = false

当选项设定为 true，生成的查询计划会有两个 MR Job。第一个 MR Job 中，Map 的输出结果集合会随机分布到 Reduce 中，每个 Reduce 做部分聚合操作，并输出结果，这样处理的结果是相同的 Group By Key 有可能被分发到不同的 Reduce 中，从而达到负载均衡的目的；第二个 MR Job 再根据预处理的数据结果按照 Group By Key 分布到 Reduce 中（这个过程可以保证相同的 Group By Key 被分布到同一个 Reduce 中），最后完成最终的聚合操作。

(8)合并小文件

文件数目过多，会给 HDFS 带来压力，并且会影响处理效率，可以通过合并 Map 和 Reduce 的结果文件来消除这样的影响：

hive.merge.mapfiles = true 是否和并 Map 输出文件，默认为 True

hive.merge.mapredfiles = false 是否合并 Reduce 输出文件，默认为 False

hive.merge.size.per.task = 256*1000*1000 合并文件的大小

十七、Hive 案例分析

这里我们以机顶盒产生的用户收视数据为例，来具体分析如何使用Hive。

1、机顶盒产生的用户原始数据都有一定的格式，包含机顶盒号、收看的频道、收看的节目、收看的时间等信息。

2、用户的原始数据通常不直接交给hive处理，而是需要经过一个清洗和转化的过程。这个过程一般是通过Hadoop 作业来实现，转化成与hive表对应的格式。

这个案例的具体步骤如下：

步骤1：用户数据预处理

通过MapReduce作业将日志转化为固定的格式。

用户的原始数据如下所示。

< GHApp>< WIC cardNum="1370695139" stbNum="03111108020232488" date="2012-09-21" pageWidgetVersion="1.0">< A e="13:55:11" s="13:50:10" n="104" t="1" pi="789" p="%E5%86%8D%E5%9B%9E%E9%A6%96(21)" sn="BTV影视" />< /WIC>< /GHApp>

转化之后的数据如下所示，每个字段我们使用"@"分割符号。

1370695139@03111108020232488@2012-09-21@BTV影视@再回首@13:50:10@13:55:11@301

上面的字段分别代表：机顶盒号、用户编号、收看日期、频道、栏目、起始时间、结束时间、收视时长。

步骤2：创建hive表

我们根据对应字段，使用hive创建表。

create table tvdata(cardnum string,stbnum string,date string,sn string,p string ,s string,e string,duration int) row format delimited fields terminated by '@' stored as textfile;

步骤3：将hdfs中的数据导入表中

我们使用以下命令，将hdfs中的数据导入表中。

load data inpath '/media/tvdata/part-r-00000' into table tvdata;

步骤4：编写HQL，分析数据

使用HQL语句，统计每个的频道的人均收视时长。

select sn,sum(duration)/count(*) from tvdata group by sn;

这里我们使用HQL只是从一个角度分析数据，大家可以尝试从多个角度来分析数据。