Hadoop大数据开发基础系列：九、HiveQL

一、HiveQL是什么？

1.HiveQL概述

Hive是一种数据仓库分析系统，前面我们也说到过，它主要是将SQL的查询过程通过MapReduce来完成

HiveQL本质上就是一种SQL。Hive通过HiveSQL去查询分析需要的内容，使不熟悉MapReduce的用户也可以很方便的利用SQL来查询、汇总并分析数据。

2.HiveQL及Hive的特点

(1)与关系型数据库的SQL 略有不同，但支持了绝大多数的语句如DDL、DML 以及常见的聚合函数、连接查询、条件查询。

(2)Hive不适合用于联机，也不提供实时查询功能。

它最适合应用在基于大量不可变数据的批处理作业。如HiveQL不支持更新操作, 也不支持索引和事务, 它的子查询和join操作也很局限, 这是因其底层依赖于Hadoop平台这决定的, 但其有些特点是SQL所无法企及的。

(3)Hive具有可伸缩性，支持Hadoop集群上动态的添加设备，可拓展和容错等特点

下面对常用操作进行简单介绍：

二、创建表和删除表

1.创建表

CREATE [①TEMPORARY][②EXTERNAL] TABLE [③IF NOT EXISTS][database_name] table_name 
    [(col_name data_type)[⑨COMMENT col_comment],...[constraint_sepecification]]
    [COMMENT table_comment]
    [④PARTITIONED BY (col_name data_type)[COMMENT col_comment],...)]
    [⑤CLUSTERED BY (col_name1,col_name2,...) [⑩SORTED BY (col_name[ASC|DESC],...)] INTO num_buckets BUCKETS]
    [SKEWED BY (col_name,col_name,...)]
        ON ((col_value,col_value,...),(col_value,col_value,...),...)
        [STORED AS DIRECTORIES]
    [
    [⑥ROW FORMAT row_format]
    [⑦STORED AS file_format]
        | STORED BY '' [WITH SERDEPROPERTIES(...)]
    ]
    [LOCATION hdfs_path]
    ......(更多的参数可参见官方文档)

CREATE [TEMPORARY][EXTERNAL] TABLE [IF NOT EXISTS][database_name] table_name
    ⑧LIKE existing_table_or_view_name
    [LOCATION hdfs_path]

下面对关键字进行解释

①TEMPORARY

创建临时表，临时表只对当前的session生效一次，session结束后该表自动删除。

注意：不支持分区字段和创建索引。

②EXTERNAL

创建外部表，在建表的同时制定一个指向实际数据的路径（LOCATION），创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的时候，外部表只删除元数据，不删除数据。

配合LOCATION hdfs_name使用

③IF NOT EXISTS

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

④PARTITIONED BY

一个表可以拥有一个或者多个分区，每一个分区单独存在一个目录下。

⑤CLUSTERED BY

分桶；分桶有两个理由，第一个是查询效率更高，第二个是取样更高效。

物理上，每个桶就是表或分区中的一个文件，桶的每个文件对应于MapReduce的输出分区。

⑥ROW FORMAT

是用来设置创建的表在加载数据的时候，支持的列分隔符。

⑦STORED AS

[STORED AS file_format]

用于 指定hive存储的文件格式（在前一节有提及）。一般是结合[ROW FORMAT]使用。

⑧LIKE

借助以已存在的表， 创建一张空表。

⑨COMMENT

给语句注释的作用

⑩SORTED BY

指定按照哪一列进行排序。

CATS

create table person2 as select * from person;

在创建表的同时插入数据，生成一张新表。

2.删除表

(1)删除表

[DROP TABLE table_name]

    表删除包括表的定义和关联对象（规则、索引、约、触发器、主键，等）。很明显，一旦表被删除，那么表中包含的所有的数据行都会被一同删除。 　　

(2)截断表

[TRUNCATE TABLE table_name]

    truncate 命令则仅仅删除了表中所有的数据行。表的结构和所有的索引仍然继续存在，绑定到列上的规则、默认值、约束仍然继续绑定，并且触发器也仍然起作用。直到你输入删除表的命令（如上所述）。

三、修改表

1.重命名表

ALTER TABLE table_name RENAME TO new_table_name;

2.修改表的属性

ALTER TABLE table_name SET TBLPROPERTIES table_properties;

修改表注释、修改表serde的列分隔符、修改表的sede等

3.修改表的分区：

(1)新增分区

ALTER TABLE table_name ADD [IF NOT EXISTS] PARTITION 指定分区 [LOCATION ‘location1’];

(2)重命名分区ALERT TABLE（将table指定放入分区）

ALTER TABLE table_name PARTITION 指定分区 RENAME TO PARTITION 指定分区;

(3)自动恢复分区（让系统自动根据目录结构进行分区划分）MSCK

MSCK REPAIR TABLE table_name;

(4)删除分区

ALTER TABLE table_name DROP [IF EXISTS] PARTITION 指定分区(例：(y='WT0228',m='201501'))；

4.修改列名change colname

ALTER TABLE table_name [PARTITION partition_spec] CHANGE [COLUMN] col_oldname col_newname col_type
    [COMMENT col_col_comment];

5.添加列或者替换列

ALTER TABLE TABLE_NAME table_name
    ADD|REPLACE COLUMNS(col_name data_type,......)

四、视图

1.Hive中的视图功能特点：

(1)只有逻辑视图，没有物化视图（也就是物理表，物化视图本身会存储数据）

(2)视图只能查询，不能Load/Insert/Update/Delete数据

(3) 视图在创建时候，只是保存了一份元数据，当查询视图的时候，才开始执行视图对应的那些子查询；

2.对视图的操作

(1)创建

CREATE VIEW [IF NOT EXISTS] [db_name.] 
    AS SELECT ...;

(2)修改

ALTER VIEW [db_name.] view_name SET TBLPROPERTIES table_properties;

(3)删除

DROP VIEW [IF NOT EXISTS] [db_name.] view_name;

五、索引

(1)创建（指定希望包含在索引中的列）

CREATE INDEX index_name
    ON TABLE base_table_name
    (列名1，列名2...);

(2)删除

DROP INDEX [IF EXISTS] index_name ON table_name;

(3)修改

ALTER INDEX index_name ON table_name [PARTITION partition_spec] REBUILD;

六、显示信息

1.列出所有的库

SHOW DATABASES ;

2.列出库下所有的表

SHOW TABLES IN database_name;

3.列出所有的视图

SHOW VIEWS [IN/FROM database_name]

4.列出表分区

SHOW PARTITION table_name;

5.显示索引

SHOW (INDEX|INDEXES) ON table_with_index [ (FROM|IN) db_name]; 

6.显示表的所有列信息

SHOW COLUMNS ( FROM|IN ) table_name [(FROM|IN) db_name];

7.显示Function（自定义的函数）信息

SHOW FUNCTIONS "a.*";

8.显示锁表（给表加读写权限）的信息

SHOW LOCKS <table_name>

七、用户自定义函数 UDF

Hive中有三种UDF（User-Defined Function）

1.UDF：

操作于单个数据行，一对一的转换

2.UDAF（User-Defined Aggregate Function） ：

3.UDTF：

一行数据转换为多行数据使用的函数