Creating a Table

Function

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

• Customize the table structure, and use the key word EXTERNAL to differentiate between internal and external tables.
  • If 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 together.
  • If data is to be processed by multiple tools (such as Pig), create an external table. When an external table is deleted, only 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 fields (except for the storage format) that you want to copy to the new table when copying the structure of the existing table.

• To perform the following operations on a cluster enabled with the security service, you must have the CREATE permission for databases. To create a table using the CREATE AS SELECT statement, you must have the SELECT permission for tables. For details about the permission requirements, see Overview.
• 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 4,000 bytes. The key in WITH SERDEPROPERTIES can contain a maximum of 256 bytes.

Sample 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.
-- "delimited fields terminated by" indicates that the column delimiter is ',' and "MAP KEYS TERMINATED BY" indicates that the delimiter of key values in the MAP is '&'.
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);