Conceitos de SQL para mesclagens

Assim como no SQL, uma junção no LookML é usada para combinar linhas de duas ou mais tabelas com base em uma coluna relacionada entre elas.

No LookML, uma Análise, conforme definida pelo parâmetro LookML explore, é usada para definir como um usuário pode consultar os dados. Uma Análise consiste em pelo menos uma visualização ou um conjunto de visualizações unidas. A visualização principal em "Análise" é sempre incluída na consulta. As visualizações combinadas normalmente só são incluídas se forem necessárias para atender à consulta.

Uma visualização do LookML corresponde a uma tabela SQL (ou outro elemento que tenha a estrutura de uma tabela) no banco de dados ou a uma tabela derivada. A visualização define quais campos ou colunas estão disponíveis no banco de dados e como elas devem ser acessadas.

O exemplo abaixo é uma definição para a Análise orders.

explore: orders {
  join: users {
    type: left_outer
    sql_on: ${orders.user_id} = ${users.id} ;;
    relationship: many_to_one
  }
}

A visualização orders, que é a principal na seção "Explorar", está sendo combinada à visualização users com um SQL LEFT OUTER JOIN, conforme indicado pelo parâmetro LookML type: left_outer. A cláusula SQL ON, que é definida pelo parâmetro sql_on do LookML, não usa table_alias.column, mas se refere a to ${view_name.field_name}. Dessa forma, se o nome da tabela ou da coluna for alterado no banco de dados, essa alteração só precisará ser feita em um só lugar.

O parâmetro relationship é importante. As junções podem causar problemas de fanout em que as linhas são duplicadas. Ao especificar que muitos pedidos vão se agrupar em apenas um usuário, o Looker reconhece que os fanouts não vão ocorrer com esse agrupamento. Portanto, não é necessário um processamento especial. No entanto, os relacionamentos one_to_many podem acionar um fanout.

Por padrão, a geração automática de visualizações e Análises é feita com uma junção externa à esquerda. No exemplo anterior, no entanto, é muito provável que cada pedido tenha exatamente um usuário. Portanto, a mesclagem nesse exemplo pode ser interna.

Para conferir o SQL gerado de uma Análise, execute-a na IU e selecione a guia SQL no painel Dados.

Por exemplo, se você abrir a Análise detalhada Pedidos, que foi definida anteriormente, e selecionar os campos ID do usuário e Contagem, o SQL gerado vai ser parecido com este:

SELECT
    `user_id` AS `orders.user_id`,
    COUNT(*) AS `orders.count`
FROM
    `thelook`.`orders` AS `orders`
GROUP BY
    1
ORDER BY
    2 DESC
LIMIT 500

Neste exemplo, a tabela de usuários não é referenciada. Ele só é usado quando necessário.

Se você remover a dimensão ID do usuário e adicionar a dimensão ID na visualização Usuários, o SQL vai ficar assim:

SELECT
    `users`.`id` AS `users.id`,
    COUNT(*) AS `orders.count`
FROM
    `thelook`.`orders` AS `orders`
    INNER JOIN `thelook`.`users` AS `users` ON `orders`.`user_id` = `users`.`id`
GROUP BY
    1
ORDER BY
    2 DESC
LIMIT 500

Nesse caso, como há uma seleção da visualização Usuários, a mesclagem é incluída.

O exemplo a seguir mostra o LookML no arquivo Explorar orders, que foi definido anteriormente, e adiciona uma junção à visualização order_items:

explore: orders {
  join: users {
    type: inner
    sql_on: ${orders.user_id} = ${users.id} ;;
    relationship: many_to_one
  }
  join: order_items {
    type: inner
    sql_on: ${orders.id} = ${order_items.order_id} ;;
    relationship: one_to_many
  }
}

Agora, se você abrir a análise Orders na UI, vai encontrar a visualização Order Items. Se você selecionar a medida Total Sale Price na visualização Order Items com a Count de Orders e o ID de Users, o Looker vai gerar o seguinte SQL:

SELECT
    `users`.`id` AS `users.id`,
    COUNT(DISTINCT orders.id ) AS `orders.count`,
    COALESCE(SUM(`order_items`.`sale_price`), 0) AS `order_items.total_sale_price`
FROM
    `thelook`.`orders` AS `orders`
    INNER JOIN `thelook`.`users` AS `users` ON `orders`.`user_id` = `users`.`id`
    INNER JOIN `thelook`.`order_items` AS `order_items` ON `orders`.`id` = `order_items`.`order_id`
GROUP BY
    1
ORDER BY
    2 DESC
LIMIT 500

Neste exemplo, COUNT(*) AS orders.count se tornou COUNT(DISTINCT orders.id ) AS orders.count. O Looker detectou uma possível situação de fanout e adicionou automaticamente a palavra-chave SQL DISTINCT à função SQL COUNT.