Esta página se creó para cualquier persona que quiera usar LookML para crear una exploración en Looker. La página será más fácil de entender si eres competente en SQL, especialmente si entiendes 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 de SQL.
Looker tiene la capacidad de ser un potente motor de SQL para tu empresa. El modelado abstracto en LookML permite a los equipos de datos y TI crear reglas generales que son siempre ciertas, lo que libera a los analistas de negocios para que creen consultas siempre correctas, incluso si el equipo de datos nunca anticipó una 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 realizar dos acciones correctamente para aprovechar el algoritmo: las claves primarias deben ser precisas en todas las vistas que contienen una medición (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 básicamente lo mismo que comprender qué es la tabla y qué se puede hacer con ella. Lo único que debe ser verdadero 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 unió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.
Uniones internas
Como 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 |
En este caso, la relación many_to_one se refiere a la cantidad de veces que se representa un valor del campo de unión (customer_id) en cada tabla. En la tabla orders (la tabla de la izquierda), se representa un solo ID de cliente muchas veces (en este caso, este es el cliente con el ID 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 todas las filas de la tabla customers, la relación sería many_to_many.
Puedes seguir estos pasos para determinar el valor de relación correcto de manera programática verificando las claves primarias:
- Para comenzar, escribe
many_to_manycomo la relación. Siempre y cuando tus claves primarias sean correctas, esto siempre producirá resultados precisos porque Looker siempre activará el algoritmo de agregación simétrica y aplicará la exactitud. Sin embargo, como el algoritmo complica las consultas y agrega tiempo de ejecución, te recomendamos que intentes cambiar una o ambas partes aoneen lugar demany. - Observa el campo o los campos que se encuentran en tu cláusula
sql_onde la tabla izquierda. Si el campo o los campos forman la clave primaria de la tabla 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 Aspectos que debes tener en cuenta más adelante en esta página). - A continuación, observa el campo o los campos que representan la tabla derecha 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. El orden de las condiciones en el parámetro sql_on no importa, 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 ayudar a distinguir de un vistazo la tabla existente en la que deseas unir la tabla nueva.
Uniones externas
Para las uniones externas, también debes tener en cuenta que puede ocurrir una distribución cuando se agregan registros nulos durante la unió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 medición de type: count. Si esto se hace de manera incorrecta, los registros nulos se contarán (lo cual es no deseado).
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, la fila nula no se habría agregado y el registro de cliente adicional se habría descartado. En ese caso, la relación sería many_to_one. Esta es la configuración predeterminada de Looker porque se supone que la tabla base define el análisis. En este caso, se analizan 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 este ejemplo, eso significaría que una tabla se uniría a la tabla de clientes. En estas situaciones, es mejor mirar solo la unión individual que se escribe cuando se evalúa el parámetro relationship. Looker comprenderá cuándo un fanout descendente afecta una consulta aunque la vista afectada no esté en la unión que realmente creó la fanout.
¿Cómo me ayuda Looker?
Looker tiene mecanismos 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 no hay forma de que Looker maneje una distribución (por lo general, porque no se indica una clave primaria), no aparecerán mediciones en Explorar desde esa vista. Para corregir esto, simplemente designa un campo como la clave primaria para permitir que tus mediciones se incluyan en 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 agregaciones simétricas 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 principal de la tabla de la izquierda, puedes cambiar el lado izquierdo del parámetro relationship a one. De lo contrario, debe permanecer como many". Esto se aplica a menos que tu tabla contenga varias columnas que no tengan registros repetidos. En este caso, puedes tratar cualquier columna como si fuera una clave primaria cuando formulas tu relación, incluso si no es la columna designada como primary_key: yes.
Puede ser útil asegurarte de que exista algún tipo de regla de software que garantice que la instrucción del párrafo anterior siempre sea verdadera para la columna que designes. Si es así, trátalo como tal 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 verdad de la unicidad implícita cuando se designe un campo como la clave primaria, pero no hará lo mismo con otros campos. Simplemente no invocará el algoritmo de agregaciones simétricas.