Hive Tables

This tutorials provides most of the information related to tables in Hive. There are 2 type of tables in Hive.
→ Internal Table: Internal Tables stores data inside HDFS hive/warehouse with tablename as directory. Internal tables are also called managed tables.
→ External Table: External Tables stores data in the user defined HDFS directory.

Differences between Internal/Managed & External Table based on various parameters

	Internal/Managed Table	External Table
Table Creation	User don't have to specify internal keyword in create table statement. Example: 'CREATE TABLE tablename'.	User has to specifically specify external keyword in create table statement. example: 'CREATE EXTERNAL TABLE tablename'.
Table Data Location	Data will stored in system defined HDFS hive/warehouse directory.	User has to specify the HDFS data directory in create table statement.
DROP Table	Drop table command will remove all the data from HDFS as well and data will lost.	Drop table command will not remove any data(no data loss) from HDFS and it will just remove table defintion.

➠ Hive Internal Table: Below table example will demonstrate simple Internal table in Hive.

CREATE TABLE hiveFirstTable
(  
   order_id INT,
   order_date STRING,
   cust_id INT,
   order_status STRING
)
ROW FORMAT delimited 
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n'
STORED AS textfile;

➠ Hive External Table: Below table example will demonstrate simple External table in Hive.

CREATE EXTERNAL TABLE hiveFirstExternalTable
(  
   order_id INT,
   order_date STRING,
   cust_id INT,
   order_status STRING
)
ROW FORMAT delimited 
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n'
STORED AS textfile
LOCATION '/user/username/somedirectory';

Hive Table Attributes: Some of the attributes that can be used in create table statement in Hive.

Attribute	Description
ROW FORMAT	This is used to specify the format of data file that will be loaded into HIVE. Possible types: DELIMITED, SERDE
TBLPROPERTIES	This attribute can be used to specify multiple properties for tables as key-value pair. Example: Ignoring 'n' number of lines of header or footer from file to be loaded.
STORED AS	This is used to specify the format of data file that will be loaded/stored into HIVE. Default is plain text file, other types includes: ORC, PARQUET, RCFILE, AVRO, JSONFILE, SEQUENCEFILE.
LINES TERMINATED BY	This is used to specify the character which can be used to differentiate multiple rows. Default is newline character.
PARTITION BY	This is used to partition the table data into multiple files based on partition column defined.
CLUSTERED BY	This is used to classify data into specified number of bucket(again multiple files) using hash function.
LOCATION	This property is specifically for external table to specify the directory location where data file loaded into the external table will be stored.

Note: PARTITION BY & CLUSTERED BY can be used simultaneously.

➠ ROW FORMAT: File to be loaded into Hive should be either delimited or SERDE compliant.

Delimiter should be specified for the tables defined 'ROW FORMAT delimited'.
```
ROW FORMAT delimited FIELDS TERMINATED BY '\t' 
```

SERDE is a combination of Serializer and Deserializer

ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'

➠ TBLPROPERTIES: Various key-value attribute can be used in TBLPROPERTIES to define the way table should work. Multiple table properties can be specified inside TBLPROPERTIES.

Table Comments: Used to add comments to the created table.

TBLPROPERTIES("comment"="Adding table information as comment")

Ignoring Header: Used to ignore 'n' number of rows from top of file before loading data into Hive. In the below example, first row will be ignored.
```
TBLPROPERTIES ("skip.header.line.count"="1");
```
Ignoring Footer: Used to ignore 'n' number of rows from bottom of file before loading data into Hive. In the below example, last row will be ignored.
```
TBLPROPERTIES("skip.footer.line.count"="1")
```
ACID Properties: This property is used to specify whether table should support ACID(Transactional properties) properties or not. Default value is false.
```
TBLPROPERTIES("transactional"="true")
TBLPROPERTIES("transactional"="false")
```
ORC Properties: Used to Specify properties related to files stored as ORC such compression type(ZLIB or SNAPPY), row create indexes.
```
TBLPROPERTIES("orc.compress"="ZLIB")
TBLPROPERTIES("orc.compress"="SNAPPY")
TBLPROPERTIES("orc.create.index"="true")
```

More Hive Table Examples

Skipping Header From File
Skipping Footer From File
Skipping Both Header/Footer
Transactional Table
Partitioned & Clustered Table
Create Table Using Other Table

➠ Skipping Header From File: Below table will ignore first line from a loaded file in Hive. Number one(1) in the below example can be replaced with any number to ignore that many header rows.

CREATE TABLE hiveFirstTableSkipHeader
(  
   order_id INT,
   order_date STRING,
   cust_id INT,
   order_status STRING
)
ROW FORMAT delimited 
FIELDS TERMINATED BY '\t' 
LINES TERMINATED BY '\n'
STORED AS textfile
TBLPROPERTIES("skip.header.line.count"="1");

➠ Skipping Footer From File: Below table will ignore last line from a loaded file in Hive. Number one(1) in the below example can be replaced with any number to ignore that many footer rows.

CREATE TABLE hiveFirstTableSkipFooter
(  
   order_id INT,
   order_date STRING,
   cust_id INT,
   order_status STRING
)
ROW FORMAT delimited 
FIELDS TERMINATED BY '\t' 
LINES TERMINATED BY '\n'
STORED AS textfile
TBLPROPERTIES("skip.footer.line.count"="1");

➠ Skipping Both Header/Footer From File: Below table will ignore last line from a loaded file in Hive. Number one(1) in the below example can be replaced with any number to ignore that many header & footer rows.

CREATE TABLE hiveFirstTableSkipHeaderFooter
(  
   order_id INT,
   order_date STRING,
   cust_id INT,
   order_status STRING
)
ROW FORMAT delimited 
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n'
STORED AS textfile
tblproperties("skip.header.line.count"="1", "skip.footer.line.count"="1");

➠ Transactional Table: Transactional Table property should be enabled in order to delete, insert & update data in Hive table. Also table storage format should be ORC (or other hive compliant format).

CREATE TABLE hiveFirstTransTable
(  
   order_id INT,
   order_date STRING,
   cust_id INT,
   order_status STRING
)
ROW FORMAT delimited 
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n'
STORED AS ORC
TBLPROPERTIES("transactional"="true");

Note: Below properties need to be set otherwise create table statement will fail

SET hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;
SET hive.support.concurrency=true;

➠ Partitioned & Clustered Table: Below create table example is the example of partitioned and clustered table.

Create table hiveFirstPartitionedClusteredTable
(  
   order_id INT,
   order_date STRING,
   cust_id INT
)
PARTITIONED BY (order_status STRING)
CLUSTERED by (order_id) INTO 10 buckets
Row format delimited 
fields terminated by  ','
Stored as textfile;

➠ SERDE Table: SERDE is popularly used to load from sources storing data in JSON format. Check out this Hive SERDE page to know more about SERDE.

CREATE TABLE hiveFirstTransTable
(  
   order_id INT,
   order_date STRING,
   cust_id INT,
   order_status STRING
)
ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe' 
STORED AS TEXTFILE;

➠ Create Table Using Other Table

Copy table structure without data

CREATE TABLE hiveFirstTable_copy  like hiveFirstTable;

Copy table structure with data

CREATE TABLE hiveFirstTable_copy_with_date  as select * from  hiveFirstTable;