Creating a Table

Function

This topic describes how to use Hive Query Language (HQL) to create internal and external tables. You can create a table in three modes:

• Define the table structure, and use the key word EXTERNAL to differentiate between internal and external tables.
  • If all data is to be processed by Hive, create an internal table. When an internal table is deleted, the metadata and data in the table are deleted.
  • If data is to be processed by multiple tools (such as Pig), create an external table. When an external table is deleted, only the metadata is deleted.
• Create a table based on existing tables. Use CREATE LIKE to fully copy the original table structure, including the storage format.
• Create a table based on query results using CREATE AS SELECT.

  In this mode, you can specify which fields are to be copied when copying the original table structure. The storage format is not copied.

  

  Both the table name and field name can contain a maximum of 128 bytes. Both the field comment and value can contain a maximum of 4000 bytes. The key in WITH SERDEPROPERTIES can contain a maximum of 256 bytes.

Example Codes

 -- Create an external table employees_info. 
 CREATE EXTERNAL TABLE IF NOT EXISTS employees_info 
 ( 
 id INT, 
 name STRING, 
 usd_flag STRING, 
 salary DOUBLE, 
 deductions MAP<STRING, DOUBLE>, 
 address STRING, 
 entrytime STRING 
 ) 
 -- Specify the field delimiter. Use delimited fields terminated by to specify the delimiter between columns to a comma (,). 
 -- Use MAP KEYS TERMINATED BY to specify the delimiter between map keys to &. 
 ROW FORMAT delimited fields terminated by ',' MAP KEYS TERMINATED BY '&'  
 -- Set the storage format to TEXTFILE. 
 STORED AS TEXTFILE;  

 -- Use CREATE Like to create a table. 
 CREATE TABLE employees_like LIKE employees_info; 

 -- Use DESCRIBE to query the structures of employees_info, employees_like, and employees_as_select tables. 
 DESCRIBE employees_info; 
 DESCRIBE employees_like; 

Extensions

• Create a partition.

  A table may have one or multiple partitions. Each partition is saved as an independent folder in the table directory. Partitions help minimize the query scope, accelerate data query, and allow users to manage data based on certain criteria.

  A partition is defined using the PARTITIONED BY clause during table creation.

   
   CREATE EXTERNAL TABLE IF NOT EXISTS employees_info_extended 
   ( 
   id INT, 
   name STRING, 
   usd_flag STRING, 
   salary DOUBLE, 
   deductions MAP<STRING, DOUBLE>, 
   address STRING 
   ) 
   -- Use PARTITIONED BY to specify the column name and data type of the partition. 
   PARTITIONED BY (entrytime STRING)  
   STORED AS TEXTFILE;     

• Update the table structure.

  After a table is created, you can use ALTER TABLE to add or delete fields to or from the table, modify table attributes, and add partitions.

   
   -- Add the tel_phone and email fields to the employees_info_extended table. 
   ALTER TABLE employees_info_extended ADD COLUMNS (tel_phone STRING, email STRING);     

• Configure Hive data encryption when creating a table.

  Set the table format to RCFile (recommended) or SequenceFile, and the encryption algorithm to ARC4Codec. SequenceFile is a unique Hadoop file format, and RCFile is a Hive file format with optimized column storage. When a big table is queried, RCFile provides higher performance than SequenceFile.

   set hive.exec.compress.output=true; 
   set hive.exec.compress.intermediate=true; 
   set hive.intermediate.compression.codec=org.apache.hadoop.io.encryption.arc4.ARC4Codec; 
   create table seq_Codec (key string, value string) stored as RCFile;