[jira] [Resolved] (IMPALA-4166) Introduce SORT BY clause in CREATE TABLE statement

["Lars Volker (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/IMPALA-4166?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Lars Volker resolved IMPALA-4166.
---------------------------------
       Resolution: Fixed
    Fix Version/s: Impala 2.9.0

IMPALA-4166: Add SORT BY sql clause

This change adds support for adding SORT BY (...) clauses to CREATE
TABLE and ALTER TABLE statements. Examples are:

CREATE TABLE t (i INT, j INT, k INT) PARTITIONED BY (l INT) SORT BY (i, j);
CREATE TABLE t SORT BY (int_col,id) LIKE u;
CREATE TABLE t LIKE PARQUET '/foo' SORT BY (id,zip);

ALTER TABLE t SORT BY (int_col,id);
ALTER TABLE t SORT BY ();

Sort columns can only be specified for Hdfs tables and effectiveness may
vary based on storage type; for example TEXT tables will not see
improved compression. The SORT BY clause must not contain clustering
columns. The columns in the SORT BY clause are stored in the
'sort.columns' table property and will result in an additional SORT node
being added to the plan before the final table sink. Specifying sort
columns also enables clustering during inserts, so the SORT node will
contain all partitioning columns first, followed by the sort columns. We
do this because sort columns add a SORT node to the plan and adding the
clustering columns to the SORT node is cheap.

Sort columns supersede the sortby() hint, which we will remove in a
subsequent change (IMPALA-5144). Until then, it is possible to specify
sort columns using both ways at the same time and the column lists
will be concatenated.

Change-Id: I08834f38a941786ab45a4381c2732d929a934f75
Reviewed-on: http://gerrit.cloudera.org:8080/6495
Reviewed-by: Lars Volker <lv...@cloudera.com>
Tested-by: Impala Public Jenkins

> Introduce SORT BY clause in CREATE TABLE statement
> --------------------------------------------------
>
>                 Key: IMPALA-4166
>                 URL: https://issues.apache.org/jira/browse/IMPALA-4166
>             Project: IMPALA
>          Issue Type: New Feature
>          Components: Catalog
>    Affects Versions: Impala 2.2, Impala 2.3.0, Impala 2.5.0, Impala 2.4.0, Impala 2.6.0, Impala 2.7.0
>            Reporter: Alexander Behm
>            Assignee: Lars Volker
>              Labels: ramp-up, usability
>             Fix For: Impala 2.9.0
>
>
> This issue is intended as a usability improvement for IMPALA-4163 where the SORT BY columns can be specified directly in the table definition like this:
> {code}
> CREATE TABLE t (day INT, hour INT)
> PARTITIONED BY (year INT, month INT)
> SORT BY (day, hour);
> {code}
> The above table creation has the effect that all inserts into the table have an implicit "sortby(day,hour)" plan hint applied. See IMPALA-4163 for details on the hint.
> Just like with the "sortby" hint the SORT BY clause can only contain non-partition columns for HDFS tables and non-primary key columns for Kudu tables.
> This has the following benefits:
> * Users will not have to remember to put the sortby hint in all insert statements.
> * The SORT BY columns are a physical design choice, so it makes sense to store them as part of the table metadata.
> * This is a convenience feature. It has the same effect as the sortby() hint for INSERT statements, but doesn't require the user to remember to include the hint for every INSERT statement.
> Challenges:
> * The Hive Metastore has no SORT BY concept, so we'll need to store the information in the generic TBLPROPERTIES map.
> * No other engines (Hive, Spark) will understand this table property. That means that data written by those engines will require an explicit sorting hint (as far as that's available).



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)