ITPub博客

首页 > 大数据 > Hadoop > HiveQL详解

HiveQL详解

Hadoop 作者:123456po123456 时间:2013-12-12 09:35:59 0 删除 编辑

HiveQL详解

HiveQL是一种类似SQL的语言, 它与大部分的SQL语法兼容, 但是并不完全支持SQL标准, 如HiveQL不支持更新操作, 也不支持索引和事务, 它的子查询和join操作也很局限, 这是因其底层依赖于Hadoop云平台这一特性决定的, 但其有些特点是SQL所无法企及的。例如多表查询、支持create table as select和集成MapReduce脚本等, 本节主要介绍Hive的数据类型和常用的HiveQL操作。

1.hive client命令
a.hive命令参数


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

b.交互模式


hive> show tables; #查看所有表名
hive> show tables  'ad*'  #查看以'ad'开头的表名
hive> set 命令 #设置变量与查看变量;
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 /user/hive/warehouse/ #查看hive所有文件
hive> dfs  -ls /user/hive/warehouse/ptest #查看ptest文件
hive> source file <filepath> #在client里执行一个hive脚本文件
hive> quit #退出交互式shell
hive> exit #退出交互式shell
hive> reset #重置配置为默认值
hive> !ls #从Hive shell执行一个shell命令

2.操作及函数


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

3.字段类型
Hive支持基本数据类型和复杂类型, 基本数据类型主要有数值类型(INT、FLOAT、DOUBLE)、布尔型和字符串, 复杂类型有三种:ARRAY、MAP 和 STRUCT。
a.基本数据类型
TINYINT: 1个字节
SMALLINT: 2个字节
INT: 4个字节   
BIGINT: 8个字节
BOOLEAN: TRUE/FALSE  
FLOAT: 4个字节,单精度浮点型
DOUBLE: 8个字节,双精度浮点型STRING       字符串

b.复杂数据类型
ARRAY: 有序字段
MAP: 无序字段
STRUCT: 一组命名的字段

4.表类型
hive表大致分为普通表、外部表、分区表三种。
a.普通表


创建表
hive> create table tb_person(id int, name string);

创建表并创建分区字段ds
hive> 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_stu add columns (new_col2 int comment 'a comment');

更改表名
hive> alter table tb_stu rename to tb_stu;

删除表(hive只能删分区,不能删记录或列 )
hive> drop table tb_stu;

对于托管表, drop 操作会把元数据和数据文件删除掉, 对于外部表, 只是删除元数据。如果只要删除表中的数据, 保留表名可以在 HDFS 上删除数据文件:
hive> dfs –rmr /user/hive/warehouse/mutill1/*

将本地/home/hadoop/ziliao/stu.txt文件中的数据加载到表中, stu.txt文件数据如下:
1 zhangsan
2 lisi
3 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 关键字。托管表导入的数据文件可在数据仓库目录“user/hive/warehouse/”中看到。

查看数据


hive> dfs -ls /user/hive/warehouse/tb_stu
hive> dfs -ls /user/hive/warehouse/tb_person

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


create external table tb_record(col1 string, col2 string) row format delimited fields terminated by '	' location '/user/hadoop/input';

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

c.分区表
分区是表的部分列的集合, 可以为频繁使用的数据建立分区, 这样查找分区中的数据时就不需要扫描全表, 这对于提高查找效率很有帮助。
创建分区:create table log(ts bigint,line string) partitioned by(name string);
插入分区:insert overwrite table log partition(name='xiapi') select id from userinfo where name='xiapi';
查看分区:show  partitions log;
删除分区: alter table ptest drop partition (name='xiapi')
备注:通常情况下需要先预先创建好分区,然后才能使用该分区。还有分区列的值要转化为文件夹的存储路径,所以如果分区列的值中包含特殊值,如 '%', ':', '/', '#',它将会被使用%加上 2 字节的 ASCII 码进行转义。

5. sql操作及桶
1). 创建表
首先建立三张测试表:
userinfo表中有两列,以tab键分割,分别存储用户的id和名字name;
classinfo表中有两列,以tab键分割,分别存储课程老师teacher和课程名classname;
choice表中有两列,以tab键分割,分别存储用户的userid和选课名称classname(类似中间表)。

创建测试表:


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

注意:' '相当于一个tab键盘。
显示刚才创建的数据表:
hive> show tables;

2). 导入数据
建表后,可以从本地文件系统或 HDFS 中导入数据文件,导入数据样例如下:
userinfo.txt内容如下(数据之间用tab键隔开):
   xiapi
   xiaoxue
   qingqing

classinfo.txt内容如下(数据之间用tab键隔开):
jack    math
sam    china
lucy    english

choice.txt内容如下(数据之间用tab键隔开):
   math
   china
   english
   china
   english
   english
首先在本地“/home/hadoop/ziliao”下按照上面建立三个文件, 并添加如上的内容信息。

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;

4. 分区


a.创建分区
hive> create table ptest(userid int) partitioned by (name string) row format delimited fields terminated by '	';
b.准备导入数据
xiapi.txt内容如下(数据之间用tab键隔开):
1    
c.导入数据
hive> load data local inpath '/home/hadoop/ziliao/xiapi.txt' overwrite into table ptest partition (name='xiapi');
d.查看分区
hive> dfs -ls /user/hive/warehouse/ptest/name=xiapi;
e.查询分区
hive> select * from ptest where name='xiapi';
f.显示分区
hive> show partitions ptest;
g.对分区插入数据(每次都会覆盖掉原来的数据):
hive> insert overwrite table ptest partition(name='xiapi') select id from userinfo where name='xiapi';
h.删除分区
hive> alter table ptest drop partition (name='xiapi')

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


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

向桶中插入数据, 这里按照用户 id 分了三个桶, 在插入数据时对应三个 reduce 操作,输出三个文件。
hive> insert overwrite table btest2 select * from userinfo;

查看数据仓库下的桶目录,三个桶对应三个目录。
hive> dfs -ls /user/hive/warehouse/btest2;

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


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

分桶可以获得比分区更高的查询效率,同时分桶也便于对全部数据进行采样处理。下面是对桶取样的操作。
hive>select * from btest2 tablesample(bucket 1 out of 3 on id);

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


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

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

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


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

b. 左连接
左连接的结果集包括“LEFT OUTER”子句中指定的左表的所有行, 而不仅仅是连接列所匹配的行。如果左表的某行在右表中没有匹配行, 则在相关联的结果集中右表的所有选择列均为空值。


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

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


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

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


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

e. 半连接
半连接是 Hive 所特有的, Hive 不支持 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;

 

<!-- 正文结束 -->

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/21253833/viewspace-1119889/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2009-03-30