【大数据】Hive_DDL、DML

一、DDL数据定义

1. 数据库

1.1 创建数据库

（1）语法

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

（2）案例

-- 创建一个数据库，不指定路径
create database db_hive1;
-- 创建一个数据库，指定路径
create database db_hive2 location '/db_hive2';
-- 创建一个数据库，带有dbproperties
create database db_hive3 with dbproperties('create_date'='2023-9-22');

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

1.2 查询数据库

（1）展示所有数据库

SHOW DATABASES [LIKE 'identifier_with_wildcards'];

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

案例

show databases like 'db_hive*';

（2）查看数据库信息

DESCRIBE DATABASE [EXTENDED] db_name;

案例

-- 查看基本信息
desc database db_hive3;
-- 查看更多信息
desc database extended db_hive3;

1.3 修改数据库

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

（1）语法

--修改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;

（2）案例

--修改dbproperties
ALTER DATABASE db_hive3 SET DBPROPERTIES ('create_date'='2023-11-20');

1.4 删除数据库

（1）语法

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

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

（2）案例

--删除空数据库
drop database db_hive2;
--删除非空数据库
drop database db_hive3 cascade;

1.5 切换当前数据库

USE database_name;

2. 表（table）

2.1 创建表

2.1.1 普通建表

（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, ...)]

（2）关键字说明：

• 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的分隔符
• lines terminated by ：行分隔符

• STORED AS（重点）
  指定文件格式，常用的文件格式有，textfile（默认值），sequence file，orc file、parquet file等等。
• LOCATION
  指定表所对应的HDFS路径，若不指定路径，其默认值为
  ${hive.metastore.warehouse.dir}/db_name.db/table_name
• TBLPROPERTIES
  用于配置表的一些KV键值对参数

2.1.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.1.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.1.4案例

（1）内部表

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

创建内部表如下：

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

准备其需要的文件如下，注意字段之间的分隔符。

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
--上传文件到Hive表指定的路径
hadoop fs -put student.txt /user/hive/warehouse/student
--删除表，观察数据HDFS中的数据文件是否还在
hive (default)> drop table student;

当我们删除一个内部表时，Hive也会删除这个表中的数据。内部表不适合和其他工具共享数据。即：Mysql元数据库中的元数据信息、Hdfs上的数据文件夹和数据文件都会被删除。

（2）外部表

外部表通常可用于处理其他工具上传的数据文件，对于外部表，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';

--上传文件到Hive表指定的路径
hadoop fs -put student.txt /user/hive/warehouse/student
--删除表，观察数据HDFS中的数据文件是否还在
hive (default)> drop table student;

删除该表并不会删除掉这份数据，不过描述表的元数据信息会被删除掉。也即：只删除 Mysql 元数据库中存储的元数据信息，不删除 hdfs 上的数据文件内容。

（3）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}}
--上传文件到Hive表指定的路径
hadoop fs -put teacher.txt /user/hive/warehouse/teacher

（4）create table as select 和 create table like

--create table as select
create table teacher1 as select * from teacher;

--create table like
create table teacher2 like teacher;

2.2 查看表

（1）展示所有表

SHOW TABLES [IN database_name] LIKE ['identifier_with_wildcards'];

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

案例

show tables like 'stu*';

（2）查看表信息

DESCRIBE [EXTENDED | FORMATTED] [db_name.]table_name

• EXTENDED：展示详细信息
• FORMATTED：对详细信息进行格式化的展示

案例

- 查看基本信息
desc stu;
- 查看更多信息
desc formatted stu;

2.3 修改表

（1）重命名表

ALTER TABLE table_name RENAME TO new_table_name

案例

alter table stu rename to stu1;

（2）修改列信息

--增加列（该语句允许用户增加新的列，新增列的位置位于末尾）
ALTER TABLE table_name ADD 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]

--替换列（该语句允许用户用新的列集替换表中原有的全部列）
ALTER TABLE table_name REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)

案例

--查询表结构
desc stu;
--添加列
alter table stu add columns(age int);
--查询表结构
desc stu;
--更新列
alter table stu change column age ages double;
--替换列
alter table stu replace columns(id int, name string);

2.4 删除表

DROP TABLE [IF EXISTS] table_name;

案例

drop table stu;

2.5 清空表

TRUNCATE [TABLE] table_name

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

案例

truncate table student;

二、DML数据操作

1. Load

Load语句可将文件导入到Hive表中。

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

关键字说明：
（1）local：表示从本地加载数据到Hive表；否则从HDFS加载数据到Hive表。
（2）overwrite：表示覆盖表中已有数据，否则表示追加。
（3）partition：表示上传到指定分区，若目标是分区表，需指定分区。

2. Insert

2.1 将查询结果插入表中

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

关键字说明：
（1）INTO：将结果追加到目标表
（2）OVERWRITE：用结果覆盖原有数据

案例

--新建一张表
create table student1(
    id int, 
    name string
) 
row format delimited fields terminated by '\t';
--根据查询结果插入数据
insert overwrite table student3 
select 
    id, 
    name 
from student;

注意：

• insert不支持插入部分字段；
• insert 可以从A表插入A表，即允许insert into table A select * from A，可以替换其中某些字段，来避免使用update。

2.2 将给定Values插入表中

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

案例

insert into table  student1 values(1,'wangwu'),(2,'zhaoliu');

2.3 将查询结果写入目标路径

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

案例

insert overwrite local directory '/opt/module/datas/student' 
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.JsonSerDe'
select id,name from student;

3. Export&Import

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

（1）语法

--导出
EXPORT TABLE tablename TO 'export_target_path'

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

（2）案例

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

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