Esta página destina-se a qualquer pessoa que tente usar o LookML para criar uma Exploração no Looker. A página é mais fácil de compreender se tiver conhecimentos de SQL, especificamente se compreender a diferença entre as junções internas e externas. Para uma explicação concisa da diferença entre as junções internas e externas, consulte este artigo do w3schools sobre junções SQL.
O Looker tem a capacidade de ser um motor de SQL poderoso para a sua empresa. A modelagem abstrata no LookML permite que as equipas de dados e de TI criem regras gerais que são sempre verdadeiras, o que permite aos analistas de negócios criar consultas em grande escala que estão sempre corretas, mesmo que a equipa de dados nunca tenha previsto uma necessidade para elas. O principal fator desta capacidade é o algoritmo de agregações simétricas, que resolve um problema generalizado do setor com junções SQL. No entanto, têm de ser feitas duas coisas corretamente para tirar partido do algoritmo: as chaves primárias têm de ser precisas em todas as vistas que contenham uma medida (normalmente, todas) e os parâmetros relationship têm de estar corretos em todas as junções.
Chaves principais
Em muitos aspetos, compreender a chave primária de uma tabela é essencialmente o mesmo que compreender o que é a tabela e o que pode ser feito com ela. A única coisa que tem de ser verdadeira é que a coluna (ou o conjunto de colunas concatenadas) que escolher como chave principal não pode ter valores repetidos.
O parâmetro relationship
Agora que validou as chaves principais, pode determinar o valor correto para o parâmetro relationship da junção. O objetivo do parâmetro relationship é indicar ao Looker se deve invocar agregados simétricos quando a junção é escrita numa consulta SQL. Uma possível abordagem aqui seria dizer ao Looker para os invocar sempre, o que produziria sempre resultados precisos. No entanto, isto tem um custo de desempenho, pelo que é melhor usar os agregados simétricos com moderação.
O processo para determinar o valor correto é ligeiramente diferente entre as junções internas e externas.
Junções internas
Por exemplo, suponha que tem uma tabela de encomendas com uma chave primária de order_id:
| order_id | valor | customer_id |
|---|---|---|
| 1 | 25,00 € | 1 |
| 2 | 50 $ | 1 |
| 3 | 75 $ | 2 |
| 4 | 35,00 $ | 3 |
Suponhamos que também tem uma tabela de clientes com uma chave principal de customer_id:
| customer_id | first_name | last_name | visitas |
|---|---|---|---|
| 1 | Amelia | Earhart | 2 |
| 2 | Bessie | Coleman | 2 |
| 3 | Wilbur | Wright | 4 |
Pode juntar estas tabelas no campo customer_id, que está presente em ambas as tabelas. Esta junção seria representada no LookML da seguinte forma:
explore: orders {
join: customers {
type: inner
sql_on: ${orders.customer_id} = ${customers.customer_id} ;;
relationship: many_to_one
}
}
O resultado desta junção do LookML pode ser representado como uma única tabela unida, da seguinte forma:
| order_id | valor | customer_id | customer_id | first_name | last_name | visitas |
|---|---|---|---|---|---|---|
| 1 | 25,00 € | 1 | 1 | Amelia | Earhart | 2 |
| 2 | 50 $ | 1 | 1 | Amelia | Earhart | 2 |
| 3 | 75 $ | 2 | 2 | Bessie | Coleman | 2 |
| 4 | 35,00 $ | 3 | 3 | Wilbur | Wright | 4 |
A relação many_to_one aqui refere-se ao número de vezes que um valor do campo de junção (customer_id) é representado em cada tabela. Na tabela orders (a tabela à esquerda), um único ID de cliente é representado várias vezes (neste caso, é o cliente com o ID 1, que está presente em várias linhas).
Na tabela customers (a tabela à direita), cada ID de cliente só é representado uma vez, uma vez que customer_id é a chave principal dessa tabela. Por conseguinte, os registos na tabela orders podem ter muitas correspondências para um único valor na tabela customers. Se customer_id não fosse único em todas as linhas da tabela customers, a relação seria many_to_many.
Pode seguir estes passos para determinar o valor de relação correto de forma programática através da verificação das chaves primárias:
- Comece por escrever
many_to_manycomo a relação. Desde que as chaves primárias estejam corretas, isto produz sempre resultados precisos porque o Looker aciona sempre o algoritmo de agregação simétrica e aplica a precisão. No entanto, uma vez que o algoritmo complica as consultas e adiciona tempo de execução, é vantajoso tentar alterar um ou ambos os lados paraoneem vez demany. - Consulte o campo ou os campos que estão na cláusula
sql_onda tabela à esquerda. Se o campo ou os campos formarem a chave principal da tabela do lado esquerdo, pode alterar o lado esquerdo do parâmetrorelationshipparaone. Caso contrário, normalmente, tem de permanecermany. (Para informações sobre um caso especial, consulte a secção Aspetos a considerar mais adiante nesta página.) - Em seguida, analise o campo ou os campos que representam a tabela à direita na cláusula
sql_on. Se o campo ou os campos formarem a chave primária da tabela do lado direito, pode alterar o lado direito paraone.
É uma prática recomendada escrever a frase sql_on começando pela tabela à esquerda, que é representada no lado esquerdo do sinal de igual, e pela tabela à direita, que está no lado direito. A ordem das condições no parâmetro sql_on é irrelevante, a menos que a ordem seja relevante para o dialeto SQL da sua base de dados. Embora o parâmetro sql_on não exija que ordene os campos desta forma, organizar as condições sql_on de modo que os lados esquerdo e direito do sinal de igual correspondam à forma como o parâmetro relationship é lido da esquerda para a direita pode ajudar a determinar a relação. Ordenar os campos desta forma também pode ajudar a distinguir mais facilmente, rapidamente, a tabela existente na análise detalhada à qual está a juntar a nova tabela.
Junções externas
Para as junções externas, também tem de ter em consideração que pode ocorrer uma expansão quando são adicionados registos nulos durante a junção. Isto é particularmente importante porque as junções externas esquerdas são a predefinição no Looker. Embora os registos nulos não afetem as somas nem as médias, afetam a forma como o Looker executa uma medida de type: count. Se isto for feito incorretamente, os registos nulos são contabilizados (o que não é desejável).
Numa junção externa completa, podem ser adicionados registos nulos a qualquer uma das tabelas se a respetiva chave de junção tiver valores em falta que existam na outra tabela. Isto é ilustrado no exemplo seguinte, que envolve uma tabela orders:
| order_id | valor | customer_id |
|---|---|---|
| 1 | 25,00 € | 1 |
| 2 | 50 $ | 1 |
| 3 | 75 $ | 2 |
| 4 | 35,00 $ | 3 |
Para o exemplo, suponha que também tem a seguinte tabela customers:
| customer_id | first_name | last_name | visitas |
|---|---|---|---|
| 1 | Amelia | Earhart | 2 |
| 2 | Bessie | Coleman | 2 |
| 3 | Wilbur | Wright | 4 |
| 4 | Charles | Yeager | 3 |
Depois de juntar estas tabelas, a tabela resultante pode ser representada da seguinte forma:
| order_id | valor | customer_id | customer_id | first_name | last_name | visitas |
|---|---|---|---|---|---|---|
| 1 | 25,00 € | 1 | 1 | Amelia | Earhart | 2 |
| 2 | 50 $ | 1 | 1 | Amelia | Earhart | 2 |
| 3 | 75 $ | 2 | 2 | Bessie | Coleman | 2 |
| 4 | 35,00 $ | 3 | 3 | Wilbur | Wright | 4 |
| nulo | nulo | nulo | 4 | Charles | Yeager | 3 |
Tal como numa junção interna, a relação entre as chaves principais das tabelas é many_to_one. No entanto, o registo nulo adicionado força a necessidade de agregações simétricas também na tabela esquerda. Por conseguinte, tem de alterar o parâmetro relationship para many_to_many, porque a execução desta junção interrompe as contagens na tabela à esquerda.
Se este exemplo tivesse sido uma junção externa à esquerda, a linha nula não teria sido adicionada e o registo de cliente adicional teria sido ignorado. Nesse caso, a relação continuaria a ser many_to_one. Esta é a predefinição do Looker, porque se assume que a tabela base define a análise. Neste caso, está a analisar encomendas e não clientes. Se a tabela de clientes estivesse à esquerda, a situação seria diferente.
Junções de vários níveis
Em algumas explorações, a tabela base junta-se a uma ou mais vistas que, por sua vez, têm de se juntar a uma ou mais vistas adicionais. No exemplo aqui, isso significa que uma tabela seria unida à tabela de clientes. Nestes casos, é melhor analisar apenas a junção individual que está a ser escrita quando avaliar o parâmetro relationship. O Looker vai compreender quando uma ramificação a jusante afeta uma consulta, mesmo que a vista afetada não esteja na junção que criou efetivamente a ramificação.
Como é que o Looker me ajuda?
Existem mecanismos no Looker para ajudar a garantir que o valor da relação está correto. Uma é uma verificação da singularidade da chave principal. Sempre que existe uma distribuição e são necessários agregados simétricos para calcular uma medida, o Looker verifica a chave principal usada para garantir a unicidade. Se não for único, é apresentado um erro no tempo de execução da consulta (no entanto, não existe um erro do validador do LookML para isto).
Além disso, se não houver forma de o Looker processar uma distribuição (normalmente, porque não é indicada nenhuma chave primária), não são apresentadas medidas na exploração dessa vista. Para corrigir este problema, basta designar um campo como a chave principal para permitir que as suas métricas entrem na funcionalidade Explorar.
Aspetos a considerar
Suporte de dialetos para agregações simétricas
O Looker pode estabelecer ligação a alguns dialetos que não suportam agregações simétricas. Pode ver uma lista de dialetos e o respetivo suporte para agregações simétricas na página de documentação symmetric_aggregates.
Caso especial
A secção Junção interna anteriormente nesta página indica que, para determinar o valor da relação correto, deve consultar o campo ou os campos que estão na cláusula sql_on da tabela à esquerda: "Se o campo ou os campos formarem a chave primária da tabela à esquerda, pode alterar o lado esquerdo do parâmetro relationship para one. Caso contrário, normalmente, tem de permanecer como many". Isto é verdade, a menos que a tabela contenha várias colunas que não tenham registos repetidos. Neste caso, pode tratar qualquer coluna deste tipo como se fosse uma chave principal ao formular a relação, mesmo que não seja a coluna designada primary_key: yes.
Pode ser útil certificar-se de que existe algum tipo de regra de software em vigor que garanta que a declaração no parágrafo anterior permanece sempre verdadeira para a coluna que designar. Se for o caso, trate-o como tal e tome nota da respetiva propriedade especial no ficheiro de visualização para que outros possam consultar no futuro (com o link do SQL Runner para o comprovar). Tenha em atenção que o Looker confirma a veracidade da unicidade implícita quando um campo é designado como a chave primária, mas não o faz para outros campos. Simplesmente, não invoca o algoritmo de acumulações simétricas.