Esta página está dirigida a 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, sobre todo si sabes la diferencia entre las combinaciones internas y externas. Para ver una explicación concisa de las diferencias entre las combinaciones internas y externas, consulta este artículo de w3schools sobre combinaciones SQL.
Looker puede ser un potente motor SQL para tu empresa. El modelado abstracto en LookML permite a los equipos de datos y de TI crear reglas generales que siempre son verdaderas, lo que permite a los analistas de negocio crear consultas en el entorno de producción que siempre son correctas, aunque el equipo de datos nunca haya previsto la necesidad de ellas. El principal factor que permite esta función es el algoritmo de agregaciones simétricas, que resuelve un problema generalizado del sector con las uniones SQL. Sin embargo, para aprovechar el algoritmo, deben hacerse dos cosas correctamente: las claves principales deben ser precisas en todas las vistas que contengan una medida (normalmente, todas) y los parámetros relationship deben ser correctos en todas las combinaciones.
Claves principales
En muchos aspectos, entender la clave principal de una tabla es esencialmente lo mismo que entender qué es la tabla y qué se puede hacer con ella. Lo único que debe cumplirse es que la columna (o el conjunto de columnas concatenadas) que elijas como clave principal no tenga valores repetidos.
Parámetro relationship
Ahora que ha verificado sus claves principales, puede determinar el valor correcto del parámetro relationship de la unión. El parámetro relationship indica a Looker si debe invocar agregaciones simétricas cuando la unión se escribe en una consulta de SQL. Una posible solución sería indicar a Looker que siempre los invoque, lo que siempre produciría resultados precisos. Sin embargo, esto tiene un coste en términos 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 combinaciones internas y externas.
Uniones internas
Por ejemplo, supongamos que tiene una tabla de pedidos con la clave principal order_id:
| order_id | amount | customer_id |
|---|---|---|
| 1 | 25,00 USD | 1 |
| 2 | 50,00 $ | 1 |
| 3 | 75,00 $ | 2 |
| 4 | 35,00 USD | 3 |
Supongamos que también tienes una tabla de clientes con la clave principal customer_id:
| customer_id | first_name | last_name | visitas |
|---|---|---|---|
| 1 | Amelia | Earhart | 2 |
| 2 | Bessie | Coleman | 2 |
| 3 | Wilbur | Wright | 4 |
Puede combinar estas tablas en el campo customer_id, que está presente en ambas tablas. Esta combinació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, como se muestra a continuación:
| order_id | amount | customer_id | customer_id | first_name | last_name | visitas |
|---|---|---|---|---|---|---|
| 1 | 25,00 USD | 1 | 1 | Amelia | Earhart | 2 |
| 2 | 50,00 $ | 1 | 1 | Amelia | Earhart | 2 |
| 3 | 75,00 $ | 2 | 2 | Bessie | Coleman | 2 |
| 4 | 35,00 USD | 3 | 3 | Wilbur | Wright | 4 |
La many_to_onerelacióncustomer_id hace referencia al número 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), un mismo ID de cliente se representa muchas veces (en este caso, se trata del 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 principal de esa tabla. Por lo tanto, los registros de la tabla orders pueden 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 mediante programación comprobando las claves principales:
- Empieza escribiendo
many_to_manycomo relación. Siempre que las claves primarias sean correctas, se obtendrán 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 añade tiempo de ejecución, es recomendable intentar cambiar uno o ambos lados aoneen lugar demany. - Consulta el campo o los campos que hay en la cláusula
sql_onde la tabla de la izquierda. Si los campos forman la clave principal de la tabla de la izquierda, puedes cambiar el lado izquierdo del parámetrorelationshipporone. Si no es así, normalmente debe permanecermany. 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, consulta el campo o los campos que representan la tabla de la derecha en la cláusula
sql_on. Si el campo o los campos forman la clave principal de la tabla de la derecha, puedes cambiar el lado derecho aone.
Te recomendamos que escribas la frase sql_on empezando por la tabla de la izquierda, que se representa en el lado izquierdo del signo igual, y la tabla de la derecha, que se encuentra 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 no es necesario que ordenes los campos de esta forma en el parámetro sql_on, si colocas las condiciones de sql_on de forma 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, te resultará más fácil determinar la relación. Ordenar los campos de esta forma también puede ayudarte a discernir de un vistazo a qué tabla de Explorar se une la nueva tabla.
Combinaciones externas
En el caso de las combinaciones externas, también debes tener en cuenta que puede producirse un fanout cuando se añaden registros nulos durante la combinación. Esto es especialmente importante porque las combinaciones externas izquierdas son el valor predeterminado en Looker. Aunque los registros nulos no afectan a las sumas ni a las medias, sí influyen en la forma en que Looker calcula una medida de type: count. Si se hace de forma incorrecta, se contabilizarán los registros nulos (lo cual no es deseable).
En una unión externa completa, se pueden añadir registros nulos a cualquiera de las tablas si su clave de unión no tiene valores que sí existen en la otra tabla. Esto se ilustra en el siguiente ejemplo, que incluye una tabla orders:
| order_id | amount | customer_id |
|---|---|---|
| 1 | 25,00 USD | 1 |
| 2 | 50,00 $ | 1 |
| 3 | 75,00 $ | 2 |
| 4 | 35,00 USD | 3 |
En este ejemplo, supongamos que también tiene 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 han combinado estas tablas, la tabla combinada se puede representar de la siguiente manera:
| order_id | amount | customer_id | customer_id | first_name | last_name | visitas |
|---|---|---|---|---|---|---|
| 1 | 25,00 USD | 1 | 1 | Amelia | Earhart | 2 |
| 2 | 50,00 $ | 1 | 1 | Amelia | Earhart | 2 |
| 3 | 75,00 $ | 2 | 2 | Bessie | Coleman | 2 |
| 4 | 35,00 USD | 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 principales de las tablas es many_to_one. Sin embargo, el registro nulo añadido obliga a que también haya agregaciones simétricas en la tabla de la izquierda. Por lo tanto, debe cambiar el parámetro relationship por many_to_many, ya que al realizar esta combinación se alteran los recuentos de la tabla de la izquierda.
Si este ejemplo hubiera sido una combinación externa izquierda, no se habría añadido 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. Es el valor predeterminado de Looker porque se presupone que la tabla base define el análisis. En este caso, estás analizando pedidos, no clientes. Si la tabla de clientes estuviera a la izquierda, la situación sería diferente.
Combinaciones multinivel
En algunas Exploraciones, la tabla base se combina con una o varias vistas que, a su vez, deben combinarse con una o varias vistas adicionales. En este ejemplo, eso significaría que se uniría una tabla a la tabla de clientes. En estos casos, lo mejor es fijarse solo en la unión individual que se escribe al evaluar el parámetro relationship. Looker detectará cuándo afecta un fanout de nivel inferior a una consulta, aunque la vista afectada no esté en la combinación que ha creado el fanout.
¿Cómo me ayuda Looker?
Looker tiene mecanismos para asegurarse de que el valor de la relación sea correcto. Una de ellas es una comprobación de la unicidad de la clave principal. Siempre que haya una distribución y se necesiten agregaciones simétricas para calcular una medida, Looker comprobará si la clave principal utilizada es única. Si no es único, se mostrará un error durante el tiempo de ejecución de la consulta (sin embargo, no hay ningún error del validador de LookML).
Además, si Looker no puede gestionar un fanout (normalmente porque no se ha indicado ninguna clave principal), no aparecerá ninguna medida en la exploración de esa vista. Para corregirlo, solo tienes que designar un campo como clave principal para que tus medidas se incluyan en Explorar.
Cuestiones que debes tener en cuenta
Compatibilidad con dialectos para agregados simétricos
Looker puede conectarse con algunos dialectos que no admiten agregaciones simétricas. Puedes consultar una lista de dialectos y su compatibilidad con agregaciones simétricas en la página de documentación symmetric_aggregates.
Caso especial
En la sección Inner join (Combinación interna) de esta página se indica que, para determinar el valor de relación correcto, debes consultar los campos de la cláusula sql_on de la tabla de la izquierda: "Si 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, normalmente debe permanecer como many". Esto es así a menos que la tabla contenga varias columnas que no tengan registros repetidos. En este caso, puedes tratar cualquier columna de este tipo como si fuera una clave principal al formular la relación, aunque no sea la columna designada primary_key: yes.
Puede ser útil asegurarse de que haya 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átala como tal y anota su propiedad especial en el archivo de vista para que otros puedan consultarla en el futuro (incluye el enlace de SQL Runner para demostrarlo). Sin embargo, ten en cuenta que Looker confirmará la veracidad de la unicidad implícita cuando un campo se designe como clave principal, pero no lo hará con otros campos. Simplemente, no invocará el algoritmo de agregados simétricos.