http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/661921b2/docs/topics/impala_create_table.xml ---------------------------------------------------------------------- diff --git a/docs/topics/impala_create_table.xml b/docs/topics/impala_create_table.xml index 818553d..77c6452 100644 --- a/docs/topics/impala_create_table.xml +++ b/docs/topics/impala_create_table.xml @@ -21,7 +21,13 @@ under the License. <concept id="create_table" outputclass="impala sql_statement"> <title outputclass="impala_title sql_statement_title">CREATE TABLE Statement</title> - <titlealts audience="PDF"><navtitle>CREATE TABLE</navtitle></titlealts> + + <titlealts audience="PDF"> + + <navtitle>CREATE TABLE</navtitle> + + </titlealts> + <prolog> <metadata> <data name="Category" value="Impala"/> @@ -34,7 +40,7 @@ under the License. <data name="Category" value="Tables"/> <data name="Category" value="Schemas"/> <data name="Category" value="S3"/> - <!-- <data name="Category" value="Kudu"/> --> + <data name="Category" value="Kudu"/> </metadata> </prolog> @@ -42,8 +48,8 @@ under the License. <p> <indexterm audience="hidden">CREATE TABLE statement</indexterm> - Creates a new table and specifies its characteristics. While creating a table, you optionally specify aspects - such as: + Creates a new table and specifies its characteristics. While creating a table, you + optionally specify aspects such as: </p> <ul> @@ -79,7 +85,10 @@ under the License. </p> <codeblock>CREATE [EXTERNAL] TABLE [IF NOT EXISTS] [<varname>db_name</varname>.]<varname>table_name</varname> - (<varname>col_name</varname> <varname>data_type</varname> [COMMENT '<varname>col_comment</varname>'], ...) + (<varname>col_name</varname> <varname>data_type</varname> + [COMMENT '<varname>col_comment</varname>'] + [, ...] + ) [PARTITIONED BY (<varname>col_name</varname> <varname>data_type</varname> [COMMENT '<varname>col_comment</varname>'], ...)] [COMMENT '<varname>table_comment</varname>'] [WITH SERDEPROPERTIES ('<varname>key1</varname>'='<varname>value1</varname>', '<varname>key2</varname>'='<varname>value2</varname>', ...)] @@ -92,28 +101,6 @@ under the License. </codeblock> <p> - <b>Column definitions inferred from data file:</b> - </p> - -<codeblock>CREATE [EXTERNAL] TABLE [IF NOT EXISTS] [<varname>db_name</varname>.]<varname>table_name</varname> - LIKE PARQUET '<varname>hdfs_path_of_parquet_file</varname>' - [COMMENT '<varname>table_comment</varname>'] - [PARTITIONED BY (<varname>col_name</varname> <varname>data_type</varname> [COMMENT '<varname>col_comment</varname>'], ...)] - [WITH SERDEPROPERTIES ('<varname>key1</varname>'='<varname>value1</varname>', '<varname>key2</varname>'='<varname>value2</varname>', ...)] - [ - [ROW FORMAT <varname>row_format</varname>] [STORED AS <varname>file_format</varname>] - ] - [LOCATION '<varname>hdfs_path</varname>'] - [TBLPROPERTIES ('<varname>key1</varname>'='<varname>value1</varname>', '<varname>key2</varname>'='<varname>value2</varname>', ...)] -<ph rev="1.4.0"> [CACHED IN '<varname>pool_name</varname>'</ph> <ph rev="2.2.0">[WITH REPLICATION = <varname>integer</varname>]</ph> | UNCACHED] -data_type: - <varname>primitive_type</varname> - | array_type - | map_type - | struct_type -</codeblock> - - <p> <b>CREATE TABLE AS SELECT:</b> </p> @@ -171,9 +158,106 @@ file_format: | TEXTFILE</ph> </codeblock> + <p> + <b>Column definitions inferred from data file:</b> + </p> + +<codeblock>CREATE [EXTERNAL] TABLE [IF NOT EXISTS] [<varname>db_name</varname>.]<varname>table_name</varname> + LIKE PARQUET '<varname>hdfs_path_of_parquet_file</varname>' + [COMMENT '<varname>table_comment</varname>'] + [PARTITIONED BY (<varname>col_name</varname> <varname>data_type</varname> [COMMENT '<varname>col_comment</varname>'], ...)] + [WITH SERDEPROPERTIES ('<varname>key1</varname>'='<varname>value1</varname>', '<varname>key2</varname>'='<varname>value2</varname>', ...)] + [ + [ROW FORMAT <varname>row_format</varname>] [STORED AS <varname>file_format</varname>] + ] + [LOCATION '<varname>hdfs_path</varname>'] + [TBLPROPERTIES ('<varname>key1</varname>'='<varname>value1</varname>', '<varname>key2</varname>'='<varname>value2</varname>', ...)] +<ph rev="1.4.0"> [CACHED IN '<varname>pool_name</varname>'</ph> <ph rev="2.2.0">[WITH REPLICATION = <varname>integer</varname>]</ph> | UNCACHED] +data_type: + <varname>primitive_type</varname> + | array_type + | map_type + | struct_type +</codeblock> + + <p> + <b>Kudu tables:</b> + </p> + +<codeblock rev="kudu">CREATE TABLE [IF NOT EXISTS] [<varname>db_name</varname>.]<varname>table_name</varname> + (<varname>col_name</varname> <varname>data_type</varname> + <ph rev="kudu IMPALA-3719">[<varname>kudu_column_attribute</varname> ...]</ph> + [COMMENT '<varname>col_comment</varname>'] + [, ...] + [PRIMARY KEY (<varname>col_name</varname>[, ...])] + ) + <ph rev="kudu">[PARTITION BY <varname>kudu_partition_clause</varname></ph> + [COMMENT '<varname>table_comment</varname>'] + STORED AS KUDU + [TBLPROPERTIES ('<varname>key1</varname>'='<varname>value1</varname>', '<varname>key2</varname>'='<varname>value2</varname>', ...)] +</codeblock> + + <p rev="kudu IMPALA-3719"> + <b>Kudu column attributes:</b> +<codeblock rev="kudu"> + PRIMARY KEY +| [NOT] NULL +| ENCODING <varname>codec</varname> +| COMPRESSION <varname>algorithm</varname> +| DEFAULT <varname>constant</varname> +| BLOCK_SIZE <varname>number</varname> +</codeblock> + </p> + + <p rev="kudu IMPALA-3719"> + <b>kudu_partition_clause:</b> +<codeblock rev="kudu"> +kudu_partition_clause ::= PARTITION BY [<varname>hash_clause</varname>] [, <varname>range_clause</varname> [ , <varname>range_clause</varname> ] ] + +hash_clause ::= + HASH [ (<varname>pk_col</varname> [, ...]) ] + PARTITIONS <varname>n</varname> + +range_clause ::= + RANGE [ (<varname>pk_col</varname> [, ...]) ] + ( + { + PARTITION <varname>constant_expression</varname> <varname>range_comparison_operator</varname> VALUES <varname>range_comparison_operator</varname> <varname>constant_expression</varname> + | PARTITION VALUE = <varname>constant_expression_or_tuple</varname> + } + [, ...] + ) + +range_comparison_operator ::= { < | <= } +</codeblock> + </p> + + <p> + <b>External Kudu tables:</b> + </p> + +<codeblock rev="kudu">CREATE EXTERNAL TABLE [IF NOT EXISTS] [<varname>db_name</varname>.]<varname>table_name</varname> + [COMMENT '<varname>table_comment</varname>'] + STORED AS KUDU + [TBLPROPERTIES ('kudu.table_name'='<varname>internal_kudu_name</varname>')] +</codeblock> + + <p> + <b>CREATE TABLE AS SELECT for Kudu tables:</b> + </p> + +<codeblock rev="kudu">CREATE TABLE [IF NOT EXISTS] <varname>db_name</varname>.]<varname>table_name</varname> + [PRIMARY KEY (<varname>col_name</varname>[, ...])] + [PARTITION BY <varname>kudu_partition_clause</varname> + [COMMENT '<varname>table_comment</varname>'] + STORED AS KUDU + [TBLPROPERTIES ('<varname>key1</varname>'='<varname>value1</varname>', '<varname>key2</varname>'='<varname>value2</varname>', ...)] +AS + <varname>select_statement</varname></codeblock> + <p conref="../shared/impala_common.xml#common/ddl_blurb"/> - <!-- Should really have some info up front about all the data types and file formats. +<!-- Should really have some info up front about all the data types and file formats. Consider adding here, or at least making inline links to the relevant keywords in the syntax spec above. --> @@ -182,45 +266,47 @@ file_format: </p> <p> - Depending on the form of the <codeph>CREATE TABLE</codeph> statement, the column definitions are - required or not allowed. + Depending on the form of the <codeph>CREATE TABLE</codeph> statement, the column + definitions are required or not allowed. </p> <p> With the <codeph>CREATE TABLE AS SELECT</codeph> and <codeph>CREATE TABLE LIKE</codeph> - syntax, you do not specify the columns at all; the column names and types are derived from the source table, query, - or data file. + 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> With the basic <codeph>CREATE TABLE</codeph> syntax, you must list one or more columns, - its name, type, 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 - <codeph>STORED AS AVRO</codeph> clause, you can omit the list of columns and specify the same metadata - as part of the <codeph>TBLPROPERTIES</codeph> clause. + its name, type, 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 <codeph>STORED AS AVRO</codeph> clause, you can omit the list of columns + and specify the same metadata as part of the <codeph>TBLPROPERTIES</codeph> clause. </p> <p conref="../shared/impala_common.xml#common/complex_types_blurb"/> <p rev="2.3.0"> - The Impala complex types (<codeph>STRUCT</codeph>, <codeph>ARRAY</codeph>, or <codeph>MAP</codeph>) - are available in <keyword keyref="impala23_full"/> 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 <xref href="impala_complex_types.xml#complex_types"/> for usage details. + The Impala complex types (<codeph>STRUCT</codeph>, <codeph>ARRAY</codeph>, or + <codeph>MAP</codeph>) are available in <keyword keyref="impala23_full"/> 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 + <xref href="impala_complex_types.xml#complex_types"/> for usage details. </p> - <!-- This is kind of an obscure and rare usage scenario. Consider moving all the complex type stuff further down +<!-- This is kind of an obscure and rare usage scenario. Consider moving all the complex type stuff further down after some of the more common clauses. --> + <p rev="2.3.0"> 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 <codeph>ALTER TABLE</codeph> to change the file format of individual partitions to Parquet; Impala - can then query only the Parquet-format partitions in that table. + 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 <codeph>ALTER TABLE</codeph> to change the file + format of individual partitions to Parquet; Impala can then query only the Parquet-format + partitions in that table. </p> <p conref="../shared/impala_common.xml#common/complex_types_partitioning"/> @@ -230,19 +316,20 @@ file_format: </p> <p> - By default, Impala creates an <q>internal</q> 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 - <codeph>EXTERNAL</codeph> clause, Impala treats the table as an <q>external</q> 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 + By default, Impala creates an <q>internal</q> 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 <codeph>EXTERNAL</codeph> clause, Impala treats the table as an + <q>external</q> 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 <xref href="impala_tables.xml#tables"/>. </p> <p> - Typically, for an external table you include a <codeph>LOCATION</codeph> 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 <filepath>/user/etl/destination</filepath>, you might create an external - table as follows: + Typically, for an external table you include a <codeph>LOCATION</codeph> 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 + <filepath>/user/etl/destination</filepath>, you might create an external table as follows: </p> <codeblock>CREATE EXTERNAL TABLE external_parquet (c1 INT, c2 STRING, c3 TIMESTAMP) @@ -250,11 +337,12 @@ file_format: </codeblock> <p> - Although the <codeph>EXTERNAL</codeph> and <codeph>LOCATION</codeph> clauses are often specified together, - <codeph>LOCATION</codeph> is optional for external tables, and you can also specify <codeph>LOCATION</codeph> - for internal tables. The difference is all about whether Impala <q>takes control</q> 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 <codeph>LOCATION</codeph> attribute, see + Although the <codeph>EXTERNAL</codeph> and <codeph>LOCATION</codeph> clauses are often + specified together, <codeph>LOCATION</codeph> is optional for external tables, and you can + also specify <codeph>LOCATION</codeph> for internal tables. The difference is all about + whether Impala <q>takes control</q> 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 <codeph>LOCATION</codeph> attribute, see <xref href="impala_tables.xml#tables"/>. </p> @@ -263,80 +351,330 @@ file_format: </p> <p> - The <codeph>PARTITIONED BY</codeph> 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 <xref href="impala_partitioning.xml#partitioning"/>. + The <codeph>PARTITIONED BY</codeph> 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 + <xref href="impala_partitioning.xml#partitioning"/>. </p> + <note rev="IMPALA-3719"> + <p> + All Kudu tables require partitioning, which involves different syntax than non-Kudu + tables. See the <codeph>PARTITION BY</codeph> clause, rather than <codeph>PARTITIONED + BY</codeph>, for Kudu tables. + </p> + </note> + <p rev="2.5.0"> - Prior to <keyword keyref="impala25_full"/> 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 <keyword keyref="impala25_full"/> and higher, you can now use the <codeph>PARTITIONED BY</codeph> clause with a - <codeph>CREATE TABLE AS SELECT</codeph> statement. See the examples under the following discussion of - the <codeph>CREATE TABLE AS SELECT</codeph> syntax variation. + Prior to <keyword keyref="impala25_full"/>, 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 <keyword keyref="impala25_full"/> and higher, you can now use the + <codeph>PARTITIONED BY</codeph> clause with a <codeph>CREATE TABLE AS SELECT</codeph> + statement. See the examples under the following discussion of the <codeph>CREATE TABLE AS + SELECT</codeph> syntax variation. </p> -<!-- + <p rev="kudu" conref="../shared/impala_common.xml#common/kudu_blurb"/> + <p rev="kudu"> - <b>Partitioning for Kudu tables (PARTITION BY clause)</b> + Because Kudu tables do not support clauses related to HDFS and S3 data files and + partitioning mechanisms, the syntax associated with the <codeph>STORED AS KUDU</codeph> + clause is shown separately in the above syntax descriptions. Kudu tables have their own + syntax for <codeph>CREATE TABLE</codeph>, <codeph>CREATE EXTERNAL TABLE</codeph>, and + <codeph>CREATE TABLE AS SELECT</codeph>. All internal Kudu tables require a + <codeph>PARTITION BY</codeph> clause, different than the <codeph>PARTITIONED BY</codeph> + clause for HDFS-backed tables. + </p> + + <p> + Here are some examples of creating empty Kudu tables: + </p> + +<codeblock><![CDATA[ +-- 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 <= VALUES < 1990, + PARTITION 1990 <= VALUES < 2000, + PARTITION VALUE = 2001, + PARTITION 2001 < VALUES) + STORED AS KUDU; +]]> +</codeblock> + + <p> + Here is an example of creating an external Kudu table: + </p> + +<codeblock><![CDATA[ +-- 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'); +]]> +</codeblock> + + <p> + Here is an example of <codeph>CREATE TABLE AS SELECT</codeph> syntax for a Kudu table: </p> +<codeblock><![CDATA[ +-- 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; +]]> +</codeblock> + +<!-- This clause restriction and other syntax nuances come from this blog post: + http://blog.cloudera.com/blog/2015/11/how-to-use-impala-with-kudu/ + which has some details and examples not found in the actual Kudu docs. --> + <p rev="kudu"> + The following <codeph>CREATE TABLE</codeph> clauses are not supported for Kudu tables: + </p> + + <ul rev="kudu"> + <li> + <codeph>PARTITIONED BY</codeph> (Kudu tables use the clause <codeph>PARTITION + BY</codeph> instead) + </li> + + <li> + <codeph>LOCATION</codeph> + </li> + + <li> + <codeph>ROWFORMAT</codeph> + </li> + + <li> + <codeph>CACHED IN | UNCACHED</codeph> + </li> + + <li> + <codeph>WITH SERDEPROPERTIES</codeph> + </li> + </ul> + + <p rev="IMPALA-3719"> + For more on the <codeph>PRIMARY KEY</codeph> clause, see + <xref href="impala_kudu.xml#kudu_primary_key"/> and + <xref href="impala_kudu.xml#kudu_primary_key_attribute"/>. + </p> + + <p rev="IMPALA-3719"> + For more on the <codeph>NULL</codeph> and <codeph>NOT NULL</codeph> attributes, see + <xref href="impala_kudu.xml#kudu_not_null_attribute"/>. + </p> + + <p rev="IMPALA-3719"> + For more on the <codeph>ENCODING</codeph> attribute, see + <xref href="impala_kudu.xml#kudu_encoding_attribute"/>. + </p> + + <p rev="IMPALA-3719"> + For more on the <codeph>COMPRESSION</codeph> attribute, see + <xref href="impala_kudu.xml#kudu_compression_attribute"/>. + </p> + + <p rev="IMPALA-3719"> + For more on the <codeph>DEFAULT</codeph> attribute, see + <xref href="impala_kudu.xml#kudu_default_attribute"/>. + </p> + + <p rev="IMPALA-3719"> + For more on the <codeph>BLOCK_SIZE</codeph> attribute, see + <xref href="impala_kudu.xml#kudu_block_size_attribute"/>. + </p> + + <p rev="kudu IMPALA-3719"> + <b>Partitioning for Kudu tables (PARTITION BY clause)</b> + </p> + + <p rev="kudu IMPALA-3719"> For Kudu tables, you specify logical partitioning across one or more columns using the <codeph>PARTITION BY</codeph> 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 <codeph>HASH</codeph> clause lets you divide one or a set of partition key columns - into a specified number of partitions; you can use more than one <codeph>HASH</codeph> - clause, specifying a distinct set of partition key columns for each. - The optional <codeph>RANGE</codeph> clause further subdivides the partitions, based on - a range of values for the partition key columns. + multiple values for a partition key column can be located in the same partition. The + optional <codeph>HASH</codeph> clause lets you divide one or a set of partition key + columns into a specified number of buckets. You can use more than one + <codeph>HASH</codeph> clause, specifying a distinct set of partition key columns for each. + The optional <codeph>RANGE</codeph> clause further subdivides the partitions, based on a + set of comparison operations for the partition key columns. + </p> + + <p rev="kudu IMPALA-3719"> + Here are some examples of the <codeph>PARTITION BY HASH</codeph> syntax: + </p> + +<codeblock rev="kudu IMPALA-3719"><![CDATA[ +-- 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; +]]> +</codeblock> + + <p rev="kudu IMPALA-3719"> + Here are some examples of the <codeph>PARTITION BY RANGE</codeph> syntax: + </p> + +<codeblock rev="kudu IMPALA-3719"><![CDATA[ +-- Create partitions that cover every possible value of X. +-- Ranges that span multiple values use the keyword VALUES between +-- a pair of < and <= comparisons. +create table range_t1 (x bigint, s string, s2 string, primary key (x, s)) + partition by range (x) + ( + partition 0 <= values <= 49, partition 50 <= values <= 100, + partition values < 0, partition 100 < 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 <= values <= 49, partition 50 <= values <= 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; +]]> +</codeblock> + + <p rev="kudu IMPALA-3719"> + Here are some examples combining both <codeph>HASH</codeph> and <codeph>RANGE</codeph> + syntax for the <codeph>PARTITION BY</codeph> clause: + </p> + +<codeblock rev="kudu IMPALA-3719"><![CDATA[ +-- 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 <= values <= 49, partition 50 <= values <= 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 <= values <= 49, partition 50 <= values <= 100 + ) + stored as kudu; +]]> +</codeblock> + + <p rev="kudu IMPALA-3719"> + For more usage details and examples of the Kudu partitioning syntax, see + <xref keyref="impala_kudu"/>. </p> ---> <p> <b>Specifying file format (STORED AS and ROW FORMAT clauses):</b> </p> <p rev="DOCS-1523"> - The <codeph>STORED AS</codeph> 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 - <xref href="impala_file_formats.xml#file_formats"/>. + The <codeph>STORED AS</codeph> 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 <xref href="impala_file_formats.xml#file_formats"/>. </p> <note> - In Impala 1.4.0 and higher, Impala can create Avro tables, which formerly required doing the <codeph>CREATE - TABLE</codeph> statement in Hive. See <xref href="impala_avro.xml#avro"/> for details and examples. + In Impala 1.4.0 and higher, Impala can create Avro tables, which formerly required doing + the <codeph>CREATE TABLE</codeph> statement in Hive. See + <xref href="impala_avro.xml#avro"/> for details and examples. </note> <p> - By default (when no <codeph>STORED AS</codeph> clause is specified), data files in Impala tables are created - as text files with Ctrl-A (hex 01) characters as the delimiter. + By default (when no <codeph>STORED AS</codeph> clause is specified), data files in Impala + tables are created as text files with Ctrl-A (hex 01) characters as the delimiter. <!-- Verify if ROW FORMAT is entirely ignored outside of text tables, or does it apply somehow to SequenceFile and/or RCFile too? --> - Specify the <codeph>ROW FORMAT DELIMITED</codeph> clause to produce or ingest data files that use a different - delimiter character such as tab or <codeph>|</codeph>, or a different line end character such as carriage - return or newline. When specifying delimiter and line end characters with the <codeph>FIELDS TERMINATED - BY</codeph> and <codeph>LINES TERMINATED BY</codeph> clauses, use <codeph>'\t'</codeph> for tab, - <codeph>'\n'</codeph> for newline or linefeed, <codeph>'\r'</codeph> for carriage return, and - <codeph>\</codeph><codeph>0</codeph> for ASCII <codeph>nul</codeph> (hex 00). For more examples of text - tables, see <xref href="impala_txtfile.xml#txtfile"/>. + Specify the <codeph>ROW FORMAT DELIMITED</codeph> clause to produce or ingest data files + that use a different delimiter character such as tab or <codeph>|</codeph>, or a different + line end character such as carriage return or newline. When specifying delimiter and line + end characters with the <codeph>FIELDS TERMINATED BY</codeph> and <codeph>LINES TERMINATED + BY</codeph> clauses, use <codeph>'\t'</codeph> for tab, <codeph>'\n'</codeph> for newline + or linefeed, <codeph>'\r'</codeph> for carriage return, and + <codeph>\</codeph><codeph>0</codeph> for ASCII <codeph>nul</codeph> (hex 00). For more + examples of text tables, see <xref href="impala_txtfile.xml#txtfile"/>. </p> <p> - The <codeph>ESCAPED BY</codeph> clause applies both to text files that you create through an - <codeph>INSERT</codeph> statement to an Impala <codeph>TEXTFILE</codeph> 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 <codeph>CREATE EXTERNAL TABLE ... LOCATION</codeph>, the <codeph>LOAD DATA</codeph> statement, or - through an HDFS operation such as <codeph>hdfs dfs -put <varname>file</varname> - <varname>hdfs_path</varname></codeph>.) 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 - <codeph>\</codeph> as the escape character, specify the clause in <cmdname>impala-shell</cmdname> as - <codeph>ESCAPED BY '\\'</codeph>. + The <codeph>ESCAPED BY</codeph> clause applies both to text files that you create through + an <codeph>INSERT</codeph> statement to an Impala <codeph>TEXTFILE</codeph> 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 <codeph>CREATE EXTERNAL TABLE ... + LOCATION</codeph>, the <codeph>LOAD DATA</codeph> statement, or through an HDFS operation + such as <codeph>hdfs dfs -put <varname>file</varname> + <varname>hdfs_path</varname></codeph>.) 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 <codeph>\</codeph> as the + escape character, specify the clause in <cmdname>impala-shell</cmdname> as <codeph>ESCAPED + BY '\\'</codeph>. </p> <note conref="../shared/impala_common.xml#common/thorn"/> @@ -346,10 +684,10 @@ file_format: </p> <p> - To create an empty table with the same columns, comments, and other attributes as another table, use the - following variation. The <codeph>CREATE TABLE ... LIKE</codeph> form allows a restricted set of clauses, - currently only the <codeph>LOCATION</codeph>, <codeph>COMMENT</codeph>, and <codeph>STORED AS</codeph> - clauses. + To create an empty table with the same columns, comments, and other attributes as another + table, use the following variation. The <codeph>CREATE TABLE ... LIKE</codeph> form allows + a restricted set of clauses, currently only the <codeph>LOCATION</codeph>, + <codeph>COMMENT</codeph>, and <codeph>STORED AS</codeph> clauses. </p> <codeblock>CREATE [EXTERNAL] TABLE [IF NOT EXISTS] [<varname>db_name</varname>.]<varname>table_name</varname> @@ -360,45 +698,49 @@ file_format: <note rev="1.2.0"> <p rev="1.2.0"> - To clone the structure of a table and transfer data into it in a single operation, use the <codeph>CREATE - TABLE AS SELECT</codeph> syntax described in the next subsection. + To clone the structure of a table and transfer data into it in a single operation, use + the <codeph>CREATE TABLE AS SELECT</codeph> syntax described in the next subsection. </p> </note> <p> - When you clone the structure of an existing table using the <codeph>CREATE TABLE ... LIKE</codeph> syntax, - the new table keeps the same file format as the original one, so you only need to specify the <codeph>STORED - AS</codeph> clause if you want to use a different file format, or when specifying a view as the original - table. (Creating a table <q>like</q> a view produces a text table by default.) + When you clone the structure of an existing table using the <codeph>CREATE TABLE ... + LIKE</codeph> syntax, the new table keeps the same file format as the original one, so you + only need to specify the <codeph>STORED AS</codeph> clause if you want to use a different + file format, or when specifying a view as the original table. (Creating a table + <q>like</q> a view produces a text table by default.) </p> <p> - Although normally Impala cannot create an HBase table directly, Impala can clone the structure of an existing - HBase table with the <codeph>CREATE TABLE ... LIKE</codeph> syntax, preserving the file format and metadata - from the original table. + Although normally Impala cannot create an HBase table directly, Impala can clone the + structure of an existing HBase table with the <codeph>CREATE TABLE ... LIKE</codeph> + syntax, preserving the file format and metadata from the original table. </p> <p> - There are some exceptions to the ability to use <codeph>CREATE TABLE ... LIKE</codeph> 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 <codeph>CREATE TABLE ... LIKE</codeph> operation works in Hive; if not, it - typically will not work in Impala either. + There are some exceptions to the ability to use <codeph>CREATE TABLE ... LIKE</codeph> + 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 <codeph>CREATE + TABLE ... LIKE</codeph> operation works in Hive; if not, it typically will not work in + Impala either. </p> <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 <codeph>ALTER TABLE ... ADD PARTITION</codeph> statements. + 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 <codeph>ALTER TABLE ... ADD PARTITION</codeph> statements. </p> <p conref="../shared/impala_common.xml#common/create_table_like_view"/> <p> - Because <codeph>CREATE TABLE ... LIKE</codeph> only manipulates table metadata, not the physical data of the - table, issue <codeph>INSERT INTO TABLE</codeph> 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 <codeph>CREATE TABLE ... LIKE</codeph> to create the table, but Impala cannot insert data in that file - format; in these cases, you must load the data in Hive. See + Because <codeph>CREATE TABLE ... LIKE</codeph> only manipulates table metadata, not the + physical data of the table, issue <codeph>INSERT INTO TABLE</codeph> 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 <codeph>CREATE TABLE ... + LIKE</codeph> to create the table, but Impala cannot insert data in that file format; in + these cases, you must load the data in Hive. See <xref href="impala_file_formats.xml#file_formats"/> for details.) </p> @@ -407,16 +749,16 @@ file_format: </p> <p> - The <codeph>CREATE TABLE AS SELECT</codeph> 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 <codeph>INSERT</codeph> statement. This idiom is so popular that it has its own acronym, - <q>CTAS</q>. + The <codeph>CREATE TABLE AS SELECT</codeph> 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 <codeph>INSERT</codeph> statement. + This idiom is so popular that it has its own acronym, <q>CTAS</q>. </p> <p> - The following examples show how to copy data from a source table <codeph>T1</codeph> - to a variety of destinations tables, applying various transformations to the table - properties, table layout, or the data itself as part of the operation: + The following examples show how to copy data from a source table <codeph>T1</codeph> 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> <codeblock> @@ -484,34 +826,36 @@ CREATE TABLE synthesized_columns AS SELECT upper(c1) AS all_caps, c2+c3 AS total <!-- CREATE TABLE AS <select> now incorporated up higher in the original syntax diagram. --> <p rev="1.2"> - See <xref href="impala_select.xml#select"/> for details about query syntax for the <codeph>SELECT</codeph> - portion of a <codeph>CREATE TABLE AS SELECT</codeph> statement. + See <xref href="impala_select.xml#select"/> for details about query syntax for the + <codeph>SELECT</codeph> portion of a <codeph>CREATE TABLE AS SELECT</codeph> statement. </p> <p rev="1.2"> - 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. + 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> <note rev="DOCS-1523"> - When using the <codeph>STORED AS</codeph> clause with a <codeph>CREATE TABLE AS SELECT</codeph> - 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. + When using the <codeph>STORED AS</codeph> clause with a <codeph>CREATE TABLE AS + SELECT</codeph> 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. </note> <p rev="2.5.0"> - Prior to <keyword keyref="impala25_full"/> 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 <keyword keyref="impala25_full"/> and higher, you can now use the <codeph>PARTITIONED BY</codeph> clause with a - <codeph>CREATE TABLE AS SELECT</codeph> statement. The following example demonstrates how you can copy - data from an unpartitioned table in a <codeph>CREATE TABLE AS SELECT</codeph> operation, creating a new - partitioned table in the process. The main syntax consideration is the column order in the <codeph>PARTITIONED BY</codeph> - clause and the select list: the partition key columns must be listed last in the select list, in the same - order as in the <codeph>PARTITIONED BY</codeph> 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 - <codeph>PARTITIONED BY</codeph> clause, not the data types or column comments. + Prior to <keyword keyref="impala25_full"/> 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 <keyword keyref="impala25_full"/> and higher, you can now use the <codeph>PARTITIONED + BY</codeph> clause with a <codeph>CREATE TABLE AS SELECT</codeph> statement. The following + example demonstrates how you can copy data from an unpartitioned table in a <codeph>CREATE + TABLE AS SELECT</codeph> operation, creating a new partitioned table in the process. The + main syntax consideration is the column order in the <codeph>PARTITIONED BY</codeph> + clause and the select list: the partition key columns must be listed last in the select + list, in the same order as in the <codeph>PARTITIONED BY</codeph> 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 <codeph>PARTITIONED BY</codeph> clause, not + the data types or column comments. </p> <codeblock rev="2.5.0"> @@ -544,12 +888,11 @@ show partitions partitions_yes; +-------+-------+-------+--------+------+... </codeblock> - <p rev="2.5.0"> - The most convenient layout for partitioned tables is with all the - partition key columns at the end. The CTAS <codeph>PARTITIONED BY</codeph> syntax - requires that column order in the select list, resulting in that same - column order in the destination table. - </p> + <p rev="2.5.0"> + The most convenient layout for partitioned tables is with all the partition key columns at + the end. The CTAS <codeph>PARTITIONED BY</codeph> syntax requires that column order in the + select list, resulting in that same column order in the destination table. + </p> <codeblock rev="2.5.0"> describe partitions_no; @@ -574,10 +917,10 @@ describe partitions_yes; +-------+----------+---------+ </codeblock> - <p rev="2.5.0"> - 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> + <p rev="2.5.0"> + 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> <codeblock rev="2.5.0"> -- We expect this CTAS to fail because non-key column S @@ -588,14 +931,15 @@ ERROR: AnalysisException: Partition column name mismatch: year != month </codeblock> <p rev="1.2"> - 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: + 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 rev="1.2"> - As part of a CTAS operation, you can convert the data to any file format that Impala can write (currently, - <codeph>TEXTFILE</codeph> and <codeph>PARQUET</codeph>). You cannot specify the lower-level properties of a - text table, such as the delimiter. + As part of a CTAS operation, you can convert the data to any file format that Impala can + write (currently, <codeph>TEXTFILE</codeph> and <codeph>PARQUET</codeph>). You cannot + specify the lower-level properties of a text table, such as the delimiter. </p> <p rev="obwl" conref="../shared/impala_common.xml#common/insert_sort_blurb"/> @@ -605,57 +949,65 @@ ERROR: AnalysisException: Partition column name mismatch: year != month </p> <p rev="1.4.0"> - The variation <codeph>CREATE TABLE ... LIKE PARQUET '<varname>hdfs_path_of_parquet_file</varname>'</codeph> - lets you skip the column definitions of the <codeph>CREATE TABLE</codeph> 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 <codeph>LOCATION</codeph> attribute of the new table. (Although - you can also specify the enclosing directory with the <codeph>LOCATION</codeph> attribute, to both use the - same schema as the data file and point the Impala table at the associated directory for querying.) + The variation <codeph>CREATE TABLE ... LIKE PARQUET + '<varname>hdfs_path_of_parquet_file</varname>'</codeph> lets you skip the column + definitions of the <codeph>CREATE TABLE</codeph> 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 <codeph>LOCATION</codeph> attribute of the new table. (Although you can also + specify the enclosing directory with the <codeph>LOCATION</codeph> attribute, to both use + the same schema as the data file and point the Impala table at the associated directory + for querying.) </p> <p rev="1.4.0"> - The following considerations apply when you use the <codeph>CREATE TABLE LIKE PARQUET</codeph> technique: + The following considerations apply when you use the <codeph>CREATE TABLE LIKE + PARQUET</codeph> technique: </p> <ul rev="1.4.0"> <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. + 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> - 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 <codeph>PARTITION</codeph> clause as - in the original <codeph>CREATE TABLE</codeph> statement. + 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 <codeph>PARTITION</codeph> clause as in the original + <codeph>CREATE TABLE</codeph> statement. </li> <li> - The file format of the new table defaults to text, as with other kinds of <codeph>CREATE TABLE</codeph> - statements. To make the new table also use Parquet format, include the clause <codeph>STORED AS - PARQUET</codeph> in the <codeph>CREATE TABLE LIKE PARQUET</codeph> statement. + The file format of the new table defaults to text, as with other kinds of <codeph>CREATE + TABLE</codeph> statements. To make the new table also use Parquet format, include the + clause <codeph>STORED AS PARQUET</codeph> in the <codeph>CREATE TABLE LIKE + PARQUET</codeph> statement. </li> <li> - If the Parquet data file comes from an existing Impala table, currently, any <codeph>TINYINT</codeph> or - <codeph>SMALLINT</codeph> columns are turned into <codeph>INT</codeph> columns in the new table. - Internally, Parquet stores such values as 32-bit integers. + If the Parquet data file comes from an existing Impala table, currently, any + <codeph>TINYINT</codeph> or <codeph>SMALLINT</codeph> columns are turned into + <codeph>INT</codeph> columns in the new table. Internally, Parquet stores such values as + 32-bit integers. </li> <li> - When the destination table uses the Parquet file format, the <codeph>CREATE TABLE AS SELECT</codeph> and - <codeph>INSERT ... SELECT</codeph> statements always create at least one data file, even if the - <codeph>SELECT</codeph> part of the statement does not match any rows. You can use such an empty Parquet - data file as a template for subsequent <codeph>CREATE TABLE LIKE PARQUET</codeph> statements. + When the destination table uses the Parquet file format, the <codeph>CREATE TABLE AS + SELECT</codeph> and <codeph>INSERT ... SELECT</codeph> statements always create at least + one data file, even if the <codeph>SELECT</codeph> part of the statement does not match + any rows. You can use such an empty Parquet data file as a template for subsequent + <codeph>CREATE TABLE LIKE PARQUET</codeph> statements. </li> </ul> <p> - For more details about creating Parquet tables, and examples of the <codeph>CREATE TABLE LIKE - PARQUET</codeph> syntax, see <xref href="impala_parquet.xml#parquet"/>. + For more details about creating Parquet tables, and examples of the <codeph>CREATE TABLE + LIKE PARQUET</codeph> syntax, see <xref href="impala_parquet.xml#parquet"/>. </p> <p> @@ -663,25 +1015,28 @@ ERROR: AnalysisException: Partition column name mismatch: year != month </p> <p rev="1.2"> - You can associate arbitrary items of metadata with a table by specifying the <codeph>TBLPROPERTIES</codeph> - 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 <codeph>ALTER TABLE</codeph> statement. You - can observe the table properties for different delimiter and escape characters using the <codeph>DESCRIBE - FORMATTED</codeph> command, and change those settings for an existing table with <codeph>ALTER TABLE ... SET - TBLPROPERTIES</codeph>. + You can associate arbitrary items of metadata with a table by specifying the + <codeph>TBLPROPERTIES</codeph> 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 <codeph>ALTER TABLE</codeph> statement. You can observe the + table properties for different delimiter and escape characters using the <codeph>DESCRIBE + FORMATTED</codeph> command, and change those settings for an existing table with + <codeph>ALTER TABLE ... SET TBLPROPERTIES</codeph>. </p> <p rev="1.2"> - You can also associate SerDes properties with the table by specifying key-value pairs through the - <codeph>WITH SERDEPROPERTIES</codeph> 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. + You can also associate SerDes properties with the table by specifying key-value pairs + through the <codeph>WITH SERDEPROPERTIES</codeph> 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> - Some DDL operations that interact with other Hadoop components require specifying particular values in the - <codeph>SERDEPROPERTIES</codeph> or <codeph>TBLPROPERTIES</codeph> fields, such as creating an Avro table or - an HBase table. (You typically create HBase tables in Hive, because they require additional clauses not + Some DDL operations that interact with other Hadoop components require specifying + particular values in the <codeph>SERDEPROPERTIES</codeph> or + <codeph>TBLPROPERTIES</codeph> 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.) <!-- Haven't got a working example from Lenni, so suppressing this recommendation for now. The Avro schema properties can be specified through either @@ -692,21 +1047,23 @@ ERROR: AnalysisException: Partition column name mismatch: year != month </p> <p> - To see the column definitions and column comments for an existing table, for example before issuing a - <codeph>CREATE TABLE ... LIKE</codeph> or a <codeph>CREATE TABLE ... AS SELECT</codeph> statement, issue the - statement <codeph>DESCRIBE <varname>table_name</varname></codeph>. To see even more detail, such as the - location of data files and the values for clauses such as <codeph>ROW FORMAT</codeph> and <codeph>STORED - AS</codeph>, issue the statement <codeph>DESCRIBE FORMATTED <varname>table_name</varname></codeph>. - <codeph>DESCRIBE FORMATTED</codeph> is also needed to see any overall table comment (as opposed to individual - column comments). + To see the column definitions and column comments for an existing table, for example + before issuing a <codeph>CREATE TABLE ... LIKE</codeph> or a <codeph>CREATE TABLE ... AS + SELECT</codeph> statement, issue the statement <codeph>DESCRIBE + <varname>table_name</varname></codeph>. To see even more detail, such as the location of + data files and the values for clauses such as <codeph>ROW FORMAT</codeph> and + <codeph>STORED AS</codeph>, issue the statement <codeph>DESCRIBE FORMATTED + <varname>table_name</varname></codeph>. <codeph>DESCRIBE FORMATTED</codeph> is also needed + to see any overall table comment (as opposed to individual column comments). </p> <p> After creating a table, your <cmdname>impala-shell</cmdname> session or another - <cmdname>impala-shell</cmdname> 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 <codeph>CREATE TABLE</codeph> statement return only when the table is recognized by all Impala nodes - in the cluster, enable the <codeph>SYNC_DDL</codeph> query option. + <cmdname>impala-shell</cmdname> 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 <codeph>CREATE TABLE</codeph> + statement return only when the table is recognized by all Impala nodes in the cluster, + enable the <codeph>SYNC_DDL</codeph> query option. </p> <p rev="1.4.0"> @@ -714,9 +1071,10 @@ ERROR: AnalysisException: Partition column name mismatch: year != month </p> <p rev="1.4.0"> - If you specify the <codeph>CACHED IN</codeph> 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 <xref href="impala_perf_hdfs_caching.xml#hdfs_caching"/> for details about using the HDFS + If you specify the <codeph>CACHED IN</codeph> 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 + <xref href="impala_perf_hdfs_caching.xml#hdfs_caching"/> for details about using the HDFS caching feature. </p> @@ -729,42 +1087,44 @@ ERROR: AnalysisException: Partition column name mismatch: year != month </p> <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. + 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> - 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: + 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> <li> <p> - If certain columns are often <codeph>NULL</codeph>, specify those columns last. You might produce data - files that omit these trailing columns entirely. Impala automatically fills in the <codeph>NULL</codeph> - values if so. + If certain columns are often <codeph>NULL</codeph>, specify those columns last. You + might produce data files that omit these trailing columns entirely. Impala + automatically fills in the <codeph>NULL</codeph> values if so. </p> </li> <li> <p> - If an unpartitioned table will be used as the source for an <codeph>INSERT ... SELECT</codeph> 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 <codeph>INSERT ... SELECT *</codeph> when - copying data to the partitioned table, rather than specifying each column name individually. + If an unpartitioned table will be used as the source for an <codeph>INSERT ... + SELECT</codeph> 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 <codeph>INSERT ... SELECT *</codeph> when copying + data to the partitioned table, rather than specifying each column name individually. </p> </li> <li> <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 <codeph>SELECT *</codeph> 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. + 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 + <codeph>SELECT *</codeph> 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> @@ -772,34 +1132,37 @@ ERROR: AnalysisException: Partition column name mismatch: year != month <p conref="../shared/impala_common.xml#common/hive_blurb"/> <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 - <codeph>ANALYZE TABLE</codeph> 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 <codeph>COMPUTE STATS</codeph> statement - produces these statistics within Impala, without needing to use Hive at all. + 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 <codeph>ANALYZE TABLE</codeph> 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 <codeph>COMPUTE STATS</codeph> statement produces these + statistics within Impala, without needing to use Hive at all. </p> <p conref="../shared/impala_common.xml#common/hbase_blurb"/> <note> <p> - The Impala <codeph>CREATE TABLE</codeph> statement cannot create an HBase table, because it currently does - not support the <codeph>STORED BY</codeph> clause needed for HBase tables. Create such tables in Hive, then - query them through Impala. For information on using Impala with HBase tables, see - <xref href="impala_hbase.xml#impala_hbase"/>. + The Impala <codeph>CREATE TABLE</codeph> statement cannot create an HBase table, because + it currently does not support the <codeph>STORED BY</codeph> clause needed for HBase + tables. Create such tables in Hive, then query them through Impala. For information on + using Impala with HBase tables, see <xref href="impala_hbase.xml#impala_hbase"/>. </p> </note> <p conref="../shared/impala_common.xml#common/s3_blurb"/> + <p rev="2.2.0"> To create a table where the data resides in the Amazon Simple Storage Service (S3), - specify a <codeph>s3a://</codeph> prefix <codeph>LOCATION</codeph> attribute pointing to the data files in S3. + specify a <codeph>s3a://</codeph> prefix <codeph>LOCATION</codeph> attribute pointing to + the data files in S3. </p> <p rev="2.6.0 CDH-39913 IMPALA-1878"> - In <keyword keyref="impala26_full"/> and higher, you can - use this special <codeph>LOCATION</codeph> syntax - as part of a <codeph>CREATE TABLE AS SELECT</codeph> statement. + In <keyword keyref="impala26_full"/> and higher, you can use this special + <codeph>LOCATION</codeph> syntax as part of a <codeph>CREATE TABLE AS SELECT</codeph> + statement. </p> <p conref="../shared/impala_common.xml#common/s3_ddl"/> @@ -809,29 +1172,32 @@ ERROR: AnalysisException: Partition column name mismatch: year != month <p conref="../shared/impala_common.xml#common/hdfs_blurb"/> <p> - The <codeph>CREATE TABLE</codeph> statement for an internal table creates a directory in HDFS. The - <codeph>CREATE EXTERNAL TABLE</codeph> 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 - <codeph>DESCRIBE FORMATTED <varname>table</varname></codeph> statement. To examine the contents of that HDFS - directory, use an OS command such as <codeph>hdfs dfs -ls hdfs://<varname>path</varname></codeph>, either - from the OS command line or through the <codeph>shell</codeph> or <codeph>!</codeph> commands in - <cmdname>impala-shell</cmdname>. + The <codeph>CREATE TABLE</codeph> statement for an internal table creates a directory in + HDFS. The <codeph>CREATE EXTERNAL TABLE</codeph> 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 <codeph>DESCRIBE FORMATTED + <varname>table</varname></codeph> statement. To examine the contents of that HDFS + directory, use an OS command such as <codeph>hdfs dfs -ls + hdfs://<varname>path</varname></codeph>, either from the OS command line or through the + <codeph>shell</codeph> or <codeph>!</codeph> commands in <cmdname>impala-shell</cmdname>. </p> <p> - The <codeph>CREATE TABLE AS SELECT</codeph> syntax creates data files under the table data directory to hold - any data copied by the <codeph>INSERT</codeph> portion of the statement. (Even if no data is copied, Impala - might create one or more empty data files.) + The <codeph>CREATE TABLE AS SELECT</codeph> syntax creates data files under the table data + directory to hold any data copied by the <codeph>INSERT</codeph> portion of the statement. + (Even if no data is copied, Impala might create one or more empty data files.) </p> <p conref="../shared/impala_common.xml#common/permissions_blurb"/> + <p rev="CDH-19187"> - The user ID that the <cmdname>impalad</cmdname> daemon runs under, - typically the <codeph>impala</codeph> user, must have both execute and write - permission for the database directory where the table is being created. + The user ID that the <cmdname>impalad</cmdname> daemon runs under, typically the + <codeph>impala</codeph> user, must have both execute and write permission for the database + directory where the table is being created. </p> <p conref="../shared/impala_common.xml#common/security_blurb"/> + <p conref="../shared/impala_common.xml#common/redaction_yes"/> <p conref="../shared/impala_common.xml#common/cancel_blurb_maybe"/> @@ -840,11 +1206,17 @@ ERROR: AnalysisException: Partition column name mismatch: year != month <p> <xref href="impala_tables.xml#tables"/>, - <xref href="impala_alter_table.xml#alter_table"/>, <xref href="impala_drop_table.xml#drop_table"/>, - <xref href="impala_partitioning.xml#partitioning"/>, <xref href="impala_tables.xml#internal_tables"/>, - <xref href="impala_tables.xml#external_tables"/>, <xref href="impala_compute_stats.xml#compute_stats"/>, + <xref href="impala_alter_table.xml#alter_table"/>, + <xref href="impala_drop_table.xml#drop_table"/>, + <xref href="impala_partitioning.xml#partitioning"/>, + <xref href="impala_tables.xml#internal_tables"/>, + <xref href="impala_tables.xml#external_tables"/>, + <xref href="impala_compute_stats.xml#compute_stats"/>, <xref href="impala_sync_ddl.xml#sync_ddl"/>, <xref href="impala_show.xml#show_tables"/>, - <xref href="impala_show.xml#show_create_table"/>, <xref href="impala_describe.xml#describe"/> + <xref href="impala_show.xml#show_create_table"/>, + <xref href="impala_describe.xml#describe"/> </p> + </conbody> + </concept>
http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/661921b2/docs/topics/impala_decimal.xml ---------------------------------------------------------------------- diff --git a/docs/topics/impala_decimal.xml b/docs/topics/impala_decimal.xml index 0175911..36b149d 100644 --- a/docs/topics/impala_decimal.xml +++ b/docs/topics/impala_decimal.xml @@ -822,6 +822,9 @@ SELECT CAST(1000.5 AS DECIMAL); <p conref="../shared/impala_common.xml#common/column_stats_constant"/> + <p conref="../shared/impala_common.xml#common/kudu_blurb"/> + <p conref="../shared/impala_common.xml#common/kudu_unsupported_data_type"/> + <p conref="../shared/impala_common.xml#common/related_info"/> <p> http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/661921b2/docs/topics/impala_describe.xml ---------------------------------------------------------------------- diff --git a/docs/topics/impala_describe.xml b/docs/topics/impala_describe.xml index 2548d44..1f7ce9b 100644 --- a/docs/topics/impala_describe.xml +++ b/docs/topics/impala_describe.xml @@ -697,6 +697,91 @@ Returned 27 row(s) in 0.17s</codeblock> in an arbitrary HDFS directory based on its <codeph>LOCATION</codeph> attribute.) </p> + <p rev="kudu" conref="../shared/impala_common.xml#common/kudu_blurb"/> + + <p rev="kudu"> + The information displayed for Kudu tables includes the additional attributes + that are only applicable for Kudu tables: + </p> + <ul rev="kudu"> + <li> + Whether or not the column is part of the primary key. Every Kudu table + has a <codeph>true</codeph> value here for at least one column. There + could be multiple <codeph>true</codeph> values, for tables with + composite primary keys. + </li> + <li> + Whether or not the column is nullable. Specified by the <codeph>NULL</codeph> + or <codeph>NOT NULL</codeph> attributes on the <codeph>CREATE TABLE</codeph> statement. + Columns that are part of the primary key are automatically non-nullable. + </li> + <li> + The default value, if any, for the column. Specified by the <codeph>DEFAULT</codeph> + attribute on the <codeph>CREATE TABLE</codeph> statement. If the default value is + <codeph>NULL</codeph>, that is not indicated in this column. It is implied by + <codeph>nullable</codeph> being true and no other default value specified. + </li> + <li> + The encoding used for values in the column. Specified by the <codeph>ENCODING</codeph> + attribute on the <codeph>CREATE TABLE</codeph> statement. + </li> + <li> + The compression used for values in the column. Specified by the <codeph>COMPRESSION</codeph> + attribute on the <codeph>CREATE TABLE</codeph> statement. + </li> + <li> + The block size (in bytes) used for the underlying Kudu storage layer for the column. + Specified by the <codeph>BLOCK_SIZE</codeph> attribute on the <codeph>CREATE TABLE</codeph> + statement. + </li> + </ul> + + <p rev="kudu"> + The following example shows <codeph>DESCRIBE</codeph> output for a simple Kudu table, with + a single-column primary key and all column attributes left with their default values: + </p> + +<codeblock rev="kudu"> +describe million_rows; ++------+--------+---------+-------------+----------+---------------+---------------+---------------------+------------+ +| name | type | comment | primary_key | nullable | default_value | encoding | compression | block_size | ++------+--------+---------+-------------+----------+---------------+---------------+---------------------+------------+ +| id | string | | true | false | | AUTO_ENCODING | DEFAULT_COMPRESSION | 0 | +| s | string | | false | false | | AUTO_ENCODING | DEFAULT_COMPRESSION | 0 | ++------+--------+---------+-------------+----------+---------------+---------------+---------------------+------------+ +</codeblock> + + <p rev="kudu"> + The following example shows <codeph>DESCRIBE</codeph> output for a Kudu table with a + two-column primary key, and Kudu-specific attributes applied to some columns: + </p> + +<codeblock rev="kudu"> +create table kudu_describe_example +( + c1 int, c2 int, + c3 string, c4 string not null, c5 string default 'n/a', c6 string default '', + c7 bigint not null, c8 bigint null default null, c9 bigint default -1 encoding bit_shuffle, + primary key(c1,c2) +) +partition by hash (c1, c2) partitions 10 stored as kudu; + +describe kudu_describe_example; ++------+--------+---------+-------------+----------+---------------+---------------+---------------------+------------+ +| name | type | comment | primary_key | nullable | default_value | encoding | compression | block_size | ++------+--------+---------+-------------+----------+---------------+---------------+---------------------+------------+ +| c1 | int | | true | false | | AUTO_ENCODING | DEFAULT_COMPRESSION | 0 | +| c2 | int | | true | false | | AUTO_ENCODING | DEFAULT_COMPRESSION | 0 | +| c3 | string | | false | true | | AUTO_ENCODING | DEFAULT_COMPRESSION | 0 | +| c4 | string | | false | false | | AUTO_ENCODING | DEFAULT_COMPRESSION | 0 | +| c5 | string | | false | true | n/a | AUTO_ENCODING | DEFAULT_COMPRESSION | 0 | +| c6 | string | | false | true | | AUTO_ENCODING | DEFAULT_COMPRESSION | 0 | +| c7 | bigint | | false | false | | AUTO_ENCODING | DEFAULT_COMPRESSION | 0 | +| c8 | bigint | | false | true | | AUTO_ENCODING | DEFAULT_COMPRESSION | 0 | +| c9 | bigint | | false | true | -1 | BIT_SHUFFLE | DEFAULT_COMPRESSION | 0 | ++------+--------+---------+-------------+----------+---------------+---------------+---------------------+------------+ +</codeblock> + <p conref="../shared/impala_common.xml#common/related_info"/> <p> http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/661921b2/docs/topics/impala_double.xml ---------------------------------------------------------------------- diff --git a/docs/topics/impala_double.xml b/docs/topics/impala_double.xml index d229fd4..0dfa137 100644 --- a/docs/topics/impala_double.xml +++ b/docs/topics/impala_double.xml @@ -108,6 +108,9 @@ SELECT CAST(1000.5 AS DOUBLE); <p conref="../shared/impala_common.xml#common/float_double_decimal_caveat"/> + <p conref="../shared/impala_common.xml#common/kudu_blurb"/> + <p conref="../shared/impala_common.xml#common/kudu_non_pk_data_type"/> + <p conref="../shared/impala_common.xml#common/related_info"/> <p> http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/661921b2/docs/topics/impala_drop_table.xml ---------------------------------------------------------------------- diff --git a/docs/topics/impala_drop_table.xml b/docs/topics/impala_drop_table.xml index b89368b..318393c 100644 --- a/docs/topics/impala_drop_table.xml +++ b/docs/topics/impala_drop_table.xml @@ -155,6 +155,15 @@ drop table temporary.trivial;</codeblock> no particular permissions are needed for the associated HDFS files or directories. </p> + <p rev="kudu" conref="../shared/impala_common.xml#common/kudu_blurb"/> + <p rev="kudu"> + Kudu tables can be managed or external, the same as with HDFS-based + tables. For a managed table, the underlying Kudu table and its data + are removed by <codeph>DROP TABLE</codeph>. For an external table, + the underlying Kudu table and its data remain after a + <codeph>DROP TABLE</codeph>. + </p> + <p conref="../shared/impala_common.xml#common/related_info"/> <p> http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/661921b2/docs/topics/impala_explain.xml ---------------------------------------------------------------------- diff --git a/docs/topics/impala_explain.xml b/docs/topics/impala_explain.xml index 4c41fdb..7c4d444 100644 --- a/docs/topics/impala_explain.xml +++ b/docs/topics/impala_explain.xml @@ -234,6 +234,42 @@ EXPLAIN_LEVEL set to extended if the source table is partitioned.) </p> + <p rev="kudu" conref="../shared/impala_common.xml#common/kudu_blurb"/> + <p> + The <codeph>EXPLAIN</codeph> statement displays equivalent plan + information for queries against Kudu tables as for queries + against HDFS-based tables. + </p> + + <p> + To see which predicates Impala can <q>push down</q> to Kudu for + efficient evaluation, without transmitting unnecessary rows back + to Impala, look for the <codeph>kudu predicates</codeph> item in + the scan phase of the query. The label <codeph>kudu predicates</codeph> + indicates a condition that can be evaluated efficiently on the Kudu + side. The label <codeph>predicates</codeph> in a <codeph>SCAN KUDU</codeph> + node indicates a condition that is evaluated by Impala. + For example, in a table with primary key column <codeph>X</codeph> + and non-primary key column <codeph>Y</codeph>, you can see that + some operators in the <codeph>WHERE</codeph> clause are evaluated + immediately by Kudu and others are evaluated later by Impala: +<codeblock> +EXPLAIN SELECT x,y from kudu_table WHERE + x = 1 AND x NOT IN (2,3) AND y = 1 + AND x IS NOT NULL AND x > 0; ++---------------- +| Explain String ++---------------- +... +| 00:SCAN KUDU [jrussell.hash_only] +| predicates: x IS NOT NULL, x NOT IN (2, 3) +| kudu predicates: x = 1, x > 0, y = 1 +</codeblock> + Only binary predicates and <codeph>IN</codeph> predicates containing + literal values that exactly match the types in the Kudu table, and do not + require any casting, can be pushed to Kudu. + </p> + <p conref="../shared/impala_common.xml#common/related_info"/> <p> <xref href="impala_select.xml#select"/>, http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/661921b2/docs/topics/impala_float.xml ---------------------------------------------------------------------- diff --git a/docs/topics/impala_float.xml b/docs/topics/impala_float.xml index 00d4f4b..12f87b0 100644 --- a/docs/topics/impala_float.xml +++ b/docs/topics/impala_float.xml @@ -102,6 +102,9 @@ SELECT CAST(1000.5 AS FLOAT); <p conref="../shared/impala_common.xml#common/float_double_decimal_caveat"/> + <p conref="../shared/impala_common.xml#common/kudu_blurb"/> + <p conref="../shared/impala_common.xml#common/kudu_non_pk_data_type"/> + <p conref="../shared/impala_common.xml#common/related_info"/> <p> http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/661921b2/docs/topics/impala_grant.xml ---------------------------------------------------------------------- diff --git a/docs/topics/impala_grant.xml b/docs/topics/impala_grant.xml index cdba5e0..f1a0672 100644 --- a/docs/topics/impala_grant.xml +++ b/docs/topics/impala_grant.xml @@ -129,6 +129,9 @@ object_type ::= TABLE | DATABASE | SERVER | URI <p conref="../shared/impala_common.xml#common/permissions_blurb_no"/> + <p conref="../shared/impala_common.xml#common/kudu_blurb"/> + <p conref="../shared/impala_common.xml#common/kudu_sentry_limitations"/> + <p conref="../shared/impala_common.xml#common/related_info"/> <p> http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/661921b2/docs/topics/impala_invalidate_metadata.xml ---------------------------------------------------------------------- diff --git a/docs/topics/impala_invalidate_metadata.xml b/docs/topics/impala_invalidate_metadata.xml index 97dfb09..b316b0d 100644 --- a/docs/topics/impala_invalidate_metadata.xml +++ b/docs/topics/impala_invalidate_metadata.xml @@ -241,6 +241,11 @@ ERROR: AnalysisException: Database does not exist: new_db_from_hive <p conref="../shared/impala_common.xml#common/s3_metadata"/> <p conref="../shared/impala_common.xml#common/cancel_blurb_no"/> + + <p rev="kudu" conref="../shared/impala_common.xml#common/kudu_blurb"/> + <p conref="../shared/impala_common.xml#common/kudu_metadata_intro"/> + <p conref="../shared/impala_common.xml#common/kudu_metadata_details"/> + <p conref="../shared/impala_common.xml#common/related_info"/> <p> <xref href="impala_hadoop.xml#intro_metastore"/>,
