【Hive】DML及DQL语句

由于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 方式:

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

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 表名; 细节:

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

数据导入之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;

posted @ 2023-06-08 22:45 FlowersandBoys 阅读(42) 评论(0) 编辑收藏举报

刷新页面返回顶部

FlowersandBoys