开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> 大数据 -> HIVE的 “DDL其他语法、DML语法、DQL语法、高阶排序、join相关内容” -> 正文阅读

[大数据]HIVE的 “DDL其他语法、DML语法、DQL语法、高阶排序、join相关内容”

1、DDL其他语法说明

都对库的相关操作

-- 创建库的语法：
create database [if not exists] database_name;

-- 查看库的详细信息：携带上extended会看到更为详细的库信息
describe database [extended] db_name;
-- 查看所有的库
show database；

-- 如何使用库
use db_name;
-- 如何删除库
drop database db_name；

对于表的相关的操作:

show tables； 查看所有的表
describe formated tab_name;  查看一个表的详细信息
desc formatted table_name;
desc 表名；  查看表的基本信息

'如何删除表'
drop table 表名；

'清空表'
truncate table 表名；  先删除表 然后重新建表操作（此操作仅能针对内部表）

以下的对表结构修改操作, 整体是一个了解, 在hive中几乎不需要对表进行结构修改:
--1、更改表名
ALTER TABLE table_name RENAME TO new_table_name;
--2、更改表属性
ALTER TABLE table_name SET TBLPROPERTIES (property_name = property_value, ... );
--更改表注释
ALTER TABLE student SET TBLPROPERTIES ('comment' = "new comment for student table");
--3、更改SerDe属性
ALTER TABLE table_name SET SERDE serde_class_name [WITH SERDEPROPERTIES (property_name = property_value, ... )];
ALTER TABLE table_name [PARTITION partition_spec] SET SERDEPROPERTIES serde_properties;
ALTER TABLE table_name SET SERDEPROPERTIES ('field.delim' = ',');
--移除SerDe属性
ALTER TABLE table_name [PARTITION partition_spec] UNSET SERDEPROPERTIES (property_name, ... );

--4、更改表的文件存储格式 该操作仅更改表元数据。现有数据的任何转换都必须在Hive之外进行。
ALTER TABLE table_name  SET FILEFORMAT file_format;
--5、更改表的存储位置路径
ALTER TABLE table_name SET LOCATION "new location";

--6、更改列名称/类型/位置/注释
CREATE TABLE test_change (a int, b int, c int);

ALTER TABLE test_change CHANGE a a1 INT;

ALTER TABLE test_change CHANGE a1 a2 STRING AFTER b;

ALTER TABLE test_change CHANGE c c1 INT FIRST;

ALTER TABLE test_change CHANGE a1 a1 INT COMMENT 'this is column a1';

--7、添加/替换列
--使用ADD COLUMNS，您可以将新列添加到现有列的末尾但在分区列之前。
--REPLACE COLUMNS 将删除所有现有列，并添加新的列集。
ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type,...);

show 相关的语法

-- 查看所有库
show databases;

-- 查看所有的表
show tables;

-- 查看所有的分区
show partitions;

-- 显示所有 hive支持的函数信息
show functions;

对分区的相关的操作:

-- 如何添加分区
ALTER TABLE table_name add partition (dt='20170101');  -- 一次添加一个分区
ALTER TABLE table_name ADD 
 					   PARTITION (dt='2008-08-08', country='us')
                       PARTITION (dt='2008-08-09', country='us') ; -- 一次性添加多个分区
-- 自动修复分区信息
MSCK [REPAIR] TABLE table_name [ADD/DROP/SYNC PARTITIONS];

-- 删除分区:
ALTER TABLE table_name drop [IF EXISTS] partition (dt='2008-08-08', country='us');

-- 查看某个表一共有那些分区
show partitions table_name;

分区操作的需求:

	假设现在业务端每天会将用户订单数据存储在HDFS上,以 datestr=2021-08-21 方式存储每天的数据, 这一份数据大家都需要共享,不能移动位置 请你在hive上建表映射这份数据

准备工作:  
第一步: 准备一个订单数据 order.txt 放置在 node1的 /root/hivedata/
o01 p01 1 800
o02 p02 2 1400
o03 p01 5 4000
o04 p03 3 900
o05 p02 10 7000

第二步: 在HDFS上创建一下这么几个目录
hdfs dfs -mkdir -p /order/datestr=2021-09-14
hdfs dfs -mkdir -p /order/datestr=2021-09-15
hdfs dfs -mkdir -p /order/datestr=2021-09-16

第三步: 将order.txt数据分别放置在以上的三个目录中, 用于模拟每天的订单数据
hdfs dfs -put order.txt /order/datestr=2021-09-14
hdfs dfs -put order.txt /order/datestr=2021-09-15
hdfs dfs -put order.txt /order/datestr=2021-09-16

建表进行处理操作:

1) 在hive中进行建表: 外部表 + 分区表  + location指定加载数据路径

create external table order_hive(
    oid string,
    pid string,
    num int,
    price int
)
partitioned by (datestr string)
row format delimited fields terminated by ' '
location '/order';

思考: 是否有数据呢?  没有  
	原因: 当前hive压根就不知道有那些分区信息, 缺失分区信息元数据

2) 添加分区信息: 相当于在添加分区元数据信息
ALTER TABLE order_hive add partition (datestr='2021-09-14');
ALTER TABLE order_hive ADD PARTITION (datestr='2021-09-15') PARTITION (datestr='2021-09-16') ;

3) 测试数据是否加载成功
select * from order_hive;  
发现, 是没有任何问题的

但是: 如果目录下有100个分区呢?  怎么解决, 如果用刚刚的这种操作是不是有点繁琐

此时可以采用自动修复分区操作:  REPAIR 批量运行操作 
MSCK REPAIR  TABLE order_hive;

先将分区信息删除:
ALTER TABLE order_hive drop  partition (datestr='2021-09-14');
ALTER TABLE order_hive drop  partition (datestr='2021-09-15');
ALTER TABLE order_hive drop  partition (datestr='2021-09-16');

2、DML相关的语法内容

DML: 数据操作语句 , 主要是用于对数据进行 增 删 改的操作,
'但是在hive中更多描述的是增 没有删和改的操作, 或者说hive默认情况下压根不支持 删除数据和修改数据的操作'

2.1、Load的相关内容

load data [local] inpath '路径' [overwrite] into table table_name [partition(分区字段1=值1,分区字段2=值2.........)]

'说明：'
	1） 如果load命令添加local 表示从hiveserver2服务所在节点的本地路径上加载数据，如果没有使用local，表示从hdfs上路径导入数据
	2）overwrite：'覆盖操作'，表示如果添加了此字段，表示将原有的数据进行了覆盖操作（没有分区，覆盖所有，如果有分区，就覆盖对应分区下的数据）

' 新的特性： '
	在hive3.X以上，load data 在对分桶表进行插入数据的时候，会执行MR进行分桶操作，而老版本是不支持的
	在老版本中，load操作不会执行执行MR，底层仅仅是在执行hdfs的 put 或者 mv的命令
	在老版本 仅支持 内部表  外部表 和 分区表， '分桶不支持（或者说：不良好 ， 没有分区效果）'

2.2、insert + select 导入方式

这种方式, 将select语句的结果导入到指定的表中

insert into|overwrite table 表名 [partition(分区字段1=值1,分区字段2=值2...)]
select ....

注意事项：
	1）需要保证查询结果列的数目和需要插入数据表格的列数目一致。并且顺序也保持一致，建议字段名称也保持一致
	2) 如果查询出来的数据类型和插入表格对应的列数据类型不一致，将会进行转换，但是不能保证转换一定成功，转换失败的数据将会为NULL, 建议执行的保证每一列对接的类型都是一致的,如果不一致使用显示转换进行转换
	3) 此种方式会运行MR来处理的, 这种方式的导入支持所有类型的表

多重插入数据的操作: (了解) 一次扫描, 多次插入

from student
insert overwrite table student_insert1
select num
insert overwrite table student_insert2
select name;

相当于:

insert overwrite table student_insert1
select num from student;

insert overwrite table student_insert2
select name from student;

2.3、insert + directory导出数据

语法结构：
insert overwrite [local] directory '路径地址'
	[row format row_format][stored as file_format]
select ..... from .....

相关的案例:

需求： 将hive中order_hive 中数据导出到hdfs的/order_out目录下

insert overwrite directory '/order_out'
select * from order_hive;

--希望导出到linux的 /root/hivedata/order_linux_out ,, 同时希望分隔符号为 \t 
insert overwrite local directory '/root/hivedata/order_linux_out'
row format delimitied fields terminated by '\t'
select * from order_hive;

-- 设置导出文件存储格式: 默认为textFile(普通文本)   尝试导出:ORC
insert overwrite local directory '/root/hivedata/order_linux_out2'
row format delimitied fields terminated by '\t' stored as ORC 
select * from order_hive;

3、DQL相关的内容

DQL: 数据查询语句, 主要用于对数据表的数据查询操作 , DQL语句不会对表中数据有任何的影响

[WITH CommonTableExpression (, CommonTableExpression)*] 
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]


说明:
	with关键词: 在后期主要用于提前通用的数据集(主要是以子查询的方式存在)
	CLUSTER BY: 局部排序操作 , 是 DISTRIBUTE BY  和 SORT BY一种简写操作
	DISTRIBUTE BY  和 SORT BY: 局部排序操作

去重操作: distinct

SQL:
	select distinct id,name   from xxx;

**where语句: **

' 注意: 不允许使用聚合函数的, 因为where是对from后的表进行筛选过滤的操作, 而不是聚合操作 '

分区查询:

注意: 
	在执行查询的时候, 如果操作的表是一张分区表, 建议能带上分区字段, 尽量带上, 因为这样可以减少读取数据扫描量, 从而提升效率, 我们将这种行为称为  分区裁剪
	在hive的严格模式, 要求如果操作的是一张分区表, 然后没有携带分区字段, 严格模式会限制此SQL的执行(数据量足够大)

分组操作: group by

注意:
	1) 分组必聚合
	2) 在执行分组后, select后面只能出现分组中存在的字段, 不能出现分组中不存在的字段, 如果必须用, 请使用聚合函数包裹

having: 过滤

注意:
	1) 一般应用在分组后, 对分组聚合后的结果数据进行过滤操作
	2) having中可以应用聚合函数

整个SQL执行的顺序:

1) 先执行 from 表  (加载表)
2) 接着执行where操作, 对加载出来的数据进行过滤操作
3) 然后执行group by 操作
4) 执行select中聚合函数操作
5) 再执行 having 对聚合后结果进行过滤处理
6) 再执行 排序操作操作
7) 执行select操作, 获取结果
8) 最后执行limit操作, 获取指定数据

4. 高阶查询

4.1 排序相关的内容:

在hive中跟数据排序相关的关键词：sort / order / cluster / distribute by

order by: 全局排序的操作

全局排序: '指的对整个数据集进行排序操作'
注意:
	在进行全局排序中, 翻译的'MR只能允许一个reduce程序'
	由于只有一个reduce, 此reduce需要接受全局所以的数据, 压力比较大的, 因此hive的严格模式要求, 在使用order by 必须和 limit 组合使用, 以减少reduce的压力, 从而提升效率
	
假设强制让hive翻译的MR 运行多个reduce程序:
	set mapreduce.job.reduces =2;

distribute by 列名1,列名2…SORT BY 列名1,列名2… :
- 作用: distribute by 按照谁来进行分桶key sort by 按照谁来对每个桶数据进行排序操作

设置reduce的数量: 默认为 -1(自动  hive会根据读取的数据量的大小, 自动选择需要运行多少个reduce)
set mapreduce.job.reduces =1;
select * from hero_parti_1 where role in('cike','tanke')  distribute by role sort by hp_max;

说明: 当reduce只有1个的时候, sort by 和order by是一样的

set mapreduce.job.reduces =2;
select * from hero_parti_1 where role in('cike','tanke')  distribute by hp_max sort by hp_max ;


好处: 局部支持可以运行多个reduce程序, 执行效率要比order by 强 

适用于: 只需要对一部分数据进行排序的场景中

CLUSTER BY : 是 distribute by和 SORT BY简写
- 当distribute by的字段和 sort by的字段是一个字段的时候, 同时进行的升序排序, 此时可以简写 CLUSTER BY

distribute by和 SORT BY 的 SQL: 
select * from hero_parti_1 where role in('cike','tanke')  distribute by hp_max sort by hp_max ;

等同于:
select * from hero_parti_1 where role in('cike','tanke') CLUSTER BY hp_max  ;

4.2 union相关的内容

UNION用于将来自多个SELECT语句的结果合并为一个结果集(上下合并)

select_statement UNION [ALL | DISTINCT] select_statement UNION [ALL | DISTINCT] select_statement ...

说明:
	使用DISTINCT关键字与只使用UNION默认值效果一样，都会删除重复行。
	使用ALL关键字，不会删除重复行，结果集包括所有SELECT语句的匹配行（包括重复行）。

4.3 Common Table Expressions（CTE）

公用表表达式（CTE）是一个临时结果集

示例:
	with q1 as (select sno,sname,sage from student where sno = 95002)
	select *
	from q1;
	
	相当于:
	select *
	from (select sno,sname,sage from student where sno = 95002) q1;


示例2:
	-- chaining CTEs 链式
	with q1 as ( select * from student where sno = 95002),
     	 q2 as ( select sno,sname,sage from q1)
	select * from (select sno from q2) a;

示例3: select * from teacher_1 union all select * from teacher_1;
	转换为 with的操作:
    with t1 as (select * from teacher_1),
         t2 as (select * from teacher_1),
         t3 as (select  * from  t1 union all  select * from t2)
    select *  from t3;
    
 
说明: 对于 cte的SQL语句, 可以完全将其将当做是一条select语句即可, 只不过是将原来在SQL中写子查询操作, 移植到最上面使用with标识操作

5. join的相关内容

join : 进行多表连接的操作

join的分类:

交叉连接: 笛卡尔积(两个表的乘积结果)  --了解
	select * from 表A join 表B
内连接:
	隐式内连接:  select   * from 表A , 表B where 关联条件
	显示内连接: select  * from 表A [inner] join 表B on 关联条件
	注意: 在hive中, 建议使用显示内连接, 而不使用隐式内连接, 因为隐式内连接, 是先产生笛卡尔积的结果, 然后通过where进行过滤, 而显示内连接, 是在join的过程中, 边join边通过on条件进行过滤操作
外连接:
	左外连接:  select  * from 表A left  join 表B on 关联条件
	右外连接:  select  * from 表A right  join 表B on 关联条件
	全外连接:  select  * from 表A full  join 表B on 关联条件
	左半开连接:  select  * from 表A left semi  join 表B on 关联条件