Hive Data Definition Language (DDL)

目录

• Create/Drop/Alter/Use Database
  • Create Database
  • Drop Database
  • Alter Database
  • Use Database
• Create/Drop/Alter Connector （hive4使用）
  • Create Connector
  • Drop Connector
  • Alter Connector
• Create/Drop/Truncate Table
  • Create Table
  • Drop Table
  • Truncate Table
• Alter Table/Partition/Column
  • Rename Table
  • Alter Table Properties
  • Add SerDe Properties
  • Remove SerDe Properties （hive4启用）
  • Alter Table Storage Properties
  • Alter Table Skewed or Stored as Directories
  • Alter Table Constraints
  • Alter Partition
    • Add Partitions
    • Rename Partition
    • Exchange Partition
    • Recover Partitions (MSCK REPAIR TABLE)
    • Drop Partitions
  • Alter Either Table or Partition（更改其一）
    • Alter Table/Partition File Format
    • Alter Table/Partition Location
    • Alter Table/Partition Touch
    • Alter Table/Partition Protections
    • Alter Table/Partition Compact（更改压缩设置）
    • Alter Table/Partition Concatenate（更改文件合并设置）
    • Alter Table/Partition Update Columns
  • Alter Column
    • Change Column Name/Type/Position/Comment
    • Add/Replace Columns（添加/更新列）
    • Partial Partition Specification（部分分区规范）
• Create/Drop/Alter View（视图）
  • Create View
  • Drop View
  • Alter View Properties
  • Alter View As Select
• Create/Drop/Alter Materialized View（hive3启用 物化视图）
  • Create Materialized View
  • Drop Materialized View
  • Alter Materialized View
• Create/Drop/Alter Index（hive3已移除）
• Create/Drop Macro（宏，类似自定义函数，无需Java实现）
  • Create Temporary Macro
  • Drop Temporary Macro
• Create/Drop/Reload Function
  • Create Temporary Function（临时函数，session范围）
  • Drop Temporary Function
  • Create Function（永久函数，全局范围）
  • Drop Function
  • Reload Function
• Show
  • Show Databases
  • Show Connectors
  • Show Tables
  • Show Views
  • Show Materialized Views
  • Show Partitions
  • Show Table/Partition Extended（扩展信息）
  • Show Table Properties
  • Show Create Table /View
  • Show Indexes（hive3移除）
  • Show Columns
  • Show Functions
  • Show Locks
  • Show Conf
  • Show Transactions
  • Show Compactions
• Describe
  • Describe Database
  • Describe Dataconnector（hive4启用）
  • Describe Table/View/Materialized View/Column
  • Display Column Statistics（列统计信息 Statistics in Hive）
  • Describe Partition

Create/Drop/Alter/Use Database

Create Database

CREATE [REMOTE] (DATABASE|SCHEMA) [IF NOT EXISTS] database_name --REMOTE: hive4使用
  [COMMENT database_comment]
  [LOCATION hdfs_path] -- hive4以下使用：指定内/外部存储位置
  [MANAGEDLOCATION hdfs_path] -- hive4使用：LOCATION指定外部表存储位置，MANAGEDLOCATION指定内部表存储位置
  [WITH DBPROPERTIES (property_name=property_value, ...)];

Drop Database

DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE]; --默认RESTRICT：数据库不为空将失败；CASCADE：强制全部删除

Alter Database

--修改参数
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;
--hive4使用
ALTER (DATABASE|SCHEMA) database_name SET MANAGEDLOCATION hdfs_path;

Use Database

USE database_name; --切换数据库
USE DEFAULT; --回到默认库

SELECT current_database(); --查看当前使用数据库

Create/Drop/Alter Connector （hive4使用）

Create Connector

CREATE CONNECTOR [IF NOT EXISTS] connector_name
  [TYPE datasource_type] --此连接器连接到的远程数据源的类型。例如MYSQL。该类型确定 Driver 类以及特定于此数据源的任何其他参数
  [URL datasource_url] --远程数据源的 URL
  [COMMENT connector_comment]
  [WITH DCPROPERTIES (property_name=property_value, ...)];

Drop Connector

DROP CONNECTOR [IF EXISTS] connector_name;

Alter Connector

ALTER CONNECTOR connector_name SET DCPROPERTIES (property_name=property_value, ...);
ALTER CONNECTOR connector_name SET URL new_url;
ALTER CONNECTOR connector_name SET OWNER [USER|ROLE] user_or_role;

Create/Drop/Truncate Table

Create Table

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.] table_name -- 临时表 外部表
  [(col_name data_type [column_constraint_specification] [COMMENT col_comment], ... 		    [constraint_specification])]
  [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] -- 分桶
  [SKEWED BY (col_name, col_name, ...)
  	ON ((col_value, col_value, ...), (col_value, col_value, ...), ...)
 	[STORED AS DIRECTORIES]] -- SKEWED BY (dept_no) ON (1000, 2000)：根据values将数据放入对应文件中 
  [
   [ROW FORMAT row_format] -- row format delimited fields terminated by '\t' 
   [STORED AS file_format] -- 默认 stored as textfile
 	| STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)]  
  ]
  [LOCATION hdfs_path] -- 指定HDFS路径
  [TBLPROPERTIES (property_name=property_value, ...)]
  [AS select_statement]; -- 以查询语句结果建表
  
  -- 以现有表/视图建表
  CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
  LIKE existing_table_or_view_name 
  [LOCATION hdfs_path];
  
data_type:
  	| primitive_type:
  		TINYINT             -- 微整型，占用1个字节，存储 0-255 的整数
  		SMALLINT         -- 小整型，占用2个字节，存储 –32768 到 32767 的整数
  		INT			-- 整型，占用4个字节，存储 -2147483648 到 2147483647 的整数
  		BIGINT		-- 长整型，占用8个字节，存储 -2^63 到 2^63-1 的整数
  		BOOLEAN		-- TRUE/FALSE
 		FLOAT		-- 单精度浮点型，占用4个字节
  		DOUBLE		-- 双精度浮点型，占用8个字节
  		DOUBLE PRECISION -- 指定显示长度 
  		STRING		-- 不限长字符串（2G）
  		BINARY              -- byte[]
 		TIMESTAMP  	-- yyyy-MM-dd HH:MM:SS (2022-01-01 12:00:00.200)
 		DATE                -- 2022-01-01
  		DECIMAL		-- 默认 DECIMAL(10, 0)：显示总长度10，小数位0
 		DECIMAL(precision, scale)  -- 指定显示总长度和小数位
  		VARCHAR         -- 变长字符串（65535）
  		CHAR		-- 定长字符串（255）；VARCHAR(20): 最大长度是20，CHAR(20)：固定长度是20
  	| array_type:
  		ARRAY < data_type >
  	| map_type:
  		MAP < primitive_type, data_type >
  	| struct_type:
  		STRUCT < col_name : data_type [COMMENT col_comment], ...>
  	| union_type:
  		UNIONTYPE < data_type, data_type, ... > 
  		
row_format:
  	DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char]
        [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
        [NULL DEFINED AS char]   -- (Note: Available in Hive 0.13 and later)
  | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)] -- SERDE:Serializer/Deserializer 序列化与反序列化
  
file_format:
  	SEQUENCEFILE	-- 基于行存储，二进制文件，可压缩、可切分（并行处理），优化磁盘利用率和I/O，查询效率高；存储空间消耗最大，通过text文件转换加载
  	TEXTFILE    -- 默认！基于行存储，不压缩，磁盘开销大，不可切分；便于查看和编辑
  	RCFILE      -- 行列存储相结合。首先，将数据按行分块，保证同一个record在一个块上；其次，块数据列式存储，有利于数据压缩和快速的列查询
  	ORC         -- 常用！RCFILE的改进版本：压缩快，快速列存取；加载时性能消耗较大，通过text文件转换加载
  	PARQUET     -- 常用！基于列存储，二进制文件，可压缩，查询性能好；写较慢
  	AVRO        -- 数据序列化，有丰富的数据结构类型、快速可压缩，二进制数据
  	JSONFILE    -- 直接存储JSON文件，hive4启用
  	INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname -- 自定义
 
column_constraint_specification:
	[ PRIMARY KEY|UNIQUE|NOT NULL|DEFAULT [default_value]|CHECK  [check_expression] ENABLE|DISABLE NOVALIDATE RELY/NORELY ]
 
default_value:
	[ LITERAL|CURRENT_USER()|CURRENT_DATE()|CURRENT_TIMESTAMP()|NULL ] 
 
constraint_specification:
  	[, PRIMARY KEY (col_name, ...) DISABLE NOVALIDATE RELY/NORELY ]
    [, PRIMARY KEY (col_name, ...) DISABLE NOVALIDATE RELY/NORELY ]
    [, CONSTRAINT constraint_name FOREIGN KEY (col_name, ...) REFERENCES table_name(col_name, ...) DISABLE NOVALIDATE 
    [, CONSTRAINT constraint_name UNIQUE (col_name, ...) DISABLE NOVALIDATE RELY/NORELY ]
    [, CONSTRAINT constraint_name CHECK [check_expression] ENABLE|DISABLE NOVALIDATE RELY/NORELY ]

TBLPROPERTIES:
	TBLPROPERTIES ("comment"="table_comment")
	TBLPROPERTIES ("hbase.table.name"="table_name")
	TBLPROPERTIES ("immutable"="true") or ("immutable"="false")  –- 从查询中插入数据到表：从Hive 0.13.0开始，可以通过使用TBLPROPERTIES ("immutable"="true") 创建表来使其不可变，默认值为 "false"。
如果已存在任何数据，则不允许将 INSERT INTO 行为插入到不可变表中，但如果不可变表为空，则 INSERT INTO 仍然有效。INSERT OVERWRITE 的行为不受不可变表属性的影响。不可变表受到保护，以防止由于脚本将数据加载到其中而意外更新，该脚本被错误地运行了多次。第一次插入到不可变表中会成功，而连续插入会失败，从而导致表中只有一组数据。
	TBLPROPERTIES ("orc.compress"="ZLIB") or ("orc.compress"="SNAPPY") or ("orc.compress"="NONE") and other ORC properties -- 指定压缩格式
	TBLPROPERTIES ("transactional"="true") or ("transactional"="false") -- 如果一个表要用于ACID写入(插入，更新，删除)，那么必须在该表上设置表属性 ("transactional"="true")。注意，一旦表通过TBLPROPERTIES ("transactional"="true") 被定义为ACID表，它就不能被转换回非ACID表，也就是说，不允许更改TBLPROPERTIES ("transactional"="false")。
	TBLPROPERTIES ("NO_AUTO_COMPACTION"="true") or ("NO_AUTO_COMPACTION"="false") -- 如果表所有者不希望系统自动决定何时进行压缩，那么可以设置表属性 ("NO_AUTO_COMPACTION"="true")，默认"false"。手动压缩仍然可以使用 Alter Table/Partition Compact 语句完成。
	TBLPROPERTIES ("compactor.mapreduce.map.memory.mb"="mapper_memory") -- 指定压缩 mapjob 时内存
	TBLPROPERTIES ("compactorthreshold.hive.compactor.delta.num.threshold"="threshold_num") -- 如果超过 threshold_num 个增量目录，则触发 minor 压缩
	TBLPROPERTIES ("compactorthreshold.hive.compactor.delta.pct.threshold"="threshold_pct") -- 如果增量文件的大小与基本文件的大小之比大于50%，则触发 major 压缩
	TBLPROPERTIES ("auto.purge"="true") or ("auto.purge"="false")  -- 仅内部表使用，truncate表时数据不会进入 Trash
	TBLPROPERTIES ("EXTERNAL"="TRUE") -- 将内部表更改为外部表，“FALSE” 则相反
	TBLPROPERTIES ("external.table.purge"="true") -- hive4启用，外部表数据也可被删除

Drop Table

DROP TABLE [IF EXISTS] table_name [PURGE]; -- 删除该表的元数据和数据（删除外部表时，数据不会于文件系统中被删除）。如果配置了 Trash (且未指定 PURGE)，数据实际上会移动到 .Trash/Current 目录。元数据则完全丢失。 

Truncate Table

TRUNCATE [TABLE] table_name [PARTITION partition_spec]; -- 删除（仅内部）表/分区内全部数据，（hive2.2.0）如果配置了 Trash，数据实际上会移动到 .Trash/Current 目录。
partition_spec:
	(partition_column = partition_col_value, partition_column = partition_col_value, ...)

Alter Table/Partition/Column

Rename Table

ALTER TABLE table_name RENAME TO new_table_name;

Alter Table Properties

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

-- Alter Table Comment
ALTER TABLE table_name SET TBLPROPERTIES ('comment' = new_comment);

Add SerDe Properties

ALTER TABLE table_name [PARTITION partition_spec] SET SERDE serde_class_name [WITH SERDEPROPERTIES serde_properties];

ALTER TABLE table_name [PARTITION partition_spec] SET SERDEPROPERTIES serde_properties;

serde_properties:
  : (property_name = property_value, property_name = property_value, ... )
  
-- 例
ALTER TABLE table_name SET SERDEPROPERTIES ('field.delim' = ',');

Remove SerDe Properties （hive4启用）

ALTER TABLE table_name [PARTITION partition_spec] UNSET SERDEPROPERTIES (property_name, ... );

-- 例
ALTER TABLE table_name UNSET SERDEPROPERTIES ('field.delim');

Alter Table Storage Properties

ALTER TABLE table_name CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name, ...)]
  INTO num_buckets BUCKETS;

Alter Table Skewed or Stored as Directories

-- Alter Table Skewed
ALTER TABLE table_name SKEWED BY (col_name1, col_name2, ...)
  ON ([(col_name1_value, col_name2_value, ...) [, (col_name1_value, col_name2_value), ...]
  [STORED AS DIRECTORIES];
       
-- Alter Table Not Skewed
ALTER TABLE table_name NOT SKEWED;

-- Alter Table Not Stored as Directories
ALTER TABLE table_name NOT STORED AS DIRECTORIES;
       
-- Alter Table Set Skewed Location
ALTER TABLE table_name SET SKEWED LOCATION (col_name1="location1" [, col_name2="location2", ...] );

Alter Table Constraints

-- ENABLE NOVALIDATE: 启用约束，创建索引，仅对新加入的数据强制执行约束，而不管表中的现有数据
-- DISABLE NOVALIDATE: 关闭约束，删除索引，可以对约束列的数据进行修改等操作
-- RELY/NORELY同样是可选的。如果一个约束指定RELY，也就是希望HIVE CBO（cost based optimize）使用约束信息来获得更好的统计，对不必要的连接删除（ join elimination），来得到更好的整体执行计划。
-- DISABLE 和 NOVALIDATE 现在是强制性的
ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY (column, ...) DISABLE NOVALIDATE;
ALTER TABLE table_name ADD CONSTRAINT constraint_name FOREIGN KEY (column, ...) REFERENCES table_name(column, ...) DISABLE NOVALIDATE RELY;
ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE (column, ...) DISABLE NOVALIDATE;
ALTER TABLE table_name CHANGE COLUMN column_name column_name data_type CONSTRAINT constraint_name NOT NULL ENABLE;
ALTER TABLE table_name CHANGE COLUMN column_name column_name data_type CONSTRAINT constraint_name DEFAULT default_value ENABLE;
ALTER TABLE table_name CHANGE COLUMN column_name column_name data_type CONSTRAINT constraint_name CHECK check_expression ENABLE;

ALTER TABLE table_name DROP CONSTRAINT constraint_name;

Alter Partition

Add Partitions

ALTER TABLE table_name ADD [IF NOT EXISTS] PARTITION partition_spec [LOCATION 'location'][, PARTITION partition_spec [LOCATION 'location'], ...];

partition_spec:
  : (partition_column = partition_col_value, partition_column = partition_col_value, ...)
  
-- 例
ALTER TABLE page_view 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

ALTER TABLE table_name PARTITION partition_spec RENAME TO PARTITION partition_spec;

Exchange Partition

-- 此语句允许将分区中的数据从一个表移动到另一个具有相同模式但尚未拥有该分区的表。

-- 移动分区数据从 table_name_1 到 table_name_2
ALTER TABLE table_name_2 EXCHANGE PARTITION (partition_spec) WITH TABLE table_name_1;
-- 移动多分区数据
ALTER TABLE table_name_2 EXCHANGE PARTITION (partition_spec, partition_spec2, ...) WITH TABLE table_name_1;

Recover Partitions (MSCK REPAIR TABLE)

更新元数据中因 直接于HDFS上进行的数据文件操作而元数据中不存在的分区信息

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

-- 例
MSCK REPAIR TABLE test [ADD PARTITIONS]; -- 它会添加任何存在于HDFS但不在元数据的分区到元数据
MSCK REPAIR TABLE test DROP PARTITIONS; -- 它会从元数据中删除已经从HDFS删除的分区信息
MSCK REPAIR TABLE test SYNC PARTITIONS; -- = ADD + DROP

Drop Partitions

ALTER TABLE table_name DROP [IF EXISTS] PARTITION partition_spec [, PARTITION partition_spec, ...];

-- 如果表被 NO_DROP CASCADE 保护，则
ALTER TABLE table_name DROP [IF EXISTS] PARTITION partition_spec IGNORE PROTECTION;

-- 不进入 Trash
ALTER TABLE table_name DROP [IF EXISTS] PARTITION partition_spec PURGE;

Alter Either Table or Partition（更改其一）

Alter Table/Partition File Format

ALTER TABLE table_name [PARTITION partition_spec] SET FILEFORMAT file_format;

Alter Table/Partition Location

ALTER TABLE table_name [PARTITION partition_spec] SET LOCATION "new location";

Alter Table/Partition Touch

ALTER TABLE table_name TOUCH [PARTITION partition_spec];

TOUCH 读取元数据，并将其写回。这将导致触发前/后执行 hooks。一个示例用例是，如果有一个 hook 记录所有被修改的表/分区，以及一个直接修改HDFS上文件的外部脚本，因为脚本修改了 hive 外部的文件，所以 hook 不会记录修改的内容。外部脚本可以调用 TOUCH 来触发 hook，并将上述表或分区标记为已修改。注意，TOUCH不会创建不存在的表或分区。

Alter Table/Partition Protections

-- 启用 NO_DROP 将防止表被删除
ALTER TABLE table_name [PARTITION partition_spec] ENABLE|DISABLE NO_DROP [CASCADE];

-- 启用 OFFLINE 可以防止查询表或分区中的数据，但仍然可以访问元数据
ALTER TABLE table_name [PARTITION partition_spec] ENABLE|DISABLE OFFLINE;

Alter Table/Partition Compact（更改压缩设置）

ALTER TABLE table_name [PARTITION (partition_key = 'partition_value' [, ...])]
  COMPACT 'compaction_type'[AND WAIT]
  [WITH OVERWRITE TBLPROPERTIES ("property"="value" [, ...])];

通常情况下，当 Hive transactions 被使用时，不需要请求压缩，因为系统会检测到它们的需求，并初始化压缩。
但是，如果关闭了对表的压缩，或者您希望在系统不愿意的时候对表进行压缩，那么 ALTER table 可以启动压缩。
默认情况下，该语句将对压缩请求排队并返回。要查看压缩过程，请使用SHOW COMPACTIONS。在 Hive 2.2.0版本中，“AND WAIT” 可以被指定为操作阻塞，直到压缩完成。

Alter Table/Partition Concatenate（更改文件合并设置）

ALTER TABLE table_name [PARTITION (partition_key = 'partition_value' [, ...])] CONCATENATE;

如果表或分区包含许多小的 RCFile 或 ORC 文件，那么上面的命令会将它们合并成更大的文件。对于 RCFile，合并发生在 block 级别，而对于 ORC 文件，合并发生在 stripe 级别，从而避免了解压和解码数据的开销。

Alter Table/Partition Update Columns

ALTER TABLE table_name [PARTITION (partition_key = 'partition_value' [, ...])] UPDATE COLUMNS;

具有自描述表模式的 serdes 的表在现实中可能与存储在 Hive Metastore 中的表有不同的模式。例如，当用户使用模式 url 或模式文字创建一个 Avro 存储表时，模式将被插入到 HMS 中，然后无论 serde 中的 url 或文字如何变化，模式都不会在 HMS 中被更改。这可能会导致问题，尤其是在与其他Apache组件集成时。

更新列特性为用户提供了一种将 serde 中所做的任何模式更改同步到 HMS 的方法。它同时适用于表和分区级别，而且显然只适用于那些模式没有被 HMS 跟踪的表（见metastore.serdes.use.metastore.for.schema）。在后面这些serde 类型上使用该命令将导致错误。

Alter Column

Change Column Name/Type/Position/Comment

ALTER TABLE table_name [PARTITION partition_spec] CHANGE [COLUMN] col_old_name col_new_name column_type
  [COMMENT col_comment]
  [FIRST|AFTER column_name] -- 指定与某列的相对位置
  [CASCADE|RESTRICT] -- CASCADE 命令更改表元数据的列，并将相同的更改级联到所有分区元数据。 RESTRICT 是默认值，将列更改仅限于表元数据。
  ;
  
-- 例
	CREATE TABLE test_change (a int, b int, c int);
	-- 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';

Add/Replace Columns（添加/更新列）

-- 添加新列至所有列的最后且分区列之前 /更新列
ALTER TABLE table_name 
  [PARTITION partition_spec]
  ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)
  [CASCADE|RESTRICT];
-- REPLACE COLUMNS 删除所有现有列并添加新的列集，也可用来删除列，例如前面的test_change表："ALTER TABLE test_change REPLACE COLUMNS (a int, b int);" 则会删除 column c

Partial Partition Specification（部分分区规范）

-- 更改多个分区需要为每个分区发出下面语句
ALTER TABLE foo PARTITION (ds='2008-04-08', hr=11) CHANGE COLUMN dec_column_name dec_column_name DECIMAL(38,18);
ALTER TABLE foo PARTITION (ds='2008-04-08', hr=12) CHANGE COLUMN dec_column_name dec_column_name DECIMAL(38,18);
...

-- 使用 Partial Partition Specification
SET hive.exec.dynamic.partition = true; -- 动态分区
-- 更改表中所有 ds='2008-04-08' 的分区
ALTER TABLE foo PARTITION (ds='2008-04-08', hr) CHANGE COLUMN dec_column_name dec_column_name DECIMAL(38,18);
-- 更改表中所有分区
ALTER TABLE foo PARTITION (ds, hr) CHANGE COLUMN dec_column_name dec_column_name DECIMAL(38,18);

Create/Drop/Alter View（视图）

Create View

CREATE VIEW [IF NOT EXISTS][db_name.]view_name [(column_name [COMMENT column_comment], ...) ]
  [COMMENT view_comment]
  [TBLPROPERTIES (property_name = property_value, ...)]
  AS SELECT ...;

请注意，视图是没有关联存储的纯逻辑对象。 当查询引用视图时，会评估视图的定义以生成一组行供查询进一步处理。 （这是一个概念性的描述；事实上，作为查询优化的一部分，Hive 可以将视图的定义与查询的定义结合起来，例如将过滤器从查询推送到视图中。）

• 视图是只读的，不能用作 LOAD / INSERT / ALTER 的目标；
• 在创建视图时候视图就已经固定，对基表的后续更改（如添加列）将不会反映在视图；
• 删除基表并不会删除视图，需要手动删除视图；
• 视图可能包含 ORDER BY 和 LIMIT 子句。如果引用视图的查询语句也包含这类子句，其执行优先级低于视图对应字句。例如，视图 custom_view 指定 LIMIT 5，查询语句为 select * from custom_view LIMIT 10，此时结果最多返回 5 行。
• 创建视图时，如果未提供列名，则将从 SELECT 语句中自动派生列名；
• 创建视图时，如果 SELECT 语句中包含其他表达式，例如 x + y，则列名称将以_C0，_C1等形式生成,有一点需要注意的是，C0 并不是从第一个表达式字段开始的，而是从第一个定义字段开始的

Drop View

DROP VIEW [IF EXISTS][db_name.]view_name;

删除由其他视图引用的视图时，不会发出警告（相关视图会悬空而无效，必须由用户删除或重新创建）。

Alter View Properties

ALTER VIEW [db_name.]view_name SET TBLPROPERTIES table_properties;

table_properties:
  : (property_name = property_value, property_name = property_value, ...)

Alter View As Select

ALTER VIEW [db_name.]view_name AS select_statement;

Alter View As Select 更改必须存在的视图的定义。 语法与 CREATE VIEW 类似，效果与 CREATE OR REPLACE VIEW 相同。注意：视图必须已经存在，如果视图有分区，则不能用 Alter View As Select 替换。

Create/Drop/Alter Materialized View（hive3启用 物化视图）

物化视图作为 Hive3.x 的新特性，可以将查询的结果保存到一张表上，供客户端之后如果查询同样的数据时，就可以直接返回结果，而不用再执行 MR 程序，效率极高。

Create Materialized View

-- 实体化视图列的名称将自动从定义的 SELECT 表达式中派生。
CREATE MATERIALIZED VIEW [IF NOT EXISTS][db_name.]materialized_view_name
  [DISABLE REWRITE] -- 是否可以重写
  [COMMENT materialized_view_comment]
  [PARTITIONED ON (col_name, ...)] -- hive 3.2.0
  [CLUSTERED ON (col_name, ...) | DISTRIBUTED ON (col_name, ...) SORTED ON (col_name, ...)] -- hive 4.0.0
  [
	[ROW FORMAT row_format]
	[STORED AS file_format]
  		| STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)]
  ]
  [LOCATION hdfs_path]
  [TBLPROPERTIES (property_name=property_value, ...)]
AS SELECT ...;

Drop Materialized View

DROP MATERIALIZED VIEW [db_name.]materialized_view_name;

Alter Materialized View

ALTER MATERIALIZED VIEW [db_name.]materialized_view_name ENABLE|DISABLE REWRITE;

一旦创建了物化视图，优化器将能够利用其定义语义使用物化视图自动重写传入的查询，从而加速查询执行。用户可以选择性地启用/禁用物化视图进行重写。默认情况下，物化视图在创建时启用重写。

Create/Drop/Alter Index（hive3已移除）

Create/Drop Macro（宏，类似自定义函数，无需Java实现）

Create Temporary Macro

CREATE TEMPORARY MACRO macro_name([col_name col_type, ...]) expression;

-- 例
CREATE TEMPORARY MACRO fixed_number() 42;
CREATE TEMPORARY MACRO string_len_plus_two(x string) length(x) + 2;
CREATE TEMPORARY MACRO simple_add (x int, y int) x + y;

Drop Temporary Macro

DROP TEMPORARY MACRO [IF EXISTS] macro_name;

Create/Drop/Reload Function

Create Temporary Function（临时函数，session范围）

CREATE TEMPORARY FUNCTION function_name AS class_name;

此语句允许您创建一个由 class_name 实现的函数。 只要会话持续，您就可以在 Hive 查询中使用此功能。 您可以使用 Hive 类路径中的任何类。 您可以通过执行 'ADD JAR' 语句将 jars 添加到类路径。

Drop Temporary Function

DROP TEMPORARY FUNCTION [IF EXISTS] function_name;

Create Function（永久函数，全局范围）

CREATE FUNCTION [db_name.]function_name AS class_name
  [USING JAR|FILE|ARCHIVE 'file_uri' [, JAR|FILE|ARCHIVE 'file_uri'] ];

此语句允许您创建一个由 class_name 实现的函数。 可以使用 USING 子句指定需要添加到环境中的 JAR、FILE 或 ARCHIVE； 当 Hive 会话首次引用该函数时，这些资源将被添加到环境中，就像已发出 ADD JAR/FILE 一样。 如果 Hive 未处于本地模式，则资源位置必须是非本地 URI，例如 HDFS 位置。

该函数将被添加到指定的数据库中，或者在创建该函数时添加到当前数据库中。 可以通过完全限定函数名（db_name.function_name）来引用该函数，或者如果该函数在当前数据库中，则可以不加限定地引用该函数。

Drop Function

DROP FUNCTION [IF EXISTS] function_name;

Reload Function

集群上有多个 hiveServer2 实例的时候，在一个 hiveServer2 实例上注册的 Function 在另外一个 hiveServer2 实例上并不能马上看到新注册的实例，需要重新加载以刷新本实例的函数信息。

RELOAD (FUNCTIONS|FUNCTION);

Show

Show Databases

SHOW (DATABASES|SCHEMAS) [LIKE 'identifier_with_wildcards'];

Show Connectors

SHOW CONNECTORS;

Show Tables

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

Show Views

SHOW VIEWS [IN/FROM database_name][LIKE 'pattern_with_wildcards'];

Show Materialized Views

SHOW Materialized VIEWS [IN/FROM database_name][LIKE 'pattern_with_wildcards'];

Show Partitions

SHOW PARTITIONS table_name;
SHOW PARTITIONS [db_name.]table_name [PARTITION(partition_spec)]; 

SHOW PARTITIONS [db_name.]table_name [PARTITION(partition_spec)] [WHERE where_condition] [ORDER BY col_list] [LIMIT rows];   -- hive4启用
-- 例
	SHOW PARTITIONS databaseFoo.tableBar LIMIT 10;  
	SHOW PARTITIONS databaseFoo.tableBar PARTITION(ds='2010-03-03') LIMIT 10; 
	SHOW PARTITIONS databaseFoo.tableBar PARTITION(ds='2010-03-03') ORDER BY hr DESC LIMIT 10;
	SHOW PARTITIONS databaseFoo.tableBar PARTITION(ds='2010-03-03') WHERE hr >= 10 ORDER BY hr DESC LIMIT 10;  
  	SHOW PARTITIONS databaseFoo.tableBar WHERE hr >= 10 AND ds='2010-03-03' ORDER BY hr DESC LIMIT 10;   

Show Table/Partition Extended（扩展信息）

将列出与给定正则表达式匹配的所有表的信息。 此命令的输出包括基本表信息和文件系统信息，如 totalNumberFiles、totalFileSize、maxFileSize、minFileSize、lastAccessTime 和 lastUpdateTime。 如果分区存在，它将输出给定分区的文件系统信息，而不是表的文件系统信息。

SHOW TABLE EXTENDED [IN|FROM database_name] LIKE 'identifier_with_wildcards' [PARTITION(partition_spec)];

-- 例
hive> show table extended like part_table;
OK
tableName:part_table
owner:thejas
location:file:/tmp/warehouse/part_table
inputformat:org.apache.hadoop.mapred.TextInputFormat
outputformat:org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
columns:struct columns { i32 i}
partitioned:true
partitionColumns:struct partition_columns { string d}
totalNumberFiles:1
totalFileSize:2
maxFileSize:2
minFileSize:2
lastAccessTime:0
lastUpdateTime:1459382233000

Show Table Properties

SHOW TBLPROPERTIES tblname;
SHOW TBLPROPERTIES tblname("foo");

Show Create Table /View

SHOW CREATE TABLE ([db_name.]table_name|view_name);

Show Indexes（hive3移除）

SHOW [FORMATTED] (INDEX|INDEXES) ON table_with_index [(FROM|IN) db_name];

Show Columns

SHOW COLUMNS (FROM|IN) table_name [(FROM|IN) db_name];
SHOW COLUMNS (FROM|IN) table_name [(FROM|IN) db_name]  [ LIKE 'pattern_with_wildcards']; -- hive3启用

-- 例
CREATE DATABASE test_db;
USE test_db;
CREATE TABLE foo(col1 INT, col2 INT, col3 INT, cola INT, colb INT, colc INT, a INT, b INT, c INT);

SHOW COLUMNS FROM foo;                            -- show all column in foo
SHOW COLUMNS FROM foo "*";                        -- show all column in foo
SHOW COLUMNS IN foo "col*";                       -- OUTPUT col1,col2,col3,cola,colb,colc
SHOW COLUMNS FROM foo '*c';                       -- OUTPUT c,colc
SHOW COLUMNS FROM foo LIKE "col1|cola";           -- OUTPUT col1,cola
SHOW COLUMNS FROM foo FROM test_db LIKE 'col*';   -- OUTPUT col1,col2,col3,cola,colb,colc
SHOW COLUMNS IN foo IN test_db LIKE 'col*';       -- OUTPUT col1,col2,col3,cola,colb,colc

Show Functions

SHOW FUNCTIONS [LIKE "<pattern>"];

Show Locks

SHOW LOCKS <table_name>;
SHOW LOCKS <table_name> EXTENDED;
SHOW LOCKS <table_name> PARTITION (<partition_spec>);
SHOW LOCKS <table_name> PARTITION (<partition_spec>) EXTENDED;
SHOW LOCKS (DATABASE|SCHEMA) database_name;  

Show Conf

SHOW CONF <configuration_name>;

[Configuration Properties]

Show Transactions

SHOW TRANSACTIONS;

-- including:
transaction ID
transaction state
user who started the transaction
machine where the transaction was started
timestamp when the transaction was started (as of Hive 2.2.0)
timestamp for last heartbeat (as of Hive 2.2.0 )

Show Compactions

SHOW COMPACTIONS;

-- including:
"CompactionId" - unique internal id (As of Hive 3.0)
"Database" - Hive database name
"Table" - table name
"Partition" - partition name (if the table is partitioned)
"Type" - whether it is a major or minor compaction
"State" - the state the compaction is in, which can be:
	"initiated" – waiting in the queue to be compacted
	"working" – being compacted
	"ready for cleaning" – the compaction has been done and the old files are scheduled to be cleaned
	"failed" – the job failed. The metastore log will have more detail.
	"succeeded" – A-ok
	"attempted" – initiator attempted to schedule a compaction but failed. The metastore log will have more information.
"Worker" - thread ID of the worker thread doing the compaction (only if in working state)
"Start Time" - the time at which the compaction started (only if in working or ready for cleaning state)
"Duration(ms)" - time this compaction took (As of Hive 2.2 )
"HadoopJobId" - Id of the submitted Hadoop job (As of Hive 2.2)

Describe

Describe Database

DESCRIBE DATABASE|SCHEMA [EXTENDED] db_name;

Describe Dataconnector（hive4启用）

DESCRIBE CONNECTOR [EXTENDED] connector_name;

Describe Table/View/Materialized View/Column

该语法有两种格式，具体取决于是否指定了数据库。

-- 如果未指定数据库，则在点之后提供可选的列信息：
DESCRIBE [EXTENDED|FORMATTED] 
  table_name[.col_name ( [.field_name] | [.'$elem$'] | [.'$key$'] | [.'$value$'] )* ];

-- 如果指定了数据库，则在空格后提供可选的列信息：
DESCRIBE [EXTENDED|FORMATTED] 
  [db_name.]table_name[ col_name ( [.field_name] | [.'$elem$'] | [.'$key$'] | [.'$value$'] )* ];

DESCRIBE 显示列列表，包括给定表的分区列。 如果指定了 EXTENDED 关键字，那么它将以 Thrift 序列化形式显示表的所有元数据。如果指定了 FORMATTED 关键字，那么它将以表格格式显示元数据。

如果一个表有一个复杂的列，那么您可以通过指定 table_name.complex_col_name 来检查该列的属性（和 field_name 用于结构的元素，'$elem$' 用于数组元素，'$key$' 用于映射键，以及 '$value$' 用于映射值）。 您可以递归地指定它来探索复杂的列类型。

Display Column Statistics（列统计信息 Statistics in Hive）

DESCRIBE FORMATTED [db_name.]table_name column_name; 
DESCRIBE FORMATTED [db_name.]table_name column_name PARTITION (partition_spec);  

Describe Partition

-- 如果未指定数据库，则在点之后提供可选的列信息：
DESCRIBE [EXTENDED|FORMATTED] table_name[.column_name] PARTITION partition_spec;

-- 如果指定了数据库，则在空格后提供可选的列信息：
DESCRIBE [EXTENDED|FORMATTED] [db_name.]table_name [column_name] PARTITION partition_spec;