Concetti SQL per le tabelle derivate

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

In questa guida sono trattati i seguenti argomenti:

Come Looker genera l'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 inline, 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 di tabella comune per eseguire 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 ...

Il seguente esempio 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 esempio di LookML definisce una tabella derivata denominata top_100_users che identifica i 100 utenti 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 viene fatto riferimento alla vista top_100_users in una query di esplorazione, Looker utilizza questa istruzione SQL SELECT nell'SQL generato come CTE o vista in linea, a seconda del dialetto.

L'utilizzo di SQL per definire le tabelle derivate può presentare alcune limitazioni. Nell'esempio della sezione sulle tabelle derivate basate su SQL, ad esempio, si applicano 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 database diverso, è necessario modificare la definizione della tabella derivata basata su SQL.

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 di tabelle derivate basate su SQL, quando definisci una tabella derivata nativa, sfrutti 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 punta all'oggetto LookML esistente in cui è definita la tabella del database, farà automaticamente riferimento alla tabella appropriata.
  • Allo stesso modo, se passi a un dialetto diverso, il codice LookML per la tabella derivata nativa sarà comunque valido perché Looker genera l'SQL appropriato per la connessione al database.

Puoi definire manualmente il codice LookML per la tabella derivata nativa. Tuttavia, il modo più semplice per creare una tabella derivata nativa è farla creare a Looker da una query di Esplora. In Esplora, seleziona i campi che vuoi utilizzare nella tabella derivata e utilizza l'opzione Ottieni LookML > Tabella derivata dal menu a forma di ingranaggio di Esplora 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 visualizzazione del progetto per creare la tabella derivata.

Il seguente esempio mostra una tabella derivata nativa che identifica i 100 utenti 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 temporaneo del database e la rigeneri in base alla pianificazione specificata. Per ulteriori informazioni, vedi Tabelle derivate in Looker.