Concetti SQL per le tabelle derivate

Sebbene le visualizzazioni LookML siano in genere basate sulle tabelle esistenti nel database, puoi anche creare visualizzazioni 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 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 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, nel seguente esempio LookML definisce una tabella derivata denominata top_100_users che identifica i primi 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 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, devono essere aggiornati in più punti, incluso nella 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 in modo nativo sono spesso una scelta migliore.

Tabelle derivate native

In Looker, le tabelle derivate native sono definite con LookML. A differenza di utilizzare le tabelle derivate basate su SQL, quando definisci una tabella derivata nativa, sfrutti il tuo 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 di database, la tabella derivata nativa 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 Esplora. In Esplora, seleziona i campi che vuoi utilizzare nella tabella derivata e usa il pulsante Ottieni LookML > Tabella derivata dal menu a forma di ingranaggio Esplora per recuperare il LookML. Looker genera il LookML necessario per creare una tabella derivata dalla query Esplora, incluse le definizioni dei campi pertinenti del modello LookML necessarie per creare le colonne per la tabella derivata. Puoi copiare il LookML in un file di vista nel tuo progetto per creare la tabella derivata.

L'esempio seguente mostra una tabella derivata nativa che identifica i primi 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 tutte le definizioni.

Tabelle derivate permanenti (PDT)

In Looker puoi creare sia tabelle derivate temporanee che 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.