Declara y optimiza con restricciones UNIQUE en Databricks

Los equipos de datos a menudo mantienen garantías de unicidad en sus pipelines, pero sin una manera flexible de representarlas todas en el esquema de la tabla, los usuarios y las herramientas no pueden descubrirlas y el optimizador de consultas no puede aprovecharlas de forma segura. Las entidades de negocio a menudo tienen más de un valor, o combinación de valores, que se sabe que son únicos: una tabla users podría usar user_id como su identificador principal de filas, mientras que su modelo de datos también registra garantías de unicidad separadas para username, una dirección de correo electrónico y (workspace_id, external_user_id).

Databricks ya admite restricciones PRIMARY KEY y FOREIGN KEY: una clave principal identifica las filas de una tabla y las claves externas pueden hacer referencia a ella para describir relaciones. Sin embargo, una tabla solo puede tener una clave principal y sus columnas no pueden aceptar valores nulos. Las restricciones UNIQUE, ahora disponibles en Vista previa pública para tablas Delta de Unity Catalog en Databricks SQL y Databricks Runtime 18.2 y versiones posteriores, agregan una forma de registrar las otras garantías de unicidad. Una tabla puede declarar múltiples restricciones UNIQUE, sus columnas pueden aceptar valores nulos y las claves externas pueden hacer referencia a ellas. Después de validar estas restricciones y marcarlas con RELY, el optimizador de consultas también puede usarlas para eliminar uniones y agregaciones innecesarias.

Cuando una clave principal no puede cubrir todas las columnas únicas

PRIMARY KEY y UNIQUE registran la unicidad, pero cumplen funciones diferentes en el esquema:

Incluye las garantías de unicidad de tu pipeline en el esquema

La siguiente tabla tiene una clave principal y dos restricciones UNIQUE:

Las dos restricciones únicas ilustran diferentes tipos de unicidad. users_external_uq se aplica a una combinación de columnas: cada par (workspace_id, external_user_id) debe ser único, aunque cualquiera de los valores pueda aparecer en otros pares. users_optional_id_uq se aplica a una columna que acepta valores nulos. Las siguientes filas muestran qué valores satisfacen esa garantía:

Registra la garantía sin pagar un costo en cada escritura

Al igual que las restricciones de claves principales y externas en Databricks, las restricciones UNIQUE son informativas y no se aplican de forma forzosa cuando se escriben datos. Declarar UNIQUE (optional_identifier), por ejemplo, no hace que una escritura que contenga valores duplicados no nulos falle.

Esta separación permite que el esquema registre una garantía de unicidad mantenida por un pipeline de ingesta, un sistema ascendente u otro proceso de calidad de datos. La garantía también se convierte en metadatos de la tabla que los usuarios, las herramientas y las aplicaciones pueden inspeccionar.

Hacer referencia a una restricción UNIQUE desde una clave externa

Una clave externa puede hacer referencia a una restricción UNIQUE además de a una clave principal. Por ejemplo, se puede hacer referencia a la restricción compuesta UNIQUE declarada anteriormente de la siguiente manera:

Las columnas principales a las que se hace referencia identifican a qué restricción única apunta la clave externa. Las columnas secundarias y principales corresponden por posición: workspace_id se asigna a workspace_id, y source_user_id se asigna a external_user_id. Sus nombres no necesitan coincidir, pero sus tipos y posiciones deben corresponderse. Las columnas principales (parent) REFERENCES deben aparecer en el mismo orden que las columnas en la definición de la restricción UNIQUE; de lo contrario, la restricción seguirá siendo válida, pero la eliminación de uniones no se activará de forma silenciosa.

Para hacer referencia a una restricción UNIQUE de una sola columna, nombra explícitamente su columna principal, por ejemplo, REFERENCES users (username). Si se omite la lista de columnas principales, REFERENCES users apunta en su lugar a la clave principal de la tabla.

Optimizar consultas con RELY

De forma predeterminada, una restricción única se crea con NORELY. Registra la semántica del esquema prevista, pero Databricks no la utiliza para reescrituras de consultas dependientes de la corrección. La siguiente sentencia crea una restricción con este valor predeterminado:

Una vez que hayas verificado los datos y te hayas asegurado de que tus pipelines seguirán manteniendo la garantía, puedes eliminar y volver a crear la restricción con RELY:

En este ejemplo, CASCADE también elimina cualquier clave externa que haga referencia a users_username_uq, y estas deben volver a crearse después de volver a agregar la restricción única. Sin CASCADE, DROP CONSTRAINT utiliza el comportamiento predeterminado RESTRICT y se rechaza cuando existen claves externas dependientes.

Una clave externa que hace referencia a la restricción única también se puede declarar con RELY:

RELY se configura de forma independiente en cada restricción. No valida los datos existentes ni aplica la restricción en futuras escrituras. Permite que Databricks asuma que la restricción es válida al optimizar consultas. Si los datos infringen una restricción en la que se confía, una reescritura basada en dicha restricción puede producir resultados incorrectos. Por lo tanto, debes usar RELY solo para garantías que se validen y mantengan continuamente.

Con RELY, las restricciones de unicidad pueden proporcionar la información necesaria para las reescrituras de combinaciones y agregaciones elegibles. La elegibilidad también depende de otras garantías requeridas por la consulta, incluida la nulabilidad, como ilustran los ejemplos siguientes. Este soporte de optimización está disponible en Photon-enabled compute. El blog sobre claves primarias y externas describe optimizaciones de combinaciones y agregaciones basadas en restricciones que las restricciones de UNIQUE también pueden habilitar. Desde que se publicó esa entrada, el soporte para RELY está disponible para claves externas y ahora también para restricciones de UNIQUE.

Eliminar combinaciones usando restricciones UNIQUE y de clave externa

Algunos paneles construidos sobre los mismos datos necesitan atributos de users, por lo que los equipos suelen crear una vista compartida que siempre combina user_events con users. Un panel que solo necesita información de user_events aún puede heredar esa combinación de la vista. Por lo tanto, su SQL generado puede parecerse a la consulta siguiente, aunque no seleccione ninguna columna de users:

Aunque la consulta no devuelve ninguna columna de users, el optimizador normalmente no puede eliminar la combinación. Una combinación interna (inner join) podría eliminar un evento si no existe un usuario coincidente, o duplicarlo si varios usuarios tienen el mismo nombre de usuario.

Las restricciones en las que se confía descartarán ambas posibilidades. La restricción UNIQUE garantiza como máximo un usuario coincidente, mientras que la clave externa garantiza que existe una coincidencia. Como user_events.username también es NOT NULL, cada evento tiene exactamente una coincidencia. Por lo tanto, la combinación no puede cambiar el resultado, y el optimizador puede leer event_id directamente desde user_events.

El blog anterior sobre claves primarias y externas demuestra el caso relacionado de LEFT OUTER JOIN, que solo necesita la garantía de como máximo una coincidencia. Este ejemplo de INNER JOIN también necesita la clave externa para establecer que existe una coincidencia.

Con las restricciones de NORELY, el plan optimizado conserva la combinación:

Cuando ambas restricciones usan RELY, el optimizador puede saber que cada evento tiene exactamente un usuario coincidente y eliminar la combinación:

En este ejemplo, user_events.username es NOT NULL, pero la optimización también se aplica cuando admite valores nulos (nullable): Databricks eliminará la combinación y añadirá un filtro IS NOT NULL en user_events.username para preservar el manejo de nulos de la combinación interna.

Evaluamos esta optimización en el conjunto de datos TPC-DS SF10000, que representa un factor de escala de 10 TB. En la tabla de 28 800 millones de filas store_sales unida a la tabla de 65 millones de filas customer, declaramos customer.c_customer_sk como UNIQUE y definimos una clave externa desde store_sales.ss_customer_sk. Con RELY en ambas restricciones, Databricks eliminó la combinación, eliminó más del 99,99 % del mezclado (shuffle) y redujo el tiempo medio de ejecución del motor de 23,6 segundos a 3,0 segundos, una aceleración de 7,9x.

Omitir DISTINCT cuando los valores ya son únicos

Las herramientas de BI y los generadores de consultas a menudo añaden DISTINCT por precaución, ya que las combinaciones pueden producir filas duplicadas. Cuando los valores seleccionados ya son únicos, DISTINCT no elimina nada, pero aún puede requerir una agregación y un mezclado a menos que el optimizador pueda demostrar que la operación es innecesaria. Una restricción de confianza UNIQUE puede proporcionar esa prueba.

Considere una tabla user_identifiers con estas dos restricciones:

• UNIQUE (user_id) RELY, donde user_id es NOT NULL
• UNIQUE (optional_identifier) RELY, donde optional_identifier admite valores nulos

La tabla contiene los siguientes datos, que satisfacen ambas restricciones:

Para user_id, el optimizador puede usar juntas las garantías de unicidad y no nulidad:

El plan optimizado no contiene ninguna agregación:

La agregación desaparece porque la restricción en la que se confía establece que cada user_id ya es distinto.

La consulta equivalente sobre optional_identifier muestra por qué el optimizador también tiene en cuenta la nulabilidad:

El plan optimizado conserva la agregación:

Ambas restricciones usan RELY, pero la restricción que admite nulos permite las dos filas de NULL mostradas arriba. El comando DISTINCT de SQL debe fusionar esas filas en una, por lo que la agregación sigue siendo necesaria.

Las restricciones de UNIQUE que admiten nulos aún pueden habilitar la optimización cuando la consulta proporciona la garantía de no nulidad faltante:

El optimizador puede eliminar la agregación:

El filtro elimina los valores que pueden repetirse bajo la semántica de UNIQUE con valores nulos. Se garantiza que los valores restantes son distintos, por lo que Databricks puede volver a evitar la agregación.

Evaluamos esta optimización en el mismo conjunto de datos TPC-DS SF10000, que representa un factor de escala de 10 TB. En la tabla de 28 800 millones de filas store_sales, declaramos (ss_item_sk, ss_ticket_number) como una restricción UNIQUE compuesta y ejecutamos una consulta DISTINCT sobre esas columnas. Con RELY, Databricks eliminó la agregación de DISTINCT, eliminó más del 99,99 % del mezclado y redujo el tiempo medio de ejecución del motor de 33,7 segundos a 2,8 segundos, una aceleración de 12,0x.

Valide sus datos antes de que el optimizador use una restricción

Encontrar filas duplicadas y huérfanas

Antes de usar RELY, compruebe que los datos cumplan con la garantía. Para una restricción UNIQUE que admita valores nulos, excluya las filas que contengan NULL porque UNIQUE permite que esas filas se repitan. Esta consulta encuentra identificadores no nulos duplicados:

Para una restricción UNIQUE compuesta, agrupa por cada columna restringida y añade una condición IS NOT NULL para cada columna que admita valores nulos. También puedes encontrar filas secundarias sin una fila principal coincidente usando un left anti join:

Estas comprobaciones validan los datos actuales. Tu canalización de ingesta o calidad de datos debe seguir manteniendo las garantías después de habilitar RELY.

Confirma las restricciones declaradas

Tras declarar o volver a crear una restricción, puedes confirmar su definición de varias maneras:

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

Consulta Constraints on Databricks y la cláusula CONSTRAINT para ver la sintaxis completa de creación, alteración, inspección y eliminación.

Implementa UNIQUE y RELY de forma incremental

Puedes adoptar UNIQUE de forma incremental:

1. Declara las garantías de unicidad existentes con el valor predeterminado NORELY.
2. Haz referencia a claves únicas desde claves externas donde la relación forme parte del esquema.
3. Valida y mantén continuamente la unicidad y la integridad referencial en tus canalizaciones de datos.
4. Usa RELY en la restricción UNIQUE solo después de verificar la unicidad, y en la clave externa solo después de comprobar que cada valor secundario aplicable tiene una fila principal coincidente.
5. Compara los planes con EXPLAIN para confirmar las reescrituras elegibles.

Conclusión

Las restricciones UNIQUE permiten que una tabla registre múltiples garantías de unicidad además de su única PRIMARY KEY que no admite valores nulos, y las columnas restringidas pueden admitir valores nulos. Las claves externas pueden hacer referencia a una restricción UNIQUE así como a una clave principal. Las restricciones UNIQUE son informativas de manera predeterminada; tras la validación, RELY permite que Databricks las use para optimizar las uniones elegibles y las consultas DISTINCT.

(Esta entrada del blog ha sido traducida utilizando herramientas basadas en inteligencia artificial) Publicación original