Concetti SQL per le visualizzazioni

Ogni file di visualizzazione nel tuo progetto LookML definisce una singola visualizzazione in Looker, che specifica una tabella da interrogare e quali campi (dimensioni e misure) di quella tabella verranno visualizzati nell'interfaccia utente di Looker. Una vista corrisponde a una singola tabella nel database o a una singola tabella derivata.

In questa guida sono trattati i seguenti argomenti:

Per scoprire di più sull'utilizzo di SQL per definire e personalizzare le tabelle derivate in LookML, consulta Concetti SQL per le tabelle derivate.

La visualizzazione

Ecco un esempio di un file di visualizzazione denominato users.view, che include le definizioni per la tabella del database su cui verrà eseguita la query insieme a diverse dimensioni e misure:

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
  }
}

Il primo elemento della definizione della vista è il parametro sql_table_name, che specifica la tabella nel database su cui verrà eseguita una query da una vista. Questo valore è l'unico punto dell'intero modello in cui viene definito il nome della tabella, perché tutti gli altri riferimenti alla vista utilizzeranno l'alias della tabella ${TABLE}. Se vuoi modificare il nome della tabella del database, devi modificarlo solo nel parametro sql_table_name. Quando fai riferimento a una tabella di database, ci sono alcuni aspetti da considerare.

Looker utilizza il valore sql_table_name per scrivere la clausola SQL FROM, seguita dal nome della vista, che diventa l'alias della tabella. L'equivalente SQL sarà simile a questo:

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

Looker utilizza le dimensioni e le misure definite della visualizzazione per generare la clausola SELECT SQL. Ogni dimensione definisce il tipo di dimensione, ad esempio stringa, numero o booleano, e un parametro LookML sql che fa riferimento alla dimensione all'interno della vista, utilizzando l'alias della tabella. Per una dimensione denominata age, vedi il seguente esempio:

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

Quando Looker crea l'SQL da inviare al database, sostituisce l'alias della vista con ${TABLE}. Per la dimensione age dell'esempio precedente, Looker produrrebbe una clausola SELECT come la seguente:

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

Le misure sono spesso aggregazioni eseguite sulle dimensioni. Specifichi l'alias della dimensione nell'espressione sql di una misura. Ad esempio, una misura che calcola la media della dimensione age potrebbe avere un'espressione sql con l'alias ${age}, come nel seguente esempio:

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

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

Se rinomini la dimensione age, il nuovo alias viene propagato a tutti i relativi riferimenti all'alias della dimensione.

Personalizzare un file di visualizzazione

Puoi personalizzare le espressioni SQL del file di visualizzazione o utilizzare la logica LookML integrata di Looker per imitare la logica di un'espressione SQL.

Utilizzo di un'espressione SQL

Supponiamo che tu voglia dividere i dati sull'età in quattro coorti: gli utenti minori di 18 anni definiti "Giovani", gli utenti di età compresa tra 18 e 35 anni definiti "Giovani adulti", gli utenti di età compresa tra 36 e 65 anni definiti "Adulti" e gli utenti di età pari o superiore a 65 anni definiti "Anziani". Per eseguire questa divisione, devi definire una nuova dimensione, ad esempio dimension: age_cohort, con un'espressione sql che acquisisca queste coorti. La seguente definizione di dimensione LookML utilizza un'istruzione CASE adatta a una connessione al database 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 ;;
}

Ora che hai definito la coorte di età come dimensione, puoi riutilizzare la logica CASE includendo la dimensione della coorte di età nelle query di esplorazione.

Quando crei una query di Esplora con la dimensione Coorte di età, puoi utilizzare la scheda SQL di Esplora per visualizzare l'SQL generato da Looker. Con la dimensione Coorte di età, l'SQL sarà simile al seguente:

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

Utilizzo della logica CASE integrata di Looker

Puoi ottenere lo stesso effetto di un'istruzione SQL CASE con un'espressione indipendente dal database. Il parametro LookML case ti consente di definire i bucket di coorte costituiti da istruzioni when che utilizzano espressioni sql per acquisire condizioni e stringhe specifiche per l'etichettatura dei risultati.

Di seguito è riportato un esempio della stessa nuova dimensione age_cohort scritta con il parametro 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"
    }
  }

In fase di runtime, Looker crea la sintassi SQL CASE corretta per il tuo database. Inoltre, Looker crea un'altra espressione per gestire l'ordinamento dei gruppi, in modo che le etichette risultanti non vengano ordinate solo in ordine alfanumerico (a meno che tu non definisca l'ordine di ordinamento come alfanumerico). Looker crea una query SQL risultante simile alla seguente:

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

Utilizzo della logica di bin o livello integrata di Looker

Un altro metodo per specificare come devono essere raggruppati i valori numerici utilizza i tipi di parametri bin o tier integrati di Looker. type:bin viene utilizzato insieme al parametro bins, mentre type: tier viene utilizzato insieme al parametro tiers per separare una dimensione numerica in un insieme di intervalli numerici. Il compromesso è che non puoi definire etichette per ogni bin.

Il seguente esempio di LookML utilizza il parametro bins in una dimensione per definire il valore minimo in ogni insieme:

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

Puoi utilizzare il parametro tiers in una dimensione esattamente allo stesso modo. Ad esempio:

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

Looker genera quindi un'istruzione SQL simile alla seguente:

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