Tutorial sull'aggregazione dei dati

Per ulteriori dettagli, consulta la pagina della documentazione relativa all'awareness degli aggregati.

Introduzione

Questa pagina è una guida per implementare la consapevolezza aggregata in uno scenario pratico, inclusa l'identificazione delle opportunità di implementazione, il valore generato dalla consapevolezza aggregata e un semplice flusso di lavoro per implementarla in un modello reale. Questa pagina non è una spiegazione approfondita di tutte le funzionalità di riconoscimento degli aggregati o di casi limite, né è un catalogo esaustivo di tutte le sue funzionalità.

Che cos'è il riconoscimento degli aggregati?

In Looker, esegui principalmente query su tabelle o viste non elaborate nel tuo database. A volte si tratta di tabelle derivate permanenti (PDT) di Looker.

Spesso potresti incontrare set di dati o tabelle molto grandi che, per avere prestazioni ottimali, richiedono tabelle di aggregazione o raggruppamenti.

In genere, puoi creare tabelle di aggregazione come una tabella orders_daily che contiene una dimensionalità limitata. Questi aspetti devono essere trattati separatamente e modellati separatamente nell'esplorazione e non vanno inseriti nel modello in modo ordinato. Questi limiti comportano un'esperienza utente scadente quando l'utente deve scegliere tra più esplorazioni per gli stessi dati.

Ora, grazie al riconoscimento degli aggregati di Looker, puoi precompilare le tabelle aggregate a vari livelli di granularità, dimensionalità e aggregazione. e puoi informare Looker su come usarle nelle esplorazioni esistenti. Le query sfrutteranno quindi queste tabelle di aggregazione laddove Looker lo ritiene opportuno, senza alcun input utente'utente. Ciò ridurrà le dimensioni delle query, ridurrà i tempi di attesa e migliorerà l'esperienza utente.

NOTA: le tabelle aggregate di Looker sono un tipo di tabella derivata permanente (PDT). Ciò significa che le tabelle aggregate hanno gli stessi requisiti di database e connessione delle PDT.

Per verificare se il dialetto del database e la connessione a Looker possono supportare le PDT, consulta i requisiti elencati nella pagina della documentazione Tabelle derivate in Looker.

Per verificare se il dialetto del tuo database supporta il riconoscimento degli aggregati, consulta la pagina della documentazione Consapevolezza aggregata.

Il valore del riconoscimento aggregato

Esistono diverse proposte di valore significative, offerte di awareness aggregate per ottenere valore aggiunto dal modello Looker esistente:

• Miglioramento del rendimento: l'implementazione del riconoscimento degli aggregati velocizza le query degli utenti. Looker utilizzerà una tabella più piccola se contiene i dati necessari per completare la query dell'utente.
• Risparmio sui costi: alcuni dialetti vengono addebitati in base alla dimensione della query su un modello di consumo. Se Looker esegue query su tabelle più piccole, il costo per query utente sarà ridotto.
• Miglioramento dell'esperienza utente: oltre a un'esperienza migliorata che recupera le risposte più velocemente, il consolidamento elimina la creazione di esplorazioni ridondanti.
• Riduzione dell'impronta di LookML: la sostituzione delle strategie esistenti per la consapevolezza degli aggregati basate su liquidi con un'implementazione nativa e flessibile porta a una maggiore resilienza e a un minor numero di errori.
• Possibilità di sfruttare LookML esistente: le tabelle aggregate utilizzano l'oggetto query, che riutilizza la logica modellata esistente anziché duplicare la logica con SQL personalizzato esplicito.

Esempio di base

Ecco un'implementazione molto semplice in un modello Looker per dimostrare quanto possa essere leggera la consapevolezza degli aggregati. Data un'ipotetica tabella flights nel database con una riga per ogni volo registrato tramite FAA, possiamo modellare questa tabella in Looker con una propria vista ed esplorazione. Ecco il LookML per una tabella aggregata che possiamo definire per l'esplorazione:

  explore: flights {
    aggregate_table: flights_by_week_and_carrier {
      query: {
        dimensions: [carrier, depart_week]
        measures: [cancelled_count, count]
      }

      materialization: {
        sql_trigger_value: SELECT CURRENT-DATE;;
      }
    }
  }

Con questa tabella aggregata, un utente può eseguire query sull'esplorazione flights e Looker sfrutterà automaticamente la tabella aggregata definita in LookML e la userà per rispondere alle query. L'utente non dovrà informare Looker di eventuali condizioni speciali: se la tabella è adatta ai campi selezionati dall'utente, Looker la utilizzerà.

Gli utenti con autorizzazioni see_sql possono utilizzare i commenti nella scheda SQL di un'esplorazione per vedere quale tabella aggregata verrà utilizzata per una query. Ecco un esempio di scheda SQL di Looker per una query che utilizza la tabella aggregata flights:flights_by_week_and_carrier in teach_scratch:

Consulta la pagina della documentazione Consapevolezza degli aggregati per informazioni dettagliate su come determinare se vengono utilizzate tabelle aggregate per una query.

Identificare le opportunità

Per massimizzare i vantaggi della consapevolezza aggregata, devi identificare i punti in cui quest'ultima può svolgere un ruolo importante nell'ottimizzazione o nel generare il valore della consapevolezza aggregata.

Identifica le dashboard con un runtime elevato

Una grande opportunità per il rilevamento degli aggregati è la creazione di tabelle aggregate per le dashboard molto utilizzate con un runtime molto elevato. I tuoi utenti potrebbero venire a conoscenza di dashboard lente, ma se hai see_system_activity, puoi anche utilizzare l'esplorazione della cronologia delle attività del sistema di Looker per trovare dashboard con un tempo di esecuzione più lento della media. Come scorciatoia, puoi aprire questo link Esplora la cronologia delle attività di sistema in un browser, quindi sostituire "nome host" nell'URL con il nome dell'istanza di Looker. Vedrai una visualizzazione Esplora con i dati sulle dashboard dell'istanza, tra cui Titolo, Cronologia, Conteggio di esplorazioni, Razione dalla cache rispetto al database e Il rendimento è peggiore della media:

In questo esempio, sono presenti diverse dashboard con un utilizzo elevato che hanno prestazioni peggiori della media, come la dashboard Visualizzazioni di esempio. La dashboard Visualizzazioni di esempio utilizza due esplorazioni, quindi una buona strategia sarebbe creare tabelle aggregate per entrambe le esplorazioni.

Identifica le esplorazioni lente e con molte query di ricerca da parte degli utenti

Un'altra opportunità per ottenere la consapevolezza dei dati aggregati è costituita dalle esplorazioni che generano molte query da parte degli utenti e che hanno una risposta alle query inferiore alla media.

Puoi utilizzare l'esplorazione della cronologia delle attività del sistema come punto di partenza per identificare opportunità di ottimizzazione delle esplorazioni. Come scorciatoia, puoi aprire il link a Esplora cronologia attività del sistema in un browser, quindi sostituire "nome host" nell'URL con il nome dell'istanza di Looker. Vedrai una visualizzazione di esplorazione con i dati relativi alle esplorazioni dell'istanza, tra cui Esplora, Modello, Conteggio esecuzioni query, Conteggio utenti e Tempo di esecuzione medio in secondi:

In Esplorazione della cronologia puoi identificare i seguenti tipi di esplorazioni nell'istanza:

• Esplorazioni su cui gli utenti eseguono query (anziché quelle provenienti dall'API o da caricamenti pianificati)
• Esplorazioni sottoposte spesso a query
• Esplora con un rendimento scarso (rispetto ad altre esplorazioni)

Nell'esempio precedente della cronologia delle attività del sistema, le esplorazioni flights e order_items sono probabili candidati per l'implementazione dell'awareness aggregata.

Identifica i campi più utilizzati nelle query

Infine, puoi identificare altre opportunità a livello di dati comprendendo i campi che gli utenti includono comunemente nelle query e nei filtri.

Utilizza l'esplorazione sull'utilizzo del campo delle attività del sistema per comprendere i campi più comuni all'interno delle esplorazioni che hai identificato come lenti e molto utilizzate. Come scorciatoia, puoi aprire questo link Esplora sull'utilizzo dei campi delle attività di sistema in un browser e sostituire "nome host" nell'URL con il nome dell'istanza di Looker. Sostituisci i filtri di conseguenza. Viene visualizzata un'esplorazione con un grafico a barre che indica il numero di volte in cui un campo è stato utilizzato in una query:

Nell'esempio di esplorazione dell'attività di sistema mostrata nell'immagine, puoi vedere che flights.count e flights.depart_week sono i due campi selezionati più di frequente per l'esplorazione. Pertanto, questi campi sono ideali per essere inclusi nelle tabelle aggregate.

Dati concreti come questi sono utili, ma ci sono elementi soggettivi che guideranno i criteri di selezione. Ad esempio, esaminando i quattro campi precedenti, puoi tranquillamente supporre che gli utenti di solito considerino il numero di voli pianificati e il numero di voli cancellati e che vogliano suddividere i dati sia per settimana che per vettore. Questo è un esempio di combinazione chiara, logica e reale di campi e metriche.

Riepilogo

I passaggi in questa pagina della documentazione devono servire da guida per trovare dashboard, esplorazioni e campi che devono essere presi in considerazione per l'ottimizzazione. È anche opportuno capire che tutte e tre le dashboard possono essere si escludono a vicenda: le dashboard problematiche potrebbero non essere basate sulle esplorazioni problematiche e la creazione di tabelle aggregate con i campi di uso comune potrebbe non essere d'aiuto in queste dashboard. È possibile che si tratti di tre implementazioni di consapevolezza aggregate discrete.

Progettazione di tabelle aggregate

Dopo aver identificato le opportunità per l'identificazione degli aggregati, puoi progettare tabelle aggregate che rispondono al meglio a queste opportunità. Consulta la pagina della documentazione Consapevolezza dell'aggregazione per informazioni su campi, misure e periodi di tempo supportati nelle tabelle aggregate, nonché altre linee guida per la progettazione di tabelle aggregate.

NOTA:per utilizzare la query, le tabelle aggregate non devono corrispondere esattamente. Se la query ha una granularità alla settimana e hai una tabella di aggregazione giornaliera, Looker utilizzerà la tabella aggregata anziché la tabella non elaborata a livello di timestamp. Allo stesso modo, se una tabella aggregata è raggruppata al livello brand e date e la query di un utente solo a livello brand, è comunque idonea a essere utilizzata da Looker per il rilevamento aggregato.

L'awareness aggregata è supportata per le seguenti misure:

• Misure standard : misure di tipo SUM, COUNT, AVERAGE, MIN e MAX
• Misure composte : misure di tipo NUMBER, STRING, YESNO e DATE
• Approssimazioni distinte per misure: dialetti che possono utilizzare la funzionalità HyperLogLog

L'awareness aggregata non è supportata per le seguenti misure:

• Misure distinte: poiché la distinzione può essere calcolata solo su dati atomici e non aggregati, le misure *_DISTINCT non sono supportate al di fuori di queste approssimazioni che utilizzano HyperLogLog.
• Misure basate sulla cardinalità: come per le misure distinte, le mediane e i percentili non possono essere preaggregati e non sono supportati. 

NOTA: se sei a conoscenza di una potenziale query dell'utente con tipi di misure non supportati dal riconoscimento degli aggregati, in questo caso ti conviene creare una tabella aggregata che corrisponda esattamente a una query. È possibile utilizzare una tabella aggregata che corrisponde esattamente alla query per rispondere a una query con tipi di misure che altrimenti non sarebbero supportati per il rilevamento degli aggregati.

Granularità della tabella aggregata

Prima di creare tabelle per combinazioni di dimensioni e misure, devi determinare i modelli di utilizzo comuni e la selezione dei campi in modo da creare tabelle aggregate che verranno utilizzate il più spesso possibile con il maggiore impatto. Tieni presente che tutti i campi utilizzati nella query (selezionati o filtrati) devono trovarsi nella tabella aggregata affinché la tabella venga utilizzata per la query. Tuttavia, come indicato in precedenza, la tabella aggregata non deve necessariamente corrispondere esattamente a quella di una query utilizzata per la query. Puoi gestire molte potenziali query degli utenti all'interno di un'unica tabella aggregata e registrare comunque notevoli miglioramenti del rendimento.

Dall'esempio di identificazione dei campi più utilizzati nelle query, esistono due dimensioni (flights.depart_week e flights.carrier), selezionate molto spesso, nonché due misure (flights.count e flights.cancelled_count). Sarebbe quindi logico creare una tabella aggregata che utilizzi tutti e quattro questi campi. Inoltre, la creazione di una singola tabella aggregata per flights_by_week_and_carrier comporterà un utilizzo più frequente delle tabelle aggregate rispetto a quelle di due diverse tabelle aggregate per flights_by_week e flights_by_carrier.

Ecco un esempio di tabella aggregata che potremmo creare per le query sui campi comuni:

  explore: flights {
    aggregate_table: flights_by_week_and_carrier {
      query: {
        dimensions: [carrier, depart_week]
        measures: [cancelled_count, count]
      }

      materialization: {
        sql_trigger_value: SELECT CURRENT-DATE;;
      }
    }
  }

I tuoi utenti aziendali e le prove aneddotiche, nonché i dati dell'attività di sistema di Looker, possono aiutarti a guidare il processo decisionale.

Bilanciare applicabilità e prestazioni

L'esempio seguente mostra una query Esplora dei campi Settimana di partenza dei voli, Corriere dei dettagli dei voli, Conteggio voli e Conteggio dettagliato dei voli annullati dalla tabella aggregata flights_by_week_and_carrier:

L'esecuzione di questa query dalla tabella del database originale ha richiesto 15,8 secondi e ha analizzato 38 milioni di righe senza join utilizzando Amazon Redshift. Il pivot della query, che sarebbe una normale operazione dell'utente, ha richiesto 29,5 secondi.

Dopo aver implementato la tabella aggregata flights_by_week_and_carrier, la query successiva ha richiesto 7,2 secondi e ha analizzato 4592 righe. Si tratta di una riduzione del 99,98% delle dimensioni della tabella. Il pivot della query ha richiesto 9,8 secondi.

Dall'esplorazione sull'utilizzo del campo delle attività del sistema, possiamo vedere con quale frequenza i nostri utenti includono questi campi nelle query. In questo esempio, flights.count è stato utilizzato 47.848 volte, flights.depart_week è stato utilizzato 18.169 volte, flights.cancelled_count è stato utilizzato 16.570 volte e flights.carrier è stato utilizzato 13.517 volte.

Anche se abbiamo stimato molto modestamente che il 25% di queste query ha utilizzato tutti e 4 i campi nel modo più semplice (semplice selezione, nessun pivot), 3379 x 8,6 secondi = 8 ore, 4 minuti di tempo di attesa aggregato degli utenti eliminati.

NOTA: il modello di esempio utilizzato qui è molto basilare. Questi risultati non devono essere utilizzati come benchmark o riferimento per il modello.

Dopo aver applicato lo stesso flusso al nostro modello di e-commerce order_items, l'esplorazione più utilizzata nell'istanza ‐ i risultati sono i seguenti:

Origine	Tempo query	Righe scansionate
Tavolo base	13,1 secondi	285.000
Tabella aggregata	5,1 secondi	138.000
Delta	8 secondi	147.000

I campi utilizzati nella query e nella tabella aggregata successiva erano brand, created_date, orders_count e total_revenue, con due join. I campi sono stati utilizzati in totale 11.000 volte. Stimando lo stesso utilizzo combinato di circa il 25%, il risparmio aggregato per gli utenti sarebbe pari a 6 ore e 6 minuti (8 s * 2750 = 22.000 s). La creazione della tabella aggregata ha richiesto 17,9 secondi.

Osservando questi risultati, vale la pena fare un passo indietro e valutare i ritorni potenzialmente ottenuti da:

• Ottimizzare modelli più grandi e complessi/Esplorazioni con "accettabili" il rendimento e potrebbe notare un miglioramento del rendimento derivante da migliori pratiche di definizione del modello

rispetto a

• Utilizzare la consapevolezza degli aggregati per ottimizzare i modelli più semplici che vengono utilizzati più spesso e hanno un rendimento scarso

Vedrai ritorni diminuire per i tuoi sforzi mentre cerchi di ottenere l'ultimo bit di prestazioni da Looker e dal tuo database. Devi sempre conoscere le aspettative di prestazioni di base, in particolare quelle degli utenti aziendali, e le limitazioni imposte dal tuo database (come contemporaneità, soglie di query, costo e così via). Non aspettarti che l'awareness aggregata superi questi limiti.

Inoltre, quando progetti una tabella aggregata, ricorda che avere più campi si tradurrà in una tabella aggregata più grande e più lenta. Le tabelle più grandi possono ottimizzare più query e quindi essere utilizzate in più situazioni, ma quelle grandi non saranno veloci come quelle più piccole e più semplici.

Ad esempio:

  explore: flights {
    aggregate_table: flights_by_week_and_carrier {
      query: {
        dimensions: [carrier, depart_week,flights.distance, flights.arrival_week,flights.cancelled]
        measures: [cancelled_count, count, flights.average_distance, flights.total_distance]
      }

      materialization: {
        sql_trigger_value: SELECT CURRENT-DATE;;
      }
    }
  }

In questo modo, la tabella aggregata verrà utilizzata per qualsiasi combinazione di dimensione mostrata e per qualsiasi misura inclusa, pertanto la tabella potrà essere utilizzata per rispondere a molte query diverse degli utenti. Tuttavia,per utilizzare questa tabella per una semplice query SELECT di carrier e count sarebbe necessaria l'analisi di una tabella di 885.000 righe. Al contrario, la stessa query richiederebbe una scansione di 4592 righe solo se la tabella si basava su due dimensioni. La tabella di 885.000 righe è ancora una riduzione del 97% delle dimensioni della tabella (rispetto ai 38 milioni di righe precedenti); ma l'aggiunta di un'altra dimensione aumenta le dimensioni della tabella a 20 milioni di righe. Di conseguenza, i ritorni diminuisce quando includi più campi nella tabella aggregata per aumentarne l'applicabilità a più query.

Creazione di tabelle aggregate

Riprendendo il nostro esempio Voli Explore che abbiamo identificato come opportunità di ottimizzazione, la strategia migliore sarebbe creare tre diverse tabelle aggregate:

• flights_by_week_and_carrier
• flights_by_month_and_distance
• flights_by_year

Il modo più semplice per creare queste tabelle aggregate è recuperare il LookML della tabella aggregata da una query Esplora o da una dashboard e aggiungere il LookML ai file di progetto Looker.

Dopo aver aggiunto le tabelle aggregate al progetto LookML ed aver eseguito il deployment degli aggiornamenti in produzione, le esplorazioni sfrutteranno le tabelle aggregate per conto degli utenti query.

Persistenza

Per essere accessibili per il rilevamento degli aggregati, le tabelle aggregate devono essere rese persistenti nel database. Una best practice consiste nell'allineare la rigenerazione automatica di queste tabelle aggregate al tuo criterio di memorizzazione nella cache sfruttando i gruppi di dati. Devi utilizzare lo stesso gruppo di dati per una tabella aggregata utilizzata per l'esplorazione associata. Se non puoi utilizzare i gruppi di dati, un'opzione alternativa è utilizzare il parametro sql_trigger_value. Di seguito è riportato un valore generico basato sulle date per il campo sql_trigger_value:

sql_trigger_value: SELECT CURRENT_DATE() ;;

In questo modo, le tabelle aggregate verranno create automaticamente a mezzanotte ogni giorno.

Logica dell'intervallo di tempo

Quando Looker crea una tabella aggregata, includerà i dati fino al momento in cui è stata creata. Tutti i dati aggiunti successivamente alla tabella di base nel database vengono normalmente esclusi dai risultati di una query che utilizza quella tabella aggregata.

Questo diagramma mostra la cronologia di ricezione e registrazione degli ordini nel database rispetto al momento in cui è stata creata la tabella aggregata Ordini. Due ordini ricevuti oggi non saranno presenti nella tabella aggregata Ordini, poiché gli ordini sono stati ricevuti dopo la creazione della tabella aggregata:

Tuttavia, Looker può unire i dati aggiornati nella tabella aggregata quando un utente esegue una query per un periodo di tempo che si sovrappone alla tabella aggregata, come illustrato nello stesso diagramma della cronologia:

Poiché Looker può unire i dati aggiornati a una tabella aggregata, se un utente filtra per un periodo di tempo che si sovrappone alla fine dell'aggregazione e della tabella di base, gli ordini ricevuti dopo la creazione della tabella aggregata verranno inclusi nei risultati dell'utente. Consulta la pagina della documentazione Informazioni sull'aggregazione per informazioni dettagliate e per conoscere le condizioni che devono essere soddisfatte per unire dati aggiornati e aggregare le query delle tabelle.

Riepilogo

Per ricapitolare, per creare un'implementazione di awareness aggregata, sono previsti tre passaggi fondamentali:

1. Identifica le opportunità in cui l'ottimizzazione mediante tabelle aggregate è appropriata e di impatto.
2. Progetta tabelle aggregate che offrano la massima copertura per le query degli utenti più comuni, pur rimanendo sufficientemente piccole da ridurre sufficientemente le dimensioni di queste query.
3. Crea le tabelle aggregate nel modello Looker, associando la persistenza della tabella alla persistenza della cache di esplorazione.