Google Git
Sign in
apache/impala/refs/heads/asf-site/./docs/build/plain-html/topics/impala_create_table.html
blob: 551efbbc9982041120006fa70dfa3ac3aa52ca4c [file]
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE html
PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<meta name="copyright" content="(C) Copyright 2025" />
<meta name="DC.rights.owner" content="(C) Copyright 2025" />
<meta name="DC.Type" content="concept" />
<meta name="DC.Title" content="CREATE TABLE Statement" />
<meta name="DC.Relation" scheme="URI" content="../topics/impala_langref_sql.html" />
<meta name="prodname" content="Impala" />
<meta name="prodname" content="Impala" />
<meta name="version" content="Impala 3.4.x" />
<meta name="version" content="Impala 3.4.x" />
<meta name="DC.Format" content="XHTML" />
<meta name="DC.Identifier" content="create_table" />
<link rel="stylesheet" type="text/css" href="../commonltr.css" />
<title>CREATE TABLE Statement</title>
</head>
<body class="impala sql_statement" id="create_table">
<h1 class="title topictitle1 impala_title sql_statement_title" id="ariaid-title1">CREATE TABLE Statement</h1>
<div class="body conbody">
<p class="p">
Creates a new table and specifies its characteristics. While creating a table, you
optionally specify aspects such as:
</p>
<ul class="ul">
<li class="li">
Whether the table is internal or external.
</li>
<li class="li">
The columns and associated data types.
</li>
<li class="li">
The columns used for physically partitioning the data.
</li>
<li class="li">
The file format for data files.
</li>
<li class="li">
The HDFS directory where the data files are located.
</li>
</ul>
<p class="p">
<strong class="ph b">Syntax:</strong>
</p>
<p class="p">
The general syntax for creating a table and specifying its columns is as follows:
</p>
<p class="p">
<strong class="ph b">Explicit column definitions:</strong>
</p>
<pre class="pre codeblock"><code>CREATE [EXTERNAL] TABLE [IF NOT EXISTS] [<var class="keyword varname">db_name</var>.]<var class="keyword varname">table_name</var>
(<var class="keyword varname">col_name</var> <var class="keyword varname">data_type</var>
[<var class="keyword varname">constraint_specification</var>]
[COMMENT '<var class="keyword varname">col_comment</var>']
[, ...]
)
[PARTITIONED BY (<var class="keyword varname">col_name</var> <var class="keyword varname">data_type</var> [COMMENT '<var class="keyword varname">col_comment</var>'], ...)]
<span class="ph">[SORT BY ([<var class="keyword varname">column</var> [, <var class="keyword varname">column</var> ...]])]</span>
[COMMENT '<var class="keyword varname">table_comment</var>']
[ROW FORMAT <var class="keyword varname">row_format</var>]
[WITH SERDEPROPERTIES ('<var class="keyword varname">key1</var>'='<var class="keyword varname">value1</var>', '<var class="keyword varname">key2</var>'='<var class="keyword varname">value2</var>', ...)]
[STORED AS <var class="keyword varname">file_format</var>]
[LOCATION '<var class="keyword varname">hdfs_path</var>']
<span class="ph">[CACHED IN '<var class="keyword varname">pool_name</var>'</span> <span class="ph">[WITH REPLICATION = <var class="keyword varname">integer</var>]</span> | UNCACHED]
[TBLPROPERTIES ('<var class="keyword varname">key1</var>'='<var class="keyword varname">value1</var>', '<var class="keyword varname">key2</var>'='<var class="keyword varname">value2</var>', ...)]
</code></pre>
<p class="p">
<strong class="ph b">CREATE TABLE AS SELECT:</strong>
</p>
<pre class="pre codeblock"><code>CREATE [EXTERNAL] TABLE [IF NOT EXISTS] <var class="keyword varname">db_name</var>.]<var class="keyword varname">table_name</var>
<span class="ph">[PARTITIONED BY (<var class="keyword varname">col_name</var>[, ...])]</span>
<span class="ph">[SORT BY ([<var class="keyword varname">column</var> [, <var class="keyword varname">column</var> ...]])]</span>
[COMMENT '<var class="keyword varname">table_comment</var>']
[ROW FORMAT <var class="keyword varname">row_format</var>]
[WITH SERDEPROPERTIES ('<var class="keyword varname">key1</var>'='<var class="keyword varname">value1</var>', '<var class="keyword varname">key2</var>'='<var class="keyword varname">value2</var>', ...)]
<span class="ph">[STORED AS <var class="keyword varname">ctas_file_format</var>]</span>
[LOCATION '<var class="keyword varname">hdfs_path</var>']
<span class="ph"> [CACHED IN '<var class="keyword varname">pool_name</var>'</span> <span class="ph">[WITH REPLICATION = <var class="keyword varname">integer</var>]</span> | UNCACHED]
[TBLPROPERTIES ('<var class="keyword varname">key1</var>'='<var class="keyword varname">value1</var>', '<var class="keyword varname">key2</var>'='<var class="keyword varname">value2</var>', ...)]
AS
<var class="keyword varname">select_statement</var></code></pre>
<pre class="pre codeblock"><code>primitive_type:
TINYINT
| SMALLINT
| INT
| BIGINT
| BOOLEAN
| FLOAT
| DOUBLE
<span class="ph">| DECIMAL</span>
| STRING
<span class="ph">| CHAR</span>
<span class="ph">| VARCHAR</span>
| TIMESTAMP
<span class="ph">complex_type:
struct_type
| array_type
| map_type
struct_type: STRUCT &lt; <var class="keyword varname">name</var> : <var class="keyword varname">primitive_or_complex_type</var> [COMMENT '<var class="keyword varname">comment_string</var>'], ... &gt;
array_type: ARRAY &lt; <var class="keyword varname">primitive_or_complex_type</var> &gt;
map_type: MAP &lt; <var class="keyword varname">primitive_type</var>, <var class="keyword varname">primitive_or_complex_type</var> &gt;
</span>
constraint_specification:
PRIMARY KEY (<var class="keyword varname">col_name</var>, ...) [DISABLE] [NOVALIDATE] [RELY], [<var class="keyword varname">foreign_key_specification</var>, ...]
foreign_key_specification:
FOREIGN KEY (<var class="keyword varname">col_name</var>, ...) REFERENCES table_name(<var class="keyword varname">col_name</var>, ...) [DISABLE] [NOVALIDATE] [RELY]
row_format:
DELIMITED [FIELDS TERMINATED BY '<var class="keyword varname">char</var>' [ESCAPED BY '<var class="keyword varname">char</var>']]
[LINES TERMINATED BY '<var class="keyword varname">char</var>']
file_format:
PARQUET
| TEXTFILE
| AVRO
| SEQUENCEFILE
| RCFILE
<span class="ph">ctas_file_format:
PARQUET
| TEXTFILE</span>
</code></pre>
<p class="p">
<strong class="ph b">Column definitions inferred from data file:</strong>
</p>
<pre class="pre codeblock"><code>CREATE [EXTERNAL] TABLE [IF NOT EXISTS] [<var class="keyword varname">db_name</var>.]<var class="keyword varname">table_name</var>
LIKE PARQUET '<var class="keyword varname">hdfs_path_of_parquet_file</var>'
[PARTITIONED BY (<var class="keyword varname">col_name</var> <var class="keyword varname">data_type</var> [COMMENT '<var class="keyword varname">col_comment</var>'], ...)]
<span class="ph">[SORT BY ([<var class="keyword varname">column</var> [, <var class="keyword varname">column</var> ...]])]</span>
[COMMENT '<var class="keyword varname">table_comment</var>']
[ROW FORMAT <var class="keyword varname">row_format</var>]
[WITH SERDEPROPERTIES ('<var class="keyword varname">key1</var>'='<var class="keyword varname">value1</var>', '<var class="keyword varname">key2</var>'='<var class="keyword varname">value2</var>', ...)]
[STORED AS <var class="keyword varname">file_format</var>]
[LOCATION '<var class="keyword varname">hdfs_path</var>']
<span class="ph"> [CACHED IN '<var class="keyword varname">pool_name</var>'</span> <span class="ph">[WITH REPLICATION = <var class="keyword varname">integer</var>]</span> | UNCACHED]
[TBLPROPERTIES ('<var class="keyword varname">key1</var>'='<var class="keyword varname">value1</var>', '<var class="keyword varname">key2</var>'='<var class="keyword varname">value2</var>', ...)]
data_type:
<var class="keyword varname">primitive_type</var>
| array_type
| map_type
| struct_type
</code></pre>
<p class="p">
<strong class="ph b">Internal Kudu tables:</strong>
</p>
<pre class="pre codeblock"><code>CREATE TABLE [IF NOT EXISTS] [<var class="keyword varname">db_name</var>.]<var class="keyword varname">table_name</var>
(<var class="keyword varname">col_name</var> <var class="keyword varname">data_type</var>
<span class="ph">[<var class="keyword varname">kudu_column_attribute</var> ...]</span>
[COMMENT '<var class="keyword varname">col_comment</var>']
[, ...]
[PRIMARY KEY (<var class="keyword varname">col_name</var>[, ...])]
)
<span class="ph">[PARTITION BY <var class="keyword varname">kudu_partition_clause</var>]</span>
[COMMENT '<var class="keyword varname">table_comment</var>']
STORED AS KUDU
[TBLPROPERTIES ('<var class="keyword varname">key1</var>'='<var class="keyword varname">value1</var>', '<var class="keyword varname">key2</var>'='<var class="keyword varname">value2</var>', ...)]
</code></pre>
<div class="p">
<strong class="ph b">Kudu column attributes:</strong>
<pre class="pre codeblock"><code>
PRIMARY KEY
| [NOT] NULL
| ENCODING <var class="keyword varname">codec</var>
| COMPRESSION <var class="keyword varname">algorithm</var>
| DEFAULT <var class="keyword varname">constant</var>
| BLOCK_SIZE <var class="keyword varname">number</var>
</code></pre>
</div>
<div class="p">
<strong class="ph b">kudu_partition_clause:</strong>
<pre class="pre codeblock"><code>
kudu_partition_clause ::= [ <var class="keyword varname">hash_clause</var> [, ...]] [, <var class="keyword varname">range_clause</var> ]
hash_clause ::=
HASH [ (<var class="keyword varname">pk_col</var> [, ...]) ]
PARTITIONS <var class="keyword varname">n</var>
range_clause ::=
RANGE [ (<var class="keyword varname">pk_col</var> [, ...]) ]
(
{
PARTITION <var class="keyword varname">constant_expression</var> <var class="keyword varname">range_comparison_operator</var> VALUES <var class="keyword varname">range_comparison_operator</var> <var class="keyword varname">constant_expression</var>
| PARTITION VALUE = <var class="keyword varname">constant_expression_or_tuple</var>
}
[, ...]
)
range_comparison_operator ::= { &lt; | &lt;= }
</code></pre>
</div>
<p class="p">
<strong class="ph b">External Kudu tables:</strong>
</p>
<p class="p">In Impala 3.4 and earlier, you can create an external Kudu table based on a pre-existing Kudu
schema using the table property <code class="ph codeph">'kudu.table_name'='internal_kudu_name'</code>. </p>
<pre class="pre codeblock"><code>CREATE EXTERNAL TABLE [IF NOT EXISTS] [<var class="keyword varname">db_name</var>.]<var class="keyword varname">table_name</var>
[COMMENT '<var class="keyword varname">col_comment</var>']
STORED AS KUDU
[TBLPROPERTIES ('<var class="keyword varname">kudu.table.name</var>'='<var class="keyword varname">internal_kudu_name</var>', '<var class="keyword varname">key1</var>'='<var class="keyword varname">value1</var>',...)]
</code></pre>
<p class="p">Alternatively, in Impala 3.4 and higher, you can also create an external Kudu table as follows:</p>
<pre class="pre codeblock"><code>CREATE EXTERNAL TABLE [IF NOT EXISTS] [<var class="keyword varname">db_name</var>.]<var class="keyword varname">table_name</var>
(<var class="keyword varname">col_name</var> <var class="keyword varname">data_type</var>
[<var class="keyword varname">kudu_column_attribute</var> ...]
[COMMENT '<var class="keyword varname">col_comment</var>']
[, ...]
[PRIMARY KEY (<var class="keyword varname">col_name</var>[, ...])]
)
[PARTITION BY <var class="keyword varname">kudu_partition_clause</var>]
[COMMENT '<var class="keyword varname">table_comment</var>']
STORED AS KUDU
[TBLPROPERTIES ('external.table.purge'='true', '<var class="keyword varname">key1</var>'='<var class="keyword varname">value1</var>',...)]
</code></pre>
<ul class="ul">
<li class="li">Use a Hive metastore (HMS) 3 or later.</li>
<li class="li">Provide column specifications to define the schema when you create the table, similar to
creating an internal table. </li>
<li class="li">Omit the <code class="ph codeph">kudu.table_name</code> table property as there is no pre-existing
schema. </li>
<li class="li">Include the required <code class="ph codeph">external.table.purge</code> property.</li>
</ul>
<p class="p"> Only the schema metadata is stored in HMS when you create an external table; however, using
this create table syntax, drop table on the Kudu external table deletes the data stored
outside HMS in Kudu as well as the metadata (schema) inside HMS. Likewise, renaming the table
changes the name of the table in HMS and in Kudu. Kudu synchronizes changes to the actual data
and metadata; consequently, operations such as dropping a table or altering a table name
simulate internal table operations. </p>
<p class="p">
<strong class="ph b">CREATE TABLE AS SELECT for Kudu tables:</strong>
</p>
<pre class="pre codeblock"><code>CREATE TABLE [IF NOT EXISTS] <var class="keyword varname">db_name</var>.]<var class="keyword varname">table_name</var>
[PRIMARY KEY (<var class="keyword varname">col_name</var>[, ...])]
[PARTITION BY <var class="keyword varname">kudu_partition_clause</var>]
[COMMENT '<var class="keyword varname">table_comment</var>']
STORED AS KUDU
[TBLPROPERTIES ('<var class="keyword varname">key1</var>'='<var class="keyword varname">value1</var>', '<var class="keyword varname">key2</var>'='<var class="keyword varname">value2</var>', ...)]
AS
<var class="keyword varname">select_statement</var></code></pre>
<p class="p">
<strong class="ph b">Statement type:</strong> DDL
</p>
<p class="p">
<strong class="ph b">Column definitions:</strong>
</p>
<p class="p">
Depending on the form of the <code class="ph codeph">CREATE TABLE</code> statement, the column
definitions are required or not allowed.
</p>
<p class="p">
With the <code class="ph codeph">CREATE TABLE AS SELECT</code> and <code class="ph codeph">CREATE TABLE LIKE</code>
syntax, you do not specify the columns at all; the column names and types are derived from
the source table, query, or data file.
</p>
<p class="p"> With the basic <code class="ph codeph">CREATE TABLE</code> syntax, you must list one or more columns, its
name, type, optionally constraints, and optionally a comment, in addition to any columns used
as partitioning keys. There is one exception where the column list is not required: when
creating an Avro table with the <code class="ph codeph">STORED AS AVRO</code> clause, you can omit the list
of columns and specify the same metadata as part of the <code class="ph codeph">TBLPROPERTIES</code> clause. </p>
<p class="p">
<strong class="ph b">Constraints:</strong>
</p>
<div class="p">Constraints are advisory and intended for estimating cardinality during query planning in a
future release; there is no attempt to enforce constraints. Add primary and foreign key
information after column definitions. Do not include a constraint name; the constraint name is
generated internally as a UUID. The following constraint states are supported: <ul class="ul" id="create_table__ul_gbz_3kl_4kb">
<li class="li">DISABLE</li>
<li class="li">NOVALIDATE</li>
<li class="li">RELY</li>
</ul>
The ENABLE, VALIDATE, and NORELY options are not supported. The foreign key must be
defined as the primary key in the referenced table. </div>
<div class="p"> Constraint examples: <pre class="pre codeblock"><code>CREATE TABLE pk(col1 INT, col2 STRING, PRIMARY KEY(col1, col2));</code></pre>
<pre class="pre codeblock"><code>CREATE TABLE fk(id INT, col1 INT, col2 STRING, PRIMARY KEY(id),
FOREIGN KEY(col1, col2) REFERENCES pk(col1, col2));</code></pre>
<pre class="pre codeblock"><code>CREATE TABLE pk(id INT, PRIMARY KEY(id) DISABLE, NOVALIDATE, RELY);</code></pre>
<pre class="pre codeblock"><code>CREATE TABLE fk(id INT, col1 INT, col2 STRING, PRIMARY KEY(id),
FOREIGN KEY(col1, col2) REFERENCES pk(col1, col2));</code></pre>
</div>
<p class="p">
<strong class="ph b">Complex type considerations:</strong>
</p>
<p class="p">
The Impala complex types (<code class="ph codeph">STRUCT</code>, <code class="ph codeph">ARRAY</code>, or
<code class="ph codeph">MAP</code>) are available in <span class="keyword">Impala 2.3</span> and higher.
Because you can nest these types (for example, to make an array of maps or a struct with
an array field), these types are also sometimes referred to as nested types. See
<a class="xref" href="impala_complex_types.html#complex_types">Complex Types (Impala 2.3 or higher only)</a> for usage details.
</p>
<p class="p">
Impala can create tables containing complex type columns, with any supported file format.
Because currently Impala can only query complex type columns in Parquet tables, creating
tables with complex type columns and other file formats such as text is of limited use.
For example, you might create a text table including some columns with complex types with
Impala, and use Hive as part of your to ingest the nested type data and copy it to an
identical Parquet table. Or you might create a partitioned table containing complex type
columns using one file format, and use <code class="ph codeph">ALTER TABLE</code> to change the file
format of individual partitions to Parquet; Impala can then query only the Parquet-format
partitions in that table.
</p>
<p class="p">
Partitioned tables can contain complex type columns. All the partition key columns must
be scalar types.
</p>
<p class="p">
<strong class="ph b">Internal and external tables (EXTERNAL and LOCATION clauses):</strong>
</p>
<p class="p"> By default, Impala creates an internal table, where Impala manages the underlying data files
for the table, and physically deletes the data files when you drop the table. If you specify
the <code class="ph codeph">EXTERNAL</code> clause, Impala treats the table as an <span class="q">"external"</span> table,
where the data files are typically produced outside Impala and queried from their original
locations in HDFS, and Impala leaves the data files in place when you drop the table. For
details about internal and external tables, see <a class="xref" href="impala_tables.html#tables">Overview of Impala Tables</a>. </p>
<p class="p">
Typically, for an external table you include a <code class="ph codeph">LOCATION</code> clause to specify
the path to the HDFS directory where Impala reads and writes files for the table. For
example, if your data pipeline produces Parquet files in the HDFS directory
<span class="ph filepath">/user/etl/destination</span>, you might create an external table as follows:
</p>
<pre class="pre codeblock"><code>CREATE EXTERNAL TABLE external_parquet (c1 INT, c2 STRING, c3 TIMESTAMP)
STORED AS PARQUET LOCATION '/user/etl/destination';
</code></pre>
<p class="p">
Although the <code class="ph codeph">EXTERNAL</code> and <code class="ph codeph">LOCATION</code> clauses are often
specified together, <code class="ph codeph">LOCATION</code> is optional for external tables, and you can
also specify <code class="ph codeph">LOCATION</code> for internal tables. The difference is all about
whether Impala <span class="q">"takes control"</span> of the underlying data files and moves them when you
rename the table, or deletes them when you drop the table. For more about internal and
external tables and how they interact with the <code class="ph codeph">LOCATION</code> attribute, see
<a class="xref" href="impala_tables.html#tables">Overview of Impala Tables</a>.
</p>
<p class="p">
<strong class="ph b">Partitioned tables (PARTITIONED BY clause):</strong>
</p>
<p class="p">
The <code class="ph codeph">PARTITIONED BY</code> clause divides the data files based on the values from
one or more specified columns. Impala queries can use the partition metadata to minimize
the amount of data that is read from disk or transmitted across the network, particularly
during join queries. For details about partitioning, see
<a class="xref" href="impala_partitioning.html#partitioning">Partitioning for Impala Tables</a>.
</p>
<div class="note note"><span class="notetitle">Note:</span>
<p class="p">
All Kudu tables require partitioning, which involves different syntax than non-Kudu
tables. See the <code class="ph codeph">PARTITION BY</code> clause, rather than <code class="ph codeph">PARTITIONED
BY</code>, for Kudu tables.
</p>
<p class="p">
In <span class="keyword">Impala 2.10</span> and higher, the <code class="ph codeph">PARTITION BY</code>
clause is optional for Kudu tables. If the clause is omitted, Impala automatically
constructs a single partition that is not connected to any column. Because such a table
cannot take advantage of Kudu features for parallelized queries and query optimizations,
omitting the <code class="ph codeph">PARTITION BY</code> clause is only appropriate for small lookup
tables.
</p>
</div>
<p class="p">
Prior to <span class="keyword">Impala 2.5</span>, you could use a partitioned table as the
source and copy data from it, but could not specify any partitioning clauses for the new
table. In <span class="keyword">Impala 2.5</span> and higher, you can now use the
<code class="ph codeph">PARTITIONED BY</code> clause with a <code class="ph codeph">CREATE TABLE AS SELECT</code>
statement. See the examples under the following discussion of the <code class="ph codeph">CREATE TABLE AS
SELECT</code> syntax variation.
</p>
<p class="p">
<strong class="ph b">Sorted tables (SORT BY clause):</strong>
</p>
<p class="p">
The optional <code class="ph codeph">SORT BY</code> clause lets you specify zero or more columns that
are sorted in ascending order in the data files created by each Impala <code class="ph codeph">INSERT</code>
or <code class="ph codeph">CREATE TABLE AS SELECT</code> operation. There are two orderings to chose
from: <code class="ph codeph">LEXICAL</code> and <code class="ph codeph">ZORDER</code>. The default ordering is
<code class="ph codeph">LEXICAL</code>, which can be used for any number of sort columns.
<code class="ph codeph">ZORDER</code> can only be used to sort more than one column.
</p>
<p class="p">
Creating data files that are sorted is most useful for Parquet tables, where the
metadata stored inside each file includes the minimum and maximum values for each
column in the file. (The statistics apply to each row group within the file;
for simplicity, Impala writes a single row group in each file.)
Grouping data values together in relatively narrow ranges within each data file makes it
possible for Impala to quickly skip over data files that do not contain value ranges
indicated in the <code class="ph codeph">WHERE</code> clause of a query, and can improve the
effectiveness of Parquet encoding and compression.
</p>
<p class="p">
This clause is not applicable for Kudu tables or HBase tables. Although it works for other
HDFS file formats besides Parquet, the more efficient layout is most evident with Parquet
tables, because each Parquet data file includes statistics about the data values in that
file.
</p>
<p class="p">
The <code class="ph codeph">SORT BY</code> columns cannot include any partition key columns for a
partitioned table, because those column values are not represented in the underlying data
files.
</p>
<p class="p">
Because data files can arrive in Impala tables by mechanisms that do not respect the
<code class="ph codeph">SORT BY</code> clause, such as <code class="ph codeph">LOAD DATA</code> or ETL tools that
create HDFS files, Impala does not guarantee or rely on the data being sorted. The sorting
aspect is only used to create a more efficient layout for Parquet files generated by
Impala, which helps to optimize the processing of those Parquet files during Impala
queries. During an <code class="ph codeph">INSERT</code> or <code class="ph codeph">CREATE TABLE AS SELECT</code>
operation, the sorting occurs when the <code class="ph codeph">SORT BY</code> clause applies to the
destination table for the data, regardless of whether the source table has a <code class="ph codeph">SORT
BY</code> clause.
</p>
<p class="p">
For example, when creating a table intended to contain census data, you might define sort
columns such as last name and state. If a data file in this table contains a narrow range
of last names, for example from <code class="ph codeph">Smith</code> to <code class="ph codeph">Smythe</code>, Impala
can quickly detect that this data file contains no matches for a <code class="ph codeph">WHERE</code>
clause such as <code class="ph codeph">WHERE last_name = 'Jones'</code> and avoid reading the entire
file.
</p>
<pre class="pre codeblock"><code>CREATE TABLE census_data (last_name STRING, first_name STRING, state STRING, address STRING)
SORT BY LEXICAL (last_name, state)
STORED AS PARQUET;
</code></pre>
<p class="p">
Likewise, if an existing table contains data without any sort order, you can reorganize
the data in a more efficient way by using <code class="ph codeph">INSERT</code> or <code class="ph codeph">CREATE TABLE
AS SELECT</code> to copy that data into a new table with a <code class="ph codeph">SORT BY</code>
clause:
</p>
<pre class="pre codeblock"><code>CREATE TABLE sorted_census_data
SORT BY (last_name, state)
STORED AS PARQUET
AS SELECT last_name, first_name, state, address
FROM unsorted_census_data;
</code></pre>
<p class="p">
The metadata for the <code class="ph codeph">SORT BY</code> clause is stored in the
<code class="ph codeph">TBLPROPERTIES</code> fields for the table. Other SQL engines that can
interoperate with Impala tables, such as Hive and Spark SQL, do not recognize this
property when inserting into a table that has a <code class="ph codeph">SORT BY</code> clause.
</p>
<p class="p">
<strong class="ph b">Transactional tables:</strong>
</p>
<p class="p">
In the version 3.3 and higher, when integrated with Hive 3, Impala can create, read, and
insert into transactional tables.
</p>
<div class="p">
To create a table that supports transactions, use the <code class="ph codeph">TBLPROPERTIES</code>
clause and set the <code class="ph codeph">'transactional'</code> and
<code class="ph codeph">'transactional_properties'</code> as below. Currently, Impala only supports
insert-only transactional tables.
<pre class="pre codeblock"><code>TBLPROPERTIES('transactional'='true', 'transactional_properties'='insert_only')</code></pre>
</div>
<p class="p">
When integrated with Hive3 and the <code class="ph codeph">DEFAULT_TRANSACTIONAL_TYPE</code> query
option is set to <code class="ph codeph">INSERT_ONLY</code>, tables are created as insert-only
transactional table by default.
</p>
<p class="p">
Transactional tables are not supported for Kudu and HBase.
</p>
<p class="p">
<strong class="ph b">Kudu considerations:</strong>
</p>
<p class="p">
Because Kudu tables do not support clauses related to HDFS and S3 data files and
partitioning mechanisms, the syntax associated with the <code class="ph codeph">STORED AS KUDU</code>
clause is shown separately in the above syntax descriptions. Kudu tables have their own
syntax for <code class="ph codeph">CREATE TABLE</code>, <code class="ph codeph">CREATE EXTERNAL TABLE</code>, and
<code class="ph codeph">CREATE TABLE AS SELECT</code>. <span class="ph">Prior to
<span class="keyword">Impala 2.10</span>, all internal Kudu tables require a <code class="ph codeph">PARTITION
BY</code> clause, different than the <code class="ph codeph">PARTITIONED BY</code> clause for
HDFS-backed tables.</span>
</p>
<p class="p">
Here are some examples of creating empty Kudu tables:
</p>
<pre class="pre codeblock"><code>
<span class="ph">-- Single partition. Only for <span class="keyword">Impala 2.10</span> and higher.
-- Only suitable for small lookup tables.
CREATE TABLE kudu_no_partition_by_clause
(
id bigint PRIMARY KEY, s STRING, b BOOLEAN
)
STORED AS KUDU;</span>
-- Single-column primary key.
CREATE TABLE kudu_t1 (id BIGINT PRIMARY key, s STRING, b BOOLEAN)
PARTITION BY HASH (id) PARTITIONS 20 STORED AS KUDU;
-- Multi-column primary key.
CREATE TABLE kudu_t2 (id BIGINT, s STRING, b BOOLEAN, PRIMARY KEY (id,s))
PARTITION BY HASH (s) PARTITIONS 30 STORED AS KUDU;
-- Meaningful primary key column is good for range partitioning.
CREATE TABLE kudu_t3 (id BIGINT, year INT, s STRING,
b BOOLEAN, PRIMARY KEY (id,year))
PARTITION BY HASH (id) PARTITIONS 20,
RANGE (year) (PARTITION 1980 &lt;= VALUES &lt; 1990,
PARTITION 1990 &lt;= VALUES &lt; 2000,
PARTITION VALUE = 2001,
PARTITION 2001 &lt; VALUES)
STORED AS KUDU;
</code></pre>
<p class="p">
Here is an example of creating an external Kudu table based on an pre-existing table
identified by the table property: </p>
<pre class="pre codeblock"><code>
-- Inherits column definitions from original table.
-- For tables created through Impala, the kudu.table_name property
-- comes from DESCRIBE FORMATTED output from the original table.
CREATE EXTERNAL TABLE external_t1 STORED AS KUDU
TBLPROPERTIES ('kudu.table_name'='kudu_tbl_created_via_api');
</code></pre>
<p class="p">
In Impala 3.4 and higher, by default HMS implicitly translates internal Kudu tables to
external Kudu tables with the 'external.table.purge' property set to true. You can explicitly
create such external Kudu tables similar to the way you create internal Kudu tables. You must
set the table property <code class="ph codeph">'external.table.purge'</code> to true. Here is an example of
creating an external Kudu table:
</p>
<pre class="pre codeblock"><code>CREATE EXTERNAL TABLE myextkudutbl (
id int PRIMARY KEY,
name string)
PARTITION BY HASH PARTITIONS 8
STORED AS KUDU
TBLPROPERTIES ('external.table.purge'='true');
</code></pre>
<p class="p">
Operations on the resulting external table in Impala, HMS, and Kudu table metadata is
synchronized. HMS-Kudu integration does not need to be enabled for external table
synchronization. Such synchronized tables behave similar to internal tables. For example,
dropping a table removes the underlying Kudu table data as well as the table metadata in HMS.
</p>
<p class="p">
If you want to drop only the HMS metadata and not drop the Kudu table, you
can set <code class="ph codeph">external.table.purge</code> to false, as shown in the following example:
</p>
<pre class="pre codeblock"><code>
ALTER TABLE myextkudutbl set tblproperties('external.table.purge'='false');
</code></pre>
<p class="p">
Here is an example of <code class="ph codeph">CREATE TABLE AS SELECT</code> syntax for a Kudu table:
</p>
<pre class="pre codeblock"><code>
-- The CTAS statement defines the primary key and partitioning scheme.
-- The rest of the column definitions are derived from the select list.
CREATE TABLE ctas_t1
PRIMARY KEY (id) PARTITION BY HASH (id) PARTITIONS 10
STORED AS KUDU
AS SELECT id, s FROM kudu_t1;
</code></pre>
<p class="p">
The following <code class="ph codeph">CREATE TABLE</code> clauses are not supported for Kudu tables:
</p>
<ul class="ul">
<li class="li">
<code class="ph codeph">PARTITIONED BY</code> (Kudu tables use the clause <code class="ph codeph">PARTITION
BY</code> instead)
</li>
<li class="li">
<code class="ph codeph">LOCATION</code>
</li>
<li class="li">
<code class="ph codeph">ROWFORMAT</code>
</li>
<li class="li">
<code class="ph codeph">CACHED IN | UNCACHED</code>
</li>
<li class="li">
<code class="ph codeph">WITH SERDEPROPERTIES</code>
</li>
</ul>
<p class="p">
For more on the <code class="ph codeph">PRIMARY KEY</code> clause, see
<a class="xref" href="impala_kudu.html#kudu_primary_key">Primary Key Columns for Kudu Tables</a> and
<a class="xref" href="impala_kudu.html#kudu_primary_key_attribute">PRIMARY KEY Attribute</a>.
</p>
<p class="p">
For more on creating a Kudu table with a specific replication factor, see
<a class="xref" href="impala_kudu.html#kudu_replication_factor">Kudu Replication Factor</a>.
</p>
<p class="p">
For more on the <code class="ph codeph">NULL</code> and <code class="ph codeph">NOT NULL</code> attributes, see
<a class="xref" href="impala_kudu.html#kudu_not_null_attribute">NULL | NOT NULL Attribute</a>.
</p>
<p class="p">
For more on the <code class="ph codeph">ENCODING</code> attribute, see
<a class="xref" href="impala_kudu.html#kudu_encoding_attribute">ENCODING Attribute</a>.
</p>
<p class="p">
For more on the <code class="ph codeph">COMPRESSION</code> attribute, see
<a class="xref" href="impala_kudu.html#kudu_compression_attribute">COMPRESSION Attribute</a>.
</p>
<p class="p">
For more on the <code class="ph codeph">DEFAULT</code> attribute, see
<a class="xref" href="impala_kudu.html#kudu_default_attribute">DEFAULT Attribute</a>.
</p>
<p class="p">
For more on the <code class="ph codeph">BLOCK_SIZE</code> attribute, see
<a class="xref" href="impala_kudu.html#kudu_block_size_attribute">BLOCK_SIZE Attribute</a>.
</p>
<p class="p">
<strong class="ph b">Partitioning for Kudu tables (PARTITION BY clause)</strong>
</p>
<p class="p">
For Kudu tables, you specify logical partitioning across one or more columns using the
<code class="ph codeph">PARTITION BY</code> clause. In contrast to partitioning for HDFS-based tables,
multiple values for a partition key column can be located in the same partition. The
optional <code class="ph codeph">HASH</code> clause lets you divide one or a set of partition key
columns into a specified number of buckets. You can use more than one
<code class="ph codeph">HASH</code> clause, specifying a distinct set of partition key columns for each.
The optional <code class="ph codeph">RANGE</code> clause further subdivides the partitions, based on a
set of comparison operations for the partition key columns.
</p>
<p class="p">
Here are some examples of the <code class="ph codeph">PARTITION BY HASH</code> syntax:
</p>
<pre class="pre codeblock"><code>
-- Apply hash function to 1 primary key column.
create table hash_t1 (x bigint, y bigint, s string, primary key (x,y))
partition by hash (x) partitions 10
stored as kudu;
-- Apply hash function to a different primary key column.
create table hash_t2 (x bigint, y bigint, s string, primary key (x,y))
partition by hash (y) partitions 10
stored as kudu;
-- Apply hash function to both primary key columns.
-- In this case, the total number of partitions is 10.
create table hash_t3 (x bigint, y bigint, s string, primary key (x,y))
partition by hash (x,y) partitions 10
stored as kudu;
-- When the column list is omitted, apply hash function to all primary key columns.
create table hash_t4 (x bigint, y bigint, s string, primary key (x,y))
partition by hash partitions 10
stored as kudu;
-- Hash the X values independently from the Y values.
-- In this case, the total number of partitions is 10 x 20.
create table hash_t5 (x bigint, y bigint, s string, primary key (x,y))
partition by hash (x) partitions 10, hash (y) partitions 20
stored as kudu;
</code></pre>
<p class="p">
Here are some examples of the <code class="ph codeph">PARTITION BY RANGE</code> syntax:
</p>
<pre class="pre codeblock"><code>
-- Create partitions that cover every possible value of X.
-- Ranges that span multiple values use the keyword VALUES between
-- a pair of &lt; and &lt;= comparisons.
create table range_t1 (x bigint, s string, s2 string, primary key (x, s))
partition by range (x)
(
partition 0 &lt;= values &lt;= 49, partition 50 &lt;= values &lt;= 100,
partition values &lt; 0, partition 100 &lt; values
)
stored as kudu;
-- Create partitions that cover some possible values of X.
-- Values outside the covered range(s) are rejected.
-- New range partitions can be added through ALTER TABLE.
create table range_t2 (x bigint, s string, s2 string, primary key (x, s))
partition by range (x)
(
partition 0 &lt;= values &lt;= 49, partition 50 &lt;= values &lt;= 100
)
stored as kudu;
-- A range can also specify a single specific value, using the keyword VALUE
-- with an = comparison.
create table range_t3 (x bigint, s string, s2 string, primary key (x, s))
partition by range (s)
(
partition value = 'Yes', partition value = 'No', partition value = 'Maybe'
)
stored as kudu;
-- Using multiple columns in the RANGE clause and tuples inside the partition spec
-- only works for partitions specified with the VALUE= syntax.
create table range_t4 (x bigint, s string, s2 string, primary key (x, s))
partition by range (x,s)
(
partition value = (0,'zero'), partition value = (1,'one'), partition value = (2,'two')
)
stored as kudu;
</code></pre>
<p class="p">
Here are some examples combining both <code class="ph codeph">HASH</code> and <code class="ph codeph">RANGE</code>
syntax for the <code class="ph codeph">PARTITION BY</code> clause:
</p>
<pre class="pre codeblock"><code>
-- Values from each range partition are hashed into 10 associated buckets.
-- Total number of partitions in this case is 10 x 2.
create table combined_t1 (x bigint, s string, s2 string, primary key (x, s))
partition by hash (x) partitions 10, range (x)
(
partition 0 &lt;= values &lt;= 49, partition 50 &lt;= values &lt;= 100
)
stored as kudu;
-- The hash partitioning and range partitioning can apply to different columns.
-- But all the columns used in either partitioning scheme must be from the primary key.
create table combined_t2 (x bigint, s string, s2 string, primary key (x, s))
partition by hash (s) partitions 10, range (x)
(
partition 0 &lt;= values &lt;= 49, partition 50 &lt;= values &lt;= 100
)
stored as kudu;
</code></pre>
<p class="p">
For more usage details and examples of the Kudu partitioning syntax, see
<a class="xref" href="impala_kudu.html">Using Impala to Query Kudu Tables</a>.
</p>
<p class="p">
<strong class="ph b">Specifying file format (STORED AS and ROW FORMAT clauses):</strong>
</p>
<p class="p">
The <code class="ph codeph">STORED AS</code> clause identifies the format of the underlying data files.
Currently, Impala can query more types of file formats than it can create or insert into.
Use Hive to perform any create or data load operations that are not currently available in
Impala. For example, Impala can create an Avro, SequenceFile, or RCFile table but cannot
insert data into it. There are also Impala-specific procedures for using compression with
each kind of file format. For details about working with data files of various formats,
see <a class="xref" href="impala_file_formats.html#file_formats">How Impala Works with Hadoop File Formats</a>.
</p>
<div class="note note"><span class="notetitle">Note:</span>
In Impala 1.4.0 and higher, Impala can create Avro tables, which formerly required doing
the <code class="ph codeph">CREATE TABLE</code> statement in Hive. See
<a class="xref" href="impala_avro.html#avro">Using the Avro File Format with Impala Tables</a> for details and examples.
</div>
<p class="p">
By default (when no <code class="ph codeph">STORED AS</code> clause is specified), data files in Impala
tables are created as text files with Ctrl-A (hex 01) characters as the delimiter.
Specify the <code class="ph codeph">ROW FORMAT DELIMITED</code> clause to produce or ingest data files
that use a different delimiter character such as tab or <code class="ph codeph">|</code>, or a different
line end character such as carriage return or newline. When specifying delimiter and line
end characters with the <code class="ph codeph">FIELDS TERMINATED BY</code> and <code class="ph codeph">LINES TERMINATED
BY</code> clauses, use <code class="ph codeph">'\t'</code> for tab, <code class="ph codeph">'\n'</code> for newline
or linefeed, <code class="ph codeph">'\r'</code> for carriage return, and
<code class="ph codeph">\</code><code class="ph codeph">0</code> for ASCII <code class="ph codeph">nul</code> (hex 00). For more
examples of text tables, see <a class="xref" href="impala_txtfile.html#txtfile">Using Text Data Files with Impala Tables</a>.
</p>
<p class="p">
The <code class="ph codeph">ESCAPED BY</code> clause applies both to text files that you create through
an <code class="ph codeph">INSERT</code> statement to an Impala <code class="ph codeph">TEXTFILE</code> table, and to
existing data files that you put into an Impala table directory. (You can ingest existing
data files either by creating the table with <code class="ph codeph">CREATE EXTERNAL TABLE ...
LOCATION</code>, the <code class="ph codeph">LOAD DATA</code> statement, or through an HDFS operation
such as <code class="ph codeph">hdfs dfs -put <var class="keyword varname">file</var>
<var class="keyword varname">hdfs_path</var></code>.) Choose an escape character that is not used
anywhere else in the file, and put it in front of each instance of the delimiter character
that occurs within a field value. Surrounding field values with quotation marks does not
help Impala to parse fields with embedded delimiter characters; the quotation marks are
considered to be part of the column value. If you want to use <code class="ph codeph">\</code> as the
escape character, specify the clause in <span class="keyword cmdname">impala-shell</span> as <code class="ph codeph">ESCAPED
BY '\\'</code>.
</p>
<div class="note note"><span class="notetitle">Note:</span>
The <code class="ph codeph">CREATE TABLE</code> clauses <code class="ph codeph">FIELDS TERMINATED BY</code>,
<code class="ph codeph">ESCAPED BY</code>, and <code class="ph codeph">LINES TERMINATED BY</code> have special rules
for the string literal used for their argument, because they all require a single
character. You can use a regular character surrounded by single or double quotation
marks, an octal sequence such as <code class="ph codeph">'\054'</code> (representing a comma), or an
integer in the range '-127'..'128' (with quotation marks but no backslash), which is
interpreted as a single-byte ASCII character. Negative values are subtracted from 256;
for example, <code class="ph codeph">FIELDS TERMINATED BY '-2'</code> sets the field delimiter to
ASCII code 254, the <span class="q">"Icelandic Thorn"</span> character used as a delimiter by some data
formats.
</div>
<p class="p">
<strong class="ph b">Cloning tables (LIKE clause):</strong>
</p>
<p class="p">
To create an empty table with the same columns, comments, and other attributes as another
table, use the following variation. The <code class="ph codeph">CREATE TABLE ... LIKE</code> form allows
a restricted set of clauses, currently only the <code class="ph codeph">LOCATION</code>,
<code class="ph codeph">COMMENT</code>, and <code class="ph codeph">STORED AS</code> clauses.
</p>
<pre class="pre codeblock"><code>CREATE [EXTERNAL] TABLE [IF NOT EXISTS] [<var class="keyword varname">db_name</var>.]<var class="keyword varname">table_name</var>
<span class="ph">LIKE { [<var class="keyword varname">db_name</var>.]<var class="keyword varname">table_name</var> | PARQUET '<var class="keyword varname">hdfs_path_of_parquet_file</var>' }</span>
[COMMENT '<var class="keyword varname">table_comment</var>']
[STORED AS <var class="keyword varname">file_format</var>]
[LOCATION '<var class="keyword varname">hdfs_path</var>']</code></pre>
<div class="note note"><span class="notetitle">Note:</span>
<p class="p">
To clone the structure of a table and transfer data into it in a single operation, use
the <code class="ph codeph">CREATE TABLE AS SELECT</code> syntax described in the next subsection.
</p>
</div>
<p class="p">
When you clone the structure of an existing table using the <code class="ph codeph">CREATE TABLE ...
LIKE</code> syntax, the new table keeps the same file format as the original one, so you
only need to specify the <code class="ph codeph">STORED AS</code> clause if you want to use a different
file format, or when specifying a view as the original table. (Creating a table
<span class="q">"like"</span> a view produces a text table by default.)
</p>
<p class="p">
Although normally Impala cannot create an HBase table directly, Impala can clone the
structure of an existing HBase table with the <code class="ph codeph">CREATE TABLE ... LIKE</code>
syntax, preserving the file format and metadata from the original table.
</p>
<p class="p">
There are some exceptions to the ability to use <code class="ph codeph">CREATE TABLE ... LIKE</code>
with an Avro table. For example, you cannot use this technique for an Avro table that is
specified with an Avro schema but no columns. When in doubt, check if a <code class="ph codeph">CREATE
TABLE ... LIKE</code> operation works in Hive; if not, it typically will not work in
Impala either.
</p>
<p class="p">
If the original table is partitioned, the new table inherits the same partition key
columns. Because the new table is initially empty, it does not inherit the actual
partitions that exist in the original one. To create partitions in the new table, insert
data or issue <code class="ph codeph">ALTER TABLE ... ADD PARTITION</code> statements.
</p>
<p class="p">
Prior to Impala 1.4.0, it was not possible to use the <code class="ph codeph">CREATE TABLE LIKE
<var class="keyword varname">view_name</var></code> syntax. In Impala 1.4.0 and higher, you can create
a table with the same column definitions as a view using the <code class="ph codeph">CREATE TABLE
LIKE</code> technique. Although <code class="ph codeph">CREATE TABLE LIKE</code> normally inherits
the file format of the original table, a view has no underlying file format, so
<code class="ph codeph">CREATE TABLE LIKE <var class="keyword varname">view_name</var></code> produces a text table by
default. To specify a different file format, include a <code class="ph codeph">STORED AS
<var class="keyword varname">file_format</var></code> clause at the end of the <code class="ph codeph">CREATE TABLE
LIKE</code> statement.
</p>
<p class="p">
Because <code class="ph codeph">CREATE TABLE ... LIKE</code> only manipulates table metadata, not the
physical data of the table, issue <code class="ph codeph">INSERT INTO TABLE</code> statements afterward
to copy any data from the original table into the new one, optionally converting the data
to a new file format. (For some file formats, Impala can do a <code class="ph codeph">CREATE TABLE ...
LIKE</code> to create the table, but Impala cannot insert data in that file format; in
these cases, you must load the data in Hive. See
<a class="xref" href="impala_file_formats.html#file_formats">How Impala Works with Hadoop File Formats</a> for details.)
</p>
<p class="p" id="create_table__ctas">
<strong class="ph b">CREATE TABLE AS SELECT:</strong>
</p>
<p class="p">
The <code class="ph codeph">CREATE TABLE AS SELECT</code> syntax is a shorthand notation to create a
table based on column definitions from another table, and copy data from the source table
to the destination table without issuing any separate <code class="ph codeph">INSERT</code> statement.
This idiom is so popular that it has its own acronym, <span class="q">"CTAS"</span>.
</p>
<p class="p">
The following examples show how to copy data from a source table <code class="ph codeph">T1</code> to a
variety of destinations tables, applying various transformations to the table properties,
table layout, or the data itself as part of the operation:
</p>
<pre class="pre codeblock"><code>
-- Sample table to be the source of CTAS operations.
CREATE TABLE t1 (x INT, y STRING);
INSERT INTO t1 VALUES (1, 'one'), (2, 'two'), (3, 'three');
-- Clone all the columns and data from one table to another.
CREATE TABLE clone_of_t1 AS SELECT * FROM t1;
+-------------------+
| summary |
+-------------------+
| Inserted 3 row(s) |
+-------------------+
-- Clone the columns and data, and convert the data to a different file format.
CREATE TABLE parquet_version_of_t1 STORED AS PARQUET AS SELECT * FROM t1;
+-------------------+
| summary |
+-------------------+
| Inserted 3 row(s) |
+-------------------+
-- Copy only some rows to the new table.
CREATE TABLE subset_of_t1 AS SELECT * FROM t1 WHERE x &gt;= 2;
+-------------------+
| summary |
+-------------------+
| Inserted 2 row(s) |
+-------------------+
-- Same idea as CREATE TABLE LIKE: clone table layout but do not copy any data.
CREATE TABLE empty_clone_of_t1 AS SELECT * FROM t1 WHERE 1=0;
+-------------------+
| summary |
+-------------------+
| Inserted 0 row(s) |
+-------------------+
-- Reorder and rename columns and transform the data.
CREATE TABLE t5 AS SELECT upper(y) AS s, x+1 AS a, 'Entirely new column' AS n FROM t1;
+-------------------+
| summary |
+-------------------+
| Inserted 3 row(s) |
+-------------------+
SELECT * FROM t5;
+-------+---+---------------------+
| s | a | n |
+-------+---+---------------------+
| ONE | 2 | Entirely new column |
| TWO | 3 | Entirely new column |
| THREE | 4 | Entirely new column |
+-------+---+---------------------+
</code></pre>
<p class="p">
See <a class="xref" href="impala_select.html#select">SELECT Statement</a> for details about query syntax for the
<code class="ph codeph">SELECT</code> portion of a <code class="ph codeph">CREATE TABLE AS SELECT</code> statement.
</p>
<p class="p">
The newly created table inherits the column names that you select from the original table,
which you can override by specifying column aliases in the query. Any column or table
comments from the original table are not carried over to the new table.
</p>
<div class="note note"><span class="notetitle">Note:</span>
When using the <code class="ph codeph">STORED AS</code> clause with a <code class="ph codeph">CREATE TABLE AS
SELECT</code> statement, the destination table must be a file format that Impala can
write to: currently, text or Parquet. You cannot specify an Avro, SequenceFile, or RCFile
table as the destination table for a CTAS operation.
</div>
<p class="p">
Prior to <span class="keyword">Impala 2.5</span> you could use a partitioned table as the source
and copy data from it, but could not specify any partitioning clauses for the new table.
In <span class="keyword">Impala 2.5</span> and higher, you can now use the
<code class="ph codeph">PARTITIONED BY</code> clause with a <code class="ph codeph">CREATE TABLE AS SELECT</code>
statement. The following example demonstrates how you can copy data from an unpartitioned
table in a <code class="ph codeph">CREATE TABLE AS SELECT</code> operation, creating a new partitioned
table in the process. The main syntax consideration is the column order in the
<code class="ph codeph">PARTITIONED BY</code> clause and the select list: the partition key columns must
be listed last in the select list, in the same order as in the <code class="ph codeph">PARTITIONED
BY</code> clause. Therefore, in this case, the column order in the destination table is
different from the source table. You also only specify the column names in the
<code class="ph codeph">PARTITIONED BY</code> clause, not the data types or column comments.
</p>
<pre class="pre codeblock"><code>
create table partitions_no (year smallint, month tinyint, s string);
insert into partitions_no values (2016, 1, 'January 2016'),
(2016, 2, 'February 2016'), (2016, 3, 'March 2016');
-- Prove that the source table is not partitioned.
show partitions partitions_no;
ERROR: AnalysisException: Table is not partitioned: ctas_partition_by.partitions_no
-- Create new table with partitions based on column values from source table.
<strong class="ph b">create table partitions_yes partitioned by (year, month)
as select s, year, month from partitions_no;</strong>
+-------------------+
| summary |
+-------------------+
| Inserted 3 row(s) |
+-------------------+
-- Prove that the destination table is partitioned.
show partitions partitions_yes;
+-------+-------+-------+--------+------+...
| year | month | #Rows | #Files | Size |...
+-------+-------+-------+--------+------+...
| 2016 | 1 | -1 | 1 | 13B |...
| 2016 | 2 | -1 | 1 | 14B |...
| 2016 | 3 | -1 | 1 | 11B |...
| Total | | -1 | 3 | 38B |...
+-------+-------+-------+--------+------+...
</code></pre>
<p class="p">
The most convenient layout for partitioned tables is with all the partition key columns at
the end. The CTAS <code class="ph codeph">PARTITIONED BY</code> syntax requires that column order in the
select list, resulting in that same column order in the destination table.
</p>
<pre class="pre codeblock"><code>
describe partitions_no;
+-------+----------+---------+
| name | type | comment |
+-------+----------+---------+
| year | smallint | |
| month | tinyint | |
| s | string | |
+-------+----------+---------+
-- The CTAS operation forced us to put the partition key columns last.
-- Having those columns last works better with idioms such as SELECT *
-- for partitioned tables.
describe partitions_yes;
+-------+----------+---------+
| name | type | comment |
+-------+----------+---------+
| s | string | |
| year | smallint | |
| month | tinyint | |
+-------+----------+---------+
</code></pre>
<p class="p">
Attempting to use a select list with the partition key columns not at the end results in
an error due to a column name mismatch:
</p>
<pre class="pre codeblock"><code>
-- We expect this CTAS to fail because non-key column S
-- comes after key columns YEAR and MONTH in the select list.
create table partitions_maybe partitioned by (year, month)
as select year, month, s from partitions_no;
ERROR: AnalysisException: Partition column name mismatch: year != month
</code></pre>
<p class="p">
For example, the following statements show how you can clone all the data in a table, or a
subset of the columns and/or rows, or reorder columns, rename them, or construct them out
of expressions:
</p>
<p class="p">
As part of a CTAS operation, you can convert the data to any file format that Impala can
write (currently, <code class="ph codeph">TEXTFILE</code> and <code class="ph codeph">PARQUET</code>). You cannot
specify the lower-level properties of a text table, such as the delimiter.
</p>
<p class="p">
<strong class="ph b">Sorting considerations:</strong> Although you can specify an <code class="ph codeph">ORDER BY</code>
clause in an <code class="ph codeph">INSERT ... SELECT</code> statement, any <code class="ph codeph">ORDER BY</code>
clause is ignored and the results are not necessarily sorted. An <code class="ph codeph">INSERT ...
SELECT</code> operation potentially creates many different data files, prepared by
different executor Impala daemons, and therefore the notion of the data being stored in
sorted order is impractical.
</p>
<p class="p">
<strong class="ph b">CREATE TABLE LIKE PARQUET:</strong>
</p>
<p class="p">
The variation <code class="ph codeph">CREATE TABLE ... LIKE PARQUET
'<var class="keyword varname">hdfs_path_of_parquet_file</var>'</code> lets you skip the column
definitions of the <code class="ph codeph">CREATE TABLE</code> statement. The column names and data
types are automatically configured based on the organization of the specified Parquet data
file, which must already reside in HDFS. You can use a data file located outside the
Impala database directories, or a file from an existing Impala Parquet table; either way,
Impala only uses the column definitions from the file and does not use the HDFS location
for the <code class="ph codeph">LOCATION</code> attribute of the new table. (Although you can also
specify the enclosing directory with the <code class="ph codeph">LOCATION</code> attribute, to both use
the same schema as the data file and point the Impala table at the associated directory
for querying.)
</p>
<p class="p">
The following considerations apply when you use the <code class="ph codeph">CREATE TABLE LIKE
PARQUET</code> technique:
</p>
<ul class="ul">
<li class="li">
Any column comments from the original table are not preserved in the new table. Each
column in the new table has a comment stating the low-level Parquet field type used to
deduce the appropriate SQL column type.
</li>
<li class="li">
If you use a data file from a partitioned Impala table, any partition key columns from
the original table are left out of the new table, because they are represented in HDFS
directory names rather than stored in the data file. To preserve the partition
information, repeat the same <code class="ph codeph">PARTITION</code> clause as in the original
<code class="ph codeph">CREATE TABLE</code> statement.
</li>
<li class="li">
The file format of the new table defaults to text, as with other kinds of <code class="ph codeph">CREATE
TABLE</code> statements. To make the new table also use Parquet format, include the
clause <code class="ph codeph">STORED AS PARQUET</code> in the <code class="ph codeph">CREATE TABLE LIKE
PARQUET</code> statement.
</li>
<li class="li">
If the Parquet data file comes from an existing Impala table, currently, any
<code class="ph codeph">TINYINT</code> or <code class="ph codeph">SMALLINT</code> columns are turned into
<code class="ph codeph">INT</code> columns in the new table. Internally, Parquet stores such values as
32-bit integers.
</li>
<li class="li">
When the destination table uses the Parquet file format, the <code class="ph codeph">CREATE TABLE AS
SELECT</code> and <code class="ph codeph">INSERT ... SELECT</code> statements always create at least
one data file, even if the <code class="ph codeph">SELECT</code> part of the statement does not match
any rows. You can use such an empty Parquet data file as a template for subsequent
<code class="ph codeph">CREATE TABLE LIKE PARQUET</code> statements.
</li>
</ul>
<p class="p">
For more details about creating Parquet tables, and examples of the <code class="ph codeph">CREATE TABLE
LIKE PARQUET</code> syntax, see <a class="xref" href="impala_parquet.html#parquet">Using the Parquet File Format with Impala Tables</a>.
</p>
<p class="p">
<strong class="ph b">Visibility and Metadata (TBLPROPERTIES and WITH SERDEPROPERTIES clauses):</strong>
</p>
<p class="p">
You can associate arbitrary items of metadata with a table by specifying the
<code class="ph codeph">TBLPROPERTIES</code> clause. This clause takes a comma-separated list of
key-value pairs and stores those items in the metastore database. You can also change the
table properties later with an <code class="ph codeph">ALTER TABLE</code> statement. You can observe the
table properties for different delimiter and escape characters using the <code class="ph codeph">DESCRIBE
FORMATTED</code> command, and change those settings for an existing table with
<code class="ph codeph">ALTER TABLE ... SET TBLPROPERTIES</code>.
</p>
<p class="p">
You can also associate SerDes properties with the table by specifying key-value pairs
through the <code class="ph codeph">WITH SERDEPROPERTIES</code> clause. This metadata is not used by
Impala, which has its own built-in serializer and deserializer for the file formats it
supports. Particular property values might be needed for Hive compatibility with certain
variations of file formats, particularly Avro.
</p>
<p class="p">
Some DDL operations that interact with other Hadoop components require specifying
particular values in the <code class="ph codeph">SERDEPROPERTIES</code> or
<code class="ph codeph">TBLPROPERTIES</code> fields, such as creating an Avro table or an HBase table.
(You typically create HBase tables in Hive, because they require additional clauses not
currently available in Impala.)
</p>
<p class="p">
To see the column definitions and column comments for an existing table, for example
before issuing a <code class="ph codeph">CREATE TABLE ... LIKE</code> or a <code class="ph codeph">CREATE TABLE ... AS
SELECT</code> statement, issue the statement <code class="ph codeph">DESCRIBE
<var class="keyword varname">table_name</var></code>. To see even more detail, such as the location of
data files and the values for clauses such as <code class="ph codeph">ROW FORMAT</code> and
<code class="ph codeph">STORED AS</code>, issue the statement <code class="ph codeph">DESCRIBE FORMATTED
<var class="keyword varname">table_name</var></code>. <code class="ph codeph">DESCRIBE FORMATTED</code> is also needed
to see any overall table comment (as opposed to individual column comments).
</p>
<p class="p">
After creating a table, your <span class="keyword cmdname">impala-shell</span> session or another
<span class="keyword cmdname">impala-shell</span> connected to the same node can immediately query that
table. There might be a brief interval (one statestore heartbeat) before the table can be
queried through a different Impala node. To make the <code class="ph codeph">CREATE TABLE</code>
statement return only when the table is recognized by all Impala nodes in the cluster,
enable the <code class="ph codeph">SYNC_DDL</code> query option.
</p>
<p class="p">
<strong class="ph b">HDFS caching (CACHED IN clause):</strong>
</p>
<p class="p">
If you specify the <code class="ph codeph">CACHED IN</code> clause, any existing or future data files in
the table directory or the partition subdirectories are designated to be loaded into
memory with the HDFS caching mechanism. See
<a class="xref" href="impala_perf_hdfs_caching.html#hdfs_caching">Using HDFS Caching with Impala (Impala 2.1 or higher only)</a> for details about using
the HDFS caching feature.
</p>
<p class="p">
In <span class="keyword">Impala 2.2</span> and higher, the optional <code class="ph codeph">WITH
REPLICATION</code> clause for <code class="ph codeph">CREATE TABLE</code> and <code class="ph codeph">ALTER
TABLE</code> lets you specify a <dfn class="term">replication factor</dfn>, the number of hosts
on which to cache the same data blocks. When Impala processes a cached data block, where
the cache replication factor is greater than 1, Impala randomly selects a host that has
a cached copy of that data block. This optimization avoids excessive CPU usage on a
single host when the same cached data block is processed multiple times. Where
practical, specify a value greater than or equal to the HDFS block replication factor.
</p>
<p class="p">
<strong class="ph b">Column order</strong>:
</p>
<p class="p">
If you intend to use the table to hold data files produced by some external source,
specify the columns in the same order as they appear in the data files.
</p>
<p class="p">
If you intend to insert or copy data into the table through Impala, or if you have control
over the way externally produced data files are arranged, use your judgment to specify
columns in the most convenient order:
</p>
<ul class="ul">
<li class="li">
<p class="p">
If certain columns are often <code class="ph codeph">NULL</code>, specify those columns last. You
might produce data files that omit these trailing columns entirely. Impala
automatically fills in the <code class="ph codeph">NULL</code> values if so.
</p>
</li>
<li class="li">
<p class="p">
If an unpartitioned table will be used as the source for an <code class="ph codeph">INSERT ...
SELECT</code> operation into a partitioned table, specify last in the unpartitioned
table any columns that correspond to partition key columns in the partitioned table,
and in the same order as the partition key columns are declared in the partitioned
table. This technique lets you use <code class="ph codeph">INSERT ... SELECT *</code> when copying
data to the partitioned table, rather than specifying each column name individually.
</p>
</li>
<li class="li">
<p class="p">
If you specify columns in an order that you later discover is suboptimal, you can
sometimes work around the problem without recreating the table. You can create a view
that selects columns from the original table in a permuted order, then do a
<code class="ph codeph">SELECT *</code> from the view. When inserting data into a table, you can
specify a permuted order for the inserted columns to match the order in the
destination table.
</p>
</li>
</ul>
<p class="p">
<strong class="ph b">Hive considerations:</strong>
</p>
<p class="p">
Impala queries can make use of metadata about the table and columns, such as the number of
rows in a table or the number of different values in a column. Prior to Impala 1.2.2, to
create this metadata, you issued the <code class="ph codeph">ANALYZE TABLE</code> statement in Hive to
gather this information, after creating the table and loading representative data into it.
In Impala 1.2.2 and higher, the <code class="ph codeph">COMPUTE STATS</code> statement produces these
statistics within Impala, without needing to use Hive at all.
</p>
<p class="p">
<strong class="ph b">HBase considerations:</strong>
</p>
<div class="note note"><span class="notetitle">Note:</span>
<p class="p">
The Impala <code class="ph codeph">CREATE TABLE</code> statement cannot create an HBase table, because
it currently does not support the <code class="ph codeph">STORED BY</code> clause needed for HBase
tables. Create such tables in Hive, then query them through Impala. For information on
using Impala with HBase tables, see <a class="xref" href="impala_hbase.html#impala_hbase">Using Impala to Query HBase Tables</a>.
</p>
</div>
<p class="p">
<strong class="ph b">Amazon S3 considerations:</strong>
</p>
<p class="p">
To create a table where the data resides in the Amazon Simple Storage Service (S3),
specify a <code class="ph codeph">s3a://</code> prefix <code class="ph codeph">LOCATION</code> attribute pointing to
the data files in S3.
</p>
<p class="p">
In <span class="keyword">Impala 2.6</span> and higher, you can use this special
<code class="ph codeph">LOCATION</code> syntax as part of a <code class="ph codeph">CREATE TABLE AS SELECT</code>
statement.
</p>
<p class="p">
In <span class="keyword">Impala 2.6</span> and higher, Impala DDL statements such as
<code class="ph codeph">CREATE DATABASE</code>, <code class="ph codeph">CREATE TABLE</code>, <code class="ph codeph">DROP DATABASE
CASCADE</code>, <code class="ph codeph">DROP TABLE</code>, and <code class="ph codeph">ALTER TABLE [ADD|DROP]
PARTITION</code> can create or remove folders as needed in the Amazon S3 system. Prior
to <span class="keyword">Impala 2.6</span>, you had to create folders yourself and point
Impala database, tables, or partitions at them, and manually remove folders when no
longer needed. See <a class="xref" href="../shared/../topics/impala_s3.html#s3">Using Impala with Amazon S3 Object Store</a> for details about reading
and writing S3 data with Impala.
</p>
<p class="p">
<strong class="ph b">Sorting considerations:</strong> Although you can specify an <code class="ph codeph">ORDER BY</code>
clause in an <code class="ph codeph">INSERT ... SELECT</code> statement, any <code class="ph codeph">ORDER BY</code>
clause is ignored and the results are not necessarily sorted. An <code class="ph codeph">INSERT ...
SELECT</code> operation potentially creates many different data files, prepared by
different executor Impala daemons, and therefore the notion of the data being stored in
sorted order is impractical.
</p>
<p class="p">
<strong class="ph b">HDFS considerations:</strong>
</p>
<p class="p">
The <code class="ph codeph">CREATE TABLE</code> statement for an internal table creates a directory in
HDFS. The <code class="ph codeph">CREATE EXTERNAL TABLE</code> statement associates the table with an
existing HDFS directory, and does not create any new directory in HDFS. To locate the HDFS
data directory for a table, issue a <code class="ph codeph">DESCRIBE FORMATTED
<var class="keyword varname">table</var></code> statement. To examine the contents of that HDFS
directory, use an OS command such as <code class="ph codeph">hdfs dfs -ls
hdfs://<var class="keyword varname">path</var></code>, either from the OS command line or through the
<code class="ph codeph">shell</code> or <code class="ph codeph">!</code> commands in <span class="keyword cmdname">impala-shell</span>.
</p>
<p class="p">
The <code class="ph codeph">CREATE TABLE AS SELECT</code> syntax creates data files under the table data
directory to hold any data copied by the <code class="ph codeph">INSERT</code> portion of the statement.
(Even if no data is copied, Impala might create one or more empty data files.)
</p>
<p class="p">
<strong class="ph b">HDFS permissions:</strong>
</p>
<p class="p">
The user ID that the <span class="keyword cmdname">impalad</span> daemon runs under, typically the
<code class="ph codeph">impala</code> user, must have both execute and write permission for the database
directory where the table is being created.
</p>
<p class="p">
<strong class="ph b">Security considerations:</strong>
</p>
<p class="p">
If these statements in your environment contain sensitive literal values such as credit
card numbers or tax identifiers, Impala can redact this sensitive information when
displaying the statements in log files and other administrative contexts. See
<span class="xref">the documentation for your Apache Hadoop distribution</span> for details.
</p>
<p class="p">
<strong class="ph b">Cancellation:</strong> Certain multi-stage statements (<code class="ph codeph">CREATE TABLE AS
SELECT</code> and <code class="ph codeph">COMPUTE STATS</code>) can be cancelled during some stages,
when running <code class="ph codeph">INSERT</code> or <code class="ph codeph">SELECT</code> operations internally.
To cancel this statement, use Ctrl-C from the <span class="keyword cmdname">impala-shell</span>
interpreter, the <span class="ph uicontrol">Cancel</span> button from the
<span class="ph uicontrol">Watch</span> page in Hue, or <span class="ph uicontrol">Cancel</span> from the list
of in-flight queries (for a particular node) on the <span class="ph uicontrol">Queries</span> tab
in the Impala web UI (port 25000).
</p>
<p class="p">
<strong class="ph b">Related information:</strong>
</p>
<p class="p">
<a class="xref" href="impala_tables.html#tables">Overview of Impala Tables</a>,
<a class="xref" href="impala_alter_table.html#alter_table">ALTER TABLE Statement</a>,
<a class="xref" href="impala_drop_table.html#drop_table">DROP TABLE Statement</a>,
<a class="xref" href="impala_partitioning.html#partitioning">Partitioning for Impala Tables</a>,
<a class="xref" href="impala_tables.html#internal_tables">Internal Tables</a>,
<a class="xref" href="impala_tables.html#external_tables">External Tables</a>,
<a class="xref" href="impala_compute_stats.html#compute_stats">COMPUTE STATS Statement</a>,
<a class="xref" href="impala_sync_ddl.html#sync_ddl">SYNC_DDL Query Option</a>,
<a class="xref" href="impala_show.html#show_tables">SHOW TABLES Statement</a>,
<a class="xref" href="impala_show.html#show_create_table">SHOW CREATE TABLE Statement</a>,
<a class="xref" href="impala_describe.html#describe">DESCRIBE Statement</a>
</p>
</div>
<div class="related-links">
<div class="familylinks">
<div class="parentlink"><strong>Parent topic:</strong> <a class="link" href="../topics/impala_langref_sql.html">Impala SQL Statements</a></div>
</div>
</div></body>
</html>