Hive Data Definition Language

HIVEQL DDL statements are documented here, Including:create Database/schema, TABLE, VIEW, FUNCTION, INDEX DROP D Atabase/schema, table, view, INDEX TRUNCATE table ALTER Database/schema, table, view msck REPAIR table (or ALT  ER TABLE RECOVER partitions) show Databases/schemas, TABLES, tblproperties, partitions, Functions, index[es], COLUMNS, CREATE table DESCRIBE Database/schema, TABLE_NAME, view_name PARTITION statements are usually options of table state


ments, except for show partitions. 1.create/drop/alter/use Database Create (database| SCHEMA) [IF not EXISTS] database_name [COMMENT database_comment] [LOCATION Hdfs_path] [with Dbproperties (property_n
  
Ame=property_value, ...)]; 2.Drop Database Drop (database| SCHEMA) [IF EXISTS] database_name [restrict|

CASCADE]; 3.Alter Database Alter (database|   SCHEMA) database_name SET dbproperties (property_name=property_value, ...); --(Note:schema added in Hive 0.14.0) ALTER (database| SCHEMA) Database_naMe SET OWNER [user|   Role] User_or_role; --(Note:hive 0.13.0 and later;
SCHEMA added in Hive 0.14.0) 4.Use Database use database_name;
Use DEFAULT;

To check which the database is currently being Used:select current_database () (as of Hive 0.13.0). 5.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 [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, ...)
     --(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 | 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:availableIn Hive 0.12.0 and later) | CHAR--(note:available in Hive 0.13.0 and later) Array_type:array < data_type > Map_type:map &L T Primitive_type, Data_type > Struct_type:struct < Col_name:data_type [COMMENT col_comment], ...> union_t Ype:uniontype < Data_type, data_type, ... >-(note:available in Hive 0.7.0 and later) Row_format:deli mited [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) |
  InputFormat input_format_classname outputformat output_format_classname 6.Partitioned Tables CREATE TABLE table_name ( ID int, dtdontquery string, name string) partitioned by (date string) CREATE Tabl E Page_view (viewtime INT, UserID BIGINT, Page_url string, referrer_url string, IP string COMMENT ' IP address of The User ') COMMENT ' this are the Page view table ' partitioned by (DT string, country string) ROW FORMAT delimited Fiel


DS terminated by ' \001 ' STORED as Sequencefile; 
     7.External Tables CREATE External TABLE page_view (viewtime INT, UserID BIGINT, Page_url string, Referrer_url string, IP string COMMENT ' IP address to the User ', country string COMMENT ' country of Origination ') COMMENT ' this is The Staging Page view table ' ROW FORMAT delimited FIELDS terminated by ' \054 ' STORED as Textfile ';hdfs_location> '; 8.Create table as Select (CTAS) Create Table new_key_value_store ROW FORMAT Serde "Org.apache.hadoop.hive.serde2.col Umnar. Columnarserde "STORED as rcfile as SELECT (key% 1024) New_key, concat (key, value) Key_value_pair from Key_value_sto


Re SORT by New_key, Key_value_pair;

9.Create table like Create table Empty_key_value_store like Key_value_store;
     10.Bucketed Sorted Tables CREATE TABLE page_view (viewtime INT, UserID BIGINT, Page_url string, Referrer_url string,  IP string COMMENT ' IP address of the User ' COMMENT ' This is the Page view table ' partitioned by (DT string, country
   STRING) CLUSTERED by (userid) SORTED by (viewtime) to buckets ROW FORMAT delimited FIELDS terminated by ' \001 '
 
  COLLECTION ITEMS terminated by ' \002 ' MAP KEYS terminated by ' \003 ' STORED as Sequencefile;

More info, refer to Page:https://cwiki.apache.org/confluence/display/hive/languagemanual+ddl