Conceitos de SQL para visualizações

Cada arquivo de visualização no projeto do LookML define uma única visualização no Looker, que especifica uma tabela a ser consultada e quais campos (dimensões e medidas) dessa tabela serão exibidos na interface do Looker. Uma visualização corresponde a uma única tabela no seu banco de dados ou a uma única tabela derivada.

Neste guia, você aprenderá sobre os seguintes tópicos:

Para saber mais sobre como usar o SQL para definir e personalizar tabelas derivadas no LookML usando SQL, consulte Conceitos de SQL para tabelas derivadas.

A visualização

Este é um exemplo de um arquivo de visualização chamado users.view, que inclui definições para a tabela do banco de dados que será consultada com várias dimensões e medidas:

view: users {
  sql_table_name: thelook.users ;;

  dimension: id {
    primary_key: yes
    type: number
    sql: ${TABLE}.id ;;
  }

  dimension: age {
    type: number
    sql: ${TABLE}.age ;;
  }
   measure: average_age {
    type: average
    sql: ${age} ;;  }

  dimension_group: created {
    type: time
    timeframes: [raw, time, date, week, month, quarter, year]
    sql: ${TABLE}.created_at ;;
  }

  measure: count {
    type: count
  }
}

O primeiro elemento da definição da visualização é o parâmetro sql_table_name, que especifica a tabela no seu banco de dados que será consultada por uma visualização. Esse valor é o único lugar em todo o modelo em que o nome da tabela é definido, porque todas as outras referências à visualização usam o alias ${TABLE}. Se você quiser alterar o nome da tabela do banco de dados, isso só precisa ser alterado no parâmetro sql_table_name. Há algumas coisas a serem consideradas ao referenciar uma tabela de banco de dados.

O Looker usa o valor sql_table_name para escrever a cláusula SQL FROM, seguido pelo nome da visualização, que se torna o alias da tabela. O equivalente no SQL ficaria assim:

FROM `thelook`.`users` AS `users`

O Looker usa as dimensões e medições definidas da visualização para gerar a cláusula SQL SELECT. Cada dimensão define o tipo de dimensão, como string, número ou booleano, e um parâmetro sql do LookML que faz referência à dimensão na visualização usando o alias da tabela. Para uma dimensão chamada age, consulte o exemplo a seguir:

  dimension: age {
    type: number
    sql: ${TABLE}.age ;;
  }

Quando o Looker cria o SQL para enviar ao seu banco de dados, ele substitui o alias da visualização no ${TABLE}. Para a dimensão age do exemplo anterior, o Looker produziria uma cláusula SELECT como esta:

SELECT `users`.`age` AS `users.age`

As medidas geralmente são agregações realizadas em dimensões. Você especifica o alias da dimensão na expressão sql de uma medição. Por exemplo, uma medida que calcula a média da dimensão age pode ter uma expressão sql com o alias ${age}, como neste exemplo:

  dimension: age {
    type: number
    sql: ${TABLE}.age ;;
  }

  measure: average_age {
    type: average
    sql: ${age} ;;
  }

Se você renomear a dimensão age, o novo alias dela será propagado para qualquer uma das referências de alias de dimensão.

Personalizar um arquivo de visualização

É possível personalizar as expressões SQL do seu arquivo de visualização ou usar a lógica LookML integrada do Looker para imitar a lógica de uma expressão SQL.

Como usar uma expressão SQL

Suponha que você queira dividir os dados de idade em quatro coortes, com os usuários com menos de 18 anos definidos como "Jovem", os usuários com idades entre 18 e 35 anos como "Jovem adulto", os usuários com idades entre 36 e 65 anos como "Adulto mais velho" e os usuários com 65 anos ou mais como "Sênior". Para realizar essa divisão, você precisa definir uma nova dimensão, por exemplo, dimension: age_cohort, com uma expressão sql que capture essas coortes. A definição de dimensão do LookML a seguir usa uma instrução CASE adequada para uma conexão de banco de dados MySQL:

dimension: age_cohort {
  type: string
  sql:
    CASE
      WHEN ${age} < 18 THEN 'Youth'
      WHEN ${age} < 35 THEN 'Young Adult'
      WHEN ${age} < 65 THEN 'Older Adult'
      ELSE 'Senior'
    END ;;
}

Agora que você definiu a coorte de idade como uma dimensão, é possível reutilizar a lógica CASE incluindo a dimensão de coorte de idade nas suas consultas da Análise.

Ao criar uma consulta da Análise com a dimensão de coorte de idade, é possível usar a guia SQL dela para acessar o SQL gerado pelo Looker. Com a dimensão de coorte de idade, o SQL ficará mais ou menos assim:

SELECT
CASE
  WHEN users.age < 18 THEN 'Youth'
  WHEN users.age < 35 THEN 'Young Adult'
  WHEN users.age < 65 THEN 'Older Adult'
  ELSE 'Senior'
END  AS `users.age_cohort`,
AVG(`age`) AS `users.average_age`,
COUNT(*) AS `users.count`
FROM
  `thelook`.`users` AS `users`
GROUP BY
  1
ORDER BY
  2 DESC
LIMIT 500

Usar a lógica de caso integrada do Looker

É possível ter o mesmo efeito de uma instrução SQL CASE com uma expressão independente do banco de dados. O parâmetro case do LookML permite definir os buckets de coorte que são compostos de instruções when que usam expressões sql para capturar condições e strings específicas para rotular os resultados.

Confira a seguir um exemplo da mesma nova dimensão age_cohort gravada com o parâmetro case do LookML:

  dimension: age_cohort {
    case: {
      when: {
        sql: ${age} < 18 ;;
        label: "Youth"
      }
      when: {
        sql: ${age} < 35 ;;
        label: "Young Adult"
      }
      when: {
        sql: ${age} < 65 ;;
        label: "Middle-aged Adult"
      }
      else: "Older Adult"
    }
  }

No ambiente de execução, o Looker cria a sintaxe SQL CASE correta para seu banco de dados. Além disso, o Looker cria outra expressão para processar a classificação dos grupos. Assim, os rótulos resultantes não serão apenas classificados de forma alfanumérica, a menos que você defina a ordem de classificação como alfanumérica. O Looker cria uma consulta SQL semelhante a esta:

SELECT
CASE
  WHEN users.age < 18  THEN '0'
  WHEN users.age < 35  THEN '1'
  WHEN users.age < 65  THEN '2'
  ELSE '3'
END AS `users.age_cohort__sort_`,
CASE
  WHEN users.age < 18  THEN 'Youth'
  WHEN users.age < 35  THEN 'Young Adult'
  WHEN users.age < 65  THEN 'Older Adult'
  ELSE 'Senior'
END AS `users.age_cohort`,
AVG(`age`) AS `users.average_age`,
COUNT(*) AS `users.count`
FROM
  `thelook`.`users` AS `users`
GROUP BY
  1,
  2
ORDER BY
  1
LIMIT 500

Usar a lógica integrada de agrupamentos ou níveis do Looker

Outro método para especificar como os valores numéricos devem ser agrupados usa os tipos de parâmetro bin ou tier integrados do Looker. O type:bin é usado com o parâmetro bins (da mesma forma, o type: tier é usado com o parâmetro tiers) para separar uma dimensão numérica em um conjunto de intervalos de números. A desvantagem é que não é possível definir rótulos para cada intervalo.

O exemplo de LookML a seguir usa o parâmetro bins em uma dimensão para definir o valor mínimo em cada conjunto:

  dimension: age_cohort {
    type: bin
    bins: [18,36,65]
    style: integer
    sql: ${age} ;;
  }

É possível usar o parâmetro tiers em uma dimensão da mesma forma. Exemplo:

  dimension: age_cohort {
    type: tier
    tiers: [18,36,65]
    style: integer
    sql: ${age} ;;
  }

O Looker gera algo parecido com esta instrução SQL:

SELECT
CASE
  WHEN users.age  < 18 THEN '0'
  WHEN users.age  >= 18 AND users.age  < 36 THEN '1'
  WHEN users.age  >= 36 AND users.age  < 65 THEN '2'
  WHEN users.age  >= 65 THEN '3'
  ELSE '4'
END AS `users.age_cohort__sort_`,
CASE
  WHEN users.age  < 18 THEN 'Below 18'
  WHEN users.age  >= 18 AND users.age  < 36 THEN '18 to 35'
  WHEN users.age  >= 36 AND users.age  < 65 THEN '36 to 64'
  WHEN users.age  >= 65 THEN '65 or Above'
  ELSE 'Undefined'
END AS `users.age_cohort`,
AVG(`age`) AS `users.average_age`,
COUNT(*) AS `users.count`
FROM
  `thelook`.`users` AS `users`
GROUP BY
  1,
  2
ORDER BY
  1
LIMIT 500