Conceitos de SQL para visualizações de propriedades

Cada ficheiro de vista no seu projeto LookML define uma única vista no Looker, que especifica uma tabela a consultar e que campos (dimensões e medidas) dessa tabela vão ser apresentados na IU do Looker. Uma vista corresponde a uma única tabela na sua base de dados ou a uma única tabela derivada.

Neste guia, vai saber mais sobre os seguintes tópicos:

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

A vista

Segue-se um exemplo de um ficheiro de visualização denominado users.view, que inclui definições para a tabela de base de dados que vai ser consultada, juntamente 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 vista é o parâmetro sql_table_name, que especifica a tabela na sua base de dados que vai ser consultada por uma vista. Este valor é o único local em todo o modelo onde o nome da tabela está definido, porque todas as outras referências à vista usam o alias da tabela ${TABLE}. Se quiser alterar o nome da tabela da base de dados, só tem de o alterar no parâmetro sql_table_name. Existem alguns aspetos a considerar quando faz referência a uma tabela de base de dados.

O Looker usa o valor sql_table_name para escrever a respetiva cláusula SQL FROM, seguido do nome da vista, que se torna o alias da tabela. O equivalente em SQL teria o seguinte aspeto:

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

O Looker usa as dimensões e as medidas definidas da vista para gerar a respetiva cláusula SQL SELECT. Cada dimensão define o tipo de dimensão, como string, número ou Booleano, e um parâmetro sql LookML que faz referência à dimensão na vista, usando o alias da tabela. Para uma dimensão denominada age, veja o exemplo seguinte:

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

Quando o Looker cria o SQL para enviar para a sua base de dados, o Looker substitui o alias da vista por ${TABLE}. Para a dimensão age do exemplo anterior, o Looker geraria uma cláusula SELECT semelhante à seguinte:

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

As medidas são frequentemente agregações realizadas em dimensões. Especifica o alias da dimensão na expressão sql de uma medida. Por exemplo, uma medida que calcula a média da dimensão age pode ter uma expressão sql com o alias ${age}, como no exemplo seguinte:

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

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

Se mudar o nome da dimensão age, o novo alias propaga-se a qualquer uma das respetivas referências de alias de dimensão.

Personalizar um ficheiro de visualização

Pode personalizar as expressões SQL do ficheiro de visualização ou usar a lógica LookML integrada do Looker para imitar a lógica de uma expressão SQL.

Usar uma expressão SQL

Suponhamos que quer dividir os dados de idade em quatro coortes, com os utilizadores com menos de 18 anos definidos como "Jovens", os utilizadores com idades entre 18 e 35 anos como "Jovens adultos", os utilizadores com idades entre 36 e 65 anos como "Adultos mais velhos" e os utilizadores com 65 anos ou mais como "Sénior". Para fazer esta divisão, tem de definir uma nova dimensão, por exemplo, dimension: age_cohort, com uma expressão sql que capture estas coortes. A seguinte definição de dimensão do LookML usa uma declaração CASE adequada para uma ligação à base 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 definiu a coorte de idade como uma dimensão, pode reutilizar a lógica CASE incluindo a dimensão da coorte de idade nas suas consultas de exploração.

Quando cria uma consulta de exploração com a dimensão coorte etária, pode usar o separador SQL da exploração para ver o SQL que o Looker gera. Com a dimensão coorte de idade, o SQL tem um aspeto 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 capitalização integrada do Looker

Pode conseguir o mesmo efeito que uma declaração SQL CASE com uma expressão independente da base de dados. O parâmetro case do LookML permite-lhe definir os grupos de coortes compostos por declarações when que usam expressões sql para captar condições e strings específicas para etiquetar os resultados.

Segue-se um exemplo da mesma nova dimensão age_cohort escrita com o parâmetro case 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 CASE SQL correta para a sua base de dados. Além disso, o Looker cria outra expressão para processar a ordenação dos grupos, pelo que as etiquetas resultantes não são ordenadas apenas alfanumericamente (a menos que defina a ordem de ordenação como alfanumérica). O Looker cria uma consulta SQL resultante semelhante à seguinte:

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 intervalos ou níveis 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 incorporados do Looker. O elemento type:bin é usado em conjunto com o parâmetro bins. Da mesma forma, o elemento type: tier é usado em conjunto com o parâmetro tiers para separar uma dimensão numérica num conjunto de intervalos de números. A desvantagem é que não pode definir etiquetas para cada recipiente.

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

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

Pode usar o parâmetro tiers numa dimensão exatamente da mesma forma. Por exemplo:

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

Em seguida, o Looker gera algo semelhante à seguinte declaraçã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