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 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, consulta Conceptos de SQL para tablas derivadas.

La vista

A continuación, se muestra 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 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 la 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 en SQL se vería de la siguiente manera:

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

Looker usa las dimensiones y las medidas 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 el código SQL para enviarlo 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 las 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 nuevo alias se propagará a cualquiera de sus referencias de alias de dimensión.

Cómo personalizar un archivo de vista

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

Cómo usar 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 "Adultos mayores". Para realizar esta división, debes definir una nueva dimensión, 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 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 reutilizar la lógica de CASE incluyendo la dimensión de cohorte de edad en tus consultas de Explorar.

Cuando creas una consulta de Explorar con la dimensión de cohorte etaria, puedes usar la pestaña SQL de Explorar para ver el código SQL que genera Looker. Con la dimensión de cohorte etaria, el código 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 instrucción CASE de SQL con una expresión 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.

A continuación, se muestra un ejemplo de la misma dimensión nueva age_cohort 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 crea otra expresión para controlar la ordenación de los grupos, por lo que las etiquetas resultantes no se ordenarán solo de forma alfanumérica (a menos que definas el orden de clasificación como alfanumérico). Looker crea una consulta en SQL resultante similar 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 discretización o de niveles integrada de Looker

Otro método para especificar cómo se deben agrupar los valores numéricos usa los tipos de parámetros integrados bin o tier de Looker. El parámetro type:bin se usa junto con el parámetro bins, y el parámetro 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 discretización.

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 similar a 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