Esta página foi escrita para qualquer pessoa que tente usar o LookML para criar uma análise no Looker. A página será mais fácil de entender se você tiver conhecimento de SQL, especialmente se entender a diferença entre junções internas e externas. Para uma explicação concisa sobre as diferenças entre mesclagens internas e externas, consulte este artigo da w3schools sobre mesclagens SQL.
O Looker pode ser um poderoso mecanismo SQL para sua empresa. A modelagem abstrata no LookML permite que as equipes de dados e de TI criem regras gerais que são sempre verdadeiras, permitindo que analistas de negócios criem consultas que são sempre corretas, mesmo que a equipe de dados nunca tenha previsto a necessidade delas. O principal fator dessa capacidade é o algoritmo de agregação simétrica,
que resolve um problema do setor com agrupamentos SQL. No entanto, é preciso fazer duas coisas corretamente para aproveitar o algoritmo: as chaves primárias precisam ser precisas em todas as visualizações que contenham uma medida (normalmente todas elas), e os parâmetros relationship precisam estar corretos em todas as mesclagens.
Chaves primárias
De muitas maneiras, entender a chave primária de uma tabela é essencialmente o mesmo que entender o que é a tabela e o que pode ser feito com ela. A única coisa que precisa ser verdadeira é que a coluna (ou o conjunto de colunas concatenadas) que você escolher como chave primária não pode ter valores repetidos.
O parâmetro relationship:
Agora que você verificou as chaves primárias, pode determinar o valor correto para o parâmetro relationship da mesclagem. O objetivo do parâmetro relationship é informar ao Looker se ele precisa invocar agregados simétricos quando a mesclagem for gravada em uma consulta SQL. Uma possível abordagem seria instruir o Looker a sempre invocar esses métodos, o que sempre produziria resultados precisos. No entanto, isso tem um custo de desempenho, então é melhor usar agregados simétricos com cautela.
O processo para determinar o valor correto é um pouco diferente entre as mesclagens internas e externas.
Agrupamentos internos
Por exemplo, suponha que você tenha uma tabela de pedidos com uma chave primária de order_id:
| order_id | amount | customer_id |
|---|---|---|
| 1 | US$ 25,00 | 1 |
| 2 | $ 50,00 | 1 |
| 3 | $ 75,00 | 2 |
| 4 | US$ 35,00 | 3 |
Suponha que você também tenha uma tabela de clientes com uma chave primária de customer_id:
| customer_id | first_name | last_name | visitas |
|---|---|---|---|
| 1 | Amelia | Earhart | 2 |
| 2 | Béssia | Coleman | 2 |
| 3 | Wilbur | Wright | 4 |
É possível mesclar essas tabelas no campo customer_id, que está presente nas duas tabelas. Essa junção seria representada no LookML assim:
explore: orders {
join: customers {
type: inner
sql_on: ${orders.customer_id} = ${customers.customer_id} ;;
relationship: many_to_one
}
}
O resultado dessa mesclagem do LookML pode ser representado como uma única tabela mesclada, da seguinte maneira:
| order_id | amount | customer_id | customer_id | first_name | last_name | visitas |
|---|---|---|---|---|---|---|
| 1 | US$ 25,00 | 1 | 1 | Amelia | Earhart | 2 |
| 2 | $ 50,00 | 1 | 1 | Amelia | Earhart | 2 |
| 3 | $ 75,00 | 2 | 2 | Béssia | Coleman | 2 |
| 4 | US$ 35,00 | 3 | 3 | Wilbur | Wright | 4 |
O relacionamento many_to_one aqui se refere ao número de vezes que um valor do campo de junção (customer_id) é representado em cada tabela. Na tabela orders (a 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 (à direita), cada ID de cliente é representado apenas uma vez, já que customer_id é a chave primária dessa tabela. Portanto, os registros 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.
Siga estas etapas para determinar o valor de relação correto de maneira programática verificando as chaves primárias:
- Comece escrevendo
many_to_manycomo a relação. Se as chaves primárias estiverem corretas, isso sempre vai gerar resultados precisos, porque o Looker sempre vai acionar o algoritmo de agregação simétrica e garantir a precisão. No entanto, como o algoritmo complica as consultas e aumenta o tempo de execução, é benéfico tentar mudar um ou ambos os lados paraoneem vez demany. - Observe os campos da cláusula
sql_onna tabela à esquerda. Se os campos forem a chave primária da tabela à esquerda, será possível mudar o lado esquerdo do parâmetrorelationshipparaone. Caso contrário, ele normalmente deve permanecermany. Para saber mais sobre um caso especial, consulte a seção Coisas a considerar mais adiante nesta página. - Em seguida, observe o campo ou os campos que representam a tabela correta na cláusula
sql_on. Se o campo ou os campos formam a chave primária da tabela à direita, mude o lado direito paraone.
É recomendável escrever a frase sql_on começando com a tabela à esquerda, que está representada no lado esquerdo do sinal de igual, e a tabela à direita, que está no lado direito. A ordem das condições no parâmetro sql_on não importa, a menos que a ordem seja relevante para o dialeto SQL do seu banco de dados. Embora o parâmetro sql_on não exija que você ordene os campos dessa maneira, organizar as condições sql_on de modo que os lados esquerdo e direito do sinal de igualdade correspondam à forma como o parâmetro relationship é lido da esquerda para a direita pode ajudar a determinar a relação. Ordenar os campos dessa forma também pode ajudar a identificar rapidamente a tabela existente em "Explorar" com que você está mesclando a nova.
mesclagens externas
Para outer joins, também é preciso considerar que um fanout pode ocorrer quando registros nulos são adicionados durante a junção. Isso é importante principalmente porque a left outer join é o padrão no Looker. Embora os registros nulos não afetem somas ou médias, eles afetam a forma como o Looker executa uma medição de type: count. Se isso for feito incorretamente, os registros nulos serão contados (o que não é desejável).
Em uma operação "full outer join", é possível adicionar registros nulos a qualquer uma das tabelas se a chave de junção não tiver valores na outra tabela. Isso é ilustrado no exemplo a seguir, que envolve uma tabela orders:
| order_id | amount | customer_id |
|---|---|---|
| 1 | US$ 25,00 | 1 |
| 2 | $ 50,00 | 1 |
| 3 | $ 75,00 | 2 |
| 4 | US$ 35,00 | 3 |
Para o exemplo, suponha que você também tenha a seguinte tabela customers:
| customer_id | first_name | last_name | visitas |
|---|---|---|---|
| 1 | Amelia | Earhart | 2 |
| 2 | Béssia | Coleman | 2 |
| 3 | Wilbur | Wright | 4 |
| 4 | Charles | Yeager | 3 |
Depois que essas tabelas forem mescladas, a tabela mesclada poderá ser representada da seguinte maneira:
| order_id | amount | customer_id | customer_id | first_name | last_name | visitas |
|---|---|---|---|---|---|---|
| 1 | US$ 25,00 | 1 | 1 | Amelia | Earhart | 2 |
| 2 | $ 50,00 | 1 | 1 | Amelia | Earhart | 2 |
| 3 | $ 75,00 | 2 | 2 | Béssia | Coleman | 2 |
| 4 | US$ 35,00 | 3 | 3 | Wilbur | Wright | 4 |
| null | null | null | 4 | Charles | Yeager | 3 |
Assim como em uma junção interna, a relação entre as chaves primárias é many_to_one. No entanto, o registro nulo adicionado também exige agregados simétricos na tabela à esquerda. Portanto, é preciso alterar o parâmetro relationship para many_to_many, porque a execução dessa mesclagem interrompe as contagens na tabela à esquerda.
Se esse exemplo fosse uma união externa à esquerda, a linha nula não seria adicionada e o registro de cliente extra seria descartado. Nesse caso, a relação ainda seria many_to_one. Esse é o padrão do Looker porque presume que a tabela base define a análise. Nesse caso, você está analisando pedidos, não clientes. Se a tabela de clientes estivesse à esquerda, a situação seria diferente.
Mesclagens em vários níveis
Em algumas Análises, a tabela base é mesclada a uma ou mais visualizações que, por sua vez, precisam ser mescladas a uma ou mais visualizações. No exemplo, isso significa que uma tabela seria mesclada à tabela de clientes. Nessas situações, é melhor analisar apenas a mesclagem individual que está sendo gravada ao avaliar o parâmetro relationship. O Looker entende quando um fanout downstream afeta uma consulta, mesmo que a visualização afetada não esteja na mesclagem que criou o fanout.
Como o Looker me ajuda?
Existem mecanismos no Looker que ajudam a garantir que o valor da relação esteja correto. Uma delas é uma verificação de exclusividade da chave primária. Sempre que há um fanout e dados agregados simétricos são necessários para calcular uma medida, o Looker verifica a exclusividade na chave primária aproveitada. Se ele não for único, vai aparecer um erro no ambiente de execução da consulta. No entanto, não há erros do LookML Validator para isso.
Além disso, se não houver como o Looker processar um fanout (geralmente porque nenhuma chave primária está indicada), nenhuma medida vai aparecer na Análise dessa visualização. Para corrigir isso, basta designar um campo como a chave primária para permitir que as medidas entrem na Análise detalhada.
Informações importantes
Suporte a dialetos para agregados simétricos
O Looker pode se conectar a alguns dialetos que não oferecem suporte a agregados simétricos. Consulte uma lista de dialetos e o suporte para agregados simétricos na página de documentação do symmetric_aggregates.
Caso especial
A seção Inner join anterior nesta página afirma que, para determinar o valor do relacionamento correto, você precisa analisar os campos da cláusula sql_on na tabela à esquerda: "Se os campos formarem a chave primária da tabela à esquerda, é possível mudar o lado esquerdo do parâmetro relationship para one. Caso contrário, ela precisa permanecer como many. Isso é verdadeiro, a menos que a tabela tenha várias colunas sem registros repetidos. Nesse caso, é possível tratar qualquer coluna como se fosse uma chave primária ao formular a relação, mesmo que não seja a coluna designada como primary_key: yes.
Pode ser útil garantir que haja algum tipo de regra de software em vigor que garanta que a declaração no parágrafo anterior sempre permaneça verdadeira para a coluna designada. Se sim, trate-o como tal e anote a propriedade especial no arquivo de visualização para que outras pessoas possam consultar no futuro (com o link do SQL Runner para provar). No entanto, o Looker confirma a exclusividade implícita quando um campo é designado como a chave primária, mas não faz o mesmo nos outros campos. Ele simplesmente não invoca o algoritmo de conjuntos simétricos.