Conceptos de SQL para vistas

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

En esta guía, se tratan los siguientes temas:

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

La vista

Aquí tienes un ejemplo de un archivo de vista llamado users.view, que incluye definiciones de 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 la 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 quiere cambiar el nombre de la tabla de la base de datos, solo tiene que modificar el parámetro sql_table_name. Hay algunos aspectos que debes tener en cuenta al hacer referencia a una tabla de una 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 sería el siguiente:

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

Looker usa las dimensiones y las medidas definidas en la vista para generar su cláusula SELECT de SQL. Cada dimensión define el tipo de dimensión (por ejemplo, cadena, número o booleano) y un parámetro sql de LookML que hace referencia a la dimensión en la vista mediante el alias de la tabla. En el caso de una dimensión llamada age, consulta el siguiente ejemplo:

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

Cuando Looker crea el SQL que se va a enviar a tu base de datos, sustituye el alias de la vista por ${TABLE}. En el caso de la dimensión age del ejemplo anterior, Looker generarí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. El alias de la dimensión se especifica en la expresión sql de una medida. Por ejemplo, una medida que calcula la media 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 cambia el nombre de la dimensión age, su nuevo alias se propagará a todas las referencias de alias de dimensión.

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.

Usar una expresión SQL

Supongamos que quiere dividir los datos de edad en cuatro cohortes: "Jóvenes" (menores de 18 años), "Adultos jóvenes" (de 18 a 35 años), "Adultos mayores" (de 36 a 65 años) y "Personas mayores" (de 65 años en adelante). Para hacer 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 instrucción CASE 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 ha definido su cohorte de edad como dimensión, puede reutilizar la lógica CASE incluyendo la dimensión de cohorte de edad en sus consultas Explorar.

Cuando creas una consulta de Exploración con la dimensión Cohorte de edad, puedes usar la pestaña SQL de Exploración para ver el SQL que genera Looker. Con la dimensión de cohorte de edad, el código SQL será similar al siguiente:

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 la lógica de casos integrada de Looker

Puedes conseguir 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 contenedores de cohorte que se componen de instrucciones when que usan expresiones sql para registrar condiciones y cadenas específicas con las que etiquetar los resultados.

A continuación, se muestra un ejemplo de la misma dimensión age_cohort nueva escrita con el parámetro de LookML case:

  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 crea la sintaxis CASE de SQL correcta para tu base de datos. Además, Looker crea otra expresión para gestionar la ordenación de los grupos, por lo que las etiquetas resultantes no se ordenarán alfanuméricamente (a menos que defina el orden como alfanumérico). Looker crea una consulta de 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

Usar la lógica de contenedor o nivel integrada de Looker

Otro método para especificar cómo se deben agrupar los valores numéricos es usar los tipos de parámetros bin o tier integrados de Looker. El type:bin se usa junto con el parámetro bins y el type: tier se usa junto con el parámetro tiers para separar una dimensión numérica en un conjunto de intervalos 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 de cada conjunto:

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

Puede usar el parámetro tiers en una dimensión exactamente de la misma forma. Por ejemplo:

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

A continuación, Looker genera algo parecido a la siguiente instrucción 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