SQL Reference

Contents

• DDL Syntax
  • CREATE DATABASE
  • DROP DATABASE
  • ALTER DATABASE
  • CREATE TABLE
  • DROP TABLE
  • TRUNCATE TABLE
  • ALTER TABLE
    • RENAME TABLE
    • ADD COLUMN
    • ALTER TABLE PROPERTIES
    • ALTER TABLE FILE FORMAT
    • ALTER TABLE STORAGE PROPERTIES
    • ALTER TABLE LOCATION
    • ALTER TABLE TOUCH
  • CREATE VIEW
  • DROP VIEW
  • ALTER VIEW
  • CREATE INDEX
  • DROP INDEX

DDL Syntax

CREATE DATABASE

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

The use of SCHEMA and DATABASE are interchangeable - they mean the same thing.

Example

Back to top

DROP DATABASE

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

The use of SCHEMA and DATABASE are interchangeable - they mean the same thing.

Example

Back to top

ALTER DATABASE

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

No other metadata about a database can be changed.

Example

Back to top

CREATE TABLE

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
     [(col_name data_type [COMMENT col_comment], ...)]
     [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, ...])
     [
      [ROW FORMAT row_format] [STORED AS file_format]
     | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)]
     ]
     [LOCATION hdfs_path]
     [TBLPROPERTIES (property_name=property_value, ...)]

The use of SCHEMA and DATABASE are interchangeable - they mean the same thing.

Example

Back to top

DROP TABLE

DROP TABLE [IF EXISTS] table_name  

The DROP TABLE removes metadata and data for this table. The data is actually moved to the .Trash/Current directory if Trash is configured.

Back to top

TRUNCATE TABLE

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

Back to top

RENAME TABLE

ALTER TABLE table_name RENAME TO new_table_name

Back to top

ADD COLUMN

ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)

Back to top

ALTER TABLE PROPERTIES

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

Back to top

ALTER TABLE FILE FORMAT

ALTER TABLE table_name [PARTITION partitionSpec] SET FILEFORMAT file_format

Back to top

ALTER TABLE STORAGE PROPERTIES

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

Back to top

ALTER TABLE LOCATION

ALTER TABLE table_name [PARTITION partitionSpec] SET LOCATION "new location"

Back to top

ALTER TABLE TOUCH

ALTER TABLE table_name TOUCH [PARTITION partitionSpec];

Back to top

CREATE VIEW

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

Back to top

DROP VIEW

DROP VIEW [IF EXISTS] view_name

Back to top

ALTER VIEW

ALTER VIEW view_name SET TBLPROPERTIES table_properties
     table_properties:      : (property_name = property_value, property_name = property_value, ...)

Back to top

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

Back to top

DROP INDEX

DROP INDEX [IF EXISTS] index_name ON table_name

Back to top