Hive中的DDL操作

W_chuanqi

于 2023-06-08 11:22:42 发布

阅读量1.8k

点赞数 1

分类专栏： Hive数据仓库文章标签： hive 数据库 hadoop

本文链接：https://blog.csdn.net/W_chuanqi/article/details/131104203

版权

Hive数据仓库专栏收录该内容

10 篇文章

订阅专栏

Hive中的DDL操作

DDL（Data Definition Language）语句： 数据定义语言，主要是进行定义/改变表的结构、数据类型、表之间的链接等操作。常用的语句关键字有 CREATE、DROP、ALTER 等。

对有SQL基础的朋友来说，内容相对简单。

一、数据库（database）相关操作

1. 创建数据库

1）语法

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

2）案例

（1）创建一个数据库，不指定路径

create database db_hive1;

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

（2）创建一个数据库，指定路径

create database db_hive2 location '/db_hive2';

查看数据库所在位置：

（3）创建一个数据库，带有dbproperties

create database db_hive3 with dbproperties('create_date'='2022-11-18');

查看数据库：

2. 查询数据库

1）展示所有数据库

（1）语法

SHOW DATABASES [LIKE 'identifier_with_wildcards'];

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

（2）案例

查询所有数据库：

show databases;

查询所有带有db_hive前缀的数据库：

show databases like 'db_hive*';

2）查看数据库信息

（1）语法

DESCRIBE DATABASE [EXTENDED] db_name;

（2）案例

①查看基本信息

desc database db_hive3;

②查看更多信息

desc database extended db_hive3;

extended表示是否展示更为详尽的信息。

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）案例

（1）修改dbproperties

ALTER DATABASE db_hive3 SET DBPROPERTIES ('create_date'='2022-11-20');

4. 删除数据库

1）语法

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

注：

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

2）案例

（1）删除空数据库

drop database db_hive2;

（2）删除非空数据库

drop database db_hive3 cascade;

5. 切换当前数据库

1）语法

USE database_name;

2）案例

切换到db_hive1：

use db_hive1;

二、表（table）相关操作

1. 创建表

1.1 语法

1）普通建表

（1）完整语法

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name   
[(col_name data_type [COMMENT col_comment], ...)]
[COMMENT table_comment]

[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中的字段类型可分为基本数据类型和复杂数据类型。

基本数据类型如下：

Hive	说明	定义
tinyint	1byte有符号整数
smallint	2byte有符号整数
int	4byte有符号整数
bigint	8byte有符号整数
boolean	布尔类型，true或者false
float	单精度浮点数
double	双精度浮点数
decimal	十进制精准数字类型	decimal(16,2)
varchar	字符序列，需指定最大长度，最大长度的范围是[1,65535]	varchar(32)
string	字符串，无需指定最大长度
timestamp	时间类型
binary	二进制数据

复杂数据类型如下：

类型	说明	定义	取值
array	数组是一组相同类型的值的集合	array	arr[0]
map	map是一组相同类型的键-值对集合	map<string, int>	map[‘key’]
struct	结构体由多个属性组成，每个属性都有自己的属性名和数据类型	struct<id:int, name:string>	struct.id

注：类型转换

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

方式一：隐式转换

具体规则如下：

a. 任何整数类型都可以隐式地转换为一个范围更广的类型，如tinyint可以转换成int，int可以转换成bigint。

b. 所有整数类型、float和string类型都可以隐式地转换成double。

c. tinyint、smallint、int都可以转换为float。

d. boolean类型不可以转换为任何其它的类型。

详情可参考Hive官方说明：Allowed Implicit Conversions

方式二：显示转换

可以借助cast函数完成显示的类型转换

a.语法
cast(expr as <type>)
b.案例
select '1' + 2, cast('1' as int) + 2;

④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]

注：

fields terminated by ：列分隔符
collection items terminated by ： map、struct和array中每个元素之间的分隔符
map keys terminated by ：map中的key与value的分隔符
lines terminated by ：行分隔符

语法二：SERDE关键字可用于指定其他内置的SERDE或者用户自定义的SERDE。例如JSON SERDE，可用于处理JSON字符串。

ROW FORMAT SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value,property_name=property_value, ...)]

⑦STORED AS（重点）

指定文件格式，常用的文件格式有，textfile（默认值），sequence file，orc file、parquet file等等。

⑧LOCATION

指定表所对应的HDFS路径，若不指定路径，其默认值为

${hive.metastore.warehouse.dir}/db_name.db/table_name

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

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]

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

1.2 案例

1）内部表与外部表

（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

查看表，发现数据已经导入表中：

select * from student;

找到原因：发现是因为先上传的数据，然后再创建的表。删除数据后重新上传，解决问题。

删除表，观察数据HDFS中的数据文件是否还在，发现已经不存在了。

drop table student;

（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中的数据文件是否还在

drop table student;

我们发现，虽然表已经删除了，但数据文件还在。

2）SERDE和复杂数据类型

本案例重点练习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

尝试从复杂数据类型的字段中取值

select * from teacher;

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

3）create table as select和create table like

（1）create table as select

create table teacher1 as select * from teacher;

查询数据：

select * from teacher1;

（2）create table like

create table teacher2 like teacher;

创建的表中不包含数据。
select * from teacher2;

2. 查看表

1）展示所有表

（1）语法

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

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

（2）案例

show tables like 'te*';

2）查看表信息

（1）语法

DESCRIBE [EXTENDED | FORMATTED] [db_name.]table_name

注：

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

（2）案例

①查看基本信息

desc teacher;

hive (db_hive1)> desc teacher;
OK
col_name        		data_type       		comment
name                    string                  from deserializer   
friends                 array<string>           from deserializer   
students                map<string,int>         from deserializer   
address                 struct<city:string,street:string,postal_code:int>       from deserializer   
Time taken: 0.035 seconds, Fetched: 4 row(s)

②查看更多信息

desc formatted teacher;

hive (db_hive1)> desc formatted teacher;
OK
col_name        data_type       comment
# col_name              data_type               comment             
name                    string                  from deserializer   
friends                 array<string>           from deserializer   
students                map<string,int>         from deserializer   
address                 struct<city:string,street:string,postal_code:int>       from deserializer   
                 
# Detailed Table Information             
Database:               db_hive1                 
OwnerType:              USER                     
Owner:                  root                     
CreateTime:             Thu Jun 08 10:34:51 CST 2023     
LastAccessTime:         UNKNOWN                  
Retention:              0                        
Location:               hdfs://node1:9000/user/hive/warehouse/teacher    
Table Type:             MANAGED_TABLE            
Table Parameters:                
        COLUMN_STATS_ACCURATE   {\"BASIC_STATS\":\"true\",\"COLUMN_STATS\":{\"address\":\"true\",\"friends\":\"true\",\"name\":\"true\",\"students\":\"true\"}}
        bucketing_version       2                   
        numFiles                0                   
        numRows                 0                   
        rawDataSize             0                   
        totalSize               0                   
        transient_lastDdlTime   1686191691          
                 
# Storage Information            
SerDe Library:          org.apache.hadoop.hive.serde2.JsonSerDe  
InputFormat:            org.apache.hadoop.mapred.TextInputFormat         
OutputFormat:           org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat       
Compressed:             No                       
Num Buckets:            -1                       
Bucket Columns:         []                       
Sort Columns:           []                       
Storage Desc Params:             
        serialization.format    1                   
Time taken: 0.061 seconds, Fetched: 34 row(s)

3. 修改表

1）重命名表

（1）语法

ALTER TABLE table_name RENAME TO new_table_name

（2）案例

创建表：

create table if not exists stu(
    id int, 
    name string
);

重命名表

alter table stu rename to stu1;

注意：

修改的表要存在；
修改后的表不要与已存在的表重名。

2）修改列信息

（1）语法

①增加列

该语句允许用户增加新的列，新增列的位置位于末尾。

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

3）案例

（1）查询表结构

desc stu1;

（2）添加列

添加一个列age，类型为int。

alter table stu1 add columns (age int);

（3）查询表结构

desc stu1;

（4）更新列

将列age修改为ages，类型为double。

alter table stu1 change column age ages double;

（5）替换列

alter table stu1 replace columns(id int, name string);

4. 删除表

1）语法

DROP TABLE [IF EXISTS] table_name;

2）案例

drop table stu1;

5. 清空表

1）语法

TRUNCATE [TABLE] table_name

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

2）案例

（1）内部表（管理表）测试

创建内部表：

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

清空表：

```hive在这里插入代码片
truncate table student;


![image-20230608111113426](https://img-blog.csdnimg.cn/img_convert/6e887ec8236b5eeea1f7527b928b31b3.png)

查询数据：

```hive
select * from student;

发现表中已经没有数据了，查看hdfs中是否有数据：

删除表：

drop table student;

（2）外部表测试

创建外部表：

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

上传文件到指定路径：

hadoop fs -put student.txt /user/hive/warehouse/student

查询数据：

select * from student;

清空表：

truncate table student;

出现错误：

hive (db_hive1)> truncate table student;
FAILED: SemanticException [Error 10146]: Cannot truncate non-managed table student.

这个错误提示说明您尝试对一个非托管表进行truncate操作。在Apache Hive中，托管表（managed table）是由Hive自己管理其数据存储的表，因此，当您在Hive中执行TRUNCATE操作时，Hive会直接删除表中的所有数据，而存储表结构和元数据。

相反，非托管表（external table）是由用户管理其数据存储的表。对于非托管表，Hive不负责管理其数据存储，也不会对其执行数据的移动或者删除操作。因此，如果您尝试对一个非托管表执行TRUNCATE操作，将会得到类似于 “Cannot truncate non-managed table” 这样的错误提示。

如果您需要对非托管表清空数据，可以尝试使用HDFS命令或其他工具删除表数据存储路径下的数据文件。

参考链接：

1.聊聊SQL语句中 DDL 、DML 、DQL 、DCL 分别是什么https://blog.csdn.net/qq_45069279/article/details/115270096
2.尚硅谷大数据Hive 3.x教程全新升级版（基于hive3.1.3）p16-p27
3.HIve安装配置（超详细）
https://blog.csdn.net/W_chuanqi/article/details/130242723