Conceptos de SQL para vistas

Cada archivo de vista de tu proyecto de LookML define una sola vista en Looker, que especifica una tabla para consultar y qué campos (dimensiones y medidas) de esa tabla se mostrarán en la IU de Looker. Una vista corresponde a una sola tabla en tu base de datos o a una sola tabla derivada.

En esta guía, aprenderás sobre los siguientes temas:

Para obtener más información sobre el uso de SQL para definir y personalizar tablas derivadas en LookML, consulta Conceptos de SQL para tablas derivadas.

La vista

Este es un ejemplo de un archivo de vista llamado users.view, que incluye definiciones para la tabla de la base de datos que se consultará junto con varias dimensiones y 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
  }
}

El primer elemento de la definición de la vista es el parámetro sql_table_name, que especifica la tabla de tu base de datos que consultará una vista. Este valor es el único lugar de todo el modelo en el que se define el nombre de la tabla, ya que todas las demás referencias a la vista usarán el alias de la tabla ${TABLE}. Si deseas cambiar el nombre de la tabla de la base de datos, solo debes hacerlo en el parámetro sql_table_name. Hay algunos aspectos que debes tener en cuenta cuando haces referencia a una tabla de base de datos.

Looker usa el valor sql_table_name para escribir su cláusula FROM de SQL, seguida del nombre de la vista, que se convierte en el alias de la tabla. El equivalente de SQL se vería de la siguiente manera:

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

Looker usa las dimensiones y mediciones definidas de la vista para generar su cláusula SELECT de SQL. Cada dimensión define el tipo de dimensión, como cadena, número o booleano, y un parámetro sql de LookML que hace referencia a la dimensión dentro de la vista con el alias de la tabla. Para una dimensión llamada age, consulta el siguiente ejemplo:

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

Cuando Looker crea la sentencia SQL para enviarla a tu base de datos, sustituye el alias de la vista en ${TABLE}. Para la dimensión age del ejemplo anterior, Looker produciría una cláusula SELECT como la siguiente:

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

Las medidas suelen ser agregaciones que se realizan en dimensiones. Especificas el alias de la dimensión en la expresión sql de una medida. Por ejemplo, una medida que calcula el promedio de la dimensión age puede tener una expresión sql con el alias ${age}, como en el siguiente ejemplo:

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

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

Si cambias el nombre de la dimensión age, su alias nuevo se propagará a cualquiera de sus referencias de alias de dimensión.

Cómo personalizar un archivo de vista

Puedes personalizar las expresiones de SQL de tu archivo de vista o usar la lógica de LookML integrada de Looker para imitar la lógica de una expresión de SQL.

Usa una expresión de SQL

Supongamos que deseas dividir los datos de edad en cuatro cohortes, con los usuarios menores de 18 años definidos como "Jóvenes", los usuarios de 18 a 35 años como "Adultos jóvenes", los usuarios de 36 a 65 años como "Adultos mayores" y los usuarios mayores de 65 años como "Mayores". Para realizar esta división, debes definir una dimensión nueva (por ejemplo, dimension: age_cohort) con una expresión sql que capture estas cohortes. La siguiente definición de dimensión de LookML usa una sentencia CASE adecuada para una conexión de base de datos de 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 ;;
}

Ahora que definiste tu cohorte por edad como una dimensión, puedes volver a usar la lógica CASE si incluyes la dimensión de cohorte por edad en tus consultas de Explorar.

Cuando creas una consulta de Explorar con la dimensión de cohorte de edad, puedes usar la pestaña SQL de Explorar para ver el SQL que genera Looker. Con la dimensión de cohorte de edad, la SQL se verá de la siguiente manera:

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

Cómo usar la lógica de casos integrada de Looker

Puedes lograr el mismo efecto que una sentencia CASE de SQL con una expresión que no depende de la base de datos. El parámetro case de LookML te permite definir los buckets de cohortes que se componen de instrucciones when que usan expresiones sql para capturar condiciones y cadenas específicas para etiquetar los resultados.

El siguiente es un ejemplo de la misma dimensión age_cohort nueva que se escribe con el parámetro case de 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"
    }
  }

Durante el tiempo de ejecución, Looker compila la sintaxis correcta de CASE de SQL para tu base de datos. Además, Looker crea otra expresión para controlar el orden de los grupos, de modo que las etiquetas resultantes no solo se ordenen de forma alfanumérica (a menos que definas el orden de clasificación como alfanumérico). Looker compila una consulta en SQL resultante que se asemeja a la siguiente:

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 la lógica de nivel o grupo integrada de Looker

Otro método para especificar cómo se deben agrupar los valores numéricos usa los tipos de parámetros bin o tier integrados de Looker. type:bin se usa junto con el parámetro bins (del mismo modo, type: tier se usa junto con el parámetro tiers) para separar una dimensión numérica en un conjunto de rangos de números. La desventaja es que no puedes definir etiquetas para cada intervalo.

En el siguiente ejemplo de LookML, se usa el parámetro bins en una dimensión para definir el valor mínimo en cada conjunto:

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

Puedes usar el parámetro tiers en una dimensión de la misma manera. Por ejemplo:

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

Luego, Looker genera algo como la siguiente sentencia 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