I comes from the relational database world, so I am a little confused about that it seems like Spark SQL doest not leverage any schema constraint like Primary key, Foreign key and Not NULL constraint.
I believe that is why I always observe that some redundant notnull checking in the query plan.
So my question is that is there any possible way to let Spark SQL leverage these constraints if the data source ever defines them?
Thanks.
SPARK is not like an RDBMS. It is a processing engine, but the question has merit for sure.
IBM, see https://developer.ibm.com/blogs/performance-enhancements-in-spark-using-referential-integrity-constraints/, has done work in this area enhancing Spark, Catalyst and Optimize rules, see https://issues.apache.org/jira/browse/SPARK-19842.
To quote:
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.
So, as Catalyst improves more of this support will be added, but not just yet.
