Hive入门系列(4)-DML基本操作

忘是亡心i 2023-10-10 12:20 21阅读 0赞

数据导入

Load

hive提供了load命令,可以将数据文件直接导入进表中。

语法

  1. LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename
  2. [PARTITION (partcol1=val1,partcol2=val2 ...)]

(1)load data:表示加载数据
(2)local:表示从本地加载数据到 hive 表;否则从 HDFS 加载数据到 hive 表
(3)inpath:表示加载数据的路径
(4)overwrite:表示覆盖表中已有数据,否则表示追加
(5)into table:表示加载到哪张表
(6)tablename :表示具体的表
(7)partition:表示上传到指定分区

案例
创建一张用户表,并将一份用户数据导入到hive表中。

  1. # 创建表并指定分隔符为,
  2. create table t_user(id int,name string,age int) row format delimited fields terminated by ',';
  3. # 准备输入文件
  4. vim user.txt
  5. 0001,zhangsan,18
  6. 0002,lisi,20
  7. 0003,wangwu,30
  8. # 从本地文件load数据,会复制到HDFS中,未加local 将从HDFS路径中查找,并剪切数据到表目录中
  9. load data local inpath './user.txt' into table t_user ;
  10. # 查询数据
  11. select * from t_user;

在这里插入图片描述

Insert

  1. # 插入数据
  2. insert into table t_user values(0004,'wuming',90),(0005,'bujingyun',28);
  3. # 插入数据并覆盖表中已存在的数据
  4. insert overwrite table t_user values(0004,'wuming',90),(0005,'bujingyun',28);
  5. # 将单张表的查询结果数据导入到hive表中
  6. # 单表
  7. insert into table t_user select id ,name ,age from test01;
  8. # 多表
  9. from t_user
  10. insert overwrite table test01
  11. select id, name age
  12. insert overwrite table test02
  13. select id, name age

As Select

把表中数据导入到另一张空表中,会自动创建数据结构

  1. create table if not exists test4
  2. as select * from t_user;

Location

创建表时通过 Location 指定加载数据路径导入数据。

  1. # 创建HDFS目录,并将数据文件上传到此目录
  2. hive (d_test)> dfs -mkdir /test/;
  3. hive (d_test)> dfs -put ./t_user01.txt /test;
  4. # 创建表并导入数据
  5. hive (d_test)> create external table if not exists t_user01
  6. > (id int,name string ,age int)
  7. > row format delimited fields terminated by ','
  8. > location '/test';
  9. # 查看导入的数据
  10. select * from t_user01;

数据导出

Insert导出

  1. # 将查询的结果导出到本地(未格式化)
  2. mkdir -p /tmp/test
  3. hive (d_test)> insert overwrite local directory '/tmp/test'
  4. > select * from t_user;
  5. # 将查询的结果导出到本地(用‘,’作为分隔符)
  6. hive (d_test)> insert overwrite local directory '/tmp/test'
  7. > row format delimited fields terminated by ','
  8. > select * from t_user;
  9. # 将查询的结果导出到HDFS中
  10. hive (d_test)> insert overwrite directory '/test'
  11. > row format delimited fields terminated by ','
  12. > select * from t_user;

Shell命令导出

语法:

  1. # 把SQL语句查询结果写入到文件中
  2. hive -e "sql" > xxx.txt
  3. # 读取文件xxx.sql中的SQL,并把SQL语句查询结果写入到文件中
  4. hive -f xxx.sql > xxx.txt
  5. # 示例
  6. hive -e 'select * from d_test.t_user;' > /opt/soft/test/user.txt

Export导出

将hive表中的数据导出到外部。
语法:

  1. EXPORT TABLE tablename [PARTITION (part_column="value"[, ...])]
  2. TO 'export_target_path' [ FOR replication('eventid') ]

案例:

  1. export table d_test.t_user to '/test/t_user';

查询

语法:

  1. SELECT [ALL | DISTINCT] select_expr, select_expr, ...
  2. FROM table_reference
  3. [WHERE where_condition]
  4. [GROUP BY col_list]
  5. [ORDER BY col_list]
  6. [CLUSTER BY col_list
  7. | [DISTRIBUTE BY col_list] [SORT BY col_list]
  8. ]
  9. [LIMIT [offset,] rows]

准备数据:

  1. cd /opt/soft/test
  2. vim dept.txt
  3. # 插入
  4. 10 ACCOUNTING 1700
  5. 20 RESEARCH 1800
  6. 30 SALES 1900
  7. 40 OPERATIONS 1700
  8. #
  9. vim emp.txt
  10. # 插入
  11. 7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
  12. 7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
  13. 7566 JONES MANAGER 7839 1981-4-2 2975.00 20
  14. 7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
  15. 7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
  16. 7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
  17. 7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20
  18. 7839 KING PRESIDENT 1981-11-17 5000.00 10
  19. 7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
  20. 7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
  21. 7900 JAMES CLERK 7698 1981-12-3 950.00 30
  22. 7902 FORD ANALYST 7566 1981-12-3 3000.00 20
  23. 7934 MILLER CLERK 7782 1982-1-23 1300.00 10
  24. 7369 SMITH CLERK 7902 1980-12-17 800.00 20
  25. # 创建数据库
  26. cd /opt/soft/hive-2.3.7/bin
  27. hive
  28. # 部门表
  29. hive (default)> create table if not exists dept(
  30. > deptno int,
  31. > dname string,
  32. > loc int
  33. > )
  34. > row format delimited fields terminated by '\t';
  35. # 员工表
  36. hive (default)> create table if not exists emp(
  37. > empno int,
  38. > ename string,
  39. > job string,
  40. > mgr int,
  41. > hiredate string,
  42. > sal double,
  43. > comm double,
  44. > deptno int)
  45. > row format delimited fields terminated by '\t';
  46. # 导入数据
  47. load data local inpath '/opt/soft/test/dept.txt' into table dept;
  48. load data local inpath '/opt/soft/test/emp.txt' into table emp;

基本查询

  1. # 全表查询
  2. select * from emp;
  3. # 特定列全表查询
  4. select empno, ename, job from emp;
  5. # 列别名AS,可以省略
  6. select empno as nu, ename as name, job from emp;
  7. select empno nu, ename name, job from emp;

算术运算符

在这里插入图片描述

  1. # 算术运算(+ - * / %...)所有的empno加上9
  2. select empno+9 as nu from emp;

常用函数

  1. # 总记录数(count)
  2. select count(*) count from emp;
  3. # 最大值(max)
  4. select max(sal) max_sal from emp;
  5. # 最小值(min)
  6. select min(sal) min_sal from emp;
  7. # 总和(sum)
  8. select sum(sal) sum_sal from emp;
  9. # 平均值(avg)
  10. select avg(sal) avg_sal from emp;

Limit限制返回的行数

  1. select * from emp limit 1 ;

Where行过滤

  1. # 查询名字为KING的记录
  2. select * from emp where ename = 'KING';

比较运算符
在这里插入图片描述
在这里插入图片描述

  1. # 查询工资大于2000的员工
  2. select * from emp where sal > 2000;
  3. # 查询 comm 为空的所有员工信息
  4. select * from emp where comm is null
  5. # 查询mgr及comm都为null的员工
  6. select * from emp where mgr <=> comm;
  7. # 查询工资1000到1500的员工
  8. select sal from emp where sal between 1000 and 1500;
  9. # 查询工资是 1500 或 5000 的员工信息
  10. select * from emp where sal in (1000,1500);
  11. # 模糊查询查名字以 A 开头的员工信息
  12. select * from emp where ename LIKE 'A%';

逻辑运算符

在这里插入图片描述

  1. # 查询工资大于1000并且为30部门的员工
  2. select * from emp where sal > 1000 and deptno = 30;
  3. # 查询工资小于等于1000或者工资>=3000的人
  4. select * from emp sal where sal <= 1000 or sal >=3000;
  5. # 查询工资大于等于1000并且部门不是20的员工
  6. select * from emp sal where sal >= 1000 and not deptno = 20 ;

Group By

GROUP BY 语句用于结合聚合函数,根据一个或多个列对结果集进行分组。

  1. # 查询每个部门的总工资数
  2. select deptno,sum(sal) sam_sql from emp group by deptno;
  3. # 计算 emp 每个部门中每个岗位的最高薪水
  4. select deptno,job,max(sal) max_sal from emp group by deptno,job ;

Having

WHERE 关键字无法与聚合函数一起使用。HAVING 子句可以让我们筛选分组后的各组数据。

  1. # 求每个部门的平均薪水大于 2000 的部门
  2. select deptno, avg(sal) avg_sal from emp group by deptno having avg_sal > 1500;

Join
JOIN 子句用于把来自两个或多个表的行结合起来,基于这些表之间的共同字段。类似于SQL join 。
在这里插入图片描述

内连接 INNER JOIN

关键字在表中存在至少一个匹配时返回行。

  1. #
  2. select * from emp inner join dept on dept.deptno = emp.deptno;

左外连接LEFT JOIN

从左表(table1)返回所有的行,即使右表(table2)中没有匹配。如果右表中没有匹配,则结果为 NULL。

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

右外连接RIGHT JOIN

从右表(table2)返回所有的行,即使左表(table1)中没有匹配。如果左表中没有匹配,则结果为 NULL。

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

满外连接 FULL JOIN

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

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

排序

全局排序ORDER BY

对结果集按照一个列或者多个列进行排序。默认按照升序对记录进行排序。如果需要按照降序对记录进行排序,您可以使用 DESC 关键字。全局排序,只有一个 Reducer。这也就是说会有一个所有的数据都通过一个reduce进行处理的过程,对于大数据集,这个过程将消耗很大的时间来执行。

  1. # 按照ID升序排序
  2. select * from emp order by empno;
  3. # 按照ID降序排序
  4. select * from emp order by empno desc;
  5. # 多个列进行排序
  6. select ename, deptno, sal from emp order by deptno, sal;

内部排序Sort By

会在每个reduce中对数据进行排序,也就是执行一个局部排序过程。这可以保证每个reduce的输出数据都是有序的(但并非全局有效)。

  1. # 设置 reduce 个数
  2. set mapreduce.job.reduces=3;
  3. # 查看设置 reduce 个数
  4. set mapreduce.job.reduces;
  5. # 部门ID降序排
  6. select * from emp sort by deptno desc;

Distribute By

distribute by 是控制map端在reduce上是如何区分的,distribute by会把相同的Key发到同一个reduce中。一般情况下可以结合sort by 使用,distribute by必须要写在sort by之前。先进行分组reduce,再进行排序(相当于mapreduce中的分区函数)。

Order by 能够预期产生完全排序的结果,但是它是通过只用一个reduce来做到这点的。所以对于大规模的数据集它的效率非常低。在很多情况下,并不需要全局排序,此时可以换成Hive的非标准扩展sort by。Sort by为每个reducer产生一个排序文件。在有些情况下,你需要控制某个特定行应该到哪个reducer,通常是为了进行后续的聚集操作。Hive的distribute by 子句可以做这件事。

  1. # 设置 reduce 个数
  2. set mapreduce.job.reduces=3;
  3. # 查看设置 reduce 个数
  4. set mapreduce.job.reduces;
  5. # 先按照部门进行分区 再通过工资排序,
  6. select deptno, ename, sal from emp distribute by deptno sort by deptno, sal ;

Cluster By

Cluster by 除了具有distribute by的功能外,还会对该字段进行排序。当distribute by和sort by 字段相同时,可以使用cluster by 代替。不能指定排序规则为 ASC 或者 DESC。

  1. select * from emp cluster by deptno;

发表评论

表情:
评论列表 (有 0 条评论,21人围观)

还没有评论,来说两句吧...

相关阅读