BigQuery unterstützt verschachtelte Datensätze in Tabellen. Verschachtelte Datensätze können ein einzelner Datensatz sein oder wiederkehrende Werte enthalten. Diese Seite bietet einen Überblick über die Arbeit mit verschachtelten BigQuery-Daten in Looker.
Vorteile verschachtelter Datensätze
Die Verwendung verschachtelter Datensätze beim Scannen eines verteilten Datasets bietet einige Vorteile:
- Für verschachtelte Datensätze sind keine Joins erforderlich. Das bedeutet, dass Berechnungen schneller sein können und viel weniger Daten scannen, als wenn Sie die zusätzlichen Daten bei jeder Abfrage neu verknüpfen müssten.
- Verschachtelte Strukturen sind im Wesentlichen vorab verknüpfte Tabellen. Wenn Sie nicht auf die verschachtelte Spalte verweisen, entstehen keine zusätzlichen Kosten für die Abfrage, da BigQuery-Daten in Spalten gespeichert werden. Wenn Sie auf die verschachtelte Spalte verweisen, entspricht die Logik einem zusammenhängenden Join.
- Durch verschachtelte Strukturen werden wiederholte Daten vermieden, die in einer breiten, denormalisierten Tabelle wiederholt werden müssten. Mit anderen Worten, für eine Person, die in fünf Städten gelebt hat, würde eine breite denormalisierte Tabelle alle ihre Informationen in fünf Zeilen enthalten (eine für jede der Städte, in denen sie gelebt hat). In einer verschachtelten Struktur nehmen die wiederholten Informationen nur eine Zeile ein, da das Array mit fünf Städten in einer einzigen Zeile enthalten und bei Bedarf entschachtelt werden kann.
Mit verschachtelten Datensätzen in LookML arbeiten
Die folgende BigQuery-Tabelle persons_living zeigt ein typisches Schema mit Beispielnutzerdaten, darunter fullName, age, phoneNumber und citiesLived sowie den Datentyp und den Modus jeder Spalte. Das Schema zeigt, dass sich die Werte in der Spalte citiesLived wiederholen. Dies deutet darauf hin, dass einige Nutzer möglicherweise in mehreren Städten gelebt haben:
Das folgende Beispiel ist die LookML für die Explores und Ansichten, die Sie anhand des vorherigen Schemas erstellen können. Es gibt drei Ansichten: persons, persons_cities_lived und persons_phone_number. Der Explore sieht identisch aus wie ein Explore, das mit nicht verschachtelten Tabellen geschrieben wird.
Hinweis: Im folgenden Beispiel sind alle Komponenten (Ansichten und Explore) in einem Codeblock geschrieben. Es empfiehlt sich jedoch, Ansichten in einzelnen Ansichtsdateien zu platzieren und Explores und die connection:-Spezifikation in der Modelldatei zu platzieren.
-- 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]
}
}
Die einzelnen Komponenten für die Arbeit mit verschachtelten Daten in LookML werden in den folgenden Abschnitten ausführlicher erläutert:
Aufrufe
Jeder verschachtelte Datensatz wird als Ansicht geschrieben. In der Ansicht phoneNumber werden beispielsweise einfach die Dimensionen deklariert, die im Datensatz enthalten sind:
view: persons_phone_number {
dimension: areaCode {label: "Area Code"}
dimension: number {}
}
Die Ansicht persons_cities_lived ist komplexer. Wie im LookML-Beispiel gezeigt, definieren Sie die Dimensionen, die im Datensatz enthalten sind (numberOfYears und place). Sie können jedoch auch einige Messwerte definieren. Die Kennzahlen und drill_fields werden wie gewohnt definiert, als ob diese Daten in einer eigenen Tabelle wären. Der einzige echte Unterschied besteht darin, dass Sie id als primary_key deklarieren, damit die Summen richtig berechnet werden.
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]
}
}
Erklärungen aufzeichnen
In der Ansicht, die die Teildatensätze enthält (in diesem Fall persons), müssen Sie die Datensätze deklarieren. Sie werden beim Erstellen der Joins verwendet. Sie können diese LookML-Felder mit dem Parameter hidden ausblenden, da Sie sie bei der Untersuchung der Daten nicht benötigen.
view: persons {
...
dimension: citiesLived {
hidden:yes
}
dimension: phoneNumber {
hidden:yes
}
...
}
Joins
Verschachtelte Einträge in BigQuery sind Arrays von STRUCT-Elementen. Die Join-Beziehung wird nicht über einen sql_on-Parameter, sondern in die Tabelle eingebunden. In diesem Fall können Sie den Join-Parameter sql: für den UNNEST-Operator verwenden. Abgesehen von dieser Abweichung entspricht das Aufheben des Verschachtelungsgrades eines Arrays von STRUCT-Elementen genau dem Zusammenführen einer Tabelle.
Bei nicht wiederholten Einträgen können Sie einfach STRUCT verwenden. Wenn Sie daraus ein Array mit STRUCT-Elementen erstellen möchten, setzen Sie es in eckige Klammern. Das mag zwar seltsam erscheinen, aber es scheint keine Leistungseinbußen zu geben. So bleiben die Dinge übersichtlich und einfach.
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
}
}
Zusammenführen von Arrays ohne eindeutige Schlüssel für jede Zeile
Es ist zwar am besten, eindeutige natürliche Schlüssel in den Daten oder im ETL-Prozess erstellte Ersatzschlüssel zu haben, dies ist jedoch nicht immer möglich. Es kann beispielsweise vorkommen, dass einige Arrays keinen relativ eindeutigen Schlüssel für die Zeile haben. Hier kann WITH OFFSET in der Join-Syntax hilfreich sein.
Beispielsweise kann eine Spalte, die eine Person darstellt, mehrmals geladen werden, wenn die Person in mehreren Städten gelebt hat (z. B. Chicago, Denver oder San Francisco). Es kann schwierig sein, einen Primärschlüssel für die nicht verschachtelte Zeile zu erstellen, wenn kein Datum oder ein anderer identifizierbarer natürlicher Schlüssel angegeben wurde, um die Dauer der Zugehörigkeit der Person in den einzelnen Städten zu unterscheiden. Hier kann WITH OFFSET eine relative Zeilennummer (0,1,2,3) für jede nicht verschachtelte Zeile angeben. Dieser Ansatz garantiert einen eindeutigen Schlüssel für die nicht verschachtelte Zeile:
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;;
}
}
Einfache wiederholte Werte
Verschachtelte Daten in BigQuery können auch einfache Werte wie Ganzzahlen oder Strings sein. Um die Verschachtelung von Arrays mit einfachen wiederholten Werten aufzulösen, können Sie einen ähnlichen Ansatz wie zuvor verwenden und den UNNEST-Operator in einem Join verwenden.
Im folgenden Beispiel wird ein bestimmtes Array von Ganzzahlen, „unresolved_skus“, entschachtelt:
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} ;;
}
}
Der Parameter sql für das Array von Ganzzahlen, unresolved_skus, wird als ${TABLE} dargestellt. Damit wird direkt auf die Wertetabelle verwiesen, die dann in explore entschachtelt wird.