[HIVE-26389] ALTER TABLE CASCADE is slow for tables with many partitions

• Type: Improvement
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 4.0.0-alpha-2
• Component/s: Metastore, Query Planning
• Target Version/s: 4.0.0-alpha-2
• Assignee: Stamatis Zampetakis
• Reporter: Stamatis Zampetakis
• Created: 13/Jul/22 18:02
• Updated: 13/Jul/22 18:02

Description

Consider the following simplified scenario with a table having two partitions.

CREATE TABLE student (fname string, lname string) PARTITIONED BY (department string);
INSERT INTO student VALUES ('Alex','Dumas', 'Computer Science');
INSERT INTO student VALUES ('Victor','Hugo', 'Physics');

Altering a column of this table and propagating the changes to the partitions (using the CASCADE) syntax is slow.

ALTER TABLE student CHANGE lname lastname STRING CASCADE;

The seemingly simple ALTER statement outlined above triggers roughly 136 SQL queries in the underlying DBMS of the metastore (see native_sql_queries.txt).

We can observe that some of these queries are recurring and appear as many times as there are partitions in the table (see per_partition_sql_queries.txt).

As the number of partitions grows so does the number of queries so if we manage to reduce the number of queries send per partition or make them more efficient this will have a positive impact on performance.