Hive数据定义语言DDL语法参考-智能数据建设与治理 Dataphin-阿里云

本文为您介绍Dataphin计算引擎为Hadoop时，系统支持的DDL命令及语法。

CREATE TABLE

创建表。

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name    -- (Note: TEMPORARY available in Hive 0.14.0 and later)
  [(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, ...)                  -- (Note: Available in Hive 0.10.0 and later)]
     ON ((col_value, col_value, ...), (col_value, col_value, ...), ...)
     [STORED AS DIRECTORIES]
  [
   [ROW FORMAT row_format] 
   [STORED AS file_format]
     | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)]  -- (Note: Available in Hive 0.6.0 and later)
  ]
  [LOCATION hdfs_path]
  [TBLPROPERTIES (property_name=property_value, ...)]   -- (Note: Available in Hive 0.6.0 and later)
  [AS select_statement];   -- (Note: Available in Hive 0.5.0 and later; not supported for external tables)
 
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
  LIKE existing_table_or_view_name
  [LOCATION hdfs_path];
 
data_type
  : primitive_type
  | array_type
  | map_type
  | struct_type
  | union_type  -- (Note: Available in Hive 0.7.0 and later)
 
primitive_type
  : TINYINT
  | SMALLINT
  | INT
  | BIGINT
  | BOOLEAN
  | FLOAT
  | DOUBLE
  | DOUBLE PRECISION -- (Note: Available in Hive 2.2.0 and later)
  | STRING
  | BINARY      -- (Note: Available in Hive 0.8.0 and later)
  | TIMESTAMP   -- (Note: Available in Hive 0.8.0 and later)
  | DECIMAL     -- (Note: Available in Hive 0.11.0 and later)
  | DECIMAL(precision, scale)  -- (Note: Available in Hive 0.13.0 and later)
  | DATE        -- (Note: Available in Hive 0.12.0 and later)
  | VARCHAR     -- (Note: Available in Hive 0.12.0 and later)
  | CHAR        -- (Note: Available in Hive 0.13.0 and later)
 
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, ... >  -- (Note: Available in Hive 0.7.0 and later)
 
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, ...)]
 
file_format:
  : SEQUENCEFILE
  | TEXTFILE    -- (Default, depending on hive.default.fileformat configuration)
  | RCFILE      -- (Note: Available in Hive 0.6.0 and later)
  | ORC         -- (Note: Available in Hive 0.11.0 and later)
  | PARQUET     -- (Note: Available in Hive 0.13.0 and later)
  | AVRO        -- (Note: Available in Hive 0.14.0 and later)
  | JSONFILE    -- (Note: Available in Hive 4.0.0 and later)
  | 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 ]

更多信息请参见CREATE TABLE。

DROP TABLE

删除表。

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

更多信息请参见DROP TABLE。

TRUNCATE TABLE

截断表。

TRUNCATE [TABLE] table_name [PARTITION partition_spec];
 
partition_spec:
  : (partition_column = partition_col_value, partition_column = partition_col_value, ...)

更多信息请参见TRUNCATE TABLE。

ALTER TABLE

修改表。

ALTER TABLE table_name RENAME TO new_table_name;

更多信息请参见ALTER TABLE。

ALTER PARTITION

修改分区。

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 PARTITION。

ALTER COLUMN

修改字段。

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];

更多信息请参见ALTER COLUMN。

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 ...;

更多信息请参见CREATE VIEW。

DROP VIEW

删除视图。

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

更多信息请参见DROP VIEW。

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 PROPERTIES。

CREATE MATERIALIZED VIEW

创建物化视图。

CREATE MATERIALIZED VIEW [IF NOT EXISTS] [db_name.]materialized_view_name
  [DISABLE REWRITE]
  [COMMENT materialized_view_comment]
  [PARTITIONED ON (col_name, ...)]
  [CLUSTERED ON (col_name, ...) | DISTRIBUTED ON (col_name, ...) SORTED ON (col_name, ...)]
  [
    [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 ...;

更多信息请参见CREATE MATERIALIZED VIEW。

DROP MATERIALIZED VIEW

删除物化视图。

DROP MATERIALIZED VIEW [db_name.]materialized_view_name;

更多信息请参见DROP MATERIALIZED VIEW。

ALTER MATERIALIZED VIEW

修改物化视图。

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

更多信息请参见ALTER MATERIALIZED VIEW。

CREATE INDEX

创建索引。

CREATE INDEX index_name
  ON TABLE base_table_name (col_name, ...)
  AS index_type
  [WITH DEFERRED REBUILD]
  [IDXPROPERTIES (property_name=property_value, ...)]
  [IN TABLE index_table_name]
  [
     [ ROW FORMAT ...] STORED AS ...
     | STORED BY ...
  ]
  [LOCATION hdfs_path]
  [TBLPROPERTIES (...)]
  [COMMENT "index comment"];

更多信息请参见CREATE INDEX。

DROP INDEX

删除索引。

DROP INDEX [IF EXISTS] index_name ON table_name;

更多信息请参见DROP INDEX。

ALTER INDEX

修改索引。

ALTER INDEX index_name ON table_name [PARTITION partition_spec] REBUILD;

更多信息请参见ALTER INDEX。

CREATE TEMPORARY MACRO

创建临时宏。

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

更多信息请参见CREATE TEMPORARY MACRO。

DROP TEMPORARY MACRO

删除临时宏。

DROP TEMPORARY MACRO [IF EXISTS] macro_name;

更多信息请参见DROP TEMPORARY MACRO。

CREATE TEMPORARY FUNCTION

创建临时函数。

CREATE TEMPORARY FUNCTION function_name AS class_name;

更多信息请参见CREATE TEMPORARY FUNCTION。

DROP TEMPORARY FUNCTION

删除临时函数。

DROP TEMPORARY FUNCTION [IF EXISTS] function_name;

更多信息请参见DROP TEMPORARY FUNCTION。

CREATE FUNCTION

创建函数。

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

更多信息请参见CREATE FUNCTION。

DROP FUNCTION

删除函数。

DROP FUNCTION [IF EXISTS] function_name;

更多信息请参见DROP FUNCTION。

RELOAD FUNCTION

重新加载函数。

RELOAD (FUNCTIONS|FUNCTION);

更多信息请参见RELOAD FUNCTION。

SHOW TABLES

显示表。

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

更多信息请参见SHOW TABLES。

SHOW VIEWS

显示视图。

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

更多信息请参见SHOW VIEWS。

SHOW MATERIALIZED VIEWS

显示物化视图。

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

更多信息请参见SHOW MATERIALIZED VIEWS。

SHOW PARTITIONS

显示分区。

SHOW PARTITIONS table_name;

更多信息请参见SHOW PARTITIONS。

SHOW CREATE TABLE

显示创建表。

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

更多信息请参见SHOW CREATE TABLE。

SHOW INDEXES

显示索引。

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

更多信息请参见SHOW INDEXES。

SHOW COLUMNS

显示列。

SHOW COLUMNS (FROM|IN) table_name [(FROM|IN) db_name];

更多信息请参见SHOW COLUMNS。

SHOW FUNCTIONS

显示函数。

SHOW FUNCTIONS [LIKE "<pattern>"];

更多信息请参见SHOW FUNCTIONS。

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;     -- (Note: Hive 0.13.0 and later; SCHEMA added in Hive 0.14.0)

更多信息请参见SHOW LOCKS。

DESCRIBE DATABASE

描述数据库。

DESCRIBE DATABASE [EXTENDED] db_name;
DESCRIBE SCHEMA [EXTENDED] db_name;     -- (Note: Hive 1.1.0 and later)

更多信息请参见DESCRIBE DATABASE。

Hive支持的DDL语句

目录