DocsICP FilingConsole
Documentation
Home Page Dataphin Dataphin(Non-shared) User Guide Data Development SQL reference Hadoop SQL statements and functions DDL statements supported by Hive

DDL statements supported by Hive

Updated at:
Copy as MD
Product
My Favorites

Lists the DDL (Data Definition Language) commands and syntax supported by Hive when Dataphin uses a Hadoop compute engine.

Contents

CREATE TABLE

Creates a 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, ...) [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
  [SKEWED BY (col_name, ...) ON ((col_value, ...), ...) [STORED AS DIRECTORIES]]
  [[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_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
  | array_type
  | map_type
  | struct_type
  | union_type

primitive_type:
  : TINYINT
  | SMALLINT
  | INT
  | BIGINT
  | BOOLEAN
  | FLOAT
  | DOUBLE
  | DOUBLE PRECISION
  | STRING
  | BINARY
  | TIMESTAMP
  | DECIMAL
  | DECIMAL(precision, scale)
  | DATE
  | VARCHAR
  | CHAR

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]
  | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, ...)]

file_format:
  : SEQUENCEFILE
  | TEXTFILE
  | RCFILE
  | ORC
  | PARQUET
  | AVRO
  | JSONFILE
  | 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 ]
    [, 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 ]

For more information, see the referenced document.

DROP TABLE

Deletes a table.

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

For more information, see the referenced document.

TRUNCATE TABLE

Removes all rows from a table.

TRUNCATE [TABLE] table_name [PARTITION partition_spec];

partition_spec:
  : (partition_column = partition_col_value, partition_column = partition_col_value, ...)

For more information, see the referenced document.

ALTER TABLE

Modifies a table's structure.

ALTER TABLE table_name RENAME TO new_table_name;

For more information, see the referenced document.

ALTER PARTITION

Modifies a partition's definition.

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, ...)

For more information, see the referenced document.

ALTER COLUMN

Modifies a column's definition.

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

For more information, see the referenced document.

CREATE VIEW

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

For more information, see the referenced document.

DROP VIEW

Removes a view from the database.

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

For more information, see the referenced document.

ALTER VIEW PROPERTIES

Modifies properties of a view.

ALTER VIEW [db_name.]view_name SET TBLPROPERTIES (property_name = property_value, ...);

For more information, see the referenced document.

CREATE MATERIALIZED VIEW

Creates a materialized view.

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

For more information, see the referenced document.

DROP MATERIALIZED VIEW

Deletes a materialized view.

DROP MATERIALIZED VIEW [db_name.]materialized_view_name;

For additional details, see the referenced document.

ALTER MATERIALIZED VIEW

Modifies a materialized view's properties.

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

For more information, see the referenced document.

CREATE INDEX

Creates an index on a table.

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

For more information, see the referenced document.

DROP INDEX

Removes an index from a table.

DROP INDEX [IF EXISTS] index_name ON table_name;

For more information, see the referenced document.

ALTER INDEX

Modifies an existing index.

ALTER INDEX index_name ON table_name [PARTITION partition_spec] REBUILD;

For more information, see the referenced document.

CREATE TEMPORARY MACRO

Creates a temporary macro.

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

For more information, see the referenced document.

DROP TEMPORARY MACRO

Deletes a temporary macro.

DROP TEMPORARY MACRO [IF EXISTS] macro_name;

For more information, see the referenced document.

CREATE TEMPORARY FUNCTION

Creates a temporary function.

CREATE TEMPORARY FUNCTION function_name AS class_name;

For more information, see the referenced document.

DROP TEMPORARY FUNCTION

Removes a temporary function.

DROP TEMPORARY FUNCTION [IF EXISTS] function_name;

For more information, see the referenced document.

CREATE FUNCTION

Creates a function.

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

For more information, see the referenced document.

DROP FUNCTION

Deletes a function.

DROP FUNCTION [IF EXISTS] function_name;

For additional details, see the referenced document.

RELOAD FUNCTION

Reloads functions.

RELOAD (FUNCTIONS|FUNCTION);

For more information, see the referenced document.

SHOW TABLES

Lists tables in the database.

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

For more information, see the referenced document.

SHOW VIEWS

Lists views in the database.

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

For detailed usage, refer to SHOW VIEWS documentation.

SHOW MATERIALIZED VIEWS

Lists materialized views in the database.

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

For more information, see the referenced document.

SHOW PARTITIONS

Lists the partitions of a table.

SHOW PARTITIONS table_name;

For more information, see the referenced document.

SHOW CREATE TABLE

Displays the CREATE TABLE statement for a table or view.

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

For more information, see the referenced document.

SHOW INDEXES

Lists the indexes of a table.

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

For more information, see the referenced document.

SHOW COLUMNS

Lists the columns of a table.

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

For more information, see the referenced document.

SHOW FUNCTIONS

Lists available functions.

SHOW FUNCTIONS [LIKE "<pattern>"];

For more information, see the referenced document.

SHOW LOCKS

Lists current locks on tables or databases.


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)

For more information, see the referenced document.

DESCRIBE DATABASE

Displays details about a database.

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

For more information, see the referenced document.

Previous：Common commandsNext：Hive supported DQL statements