Esta página foi escrita para quem está tentando 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 mecanismo SQL poderoso 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 uma 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, duas coisas precisam ser feitas corretamente para aproveitar o algoritmo: as chaves primárias precisam ser precisas em todas as visualizações que contêm uma medida (normalmente todas elas) e os parâmetros relationship
precisam estar corretos em todas as mesclagens.
Chaves primárias
Em muitos aspectos, 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 é necessário invocar agregados simétricos quando a mesclagem é 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.
Junções internas
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 | Bessie | 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 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, conforme mostrado a seguir:
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 | Bessie | Coleman | 2 |
4 | US$ 35,00 | 3 | 3 | Wilbur | Wright | 4 |
A relação many_to_one
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. 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 for exclusivo em todas as linhas da tabela customers
, a relação será many_to_many
.
Para determinar o valor de relacionamento correto de forma programática, siga estas etapas verificando as chaves primárias:
- Comece escrevendo
many_to_many
como a relação. Se as chaves primárias estiverem corretas, isso vai gerar resultados precisos, porque o Looker sempre vai acionar o algoritmo de agregação simétrica e aplicar 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 paraone
em vez demany
. - Confira os campos que estão na cláusula
sql_on
da tabela à esquerda. Se o campo ou os campos formam a chave primária da tabela à esquerda, você pode mudar o lado esquerdo do parâmetrorelationship
paraone
. Caso contrário, ele normalmente precisa permanecer comomany
. 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 pela tabela à esquerda, que é representada à esquerda do sinal de igual, e pela 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 de sql_on
para 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 mesclagens externas, você também precisa considerar que uma ramificação pode ocorrer quando registros nulos são adicionados durante a mesclagem. Isso é importante 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 forma 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 união externa completa, os registros nulos podem ser adicionados a qualquer tabela se a chave de união não tiver valores que existem na outra tabela. Isso é ilustrado no exemplo abaixo, 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 tabela customers
a seguir:
customer_id | first_name | last_name | visitas |
---|---|---|---|
1 | Amelia | Earhart | 2 |
2 | Bessie | Coleman | 2 |
3 | Wilbur | Wright | 4 |
4 | Charles | Yeager | 3 |
Depois que essas tabelas forem mescladas, a tabela mesclada poderá ser representada da seguinte forma:
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 | Bessie | Coleman | 2 |
4 | US$ 35,00 | 3 | 3 | Wilbur | Wright | 4 |
null | null | null | 4 | Charles | Yeager | 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 também exige agregados simétricos na tabela à esquerda. Portanto, é necessário mudar 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 se presume que a tabela de 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 vai entender quando um fanout downstream afeta 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 é uma verificação de exclusividade da chave primária. Sempre que há um fanout e agregados simétricos são necessários para calcular uma medida, o Looker verifica a chave primária aproveitada para verificar a exclusividade. Se não for exclusivo, um erro vai aparecer no momento da execução da consulta. No entanto, não há um erro do validador do LookML 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. Confira uma lista de dialetos e o suporte deles a agregados simétricos na página de documentação symmetric_aggregates
.
Caso especial
A seção Junção interna anterior desta página afirma que, para determinar o valor de relacionamento correto, você precisa analisar o campo ou os campos que estão na cláusula sql_on
da tabela à esquerda: "Se o campo ou os campos formam a chave primária da tabela à esquerda, você pode 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, você pode tratar qualquer coluna como se fosse uma chave primária ao formular sua 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 chave primária, mas não faz o mesmo para outros campos. Ele simplesmente não invoca o algoritmo de conjuntos simétricos.