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 a ser consultada e quais campos (dimensões e medições) 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ê vai aprender sobre os seguintes tópicos:

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

Visualização

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

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 sql_table_name parâmetro, 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 da tabela ${TABLE}. Se você quiser mudar o nome da tabela de banco de dados, ele só precisará ser alterado no 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 SQL FROM, 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 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 sql parâmetro do LookML que referencia a 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 por ${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 medições 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 medição que calcula a média da dimensão age pode ter uma expressão sql com o alias ${age} nela, 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 de dimensão.

Personalizar um arquivo de visualização

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

Usar uma expressão SQL

Suponha que você queira dividir os dados de idade em quatro grupos, com usuários menores de 18 anos definidos como "Jovens", usuários de 18 a 35 anos como "Adultos jovens", usuários de 36 a 65 anos como "Adultos mais velhos" e usuários com 65 anos ou mais como "Idosos". Para realizar essa divisão, você precisa definir uma nova dimensão, digamos, dimension: age_cohort, com uma expressão sql que capture esses grupos. 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 o grupo de idade como uma dimensão, é possível reutilizar a lógica CASE incluindo a dimensão do grupo de idade nas consultas de análise.

Ao criar uma consulta de análise com a dimensão do grupo de idade, você pode usar a guia SQL da análise para conferir o SQL gerado pelo Looker. Com a dimensão do grupo de idade, o SQL será semelhante a este:

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 LookML

É possível conseguir 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 grupo 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 escrita 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 momento da 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 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 compartimento ou nível integrada do LookML

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 em conjunto com o parâmetro bins. Da mesma forma, o type: tier é usado em conjunto 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 compartimento.

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 exatamente da mesma maneira. Exemplo:

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

Em seguida, o Looker gera algo como a instrução SQL a seguir:

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