Concetti SQL per le tabelle derivate

Sebbene le viste LookML siano in genere basate su tabelle esistenti all'interno del database, puoi anche creare viste basate su istruzioni SQL SELECT. In Looker, questo tipo di visualizzazione è chiamato tabella derivata. Le tabelle derivate sono query i cui risultati vengono utilizzati come se fossero tabelle effettive nel database.

Questa guida illustra i seguenti argomenti:

In che modo Looker genera il codice SQL per le tabelle derivate

Quando esegui query sulle tabelle derivate in LookML, Looker traduce le query in istruzioni SQL come espressioni della tabella comune (CTE) o viste in linea, a seconda del dialetto. In altre parole, le query SQL generate possono assomigliare a uno dei seguenti esempi.

L'esempio seguente mostra come Looker potrebbe generare una query SQL che utilizza un'espressione CTE per eseguire una query su una tabella derivata:

WITH (
    SELECT o.user_id as id
    FROM orders AS o
    INNER JOIN order_items AS oi ON o.id == oi.order_id
    ORDER BY SUM(oi.total_sale_price) DESC
    GROUP BY o.customer_id
    LIMIT 100
    ) AS top_100_users
SELECT ...
FROM users AS u
INNER JOIN top_100_users ON u.id == top_100_users.id
WHERE ...

L'esempio seguente mostra come Looker potrebbe generare una query SQL che utilizza una vista in linea per eseguire query su una tabella derivata:

SELECT ...
FROM users AS u
INNER JOIN (
    SELECT o.user_id as id
    FROM orders AS o
    INNER JOIN order_items AS oi ON o.id == oi.order_id
    ORDER BY SUM(oi.total_sale_price) DESC
    GROUP BY o.customer_id
    LIMIT 100
    ) AS top_100_users ON u.id == top_100_users.id
WHERE ...

Tabelle derivate basate su SQL

Per creare una tabella derivata basata su SQL, definisci una query SQL direttamente in LookML utilizzando il parametro sql all'interno del parametro derived_table di una vista. In questo modo puoi definire le colonne della tabella derivata utilizzando SQL.

Ad esempio, il seguente codice LookML di esempio definisce una tabella derivata denominata top_100_users che identifica i 100 utenti principali con la spesa totale più elevata in tutti i loro ordini:

view: top_100_users {
  derived_table: {
    sql: SELECT o.user_id as id
         FROM orders AS o
         INNER JOIN order_items AS oi ON o.id == oi.order_id
         ORDER BY SUM(oi.total_sale_price) DESC
         GROUP BY o.customer_id
         LIMIT 100 ;;
  }

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

Quando nella query di esplorazione viene fatto riferimento alla vista top_100_users, Looker utilizza questa istruzione SQL SELECT nel codice SQL generato come CTE o vista in linea, a seconda del dialetto.

L'utilizzo di SQL per definire le tabelle derivate può avere alcune limitazioni. Nell'esempio della sezione delle tabelle derivate basate su SQL, ad esempio, valgono le seguenti considerazioni:

  • La relazione tra le tabelle orders e order_items è probabilmente già definita nel modello LookML. Se i nomi delle tabelle sottostanti nel database cambiano, i nomi delle tabelle devono essere aggiornati in più posizioni, inclusa la definizione della tabella derivata basata su SQL.
  • Il codice SQL nella definizione della tabella derivata deve essere nel dialetto corretto per il database sottostante. Se i dati vengono spostati in un altro database, la definizione della tabella derivata basata su SQL deve essere modificata.

Per questi motivi, le tabelle derivate native sono spesso una scelta migliore.

Tabelle derivate native

In Looker, le tabelle derivate native sono definite con LookML. A differenza dell'utilizzo delle tabelle derivate basate su SQL, quando definisci una tabella derivata nativa, utilizzi il modello LookML nei seguenti modi:

  • Se il nome della tabella cambia nel database, devi aggiornarlo una sola volta nel modello LookML. Poiché la tabella derivata nativa fa riferimento all'oggetto LookML esistente in cui è definita la tabella di database, farà automaticamente riferimento alla tabella appropriata.
  • Analogamente, se passi a un dialetto diverso, il codice LookML per la tabella derivata nativa sarà comunque valido perché Looker genera il codice SQL appropriato per la connessione al database.

Puoi definire manualmente il LookML per la tabella derivata nativa. Tuttavia, il modo più semplice per creare una tabella derivata nativa è chiedere a Looker di creare la tabella derivata da una query di esplorazione. Nell'esplorazione, seleziona i campi da utilizzare nella tabella derivata e utilizza l'opzione Ottieni LookML > Tabella derivata dal menu a forma di ingranaggio dell'esplorazione per ottenere il codice LookML. Looker genera il codice LookML necessario per creare una tabella derivata dalla query di esplorazione, incluse le definizioni dei campi pertinenti del modello LookML necessarie per creare le colonne della tabella derivata. Puoi copiare il codice LookML in un file di vista del progetto per creare la tabella derivata.

L'esempio seguente mostra una tabella derivata nativa che identifica i 100 utenti principali con la spesa totale più elevata in tutti i loro ordini:

view: top_100_users {
  derived_table: {
    explore_source:  orders {
      column: id {
        field: orders.customer_id
      }
      sorts: [order_items.sum_total_sale_price desc]
      limit: 100
    }
  }
}

Quando esegui una query su una tabella derivata nativa in un'esplorazione, tutti i dettagli della query SQL per la tabella derivata nativa vengono generati automaticamente. Le definizioni delle colonne di una vista LookML con una tabella derivata nativa vengono dedotte dalle definizioni dell'esplorazione sottostante, eliminando la necessità di ripetere le definizioni.

Tabelle derivate permanenti (PDT)

In Looker puoi creare sia tabelle derivate temporanee sia tabelle derivate permanenti. Dopo aver creato una tabella derivata basata su SQL o una tabella derivata nativa, puoi aggiungere la persistenza in modo che Looker scriva la tabella in uno schema scratch nel tuo database e la rigeneri secondo la pianificazione specificata. Per ulteriori informazioni, consulta la sezione Tabelle derivate in Looker.