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

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

## 数据导入 ##

### Load ###

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

**语法**：

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename 
    [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表中。

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNDM3ODc0_size_16_color_FFFFFF_t_70]

### Insert ###

# 插入数据
    insert into table t_user values(0004,'wuming',90),(0005,'bujingyun',28);
    # 插入数据并覆盖表中已存在的数据
    insert overwrite  table t_user values(0004,'wuming',90),(0005,'bujingyun',28);
    # 将单张表的查询结果数据导入到hive表中
    # 单表
    insert into table t_user select id ,name ,age from test01;
    # 多表
    from t_user
    insert overwrite table test01
    select id, name ，age 
    insert overwrite table test02
    select id, name ，age

### As Select ###

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

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

### Location ###

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

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

## 数据导出 ##

### Insert导出 ###

# 将查询的结果导出到本地（未格式化）
    mkdir -p /tmp/test
    hive (d_test)> insert overwrite local directory '/tmp/test'
                 > select * from t_user;
    # 将查询的结果导出到本地（用‘,’作为分隔符）
    hive (d_test)> insert overwrite local directory '/tmp/test'
                 > row format delimited fields terminated by ','
                 > select * from t_user;
    # 将查询的结果导出到HDFS中
    hive (d_test)> insert overwrite  directory '/test'
                 > row format delimited fields terminated by ','
                 > select * from t_user;

### Shell命令导出 ###

语法：

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

### Export导出 ###

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

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

案例：

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

## 查询 ##

语法：

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 [offset,] rows]

准备数据：

cd /opt/soft/test
    vim dept.txt
    # 插入
    10 ACCOUNTING 1700
    20 RESEARCH 1800
    30 SALES 1900
    40 OPERATIONS 1700
    # 
    vim emp.txt
    # 插入
    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
    7369 SMITH CLERK 7902 1980-12-17 800.00 20
    # 创建数据库
    cd /opt/soft/hive-2.3.7/bin
    hive
    # 部门表
    hive (default)> create table if not exists dept(
                  > deptno int,
                  > dname string,
                  > loc int
                  > )
                  > row format delimited fields terminated by '\t';
    # 员工表
    hive (default)> 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';
    # 导入数据
    load data local inpath '/opt/soft/test/dept.txt' into table dept;
    load data local inpath '/opt/soft/test/emp.txt' into table emp;

### 基本查询 ###

# 全表查询
    select * from emp;
    # 特定列全表查询
    select empno, ename, job from emp;
    # 列别名AS,可以省略
    select empno as nu, ename as name, job from emp;
    select empno  nu, ename  name, job from emp;

**算术运算符**

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNDM3ODc0_size_16_color_FFFFFF_t_70 1]

# 算术运算（+ - * / %...）所有的empno加上9
    select empno+9  as nu  from emp;

常用函数

# 总记录数(count)
    select count(*) count from emp;
    # 最大值(max)
    select max(sal) max_sal from emp;
    # 最小值（min)
    select min(sal) min_sal from emp;
    # 总和（sum）
    select sum(sal) sum_sal from emp;
    # 平均值（avg）
    select avg(sal) avg_sal from emp;

**Limit限制返回的行数**

select *  from emp limit 1 ;

**Where行过滤**

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

**比较运算符**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNDM3ODc0_size_16_color_FFFFFF_t_70 2]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNDM3ODc0_size_16_color_FFFFFF_t_70 3]

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

**逻辑运算符**

![在这里插入图片描述][20210111224625948.png]

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

**Group By**

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

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

**Having**

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

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

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

**内连接 INNER JOIN**

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

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

**左外连接LEFT JOIN**

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

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

**右外连接RIGHT JOIN**

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

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

**满外连接 FULL JOIN**

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

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

### 排序 ###

**全局排序ORDER BY**

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

# 按照ID升序排序
    select * from emp order by empno;
    # 按照ID降序排序
    select * from emp order by empno  desc;
    # 多个列进行排序
    select ename, deptno, sal from emp order by deptno, sal;

**内部排序Sort By**

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

# 设置 reduce 个数
    set mapreduce.job.reduces=3;
    # 查看设置 reduce 个数
    set mapreduce.job.reduces;
    # 部门ID降序排
    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 子句可以做这件事。

# 设置 reduce 个数
    set mapreduce.job.reduces=3;
    # 查看设置 reduce 个数
    set mapreduce.job.reduces;
    # 先按照部门进行分区  再通过工资排序，
    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。

select * from emp cluster by deptno;

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNDM3ODc0_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20210109230008987.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNDM3ODc0,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNDM3ODc0_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20210111172049410.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNDM3ODc0,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNDM3ODc0_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20210111222903434.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNDM3ODc0,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNDM3ODc0_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20210111224253267.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNDM3ODc0,size_16,color_FFFFFF,t_70
[20210111224625948.png]: https://img-blog.csdnimg.cn/20210111224625948.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNDM3ODc0_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20210112105305205.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNDM3ODc0,size_16,color_FFFFFF,t_70