Esta página está escrita para cualquier persona que intente usar LookML para crear una exploración en Looker. La página será más fácil de entender si tienes conocimientos de SQL, en especial si comprendes la diferencia entre las uniones internas y externas. Para obtener una explicación concisa de las diferencias entre las uniones internas y externas, consulta este artículo de w3schools sobre Uniones SQL.
Looker puede ser un motor de SQL potente para tu empresa. El modelado abstracto en LookML permite que los equipos de datos y TI creen reglas generales que siempre sean verdaderas, lo que permite que los analistas de negocios creen consultas reales que siempre sean correctas, incluso si el equipo de datos nunca anticipó la necesidad de ellas. El factor clave de esta función es el algoritmo de agregados simétricos, que resuelve un problema de toda la industria con las uniones de SQL. Sin embargo, se deben hacer dos cosas correctamente para aprovechar el algoritmo: las claves primarias deben ser precisas en cada vista que contenga una medida (por lo general, todas) y los parámetros relationship deben ser correctos en cada unión.
Claves primarias
En muchos sentidos, comprender la clave primaria de una tabla es esencialmente lo mismo que comprender qué es la tabla y qué se puede hacer con ella. Lo único que debe ser cierto es que la columna (o el conjunto de columnas concatenadas) que elijas como clave primaria no debe tener valores repetidos.
El parámetro relationship:
Ahora que verificaste tus claves primarias, puedes determinar el valor correcto para el parámetro relationship de la combinación. El propósito del parámetro relationship es indicarle a Looker si debe invocar agregaciones simétricas cuando la unión se escribe en una consulta en SQL. Un posible enfoque aquí sería decirle a Looker que siempre los invoque, lo que siempre produciría resultados precisos. Sin embargo, esto tiene un costo de rendimiento, por lo que es mejor usar los agregados simétricos con prudencia.
El proceso para determinar el valor correcto es ligeramente diferente entre las uniones internas y externas.
Combinaciones internas
A modo de ejemplo, supongamos que tienes una tabla de pedidos con una clave primaria de order_id:
| order_id | cantidad | customer_id |
|---|---|---|
| 1 | $25.00 | 1 |
| 2 | USD 50.00 | 1 |
| 3 | USD 75.00 | 2 |
| 4 | USD 35.00 | 3 |
Supongamos que también tienes una tabla de clientes con una clave primaria de customer_id:
| customer_id | first_name | last_name | visitas |
|---|---|---|---|
| 1 | Amelia | Earhart | 2 |
| 2 | Bessie | Coleman | 2 |
| 3 | Wilbur | Wright | 4 |
Puedes unir estas tablas en el campo customer_id, que está presente en ambas tablas. Esta unión se representaría en LookML de la siguiente manera:
explore: orders {
join: customers {
type: inner
sql_on: ${orders.customer_id} = ${customers.customer_id} ;;
relationship: many_to_one
}
}
El resultado de esta unión de LookML se puede representar como una sola tabla unida, de la siguiente manera:
| order_id | cantidad | customer_id | customer_id | first_name | last_name | visitas |
|---|---|---|---|---|---|---|
| 1 | $25.00 | 1 | 1 | Amelia | Earhart | 2 |
| 2 | USD 50.00 | 1 | 1 | Amelia | Earhart | 2 |
| 3 | USD 75.00 | 2 | 2 | Bessie | Coleman | 2 |
| 4 | USD 35.00 | 3 | 3 | Wilbur | Wright | 4 |
La relación many_to_one aquí hace referencia a la cantidad de veces que un valor del campo de unión (customer_id) se representa en cada tabla. En la tabla orders (la tabla de la izquierda), un solo ID de cliente se representa varias veces (en este caso, es el cliente con el ID de 1, que está presente en varias filas).
En la tabla customers (la tabla de la derecha), cada ID de cliente solo se representa una vez, ya que customer_id es la clave primaria de esa tabla. Por lo tanto, los registros de la tabla orders podrían tener muchas coincidencias para un solo valor en la tabla customers. Si customer_id no fuera único en cada fila de la tabla customers, la relación sería many_to_many.
Para determinar el valor de relación correcto de forma programática, puedes seguir estos pasos y verificar las claves primarias:
- Comienza por escribir
many_to_manycomo la relación. Siempre que tus claves primarias sean correctas, esto siempre generará resultados precisos, ya que Looker siempre activará el algoritmo de agregación simétrica y aplicará la precisión. Sin embargo, como el algoritmo complica las consultas y agrega tiempo de ejecución, es conveniente intentar cambiar uno o ambos lados aoneen lugar demany. - Observa el campo o los campos que se encuentran en la cláusula
sql_onde la tabla de la izquierda. Si el campo o los campos forman la clave primaria de la tabla de la izquierda, puedes cambiar el lado izquierdo del parámetrorelationshipaone. De lo contrario, por lo general, debe permanecer comomany. (Para obtener información sobre un caso especial, consulta la sección Consideraciones más adelante en esta página). - A continuación, observa el campo o los campos que representan la tabla correcta en la cláusula
sql_on. Si el campo o los campos forman la clave primaria de la tabla de la derecha, puedes cambiar el lado derecho aone.
Se recomienda escribir la frase sql_on comenzando con la tabla de la izquierda, que se representa a la izquierda del signo igual, y la tabla de la derecha, que se encuentra a la derecha. No importa el orden de las condiciones en el parámetro sql_on, a menos que el orden sea relevante para el dialecto SQL de tu base de datos. Aunque el parámetro sql_on no requiere que ordenes los campos de esta manera, organizar las condiciones de sql_on de modo que los lados izquierdo y derecho del signo igual coincidan con la forma en que se lee el parámetro relationship de izquierda a derecha puede ayudarte a determinar la relación. Ordenar los campos de esta manera también puede facilitar la identificación, de un vistazo, de la tabla existente de Explorar a la que unes la tabla nueva.
Combinaciones externas
En el caso de las combinaciones externas, también debes tener en cuenta que puede ocurrir una expansión cuando se agregan registros nulos durante la combinación. Esto es muy importante porque las uniones externas izquierdas son la opción predeterminada en Looker. Si bien los registros nulos no afectan las sumas ni los promedios, sí afectan la forma en que Looker ejecuta una medida de type: count. Si se hace de forma incorrecta, se contarán los registros nulos (lo que no es deseable).
En una unión externa completa, se pueden agregar registros nulos a cualquiera de las tablas si a su clave de unión le faltan valores que existen en la otra tabla. Esto se ilustra en el siguiente ejemplo, que incluye una tabla orders:
| order_id | cantidad | customer_id |
|---|---|---|
| 1 | $25.00 | 1 |
| 2 | USD 50.00 | 1 |
| 3 | USD 75.00 | 2 |
| 4 | USD 35.00 | 3 |
Para el ejemplo, supongamos que también tienes la siguiente tabla customers:
| customer_id | first_name | last_name | visitas |
|---|---|---|---|
| 1 | Amelia | Earhart | 2 |
| 2 | Bessie | Coleman | 2 |
| 3 | Wilbur | Wright | 4 |
| 4 | Charles | Yeager | 3 |
Una vez que se hayan unido estas tablas, la tabla resultante se puede representar de la siguiente manera:
| order_id | cantidad | customer_id | customer_id | first_name | last_name | visitas |
|---|---|---|---|---|---|---|
| 1 | $25.00 | 1 | 1 | Amelia | Earhart | 2 |
| 2 | USD 50.00 | 1 | 1 | Amelia | Earhart | 2 |
| 3 | USD 75.00 | 2 | 2 | Bessie | Coleman | 2 |
| 4 | USD 35.00 | 3 | 3 | Wilbur | Wright | 4 |
| null | null | null | 4 | Charles | Yeager | 3 |
Al igual que en una combinación interna, la relación entre las claves primarias de las tablas es many_to_one. Sin embargo, el registro nulo agregado también requiere agregaciones simétricas en la tabla de la izquierda. Por lo tanto, debes cambiar el parámetro relationship a many_to_many, ya que realizar esta unión interrumpe los recuentos en la tabla de la izquierda.
Si este ejemplo hubiera sido una unión externa izquierda, no se habría agregado la fila nula y se habría eliminado el registro de cliente adicional. En ese caso, la relación seguiría siendo many_to_one. Esta es la opción predeterminada de Looker, ya que se supone que la tabla base define el análisis. En este caso, analizas los pedidos, no los clientes. Si la tabla de clientes estuviera a la izquierda, la situación sería diferente.
Uniones de varios niveles
En algunas Exploraciones, la tabla base se une a una o más vistas que, a su vez, deben unirse a una o más vistas adicionales. En el ejemplo que se muestra aquí, eso significaría que se uniría una tabla a la tabla de clientes. En estas situaciones, lo mejor es solo mirar la unión individual que se escribe cuando se evalúa el parámetro relationship. Looker comprenderá cuándo una distribución descendente afecta una consulta, aunque la vista afectada no esté en la unión que realmente creó la distribución.
¿Cómo me ayuda Looker?
Existen mecanismos en Looker para garantizar que el valor de la relación sea correcto. Una es una verificación de unicidad de la clave primaria. Cada vez que hay un fanout y se necesitan agregaciones simétricas para calcular una medida, Looker verifica la unicidad de la clave primaria aprovechada. Si no es único, aparecerá un error en el tiempo de ejecución de la consulta (sin embargo, no hay un error de validador de LookML para esto).
Además, si Looker no puede controlar un fanout (por lo general, porque no se indica una clave primaria), no aparecerán medidas en la función Explorar desde esa vista. Para corregir esto, simplemente designa un campo como clave primaria para permitir que tus medidas ingresen a Explorar.
Aspectos para tener en cuenta
Compatibilidad con dialectos para agregaciones simétricas
Looker puede conectarse con algunos dialectos que no admiten agregaciones simétricas. Puedes ver una lista de dialectos y su compatibilidad con los agregados simétricos en la página de documentación de symmetric_aggregates.
Caso especial
En la sección Unión interna que aparece más arriba en esta página, se indica que, para determinar el valor de relación correcto, debes observar el campo o los campos que se encuentran en la cláusula sql_on de la tabla de la izquierda: "Si el campo o los campos forman la clave primaria de la tabla de la izquierda, puedes cambiar el lado izquierdo del parámetro relationship a one. De lo contrario, por lo general, debe permanecer como many". Esto es cierto, a menos que tu tabla contenga varias columnas que no tengan registros repetidos. En este caso, puedes tratar cualquier columna de este tipo como si fuera una clave primaria cuando formules tu relación, incluso si no es la columna designada como primary_key: yes.
Puede ser útil asegurarse de que exista algún tipo de regla de software que garantice que la sentencia del párrafo anterior siempre sea verdadera para la columna que designes. Si es así, hazlo y toma nota de su propiedad especial en el archivo de vista para que otros puedan consultarla en el futuro (completa con el vínculo de SQL Runner para probarlo). Sin embargo, ten en cuenta que Looker confirmará la unicidad implícita cuando un campo se designe como clave primaria, pero no hará lo mismo con otros campos. Simplemente, no invocará el algoritmo de agregaciones simétricas.