Creating an Impala Table

Description

This section describes how to use Impala SQL to create internal and external tables. You can create a table by using any of the following methods:

• Customize the table structure, and use the keyword EXTERNAL to differentiate between internal and external tables.
  • If all data is to be processed by Impala, create an internal table. When an internal table is deleted, the metadata and data in the table are deleted together.
  • To process data using multiple tools, create an external table to avoid misoperations. 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.
• Execute the CREATE AS SELECT statement to create a table based on query results.

  This method is flexible. By using this method, you can specify fields (except for the storage format) that you want to copy to the new table when replicating the structure of an existing table.

Sample Code

-- Create the 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 a comma (,).
ROW FORMAT delimited fields terminated by ','
-- Set the table storage format to TEXTFILE.
STORED AS TEXTFILE;  
 
-- Use CREATE Like to create a table.
CREATE TABLE employees_like LIKE employees_info; 
 
-- Run the DESCRIBE command to check the structures of the employees_info and employees_like tables.
DESCRIBE employees_info; 
DESCRIBE employees_like; 

-- Create the internal table employees_info.
CREATE 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 a comma (,).
ROW FORMAT delimited fields terminated by ','
-- Set the table storage format to TEXTFILE.
STORED AS TEXTFILE;  

Advanced Scenarios

• Create a partition table.

  A table may have one or more 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, 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);     

• Enable Impala to use OBS.

  Interconnect with OBS first. For details, see Interconnecting an MRS Cluster with OBS Using an IAM Agency.

  Set the storage type of the new table to OBS.

  create table obs(c1 string, c2 string) stored as parquet location 'obs://obs-lmm/hive/orctest' tblproperties('orc.compress'='SNAPPY');

  

  When Impala uses OBS to store data, a table and all its partitions must be stored in the same bucket.

  For example, if you create a partition table in OBS bucket 1, changing the storage location of a partition to another bucket will not take effect. New data will always be stored in the original table location.

  1. Create a partition table and specify its storage path.

     create table table_name(id int,name string,company string) partitioned by(dt date) row format delimited fields terminated by ',' stored as textfile location "obs://OBS bucket 1/Folder in the bucket";

  2. Modifying a table partition's storage location to a different bucket will not take effect.

     alter table table_name partition(dt date) set location "obs://OBS bucket 2/Folder in the bucket";