Esta página foi escrita para qualquer pessoa que esteja tentando usar o LookML para criar uma Análise no Looker. Será mais fácil entender a página se você for proficiente em SQL, principalmente se entender a diferença entre junções internas e externas. Para uma explicação concisa da diferença entre as JOINs internas e externas, consulte este artigo do w3schools sobre Agrupamentos no SQL (link em inglês).
O Looker pode ser um poderoso mecanismo de SQL para sua empresa. A modelagem abstrata no LookML permite que as equipes de dados e TI criem regras gerais que são sempre verdadeiras, liberando os analistas de negócios para criar consultas que estão sempre corretas, mesmo que a equipe de dados nunca tenha previsto uma necessidade delas. O principal fator desse recurso é o algoritmo de agregações simétricas, que resolve um problema do setor com mesclagens de SQL. No entanto, duas coisas precisam ser feitas 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 mesclas.
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 a chave primária não pode ter valores repetidos.
Parâmetro relationship
Agora que você verificou suas chaves primárias, pode determinar o valor correto para o parâmetro relationship da função JOIN. O objetivo do parâmetro relationship é informar ao Looker se é necessário invocar agregações simétricas quando a junção é gravada em uma consulta SQL. Uma abordagem possível seria dizer ao Looker para sempre invocá-los, o que sempre produziria resultados precisos. No entanto, isso tem um custo de desempenho, portanto, é melhor usar agregados simétricos com cautela.
O processo para determinar o valor correto é um pouco diferente entre as junções 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 | $25.00 | 1 |
| 2 | $50.00 | 1 |
| 3 | $75.00 | 2 |
| 4 | $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 | Amélia | Fone de ouvido | 2 |
| 2 | Bessi | Coleman | 2 |
| 3 | Wilbur | Wright | 4 |
É possível mesclar essas tabelas no campo customer_id, que está presente nas duas. Essa mesclagem seria representada no LookML da seguinte maneira:
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 | $25.00 | 1 | 1 | Amélia | Fone de ouvido | 2 |
| 2 | $50.00 | 1 | 1 | Amélia | Fone de ouvido | 2 |
| 3 | $75.00 | 2 | 2 | Bessi | Coleman | 2 |
| 4 | $35.00 | 3 | 3 | Wilbur | Wright | 4 |
A relação many_to_one aqui se refere ao número de vezes que um valor do campo de mesclagem (customer_id) é representado em cada tabela. Na tabela orders (à esquerda), um único ID de cliente é representado várias vezes. Nesse caso, esse é o cliente com o ID 1, presente em várias linhas.
Na tabela customers (a tabela à 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 relacionamento correto de maneira programática, verificando as chaves primárias:
- Comece escrevendo
many_to_manycomo o relacionamento. Contanto que as chaves primárias estejam corretas, isso sempre vai produzir 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 adiciona tempo de execução, é vantajoso tentar mudar um ou ambos os lados paraoneem vez demany. - Observe os campos que estão na 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 precisa permanecer comomany. Para mais informações sobre um caso especial, consulte a seção Considerações mais adiante nesta página. - Em seguida, observe os campos que representam sua tabela à direita na cláusula
sql_on. Se os campos forem a chave primária da tabela à direita, será possível mudar o lado direito paraone.
A prática recomendada é escrever a frase sql_on começando com a tabela à esquerda, que é representada no lado esquerdo do sinal de igual, e com 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 para 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 dessa maneira também ajuda a identificar rapidamente de qual tabela em "Explorar" você quer mesclar a nova tabela.
Mesclagens externas
Para junções externas, também é preciso considerar que pode ocorrer um fanout quando registros nulos são adicionados durante a mesclagem. Isso é importante principalmente porque as mesclagens externas à esquerda são o padrão no Looker. Embora os registros nulos não afetem somas ou médias, eles afetam a maneira como o Looker executa uma medida de type: count. Se isso for feito incorretamente, os registros nulos serão contados (o que é indesejável).
Em uma mesclagem externa completa, registros nulos podem ser adicionados a qualquer uma das tabelas caso a chave de mesclagem não tenha valores que existem na outra tabela. Isso é ilustrado no exemplo abaixo, que envolve uma tabela orders:
| order_id | amount | customer_id |
|---|---|---|
| 1 | $25.00 | 1 |
| 2 | $50.00 | 1 |
| 3 | $75.00 | 2 |
| 4 | $35.00 | 3 |
No exemplo, suponha que você também tenha a seguinte tabela customers:
| customer_id | first_name | last_name | visitas |
|---|---|---|---|
| 1 | Amélia | Fone de ouvido | 2 |
| 2 | Bessi | Coleman | 2 |
| 3 | Wilbur | Wright | 4 |
| 4 | Charles | Mais | 3 |
Depois que essas tabelas são mescladas, a tabela pode ser representada da seguinte maneira:
| order_id | amount | customer_id | customer_id | first_name | last_name | visitas |
|---|---|---|---|---|---|---|
| 1 | $25.00 | 1 | 1 | Amélia | Fone de ouvido | 2 |
| 2 | $50.00 | 1 | 1 | Amélia | Fone de ouvido | 2 |
| 3 | $75.00 | 2 | 2 | Bessi | Coleman | 2 |
| 4 | $35.00 | 3 | 3 | Wilbur | Wright | 4 |
| null | null | null | 4 | Charles | Mais | 3 |
Assim como em uma mesclagem interna, a relação entre as chaves primárias das tabelas é many_to_one. No entanto, o registro nulo adicionado força a necessidade de agregações simétricas também na tabela à esquerda. Portanto, você precisa 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 mesclagem externa à esquerda, a linha nula não seria adicionada e o registro extra do cliente 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 dos 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 com uma ou mais visualizações adicionais. Neste exemplo, uma tabela seria unida à tabela de clientes. Nessas situações, ao avaliar o parâmetro relationship, é melhor analisar apenas a mesclagem individual que está sendo criada. O Looker entenderá quando um fanout downstream afetar uma consulta, mesmo que a visualização afetada não esteja na mesclagem que realmente criou o fanout.
Como o Looker me ajuda?
Há mecanismos no Looker para ajudar a garantir que o valor da relação esteja correto. Uma delas é a verificação da exclusividade da chave primária. Sempre que há um fanout e agregações simétricas são necessárias para calcular uma medida, o Looker verifica se a chave primária utilizada é única. Se ele não for exclusivo, vai aparecer um erro no ambiente de execução da consulta. No entanto, não há nenhum erro do LookML Validator.
Além disso, se não houver como o Looker lidar com um fanout (geralmente porque nenhuma chave primária é indicada), nenhuma medida vai aparecer em "Explore" nessa visualização. Para corrigir isso, basta designar um campo como a chave primária para permitir que suas medidas entrem na Análise.
Informações importantes
Suporte a dialetos para agregações simétricas
O Looker pode se conectar com alguns dialetos que não são compatíveis com agregados simétricos. Confira uma lista de dialetos e o suporte a agregados simétricos na página de documentação do symmetric_aggregates.
Caso especial
A seção Inner join (Mesclagem interna) desta página afirma que, para determinar o valor de relacionamento correto, analise os campos que estão na cláusula sql_on da tabela à esquerda: "Se os campos ou os campos forem a chave primária da tabela à esquerda, será possível mudar o lado esquerdo do parâmetro relationship para one. Caso contrário, ele normalmente precisa permanecer como um many." Isso é verdade, 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 o relacionamento, 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 instrução no parágrafo anterior permaneça sempre verdadeira para a coluna que você designar. Se for o caso, trate-o como tal e anote a propriedade especial dela no arquivo de visualização para que outras pessoas consultem no futuro (completo com o link do SQL Runner para provar). No entanto, o Looker confirma a verdade da exclusividade implícita quando um campo é designado como a chave primária, mas não faz o mesmo para outros campos. Ele simplesmente não vai invocar o algoritmo de agregações simétricas.