[IMPALA-2521] Introduce CLUSTERED plan hint for insert statements

• Type: New Feature
• Status: Resolved
• Priority: Critical
• Resolution: Fixed
• Affects Version/s: Impala 2.2, Impala 2.3.0, Impala 2.5.0, Impala 2.4.0, Impala 2.6.0, Impala 2.7.0
• Fix Version/s: Impala 2.8.0
• Component/s: Frontend
• Labels: performance ramp-up
• Epic Link: Improve the reliability and effectiveness of ETL
• Target Version: Impala 2.8.0
• Assignee: Lars Volker
• Reporter: Mostafa Mokhtar
• Created: 09/Oct/15 18:58
• Updated: 14/Mar/18 23:40
• Resolved: 26/Oct/16 07:48

Description

Add a new "clustered" plan hint for insert statements.

Example:

CREATE TABLE dst (...) PARTITIONED BY (year INT, month INT);
INSERT INTO dst PARTITION(year,month) /*+ clustered */ SELECT * FROM src;

The hint specifies that the data fed into the table sink should be clustered based on the partition columns.
For now, we'll use a local sort to achieve clustering, and the plan should look like this:
SCAN -> SORT (year,month) -> TABLE SINK

Syntax and behavior

INSERT INTO dst PARTITION(year,month) /*+ clustered */ SELECT * FROM src;

• We will not support the legacy-hint style with brackets

  [clustered]

• The hint should be obeyed if the target table is a partitioned HDFS or Kudu table. Otherwise, it should be ignored with a warning.
• For Kudu tables, the sorting should be done on the primary keys.

Making clustered the default plan

Eventually, we want to make the "clustered" plan the default because it is more robust with large inserts into many partitions. With that in mind, we should also add a corresponding "noclustered" hint that removes the sort. Of course, that hint will not do anything until we change the default behavior, but we should add it nevertheless to have the hints complete.

Issue Links

• blocks: IMPALA-2523 Make HdfsTableSink aware of clustered input
• relates to: IMPALA-4167 Support insert plan hints for CREATE TABLE AS SELECT
• relates to: IMPALA-3742 INSERTs into Kudu tables should partition and sort
• relates to: IMPALA-4191 standardize query hint syntax