大数据离线阶段08：Hive DDL其他语法

Dear 丶 2023-10-15 16:04 80阅读 0赞

### **五、Hive DDL其他语法** ###

#### **1.Database|schema（数据库） DDL操作** ####

#### Create database ####

Hive中DATABASE的概念和RDBMS中类似，我们称之为数据库。在Hive中， DATABASE和SCHEMA是可互换的，使用DATABASE或SCHEMA都可以。

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

**COMMENT**：数据库的注释说明语句

**LOCATION**：指定数据库在HDFS存储位置，默认/user/hive/warehouse

**WITH DBPROPERTIES**：用于指定一些数据库的属性配置。

下面创建一个数据库：itheima

create database if not exists itheima
    comment "this is my first db"
    with dbproperties ('createdBy'='AllenWoon');

![722953895f6ca80155472f838a20769d.png][]

![a8bcab9bb852328584e9ba004caf10a3.png][]

注意：使用location指定路径的时候，最好是一个新创建的空文件夹。

#### Describe database ####

Hive中的**DESCRIBE DATABASE**语句用于显示Hive中数据库的名称，其注释（如果已设置）及其在文件系统上的位置等信息。

<table> 
 <tbody> 
  <tr> 
   <th>DESCRIBE DATABASE/SCHEMA [EXTENDED] db_name;</th> 
  </tr> 
 </tbody> 
</table>

**EXTENDED**：用于显示更多信息。

![b2d7e899091b090e8c01ab203a6216bf.png][]

![cd1679d560a81bf8714e50f8987d055a.png][]

#### Use database ####

Hive中的**USE DATABASE**语句用于选择特定的数据库,切换当前会话使用哪一个数据库进行操作。

<table> 
 <tbody> 
  <tr> 
   <th>USE database_name;</th> 
  </tr> 
 </tbody> 
</table>

![6a2c4fd99557e9131321662bad0e462d.png][]

#### Drop database ####

Hive中的**DROP DATABASE**语句用于删除（删除）数据库。

默认行为是RESTRICT，这意味着仅在数据库为空时才删除它。要删除带有表的数据库，我们可以使用**CASCADE**。

<table> 
 <tbody> 
  <tr> 
   <th>DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];</th> 
  </tr> 
 </tbody> 
</table>

![e66576ad450ff1b6c8fa71eb3a1d438d.png][]

#### Alter database ####

Hive中的**ALTER DATABASE**语句用于更改与Hive中的数据库关联的元数据。

--更改数据库属性
    ALTER (DATABASE|SCHEMA) database_name SET DBPROPERTIES (property_name=property_value, ...);
    
    --更改数据库所有者
    ALTER (DATABASE|SCHEMA) database_name SET OWNER [USER|ROLE] user_or_role;
    
    --更改数据库位置
    ALTER (DATABASE|SCHEMA) database_name SET LOCATION hdfs_path;

![eca68275070f204b105e15f04c3d03cc.png][]

#### **2.Table（表）DDL操作** ####

#### Describe table ####

Hive中的**DESCRIBE table**语句用于显示Hive中表的元数据信息。

describe formatted [db_name.]table_name;
    describe extended [db_name.]table_name;

如果指定了EXTENDED关键字，则它将以Thrift序列化形式显示表的所有元数据。如果指定了FORMATTED关键字，则它将以表格格式显示元数据。

![29cb4bad091be1d046389f1ad4a61f9f.png][]

#### Drop table ####

DROP TABLE删除该表的元数据和数据。如果已配置垃圾桶（且未指定PURGE），则该表对应的数据实际上将移动到.Trash/Current目录，而元数据完全丢失。删除EXTERNAL表时，该表中的数据不会从文件系统中删除，只删除元数据。

如果指定了PURGE，则表数据不会进入.Trash/Current目录，跳过垃圾桶直接被删除。因此如果DROP失败，则无法挽回该表数据。

DROP TABLE [IF EXISTS] table_name [PURGE];    -- (Note: PURGE available in Hive 0.14.0 and later)

#### Truncate table ####

从表中删除所有行。可以简单理解为清空表的所有数据但是保留表的元数据结构。如果HDFS启用了垃圾桶，数据将被丢进垃圾桶，否则将被删除。

<table> 
 <tbody> 
  <tr> 
   <th>TRUNCATE [TABLE] table_name;</th> 
  </tr> 
 </tbody> 
</table>

#### Alter table ####

--1、更改表名
    ALTER TABLE table_name RENAME TO new_table_name;
    --2、更改表属性
    ALTER TABLE table_name SET TBLPROPERTIES (property_name = property_value, ... );
    --更改表注释
    ALTER TABLE student SET TBLPROPERTIES ('comment' = "new comment for student table");
    --3、更改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";
    
    --6、更改列名称/类型/位置/注释
    CREATE TABLE test_change (a int, b int, c int);
    // First change column a's name to a1.
    ALTER TABLE test_change CHANGE a a1 INT;
    // Next change column a1's name to a2, its data type to string, and put it after column b.
    ALTER TABLE test_change CHANGE a1 a2 STRING AFTER b;
    // The new table's structure is:  b int, a2 string, c int.
    // Then change column c's name to c1, and put it as the first column.
    ALTER TABLE test_change CHANGE c c1 INT FIRST;
    // The new table's structure is:  c1 int, b int, a2 string.
    // Add a comment to column a1
    ALTER TABLE test_change CHANGE a1 a1 INT COMMENT 'this is column a1';
    
    --7、添加/替换列
    --使用ADD COLUMNS，您可以将新列添加到现有列的末尾但在分区列之前。
    --REPLACE COLUMNS 将删除所有现有列，并添加新的列集。
    ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type,...);

#### **Partition（分区）DDL操作** ####

#### Add partition ####

分区值仅在为字符串时才应加引号。位置必须是数据文件所在的目录。

ADD PARTITION会更改表元数据，但不会加载数据。如果分区位置中不存在数据，查询将不会返回任何结果。

--1、增加分区
    ALTER TABLE table_name ADD PARTITION (dt='20170101') location
        '/user/hadoop/warehouse/table_name/dt=20170101'; 
    --一次添加一个分区
    
    ALTER TABLE table_name ADD PARTITION (dt='2008-08-08', country='us') location '/path/to/us/part080808'
                           PARTITION (dt='2008-08-09', country='us') location '/path/to/us/part080809';  
    --一次添加多个分区

#### rename partition ####

--2、重命名分区
    ALTER TABLE table_name PARTITION partition_spec RENAME TO PARTITION partition_spec;
    ALTER TABLE table_name PARTITION (dt='2008-08-09') RENAME TO PARTITION (dt='20080809');

#### delete partition ####

可以使用ALTER TABLE DROP PARTITION删除表的分区。这将删除该分区的数据和元数据。

--3、删除分区
    ALTER TABLE table_name DROP [IF EXISTS] PARTITION (dt='2008-08-08', country='us');
    ALTER TABLE table_name DROP [IF EXISTS] PARTITION (dt='2008-08-08', country='us') PURGE; --直接删除数据 不进垃圾桶

#### msck partition ####

Hive将每个表的分区列表信息存储在其metastore中。但是，如果将新分区直接添加到HDFS（例如通过使用hadoop fs -put命令）或从HDFS中直接删除分区文件夹，则除非用户ALTER TABLE table\_name ADD/DROP PARTITION在每个新添加的分区上运行命令，否则metastore（也就是Hive）将不会意识到分区信息的这些更改。

但是，用户可以使用修复表选项运行metastore check命令。

--4、修复分区
    MSCK [REPAIR] TABLE table_name [ADD/DROP/SYNC PARTITIONS];

MSC命令的默认选项是“添加分区”。使用此选项，它将把HDFS上存在但元存储中不存在的所有分区添加到元存储中。DROP PARTITIONS选项将从已经从HDFS中删除的metastore中删除分区信息。SYNC PARTITIONS选项等效于调用ADD和DROP PARTITIONS。

如果存在大量未跟踪的分区，则可以批量运行MSCK REPAIR TABLE，以避免OOME（内存不足错误）。

#### alter partition ####

--5、修改分区
    --更改分区文件存储格式
    ALTER TABLE table_name PARTITION (dt='2008-08-09') SET FILEFORMAT file_format;
    --更改分区位置
    ALTER TABLE table_name PARTITION (dt='2008-08-09') SET LOCATION "new location";

### **六、Hive Show显示语法** ###

Show相关的语句提供了一种查询Hive metastore的方法。可以帮助用户查询相关信息。

--1、显示所有数据库 SCHEMAS和DATABASES的用法 功能一样
    show databases;
    show schemas;
    
    --2、显示当前数据库所有表/视图/物化视图/分区/索引
    show tables;
    SHOW TABLES [IN database_name]; --指定某个数据库
    
    --3、显示当前数据库下所有视图
    Show Views;
    SHOW VIEWS 'test_*'; -- show all views that start with "test_"
    SHOW VIEWS FROM test1; -- show views from database test1
    SHOW VIEWS [IN/FROM database_name];
    
    --4、显示当前数据库下所有物化视图
    SHOW MATERIALIZED VIEWS [IN/FROM database_name];
    
    --5、显示表分区信息，分区按字母顺序列出，不是分区表执行该语句会报错
    show partitions table_name;
    
    --6、显示表/分区的扩展信息
    SHOW TABLE EXTENDED [IN|FROM database_name] LIKE table_name;
    show table extended like student;
    
    --7、显示表的属性信息
    SHOW TBLPROPERTIES table_name;
    show tblproperties student;
    
    --8、显示表、视图的创建语句
    SHOW CREATE TABLE ([db_name.]table_name|view_name);
    show create table student;
    
    --9、显示表中的所有列，包括分区列。
    SHOW COLUMNS (FROM|IN) table_name [(FROM|IN) db_name];
    show columns  in student;
    
    --10、显示当前支持的所有自定义和内置的函数
    show functions;
    
    --11、Describe desc
    --查看表信息
    desc extended table_name;
    --查看表信息（格式化美观）
    desc formatted table_name;
    --查看数据库相关信息
    describe database database_name;

[722953895f6ca80155472f838a20769d.png]: https://img-blog.csdnimg.cn/img_convert/722953895f6ca80155472f838a20769d.png
[a8bcab9bb852328584e9ba004caf10a3.png]: https://img-blog.csdnimg.cn/img_convert/a8bcab9bb852328584e9ba004caf10a3.png
[b2d7e899091b090e8c01ab203a6216bf.png]: https://img-blog.csdnimg.cn/img_convert/b2d7e899091b090e8c01ab203a6216bf.png
[cd1679d560a81bf8714e50f8987d055a.png]: https://img-blog.csdnimg.cn/img_convert/cd1679d560a81bf8714e50f8987d055a.png
[6a2c4fd99557e9131321662bad0e462d.png]: https://img-blog.csdnimg.cn/img_convert/6a2c4fd99557e9131321662bad0e462d.png
[e66576ad450ff1b6c8fa71eb3a1d438d.png]: https://img-blog.csdnimg.cn/img_convert/e66576ad450ff1b6c8fa71eb3a1d438d.png
[eca68275070f204b105e15f04c3d03cc.png]: https://img-blog.csdnimg.cn/img_convert/eca68275070f204b105e15f04c3d03cc.png
[29cb4bad091be1d046389f1ad4a61f9f.png]: https://img-blog.csdnimg.cn/img_convert/29cb4bad091be1d046389f1ad4a61f9f.png