A Impala external table allows you to access external HDFS file as a regular managed table. This operation saves resources and expense of importing data file into Impala database. You can perform join using these external tables same as managed tables. You can write complex queries using these external tables. In this article, we will check on Impala create external table with some examples.
Syntax for creating impala external table is same as creating managed tables. There is one exception to this, LOCATION option is mandatory for creating external tables. LOCATION option points to the location of the HDFS flat file that you want to access as a regular managed tables table.
Impala Create External Tables Syntax
Below is the simple syntax to create Impala external tables:
CREATE EXTERNAL TABLE [IF NOT EXISTS] [imp_db_name.] table_name [(col1 data_type1 [COMMENT col_comment], ...)] [COMMENT table_comments] [ROW FORMAT row_format] [FIELDS TERMINATED BY char] [STORED AS file_format] [LOCATION hdfs_path]; -- This option is mandatory.
You can read more about Impala and Hive file format in other post:
Most of the above file formats are supported in Cloudera impala as well.
Create Impala External Table using LIKE
Impala supports creating external table by copying structure of existing managed tables or views. You can use LIKE command to create identical table structure. You can use below syntax:
CREATE EXTERNAL TABLE [IF NOT EXISTS] [db_name.]table_name LIKE existing_table_or_view [LOCATION hdfs_path];
Usually, external table has only definition that is stored in metastore. Dropping external table does not remove HDFS files that are referred in LOCATION path.
Impala Create External Table Examples
Create external table on HDFS flat file
Below is the examples of creating external tables in Cloudera Impala
https://gist.github.com/a991c92295657c4433afb6ce72da1e9e
Create external table by using LIKE to copy structure from other tables
Below is the example of using LIKE to create external table:
https://gist.github.com/c6bcfcd7bc5c67e9464fe5ab6e25c9f7
Switch Impala External to Internal and vice-versa
You can switch a table from internal to external, or from external to internal, by using the ALTER TABLE statement in Impala. Below is syntax for switching tables:
-- Switch a table from internal to external.
ALTER TABLE table_name SET TBLPROPERTIES('EXTERNAL'='TRUE');
-- Switch a table from external to internal.
ALTER TABLE table_name SET TBLPROPERTIES('EXTERNAL'='FALSE');
Identify Internal and External Tables
If you want to identify whether given table is internal or external, issue “DESCRIBE FORMATTED table_name” statement. The Table Type field will be MANAGED_TABLE for internal tables and EXTERNAL_TABLE for external tables. The LOCATION field displays the path of the table directory that it is pointing to.
