Verschachtelte und wiederkehrende Daten in Legacy-SQL abfragen
In diesem Dokument wird beschrieben, wie verschachtelte und wiederkehrende Daten in der Legacy-SQL-Abfragesyntax abgefragt werden. Die bevorzugte Abfragesyntax für BigQuery ist GoogleSQL. Informationen zum Umgang mit verschachtelten und wiederkehrenden Daten in GoogleSQL finden Sie in der GoogleSQL-Migrationsanleitung.
BigQuery unterstützt das Laden und Exportieren von verschachtelten und wiederkehrenden Daten in Form von JSON- und Avro-Dateien. Für viele Abfragen in Legacy-SQL kann BigQuery die Daten automatisch vereinfachen. So können beispielsweise mit vielen SELECT-Anweisungen unter Beibehaltung der Datenstruktur verschachtelte oder wiederkehrende Felder abgerufen und mit WHERE-Klauseln unter Beibehaltung der Struktur Daten gefiltert werden. Im Gegensatz dazu werden durch die Klauseln ORDER BY und GROUP BY die Abfragedaten implizit vereinfacht. Unter Bedingungen, in denen Daten nicht implizit vereinfacht werden, wie z. B. beim Abfragen von mehreren wiederkehrenden Feldern in Legacy-SQL, können Sie Daten unter Verwendung der SQL-Funktionen FLATTEN und WITHIN abfragen.
FLATTEN
Beim Abfragen von verschachtelten Daten vereinfacht BigQuery automatisch die Tabellendaten. Betrachten wir zum Beispiel ein Beispielschema für Personendaten:
Last modified Schema Total Rows Total Bytes Expiration
----------------- ----------------------------------- ------------ ------------- ------------
27 Sep 10:01:06 |- kind: string 4 794
|- fullName: string (required)
|- age: integer
|- gender: string
+- phoneNumber: record
| |- areaCode: integer
| |- number: integer
+- children: record (repeated)
| |- name: string
| |- gender: string
| |- age: integer
+- citiesLived: record (repeated)
| |- place: string
| +- yearsLived: integer (repeated)
Sie sehen, dass hier mehrere wiederkehrende und verschachtelte Felder vorhanden sind. Wenn Sie nun eine Legacy-SQL-Abfrage wie die folgende für die Personentabelle ausführen,
SELECT fullName AS name, age, gender, citiesLived.place, citiesLived.yearsLived FROM [dataset.tableId]
BigQuery gibt die Daten in Form einer vereinfachten Ausgabe zurück:
+---------------+-----+--------+-------------------+------------------------+ | name | age | gender | citiesLived_place | citiesLived_yearsLived | +---------------+-----+--------+-------------------+------------------------+ | John Doe | 22 | Male | Seattle | 1995 | | John Doe | 22 | Male | Stockholm | 2005 | | Mike Jones | 35 | Male | Los Angeles | 1989 | | Mike Jones | 35 | Male | Los Angeles | 1993 | | Mike Jones | 35 | Male | Los Angeles | 1998 | | Mike Jones | 35 | Male | Los Angeles | 2002 | | Mike Jones | 35 | Male | Washington DC | 1990 | | Mike Jones | 35 | Male | Washington DC | 1993 | | Mike Jones | 35 | Male | Washington DC | 1998 | | Mike Jones | 35 | Male | Washington DC | 2008 | | Mike Jones | 35 | Male | Portland | 1993 | | Mike Jones | 35 | Male | Portland | 1998 | | Mike Jones | 35 | Male | Portland | 2003 | | Mike Jones | 35 | Male | Portland | 2005 | | Mike Jones | 35 | Male | Austin | 1973 | | Mike Jones | 35 | Male | Austin | 1998 | | Mike Jones | 35 | Male | Austin | 2001 | | Mike Jones | 35 | Male | Austin | 2005 | | Anna Karenina | 45 | Female | Stockholm | 1992 | | Anna Karenina | 45 | Female | Stockholm | 1998 | | Anna Karenina | 45 | Female | Stockholm | 2000 | | Anna Karenina | 45 | Female | Stockholm | 2010 | | Anna Karenina | 45 | Female | Moscow | 1998 | | Anna Karenina | 45 | Female | Moscow | 2001 | | Anna Karenina | 45 | Female | Moscow | 2005 | | Anna Karenina | 45 | Female | Austin | 1995 | | Anna Karenina | 45 | Female | Austin | 1999 | +---------------+-----+--------+-------------------+------------------------+
In diesem Beispiel ist citiesLived.place jetzt citiesLived_place und citiesLived.yearsLived jetzt citiesLived_yearsLived.
BigQuery kann zwar verschachtelte Felder automatisch vereinfachen, aber möglicherweise müssen Sie ausdrücklich FLATTEN aufrufen, wenn mehr als ein wiederkehrendes Feld vorhanden ist. Wenn Sie beispielsweise versuchen, eine Legacy-SQL-Abfrage so auszuführen,
SELECT fullName, age FROM [dataset.tableId] WHERE (citiesLived.yearsLived > 1995 ) AND (children.age > 3)
gibt BigQuery einen Fehler zurück, der dem folgenden ähnelt:
Cannot query the cross product of repeated fields children.age and citiesLived.yearsLived
Um eine Abfrage über mehr als ein wiederkehrendes Feld auszuführen, müssen Sie eines der Felder vereinfachen:
SELECT fullName, age, gender, citiesLived.place FROM (FLATTEN([dataset.tableId], children)) WHERE (citiesLived.yearsLived > 1995) AND (children.age > 3) GROUP BY fullName, age, gender, citiesLived.place
Dies gibt Folgendes zurück:
+------------+-----+--------+-------------------+ | fullName | age | gender | citiesLived_place | +------------+-----+--------+-------------------+ | John Doe | 22 | Male | Stockholm | | Mike Jones | 35 | Male | Los Angeles | | Mike Jones | 35 | Male | Washington DC | | Mike Jones | 35 | Male | Portland | | Mike Jones | 35 | Male | Austin | +------------+-----+--------+-------------------+
WITHIN-Klausel
Das Keyword WITHIN funktioniert speziell mit Aggregatfunktionen zum Aggregieren untergeordneter und wiederkehrender Felder in Datensätzen und verschachtelten Feldern. Wenn Sie das Keyword WITHIN verwenden, müssen Sie auch den Bereich festlegen, den Sie aggregieren möchten:
WITHIN RECORD: aggregiert Daten in den wiederkehrenden Werten innerhalb des Datensatzes.WITHIN node_name: aggregiert Daten in den wiederkehrenden Werten innerhalb des angegebenen Knotens, wobei der Knoten ein übergeordneter Knoten des Felds in der Aggregatfunktion ist.
Beispiel: Sie möchten herausfinden, wie viele Kinder jede Person aus unserem vorherigen Beispiel hat. Hierzu können Sie die in jedem Datensatz vorhandene Anzahl von children.name zählen:
SELECT fullName, COUNT(children.name) WITHIN RECORD AS numberOfChildren FROM [dataset.tableId];
Sie erhalten das folgende Ergebnis:
+---------------+------------------+ | fullName | numberOfChildren | +---------------+------------------+ | John Doe | 2 | | Jane Austen | 2 | | Mike Jones | 3 | | Anna Karenina | 0 | +---------------+------------------+
Versuchen Sie zum Vergleich, die Namen aller Kinder aufzulisten:
SELECT fullName, children.name FROM [dataset.tableId]
+---------------+---------------+ | fullName | children_name | +---------------+---------------+ | John Doe | Jane | | John Doe | John | | Jane Austen | Josh | | Jane Austen | Jim | | Mike Jones | Earl | | Mike Jones | Sam | | Mike Jones | Kit | | Anna Karenina | None | +---------------+---------------+
Das stimmt mit unseren WITHIN RECORD-Abfrageergebnissen überein: John Doe hat zwei Kinder mit den Namen Jane und John, Jane Austen hat zwei Kinder mit den Namen Josh und Jim, Mike Jones hat drei Kinder mit den Namen Earl, Sam und Kit und Anna Karenina hat keine Kinder.
Beispiel: Sie möchten nun herausfinden, wie oft eine Person an verschiedenen Orten gewohnt hat.
Sie können die WITHIN-Klausel verwenden, um über einen bestimmten Knoten zu aggregieren:
SELECT fullName, COUNT(citiesLived.place) WITHIN RECORD AS numberOfPlacesLived, citiesLived.place, COUNT(citiesLived.yearsLived) WITHIN citiesLived AS numberOfTimesInEachCity, FROM [dataset.tableId];
+---------------+---------------------+-------------------+-------------------------+ | fullName | numberOfPlacesLived | citiesLived_place | numberOfTimesInEachCity | +---------------+---------------------+-------------------+-------------------------+ | John Doe | 2 | Seattle | 1 | | John Doe | 2 | Stockholm | 1 | | Mike Jones | 4 | Los Angeles | 4 | | Mike Jones | 4 | Washington DC | 4 | | Mike Jones | 4 | Portland | 4 | | Mike Jones | 4 | Austin | 4 | | Anna Karenina | 3 | Stockholm | 4 | | Anna Karenina | 3 | Moscow | 3 | | Anna Karenina | 3 | Austin | 2 | +---------------+---------------------+-------------------+-------------------------+
Diese Abfrage funktioniert so:
- Sie wendet die Klausel
WITHIN RECORDaufcitiesLived.placean und zählt die Anzahl von Orten, in denen jede Person gewohnt hat. - Sie wendet die Klausel
WITHINaufcitiesLived.yearsLivedan und zählt, wie oft jede Person in jeder Stadt gewohnt hat (Zählung erfolgt nur übercitiesLived).
Die Verwendung der Bereichsaggregation über verschachtelte und wiederkehrende Felder ist eines der leistungsstärksten Features von BigQuery, wodurch bei Abfragen häufig auf teure Verknüpfungen verzichtet werden kann.