HiveQL的使用

1.HiveQL的数据类型

HiveQL不支持更新/索引/事物等操作，子查询和join操作也很局限。

HiveQL的数据类型

基本类型：数值 布尔  字符串

复杂类型：arraymap struct

基本类型可以隐式向上转换  struct可以转换成double

2.HiveQL常用操作-----创建表

create table userinfo(id int,name string)row format delimited fields terminated by '\t';

注解：row format delimited fields terminated by是HiveQL特有的，用来指定数据的分割方式，上述格式表示数据的分割方式是以tab键进行分割，默认格式如下：

row format delimited fields terminated by '\001' collection items terminated by '\002' map keys terminated by '\003' lines terminated by '\n' stored as textfile

注解:

collection items terminated by '\002' 表示集合类型中的数据的分隔方式，ARRY STRUCT MAP中的key/value之间的分隔

map keys terminated by '\003'       针对Map的key内的分隔方式

lines terminated by '\n'      表示行之间已回车分隔

stored as textfile      指定以文本存储

Hive的表：

<1>外部表外部表中的数据不在数据仓库中，在指定的位置，只是在Hive元数据库中注册，建立方式：create external tablename...

<2>托管表托管表中的数据移动到数据仓库的目录下，由Hive管理，      建立方式：cretae table tablename...

3.HiveQL常用操作-----导入数据

表建立完成后，可以从本地文件系统或者HDFS中导入数据文件

load data local inpath '/home/dengpeng/1' overwrite into table userinfo

如果从HDFS中导入，则不要关键字local

load data inpath '/home/dengpeng/1' overwrite into table userinfo

托管表导入的数据文件可在数据仓库目录user/warehouse/<tablename>下看到

Hive的数据导入只是复制或者移动文件，并不对数据的模式进行检查，对数据的模式进行检查要等到查询的时候才进行，这就是Hive采用的“schema on load"加载方式，大大提高了加载数据的效率。

4.HiveQL常用操作-----分区(partition)

<1>定义：

分区是表的部分列的集合，可以为频繁使用的数据建立分区，这样查找分区中的数据时就不需要扫描全表啦，提高查询的效率

<2>建立分区

create table ptest(userid int) partitioned by (name string)row format delimited fields terminated by '\t'

注意：表中的列不能和分区中的列重合

<3>导入数据

load data local inpath '/home/dengpeng/1' into table ptest partition(name='jack');

load data local inpath '/home/dengpeng/jack' overwrite into table ptest partition (name='jack');

<4>查看分区数据

hive>dfs -ls /user/hive/warehouse/ptest/name=jack/;

<5>对分区进行查询

select userid from ptest where name = 'jack';

<6>显示分区

hive>show partitions ptest;

<7>对分区插入数据

insert overwrite table ptest partition(name='jack')select name from userinfo;

5.HiveQL常用的操作-----桶

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

set hive.enforce.bucketing = true

hive>set hive.enforce.bucketing;

 

hive>create table btest2(id int ,name string) clustered by(id) into 3 buckets row format delimited fields terminated by '\t';

这样按照用户id分成了3个桶，那么就对应3个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.HiveQL常用的操作-----多表插入

定义：

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

hive>create table mutil1 as select id,name from userinfo;

hive>craete table mutil2 like mutil1;

hive>from userinfo insert overwrite table mutil1 select id,name insert overwrite table mutil2 select count(distinct id),name group by name;

7.HiveQL常用操作------修改表

重命名表，增加数据列的操作如下：

alert table mutil1 rename to mutil3;

alert table mutil1 add colums(grade string);

hive>describe mutil1;查看表结构

8.HiveQL常用操作------删除表

drop table mutil1;

对于托管表，drop操作会把元数据和数据文件删除掉

对于外部表，drop操作只删除元数据

 

如果只要删除表中的数据，保留表明的话可以在HDFS上删除数据文件。

dfs -rmr /user/hive/warehouse/mutil1/*

9.HiveQL常用操作------连接

内连接select userinfo.*,choice.* from userinfo join choice on(userinfo.id = choice.userid);检索userinfo和choice表中标识号相同的所有行

左向外连接select userinfo.*,choice.* from userinfo left outer join choice on (userinfo.id = choice.usreid);

右向外连接select userinfo.*,choice.* from userinfo right outer join choice on (userinfo.id = choice.userid);

全外连接select userinfo.* from userinfo full outer join choice on (usreinfo.id = choice.usreid);

半连接select userinfo.* from userinfo left semi join choice on (userinfo.id = choice.userid); 

10.HiveQL常用操作------子查询

标准的SQL语句的子查询支持嵌套的查询，而HiveQL的支持有限，只能在from子句中加入子查询

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

11.HiveQL常用操作------创建视图

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

create view teacher_classnum as select teacher,count(classname) from classinfo group by teacher;