六，Hive之HQL详述。HQL之DDL语言，对库，表，表结构的操作。数据定义语言

目录

一，库

1，创建库

 2，删除库

3，进入库/切换库

 4，查看库

二，表

1，创建表

2，查看表

3，删除表

4，添加数据

4.1，添加普通数据类型的数据

4.2，添加复杂数据类型的数据

1，字符串类型

2，数组类型

3，map类型

4，默认分隔符

5，创建外部表

6，修改表

6.1，修改表名

6.2，修改表属性

6.3，修改表注释

6.4，修改表结构

三，对分区表的DDL操作

1，增加分区列

2，修改分区列的列名

3，删除分区列

4，修复分区列

5，修改分区列的存储形式

6，修改分区列的位置

---

---

一，库

1，创建库

create database db_2

1.1创建库的时候判定库的是否存在

create database if not exists db_2

 2，删除库

drop database 库名;

2.1，强制删库

drop database db_1 cascade

2.1，删除库的时候判定库时候存在

drop database if exists db_2 cascade

3，进入库/切换库

use 库名;

 4，查看库

show databases;

---

二，表

在Hive中，表是数据的核心存储结构，它定义了数据的模式（即列的名称和类型）。

1，创建表

CREATE TABLE IF NOT EXISTS userdb.users (  
    id INT,  
    name STRING,  
    age INT  
)  
ROW FORMAT DELIMITED  
FIELDS TERMINATED BY ','    -- 指定数据的分隔符
STORED AS TEXTFILE;   --指定数据以文本文件的形式存储

2，查看表

2.1 查看表结构

DESCRIBE users;

2.1 查看表的建表语句

SHOW CREATE TABLE users;

3，删除表

DROP TABLE IF EXISTS users;

4，添加数据

4.1，添加普通数据类型的数据

• 准备文件
• 把文件放到linux系统下某一个文件内 比如 /root/hive_data/honor_of_kings
• 把件上传到HIVE默认的存储路径下

​
   hdfs dfs -put 文件路径  /user/hive/warehouse/库名.表名
    库名和表名需要与我们sql创建的库,表名一致

​

• 创建表

--创建表
create table if not exists tb_archer(
    id int comment 'ID',
    name string comment '英雄名称',  -- 字段 注释
    hp_max int comment '最大生命',   --注释
    mp_max int comment "最大法力",
    attack_max int comment "最高物攻",
    defense_max int comment "最大物防",
    attack_range string comment "攻击范围",
    role_main string comment "主要定位",
    role_assist string comment "次要定位"
    )comment '射手信息'  -- 表注释
    row format delimited  fields terminated by '\t';  -- 指定分隔符

• 从linux系统种添加数据

load data local inpath '/root/hive_data/'honor_of_kings/表格名' into table 库名.表名

• 从hdfs里面添加数据  想要把文件上传到hdfs系统种

--[root@node1 hive_data]# hdfs dfs -put honor_of_kings /data

load data inpath '/data/01_archer.txt' into table db_1.tb_archer_2;

4.2，添加复杂数据类型的数据

1，字符串类型

1，建表

-- 创建表格
create table db_1.tb_user(
    id int,
    name string,
    hobbys string
)
row format delimited fields terminated by ',';  -- 指定文本的分隔符

2，添加数据

 

-- 插入数据 准备好的再linux上的数据
load data local inpath '/root/hive_data/honor_of_kings/aa.txt' into table db_1.tb_user;

3，查询数据

 

select *,
       split(hobbys,'-'),  --  手动把字符串分割为数组
       split(hobbys,'-')[0],  --根据下标取值
       split(hobbys,'-')[1]
from db_1.tb_user;

2，数组类型

建表

create table db_1.tb_user1(
    id int,
    name string,
    hobbys array<string>  --指定数组类型
)
row format delimited fields terminated by ','    --指定文本分隔符
collection items terminated by '-';    --指定数组的内部的分隔符

添加数据

-- 插入数据 把准备好的linux上的数据插入进去
load data local inpath '/root/hive_data/honor_of_kings/aa.txt' into table db_1.tb_user1;

 查看数据

select * ,
       hobbys[0],
       hobbys[1],
       hobbys[2]
from db_1.tb_user1;

 

3，map类型

建表  比如数据是：

/*
-- map: 键值对, {key:value, key:value}
-- 1,孙悟空,53,西部大镖客:288-大圣娶亲:888-全息碎片:0-至尊宝:888-地狱火:1688
*/

create table tb_hot_hero_skin_price(
    id int,
    name string,
    win_rate int,
    skin_price map<string,int>
)
row format delimited fields terminated by ','    -- 指定文本的分隔符
collection items terminated by '-'    --- 指定字典的分隔符
map keys terminated by ':';    -- 指定键值对的分隔符

 

添加数据

-- 插入数据
load data local inpath '/root/hive_data/honor_of_kings/02_hot_hero_skin_price.txt'
    into table tb_hot_hero_skin_price;

查看数据

select * from tb_hot_hero_skin_price;

根据键看值
	select *,
       skin_price['大圣娶亲']
from tb_hot_hero_skin_price
where id=1;

查看全部的key
	select *,
       map_keys(skin_price)
from tb_hot_hero_skin_price;

查看全部的键
	select * ,
       map_values(skin_price)
from tb_hot_hero_skin_price;

4，默认分隔符

• 约定大于配置  如果不指定分隔符 默认就是按照^A 分割（在vim）
•  他可能会显示为SOA  也或者是方框

create table tb_team_ace_player(
    id int,
    team_name string,
    ace_player_name string
);

5，创建外部表

• 我们上述建立的表都是内部表
• 内部表删除数据的时候数据会一并被删除
• 内部表管理的是元数据和数据文件
• 外部表管理的是元数据
• 外部表删除的时候 仅仅删除的元数据，数据文件不会被删除

外部表的优势

• 数据共享和灵活性
• 数据位置不变，意味着可以多了共享数据
• 不需要移动数据，意味着减少了数据的传输开销
• 利于数据整合，意味着我们可以与hdfs上其他的文件或者数据系统集成
• 数据的安全性
• 数据保护，我们删除的时候仅仅会删除元数据，而不会删除数据文件
• 避免数据损失，外部表不允许进行任何修改，从而导致数据损坏风险
• 利于数据管理
• 适用于特定场景
• 当大数据集处理的时候，使用外部表可以更方便的进行分区和查询优化，提高数据的处理效率

建立外部表 关键字 

建表的时候指定 关键字为 external

-- 创建外部表
create external table tb_student2(    -- external 关键字
    id int,
    name string,
    department string,
    salary float
)
row format delimited fields terminated by ','
location '/root/test/test_data';

6，修改表

6.1，修改表名

ALTER TABLE old_name RENAME TO new_name;

6.2，修改表属性

ALTER TABLE users SET TBLPROPERTIES ('property_key'='property_value');

6.3，修改表注释

--更改表注释
ALTER TABLE student SET TBLPROPERTIES ('comment' = "new comment for student table");

6.4，修改表结构

ALTER TABLE users ADD COLUMNS (email STRING);

---

三，对分区表的DDL操作

1，增加分区列

增
	添加一个新的分区
		-- 增加分区  增加一个分区
alter table tb_order_par add partition (dt='2024-07-16');
	添加多个分区
		--- 增加多个分区
alter table tb_order_par add partition (dt='2024-07-04')
    partition (dt='2024-07-05');
	添加一个分区并更改设置路径
		-- 增加分区并设置路径
alter table tb_order_par add partition (dt='2024-07-08') location '/root/data/2024-07-08';

2，修改分区列的列名

改
	修改分区列的列名
		-- 修改分区的名称
alter table tb_order_par partition (dt='2024-07-04') rename to partition (dt='20240704');
show partitions tb_order_par;

3，删除分区列

删
	-- 删除
alter table tb_order_par drop partition (dt='2024-07-06');
alter table tb_order_par drop partition (dt>'2024-07-06'); -- 删除大于这个日期的

4，修复分区列

修复迁移
	3.4．msck partition
		-- 迁移
-- [root@node1 hive_data]# hdfs dfs -mkdir -p /user/hive/warehouse/db_2.db/tb_order_par/dt=2024-07-08;
msck repair table  tb_order_par ;
-- 查看验证
show partitions tb_order_par;

5，修改分区列的存储形式

修改分区的存储格式
	--5、修改分区
--更改分区文件存储格式  --如TextFile、Parquet、ORC等
alter table tb_order_par partition (dt='2024-07-08') set fileformat parquet ;

6，修改分区列的位置

修改分区的 位置
	-- 更改分区位置
ALTER TABLE tb_order_par PARTITION (dt='2024-07-08') SET LOCATION "/root/data/2024-07-08";