Hive HQL数据操作、数据查询

一、HQL 数据操作

---

本方内容来源《Hadoop海量数据处理技术详解与项目实战》人民邮电出版社
建库建表等参考这里
http://blog.csdn.net/xundh/article/details/71404376#t4

1.装载数据

LOAD DATA INPATH '/user/hadoop/o' INTO TABLE test;

如果test表是一个分区表，则在HQL中指定区分：

LOAD DATA INPATH '/USER/HADOOP/O' OVERWRITE INTO TABLE test3 PARTITION(part="a");

从本地直接加载数据表表中

LOAD DATA LOCAL INPATH '/HOME/HADOOP/O' INTO TABLE test;

Hive在加载数据时不会对数据格式进行任何的验证，需要用户自己保证数据格式与表定义的格式一致。

2.通过查询语句向表中插入数据

INSERT OVERWRITE TABLE test SELECT * FROM source;

同样，当test是分区表时，必须指定分区

INSERT OVERWRITE TABLE test PARTITION (part='a') SELECT id,name FROM source;

Hive0.7以后开始支持INSERT INTO，追加的方式。

通过一次查询，产生多个不相交的输出

FROM sourceINSERT OVERWRITE TABLE test PARTITION (part='a')SELECT id,name WHERE id>=0 AND id<100INSERT OVERWRITE TABLE test PARTITION (part='b')SELECT id,name WHERE id>=100 AND id<200...

这样只通过一次对source表的查询，就将符合条件的数据插入test表的各个分区。要使用这个特性，FROM子句要写在前面。

3.利用动态分区向表中插入数据

支持基于查询参数自动推断出需要创建的分区

INSERT OVERWRITE TABLE test PARTITION(time) SELECT id,modify_time FROM source;

Hive 会根据SELECT语句中的最后一个查询字段作为动态分区的依据，而不是根据字段名来选择。如果指定了n 个动态分区的字段，Hive会将select语句中最后n个字段作为动态分区的依据。
上述语句，Hive会根据modify_time不同的值创建分区。
Hive默认没有开启动态分区，开启语句：

set hive.exec.dynamic.partition=true;set hive.exec.dynamic.partition.mode=nostrict; 允许所有分区都是动态的

• hive.exec.max.dynamic.partitions.pernode 每个Mapper或Reducer可以创建的最大分区数
• hive.exec.max.dynamic.partitions 一条动态分区创建语句能够创建的最大分区数
• hive.exec.max.created.files 一个MapReduce作业能够创建的最大文件数

4.通过CTAS加载数据

CREATE TABLE test AS SELECT id,name FROM source;

5.导出数据

INSERT OVERWRITE DIRECTORY '/user/hadoop/r' SELECT * FROM test;INSERT OVERWRITE LOCAL DIRECTORY '/home/hadoop/r' SELECT * FROM test;

如果Hive表中的数据正好满足用户需要的数据格式，可以直接复制

hadoop dfs -cp /user/hive/warehouse/source_table /user/hadoop/

6.删除分区

ALTER TABLE shphonefeature DROP IF EXISTS PARTITION(year = 2015, month = 10, day = 1);删除掉指定分区

二、数据查询

1.SELECT

SELECT col1,col2 FROM table;SELECT t.col1 c1,t.col2 c2 FROM table t;

2.嵌套查询

SELECT l.name,r.course FROM (SELECT id,name FROM left) l JOIN (select id ,course FROM right) r ON l.id=r.id;

3.正则表达式选择列

SELECT 'user.*' FROM test; 如列：user.name,user.age将被返回

4.限制数量

SELECT * FROM test LIMIT 100;

5.需要对某列值处理

SELECT id,name,sex,CASE WHEN sex='M' THEN '男' WHEN sex='F' THEN '女' ELSE '无效数据'ENDFROM student;

6.条件限制

支持常见的=、<>,!=,A IS NULL,A [NOT] LIKE B,A RLIKE B,A REGEXP B等

SELECT * FROM student WHERE age=18;

7.分组、聚合

SELECT COUNT(*) FROM student GROUP BY age;SELECT AVG(age) FROM student GROUP BY classId;SELECT name,AVG(age) FROM student where sex='F' GROUP BY classId HAVING AVG(age)>18;

8.JOIN

INNER JOIN内连接LEFt/RIGHT OUTER JOIN左/右外连接FULL OUTER JOIN 全外连接LEFT-SEMI JOIN 左半连接map-side  JOIN map阶段，将小表读到内存中去，直接在map端join。用户可以通过设置hive.auto.convert.join=true来开启自动优化，通过hive.mapjoin.smalltable.filesize来定义小表的大小，默认为25000000字节。多表JOIN

9.ORDER BY和 SORT BY

当reducer的个数为1时，两者相同。当reducer个数不止一个时，输出结果会有重合。SORT BY是控制每个reducer内的排序。

10.DISTRIBUTE BY和SORT BY

DISTRIBUTE BY可以控制某些数据进入同一个Reducer，这样经过SORT BY以后，可以得到全局排序的结果。SELECT col1,col2 FROM ss DISTRIBUTE BY col1 SORT BY col1,col2;

11.CLUSTER BY

如果在使用DISTRIBUTE BY 和SORT BY语句时，DISTRIBUTE BY和SORT BY涉及的列完全相同，并且采用升序排列，可以使用CLSTER BY代替DISTRIBUTE BY和SORT BY。

12.分桶和抽样

有时候需要对数据进行抽样，Hive提供了对表分桶抽样

SELECT * FROM  test TABLESAMPLE(BUCKET 3 OUT OF 10 ON id);

对于BUCKET x OUT OF y ON z，其中y表示y个桶，x表示取第x个桶，z表示分桶的依据是将z列的哈希值散列再除以y的余数。如果不指定z，可以采取随机列抽样的方式。

SELECT * FROM test TABLESAMPLE(BUCKET 3 OUT OF 10 ON RAND());

在建表时，可以指定为分桶表，那在抽样会更加高效

set hive.enforce.bucketing=true;CREATE TABLE buckettable (id INT) CLUSTERED BY (id) INTO 4 BUCKETS;

该表将被划分为4个桶，然后执行INSERT语句

INSET OVERWRITE TABLE buckettable SELECT * FROM source;

数据将被划分为4个文件存放在表路径下，每个文件代表一个桶。

13.UNION ALL

SELECT r.id,r.priceFROM( SELECT m.id,m.price FROM monday mUNION ALL SELECT t.id,t.price FROM tuesday t)r

Hive不支持直接UNION ALL，必须进行嵌套查询。