Hive数据操作

Hive数据操作

1. 数据的装载

• 向管理表中装载数据

(1) 装载数据的方式：

​ overwrite into 为覆写， 单独使用 into 为追加写入。

（2）装载数据的过程：

​ 使用本地路径装载时，执行过程为复制。从HDFS路径装载数据，执行过程为移动。

#装载本地系统上的数据文件(本地路径为绝对路径)
hive>load data local inpath '/usr/local/word.txt' [overwrite] into table tb_name;

#从HDFS路径装载数据文件
hive>load data inpath '/user/hive/data/words.txt' [overwirte] into table tb_name;

关系型数据库插入数据，有一个检验过程，检验插入数据是否与表的字段类型匹配；而数据仓库中加载数据时，没有检查过程的，而是直接将数据文件复制或移动到对应的表目录中。

• 通过查询语句得到的数据集，将数据集插入到指定的表中

（1）overwrite 为覆写, into 为追加写入；

（2）insert (overwrite|into)都会触发一个mapreduce应用。

#覆写
hive>insert overwrite table tb_name select * from tbl_name2;

#追加
hive>insert into table tb_name select * from tbl_name2;

• 在创建表的同时，加载数据(CTAS)

1. 创建新表的过程中，会使用查询的结果集对应的字段名和数据类型作为新表的字段名和数据类型，同时，默认新表的存储格式为 textFile。
2. 这个功能的常见情况是从一个大的宽表中选取部分需要的数据集。

hive>create table tb_name  as select field1, field2, ... from tbl_name;

2. 数据表中数据的导出，导入

（1）导出方式

使用Mapreduce应用导出数据.

overwrite 为覆写

• 将数据导出到本地文件系统(使用绝对路径)

#将查询的结果集覆盖写入到本地文件中，文件名自动命名为00000*_0
hive>insert overwrite  local directory '/home/data'select * from tb_name;

• 将数据导出到HDFS目录（绝对路径）

hive>insert overwrite directory '/user/hive/'  select * from tb_name;

• export导出数据到指定目录(hdfs路径)

#使用export导出数据到指定目录
hive>export table tb_name to '/user/hive/'

（2）导入方式

使用import命令函数，将HDFS上的数据集，导入到指定的HDFS目录下，并生成一个新的数据表（基本表）

语法格式：

IMPORT [ [EXTERNAL] TABLE new_tablename FROM ‘data_path’ [LOCATION ‘import_target_path’]

#将HDFS目录/input/data中的数据导入并生成新的内部表emp_new
hive>import table emp_new from ‘/input/data’;

3. Hive Shell

• 语法格式：

hive [-hiveconf x=y] * [<-i filename>] * [<-f filename> | <-e query-string>] [-S] [-d]

• 参数说明：-hiveconf x=y : 配置Hive/Hadoop参数-i : 从文件初始化HQL

-f : 执行HQL脚本，与 –i 参数作用类似

-e : 从命令行执行指定的HQL

-S : 静默模式，在交互模式中仅输出主要信息，也就是输出中会去掉“OK”和 “Time taken…”

-d : 自定义变量，以便在HQL脚本中使用

• 命令行示例

[]#hive -e "select * from tb_name;"

• 脚本文件示例

# 首先将以下SQL语句写入到一个文件中，文件命名为 query.hql
use empdb;select count(1) from emp;# 使用命令“hive –f”执行脚本$hive –f query.hql 

• 使用Hive命令行自定义变量，完成通用查询

# 首先修改query.hql文件为以下内容
use ${db};select count(1) from ${table};# 使用命令“hive –f”执行脚本$hive –f query.hql  -d db=empdb  -d table=emp

4. 高级查询

（1）单表查询语句中使用的子句

• 查看函数的命令

#当前会话中加载的hive的函数
hive>show functions;

#查看指定函数的简单描述, extended 详细描述。
hive>desc  function  extended  func_name;  

• where子句

where子句

(1)where 条件必须是布尔表达式，用于过滤结果集（参考示例4-1）

(2)where子句中不允许出现子查询，如：select * from tabl_name where fields_name in (select expr) 是无法执行的。

(3)多用于在查询语句中对结果集的过滤

(4)如果查询的结果集是使用了group by 进行了分组，那么不能使用where子句进行过滤，而是使用having子句过滤结果集。

hive>select * from tb_name where id < 100;

• all, distinct表达式

(1) select查询语句中默认返回所有结果集，默认子句为ALL(缺省)

（2） select查询语句大多数情况使用distinct子句对查询的结果集进行去重操作

hive>select distinct id from tb_name;

• 嵌套查询

在外层查询语句的from子句中，嵌套一个查询语句。实际开发中，不建议嵌套超过3层。

hive>select * from select id, name from tb2 as tmprel;

• GROUP BY 分组子句

（1）Hive 支持使用 GROUP BY 进行分组聚合操作，

（2）按照指定的字段进行分组，那么select子句后一定要有该字段。

（3）分组查询通常与聚合函数一起使用。

hive> select id, avg(score) from student group by id;

• 排序子句sort by 或 order by

(1)默认排序顺序都是升序（asc）

(2)order by 子句排序是对最终结果集进行排序，或者是对全局进行排序。

​ sort by 子句排序，是对mapreduce应用执行过程中，在单一的reduce阶段进行排序。是局部的排序，不能保证最终的结果集是有序的。

(3)在没有指定多个reduer的情况下，order by 与 sort by没有区别。

hive>select deptno, empno from emp order by deptno asc, sal desc;

• distribute by 结合sort by进行局部排序

hive> select * from poly distribute by id  sort by id,salary;

• cluster by替代distribute by 结合sort by进行排序，最终结果为全局排序。

当 distribute by和sort by涉及的列完全相同，并且采用升序排列时，使用Cluster by替代，且完成全局排序。

• having 子句

可以使用 HAVING 对分组数据进行过滤。在使用group by进行分组查询时，通常使用having子句进行数据集的过滤。

select deptno count(*) from emp group by deptno having sum(sal)>9000;

#where过滤用于select查询结果集的过滤，having与group by中的数据过滤
select sex,avg(age)  from big  where id > 1 group by sex having avg(age) > 20;

• Like 和 RLike子句

like：’%’ 代表零个或多个字符（任意个字符）；’_’ 代表一个字符。

RLike：是 Hive 中对 “Like” 的扩展，可以通过 Java 的正则表达式来指定匹配条件。

hive>select * from emp where sal like '88_';
hive>select * from emp where sal like '8%';

（2）多表联查

关键字：A JOIN B ON A.id = B.id

JOIN 语句的关联条件必须用 ON 指定，不能用 WHERE 指定，否则就会先做笛卡尔积，再过滤，这会导致你得不到预期的结果

分类：内连接，外连接（左外连接，右外连接，全连接），笛卡尔乘积

注意：当查询的结果中没有对应数据时，会以NULL值填充。

• 内连接查询，用于对多个表求交集。

hive>select * from A join B ON A.id = B.id;

• 左外连接(1) 求表A中的所有数据，并且表B中包含的A表中的数据

hive>select * from A left join B ON A.key = B.key;

​ (2)求表A中不包含表B中的所有数据。

hive>select * from A left join B ON A.key = B.key where B.key IS NULL;

• 右外连接

（1）求表B中的所有数据，并且表A中包含的表B中的数据

hive>select * from A right join B ON A.key = B.key;

(2)求表B中不包含表A中的所有数据

hive>select * from A right join B ON A.key = B.key where A.key IS NULL;

• 全连接

全连接用于求多个表中数据的并集，即多个表中的全部数据。

hive>select * from A full outer join B ON A.key = B.key;

(1) 去除表a和表b中的相同数据

hive>select * from A full outer join B ON A.key = B.key where A.key IS NULL or B.key is null;

联合查询 union all

语法结构：

select * from tb_name union all select * from tb_name2 ;

使用联合查询时，每一个union子查询都必需具有相同的列，而且对应的每个字段的字段类型必须是一致的，得到的结果是多个表中的数据的合集。