Hive编程入门指南

一、基本概念

1. 定义

Hive是基于Apache Hadoop的数据仓库基础架构，用于读取，写入和管理存储在分布式系统中的大型数据集，并使用SQL语法进行查询。

Hive建立在Hadoop之上，具有以下功能：

1. 通过SQL轻松访问数据的工具，从而实现数据仓库任务，如提取/转换/加载（ETL），报告和数据分析。
2. A mechanism to impose structure on a variety of data formats 一种为各类数据格式增加表结构的机制
3. 访问直接存储在HDFS或其他数据存储系统的文件
4. 通过Tez，Spark或MapReduce执行查询
5. Procedural language with HPL-SQL
6. 通过Hive LLAP，Apache YARN和Apache Slider进行子 - 秒查询检索
   

上图为一幅经典的Hive基本架构图：下层基于Hadoop，上层主要包括驱动器、服务器、客户端和元数据存储，驱动又包含了编译器、优化器和执行器

Hive旨在最大限度地提高可扩展性（通过更多的机器向Hadoop集群动态添加），性能，可扩展性，容错性以及与其输入格式的松散耦合。

Hive的组件包括HCatalog和WebHCat。

• HCatalog用于Hadoop的 表和存储 管理层，使用户可以使用不同的数据处理工具（包括Pig和MapReduce）来更轻松地读取和写入数据。
• WebHCat提供了一个可用于运行Hadoop MapReduce（或YARN），Pig，Hive作业或使用HTTP（REST风格）界面执行Hive元数据操作的服务。

2. 版本

Hive目前存在两条线：master、branch-1，对应的Hive版本即Hive2.x.y和Hive1.x.y

3. 与关系数据的对比

与关系型数据库的区别：

由于设计目的的不同，导致Hive和关系型数据库的一系列区别，需要重点理解Hive的“读模式”。

二、基本操作

Hive目前有两个客户端：Hive CLI、Beeline CLI 。Beeline是HiveServer2（在Hive 0.11中引入）的,基于SQLLine的JDBC客户端。 由于Hive新开发集中在HiveServer2上，Hive CLI很快将被弃用。

1.DDL (create/drop/alter/truncate/show/describe)

1.1、建数据库

Hive通过数据库（Databases）、表（Tables）、分区（Partitions）、桶（Buckets）来组织数据，其中分区和桶是非必需的，根据数据情况进行选择。

-- 建数据库语法CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name  [COMMENT database_comment]  [LOCATION hdfs_path]  [WITH DBPROPERTIES (property_name=property_value, ...)];

其中DADATABASE和SCHEMA等价，可以互换。

--删除数据库DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];--修改数据库属性ALTER (DATABASE|SCHEMA) database_name SET DBPROPERTIES (property_name=property_value, ...);   ALTER (DATABASE|SCHEMA) database_name SET OWNER [USER|ROLE] user_or_role;   -- (Note: Hive 0.13.0 and later; SCHEMA added in Hive 0.14.0)--使用数据库USE database_name;USE DEFAULT;

1.2. 建表

表根据数据存储位置不同分为內表、外表。内外表的区别如下图所示：

-- 建表语法CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name    -- (Note: TEMPORARY available in Hive 0.14.0 and later)  [(col_name data_type [COMMENT col_comment], ... [constraint_specification])]  [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]  [SKEWED BY (col_name, col_name, ...)                  -- (Note: Available in Hive 0.10.0 and later)]     ON ((col_value, col_value, ...), (col_value, col_value, ...), ...)     [STORED AS DIRECTORIES]  [   [ROW FORMAT row_format]    [STORED AS file_format]     | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)]  -- (Note: Available in Hive 0.6.0 and later)  ]  [LOCATION hdfs_path]  [TBLPROPERTIES (property_name=property_value, ...)]   -- (Note: Available in Hive 0.6.0 and later)  [AS select_statement];   -- (Note: Available in Hive 0.5.0 and later; not supported for external tables)

--内表示例CREATE TABLE page_view(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(dt STRING, country STRING) --指定分区键ROW FORMAT DELIMITED  --指定各类分隔符    FIELDS TERMINATED BY '\t'    LINES TERMINATED BY '\n'STORED AS SEQUENCEFILE; --指定文件存储格式,常用SEQUENCEFILE、TEXTFILE

--外表示例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>'; --指定数据文件位置

--删表DROP TABLE [IF EXISTS] table_name [PURGE];     -- (Note: PURGE available in Hive 0.14.0 and later)--删表中数据TRUNCATE TABLE table_name [PARTITION partition_spec];

1.3、修改表

--重命名ALTER TABLE table_name RENAME TO new_table_name;--修改表属性ALTER TABLE table_name SET TBLPROPERTIES table_properties;--增加分区ALTER TABLE table_name ADD [IF NOT EXISTS] PARTITION partition_spec   [LOCATION 'location1'] partition_spec [LOCATION 'location2'] ...;--删除分区ALTER TABLE table_name DROP [IF EXISTS] PARTITION partition_spec[, PARTITION partition_spec, ...]  [IGNORE PROTECTION] [PURGE];            -- (Note: PURGE available in Hive 1.2.0 and later, IGNORE PROTECTION not available 2.0.0 and later)

--修改列名、类型、位置ALTER TABLE table_name [PARTITION partition_spec] CHANGE [COLUMN] col_old_name col_new_name column_type  [COMMENT col_comment] [FIRST|AFTER column_name] [CASCADE|RESTRICT];--示例CREATE TABLE test_change (a int, b int, c int);// First change column a's name to a1.ALTER TABLE test_change CHANGE a a1 INT;// Next change column a1's name to a2, its data type to string, and put it after column b.ALTER TABLE test_change CHANGE a1 a2 STRING AFTER b;// The new table's structure is:  b int, a2 string, c int.// Then change column c's name to c1, and put it as the first column.ALTER TABLE test_change CHANGE c c1 INT FIRST;// The new table's structure is:  c1 int, b int, a2 string.// Add a comment to column a1ALTER TABLE test_change CHANGE a1 a1 INT COMMENT 'this is column a1';

1.4 查看表信息

DESCRIBE [EXTENDED|FORMATTED] table_name[.col_name ( [.field_name] | [.'$elem$'] | [.'$key$'] | [.'$value$'] )* ];

DESCRIBE table_name

加上EXTENDED或者FORMATTED 会显示更全的信息，诸如表的统计信息、存储信息等。

DESCRIBE EXTENDED table_name

2.DML (load/insert/update/delete/merge, import/export, explain plan)

2.1 加载数据

加载数据的方式有两种：1.从文件加载数据到表中;2.将查询结果插入表中

--从文件中加载数据LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]

--将查询结果插入表中Standard syntax:INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...) [IF NOT EXISTS]] select_statement1 FROM from_statement;INSERT INTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1 FROM from_statement;Hive extension (multiple inserts):FROM from_statementINSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...) [IF NOT EXISTS]] select_statement1[INSERT OVERWRITE TABLE tablename2 [PARTITION ... [IF NOT EXISTS]] select_statement2][INSERT INTO TABLE tablename2 [PARTITION ...] select_statement2] ...;FROM from_statementINSERT INTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1[INSERT INTO TABLE tablename2 [PARTITION ...] select_statement2][INSERT OVERWRITE TABLE tablename2 [PARTITION ... [IF NOT EXISTS]] select_statement2] ...;Hive extension (dynamic partition inserts):INSERT OVERWRITE TABLE tablename PARTITION (partcol1[=val1], partcol2[=val2] ...) select_statement FROM from_statement;INSERT INTO TABLE tablename PARTITION (partcol1[=val1], partcol2[=val2] ...) select_statement FROM from_statement;

--更新UPDATE tablename SET column = value [, column = value ...] [WHERE expression]

--删除数据DELETE FROM tablename [WHERE expression]

--查看执行计划EXPLAIN [EXTENDED|DEPENDENCY|AUTHORIZATION] query

学会查看执行计划是代码优化的关键

三、参考资料

1.Hive官网
2.《Hive编程指南》