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. Für die Abfrage fallen keine zusätzlichen Kosten an, wenn Sie nicht auf die verschachtelte Spalte verweisen, da BigQuery-Daten in Spalten gespeichert werden. Wenn Sie auf die verschachtelte Spalte verweisen, ist die Logik mit einem zusammengelegten Join identisch.
- Verschachtelte Strukturen vermeiden die Wiederholung von Daten, 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 große 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 wird für die wiederholten Informationen nur eine Zeile benötigt, da das Array aus fünf Städten in einer einzigen Zeile enthalten und bei Bedarf aufgehoben werden kann.
Mit verschachtelten Datensätzen in LookML arbeiten
Die folgende BigQuery-Tabelle persons_living zeigt ein typisches Schema, in dem Beispielnutzerdaten wie fullName, age, phoneNumber und citiesLived sowie der Datentyp und mode jeder Spalte gespeichert werden. 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 der LookML-Code für die Explores und Ansichten, die Sie aus dem oben gezeigten Schema erstellen können. Es gibt drei Ansichten: persons, persons_cities_lived und persons_phone_number. Das Explore ist identisch mit einem Explore, das mit nicht verschachtelten Tabellen geschrieben wird.
</ph>
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. Diese 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 Datensätze 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 davon ist das Aufheben der Verschachtelung eines Arrays von STRUCT-Elementen genau wie das Verbinden einer Tabelle.
Bei nicht wiederkehrenden Datensätzen können Sie einfach STRUCT verwenden. können Sie dies in ein Array von STRUCT-Elementen umwandeln, indem Sie es in eckige Klammern setzen. Das mag zwar seltsam erscheinen, aber es scheint keine Leistungseinbußen zu geben – und so bleibt alles ü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
}
}
Joins für Arrays ohne eindeutige Schlüssel für jede Zeile
Obwohl es am besten ist, identifizierbare natürliche Schlüssel in den Daten zu haben oder Ersatzschlüssel, die im ETL-Prozess erstellt werden, ist dies nicht immer möglich. Es könnte beispielsweise vorkommen, dass einige Arrays keinen relativen eindeutigen Schlüssel für die Zeile haben. Hier kann sich WITH OFFSET in der Join-Syntax als nützlich erweisen.
Beispielsweise kann eine Spalte, die eine Person darstellt, mehrmals geladen werden, wenn die Person in mehreren Städten (z. B. Chicago, Denver oder San Francisco) gelebt hat. 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 ist, 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 wiederkehrende 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, indem Sie den UNNEST-Operator in einem Join verwenden.
Im folgenden Beispiel wird die Verschachtelung eines bestimmten Ganzzahlarrays, "unresolved_skus", aufgehoben:
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. Diese verweist direkt auf die Tabelle mit den Werten selbst, die dann in explore aufgelöst wird.