Hive 数据库操作、表操作、分区操作、SHOW语法

数据库DDL操作

创建数据库

• COMMENT：数据库注释
• LOCATION：指定数据库在HDFS存储位置，默认/user/hive/warehouse/dfname.db
• WITH DBPROPERTIES：用于指定一些数据库的属性配置。

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

查看数据库

• 显示Hive中数据库的名称，注释，以及在文件系统上的位置信息
• EXTENDED关键字用于显示更多信息

DESCRIBE DATABASE/SCHEMA [EXTENDED]db_name;

选择指定数据库

• 切换当前会话使用的数据库

USE database_name;

删除数据库

• 默认行为时RESTRICT，表示仅在数据库为空时才删除它。
• 要删除带表的数据库，需要使用CASCADE。

DROP(DATABASE|SCHEMA)[IF EXISTS]database_name[RESTRICT|CASCADE];

修改数据库

• 更改数据库属性

ALTER(DATABASE|SCHEMA)database_name SET DBPROPERTIES(property_name=property_value,...);

• 更爱数据库所有者

ALTER(DATABASE|SCHEMA)database_name SET OWNER USER user;

• 更改数据库位置

ALTER(DATABASE|SCHEMA)database_name SET LOCATION hdfs_path;

表DDL操作

• 由于Hive建表之后加载映射数据很快，实际中如果建表有问题，可以不用修改，直接删除重建。

显示表元数据信息

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

DESCRIBE [EXTENDED|FORMATTED] table_name;

删除表

• 若已配置垃圾桶且未指定PURGE，则该表对应的数据将移动到HDFS垃圾桶，而元数据完全丢失。
• 删除外部表时，该表的数据不会被删除。
• 若指定了PURGE，则表数据跳过垃圾桶，直接被删除，无法挽回数据。

DROP TABLE[IF EXISTS] table_name [PURGE];

清空表

• 删除表中所有数据，但保留元数据结构。
• 若HDFS启动了垃圾桶，则数据被丢进垃圾桶，否则被删除。

TRUNCATE [TABLE] table_name;

修改表

• 修改表名

ALTER TABLE table_name RENAME TO new_table_name;

• 更改表属性

ALTER TABLE table_name SET TBLPROPERTIES (property_name = property_value,...);

• 更改表注释

ALTER TABLE student SET TBLPROPERTIES('comment'="new comment");

• 更改SerDe属性

ALTER TABLE table_name SET SERDE serde_class_name [WITH SERDEPROPERTIES(property_name = property_name,...)];
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,...);

• 更改表的文件存储格式，该操作仅修改元数据

ALTER TABLE table_name SET FILEFORMAT file_format;

• 更改表的存储位置路径

ALTER TABLE table_name SET LOCATION "new location";

修改字段

• 修改列名
  将a的字段名改为a1

ALTER TABLE table_name CHANGE a a1 INT;

• 修改字段类型和位置
  将a1改为a2，类型改为STRING，并移动到b字段后。

ALTER TABLE table_name CHANGE a1 a2 STRING AFTER b;

将字段移动到第一个

ALTER TABLE table_name CHANGE a1 a2 STRING FIRST;

• 修改字段注释

ALTER TABLE table_name CHANGE a1 a2 COMMENT 'this is column a1';

添加、替换列

ALTER TABLE table_name ADD | REPLACE COLUMNS (col_name data_type,...);

分区DDL操作

添加分区

• ADD PARTITION会更改元数据，但不会加载数据。
• 若分区位置中不存在数据，查询时将不会返回结果。
• 因此需要保证增加的分区位置路径下，数据已经存在，或者增加完分区后导入分区数据。

# 可添加多个分区
ALTER TABLE table_name ADD PARTITION (dt='2008') LOCATION '/path/to/us/part08' PARTITION (dt='2009') LOCATION '/path/to/us/part09';

重命名分区

ALTER TABLE table_name PARTITION (dt='2008')RENAME TO PARTITION(dt='08');

删除分区

• 将删除数据和元数据

ALTER TABLE table_name DROP [IF EXISTS] PARTITION(dt='2008',country='us')[PURGE];

修改分区

• 更改分区文件存储格式

ALTER TABLE table_name PARTITION(dt='2008')SET FILEFORMAT file_format;

• 更改分区位置

ALTER TABLE table_name PARTITION(dt='2008')SET LOCATION "new location";

MSCK分区修复

• 当分区文件不通过Hive发生变化时，Hive不会意识到分区信息的更改。
• MSCK表示元数据检查操作，用于元数据的修复。
• MSCK默认为ADD PARTITIONS，使用此选项将把HDFS上存在，但元数据中不存在的分区添加到元数据中。
• DROP PARTITIONS选项将从已经从HDFS中删除的元数据中删除分区信息。
• SYNC PARTITIONS选项等价于调用ADD和DROP PARTITIONS。
• 如果存在大量未跟踪的分区，可以批量运行MSCK REPAIR TABLE，以避免内存不足错误。

MSCK [REPAIR]TABLE table_name [ADD/DROP/SYNC PARTITIONS];

Hive Show 语法

• Show相关语句提供了一种查询Hive 元数据的方法。

显示数据库

• SCHEMAS和DATABASES用法和功能一样

SHOW DATABASES;
SHOW SCHEMAS;

显示所有表、视图、分区、索引

SHOW TABLE
SHOW TABLES [IN database_name]; --指定某个数据库

显示当前数据库下所有视图

SHOW VIEWS；

• 显示物化视图

SHOW MATERIALIZED VIEWS [IN/FROM database_name];

显示分区信息

SHOW PARTITIONS table_name;

• 显示表、分区扩展信息

SHOW TABLE EXTENDED [IN|FROM database_name]LIKE table_name;