Esta página está escrita para cualquier persona que intente usar LookML para crear una exploración en Looker. Será más fácil entender la página si eres experto en SQL, en especial si entiendes la diferencia entre uniones internas y externas. Para obtener una explicación concisa de cómo difieren las uniones internas y externas, consulta este artículo de w3schools sobre las 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 siempre son ciertas, lo que libera a los analistas de negocios para que creen consultas sobre la marcha que siempre son correctas, incluso si el equipo de datos nunca previó su necesidad. El impulsor principal de esta capacidad es el algoritmo de agregaciones simétricas, que resuelve un problema general de la industria con las uniones de SQL. Sin embargo, hay dos pasos que se deben realizar de forma correcta 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 todas las combinaciones.
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 podría hacer con ella. Lo único que debe ser verdadero es que la columna (o conjunto de columnas concatenadas) que eliges 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 indicarle 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 las agregaciones simétricas con cautela.
El proceso para determinar el valor correcto es ligeramente diferente entre las uniones internas y externas.
Uniones 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 | $50.00 | 1 |
| 3 | $75.00 | 2 |
| 4 | $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 | Amalia | 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á 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 | Amalia | Earhart | 2 |
| 2 | $50.00 | 1 | 1 | Amalia | Earhart | 2 |
| 3 | $75.00 | 2 | 2 | Bessie | Coleman | 2 |
| 4 | $35.00 | 3 | 3 | Wilbur | Wright | 4 |
Aquí, 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 de la izquierda), un solo ID de cliente se representa muchas veces (en este caso, este 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.
Puedes seguir estos pasos para determinar el valor correcto de relación de manera programática verificando las claves primarias:
- Comienza por escribir
many_to_manycomo la relación. Siempre que tus claves primarias sean correctas, esto generará resultados precisos, ya que Looker siempre activará el algoritmo de agregación simétrica y aplicará la precisión. Sin embargo, dado que el algoritmo complica las consultas y suma tiempo de ejecución, conviene intentar cambiar uno o ambos lados aoneen lugar demany. - Observa el campo o los campos que están en la cláusula
sql_onde la tabla de la 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, 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 tu tabla derecha en la cláusula
sql_on. Si el campo o los campos forman la clave primaria de la tabla derecha, puedes cambiar el lado derecho aone.
La práctica recomendada es escribir la frase sql_on que comience con la tabla izquierda, que se representa en el lado izquierdo del signo igual, y la tabla derecha, que está en el lado derecho. El orden de las condiciones del parámetro sql_on no importa, a menos que 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 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 ayudarte a discernir, de un vistazo, a qué tabla existente de la exploración estás uniendo a 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 las predeterminadas 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 cual no es recomendable).
En una combinación externa completa, los registros nulos se pueden agregar a cualquiera de las tablas si a la 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 | $50.00 | 1 |
| 3 | $75.00 | 2 |
| 4 | $35.00 | 3 |
Para el ejemplo, supongamos que también tienes la siguiente tabla customers:
| customer_id | first_name | last_name | visitas |
|---|---|---|---|
| 1 | Amalia | Earhart | 2 |
| 2 | Bessie | Coleman | 2 |
| 3 | Wilbur | Wright | 4 |
| 4 | Charles | Yeager | 3 |
Una vez unidas estas tablas, la tabla unida se puede representar de la siguiente manera:
| order_id | cantidad | customer_id | customer_id | first_name | last_name | visitas |
|---|---|---|---|---|---|---|
| 1 | $25.00 | 1 | 1 | Amalia | Earhart | 2 |
| 2 | $50.00 | 1 | 1 | Amalia | Earhart | 2 |
| 3 | $75.00 | 2 | 2 | Bessie | Coleman | 2 |
| 4 | $35.00 | 3 | 3 | Wilbur | Wright | 4 |
| null | null | null | 4 | Charles | Yeager | 3 |
Al igual que en una unión interna, la relación entre las claves primarias de las tablas es many_to_one. Sin embargo, el registro nulo agregado también fuerza la necesidad de agregaciones simétricas en la tabla 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 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 seguirá siendo many_to_one. Este es el valor predeterminado de Looker porque se supone que la tabla base define el análisis. En este caso, analizarás pedidos, no clientes. Si la mesa de los 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 del cliente. 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 una distribución descendente afecta una consulta, aunque la vista afectada no esté en la unión que en realidad 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. Cuando se necesitan agregaciones simétricas y de fanout para calcular una medición, Looker verifica la exclusividad de la clave primaria aprovechada. Si no es único, aparecerá un error en el tiempo de ejecución de la consulta (sin embargo, no habrá ningún error del Validador de LookML para esto).
Además, si Looker no puede controlar un fanout (generalmente porque no se indica una clave primaria), no aparecerán mediciones en la exploración desde esa vista. Para corregir esto, simplemente designa un campo como la clave primaria para permitir que tus mediciones ingresen a Explorar.
Aspectos para tener en cuenta
Compatibilidad de 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 de esta página, se indica que, para determinar el valor de relación correcto, debes observar el campo o los campos que están en la cláusula sql_on de la tabla izquierda: “Si el campo o los campos forman la clave primaria de la tabla izquierda, puedes cambiar el lado izquierdo del parámetro relationship a one. De lo contrario, debe permanecer como many". Esto ocurre, 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 al formular la 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 instrucción en el párrafo anterior siempre se mantenga como verdadera para la columna que designes. Si es así, trátala como tal y toma nota de su propiedad especial en el archivo de vista para que otras personas la consulten en el futuro (completa con el vínculo 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 clave primaria, pero no hará lo mismo para otros campos. Simplemente no invocará el algoritmo de agregaciones simétricas.