BigQuery supporta i record nidificati nelle tabelle. I record nidificati possono essere un singolo record o contenere valori ripetuti. Questa pagina fornisce una panoramica del lavoro con i dati nidificati di BigQuery in Looker.
Vantaggi dei record nidificati
L'utilizzo di record nidificati presenta alcuni vantaggi quando esegui la scansione di un set di dati distribuito:
- I record nidificati non richiedono join. Ciò significa che i calcoli possono essere più rapidi e scansionare molti meno dati rispetto a quanto accade se devi riunire i dati aggiuntivi ogni volta che esegui una query.
- Le strutture nidificate sono essenzialmente tabelle con join preliminare. Non sono previsti costi aggiuntivi per la query se non fai riferimento alla colonna nidificata, perché i dati BigQuery sono archiviati in colonne. Se fai riferimento alla colonna nidificata, la logica è identica a quella di un join con collocazione nello stesso luogo.
- Le strutture nidificate evitano di ripetere i dati che dovrebbero essere ripetuti in una tabella denormalizzata di grandi dimensioni. In altre parole, per una persona che ha vissuto in cinque città, un'ampia tabella denormalizzata conterrà tutte le informazioni in cinque righe (una per ciascuna delle città in cui ha vissuto). In una struttura nidificata, le informazioni ripetute richiedono una sola riga poiché l'array di cinque città può essere contenuto in una singola riga e, se necessario, non nidificato.
Utilizzo dei record nidificati in LookML
La seguente tabella BigQuery, persons_living, mostra uno schema tipico che memorizza dati utente di esempio, tra cui fullName, age, phoneNumber e citiesLived, nonché il tipo di dati e la modalità di ogni colonna. Lo schema mostra che i valori nella colonna citiesLived sono ripetuti, il che indica che alcuni utenti potrebbero aver vissuto in più città:
L'esempio seguente è il codice LookML per le esplorazioni e le visualizzazioni che puoi creare dallo schema precedente mostrato. Esistono tre visualizzazioni: persons, persons_cities_lived e persons_phone_number. L'esplorazione sembra identica a un'esplorazione scritta con tabelle non nidificate.
Nota: sebbene tutti i componenti (visualizzazioni ed esplorazione) siano scritti in un blocco di codice nell'esempio seguente, è consigliabile inserire le visualizzazioni in singoli file di vista e inserire esplorazioni e connection: specifiche nel file del modello.
-- model file
connection: "bigquery_publicdata_standard_sql"
explore: persons {
# Repeated nested object
join: persons_cities_lived {
view_label: "Persons: Cities Lived:"
sql: LEFT JOIN UNNEST(persons.citiesLived) as persons_cities_lived ;;
relationship: one_to_many
}
# Non repeated nested object
join: persons_phone_number {
view_label: "Persons: Phone:"
sql: LEFT JOIN UNNEST([${persons.phoneNumber}]) as persons_phone_number ;;
relationship: one_to_one
}
}
-- view files
view: persons {
sql_table_name: bigquery-samples.nested.persons_living ;;
dimension: id {
primary_key: yes
sql: ${TABLE}.fullName ;;
}
dimension: fullName {label: "Full Name"}
dimension: kind {}
dimension: age {type:number}
dimension: citiesLived {hidden:yes}
dimension: phoneNumber {hidden:yes}
measure: average_age {
type: average
sql: ${age} ;;
drill_fields: [fullName,age]
}
measure: count {
type: count
drill_fields: [fullName, cities_lived.place_count, age]
}
}
view: persons_phone_number {
dimension: areaCode {label: "Area Code"}
dimension: number {}
}
view: persons_cities_lived {
dimension: id {
primary_key: yes
sql: CONCAT(CAST(${persons.fullName} AS STRING),'|', CAST(${place} AS STRING)) ;;
}
dimension: place {}
dimension: numberOfYears {
label: "Number Of Years"
type: number
}
measure: place_count {
type: count
drill_fields: [place, persons.count]
}
measure: total_years {
type: sum
sql: ${numberOfYears} ;;
drill_fields: [persons.fullName, persons.age, place, numberOfYears]
}
}
Ogni componente per la gestione dei dati nidificati in LookML è descritto in maggiore dettaglio nelle sezioni seguenti:
Visualizzazioni
Ogni record nidificato viene scritto come una vista. Ad esempio, la vista phoneNumber dichiara semplicemente le dimensioni che compaiono nel record:
view: persons_phone_number {
dimension: areaCode {label: "Area Code"}
dimension: number {}
}
La visualizzazione persons_cities_lived è più complessa. Come mostrato nell'esempio di LookML, definisci le dimensioni che vengono visualizzate nel record (numberOfYears e place), ma puoi anche definire alcune misure. Le misure e drill_fields sono definite come al solito, come se questi dati fossero in una propria tabella. L'unica differenza reale è che devi dichiarare id come primary_key in modo che gli aggregati vengano calcolati correttamente.
view: persons_cities_lived {
dimension: id {
primary_key: yes
sql: CONCAT(CAST(${persons.fullName} AS STRING),'|', CAST(${place} AS STRING)) ;;
}
dimension: place {}
dimension: numberOfYears {
label: "Number Of Years"
type: number
}
measure: place_count {
type: count
drill_fields: [place, persons.count]
}
measure: total_years {
type: sum
sql: ${numberOfYears} ;;
drill_fields: [persons.fullName, persons.age, place, numberOfYears]
}
}
Registra dichiarazioni
Nella vista che contiene i sottorecord (in questo caso persons), devi dichiarare i record. Verranno utilizzati quando crei i join. Puoi nascondere questi campi LookML con il parametro hidden perché non ti serviranno durante l'esplorazione dei dati.
view: persons {
...
dimension: citiesLived {
hidden:yes
}
dimension: phoneNumber {
hidden:yes
}
...
}
Unioni
I record nidificati in BigQuery sono array di elementi STRUCT. Anziché eseguire il join con un parametro sql_on, la relazione di join è incorporata nella tabella. In questo caso, puoi utilizzare il parametro di join sql: in modo da poter utilizzare l'operatore UNNEST. A parte questa differenza, separare un array di elementi STRUCT è esattamente come unire una tabella.
Nel caso di record non ripetuti, puoi semplicemente usare STRUCT. puoi convertirlo in un array di elementi STRUCT inserendolo tra parentesi quadre. Anche se può sembrare strano, sembra che non ci siano penalità in termini di prestazioni, il che garantisce la massima chiarezza e semplicità.
explore: persons {
# Repeated nested object
join: persons_cities_lived {
view_label: "Persons: Cities Lived:"
sql: LEFT JOIN UNNEST(persons.citiesLived) as persons_cities_lived ;;
relationship: one_to_many
}
# Non repeated nested object
join: persons_phone_number {
view_label: "Persons: Phone:"
sql: LEFT JOIN UNNEST([${persons.phoneNumber}]) as persons_phone_number ;;
relationship: one_to_one
}
}
Unioni per array senza chiavi univoche per ogni riga
Sebbene sia preferibile avere chiavi naturali identificabili nei dati o chiavi surrogate create nel processo ETL, non è sempre possibile. Ad esempio, potresti riscontrare una situazione in cui alcuni array non hanno una chiave univoca relativa per la riga. È qui che WITH OFFSET può tornare utile nella sintassi di join.
Ad esempio, una colonna che rappresenta una persona potrebbe essere caricata più volte se la persona ha vissuto in più città, ad esempio Chicago, Denver, San Francisco e così via. Può essere difficile creare una chiave primaria nella riga non nidificata se non viene fornita una data o un'altra chiave naturale identificabile per distinguere il periodo di permanenza della persona in ogni città. In questo caso, WITH OFFSET può fornire un numero di riga relativo (0, 1, 2, 3) per ogni riga non nidificata. Questo approccio garantisce una chiave univoca nella riga non nidificata:
explore: persons {
# Repeated nested Object
join: persons_cities_lived {
view_label: "Persons: Cities Lived:"
sql: LEFT JOIN UNNEST(persons.citiesLived) as persons_cities_lived WITH OFFSET as person_cities_lived_offset;;
relationship: one_to_many
}
}
view: persons_cities_lived {
dimension: id {
primary_key: yes
sql: CONCAT(CAST(${persons.fullName} AS STRING),'|', CAST(${offset} AS STRING)) ;;
}
dimension: offset {
type: number
sql: person_cities_lived_offset;;
}
}
Valori ripetuti semplici
I dati nidificati in BigQuery possono essere anche valori semplici, come numeri interi o stringhe. Per separare gli array di semplici valori ripetuti, puoi utilizzare un approccio simile a quello mostrato in precedenza, utilizzando l'operatore UNNEST in un join.
L'esempio seguente separa un determinato array di numeri interi, "unresolved_skus":
explore: impressions {
join: impressions_unresolved_sku {
sql: LEFT JOIN UNNEST(unresolved_skus) AS impressions_unresolved_sku ;;
relationship: one_to_many
}
}
view: impressions_unresolved_sku {
dimension: sku {
type: string
sql: ${TABLE} ;;
}
}
Il parametro sql per l'array di numeri interi, unresolved_skus, è rappresentato come ${TABLE}. Questo fa riferimento direttamente alla tabella dei valori stessa, che viene poi estratta da explore.