526互联

【Hive】DML及DQL语句

发布时间 2023-06-08 22:53:44作者: FlowersandBoys




由于Hive是分布式语言(其本质是操作了HDFS上的文件，因为HSFS是分布式架构，所以就决定了Hive是一门分布式SQL)。丢，我解释的可能不是很专业，将就看看。通常来说存储在HDFS上的数据是不能修改的，但是appendTofile这个linux命令是个特例(hadoop fs -appendToFile /root/2.txt /aa/1.txt)-将数据从L inux上传到HDFS文件系统并将文件内筒追加到HDFS指定文件的末尾，所以Hive是没有数据的删除的SQL命令的，只有增删查，但是又说到DML了，所以数据操作只有增删。(是对数据表的操作)


关于DQL虽说跟MySQL有些许区别，实际上大同小异。




文言文


HQL DML语句介绍


 它指的是 数据操作语言, 主要是用来 操作表数据的, 对表数据进行 增删改操作, 这里特指的是: 如何往Hive表中新增数据.


数据导入:  流向:  Linux, HDFS => Hive  方式:


    

  1. 

    通过 load data方式实现.
    

    2. 

  2. 

    通过 insert + select 方式实现, 相对更灵活.
    

    3. 



load data方式详解:


 格式: ​ load data [local] inpath '源文件的路径' [overwrite] into table 表名; ​ 解释:


    1. 加local指的是本地路径, 即: Linux文件路径.  路径前缀要加 file:///  可以省略不写.
       不加local指的是HDFS路径, 路径前缀要加 hdfs://node1:8020/  可以省略不写.
    2. 不写overwrite是追加写入, 如果写了(overwrite)则是覆盖写入.


insert + select 方式详解:


 格式:  insert [into | overwrite] table 表名 partition(分区字段=值)  select 列1, 列2... from 表名; ​ 细节:


    1. into是追加写入, 后续的table可以省略.
       overwrite是覆盖写入, 后续的table不能省略.
    2. 插入的时候, 要求和查询出的列的个数, 对应的数据类型必须保持一致, 至于列名, 表名无所谓.
    3. insert + select方式底层回转MR程序来执行.


数据导出:


 格式: ​ insert overwrite [local] directory '目的地目录的路径' row format delimited fields terminated by '行格式分隔符' ​ select 列1, 列2... from 表名; ​ 细节:


    

  1. 

    加local是Linux路径, 不加则是HDFS路径.
    

    2. 

  2. 

    导出时, 是覆盖导出, 可以指定字段分隔符.
    

    3. 



数据导入之load方式


-- 不转MR, Linux的源文件还在, 相当于拷贝一份给HDFS.
load data local inpath '/export/data/hivedata/students.txt' into table stu;     
​
-- 不转MR, 相当剪切HDFS文件到另一个HDSF文件路径下.
load data inpath 'hdfs路径' overwrite into table stu;
​
-- 严格来讲有5中导入方式，前面加local表示linux路径，后面加overwrite表示覆盖写入。还有一种是在浏览器直接上传文件。


数据导入之insert+select方式


-- 会转MR程序, into方式是 追加写入, table可以省略.
insert into table stu_insert select * from stu; 
​
-- 会转MR程序, overwrite方式是 覆盖写入, table不可以省略.
insert overwrite table stu_insert select * from stu;   


数据导出


数据导出:  格式:  insert overwrite [local] directory '目的地目录的路径' row format delimited fields terminated by '行格式分隔符' select 列1, 列2... from 表名;  细节:


       1. `加local是Linux路径, 不加则是HDFS路径.`
       2. `导出时, 是覆盖导出, 可以指定字段分隔符.`


-- 将stu的表数据导出到 HDFS目录中.指定分隔符为'$'
insert overwrite directory '/wordcount' row format delimited fields terminated by '$'
select id, name, age from stu;
​
-- 将stu的表数据导出到 Linux目录中.指定分隔符为'#'
insert overwrite local directory '/export/data/hivedata2' row format delimited fields terminated by '#'
select * from stu;


文言文


HQL DQL语句介绍


 概述: ​ HQL DQL指的是 数据查询语句, 主要是对 表数据进行查询操作的. ​ 对比: ​ MySQL中 单表查询语句 完整格式如下: ​ select distinct 列1, 列2... from 表名 ​ where 组前筛选 ​ group by 分组字段 ​ having 组后筛选 ​ order by 排序字段 [asc/desc] ​ limit 起始索引, 数据条数;


Hive中 单表查询语句 完整格式如下:  [CTE表达式]  select distinct | all 列1, 列2... from 表名  where 组前筛选  group by 分组字段  having 组后筛选  order by 排序字段 [asc/desc]  cluster by 分桶排序字段 | distribute by 分桶字段 sort by 排序字段  limit 起始索引, 数据条数;


细节:  1. HQL的代表查询格式 较之于 MySQL的单表查询格式, 有3处不同.  A. HQL 可以支持 CTE表达式.  B. HQL 筛选列的时候可以写 all 或者 distinct C. HQL 支持分桶查询.  2. distribute by 表示分桶, sort by表示桶内排序, 如果 分桶字段 和 排序字段是同一个字段, 则可以用cluster by实现. 即: cluster by 分桶排序字段 = distribute by 分桶字段 + sort by 桶内排序字段


简单查询




基本跟MySQL一样,但是Hive有all关键字，其实跟select * 差不多。


select distinct orderstatus from orders; -- 去重查询订单状态


select all orderstatus from orders; -- 不去重查询，跟不写all是一样的效果


select count(orderid) from orders; -- 查询数据量，注意这里count(*)是查不出来东西的，跟MySQL不一样（count(主键字段名)> count(1)> count(※)




聚合, 分组查询




和MySQL基本一样，这里就不多bb了。




举几个简单的栗子


-- 统计未支付、已支付各自的人数
select
       count(orderno),
       case
            when ispay=0 then '未支付'
            else '已支付' end
from orders group by ispay;
select userid,max(realtotalmoney),realtotalmoney from orders where ispay=1 group by userid;
-- 统计每个用户的平均订单消费额，过滤大于10000的数据
select round(avg(realtotalmoney),0),
       userid
from orders group by userid having avg(realtotalmoney)>10000;
-- 统计每个用户的平均订单消费额
select userid,avg(realtotalmoney) from orders group by userid;
select userid,round(avg(realtotalmoney),1) from orders group by userid;
-- 统计每个用户的平均订单消费额，过滤大于10000的数据
select userid,
       round(avg(realtotalmoney),1) as real_total_monry_avg
from orders group by userid having round(avg(realtotalmoney),1)>10000;


join连接查询




比MySQL多个左半连接查询，以及满外连接。


左半连接，以左表为主，与右表join后，满足条件的数据会保存下来。


满外连接，左右表数据全部留下，对不上的用null填充。




select * from employee e1,employee_address e2 where e1.id=e2.id;  -- 隐式连接
​
select * from employee e1 inner join employee_address e2 on e1.id=e2.id;  -- 显示连接
-- 满外连接 full outer join
select * from employee e1 full join employee_address e2 on e1.id=e2.id;
-- 左半连接 left semi join
select * from employee e1 left semi join employee_address e2 on e1.id=e2.id;


分桶查询


desc formatted stu;  -- 这里发现桶的数量是默认为-1的
set mapreduce.job.reduces = 3;  --手动设置ReduceTask任务数, 相当于设置了 桶的数量
​
select * from stu cluster by id ;  -- 按照id进行分桶，相同的（哈希值%桶数)会放到一个桶中,默认根据id升序排列
select * from stu distribute by id sort by age;  -- 根据id分桶，根据年龄排序
-- 若是根据id分桶，根据根据id排序则
select * from stu cluster by id; --> 等价select * from stu distribute by id sort by id;