HIVE之DML语句参考笔记

我会带着你远行 2023-10-02 21:39 16阅读 0赞

#### 一、数据导入 ####

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

1.语法

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:表示上传到指定分区  
2.实操  
（1）加载本地文件到 hive

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

（2）加载 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;

注：  
1）put上传时，不会修改元数据，不走MR，count(*)不会变。  
2）insert写入，会走MR，两个值numFiles会变，numRows也会变，会修改元数据，count(*)会变，因为numRows变了。  
3）load上传，同过hive上传文件，会走MR，但走的也是put命令，修改了元数据，其中numFiles会变，numRows不会变。  
4）总结：以后从文件加载数据，尽量用通过load。  
注：从hfds加载到hive数据为何会快，因为修改的是元数据指向的地址指针，并不是真正的移动了文件。

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

多表（多分区）插入模式（根据多张表查询结果），也可实现简单插入，这里不做演示

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.Import 数据到指定 Hive 表中 ######

注意：先用 export 导出后，再将数据导入。
    hive (default)> import table student2
    from '/user/hive/warehouse/export/student';

#### 二、数据导出 ####

###### 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）
    [bym@hadoop102 hive]$ bin/hive -e 'select * from default.student;' >
    /opt/module/hive/data/export/student4.txt;

###### 4.Export 导出到 HDFS 上 ######

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

###### 5.清除表中数据（Truncate） ######

注意：Truncate 只能删除管理表（hdfs上的，但表中内容仍能查到），不能删除外部表中数据。drop也删除不了外部表的数据。
    hive (default)> truncate table student;

#### 三、基本查询（Select…From） ####

###### 基本语法 ######

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.全表查询与特定列查询 ######

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

###### 2.列别名 ######

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

###### 3.常用函数 ######

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;

###### 4.limit子句 ######

典型的查询会返回多行数据。LIMIT 子句用于限制返回的行数。
    hive (default)> select * from emp limit 5;

###### 5. Where 语句 ######

查询出薪水大于 1000 的所有员工
    hive (default)> select * from emp where sal >1000;
    查询 comm 为空的所有员工信息
    hive (default)> select * from emp where comm is null;
    查询工资是 1500 或 5000 的员工信息
    hive (default)> select * from emp where sal IN (1500, 5000);

###### 6.Like和Rlike ######

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

（1）查找名字以 A 开头的员工信息
    hive (default)> select * from emp where ename LIKE 'A%';
    （2）查找名字中第二个字母为 A 的员工信息
    hive (default)> select * from emp where ename LIKE '_A%';
    （3）查找名字中带有 A 的员工信息
    hive (default)> select * from emp where ename RLIKE '[A]';

###### 7.GROUP BY /HAVING ######

###### 8.JOIN(等值JOIN、内连接、左外连接、右外连接、满外连接。多表连接) ######

1.满外连接：将会返回所有表中符合 WHERE 语句条件的所有记录。如果任一表的指定字  
段没有符合条件的值的话，那么就使用 NULL 值替代。  
hive (default)> select e.empno, e.ename, d.deptno from emp e full join  
dept d on e.deptno = d.deptno;

2.多表连接：连接 n 个表，至少需要 n-1 个连接条件。例如：连接三个表，至少需要两个连接  
条件  
**优化**：当对 3 个或者更多表进行 join 连接时，如果每个 on 子句都使用相同的连接键的  
话，那么只会产生一个 MapReduce job。否则，有几个连接则产生几个MapReduce。

###### 9.排序 ######

1.Order By  
按照别名排序

按照员工薪水的 2 倍排序
    hive (default)> select ename, sal*2 twosal from emp order by twosal;

多个列排序

按照部门和工资升序排序
    hive (default)> select ename, deptno, sal from emp order by deptno, sal;

2.Sort By  
**每个 Reduce 内部排序（Sort By）**  
Sort By：对于大规模的数据集 order by 的效率非常低。在很多情况下，并不需要全局排  
序，此时可以使用 sort by。  
Sort by 为每个 reducer 产生一个排序文件。每个 Reducer 内部进行排序，对全局结果集  
来说不是排序。

1）设置 reduce 个数
    hive (default)> set mapreduce.job.reduces=3;
    2）查看设置 reduce 个数
    hive (default)> set mapreduce.job.reduces;
    3）根据部门编号降序查看员工信息
    hive (default)> select * from emp sort by deptno desc;
    4）将查询结果导入到文件中（按照部门编号降序排序）
    hive (default)> insert overwrite local directory 
    '/opt/module/data/sortby-result'
    select * from emp sort by deptno desc;

###### 10.分区（Distribute By） ######

Distribute By： 在有些情况下，我们需要控制某个特定行应该到哪个 reducer，通常是为  
了进行后续的聚集操作。distribute by 子句可以做这件事。distribute by 类似 MR 中 partition  
（自定义分区），进行分区，结合 sort by 使用。对于 distribute by 进行测试，一定要分配多 reduce 进行处理，否则无法看到 distribute  
by 的效果。

案例实操：
    （1）先按照部门编号分区，再按照员工编号降序排序。
    hive (default)> set mapreduce.job.reduces=3;
    hive (default)> insert overwrite local directory 
    '/opt/module/data/distribute-result' select * from emp distribute by 
    deptno sort by empno desc;
    注意：
    ➢ distribute by 的分区规则是根据分区字段的 hash 码与 reduce 的个数进行模除后，
    余数相同的分到一个区。 ➢ Hive 要求 DISTRIBUTE BY 语句要写在 SORT BY 语句之前。

###### 11.Cluster By ######

当 distribute by 和 sorts by 字段相同时，可以使用 cluster by 方式。  
cluster by 除了具有 distribute by 的功能外还兼具 sort by 的功能。但是排序只能是升序  
排序，不能指定排序规则为 ASC 或者 DESC。

以下两种写法等价
    hive (default)> select * from emp cluster by deptno;
    hive (default)> select * from emp distribute by deptno sort by deptno;
    注意：按照部门编号分区，不一定就是固定死的数值，可以是 20 号和 30 号部门分到一
    个分区里面去