Declare and optimize with UNIQUE constraints on Databricks

Knowledge groups typically keep uniqueness ensures of their pipelines, however with out a versatile option to signify all of them within the desk schema, customers and instruments can not uncover them and the question optimizer can not safely leverage them. Enterprise entities typically have a couple of worth, or mixture of values, identified to be distinctive: a customers desk would possibly use user_id as its main row identifier whereas its information mannequin additionally data separate uniqueness ensures for username, an e mail deal with, and (workspace_id, external_user_id).

Databricks already helps PRIMARY KEY and FOREIGN KEY constraints: a main key identifies a desk’s rows, and overseas keys can reference it to explain relationships. A desk can have just one main key, nevertheless, and its columns should be non-nullable. UNIQUE constraints, now obtainable in Public Preview for Unity Catalog Delta tables in Databricks SQL and Databricks Runtime 18.2 and above, add a option to report the opposite uniqueness ensures. A desk can declare a number of UNIQUE constraints, their columns might be nullable, and overseas keys can reference them. After you validate these constraints and mark them with RELY, the question optimizer may also use them to eradicate pointless joins and aggregations.

When one main key cannot cowl each distinctive column

PRIMARY KEY and UNIQUE each report uniqueness, however they serve totally different schema roles:

Put your pipeline’s uniqueness ensures within the schema

The next desk has a main key and two UNIQUE constraints:

The 2 distinctive constraints illustrate totally different sorts of uniqueness. users_external_uq applies to a column mixture: every (workspace_id, external_user_id) pair should be distinctive despite the fact that both worth could seem in different pairs. users_optional_id_uq applies to a nullable column. The next rows present which values fulfill that assure:

Report the assure with out paying a value on each write

Like main and overseas key constraints on Databricks, UNIQUE constraints are informational and usually are not enforced when information is written. Declaring UNIQUE (optional_identifier), for instance, doesn’t trigger a write containing duplicate non-null values to fail.

This separation lets the schema report a uniqueness assure maintained by an ingestion pipeline, an upstream system, or one other data-quality course of. The assure additionally turns into desk metadata that customers, instruments, and functions can examine.

Reference a UNIQUE constraint from a overseas key

A overseas key can reference a UNIQUE constraint along with a main key. For instance, the composite UNIQUE constraint declared above might be referenced as follows:

The referenced mum or dad columns determine which distinctive constraint the overseas key targets. Little one and mum or dad columns correspond by place: workspace_id maps to workspace_id, and source_user_id maps to external_user_id. Their names don’t have to match, however their sorts and positions should correspond. The REFERENCES (mum or dad) columns should be listed in the identical order because the columns within the UNIQUE constraint’s definition; in any other case the constraint remains to be legitimate however be a part of elimination silently will not fireplace.

To reference a single-column UNIQUE constraint, explicitly identify its mum or dad column, for instance, REFERENCES customers (username). If the mum or dad column listing is omitted, REFERENCES customers targets the desk’s main key as an alternative.

Optimize queries with RELY

By default, a novel constraint is created with NORELY. It data the meant schema semantics, however Databricks doesn’t use it for correctness-dependent question rewrites. The next assertion creates a constraint with this default:

After you’ve verified the info and ensured that your pipelines will proceed to take care of the assure, you may drop and re-create the constraint with RELY:

On this instance, CASCADE additionally drops any overseas keys that reference users_username_uq, and so they should be re-created after the distinctive constraint is added again. With out CASCADE, DROP CONSTRAINT makes use of the default RESTRICT habits and is rejected when dependent overseas keys exist.

A overseas key that references the distinctive constraint may also be declared with RELY:

RELY is ready independently on every constraint. It doesn’t validate present information or implement the constraint on future writes. It permits Databricks to imagine that the constraint is legitimate when optimizing queries. If the info violates a relied-upon constraint, a rewrite based mostly on that constraint can produce incorrect outcomes. You need to due to this fact use RELY just for ensures which might be validated and repeatedly maintained.

With RELY, distinctive constraints can present the knowledge wanted for eligible be a part of and aggregation rewrites. Eligibility additionally will depend on different ensures required by the question, together with nullability, because the examples beneath illustrate. This optimization help is offered on Photon-enabled compute. The main and overseas key weblog describes constraint-based be a part of and aggregation optimizations that UNIQUE constraints may also allow. Since that submit was printed, RELY help has develop into obtainable for overseas keys and now for UNIQUE constraints.

Get rid of joins utilizing UNIQUE and overseas key constraints

Some dashboards constructed on the identical information want attributes from customers, so groups typically create a shared view that at all times joins user_events with customers. A dashboard that wants solely info from user_events should still inherit that be a part of from the view. Its generated SQL can due to this fact appear to be the question beneath, despite the fact that it doesn’t choose any columns from customers:

Though the question returns no columns from customers, the optimizer can not usually take away the be a part of. An internal be a part of might take away an occasion if no matching consumer exists, or duplicate it if a number of customers have the identical username.

The relied-upon constraints rule out each potentialities. The UNIQUE constraint ensures at most one matching consumer, whereas the overseas key ensures {that a} match exists. As a result of user_events.username can also be NOT NULL, each occasion has precisely one match. The be a part of due to this fact can not change the end result, and the optimizer can learn event_id immediately from user_events.

The sooner main and overseas key weblog demonstrates the associated LEFT OUTER JOIN case, which wants solely the at-most-one assure. This INNER JOIN instance additionally wants the overseas key to determine {that a} match exists.

With NORELY constraints, the optimized plan retains the be a part of:

When each constraints use RELY, the optimizer can know that each occasion has precisely one matching consumer and take away the be a part of:

On this instance, user_events.username is NOT NULL, however the optimization additionally applies when it’s nullable: Databricks will take away the be a part of and add an IS NOT NULL filter on the user_events.username to protect the internal be a part of’s dealing with of nulls.

We benchmarked this optimization on the TPC-DS SF10000 dataset, which represents a ten TB scale issue. On the 28.8-billion-row store_sales desk joined to the 65-million-row buyer desk, we declared buyer.c_customer_sk as UNIQUE and outlined a overseas key from store_sales.ss_customer_sk. With RELY on each constraints, Databricks eradicated the be a part of, eliminated greater than 99.99% of shuffle, and lowered median engine execution time from 23.6 seconds to three.0 seconds – a 7.9x speedup.

Skip DISTINCT when values are already distinctive

BI instruments and question builders typically add DISTINCT defensively as a result of joins could produce duplicate rows. When the chosen values are already distinctive, DISTINCT removes nothing, however it might probably nonetheless require an aggregation and shuffle except the optimizer can show that the operation is pointless. A relied-upon UNIQUE constraint can present that proof.

Take into account a user_identifiers desk with these two constraints:

• UNIQUE (user_id) RELY, the place user_id is NOT NULL
• UNIQUE (optional_identifier) RELY, the place optional_identifier is nullable

The desk comprises the next information, which satisfies each constraints:

For user_id, the optimizer can use the distinctiveness and non-null ensures collectively:

The optimized plan comprises no aggregation:

The aggregation is gone as a result of the relied-upon constraint establishes that each user_id is already distinct.

The equal question over optional_identifier reveals why the optimizer additionally considers nullability:

The optimized plan retains the aggregation:

Each constraints use RELY, however the nullable constraint permits the 2 NULL rows proven above. SQL DISTINCT should collapse these rows into one, so the aggregation remains to be mandatory.

Nullable UNIQUE constraints can nonetheless allow the optimization when the question provides the lacking non-null assure:

The optimizer can take away the aggregation:

The filter removes the values which will repeat underneath nullable UNIQUE semantics. The remaining values are assured to be distinct, so Databricks can once more keep away from the aggregation.

We benchmarked this optimization on the identical TPC-DS SF10000 dataset, which represents a ten TB scale issue. On the 28.8-billion-row store_sales desk, we declared (ss_item_sk, ss_ticket_number) as a composite UNIQUE constraint and ran a DISTINCT question over these columns. With RELY, Databricks eradicated the DISTINCT aggregation, eliminated greater than 99.99% of shuffle, and lowered median engine execution time from 33.7 seconds to 2.8 seconds – a 12.0x speedup.

Validate your information earlier than optimizer makes use of a constraint

Discover duplicate and orphaned rows

Earlier than utilizing RELY, examine that the info satisfies the assure. For a nullable UNIQUE constraint, exclude rows containing NULL as a result of UNIQUE permits these rows to repeat. This question finds duplicate non-null identifiers:

For a composite UNIQUE constraint, group by each constrained column and add an IS NOT NULL situation for every nullable column. You too can discover youngster rows with no matching mum or dad by utilizing a left anti be a part of:

These checks validate the present information. Your ingestion or data-quality pipeline should proceed to take care of the ensures after you allow RELY.

Verify the declared constraints

After declaring or re-creating a constraint, you may affirm its definition in a number of methods:

• SHOW CREATE TABLE
• DESCRIBE TABLE EXTENDED
• Unity Catalog INFORMATION_SCHEMA relations:
  • TABLE_CONSTRAINTS
  • KEY_COLUMN_USAGE
  • CONSTRAINT_TABLE_USAGE
  • CONSTRAINT_COLUMN_USAGE
  • REFERENTIAL_CONSTRAINTS

See Constraints on Databricks and the CONSTRAINT clause for full create, alter, examine, and drop syntax.

Roll out UNIQUE and RELY incrementally

You’ll be able to undertake UNIQUE incrementally:

1. Declare present uniqueness ensures with the default NORELY.
2. Reference distinctive keys from overseas keys the place the connection is a part of the schema.
3. Validate and repeatedly keep uniqueness and referential integrity in your information pipelines.
4. Use RELY on the UNIQUE constraint solely after verifying uniqueness, and on the overseas key solely after verifying that each relevant youngster worth has an identical mum or dad row.
5. Evaluate plans with EXPLAIN to substantiate eligible rewrites.

Conclusion

UNIQUE constraints let a desk report a number of uniqueness ensures along with its single, non-nullable PRIMARY KEY, and the constrained columns might be nullable. Overseas keys can reference a UNIQUE constraint in addition to a main key. UNIQUE constraints are informational by default; after validation, RELY lets Databricks use them to optimize eligible joins and DISTINCT queries.