[SPARK-19842] Informational Referential Integrity Constraints Support in Spark

• Type: Improvement
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 2.2.0
• Component/s: SQL
• Assignee: Unassigned
• Reporter: Ioana Delaney
• Created: 07/Mar/17 00:26
• Updated: 16/Apr/21 12:23

Description

Informational Referential Integrity Constraints Support in Spark

This work proposes support for informational primary key and foreign key (referential integrity) constraints in Spark. The main purpose is to open up an area of query optimization techniques that rely on referential integrity constraints semantics.

An informational or statistical constraint is a constraint such as a unique, primary key, foreign key, or check constraint, that can be used by Spark to improve query performance. Informational constraints are not enforced by the Spark SQL engine; rather, they are used by Catalyst to optimize the query processing. They provide semantics information that allows Catalyst to rewrite queries to eliminate joins, push down aggregates, remove unnecessary Distinct operations, and perform a number of other optimizations. Informational constraints are primarily targeted to applications that load and analyze data that originated from a data warehouse. For such applications, the conditions for a given constraint are known to be true, so the constraint does not need to be enforced during data load operations.

The attached document covers constraint definition, metastore storage, constraint validation, and maintenance. The document shows many examples of query performance improvements that utilize referential integrity constraints and can be implemented in Spark.

Link to the google doc: InformationalRIConstraints

Issue Links

• duplicates: SPARK-29584 NOT NULL is not supported in Spark
• is related to: SPARK-29702 Resolve group-by columns with integrity constraints
• is related to: SPARK-27953 Save default constraint with Column into table properties when create Hive table
• is related to: SPARK-29119 DEFAULT option is not supported in Spark
• is related to: IMPALA-3531 Implement deferrable and optionally enforced PK/FK constraints
• is related to: SPARK-15302 Implement FK/PK "rely novalidate" constraints for better CBO

Sub-Tasks

• Add ALTER TABLE ADD CONSTRANT DDL to support defining primary key and foreign keys: In Progress
• ALTER TABLE table statements such as RENAME and CHANGE columns should raise error if there are any dependent constraints.: Open
• DROP TABLE should automatically drop any dependent referential constraints or raise error.: Open
• Add support for VALIDATE option to ALTER TABLE ADD CONSTRAINT command: Open
• Add DDL support to modify constraint state for a given constraint: Open
• Hints for fact tables and unique columns: Resolved
• [Performance] Inner Join Elimination based on Informational RI constraints: In Progress
• [Performance] Existential Subquery to Inner Join: Open
• [Performance] Group By Push Down through Join: Open
• [Performance] Distinct elimination: Open
• [Performance] Redundant join elimination: Open
• [Performance] Star schema detection improvements: Open