大数据技术之Hive(Hive命令---DML数据操作与基本查询)

---

大数据技术之Hive(Hive命令）

---

---

1.DML数据操作

---

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Select

---

1.1 数据导入

---

1.1.1 向表中装载数据（Load）
1．语法
hive> load data [local] inpath ‘/opt/module/datas/student.txt’ overwrite | into table student [partition (partcol1=val1,…)];
（1）load data:表示加载数据
（2）local:表示从本地加载数据到hive表；否则从HDFS加载数据到hive表
（3）inpath:表示加载数据的路径
（4）overwrite:表示覆盖表中已有数据，否则表示追加
（5）into table:表示加载到哪张表
（6）student:表示具体的表
（7）partition:表示上传到指定分区
2．实操案例
（0）创建一张表

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

（1）加载本地文件到hive

load data local inpath '/opt/student.txt' into table default.student;

（2）加载HDFS文件到hive中
上传文件到HDFS

dfs -put /opt/student.txt /user/atguigu/hive;

加载HDFS上数据

load data inpath '/user/atguigu/hive/student.txt' into table default.student;

（3）加载数据覆盖表中已有的数据
上传文件到HDFS

dfs -put /opt/student.txt /user/atguigu/hive;

加载数据覆盖表中已有的数据

load data inpath '/user/atguigu/hive/student.txt' overwrite into table default.student;

1.1.2 通过查询语句向表中插入数据（Insert）
1．创建一张分区表

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

2．基本插入数据

insert into table  student partition(month='201709') values(1,'wangwu');

3．基本模式插入（根据单张表查询结果）

insert overwrite table student partition(month='201708') select id, name from student where month='201709';

4．多插入模式（根据多张表查询结果）

      from student
      insert overwrite table student partition(month='201707')
      select id, name where month='201709'
      insert overwrite table student partition(month='201706')
      select id, name where month='201709';

1.1.3 查询语句中创建表并加载数据（As Select）
详见2.5.1章创建表。
根据查询结果创建表（查询的结果会添加到新创建的表中）

create table if not exists student3 as select id, name from student;

1.1.4 创建表时通过Location指定加载数据路径
1．创建表，并指定在hdfs上的位置

create table if not exists student5(id int, name string)
row format delimited fields terminated by '\t'
location '/user/hive/warehouse/student5';

2．上传数据到hdfs上

dfs -put /opt/module/datas/student.txt /user/hive/warehouse/student5;

3．查询数据

select * from student5;

1.1.5 Import数据到指定Hive表中
注意：先用export导出后，再将数据导入。

import table student2 partition(month='201709') from  '/user/hive/warehouse/export/student';

---

1.2 数据导出

---

1.2.1 Insert导出
1．将查询的结果导出到本地

insert overwrite local directory '/opt/export/student'
select * from student;

2．将查询的结果格式化导出到本地

insert overwrite local directory '/opt/export/student1'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'             select * from student;

3．将查询的结果导出到HDFS上(没有local)

insert overwrite directory '/user/atguigu/student2'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' 
select * from student;

1.2.2 Hadoop命令导出到本地

dfs -get /user/hive/warehouse/student/month=201709/000000_0
/opt/export/student3.txt;

1.2.3 Hive Shell 命令导出
基本语法：（hive -f/-e 执行语句或者脚本 > file）

[atguigu@hadoop102 hive]$ bin/hive -e 'select * from default.student;' >
 /opt/module/datas/export/student4.txt;

1.2.4 Export导出到HDFS上

export table default.student to '/user/hive/warehouse/export/student';

1.3 清除表中数据（Truncate）

注意：Truncate只能删除管理表，不能删除外部表中数据

truncate table student;

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Select
查询语句语法：

[WITH CommonTableExpression (, CommonTableExpression)*]    (Note: Only available
 starting with Hive 0.13.0)
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
  FROM table_reference
  [WHERE where_condition]
  [GROUP BY col_list]
  [ORDER BY col_list]
  [CLUSTER BY col_list
    | [DISTRIBUTE BY col_list] [SORT BY col_list]
  ]
 [LIMIT number]

---

2.1 基本查询（Select…From）

---

2.1.1 全表和特定列查询

1．全表查询

select * from emp;

2．选择特定列查询

select empno, ename from emp;

注意：
（1）SQL 语言大小写不敏感。
（2）SQL 可以写在一行或者多行
（3）关键字不能被缩写也不能分行
（4）各子句一般要分行写。
（5）使用缩进提高语句的可读性。

1.1.2 列别名
1．重命名一个列
2．便于计算
3．紧跟列名，也可以在列名和别名之间加入关键字‘AS’
4．案例实操
查询名称和部门

select ename AS name, deptno dn from emp;

1.1.3 算术运算符
案例实操
查询出所有员工的薪水后加1显示。

 select sal +1 from emp;

1.1.4 常用函数
1．求总行数（count）

select count(*) cnt from emp;

2．求工资的最大值（max）

select max(sal) max_sal from emp;

3．求工资的最小值（min）

select min(sal) min_sal from emp;

4．求工资的总和（sum）

select sum(sal) sum_sal from emp; 

5．求工资的平均值（avg）

select avg(sal) avg_sal from emp;

1.1.5 Limit语句
典型的查询会返回多行数据。LIMIT子句用于限制返回的行数。

select * from emp limit 5;

---

1.2 Where语句

---

1．使用WHERE子句，将不满足条件的行过滤掉
2．WHERE子句紧随FROM子句
3．案例实操
查询出薪水大于1000的所有员工

select * from emp where sal >1000;

1.2.1 比较运算符（Between/In/ Is Null）
1）下面表中描述了谓词操作符，这些操作符同样可以用于JOIN…ON和HAVING语句中。

2）案例实操
（1）查询出薪水等于5000的所有员工

select * from emp where sal =5000;

（2）查询工资在500到1000的员工信息

select * from emp where sal between 500 and 1000;

（3）查询comm为空的所有员工信息

select * from emp where comm is null;

（4）查询工资是1500或5000的员工信息

select * from emp where sal IN (1500, 5000);

1.2.2 Like和RLike
1）使用LIKE运算选择类似的值
2）选择条件可以包含字符或数字:
% 代表零个或多个字符(任意个字符)。
_ 代表一个字符。
3）RLIKE子句是Hive中这个功能的一个扩展，其可以通过Java的正则表达式这个更强大的语言来指定匹配条件。
4）案例实操
（1）查找以2开头薪水的员工信息

select * from emp where sal LIKE '2%';

（2）查找第二个数值为2的薪水的员工信息

select * from emp where sal LIKE '_2%';

（3）查找薪水中含有2的员工信息

select * from emp where sal RLIKE '[2]';

1.2.3 逻辑运算符（And/Or/Not）
案例实操
（1）查询薪水大于1000，部门是30

select * from emp where sal>1000 and deptno=30;

（2）查询薪水大于1000，或者部门是30

select * from emp where sal>1000 or deptno=30;

（3）查询除了20部门和30部门以外的员工信息

select * from emp where deptno not IN(30, 20);

---

1.3 分组

---

1.3.1 Group By语句
GROUP BY语句通常会和聚合函数一起使用，按照一个或者多个列队结果进行分组，然后对每个组执行聚合操作。
案例实操：
（1）计算emp表每个部门的平均工资

select t.deptno, avg(t.sal) avg_sal from emp t group by t.deptno;

（2）计算emp每个部门中每个岗位的最高薪水

select t.deptno, t.job, max(t.sal) max_sal from emp t group by
 t.deptno, t.job;

1.3.2 Having语句
1．having与where不同点
（1）where针对表中的列发挥作用，查询数据；having针对查询结果中的列发挥作用，筛选数据。
（2）where后面不能写分组函数，而having后面可以使用分组函数。
（3）having只用于group by分组统计语句。
2．案例实操
（1）求每个部门的平均薪水大于2000的部门
求每个部门的平均工资

select deptno, avg(sal) from emp group by deptno;

求每个部门的平均薪水大于2000的部门

select deptno, avg(sal) avg_sal from emp group by deptno having
 avg_sal > 2000;

---

1.4 Join语句

---

1.4.1 等值Join
Hive支持通常的SQL JOIN语句，但是只支持等值连接，不支持非等值连接。
案例实操
（1）根据员工表和部门表中的部门编号相等，查询员工编号、员工名称和部门名称；
select e.empno, e.ename, d.deptno, d.dname from emp e join dept d
on e.deptno = d.deptno;
1.4.2 表的别名
1．好处
（1）使用别名可以简化查询。
（2）使用表名前缀可以提高执行效率。
2．案例实操
合并员工表和部门表

select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno = d.deptno;

1.4.3 内连接
内连接：只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。

select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno = d.deptno;

1.4.4 左外连接
左外连接：JOIN操作符左边表中符合WHERE子句的所有记录将会被返回。

select e.empno, e.ename, d.deptno from emp e left join dept d on e.deptno = d.deptno;

1.4.5 右外连接
右外连接：JOIN操作符右边表中符合WHERE子句的所有记录将会被返回。

select e.empno, e.ename, d.deptno from emp e right join dept d on e.deptno = d.deptno;

1.4.6 满外连接
满外连接：将会返回所有表中符合WHERE语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用NULL值替代。

select e.empno, e.ename, d.deptno from emp e full join dept d on e.deptno = d.deptno;

1.4.7 多表连接
注意：连接 n个表，至少需要n-1个连接条件。例如：连接三个表，至少需要两个连接条件。
1．创建位置表

create table if not exists default.location(
loc int,
loc_name string
)
row format delimited fields terminated by '\t';

2．导入数据

load data local inpath '/opt/location.txt' into table default.location;

3．多表连接查询

SELECT e.ename, d.deptno, l. loc_name
FROM   emp e 
JOIN   dept d
ON     d.deptno = e.deptno 
JOIN   location l
ON     d.loc = l.loc;

大多数情况下，Hive会对每对JOIN连接对象启动一个MapReduce任务。本例中会首先启动一个MapReduce job对表e和表d进行连接操作，然后会再启动一个MapReduce job将第一个MapReduce job的输出和表l;进行连接操作。
注意：为什么不是表d和表l先进行连接操作呢？
这是因为Hive总是按照从左到右的顺序执行的。

1.4.8 笛卡尔积
1．笛卡尔集会在下面条件下产生
（1）省略连接条件
（2）连接条件无效
（3）所有表中的所有行互相连接
2．案例实操

select empno, dname from emp, dept;

6.4.9 连接谓词中不支持or 错误的

select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno = d.deptno or e.ename=d.ename;   错误的

---

1.5 排序

---

1.5.1 全局排序（Order By）
Order By：全局排序，一个Reducer
1．使用 ORDER BY 子句排序
ASC（ascend）: 升序（默认）
DESC（descend）: 降序
2．ORDER BY 子句在SELECT语句的结尾
3．案例实操
（1）查询员工信息按工资升序排列

select * from emp order by sal;

（2）查询员工信息按工资降序排列

select * from emp order by sal desc;

1.5.2 按照别名排序
按照员工薪水的2倍排序
select ename, sal*2 twosal from emp order by twosal;
1.5.3 多个列排序
按照部门和工资升序排序

select ename, deptno, sal from emp order by deptno, sal ;

1.5.4 每个MapReduce内部排序（Sort By）
Sort By：每个Reducer内部进行排序，对全局结果集来说不是排序。
1．设置reduce个数

set mapreduce.job.reduces=3;

2．查看设置reduce个数

set mapreduce.job.reduces;

3．根据部门编号降序查看员工信息

select * from emp sort by empno desc;

4．将查询结果导入到文件中（按照部门编号降序排序）

insert overwrite local directory '/opt/module/datas/sortby-result'
 select * from emp sort by deptno desc;

1.5.5 分区排序（Distribute By）
Distribute By：类似MR中partition，进行分区，结合sort by使用。
注意，Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前。
对于distribute by进行测试，一定要分配多reduce进行处理，否则无法看到distribute by的效果。
案例实操：
（1）先按照部门编号分区，再按照员工编号降序排序。

set mapreduce.job.reduces=3;
insert overwrite local directory '/opt/distribute-result' select * from emp distribute by deptno sort by empno desc;

1.5.6 Cluster By
当distribute by和sorts by字段相同时，可以使用cluster by方式。
cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是升序排序，不能指定排序规则为ASC或者DESC。
1）以下两种写法等价

select * from emp cluster by deptno;
select * from emp distribute by deptno sort by deptno;

注意：按照部门编号分区，不一定就是固定死的数值，可以是20号和30号部门分到一个分区里面去。

---

1.6 分桶及抽样查询

---

1.6.1 分桶表数据存储
分区针对的是数据的存储路径；分桶针对的是数据文件。
分区提供一个隔离数据和优化查询的便利方式。不过，并非所有的数据集都可形成合理的分区，特别是之前所提到过的要确定合适的划分大小这个疑虑。
分桶是将数据集分解成更容易管理的若干部分的另一个技术。
1．先创建分桶表，通过直接导入数据文件的方式
（1）创建分桶表

create table stu_buck(id int, name string)
clustered by(id) 
into 4 buckets
row format delimited fields terminated by '\t';

（2）查看表结构

desc formatted stu_buck;
Num Buckets:            4     

（3）导入数据到分桶表中

load data local inpath '/opt/student.txt' into table stu_buck;

2．创建分桶表时，数据通过子查询的方式导入
（1）先建一个普通的stu表

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

（2）向普通的stu表中导入数据

load data local inpath '/opt/student.txt' into table stu;

（3）清空stu_buck表中数据

truncate table stu_buck;
select * from stu_buck;

（4）导入数据到分桶表，通过子查询的方式

insert into table stu_buck select id, name from stu;

（6）需要设置一个属性

set hive.enforce.bucketing=true;
set mapreduce.job.reduces=-1;
insert into table stu_buck select id, name from stu;

（7）查询分桶的数据

select * from stu_buck;

1.6.2 分桶抽样查询
对于非常大的数据集，有时用户需要使用的是一个具有代表性的查询结果而不是全部结果。Hive可以通过对表进行抽样来满足这个需求。
查询表stu_buck中的数据。

select * from stu_buck tablesample(bucket 1 out of 4 on id);

注：tablesample是抽样语句，语法：TABLESAMPLE(BUCKET x OUT OF y) 。
y必须是table总bucket数的倍数或者因子。hive根据y的大小，决定抽样的比例。例如，table总共分了4份，当y=2时，抽取(4/2=)2个bucket的数据，当y=8时，抽取(4/8=)1/2个bucket的数据。
x表示从哪个bucket开始抽取，如果需要取多个分区，以后的分区号为当前分区号加上y。例如，table总bucket数为4，tablesample(bucket 1 out of 2)，表示总共抽取（4/2=）2个bucket的数据，抽取第1(x)个和第3(x+y)个bucket的数据。
注意：x的值必须小于等于y的值，否则
FAILED: SemanticException [Error 10061]: Numerator should not be bigger than denominator in sample clause for table stu_buck