Spark SQL 指南

ALTER DATABASE

描述

ALTER DATABASE 语句用于更改数据库的属性或位置。请注意，DATABASE、SCHEMA 和 NAMESPACE 的用法是可互换的，彼此可以替代。如果系统中找不到该数据库，将发出错误消息。

设置属性

ALTER DATABASE SET DBPROPERTIES 语句用于更改与数据库关联的属性。指定的属性值会覆盖任何具有相同属性名的现有值。此命令主要用于记录数据库的元数据，并可用于审计目的。

语法

ALTER { DATABASE | SCHEMA | NAMESPACE } database_name
    SET { DBPROPERTIES | PROPERTIES } ( property_name = property_value [ , ... ] )

参数

取消设置属性

ALTER DATABASE UNSET DBPROPERTIES 语句用于取消设置与数据库关联的属性。如果指定的属性键不存在，该命令将忽略它并最终成功执行。（从 Spark 4.0.0 开始可用）

语法

ALTER { DATABASE | SCHEMA | NAMESPACE } database_name
    UNSET { DBPROPERTIES | PROPERTIES } ( property_name [ , ... ] )

参数

设置位置

ALTER DATABASE SET LOCATION 语句用于更改数据库中添加新表的默认父目录。请注意，它不会将数据库当前目录的内容移动到新指定的位置，也不会更改指定数据库下任何表/分区关联的位置（从 Spark 3.0.0 及 Hive metastore 3.0.0 及更高版本开始可用）。

语法

ALTER { DATABASE | SCHEMA | NAMESPACE } database_name
    SET LOCATION 'new_location'

参数

示例

-- Creates a database named `inventory`.
CREATE DATABASE inventory;

-- Alters the database to set properties `Edited-by` and `Edit-date`.
ALTER DATABASE inventory SET DBPROPERTIES ('Edited-by' = 'John', 'Edit-date' = '01/01/2001');

-- Verify that properties are set.
DESCRIBE DATABASE EXTENDED inventory;
+-------------------------+------------------------------------------+
|database_description_item|                database_description_value|
+-------------------------+------------------------------------------+
|            Database Name|                                 inventory|
|              Description|                                          |
|                 Location|   file:/temp/spark-warehouse/inventory.db|
|               Properties|((Edit-date,01/01/2001), (Edited-by,John))|
+-------------------------+------------------------------------------+

-- Alters the database to set a new location.
ALTER DATABASE inventory SET LOCATION 'file:/temp/spark-warehouse/new_inventory.db';

-- Verify that a new location is set.
DESCRIBE DATABASE EXTENDED inventory;
+-------------------------+-------------------------------------------+
|database_description_item|                 database_description_value|
+-------------------------+-------------------------------------------+
|            Database Name|                                  inventory|
|              Description|                                           |
|                 Location|file:/temp/spark-warehouse/new_inventory.db|
|               Properties| ((Edit-date,01/01/2001), (Edited-by,John))|
+-------------------------+-------------------------------------------+

-- Alters the database to unset the property `Edited-by`
ALTER DATABASE inventory UNSET DBPROPERTIES ('Edited-by');

-- Verify that the property `Edited-by` has been unset.
DESCRIBE DATABASE EXTENDED inventory;
+-------------------------+-------------------------------------------+
|database_description_item|                 database_description_value|
+-------------------------+-------------------------------------------+
|            Database Name|                                  inventory|
|              Description|                                           |
|                 Location|file:/temp/spark-warehouse/new_inventory.db|
|               Properties| ((Edit-date,01/01/2001))                  |
+-------------------------+-------------------------------------------+

-- Alters the database to unset a non-existent property `non-existent`
-- Note: The command will ignore 'non-existent' and finally succeed
ALTER DATABASE inventory UNSET DBPROPERTIES ('non-existent');