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;  

-- Create an external table employees_contact.
CREATE EXTERNAL TABLE IF NOT EXISTS employees_contact
( 
    id INT,
    tel_phone STRING,
    email STRING
) 
ROW FORMAT delimited fields terminated by ',' 
STORED AS TEXTFILE;

-- Create an external table employees_info_extended.
CREATE EXTERNAL TABLE IF NOT EXISTS employees_info_extended(id INT, name STRING, usd_flag STRING, salary DOUBLE, deductions MAP<STRING, DOUBLE>, address STRING)
-- 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.
-- Use PARTITIONED BY to specify the column name and data type of the partition.
PARTITIONED BY(entrytime STRING)
ROW FORMAT delimited fields terminated by ',' MAP KEYS TERMINATED BY '&'
STORED AS TEXTFILE; 
-- 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);