【Hive---05】DDL数据定义语言『 数据库操作 | 表操作 | 视图 vs 物化视图』

1. DDL概述

1. 定义：DDL(Data Definition Langage数据定义语言)：用于对数据库中的对象结构进行创建、删除、修改。数据库对象包括：database、table、view、index等。

   注意：DDL只是对数据库中对象进行操作，不涉及表内部数据的操作，对数据操作的是DML。

2. 命令操作

1. 执行任何sql语句操作时，在前面加上explain关键字，就可以查看执行计划

   经典案例

2. 执行任何sql语句操作时，在前面加上show关键字，就可以看见完整的执行语句(主要是可以查看hivesql的一些默认配置)

   比如：查看建立test表时的语句

   show create table test;
   

   返回结果：

   CREATE TABLE `test`(
     `id` string)
   ROW FORMAT SERDE 
     'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' 
   STORED AS INPUTFORMAT 
     'org.apache.hadoop.mapred.TextInputFormat'       -- MR操作时的InputFormat
   OUTPUTFORMAT 
     'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'   -- MR操作时的OutputFormat
   LOCATION
     'hdfs://hadoop102:8020/user/hive/warehouse/test'   -- 该表在HDFS上的存储路径
   TBLPROPERTIES (
     'bucketing_version'='2', 
     'transient_lastDdlTime'='1660101789')    -- 最后一次DDL的时间等属性
   

3. 数据库操作 ⭐

3.1 创建数据库

create database [IF NOT EXISTS] database_name -- IF NOT EXISTS 判断是否已经存在，不存在时才创建

[COMMENT database_comment] -- 注释

-- 指定该表在hdfs上的存储位置，默认路径为：/user/hive/warehouse/数据库名称.db/
-- 在该数据库中创建表时，表的默认存储路径就是：/user/hive/warehouse/数据库名称.db/表名/
[LOCATION hdfs_path]

-- 创建时间、作者等其他属性
[WITH DBPROPERTIES (property_name=property_value, ...)]

注意：

1. hive默认有一个名为default的数据库，存储数据位置在hive的默认路径user/hive/warehouse/(如果修改了hive的默认路径，就在修改后的路径下)。
2. 没有use database_name切换数据库时，默认都使用的default数据库，所以没使用任何数据库时创建的表在该路径下。

3.2 查询数据库

1. 显示所有数据库名称：

   show databases;
   

2. 查询以某数据库名称：

   -- 'XX'表示指定数据库名称
   -- 'XX*'表示以XX开头的数据库名称
   show databases like 'XX';
   

3. 查看名为XX数据库的元数据信息：

   desc database [extended] XX; -- 加上extended关键字会显示参数信息
   

   

3.3 切换数据库

• 如：使用hive数据库

  use hive;
  

3.4 修改数据库

1. 修改属性

   alter database hive set dbproperties('createtime'='20200101');
   

2. 修改拥有者

   alter database hive set owner user username;
   

3. 修改存放路径

   alter database hive set location hdfs_path;
   

3.5 删除数据库

1. 删除空的数据库：

   drop database hive; 
   -- 一般加上 if exists 判断
   drop database if exists hive;
   

2. 如果数据库不为空，且删除该数据库下的所有表，则加上 cascade关键字强制删除：

   drop database hive cascade;
   

4. 表操作⭐

只需要掌握好创建表、删除表就可以了。在实际开发中，一旦表有问题，直接删除表，再重新上传数据也很快。

4.1 创建表

4.1.1 完整建表语句 ⭐

-- EXTERNAL：外部表
creat [external] table [if not exists] [database_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]

-- 数据行格式：列分隔符、换行符等
	-- delimited 是使用LazySimpleSerDe类处理"单字节分隔符"数据格式
	-- serde 是使自定义SerDe类处理“多字节分隔符”数据格式
[row format delimited | serde]

-- 指定文件格式：文本文件、压缩文件等。
-- (创建事务表时会用到，因为事务表仅支持存储为 orc 格式)
[stored as file_format]

-- 表的存储位置
[location hdfs_path]

-- 表的其他属性
-- (创建事务表会用到，要设置属性 'transactional'='true' 表示开启事务)
[tblproperties (property_name=property_value, ...)]

-- 创建表时将查询数据导入
[as select 查询语句]

以上关键字具有先后顺序，建表时一定要注意。

其中： [ ]是可选选项； | 是多选一。

注意：创建表前，必须use 数据库。如果没有指定数据库，直接建表，表会建立在hive的默认路径下

接下来介绍创建表时的各个关键字

4.1.2 external：管理表 / 外部表 ⭐

• 建表语句中，是否选择external关键字将表分为两类：

  1. 内部表 / 管理表：无该关键字，指删表的时候，Hive也会删除在hdfs上的数据。
  2. 外部表：有该关键字，指删表的时候只删除元数据，不删除hdfs上的真实数据，保证了数据安全。
     

• 两表之间的相互转换

  • 将内部表修改为外部表

    alter table table_name set tblproperties('EXTERNAL'='TRUE');
    

  • 将外部表修改内部表

    alter table table_name set tblproperties('EXTERNAL'='FALSE');
    

4.1.3 partitioned by：分区表 ⭐

https://blog.csdn.net/qq_43546676/article/details/127534535

4.1.4 clustered by：分桶表 ⭐

https://blog.csdn.net/qq_43546676/article/details/127541400

4.1.5 row format：分隔符 ⭐

1. 没有row format关键字：默认使用
   ① 以\001做为字段的分隔符；
   ② 以\n做为行分隔符。

   create table external (
       name string,
       age int
   )
   -- 不写row format 语句
   

2. 加上delimited关键字，即使用row format delimited：用于处理单分隔符问题

   1. fields terminated by ','：每个列之间用,分割
   2. collection items terminated by '-'：集合之间的元素用-分割
   3. map keys terminated by ':'：键值对之间用:分割
   4. lines terminated by '\n'：每一行数据按\n分割

   create table external student (
       name string,
       age int
   );
   row format delimited
   	fields terminated by ','
   	collection items terminated by '-'
   	map keys terminated by ':'
   	lines terminated by '\n';
   

3. 加上serde关键字，即使用row format serde：用于处理除单字节分隔符之外的复杂分隔符
   https://blog.csdn.net/qq_43546676/article/details/128313097

4.1.6 stored as + tblproperties：事务表 ⭐

1. 在创建表时使用stored as关键字可以指定表数据的存储格式。hive的表数据存储格式有很多：
   

   注意：一般情况下，对小文件进行建表时，不使用stored as关键字，即使用textfile存储格式；其它情况一般使用orc存储格式。

2. stored as + tblproperties关键字可以构建事务表：
   https://blog.csdn.net/qq_43546676/article/details/127619932

4.2 删除表

1. 删除表：
   ① 如果是管理表：真实数据和元数据都删除
   ② 如果是外部表：删除元数据，不删除真实数据

   drop table student1;
   

2. 删除表的真实数据，保留元数据：

   truncate table student1;
   

4.3 修改表

(1)表相关

1. 修改表名：

   alter table old_table_name rename to new_table_name;
   

2. 修改表的属性：

   alter table old_table_name set tblproperties(pro_name=pro_value, ...);
   
   -- 比如修改表注释：
   alter table old_table_name set tblproperties(comment
   ='alter table comment', ...);
   

3. 修改SerDe

   # 更改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";
   

(2) 表字段相关 ⭐

1. 添加字段

   -- 语法：
   --     一次可以添加一个或多个字段
   --     first|after column_name 指将列修改后放在哪个位置？
   --				   如果没有column_name ，只有first则放在第一个位置，只有after则放在最后位置
   -- 				   有column_name字段,则搭配first/after放在column_name字段前面还是后面
   --     DEFAULT 是设置该字段的默认值 
   alter table table_name add columns (col_name data_type [comment col_comment], ...) [first|after [column_name]] [DEFAULT default_value]; 
   
   -- 例子：向studen表中新增了一列city，此列的数据类型为string，列注释为'城市'，并且被添加到表的最后一个字段
   alter table student add columns city comment '所在城市' after
   
   -- 例子：向studen表中新增了一列city，此列的数据类型为string，列注释为'城市'，并且被添加到表的age列后面，该字段的默认值为'中国'
   alter table student add columns city string comment '所在城市' after age default '中国'
   

2. 删除列

   -- 语法：
   ALTER TABLE table_name DROP [COLUMN] column_name;
   
   -- 例子：
   ALTER TABLE student DROP COLUMN address;
   

3. 修改字段

   1. 修改字段类型：

      -- 语法：修改字段类型：
      -- 	     一次只能修改一个字段
      --       first|after column_name 指将列修改后放在哪个位置？column_name字段前还是后面
      alter table table_name change [column] col_old_name col_new_name column_type [comment col_comment] [first|after column_name]
      

   2. 修改字段类型

      -- 语法：
      alter table table_name modify column col_name col_type;
      
      -- 例子：
      ALTER TABLE student_info MODIFY COLUMN phone STRING(15);
      

4. 重新设置表的所有字段：

   alter table table_name replace columns (col_name data_type [comment col_comment],...)
   

4.4 查看表

1. 查看有哪些表(会显示当前数据库所有 表/视图/物化视图/分区/索引)

   show tables [in database_name];
   

2. 查看表有哪些列

   show columns in table_name;
   

3. 查看表的元数据：

   desc extended table_name;
   desc formatted table_name; -- 会将以表格的形式显示结果，更加美观
   

   

4. 查看表的属性：

show tbproperties table_name;

5. 视图 与 物化视图

• 视图
• 物化视图