HiveQL学习笔记

Hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张数据库表，并提供类SQL查询功能。其本质是将SQL转换为MapReduce程序

1. hive client命令

1.1 hive命令参数

-e: 命令行sql语句-f: SQL文件-h, --help: 帮助--hiveconf: 指定配置文件-i: 初始化文件-S, --silent: 静态模式(不将错误输出)-v, --verbose: 详细模式

1.2. 交互模式

hive> show tables;                          # 查看所有表名hive> show tables 'ad*';                    # 查看以'ad'开头的表名hive> set <cmd>;                            # 设置变量与查看变量hive> set -V;                               # 查看所有变量hive> set hive.stats.atomic;                # 查看hive.stats.atomic变量hive> set hive.stats.atomic=false;          # 设置hive.stats.atomic变量hive> dfs -ls;                              # 查看hadoop所有文件路径hive> dfs -ls /usr/hive/warehouse/;         # 查看hive所有文件hive> dfs -ls /usr/hive/warehouse/ptest;    # 查看ptest文件hive> source file <filepath>;               # 在client里执行一个hive脚本文件hive> quit;                                 # 退出交互式shellhive> exit;                                 # 退出交互式shellhive> reset;                                # 重置配置为默认值hive> !ls;                                  # 从Hive shell执行一个shell命令

2. 操作及函数

查看函数：hive> show functions;正则查看函数名：hive> functions 'xpath.*';查看具体函数内容：hive> describe function xpath;hive> desc function xpath;

3. 字段类型

Hive支持基本数据类型和复杂数据类型。基本数据类型有数值类型(INT, FLOAT, DOUBLE)，布尔型和字符串。复杂数据类型有三种：ARRAY，MAP，和STRUCT。

3.1 基本数据类型

TINYINT: 1个字节SMALLINT: 2个字节INT: 4个字节BIGINT: 8个字节BOOLEAN: TRUE/FALSEFLOAT: 4个字节，单精度浮点型DOUBLE: 8个字节，双精度浮点型STRING: 字符串

3.2 复杂数据类型

ARRAY: 有序字段MAP: 无序字段STRCUT: 一组命名的字段

4. 表类型

hive表大致分为普通表、外部表、分区表。

4.1 普通表

创建表hive> create table tb_person(id int, name string);创建表并创建分区字段dshive> create table tb_stu(id int, name string) partitioned by(ds string);查看分区hive> show partitions tb_stu;显示所有表hive> show tables;按正则表达式显示表hive> show tables 'tb_*';表添加一列hive> alter table tb_person add columns (new_col int);添加一列并增加列字段注释hive> alter table tb_person add columns (new col2 int comment 'a comment');更改表名hive> alter table tb_stu rename to tb_stu_new;删除表(hive只能删除分区，不能删除记录或列)hive> drop table tb_stu;对于托管表，drop操作会把元数据和数据文件删掉；对于外部表，只能是删元数据。如果只要删除表中的数据，保留表名，可以在HDFS上删除数据文件：hive> dfs -rmr /usr/hive/warehouse/mutil1/*

将本地文件/home/hadoop/ziliao/stu.txt中的数据加载到表中，stu.txt文件数据如下：

1 zhangsan2 lisi3 wangwu

将文件中的数据加载到表中：

hive> load data local inpath '/home/hadoop/ziliao/stu.txt' overwrite into table tb_person;

加载本地数据，同时给定分区信息

hive> load data local inpath '/home/hadoop/ziliao/stu.txt' overwrite into table tb_stu partition (ds='2008-08-15');

如果导入的数据在hdfs上，则不需要local关键字。托管表导入的数据文件可在数据仓库目录”/usr/hive/warehouse/”中看到。

查看数据

hive> fds -ls /usr/hive/warehouse/tb_stu;hive> fds -ls /usr/hive/warehouse/tb_person;

4.2 外部表

external关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径(location)，hive创建内部表(即普通表)时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据位置做任何改变。在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。

创建外部表：

hive> create external table tb_record(col1 string, col2 string) row format delimited fields terminated by '\t' location '/usr/hadoop/input';

这样表tb_record的数据就是hdfs://usr/hadoop/input/*的数据了。

4.3 分区表

分区是表的部分列的集合，可以为频繁使用的数据建立分区，这样查找分区中的数据时就不需要扫描全表，这对于查找效率很有帮助。

创建分区hive> create table log(ts bigint, line string) partitioned by(name string);插入分区hive> insert overwrite table log partition(name='xiapi') select id from userinfo where name='xiapi';查看分区hive> show partitions log;删除分区hive> alter table ptest drop partition （name='xiapi'）

通常情况下需要先预先创建好分区，然后才能使用该分区。分区列的值要转化为文件夹的存储路径，所以分区列的值中的特殊字符会被使用’%’加上2字节的ASCII码进行转义。

5. SQL操作及桶

5.1 创建表

首先创建三张测试表：

hive> create table userinfo(id int, name string) row format delimited fields terminated by '\t';hive> create table classinfo(teacher string, classname string) row format delimited fields terminated by '\t';hive> create table choice(userid int, classname string) row format delimited fields terminated by '\t';

5.2 导入数据

建表后，可以从本地文件系统或HDFS中导入数据文件，导入数据样例如下：

userinfo.txt(内容之间以tab键隔开):1   xiapi2   xiaoxue3   qingqingclassinfo.txt(内容之间以tab键隔开)jack    mathsam     chinalucy    englishchoice.txt(内容之间以tab键隔开)1   math1   china1   english2   china2   english3   english

三个文件放入本地/home/hadoop/ziliao路径

5.3 按下面导入数据

hive> load data local inpath '/home/hadoop/ziliao/userinfo.txt' overwrite into table userinfo;hive> load data local inpath '/home/hadoop/ziliao/classinfo.txt' overwrite into table classinfo;hive> load data local inpath '/home/hadoop/ziliao/choice.txt' overwrite into table choice;

查询表数据

hive> select * from userinfo;hive> select * from classinfo;hive> select * from choice;

5.4 分区

# 创建分区hive> create table ptest(userid int) partitioned by (name string) row format delimited fields terminated by '\t';# 准备导入数据xiapi.txt内容如下：1# 导入数据hive> load data local inpath '/home/hadoop/ziliao/xiapi.txt' overwrite into table ptest partition (name='xiapi');# 查看分区hive> dfs -ls /usr/hive/warehouse/ptest/name=xiapi;# 查询分区hive> select * from ptest where name='xiapi';# 显示分区hive> show partitions ptest;# 对分区插入数据(每次都会覆盖原来的数据)hive> insert overwrite table ptest partition(name='xiapi') select id from userinfo where name='xiapi';# 删除分区hive> alter table ptest drop pertition (name='xiapi');

5.5 桶

桶是比分区更细粒度的划分，可以对表或者分区进一步组织成桶。Hive对数据列值进行哈希，然后除以桶的个数求余，以此结果确定该把此记录放在哪个桶里。

把表或分区组织成桶的理由：

1. 获得更高的查询效率。连接两个在相同列上进行分桶的表时，可以使用Map端连接(Map-size join)高效实现。因为对于这两个表来说，具有相同列值的数据肯定落在相同的桶中，故可以大大减少join的数据量。
2. 取样(sampling)操作更高效。在数据集的一小部分数据上试运行查询。

可以把表或分区组织成桶，桶是按行分开组织特定字段，每个桶对应一个reduce操作。在建立桶之前，需要设置hive.enforce.bucketing属性为true，使hive能够识别桶。在表中分桶的操作如下：

hive> set hive.enforce.bucketing=true;hive> set hive.enforce.bucketing;hive.enforce.buckering=true;hive> create table btest2(id int, name string) clustered by (id) into 3 buckets row format delimited fields terminated by '\t';

向桶中插入数据，这里按照用户id分了三个桶，在插入数据时对应三个reduce操作，输出三个文件。

hive> insert overwrite table btest2 select * from userinfo;

查看数据仓库下的桶目录，三个桶对应三个目录。

hive> dfs -ls /usr/hive/warehouse/btest2;

Hive使用分桶所用的值进行hash，并用hash结果除以桶的个数做取余运算的方式来分桶，保证每个桶中都有数据，但数据条数不一定相等，如下所示：

hive> dfs -cat /usr/hive/warehouse/btest2/*0_0;hive> dfs -cat /usr/hive/warehouse/btest2/*1_0;hive> dfs -cat /usr/hive/warehouse/btest2/*2_0;

分桶可以获得比分区更高的查询效率，同时分桶也便于对全部数据进行采样处理。下面是对桶取样的操作：

hive> select * from btest2 tablesample(bucket 1 out of 3 on id);

6 多表插入

多表插入是指在同一条语句中，把读取的同一份元数据插入到不同的表中。只要扫描一遍元数据，即可完成所有表的插入操作，效率很高。多表操作示例如下：

hive> create table mutil1 as select id, name form userinfo; # 有数据hive> create table mutil2 like mutil1; # 无数据，只有表结构hive> from userinfo insert overwrite table mutil1    > select id, name insert overwrite table mutil2 select count count(disctinct id), name group by name;

7. 连接

连接是将两个表中在共同数据项上互相匹配的那些行合并起来，HiveQL的连接分为内连接，左向外连接，右向外连接，全外连接和半连接五种。

7.1 内连接(等值连接)

内连接使用比较运算符根据每个表共有的列的值匹配两个表中的行。
例如，检索userinfo和choice表中标识号相同的所有行。

hive> select userinfo.*, choice.* from userinfo join choice on(userinfo.id=choice.userid);

7.2 左连接

左连接的结果集包括left outer子句中指定的左表的所有行，而不仅仅是连接列所匹配的行。若左表某行在右表中没有匹配行，则在相关联的结果集中右表所有选择列为空值。

hive> select userinfo.* choice.* from userinfo left outer join choice on(userinfo.id=choice.userid);

7.3 右连接

右连接是左连接的反向连接，将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将左表返回空值。

hive> select userinfo.* choice.* from userinfo right outer join choice on(userinfo.id=choice.userid);

7.4 全连接

全连接返回左边和右表中的所有行。当某行在另一表中没有匹配行时，则另一个表的选择列表包含空值。如果表之间有匹配行，则整个结果集包含基表的数据值。

hive> select userinfo.*, choice.* from userinfo full outer join choice on(userinfo.id=choice.userid);

7.5 半连接

半连接是Hive特有的。Hive不支持IN操作(其实是不支持IN里面包含子查询)，但是拥有替代方案：left semi join，成为半连接。需要注意的是连接的表不能在查询的列中，只能出现在on子句中。

hive> select userinfo.* from userinfo left semi join choice on(userinfo.id=choice.userid);

8 子查询

标准SQL的子查询支持嵌套select子句，HiveQL对子查询的支持很有限，只能在from引导的子句中出现子查询。如下语句在from子句中嵌套了一个子查询(实现了对教课最多老师的查询)。

hive> select teacher, MAX(class_num)    > from (select teacher, count(classname) as class_num from classinfo group by teacher) subq    > group by teacher;

9 视图操作

目前只有Hive0.6之后的版本才支持视图。

Hive只支持逻辑视图，并不支持物理视图。建立视图可以在MySQL元数据库看到创建的视图表，但是在Hive的数据仓库目录下没有相应的视图表目录。

当查询引用一个视图时，可以评估视图的定义并为下一步查询提供记录集合。这是一种概念的描述，实际上作为优化查询的一部分，Hive可以将视图的定义与查询的定义结合起来，例如从查询到视图所使用的过滤器。

在视图创建的同时确定视图的架构。如果随后再改变基本表(如添加一列)将不会出现在视图的架构中体现。如果基本表被删除或以不兼容的方式被修改，则该视图的查询将无效。

视图只能是读的，不能用于load/insert/alter.

视图可能包含order by和limit子句。如果一个引用了视图的查询也包含这些子句，那么执行这些子句时首先要查看视图语句，然后返回结果按照视图中的语句执行。

创建视图：

hive> create view teacher_classsum as select teacher, count(classname) from classinfo group by teacher;

删除视图：

hive> drop view teacher_classnum;

10 创建函数

hive> create temporary function function_name as class_name;

该语句创建一个由类名实现的函数。在Hive中用户可以使用Hive类路径中的任何类，用户通过执行add files语句将函数类型添加到类路径，并可持续使用该函数进行操作。

删除函数：

hive> drop temporary function function_name;