Hive（3）

Hive学习（三）

1. 修改表

1.重命名表

语法如下：

ALTER TABLE table_name RENAME TO new_table_name

实例：

hive (default)> alter table dept_partition2 rename to dept_partition3;

2 .增加、修改和删除表分区

见下面分区表的基本操作

3.增加/修改/替换列信息

语法如下：

1.更新列

ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name 
column_type [COMMENT col_comment] [FIRST|AFTER column_name]

2.增加和替换列

ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT 
col_comment], ...)

注：ADD 是代表新增一字段，字段位置在所有列后面(partition 列前)， REPLACE 则是表示替换表中所有字段。

3.实操案例

1.查询表结构

hive> desc dept;

2.添加列

hive (default)> alter table dept add columns(deptdesc string);

3.查询表结构

hive> desc dept;

4.更新列

hive (default)> alter table dept change column deptdesc desc string;

5.查询表结构

hive> desc dept;

6.替换列

hive (default)> alter table dept replace columns(deptno string, dname
string, loc string);

2.删除表

hive (default)> drop table dept;

3.DML 数据操作

1. 数据导入

1. 向表中装载数据（Load）

语法：

hive> load data [local] inpath '数据的 path' [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:表示上传到指定分区

实操案例

1.创建一张表

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

2.加载本地文件到 hive

hive (default)> load data local inpath 
'/opt/module/hive/datas/student.txt' into table default.student;

3.加载 HDFS 文件到 hive 中

上传文件到 HDFS

hive (default)> dfs -put /opt/module/hive/data/student.txt 
/user/atguigu/hive;

加载 HDFS 上数据

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

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

上传文件到 HDFS

hive (default)> dfs -put /opt/module/data/student.txt /user/atguigu/hive;

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

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

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

1.创建一张表

hive (default)> create table student_par(id int, name string) row format 
delimited fields terminated by '\t';

2.基本插入数据

hive (default)> insert into table student_par  values(1,'wangwu'),(2,'zhaoliu');

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

hive (default)> insert overwrite table student_par
 select id, name from student where month='201709';

insert into：以追加数据的方式插入到表或分区，原有数据不会删除insert overwrite：会覆盖表中已存在的数据

注意：insert 不支持插入部分字段

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

hive (default)> 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';

3. 查询语句中创建表并加载数据（As Select）

根据查询结果创建表（查询的结果会添加到新创建的表中）

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

4.创建表时通过 Location 指定加载数据路径

1.上传数据到 hdfs 上

hive (default)> dfs -mkdir /student;
hive (default)> dfs -put /opt/module/datas/student.txt /student;

2.创建表，并指定在 hdfs 上的位置

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

3.查询数据

hive (default)> select * from student5;

5. Import 数据到指定 Hive 表中

注意：先用 export 导出后，再将数据导入。

hive (default)> import table student2
from '/user/hive/warehouse/export/student';

4.数据导出

1. Insert 导出

1.将查询的结果导出到本地

hive (default)> insert overwrite local directory 
'/opt/module/hive/data/export/student'
select * from student;

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

hive(default)>insert overwrite local directory 
'/opt/module/hive/data/export/student1'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
select * from student;

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

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

2. Hadoop 命令导出到本地

hive (default)> dfs -get /user/hive/warehouse/student/student.txt
/opt/module/data/export/student3.txt;

3. Hive Shell 命令导出

基本语法：（hive -f/-e 执行语句或者脚本 > file）

[root@hadoop100 hive]$ bin/hive -e 'select * from default.student;' >
/opt/module/hive/data/export/student4.txt;

4.Export 导出到 HDFS 上

(defahiveult)> export table default.student 
to '/user/hive/warehouse/export/student';

export 和 import 主要用于两个 Hadoop 平台集群之间 Hive 表迁移。

5. Sqoop 导出

后续课程专门讲

6.清除表中数据（Truncate）

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

hive (default)> truncate table student;

4.查询

查询基本语法:

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]

1.基本查询（Select…From）

1.全表和特定列查询

1.数据准备

dept:

10 ACCOUNTING 1700
20 RESEARCH 1800
30 SALES 1900
40 OPERATIONS 1700

emp：

7369 SMITH CLERK 7902 1980-12-17 800.00 20
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-4-2 2975.00 20
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20
7839 KING PRESIDENT 1981-11-17 5000.00 10
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
7900 JAMES CLERK 7698 1981-12-3 950.00 30
7902 FORD ANALYST 7566 1981-12-3 3000.00 20
7934 MILLER CLERK 7782 1982-1-23 1300.00 10

1.创建部门表

create table if not exists dept(
deptno int,
dname string,
loc int
)
row format delimited fields terminated by '\t';

2.创建员工表

create table if not exists emp(
empno int,
ename string,
job string,
mgr int,
hiredate string, 
sal double, 
comm double,
deptno int)
row format delimited fields terminated by '\t';

3.导入数据

load data local inpath '/opt/module/datas/dept.txt' into table
dept;
load data local inpath '/opt/module/datas/emp.txt' into table emp;

4.全表查询

hive (default)> select * from emp;
hive (default)> select empno,ename,job,mgr,hiredate,sal,comm,deptno from 
emp ;

5.选择特定列查询

hive (default)> select empno, ename from emp;

注意：

（1）SQL 语言大小写不敏感。

（2）SQL 可以写在一行或者多行

（3）关键字不能被缩写也不能分行

（4）各子句一般要分行写。

（5）使用缩进提高语句的可读性。

2. 列别名

1.重命名一个列

2.便于计算

3.紧跟列名，也可以在列名和别名之间加入关键字‘AS’

案例实操:

hive (default)> select ename AS name, deptno dn from emp;

3.算术运算符

A+B A 和 B 相加

A-B A 减去 B

A*B A 和 B 相乘

A/B A 除以 B

A%B A 对 B 取余

A&B A 和 B 按位取与

A|B A 和 B 按位取或

A^B A 和 B 按位取异或

~A A 按位取反

4.常用函数

1.求总行数（count）

hive (default)> select count(*) cnt from emp;

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

hive (default)> select max(sal) max_sal from emp;

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

hive (default)> select min(sal) min_sal from emp;

4.求工资的总和（sum）

hive (default)> select sum(sal) sum_sal from emp;

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

hive (default)> select avg(sal) avg_sal from emp;

5.Limit 语句

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

hive (default)> select * from emp limit 5;
hive (default)> select * from emp limit 2;

6. Where 语句

1.使用 WHERE 子句，将不满足条件的行过滤掉

2.WHERE 子句紧随 FROM 子句

3.案例实操

查询出薪水大于 1000 的所有员工

hive (default)> select * from emp where sal >1000;

注意：where 子句中不能使用字段别名。

7. 比较运算符（Between/In/ Is Null）

1.下面表中描述了谓词操作符，这些操作符同样可以用于 JOIN…ON 和 HAVING 语句中。

A=B 基本数据类型如果 A 等于 B 则返回 TRUE，反之返回 FALSE

A<=>B 基本数据类型如果 A 和 B 都为 NULL，则返回 TRUE，如果一边为 NULL，返回 False A<>B, A!=B 基本数据类型 A 或者 B 为 NULL 则返回 NULL；如果 A 不等于 B，则返回 TRUE，反之返回 FALSE

A<B 基本数据类型 A 或者 B 为 NULL，则返回 NULL；如果 A 小于 B，则返回 TRUE，反之返回 FALSE

A<=B 基本数据类型 A 或者 B 为 NULL，则返回 NULL；如果 A 小于等于 B，则返回 TRUE，反之返回 FALSE

A>B 基本数据类型 A 或者 B 为 NULL，则返回 NULL；如果 A 大于 B，则返回 TRUE，反之返回 FALSE

A>=B 基本数据类型 A 或者 B 为 NULL，则返回 NULL；如果 A 大于等于 B，则返回 TRUE，反之返回 FALSE

A [NOT] BETWEEN B AND C 基本数据类型如果 A，B 或者 C 任一为 NULL，则结果为 NULL。如果 A 的值大于等于 B 而且小于或等于 C，则结果为 TRUE，反之为 FALSE。如果使用 NOT 关键字则可达到相反的效果。

A IS NULL 所有数据类型如果 A 等于 NULL，则返回 TRUE，反之返回 FALSE

A IS NOT NULL 所有数据类型如果 A 不等于 NULL，则返回 TRUE，反之返回 FALSE IN(数值 1, 数值 2) 所有数据类型使用 IN 运算显示列表中的值

A [NOT] LIKE B STRING 类型 B 是一个 SQL 下的简单正则表达式，也叫通配符模式，如果 A 与其匹配的话，则返回 TRUE；反之返回 FALSE。B 的表达式说明如下：‘x%’表示 A 必须以字母‘x’开头，‘%x’表示 A 必须以字母’x’结尾，而‘%x%’表示 A 包含有字母’x’,可以位于开头，结尾或者字符串中间。如果使用 NOT 关键字则可达到相反的效果。

A RLIKE B, A REGEXP B STRING 类型 B 是基于 java 的正则表达式，如果 A 与其匹配，则返回 TRUE；反之返回 FALSE。匹配使用的是 JDK 中的正则表达式接口实现的，因为正则也依据其中的规则。例如，正则表达式必须和整个字符串 A 相匹配，而不是只需与其字符串匹配。

案例实操

查询出薪水等于 5000 的所有员工

hive (default)> select * from emp where sal =5000;

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

hive (default)> select * from emp where sal between 500 and 1000;

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

hive (default)> select * from emp where comm is null;

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

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

8. Like 和 RLike

1.使用 LIKE 运算选择类似的值

2.选择条件可以包含字符或数字:

% 代表零个或多个字符(任意个字符)。

_ 代表一个字符。

3.RLIKE 子句

RLIKE 子句是 Hive 中这个功能的一个扩展，其可以通过 Java 的正则表达式这个更强大的语言来指定匹配条件。

4.案例实操

查找名字以 A 开头的员工信息

hive (default)> select * from emp where ename LIKE 'A%';

查找名字中第二个字母为 A 的员工信息

hive (default)> select * from emp where ename LIKE '_A%';

查找名字中带有 A 的员工信息

hive (default)> select * from emp where ename RLIKE '[A]';

9.逻辑运算符（And/Or/Not）

AND 逻辑并

OR 逻辑或

NOT 逻辑否