hive-table-creation

Hive Table Creation
A Table can be External or Internal to the Hive system. Internal or Managed Tables are stored in Hive embedded or local Database. Table will be deleted When metadata drops from Metastore External tables data resides outside of Hive Table data stores on HDFS path. Even Table's metadata is lost, Table data will not be touched
Syntax create external (default is internal) table {table name}( column_name type, column_name2 type,...) row format delimited or row formatter type fields terminated by {field separator} lines terminated by {line separator} location stored as {file format}
Create Table By Select CREATE TABLE {NEW TABLE NAME} AS SELECT COL1, COL2, COL3 FROM {SOURCE TABLE} WHERE {CONDITIONS}
Hive Data types Primitives - STRING, INT, FLOAT, DOUBLE, BOOLEAN, TIMESTAMP Complex - ARRAY, MAP, STRUCT
Table creation examples create table EMP_STG(id int,first_name string,last_name string,email string,gender string, company string) row format delimited fields terminated by ',' lines terminated by '\n' stored as textfile TBLPROPERTIES("skip.header.line.count"="1"); create external table COMPANY_STG(company string,city string,state string,country string, country_code string,location string, timezone string) row format delimited fields terminated by ',' lines terminated by '\n' location '/hivebucketing/01/cmpstg' TBLPROPERTIES("skip.header.line.count"="1");