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 mediciones) de esa tabla aparecerá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 con SQL, 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 base de datos que se consultará junto con varias dimensiones y mediciones:

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 vista es el sql_table_name parámetro, que especifica la tabla de tu base de datos que consultará una vista. Este valor es el único lugar en todo el modelo donde se define el nombre de la tabla, ya que todas las demás referencias a la vista usarán el alias de tabla ${TABLE}. Si deseas cambiar el nombre de la tabla de la base de datos, solo debes cambiarlo en el parámetro sql_table_name. Hay algunas cosas 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 sql parámetro 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 el SQL para enviarlo a tu base de datos, Looker sustituye el alias de la vista en el ${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 mediciones suelen ser agregaciones que se realizan en dimensiones. Especificas el alias de dimensión en la expresión sql de una medición. Por ejemplo, una medición que calcula el promedio de la dimensión age puede tener una expresión sql con el alias ${age} en ella, 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 propaga 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 integrada de LookML 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 usuarios menores de 18 años definidos como "Jóvenes", usuarios de 18 a 35 años como "Adultos jóvenes", usuarios de 36 a 65 años como "Adultos mayores" y usuarios de 65 años o más como "Adultos 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 instrucción CASE que es adecuada para una conexión de base de datos 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 de edad como una dimensión, puedes volver a usar la lógica CASE si incluyes la dimensión de cohorte de edad en tus consultas de exploración.

Cuando creas una consulta de exploración con la dimensión de cohorte de edad, puedes usar la pestaña SQL de la exploración para ver el SQL que genera Looker. Con la dimensión de cohorte de edad, el 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

Usa la lógica de casos integrada de Looker

Puedes lograr el mismo efecto que una instrucción CASE de SQL con una expresión que sea independiente 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"
    }
  }

En el tiempo de ejecución, Looker compila la sintaxis CASE de SQL correcta para tu base de datos. Además, Looker compila otra expresión para controlar la clasificación de los grupos, de modo que las etiquetas resultantes no solo se clasifiquen alfanuméricamente (a menos que definas el orden de clasificación como alfanumérico). Looker compila una consulta en SQL resultante que se parece 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

Usa la lógica de contenedores o niveles 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. El type:bin se usa junto con el parámetro bins. Del mismo modo, el 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 contenedor.

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 exactamente 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 instrucción de 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