Hadoop大数据实战系列文章之Hive

hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张数据库表，并提供完整的 sql 查询功能，Hive 定义了简单的类 SQL 查询语言，称为 HQL，它允许熟悉 SQL 的用户查询数据可以将 sql语句转换为MapReduce任务进行运行，不必开发专门的 MapReduce。毕竟会写 SQL 的人比写 JAVA 的人多，这样可以让一大批运营人员直接获取海量数据。在数据仓库建设中，HIVE 灵活易用且易于维护，十分适合数据仓库的统计分析。

本章内容：

1) Hive 简介

2) Hive 基础组成

3) Hive 执行流程

4) Hive 基础操作

1. Hive 基础 原理

hive 是建设在 Hadoop 之上，Hive 包括如下组件：CLI （command line interface）、

JDBC/ODBC、Thrift Server、WEB GUI、MetaStore 和 Driver(Complier、Optimizer 和

Executor)。

1) Driver 组件：包括 Complier、Optimizer 和 Executor，它的作用是将我们写的HiveQL（类 SQL）语句进行解析、编译优化，生成执行计划，然后调用底层的MapReduce 计算框架。

2) Metastore 组件：元数据服务组件存储hive 的元数据，hive的元数据存储在关系数据库里，hive 支持的关系数据库有 derby、mysql。Hive 还支持把 metastore服务安装到远程的服务器集群里，从而解耦 hive 服务和metastore 服务。

3) Thrift 服务：thrift 是 facebook 开发的一个软件框架，它用来进行可扩展且跨语言的服务的开发，hive 集成了该服务，能让不同的编程语言调用 hive 的接口。

4) CLI：command line interface，命令行接口。

5) Thrift 客户端： hive 架构的许多客户端接口是建立在 thrift 客户端之上，包括JDBC 和 ODBC 接口。

6) WEBGUI：hive 客户端提供了一种通过网页的方式访问 hive 所提供的服务。用户接口主要有三个：CLI，Client 和 WUI。其中最常用的是 CLI，公司内可通过堡垒机连接 ssh hdp_lbg_ectech@10.126.101.7，直接输入 hive，就可连接到 HiveServer。

Hive 的 metastore组件是 hive 元数据集中存放地。Metastore组件包括两个部分：metastore 服务和后台数据的存储。后台数据存储的介质就是关系数据库，例如 hive 默认的嵌入式磁盘数据库 derby，还有 mysql 数据库。Metastore服务是建立在后台数据存储介质之上，并且可以和hive服务进行交互的服务组件，默认情况下，metastore服务和hive服务是安装在一起的，运行在同一个进程当中。我也可以把metastore服务从hive服务里剥离出来，metastore 独立安装在一个集群里，hive 远程调用 metastore 服务，这样我们可以把元数据这一层放到防火墙之后，客户端访问 hive 服务，就可以连接到元数据这一层，从而提供了更好的管理性和安全保障。使用远程的 metastore服务，可以让 metastore服务和 hive 服务运行在不同的进程里，这样也保证了 hive 的稳定性，提升了 hive 服务的效率。

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

Hive 的数据模型介绍如下：

1) Hive 数据库

类似传统数据库的 DataBase，例如 hive >create database test_database;

2) 内部表

Hive 的内部表与数据库中的表在概念上是类似。每一个 Table 在 Hive 中都有一个相应 的 目 录 存 储 数 据 。 例 如 一 个 表 hive_test ， 它 在 HDFS 中 的 路 径 为/home/hdp_lbg_ectech/warehouse/hdp_lbg_ectech_bdw.db/hive_test ， 其 中/home/hdp_lbg_ectech/warehouse 是 在 hive-site.xml 中 由${hive.metastore.warehouse.dir}指定的数据仓库的目录，所有的 Table 数据（不包括外部表）都保存在这个目录中。删除表时，元数据与数据都会被删除。

建表语句示例：

CREATE EXTERNAL TABLE hdp_lbg_ectech_bdw.hive_test

(`userid` string COMMENT'')

ROW FORMAT DELIMITED FIELDS TERMINATED BY'\001';

load data inpath ‘/home/hdp_lbg_ectech/resultdata/test.txt’overwrite into

table hive_test;

3) 外部表

外部表指向已经在 HDFS 中存在的数据，可以创建分区。它和内部表在元数据的组织上是相同的，而实际数据的存储则有较大的差异。内部表在加载数据的过程中，实际数据会被移动到数据仓库目录中。删除表时，表中的数据和元数据将会被同时删除。而外部表只有一个过程，加载数据和创建表同时完成（CREATE EXTERNAL TABLE ……LOCATION），实际数据是存储在 LOCATION 后面指定的 HDFS 路径中，并不会移动到数据仓库目录中。当删除一个外部表时，仅删除该表的元数据，而实际外部目录的数据不会被删除，推荐使用这种模式。

4) 分区

Partition相当于数据库中的列的索引，但是Hive组织方式和数据库中的很不相同。在Hive 中，表中的一个分区对应于表下的一个目录，所有的分区数据都存储在对应的目录中。一般是按时间、地区、类目来分区，便于局部查询，避免扫描整个数据源。

5) 桶

Buckets 是将表的列通过 Hash 算法进一步分解成不同的文件存储。它对指定列计算hash，根据 hash 值切分数据，目的是为了并行，每一个 Bucket 对应一个文件。例如将userid 列分散至 32 个 bucket，首先对 userid 列的值计算 hash，对应 hash 值为 0 的HDFS目录为/home/hdp_lbg_ectech/resultdata/part-00000；hash值为20的HDFS目录为/home/hdp_lbg_ectech/resultdata/part-00020。

6) Hive 的视图

视图与传统数据库的视图类似。目前只有逻辑视图，没有物化视图；视图只能查询，不能 Load/Insert/Update/Delete 数据；视图在创建时候，只是保存了一份元数据，当查询视图的时候，才开始执行视图对应的那些子查询；

2. Hive 基础操作

1) DDL 操作：包括

 建表，删除表

 修改表结构

 创建/删除视图

 创建数据库和显示命令

 增加分区，删除分区

 重命名表

 修改列的名字、类型、位置、注释

 增加/更新列

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 。

例子 1：创建简单表

CREATE TABLE pokes (foo INT, bar STRING);

例子 2：创建外部表

CREATE EXTERNAL TABLE page_view(viewTime INT, userid BIGINT,

page_url STRING, referrer_url STRING,

ip STRING COMMENT 'IP Address of the User',

country STRING COMMENT 'country of origination')

COMMENT 'This is the staging page view table'

ROW FORMAT DELIMITED FIELDS TERMINATED BY '\054'

STORED AS TEXTFILE

LOCATION '<hdfs_location>';

例子 3：创建分区表

CREATE TABLE par_table(viewTime INT, userid BIGINT,

page_url STRING, referrer_url STRING,

ip STRING COMMENT 'IP Address of the User')

COMMENT 'This is the page view table'

PARTITIONED BY(date STRING, pos STRING)

ROW FORMAT DELIMITED ‘\t’

FIELDS TERMINATED BY '\n'

STORED AS SEQUENCEFILE;

例子 4：创建 Bucket表

CREATE TABLE par_table(viewTime INT, userid BIGINT,

page_url STRING, referrer_url STRING,

ip STRING COMMENT 'IP Address of the User')

COMMENT 'This is the page view table'

PARTITIONED BY(date STRING, pos STRING)

CLUSTERED BY(userid) SORTED BY(viewTime) INTO 32 BUCKETS

ROW FORMAT DELIMITED ‘\t’

FIELDS TERMINATED BY '\n'

STORED AS SEQUENCEFILE;

例子 5：创建表并创建索引字段 ds

CREATE TABLE invites (foo INT, bar STRING) PARTITIONED BY (ds STRING);

例子 6：复制一个空表

CREATE TABLE empty_key_value_store

LIKE key_value_store;

例子 7：显示所有表

SHOW TABLES;

例子 8：按正则条件（正则表达式）显示表

SHOW TABLES '.*s';

例子 9：表添加一列

ALTER TABLE pokes ADD COLUMNS (new_col INT);

例子 10：添加一列并增加列字段注释

ALTER TABLE invites ADD COLUMNS (new_col2 INT COMMENT 'a comment');

例子 11：更改表名

ALTER TABLE events RENAME TO 3koobecaf;

例子 12：删除列

DROP TABLE pokes;

例子 13：增加、删除分区

增加：

ALTER TABLE table_name ADD [IF NOT EXISTS] partition_spec [ LOCATION 'location1' ]

partition_spec [ LOCATION 'location2' ] ...

partition_spec:

: PARTITION (partition_col = partition_col_value, partition_col = partiton_col_value, ...)

删除：

ALTER TABLE table_name DROP partition_spec, partition_spec,...

例子 14：重命名表

ALTER TABLE table_name RENAME TO new_table_name

例子 15：修改列的名字、类型、位置、注释

ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type

[COMMENT col_comment] [FIRST|AFTER column_name]

这个命令可以允许改变列名、数据类型、注释、列位置或者它们的任意组合

例子 16：创建／删除视图

CREATE VIEW [IF NOT EXISTS] view_name [ (column_name [COMMENT

column_comment], ...) ][COMMENT view_comment][TBLPROPERTIES (property_name =

property_value, ...)] AS SELECT

增加视图

如果没有提供表名，视图列的名字将由定义的 SELECT 表达式自动生成

如果修改基本表的属性，视图中不会体现，无效查询将会失败

视图是只读的，不能用 LOAD/INSERT/ALTER

DROP VIEW view_name

删除视图

例子 17：创建数据库

CREATE DATABASE name

例子 18：显示命令

show tables;

show databases;

show partitions ;

show functions

describe extended table_name dot col_name

2) DML 操作：元数据存储

hive 不支持用 insert 语句一条一条的进行插入操作，也不支持 update 操作。数据是以 load 的方式加载到建立好的表中。数据一旦导入就不可以修改。

DML 包括：

 INSERT 插入

 UPDATE 更新

 DELETE 删除

 向数据表内加载文件

 将查询结果插入到 Hive 表中

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION

(partcol1=val1, partcol2=val2 ...)]

Load 操作只是单纯的复制/移动操作，将数据文件移动到 Hive 表对应的位置。

filepath

相对路径，例如：project/data1

绝对路径，例如： /user/hive/project/data1

包含模式的完整 URI，例如：hdfs://namenode:9000/user/hive/project/data1

例子 1：向数据表内加载文件

LOAD DATA LOCAL INPATH './examples/files/kv1.txt' OVERWRITE INTO TABLE pokes;

例子 2：加载本地数据，同时给定分区信息

LOAD DATA LOCAL INPATH './examples/files/kv2.txt' OVERWRITE INTO TABLE invites

PARTITION (ds='2008-08-15');

例子 3：加载本地数据，同时给定分区信息

LOAD DATA INPATH '/user/myname/kv2.txt' OVERWRITE INTO TABLE invites PARTITION

(ds='2008-08-15');

例子 4：将查询结果插入Hive 表

基本模式：

INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)]

select_statement1 FROM from_statement

多插入模式：

FROM from_statement

INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)]

select_statement1

[INSERT OVERWRITE TABLE tablename2 [PARTITION ...] select_statement2] ...

自动分区模式：

INSERT OVERWRITE TABLE tablename PARTITION (partcol1[=val1], partcol2[=val2] ...)

select_statement FROM from_statement

例子 3：将查询结果写入HDFS 文件系统

INSERT OVERWRITE [LOCAL] DIRECTORY directory1 SELECT ... FROM ...

FROM from_statement

INSERT OVERWRITE [LOCAL] DIRECTORY directory1 select_statement1

[INSERT OVERWRITE [LOCAL] DIRECTORY directory2 select_statement2]

数据写入文件系统时进行文本序列化，且每列用^A 来区分，\n 换行

例子 3：INSERT INTO

INSERT INTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)]

select_statement1 FROM from_statement

3) DQL 操作：数据查询 SQL

DQL 包括：

 基本的 Select 操作

 基于 Partition 的查询

 Join

基本 Select 操作：

SELECT [ALL | DISTINCT] select_expr, select_expr, ...

FROM table_reference

[WHERE where_condition]

[GROUP BY col_list [HAVING condition]]

[ CLUSTER BY col_list

| [DISTRIBUTE BY col_list] [SORT BY| ORDER BY col_list]

]

[LIMIT number]

 使用 ALL 和 DISTINCT 选项区分对重复记录的处理。默认是 ALL，表示查询所有记录。DISTINCT 表示去掉重复的记录

 Where 条件

 类似我们传统 SQL 的 where 条件

 目前支持 AND,OR ,0.9 版本支持 between

 IN, NOT IN

 不支持 EXIST ,NOT EXIST

ORDER BY 与 SORT BY 的不同

 ORDER BY 全局排序，只有一个 Reduce 任务

 SORT BY 只在本机做排序

Limit

 Limit 可以限制查询的记录数

例子 1：按先件查询

SELECT a.foo FROM invites a WHERE a.ds='<DATE>';

例子 2：将查询数据输出至目录

INSERT OVERWRITE DIRECTORY '/tmp/hdfs_out' SELECT a.* FROM invites a WHERE

a.ds='<DATE>';

例子 3：将查询结果输出至本地目录

INSERT OVERWRITE LOCAL DIRECTORY '/tmp/local_out' SELECT a.* FROM pokes a;

例子 4：选择所有列到本地目录

hive> INSERT OVERWRITE TABLE events SELECT a.* FROM profiles a;

INSERT OVERWRITE TABLE events SELECT a.* FROM profiles a WHERE a.key < 100;

INSERT OVERWRITE LOCAL DIRECTORY '/tmp/reg_3' SELECT a.* FROM events a;

INSERT OVERWRITE DIRECTORY '/tmp/reg_4' select a.invites, a.pokes FROM profiles a;

INSERT OVERWRITE DIRECTORY '/tmp/reg_5' SELECT COUNT(1) FROM invites a WHERE

a.ds='<DATE>';

INSERT OVERWRITE DIRECTORY '/tmp/reg_5' SELECT a.foo, a.bar FROM invites a;

INSERT OVERWRITE LOCAL DIRECTORY '/tmp/sum' SELECT SUM(a.pc) FROM pc1 a;

例子 5：将一个表的统计结果插入另一个表中

INSERT OVERWRITE TABLE events SELECT a.bar, count(1) FROM invites a WHERE a.foo >

0 GROUP BY a.bar;

FROM pokes t1 JOIN invites t2 ON (t1.bar = t2.bar) INSERT OVERWRITE TABLE events

SELECT t1.bar, t1.foo, t2.foo;

例子 6：将多表数据插入到同一表中

FROM src

INSERT OVERWRITE TABLE dest1 SELECT src.* WHERE src.key < 100

INSERT OVERWRITE TABLE dest2 SELECT src.key, src.value WHERE src.key >= 100 and

src.key < 200

INSERT OVERWRITE TABLE dest3 PARTITION(ds='2008-04-08', hr='12') SELECT src.key

WHERE src.key >= 200 and src.key < 300

INSERT OVERWRITE LOCAL DIRECTORY '/tmp/dest4.out' SELECT src.value WHERE

src.key >= 300;

例子 7：将文件流直接插入文件

FROM invites a INSERT OVERWRITE TABLE events SELECT TRANSFORM(a.foo, a.bar) AS

(oof, rab) USING '/bin/cat' WHERE a.ds > '2008-08-09';