Conceitos de SQL para visualizações

Cada arquivo de visualização no seu projeto do LookML define uma única visualização no Looker, que especifica uma tabela para consultar e quais campos (dimensões e métricas) dessa tabela vão aparecer 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ê vai aprender sobre os seguintes tópicos:

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

A visualização

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

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 de visualização é o parâmetro sql_table_name, que especifica a tabela no 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 da tabela ${TABLE}. Se quiser mudar o nome da tabela do banco de dados, basta alterar o parâmetro sql_table_name. Há algumas coisas a considerar ao referenciar uma tabela de banco de dados.

O Looker usa o valor sql_table_name para gravar a cláusula FROM do SQL, seguida pelo nome da visualização, que se torna o alias da tabela. O equivalente em SQL seria assim:

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

O Looker usa as dimensões e medidas definidas na 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, confira este exemplo:

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

Quando o Looker cria o SQL para enviar ao banco de dados, ele substitui o alias pela 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 em uma expressão sql de uma métrica. Por exemplo, uma métrica que calcula a média da dimensão age pode ter uma expressão sql com o alias ${age}, como no exemplo a seguir:

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

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

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

Personalizar um arquivo de visualização

É possível personalizar as expressões SQL do 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

Imagine que você queira dividir os dados de idade em quatro coortes: usuários menores de 18 anos como "Jovens", de 18 a 35 anos como "Jovens adultos", de 36 a 65 anos como "Adultos mais velhos" e com 65 anos ou mais como "Idosos". Para fazer essa divisão, defina uma nova dimensão, por exemplo, dimension: age_cohort, com uma expressão sql que capture essas coortes. A definição de dimensão da 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 sua coorte de idade como uma dimensão, é possível reutilizar a lógica CASE incluindo a dimensão de coorte de idade nas consultas do recurso Detalhar.

Ao criar uma consulta do recurso Detalhar com a dimensão de faixa etária, use a guia "SQL" para ver o SQL gerado pelo Looker. Com a dimensão de faixa etária, o SQL será parecido com isto:

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 conseguir o mesmo efeito de uma instrução CASE do SQL com uma expressão independente do banco de dados. O parâmetro case da LookML permite definir os agrupamentos de coorte compostos por instruções when que usam expressões sql para capturar condições e strings específicas para rotular os resultados.

Confira um exemplo da mesma nova dimensão age_cohort gravada com o parâmetro case da 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"
    }
  }

Em tempo 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 são classificados apenas alfanumericamente, a menos que você defina a ordem de classificação como alfanumérica. O Looker cria uma consulta SQL resultante 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 de agrupamento ou nível integrada do Looker

Outro método para especificar como os valores numéricos devem ser agrupados usa os tipos de parâmetros 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 agrupamento.

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} ;;
  }

Você pode 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 como a seguinte 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