Page History

...

CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
  [LOCATIONCOMMENT hdfsdatabase_pathcomment]
  [COMMENTLOCATION databasehdfs_commentpath]
  [WITH DBPROPERTIES (property_name=property_value, ...)];

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

Drop Database

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

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

Create/Drop Table

Create Table

...

ALTER TABLE table_name SET SERDEPROPERTIES ('field.delim' = ',');

Alter Table/Partition File Format

...

Code Block
ALTER TABLE table_name [PARTITION partitionSpec] SET FILEFORMAT file_format

This statement changes the table's (or partition's) file format. For available file_format options, see the section above on CREATE TABLE.

Alter Table Storage Properties

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

These statements change the table's physical storage properties. For available file_format options, see the section above on CREATE TABLE.

Alter file format can also apply on a partition.

NOTE: These commands will only modify Hive's metadata, and will NOT reorganize or reformat existing data. Users should make sure the actual data layout conforms with the metadata definition.

...

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

Alter Table Touch

ALTER TABLE table_name TOUCH;
ALTER TABLE table_name TOUCH [PARTITION partition_specpartitionSpec];

TOUCH reads the metadata, and writes it back. This has the effect of causing the pre/post execute hooks to fire. An example use case is if you have a hook that logs all the tables/partitions that were modified, along with an external script that alters the files on HDFS directly. Since the script modifies files outside of hive, the modification wouldn't be logged by the hook. The external script could call TOUCH to fire the hook and mark the said table or partition as modified.

...

ALTER TABLE table_name ARCHIVE PARTITION partition_spec;
ALTER TABLE table_name UNARCHIVE PARTITION paritionpartition_spec;

Archiving is a feature to moves a partition's files into a Hadoop Archive (HAR). Note that only the file count will be reduced; HAR does not provide any compression. See LanguageManual Archiving for more information

...