【大数据】Hive_DDL&DML&select

---

前言

我的编程语言学习笔记——hive

---

一、DDL 数据定义

1. 数据库（database）

🍉创建数据库（方括号里为可选项）

CREATE DATABASE [IF NOT EXISTS] database_name
[COMMENT database_comment]
[LOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value, ...)];

🌰举个例子

# 创建一个数据库，不指定HDFS路径
hive (default)> create database db_hive1;
# 创建一个数据库，指定HDFS路径
hive (default)> create database db_hive2 location '/db_hive2';
# 创建一个数据库，带有dbproperties
hive (default)> create database db_hive3 with dbproperties('create_date'='2023-8-28');

若不指定路径，其默认路径为${hive.metastore.warehouse.dir}/database_name.db

🍉查询数据库

SHOW DATABASES [LIKE 'identifier_with_wildcards'];
# 查看数据库信息
DESCRIBE DATABASE [EXTENDED] db_name;

like通配表达式说明：*表示任意个任意字符，|表示或的关系

🌰举个例子

hive> show databases like 'db_hive*';
hive> desc database db_hive3;
# 查看详细信息包括dbproperties信息
hive> desc database extended db_hive3;

🍉修改数据库

用户可以使用alter database命令修改数据库某些信息，其中能够修改的信息包括dbproperties、location、owner user。需要注意的是：修改数据库location，不会改变当前已有表的路径信息，而只是改变后续创建的新表的默认的父目录

--修改dbproperties
ALTER DATABASE database_name SET DBPROPERTIES (property_name=property_value, ...);
--修改location
ALTER DATABASE database_name SET LOCATION hdfs_path;
--修改owner user
ALTER DATABASE database_name SET OWNER USER user_name;

🌰举个例子

hive> ALTER DATABASE db_hive3 SET DBPROPERTIES ('create_date'='2023-12-25');

🍉删除数据库

DROP DATABASE [IF EXISTS] database_name [RESTRICT|CASCADE];

RESTRICT：严格模式，若数据库不为空，则会删除失败，默认为该模式。
CASCADE：级联模式，若数据库不为空，则会将库中的表一并删除。

🍉切换数据库

USE database_name;

2. 表（table）

2.1. 普通建表（数据类型转换）

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name   
[(col_name data_type [COMMENT col_comment], ...)]
[COMMENT table_comment]
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
[CLUSTERED BY (col_name, col_name, ...) 
[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
[ROW FORMAT row_format] 
[STORED AS file_format]
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)]

👉关键字说明：

• TEMPORARY
  临时表，该表只在当前会话可见，会话结束，表会被删除。
• EXTERNAL（重点）
  外部表，与之相对应的是内部表（管理表）。管理表意味着Hive会完全接管该表，包括元数据和HDFS中的数据。而外部表则意味着Hive只接管元数据，而不完全接管HDFS中的数据。
• data_type（重点）
  Hive中的字段类型可分为基本数据类型和复杂数据类型。基本数据类型如下：
  
  复杂数据类型：
  
• PARTITIONED BY（重点）
  创建分区表
• CLUSTERED BY … SORTED BY…INTO … BUCKETS（重点）
  创建分桶表
• ROW FORMAT（重点）
  指定SERDE，SERDE是Serializer and Deserializer的简写。Hive使用SERDE序列化和反序列化每行数据。详情可参考 Hive-Serde
  

# DELIMITED关键字表示对文件中的每个字段按照特定分割符进行分割，其会使用默认的SERDE对每行数据进行序列化和反序列化
ROW FORAMT DELIMITED 
[FIELDS TERMINATED BY char] 
[COLLECTION ITEMS TERMINATED BY char] 
[MAP KEYS TERMINATED BY char] 
[LINES TERMINATED BY char] 
[NULL DEFINED AS char]

# SERDE关键字可用于指定其他内置的SERDE或者用户自定义的SERDE。例如JSON SERDE，可用于处理JSON字符串
ROW FORMAT SERDE serde_name [WITH SERDEPROPERTIES 
(property_name=property_value,property_name=property_value, ...)] 

fields terminated by ：列分隔符
collection items terminated by ：复杂类型map、struct和array中每个元素之间的分隔符
map keys terminated by ：专门map中的key与value的分隔符（struct结构体只保留值！）
lines terminated by ：行分隔符
null defined as：声明底层文件中null值的分隔符（默认值\N）

• STORED AS（重点）
  指定文件格式，常用的文件格式有，textfile（默认值），sequence file、orc file、parquet file等等

• LOCATION
  指定表所对应的HDFS路径，若不指定路径，其默认值为${hive.metastore.warehouse.dir}/db_name.db/table_name

• TBLPROPERTIES
  用于配置表的一些KV键值对参数

🍉数据类型转换：Hive的基本数据类型可以做类型转换，转换的方式包括隐式转换以及显示转换

• 隐式转换
  1️⃣任何整数类型都可以隐式地转换为一个范围更广的类型，如tinyint可以转换成int，int可以转换成bigint。
  2️⃣所有整数类型、float和string类型都可以隐式地转换成double。
  3️⃣tinyint、smallint、int都可以转换为float。
  4️⃣boolean类型不可以转换为任何其它的类型

官方说明文档点击👉前往

• 显示转换(强转 )：可以借助cast函数完成显示的类型转换

# 语法
cast(expr as <type>) 

hive (default)> select '1' + 2, cast('1' as int) + 2;

_c0	   _c1
3.0	    3

2.2. Create Table As Select（CTAS）建表

该语法允许用户利用select查询语句返回的结果，直接建表，表的结构和查询语句的结构保持一致，且保证包含select查询语句放回的内容

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table_name 
[COMMENT table_comment] 
[ROW FORMAT row_format] 
[STORED AS file_format] 
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)]
[AS select_statement]

2.3. Create Table Like建表

该语法允许用户复刻一张已经存在的表结构，与上述的CTAS语法不同，该语法创建出来的表中不包含数据

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
[LIKE exist_table_name]
[ROW FORMAT row_format] 
[STORED AS file_format] 
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)]

2.4 三种建表案例（内部表、外部表、json建表）

🌰内部表：Hive中默认创建的表都是的内部表，有时也被称为管理表。对于内部表，Hive会完全管理表的元数据和数据文件

create table if not exists student(
    id int, 
    name string
)
row format delimited fields terminated by '\t'
location '/user/hive/warehouse/student';

其中STORED AS未指定默认textfile，准备其需要的文件如下，注意字段之间的分隔符

vim /opt/module/datas/student.txt

1001	student1
1002	student2
1003	student3
1004	student4
1005	student5
1006	student6
1007	student7
1008	student8
1009	student9
1010	student10
1011	student11
1012	student12
1013	student13
1014	student14
1015	student15
1016	student16

# 上传文件到HDFS
cd /opt/module/datas/;hadoop fs -put student.txt /user/hive/warehouse/student

# 删除表，观察数据HDFS中的数据文件是否还在（发现都没了）
hive (default)> drop table student;

🌰外部表：通常可用于处理其他工具上传的数据文件，对于外部表，Hive只负责管理元数据，不负责管理HDFS中的数据文件。

create external table if not exists student(
    id int, 
    name string
)
row format delimited fields terminated by '\t'
location '/user/hive/warehouse/student';

发现删除后，HDFS上数据仍然都在

🌰SERDE和复杂数据类型
若现有如下格式的JSON文件需要由Hive进行分析处理，请考虑如何设计表？（以下内容为格式化之后的结果，文件中每行数据为一个完整的JSON字符串）

{
    "name": "dasongsong",
    "friends": [
        "bingbing",
        "lili"
    ],
    "students": {
        "xiaohaihai": 18,
        "xiaoyangyang": 16
    },
    "address": {
        "street": "hui long guan",
        "city": "beijing",
        "postal_code": 10010
    }
}

我们可以考虑使用专门负责JSON文件的JSON Serde，设计表字段时，表的字段名与JSON字符串中的一级字段名保持一致，对于具有嵌套结构的JSON字符串，考虑使用合适复杂数据类型保存其内容。最终设计出的表结构如下

create table teacher
(
    name     string,
    friends  array<string>,
    students map<string,int>,
    address  struct<city:string,street:string,postal_code:int>
)
row format serde 'org.apache.hadoop.hive.serde2.JsonSerDe'
location '/user/hive/warehouse/teacher';

创建该表，并准备以下文件。注意，需要确保文件中每行数据都是一行完整的JSON字符串，JSON SERDE才能正确的处理

 vim /opt/module/datas/teacher.txt

{"name":"dasongsong","friends":["bingbing","lili"],"students":{"xiaohaihai":18,"xiaoyangyang":16},"address":{"street":"hui long guan","city":"beijing","postal_code":10010}}

hadoop fs -put teacher.txt /user/hive/warehouse/teacher
# 尝试从复杂数据类型的字段中取值
select friends[0], students['xiaohaihai'], address.city from teacher;

🌰create table as select和create table like

# create table as select(完全一样)
hive> create table teacher1 as select * from teacher;
# create table like（仅表结构一样）
create table teacher2 like teacher;

2.4. 查看、修改、删除、清空表

🍉查看语法：

SHOW TABLES [IN database_name] LIKE ['identifier_with_wildcards'];
# 查看表信息
DESCRIBE [EXTENDED | FORMATTED] [db_name.]table_name

like通配表达式说明：*表示任意个任意字符（select语句用%），|表示或的关系
EXTENDED：展示详细信息
FORMATTED：对详细信息进行格式化的展示

🍉修改语法：

# 重命名表
ALTER TABLE table_name RENAME TO new_table_name

# 修改列信息（只修改元数据，HDFS不受影响）
# 增加列，该语句允许用户增加新的列，新增列的位置位于末尾
ALTER TABLE table_name ADD [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]
# 替换列，该语句允许用户用新的列集替换表中原有的全部列
ALTER TABLE table_name REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)
# 更新列，该语句允许用户修改指定列的列名、数据类型、注释信息以及在表中的位置
ALTER TABLE table_name change [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]

🌰例子(HDFS不变)：

-- 添加字段age
alter table stu add columns(age int);
-- 修改字段顺序(先关闭校验)
set hive.metastore.disallow.incomptible.col.type.changes=false;
alter table stu change column age age int after id;
-- 替换成原来一样的表
alter table stu replace columns (id int,name string);

🍉删除、清空语法：

DROP TABLE [IF EXISTS] table_name;
TRUNCATE [TABLE] table_name

truncate只能清空管理表，不能删除外部表中数据

二、DML数据操作

1. Load（Linux/HDFS➡Hive）

🍉Load语句可将文件导入到Hive表中，语法如下：

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

关键字说明：
local：表示从本地加载数据到Hive表，相当于put；否则从HDFS移动数据到Hive表，相当于move。
overwrite：表示覆盖表中已有数据，否则表示追加。
partition：表示上传到指定分区，若目标是分区表，需指定分区。

🌰举个例子(DG下)：

create table student(
    id int, 
    name string
) 
row format delimited fields terminated by '\t';

-- 加载本地文件到hive（覆盖）
load data local inpath '/opt/module/datas/student.txt' overwrite into table student;

-- 加载本地文件到hive（追加）
load data local inpath '/opt/module/datas/student.txt' into table student;

-- 不加local的移动版追加
linux> hadoop fs -put /opt/module/datas/student.txt /user/root/
load data inpath '/user/root/student.txt' into table student;

INTO：将结果追加到目标表
OVERWRITE：用结果覆盖原有数据

2. Insert

🍉将查询结果插入表中：

INSERT (INTO | OVERWRITE) TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement;

🌰举个例子：

-- 新建一张表
create table student1(
    id int,
    name string
)
row format delimited fields terminated by '\t';

-- 根据查询结果插入数据
insert overwrite table student1 select * from student;

🍉将给定values插入表中：

INSERT (INTO | OVERWRITE) TABLE tablename [PARTITION (partcol1[=val1], partcol2[=val2] ...)] 
VALUES values_row [, values_row ...]

🌰举个例子：

insert into table student1 values(55,'zhangsan'),(66,'lisi');

🍉将查询结果写入目标路径（只能override）：

INSERT OVERWRITE [LOCAL] DIRECTORY directory
[ROW FORMAT row_format] [STORED AS file_format] select_statement;

🌰举个例子：

-- 将stu表内容导出到一个js文件中
insert overwrite local directory '/opt/module/datas/student'
row format serde 'org.apache.hadoop.hive.serde2.JsonSerDe'
select * from stu;

3. Export & Import（Hive↔HDFS）

Export导出语句可将表的数据和元数据信息一并到处的HDFS路径，Import可将Export导出的内容导入Hive，表的数据和元数据信息都会恢复。Export和Import可用于两个Hive实例之间的数据迁移

--导出
EXPORT TABLE tablename TO 'export_target_path'

--导入
IMPORT [EXTERNAL] TABLE new_or_original_tablename FROM 'source_path' [LOCATION 'import_target_path']

🌰举个例子：

--导出
export table default.student to '/user/hive/warehouse/export/student';

--导入
import table student2 from '/user/hive/warehouse/export/student'

三、查询

只有数据量小学习测试的时候使用本地模式（DG中），提高效率set mapreduce.framework.name=localhost，这样map和reduce不是两个进程而是一个本地进程里的两个线程

关于select查询👉hive文档说明

1. 基本查询语法

SELECT [ALL | DISTINCT] select_expr, select_expr, ...
  FROM table_reference       -- 从什么表查
  [WHERE where_condition]   -- 过滤
  [GROUP BY col_list]        -- 分组查询
   [HAVING col_list]          -- 分组后过滤
  [ORDER BY col_list]        -- 排序
  [CLUSTER BY col_list
    | [DISTRIBUTE BY col_list] [SORT BY col_list]
  ]
 [LIMIT number]                -- 限制输出的行数

🍉数据准备：

在/opt/module/datas/路径上创建dept.txt文件

10	行政部	1700
20	财务部	1800
30	教学部	1900
40	销售部	1700

在/opt/module/datas/路径上创建emp.txt文件

7369	张三	研发	800.00	30
7499	李四	财务	1600.00	20
7521	王五	行政	1250.00	10
7566	赵六	销售	2975.00	40
7654	侯七	研发	1250.00	30
7698	马八	研发	2850.00	30
7782	金九	\N	2450.0	30
7788	银十	行政	3000.00	10
7839	小芳	销售	5000.00	40
7844	小明	销售	1500.00	40
7876	小李	行政	1100.00	10
7900	小元	讲师	950.00	30
7902	小海	行政	3000.00	10
7934	小红明	讲师	1300.00	30

在DG上创建表：

-- 创建部门表
create table if not exists dept(
    deptno int,    -- 部门编号
    dname string,  -- 部门名称
    loc int        -- 部门位置
)
row format delimited fields terminated by '\t';

-- 创建员工表
create table if not exists emp(
    empno int,      -- 员工编号
    ename string,   -- 员工姓名
    job string,     -- 员工岗位（大数据工程师、前端工程师、java工程师）
    sal double,     -- 员工薪资
    deptno int      -- 部门编号
)
row format delimited fields terminated by '\t';

-- 导入数据
load data local inpath '/opt/module/datas/dept.txt' into table dept;
load data local inpath '/opt/module/datas/emp.txt' into table emp;

🌰查询演示：

-- 全表查询
select * from emp;

-- 选择特定列查询
select empno, ename from emp;

-- 列别名（as有没有都可以）
select 
    ename AS name, 
    deptno dn 
from emp;

-- limit子句用于限制返回的行数
select * from emp limit 5; 
select * from emp limit 2,3; -- 表示从第2行开始，向下抓取3行

-- 查询出薪水大于1000的所有员工
select * from emp where sal > 1000;

-- 查询除了20部门和30部门以外的员工信息
select 
    * 
from emp 
where deptno not in(30, 20);

👉关系运算符：

where字句中不能使用别名

👉逻辑运算符：

👉聚合函数：

语法	功能
count(*)/count(1)	表示统计所有行数，包含null值
count(某列)	表示该列一共有多少行，不包含null值
max()	求最大值，不包含null，除非所有值都是null
min()	求最小值，不包含null，除非所有值都是null
sum()	求和，不包含null
avg()	求平均值，不包含null

👉HQL执行过程：

2. 分组

🍉Group By语句通常会和聚合函数一起使用，按照一个或者多个列队结果进行分组，然后对每个组执行聚合操作

-- 计算emp表每个部门的平均工资
select
    t.deptno,
    avg(t.sal) avg_sal
from emp t
group by t.deptno;

-- 计算emp每个部门中每个岗位的最高薪水。
select
    t.deptno,
    t.job,
    max(t.sal) max_sal
from emp t
group by t.deptno, t.job;

🍉Having与不用Having的区别

-- 求每个部门的平均薪水大于2000的部门
select deptno,avg_sal from (
    select
        t.deptno,
        avg(t.sal) avg_sal
    from emp t
    group by deptno
)t1
where avg_sal>2000;


select
    t.deptno,
    avg(t.sal) avg_sal
from emp t
group by deptno
having avg_sal>2000;

注意：where后面不能写分组聚合函数，而 having 后面可以使用分组聚合函数having只用于 group by分组统计语句。

3. Join语句（4种）

🍉等值join：Hive支持通常的sql join语句，hive在2.x之后也支持不等值连接

-- 根据员工表和部门表中的部门编号相等，查询员工编号、员工名称和部门名称
select 
    e.empno, 
    e.ename, 
    d.dname 
from emp e 
join dept d 
on e.deptno = d.deptno;

🍉内连接：只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来（保留公共部分）

🍉左外连接：left join操作符左边表中符合where子句的所有记录将会被返回（公共加左表）

🍉右外连接：right join操作符右边表中符合where子句的所有记录将会被返回（公共加右表）

🍉满外连接：将会返回所有表中符合where语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用null值替代

-- 满外连接
select 
    e.empno, 
    e.ename, 
    d.deptno 
from emp e 
full join dept d 
on e.deptno = d.deptno;

明确包含关系，明确想要的值，选择合适的连接方式

🍉多表连接：连接n个表，至少需要n-1个连接条件。例如：连接三个表，至少需要两个连接条件

select 
    e.ename, 
    d.dname, 
    l.loc_name
from emp e 
join dept d
on d.deptno = e.deptno 
join location l
on d.loc = l.loc;

🍉笛卡尔集的产生条件：

1. 省略连接条件
2. 连接条件无效
3. 所有表中的所有行互相连接

select 
    empno, 
    dname 
from emp, dept;

4. 联合（union & union all）

🍉上下拼接sql的结果，这点是和join有区别的，join是左右关联，union和union all是上下拼接。union去重，union all不去重，当字段不同名时，以第一个出现的字段名为主，union和union all在上下拼接sql结果时有两个要求：
1️⃣两个sql的结果，列的个数必须相同
2️⃣两个sql的结果，上下所对应列的类型必须一致

select 
*
from emp
where deptno=30
union
select 
*
from emp
where deptno=40;

5. 排序（4个By）

🍉全局排序（Order By）：只有一个Reduce才能保证全局有序（导致大数据量时reduce容易炸），查看reduce个数set mapreduce.job.reduces;，经常配合limit使用（map端会进行优化，减少了reduce端的压力，推荐！！）

asc（ascend）：升序（默认）
desc（descend）：降序

👇以下三个By用的少，但面试可能会问，更多详情移步👉官网

🍉每个Reduce内部排序（Sort By）：对于大规模的数据集order by的效率非常低。在很多情况下，并不需要全局排序，此时可以使用Sort by，为每个reduce产生一个排序文件。每个Reduce内部进行排序，对全局结果集来说不是排序（保证map到reduce的一个有序）

-- 设置reduce个数
set mapreduce.job.reduces=3;
-- 根据部门编号降序查看员工信息
select 
    * 
from emp 
sort by deptno desc;

-- 将查询结果导入到文件中（按照部门编号降序排序），一定要一个不存在的文件否则会覆盖掉之前有的文件
insert overwrite local directory '/opt/module/datas/sortby-result' select * from emp sort by deptno desc;
-- 一共有三个分区所以产生了三个文件，分区内字段均有序

🤓OrderBy与SortBy的区别？
主要区别在于:
1️⃣OrderBy是在查询结果返回给客户端之前,对结果进行排序处理。它会影响最终返回结果的顺序。
2️⃣SortBy是对查询结果进行本地排序,不会影响返回结果的顺序。它仅仅是对每个Reduce任务本地的数据进行排序。
具体来说:
1️⃣OrderBy会引起Shuffle和Reducer阶段,对全局查询结果进行全局排序。这会增加查询开销。
2️⃣SortBy仅对每个Reduce任务处理的块内数据进行排序,不会引起Shuffle阶段。不同Reduce任务之间的数据顺序不确定。
用法上:
1️⃣OrderBy一般放在查询语句的最后,如"SELECT * FROM table ORDER BY column"
2️⃣SortBy可以放在MAP或REDUCE关键字后,用于控制MAP或REDUCE阶段的处理顺序。
所以:
1️⃣OrderBy用于影响最终返回结果的顺序。
2️⃣SortBy仅对本地任务数据排序,不影响全局结果顺序,主要用于优化单个Reduce任务的处理。

总的来说,OrderBy提供了全局有序结果,开销较大；SortBy仅提供局部顺序,但效率更高。选择需要考虑查询目的和数据规模。

🍉分区（Distribute By）：在有些情况下，我们需要控制某个特定行应该到哪个Reducer，通常是为了进行后续的聚集操作。distribute by子句可以做这件事。distribute by类似MapReduce中partition（自定义分区），进行分区，结合sort by使用。对于distribute by进行测试，一定要分配多reduce进行处理，否则无法看到distribute by的效果

-- 先按照部门编号分区，再按照员工编号薪资排序
set mapreduce.job.reduces=3;
insert overwrite local directory 
'/opt/module/datas/distribute-result' 
select 
    * 
from emp 
distribute by deptno 
sort by sal desc;

🍉分区排序（Cluster By）：当distribute by和sort by字段相同时，可以使用cluster by方式。cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是升序排序，不能指定排序规则为asc或者desc

insert overwrite local directory 
'/opt/module/datas/distribute-result' 
select 
    * 
from emp 
distribute by deptno 
sort by deptno  desc;

-- 此时可以简化为cluster by
insert overwrite local directory 
'/opt/module/datas/distribute-result' 
select 
    * 
from emp 
cluster by saldeptno;

👉初级题目最值得听的一集，点击前往

---

总结

✍以上内容，更多详情前往hive官网语法手册查看详情

✍sum(if(语句,1,0))、count(if(语句,1,null))比一直join代码不仅简洁而且效率高

✍下一节，函数、分区表和分桶表~