Las restricciones de clave principal y clave externa están disponibles con carácter general y ahora permiten realizar consultas más rápidas

Databricks se complace en anunciar la disponibilidad general (GA) de las restricciones de clave primaria (PK) y clave externa (FK), a partir de Databricks Runtime 15.2 y Databricks SQL 2024.30. Esta versión sigue a una versión preliminar pública muy exitosa, adoptada por cientos de clientes activos semanales, y representa además un hito importante en la mejora de la integridad de los datos y la administración de datos relacionales dentro de Lakehouse.

Además, Databricks ahora puede usar estas restricciones para optimizar las consultas y eliminar operaciones innecesarias del plan de consulta, lo que ofrece un rendimiento mucho más rápido.

Restricciones de clave primaria y clave externa

Las claves primarias (PK) y las claves externas (FK) son elementos esenciales en las bases de datos relacionales, que actúan como bloques de construcción fundamentales para el modelado de datos. Proporcionan información sobre las relaciones de datos en el esquema a los usuarios, las herramientas y las aplicaciones; y habilitan optimizaciones que aprovechan las restricciones para acelerar las consultas. Las claves primarias y externas ahora están disponibles de forma general para sus tablas Delta Lake alojadas en Unity Catalog.

Lenguaje SQL

Puede definir restricciones al crear una tabla:

En el ejemplo anterior, definimos una restricción de clave primaria en la columna UserID. Databricks también admite restricciones en grupos de columnas.

También puede modificar las tablas Delta existentes para agregar o quitar restricciones:

Aquí creamos la clave primaria denominada products_pk en la columna no anulable ProductID en una tabla existente. Para ejecutar correctamente esta operación, debe ser el propietario de la tabla. Tenga en cuenta que los nombres de las restricciones deben ser únicos dentro del esquema.
El comando posterior elimina la clave primaria especificando el nombre.

El mismo proceso se aplica a las claves externas. La siguiente tabla define dos claves externas en el momento de la creación de la tabla:

Consulte la documentación sobre las instrucciones CREATE TABLE y ALTER TABLE para obtener más detalles sobre la sintaxis y las operaciones relacionadas con las restricciones.

Las restricciones de clave primaria y clave externa no se aplican en el motor de Databricks, pero pueden ser útiles para indicar una relación de integridad de datos que se pretende que sea verdadera. En cambio, Databricks puede aplicar las restricciones de clave primaria ascendentes como parte de la canalización de ingesta. Consulte Calidad de datos administrada con Delta Live Tables para obtener más información sobre las restricciones aplicadas. Databricks también admite las restricciones NOT NULL y CHECK aplicadas (consulte la documentación de restricciones para obtener más información).

Ecosistema de partners

Las herramientas y aplicaciones, como la versión más reciente de Tableau y PowerBI, pueden importar y utilizar automáticamente las relaciones de clave primaria y clave externa de Databricks a través de conectores JDBC y ODBC.

Ver las restricciones

Hay varias formas de ver las restricciones de clave primaria y clave externa definidas en la tabla. También puede usar simplemente comandos SQL para ver la información de las restricciones con el comando DESCRIBE TABLE EXTENDED:

Explorador de catálogos y diagrama de relación de entidad

También puede ver la información de las restricciones a través del Explorador de catálogos:

Cada columna de clave primaria y clave externa tiene un pequeño icono de clave junto a su nombre.

Y puede visualizar la información de clave primaria y clave externa y las relaciones entre tablas con el Diagrama de relación de entidad en el Explorador de catálogos. A continuación, se muestra un ejemplo de una tabla purchases que hace referencia a dos tablas, users y products:

INFORMATION SCHEMA

Las siguientes tablas de INFORMATION_SCHEMA también proporcionan información de restricciones:

• TABLE_CONSTRAINTS: Describe los metadatos de todas las restricciones de clave primaria y clave externa dentro del catálogo.
• KEY_COLUMN_USAGE: Enumera las columnas de las restricciones de clave primaria o clave externa dentro del catálogo.
• CONSTRAINT_TABLE_USAGE: Describe las restricciones que hacen referencia a las tablas en el catálogo.
• CONSTRAINT_COLUMN_USAGE: Describe las restricciones que hacen referencia a las columnas en el catálogo.
• REFERENTIAL_CONSTRAINTS: Describe las restricciones referenciales (clave externa) definidas en el catálogo.

Use la opción RELY para habilitar las optimizaciones

Si sabe que la restricción de clave primaria es válida (por ejemplo, porque su canalización de datos o trabajo ETL la aplica), puede habilitar las optimizaciones basadas en la restricción especificándola con la opción RELY, como:

El uso de la opción RELY permite a Databricks optimizar las consultas de formas que dependen de la validez de la restricción, porque está garantizando que se mantenga la integridad de los datos. Tenga cuidado aquí porque si una restricción está marcada como RELY pero los datos infringen la restricción, sus consultas pueden devolver resultados incorrectos.

Cuando no especifica la opción RELY para una restricción, el valor predeterminado es NORELY, en cuyo caso las restricciones aún se pueden usar con fines informativos o estadísticos, pero las consultas no dependerán de ellas para ejecutarse correctamente.

La opción RELY y las optimizaciones que la utilizan están disponibles actualmente para las claves primarias, y también estarán disponibles pronto para las claves externas.

Puede modificar la clave primaria de una tabla para cambiar si es RELY o NORELY mediante ALTER TABLE, por ejemplo:

Acelere sus consultas eliminando agregaciones innecesarias

Una optimización simple que podemos hacer con las restricciones de clave primaria RELY es eliminar los agregados innecesarios. Por ejemplo, en una consulta que aplica una operación distinta sobre una tabla con una clave primaria que usa RELY:

Podemos eliminar la operación DISTINCT innecesaria:

Como puede ver, esta consulta se basa en la validez de la restricción de clave primaria RELY: si hay identificadores de cliente duplicados en la tabla de clientes, la consulta transformada devolverá resultados duplicados incorrectos. Usted es responsable de aplicar la validez de la restricción si establece la opción RELY.

Si la clave primaria es NORELY (el valor predeterminado), el optimizador no eliminará la operación DISTINCT de la consulta. Entonces, puede ejecutarse más lento, pero siempre devuelve resultados correctos, incluso si hay duplicados. Si la clave primaria es RELY, Databricks puede eliminar la operación DISTINCT, lo que puede acelerar enormemente la consulta, aproximadamente 2 veces para el ejemplo anterior.

Acelere sus consultas eliminando combinaciones innecesarias

Otra optimización muy útil que podemos realizar con las claves primarias RELY es eliminar las combinaciones innecesarias. Si una consulta une una tabla a la que no se hace referencia en ningún otro lugar excepto en la condición de unión, el optimizador puede determinar que la unión es innecesaria y eliminar la unión del plan de consulta.

Para dar un ejemplo, digamos que tenemos una consulta que une dos tablas, store_sales y customer, unidas en la clave primaria de la tabla de clientes PRIMARY KEY (c_customer_sk) RELY.

Si no tuviéramos la clave primaria, cada fila de store_sales podría coincidir potencialmente con varias filas en customer, y tendríamos que ejecutar la unión para calcular el valor SUM correcto. Pero debido a que la tabla customer está unida en su clave primaria, sabemos que la unión generará una fila para cada fila de store_sales.

Por lo tanto, la consulta solo necesita la columna ss_quantity de la tabla de hechos store_sales. Por lo tanto, el optimizador de consultas puede eliminar por completo la unión de la consulta, transformándola en:

Esto se ejecuta mucho más rápido al evitar toda la unión: en este ejemplo, observamos que la optimización acelera la consulta de 1,5 minutos a 6 segundos.. ¡Y los beneficios pueden ser aún mayores cuando la unión involucra muchas tablas que se pueden eliminar!

Puede preguntar, ¿por qué alguien ejecutaría una consulta como esta? ¡En realidad, es mucho más común de lo que podría pensar! Una razón común es que los usuarios construyen vistas que unen varias tablas, como unir muchas tablas de hechos y dimensiones. Escriben consultas sobre estas vistas que a menudo usan columnas de solo algunas de las tablas, no de todas, por lo que el optimizador puede eliminar las uniones contra las tablas que no son necesarias en cada consulta. Este patrón también es común en muchas herramientas de inteligencia empresarial (BI), que a menudo generan consultas que unen muchas tablas en un esquema, incluso cuando una consulta solo usa columnas de algunas de las tablas.

Conclusión

Desde su versión preliminar pública, más de 2600 clientes de Databricks han utilizado restricciones de clave primaria y clave externa. Hoy, nos complace anunciar la disponibilidad general de esta característica, que marca una nueva etapa en nuestro compromiso de mejorar la administración y la integridad de los datos en Databricks.

Además, Databricks ahora aprovecha las restricciones de clave con la opción RELY para optimizar las consultas, por ejemplo, eliminando agregados y combinaciones innecesarias, lo que resulta en un rendimiento de consulta mucho más rápido.

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