BigQuery unterstützt zwei SQL-Dialekte: Standard-SQL und Legacy-SQL. In diesem Thema werden die Unterschiede zwischen den beiden Dialekten, unter anderem in Bezug auf Syntax, Funktionen und Semantik, beschrieben und Beispiele für einige der Highlights von Standard-SQL angeführt.
Vergleich zwischen Legacy-SQL und Standard-SQL
Früher wurden Abfragen von BigQuery mithilfe des Nicht-Standard-SQL-Dialekts BigQuery SQL ausgeführt. Mit Einführung von BigQuery 2.0 bietet BigQuery nun Unterstützung für Standard-SQL. BigQuery SQL wird jetzt als Legacy-SQL bezeichnet. Standard-SQL ist der bevorzugte SQL-Dialekt zum Abfragen von in BigQuery gespeicherten Daten.
Ist eine Migration zu Standard-SQL erforderlich?
Wir empfehlen die Migration von Legacy-SQL zu Standard-SQL, erforderlich ist diese jedoch nicht. Beispiel: Sie führen viele Abfragen anhand von Legacy-SQL aus, aber Sie möchten für eine neue Abfrage eine Standard-SQL-Funktion nutzen. Sie haben die Möglichkeit, neue Abfragen unter Verwendung von Standard-SQL zu erstellen, die neben Abfragen in Legacy-SQL ausgeführt werden.
Standard-SQL aktivieren
Zum Ausführen einer Abfrage können Sie sowohl Legacy-SQL als auch Standard-SQL verwenden. Informationen zum Wechsel zwischen SQL-Dialekten finden Sie unter SQL-Dialekte wechseln.
Vorteile von Standard-SQL
Standard-SQL entspricht dem SQL 2011-Standard und bietet Erweiterungen, die das Abfragen verschachtelter und wiederkehrender Daten unterstützen. Standard-SQL bietet im Vergleich zu Legacy-SQL unter anderem diese Vorteile:
- Komponierbarkeit unter Verwendung von
WITH-Klauseln und SQL-Funktionen - Unterabfragen in der
SELECT-Liste und derWHERE-Klausel - Korrelierte Unterabfragen
ARRAY- undSTRUCT-Datentypen- Einfüge-, Update- und Löschvorgänge
COUNT(DISTINCT <expr>)ist exakt und skalierbar und bietet die Genauigkeit vonEXACT_COUNT_DISTINCTohne dessen Einschränkungen- Automatisches Prädikat-Push-down durch
JOINs - Komplexe
JOIN-Prädikate einschließlich freier Ausdrücke
Beispiele zur Darstellung einiger dieser Funktionen finden Sie unter Highlights von Standard-SQL.
Typunterschiede
Typen in Legacy-SQL haben eine Entsprechung in Standard-SQL und umgekehrt. In einigen Fällen hat der Typ eine andere Bezeichnung. In der folgenden Tabelle wird jeder Datentyp in Legacy-SQL und seine Entsprechung in Standard-SQL aufgeführt.
| Legacy-SQL | Standard-SQL | Hinweise |
|---|---|---|
BOOL |
BOOL |
|
INTEGER |
INT64 |
|
FLOAT |
FLOAT64 |
|
STRING |
STRING |
|
BYTES |
BYTES |
|
RECORD |
STRUCT |
|
REPEATED |
ARRAY |
|
TIMESTAMP |
TIMESTAMP |
Siehe TIMESTAMP-Unterschiede |
DATE |
DATE |
Legacy-SQL bietet eingeschränkte Unterstützung für DATE |
TIME |
TIME |
Legacy-SQL bietet eingeschränkte Unterstützung für TIME |
DATETIME |
DATETIME |
Legacy-SQL bietet eingeschränkte Unterstützung für DATETIME |
Weitere Informationen zum Standard-SQL-Typsystem finden Sie unter Standard-SQL-Datentypen. Weitere Informationen zu Datentypen in BigQuery finden Sie unter Datentypen der Legacy-SQL-Version.
TIMESTAMP-Unterschiede
Standard-SQL hat einen kleineren Bereich gültiger TIMESTAMP-Werte als Legacy-SQL. Bei Standard-SQL liegen die gültigen Werte für TIMESTAMP im Bereich von 0001-01-01 00:00:00.000000 bis 9999-12-31 23:59:59.999999. Sie können beispielsweise so den Mindest- und Höchstwert für TIMESTAMP mit Standard-SQL auswählen:
#standardSQL
SELECT
min_timestamp,
max_timestamp,
UNIX_MICROS(min_timestamp) AS min_unix_micros,
UNIX_MICROS(max_timestamp) AS max_unix_micros
FROM (
SELECT
TIMESTAMP '0001-01-01 00:00:00.000000' AS min_timestamp,
TIMESTAMP '9999-12-31 23:59:59.999999' AS max_timestamp
);
Diese Abfrage gibt -62135596800000000 als min_unix_micros und 253402300799999999 als max_unix_micros zurück.
Bei der Auswahl einer Spalte, die Zeitstempelwerte außerhalb dieses Bereichs enthält, erhalten Sie einen Fehler:
#standardSQL
SELECT timestamp_column_with_invalid_values
FROM MyTableWithInvalidTimestamps;
Diese Abfrage gibt den folgenden Fehler zurück:
Cannot return an invalid timestamp value of -8446744073709551617
microseconds relative to the Unix epoch. The range of valid
timestamp values is [0001-01-1 00:00:00, 9999-12-31 23:59:59.999999]
Eine Option zur Behebung dieses Fehlers besteht darin, eine benutzerdefinierte Funktion zum Filtern von ungültigen Zeitstempeln festzulegen:
#standardSQL
CREATE TEMP FUNCTION TimestampIsValid(t TIMESTAMP) AS (
t >= TIMESTAMP('0001-01-01 00:00:00') AND
t <= TIMESTAMP('9999-12-31 23:59:59.999999')
);
SELECT timestamp_column_with_invalid_values
FROM MyTableWithInvalidTimestamps
WHERE TimestampIsValid(timestamp_column_with_invalid_values);
Eine weitere Möglichkeit zur Behebung dieses Fehlers besteht darin, die Funktion SAFE_CAST mit der Zeitstempelspalte zu verwenden. Beispiel:
#standardSQL
SELECT SAFE_CAST(timestamp_column_with_invalid_values AS STRING) AS timestamp_string
FROM MyTableWithInvalidTimestamps;
Diese Abfrage gibt NULL statt einen Zeitstempel-String für ungültige Zeitstempelwerte zurück.
Unterschiede in der Syntax
Reservierte Schlüsselwörter und ungültige Bezeichner mit Escapezeichen versehen
In Legacy-SQL werden reservierte Schlüsselwörter und Bezeichner, die ungültige Zeichen wie z. B. Leerzeichen oder Bindestriche - enthalten, mit eckigen Klammern [] als Escapezeichen versehen.
In Standard-SQL werden derartige Schlüsselwörter und Bezeichner mit dem Akzent Gravis ` als Escapezeichen versehen. Beispiel:
#standardSQL
SELECT
word,
SUM(word_count) AS word_count
FROM
`bigquery-public-data.samples.shakespeare`
WHERE word IN ('me', 'I', 'you')
GROUP BY word;
Legacy-SQL lässt reservierte Schlüsselwörter an einigen Stellen zu, an denen Standard-SQL diese nicht zulässt.
Die folgende Abfrage schlägt beispielsweise aufgrund eines Syntax error mit Standard-SQL fehl:
#standardSQL
SELECT
COUNT(*) AS rows
FROM
`bigquery-public-data.samples.shakespeare`;
Versehen Sie das Alias rows mit Gravis als Escapezeichen, um diesen Fehler zu beheben:
#standardSQL
SELECT
COUNT(*) AS `rows`
FROM
`bigquery-public-data.samples.shakespeare`;
Eine Liste mit reservierten Schlüsselwörtern und weitere Informationen zu gültigen Bezeichnern finden Sie unter Lexikalische Standard-SQL-Struktur.
Projektqualifizierte Tabellennamen
In Legacy-SQL verwenden Sie zur Abfrage einer Tabelle mit projektqualifiziertem Namen einen Doppelpunkt (:) als Trennzeichen. Beispiel:
#legacySQL
SELECT
word
FROM
[bigquery-public-data:samples.shakespeare]
LIMIT 1;
In Standard-SQL wird stattdessen ein Punkt (.) verwendet. Beispiel:
#standardSQL
SELECT
word
FROM
`bigquery-public-data.samples.shakespeare`
LIMIT 1;
Wenn Ihr Projektname eine Domain wie example.com:myproject enthält, verwenden Sie example.com:myproject als Projektnamen, einschließlich :.
Tabellen-Decorators
Standard-SQL unterstützt keine Tabellen-Decorators.
Die Semantik von Snapshot-Decorators erhalten Sie mit der Klausel FOR SYSTEM_TIME AS OF, die auf die historische Version einer Tabelle an einem bestimmten Zeitstempel verweist. Weitere Informationen finden Sie unter Mit Zeitreisen auf Verlaufsdaten zugreifen.
In Standard-SQL gibt es kein genaues Äquivalent für Bereichs-Decorators. Sie können eine ähnliche Semantik erreichen, wenn Sie eine zeitpartitionierte Tabelle erstellen und bei der Abfrage von Daten einen Partitionsfilter verwenden. Weitere Informationen finden Sie unter Partitionierte Tabellen abfragen.
Eine weitere Option besteht darin, datumsfragmentierte Tabellen zu erstellen und nach der Pseudospalte _TABLE_SUFFIX zu filtern. Weitere Informationen finden Sie unter Platzhaltertabellen.
Platzhalterfunktionen
Standard-SQL bietet keine Unterstützung der Funktionen TABLE_DATE_RANGE, TABLE_DATE_RANGE_STRICT oder TABLE_QUERY.
Sie erreichen die gleiche Semantik von TABLE_DATE_RANGE und TABLE_QUERY, wenn Sie einen Filter auf die Pseudospalte _TABLE_SUFFIX anwenden. Beispiel: Betrachten Sie die folgende Abfrage in Legacy-SQL, die die Anzahl der Zeilen in den National Oceanic and Atmospheric Administration GSOD-Tabellen (globale Tagesübersicht) für 2010 und 2011 zählt:
#legacySQL
SELECT COUNT(*)
FROM TABLE_QUERY([bigquery-public-data:noaa_gsod],
'table_id IN ("gsod2010", "gsod2011")');
Die entsprechende Abfrage in Standard-SQL sieht so aus:
#standardSQL
SELECT COUNT(*)
FROM `bigquery-public-data.noaa_gsod.*`
WHERE _TABLE_SUFFIX IN ("gsod2010", "gsod2011");
Weitere Informationen und Beispiele mit TABLE_DATE_RANGE finden Sie unter Platzhalterfunktionen in Legacy-SQL-Tabellen migrieren.
Kommaoperator mit Tabellen
In Legacy-SQL hat der Kommaoperator , die nicht standardmäßige Bedeutung von UNION ALL, wenn er auf Tabellen angewendet wird. In Standard-SQL hat der Kommaoperator die Standardbedeutung JOIN. Betrachten Sie beispielsweise die folgende Abfrage in Legacy-SQL:
#legacySQL
SELECT
x,
y
FROM
(SELECT 1 AS x, "foo" AS y),
(SELECT 2 AS x, "bar" AS y);
Dies entspricht der folgenden Abfrage in Standard-SQL:
#standardSQL
SELECT
x,
y
FROM
(SELECT 1 AS x, "foo" AS y UNION ALL
SELECT 2 AS x, "bar" AS y);
Beachten Sie außerdem, dass in Standard-SQL UNION ALL die Spalten nach Position und nicht nach Namen zuordnet. Die Abfrage oben entspricht der Folgenden:
#standardSQL
SELECT
x,
y
FROM
(SELECT 1 AS x, "foo" AS y UNION ALL
SELECT 2, "bar");
Eine allgemeine Verwendung des Kommaoperators in Standard-SQL besteht in der Verknüpfung (JOIN) mit einem Array.
Beispiel:
#standardSQL
WITH T AS (
SELECT 0 AS x, [1, 2, 3] AS arr UNION ALL
SELECT 1, [4, 5])
SELECT
x,
y
FROM
T,
UNNEST(arr) AS y;
Diese Abfrage gibt das Mengenprodukt aus der Tabelle T und den Elementen von arr zurück. Sie können die Abfrage in Standard-SQL auch so ausdrücken:
#standardSQL
WITH T AS (
SELECT 0 AS x, [1, 2, 3] AS arr UNION ALL
SELECT 1, [4, 5])
SELECT
x,
y
FROM
T
JOIN
UNNEST(arr) AS y;
In dieser Abfrage hat JOIN dieselbe Bedeutung wie der Kommaoperator ,, der T und UNNEST(arr) AS y im obigen Beispiel trennt.
Logische Ansichten
Sie können eine in Legacy-SQL definierte logische Ansicht nicht mit Standard-SQL abfragen und umgekehrt. Dies ist auf Unterschiede in der Syntax und Semantik zwischen den beiden Dialekten zurückzuführen. Stattdessen müssen Sie eine neue Ansicht mit Standard-SQL (eventuell unter einem anderen Namen) erstellen, um eine Ansicht in Legacy-SQL zu ersetzen.
Angenommen, die Ansicht V wurde in Legacy-SQL so definiert:
#legacySQL
SELECT *, UTC_USEC_TO_DAY(timestamp_col) AS day
FROM MyTable;
Angenommen, die Ansicht W wurde in Legacy-SQL so definiert:
#legacySQL
SELECT user, action, day
FROM V;
Angenommen, Sie führen die folgende Abfrage im Legacy-SQL-Dialekt täglich aus, möchten diese aber für die Verwendung in Standard-SQL migrieren:
#legacySQL
SELECT EXACT_COUNT_DISTINCT(user), action, day
FROM W
GROUP BY action, day;
Ein möglicher Pfad für die Migration besteht darin, neue Ansichten unter Verwendung unterschiedlicher Namen zu erstellen. Befolgen Sie hierzu folgende Schritte:
Erstellen Sie eine Ansicht mit dem Namen V2 in Standard-SQL mit dem folgenden Inhalt:
#standardSQL
SELECT *, EXTRACT(DAY FROM timestamp_col) AS day
FROM MyTable;
Erstellen Sie eine Ansicht mit dem Namen W2 in Standard-SQL mit dem folgenden Inhalt:
#standardSQL
SELECT user, action, day
FROM V2;
Ändern Sie die täglich ausgeführte Abfrage in Standard-SQL und verweisen Sie auf W2:
#standardSQL
SELECT COUNT(DISTINCT user), action, day
FROM W2
GROUP BY action, day;
Eine weitere Option besteht darin, die Ansichten V und W zu löschen und diese anschließend in Standard-SQL unter denselben Namen neu zu erstellen. Bei dieser Option müssten Sie jedoch alle Abfragen, die auf V oder W verweisen, gleichzeitig in Standard-SQL migrieren.
Funktionsvergleich
Im Folgenden finden Sie eine unvollständige Liste mit Funktionen in Legacy-SQL und ihren Entsprechungen in Standard-SQL.
Weitere Informationen zu Standard-SQL-Funktionen finden Sie unter Funktionen und Operatoren.
COUNT-Funktionsvergleich
Sowohl Legacy-SQL als auch Standard-SQL enthalten eine COUNT-Funktion. Die entsprechende Funktion verhält sich jedoch je nach verwendetem SQL-Dialekt unterschiedlich.
In Legacy-SQL gibt COUNT(DISTINCT x) eine ungefähre Anzahl zurück. In Standard-SQL wird eine genaue Anzahl zurückgegeben. Verwenden Sie für eine ungefähre Anzahl unterschiedlicher Werte APPROX_COUNT_DISTINCT. Dies ermöglicht eine schnellere Ausführung und beansprucht weniger Ressourcen.
URL-Funktionsvergleich
Sowohl Legacy-SQL als auch Standard-SQL enthalten Funktionen zum Parsen von URLs. In Legacy-SQL sind diese Funktionen HOST(url), TLD(url) und DOMAIN(url). In Standard-SQL lauten diese Funktionen NET.HOST(url), NET.PUBLIC_SUFFIX(url) und NET.REG_DOMAIN(url).
Verbesserungen gegenüber Funktionen in Legacy-SQL
- URL-Funktionen in Standard-SQL können URLs parsen, die mit "//" beginnen.
- Wenn die Eingabe nicht RFC 3986 entspricht oder es sich dabei nicht um eine URL handelt (z. B. "mailto:?to=&subject=&body="), werden unterschiedliche Regeln zum Parsen der Eingabe angewendet. Insbesondere URL-Funktionen in Standard-SQL können vom Standard abweichende Eingaben ohne "//", wie z. B. "www.google.com", parsen. Optimale Ergebnisse erhalten Sie, wenn Sie darauf achten, dass es sich bei der Eingabe um URLs handelt und diese RFC 3986 befolgen.
NET.PUBLIC_SUFFIXgibt Ergebnisse ohne führende Punkte zurück. So wird beispielsweise "com" statt ".com" zurückgegeben. Dies entspricht dem Format in der öffentlichen Suffixliste.NET.PUBLIC_SUFFIXundNET.REG_DOMAINunterstützen Großbuchstaben und internationalisierte Domainnamen.TLDundDOMAINbieten hierfür keine Unterstützung (und es werden eventuell unerwartete Ergebnisse zurückgegeben).
Geringfügige Abweichungen bei Grenzfällen
- Wenn die Eingabe kein Suffix in der öffentlichen Suffixliste enthält, geben
NET.PUBLIC_SUFFIXundNET.REG_DOMAINNULL zurück, währendTLDundDOMAINvon NULL abweichende Werte als bestmögliche Schätzung zurückgeben. - Wenn die Eingabe nur ein öffentliches Suffix ohne vorhergehendes Label (z. B. "http://com") enthält, gibt
NET.PUBLIC_SUFFIXdas öffentliche Suffix zurück, währendTLDeinen leeren String zurückgibt. Entsprechend gibtNET.REG_DOMAINNULL zurück, währendDOMAINdas öffentliche Suffix zurückgibt. - Bei Eingaben mit IPv6-Hosts behält
NET.HOSTdie eckigen Klammern aus dem Ergebnis gemäß RFC 3986 bei. - Bei Eingaben mit IPv4-Hosts gibt
NET.REG_DOMAINNULL zurück, währendDOMAINdie ersten drei Oktette zurückgibt.
Beispiele
In der folgenden Tabelle weist die graue Textfarbe auf Ergebnisse hin, die zwischen Legacy-SQL und Standard-SQL identisch sind.
| URL (Beschreibung) | HOST | NET.HOST | TLD | NET.PUBLIC _SUFFIX | DOMAIN | NET.REG_DOMAIN |
|---|---|---|---|---|---|---|
| "//google.com" (beginnend mit "//") |
NULL | "google.com" | NULL | "com" | NULL | "google.com" |
| "google.com" (nicht standardgemäß; kein "//") |
NULL | "google.com" | NULL | "com" | NULL | "google.com" |
| "http://user:pass@word@x.com" (nicht standardgemäß mit mehreren "@") |
"word@x.com" | "x.com" | ".com" | "com" | "word@x.com" | "x.com" |
| "http://foo.com:1:2" (nicht standardgemäß mit mehreren ":") |
"foo.com:1" | "foo.com" | ".com:1" | "com" | "foo.com" | "foo.com" |
| "http://x.Co.uk" (Großbuchstaben) |
"x.Co.uk" | "x.Co.uk" | ".uk" | "Co.uk" | "Co.uk" | "x.Co.uk" |
| "http://a.b" (öffentliches Suffix nicht gefunden) |
"a.b" | "a.b" | ".b" | NULL | "a.b" | NULL |
| "http://com" (Host enthält nur ein öffentliches Suffix) |
"com" | "com" | "" | "com" | "com" | NULL |
| "http://[::1]" (IPv6-Host; kein öffentliches Suffix) |
"::1" | "[::1]" | "" | NULL | "::1" | NULL |
| "http://1.2.3.4" (IPv4-Host; kein öffentliches Suffix) |
"1.2.3.4" | "1.2.3.4" | "" | NULL | "1.2.3" | NULL |
Unterschiede bei der Verarbeitung wiederkehrender Felder
Ein REPEATED-Typ in Legacy-SQL entspricht einem ARRAY-Typ in Standard-SQL. Beispiel: REPEATED INTEGER entspricht ARRAY<INT64> in Standard-SQL. Im folgenden Abschnitt werden einige der Unterschiede bei Vorgängen für wiederkehrende Felder zwischen Legacy- und Standard-SQL erläutert.
NULL-Elemente und NULL-Arrays
Standard-SQL unterstützt NULL-Array-Elemente, löst aber einen Fehler aus, wenn ein NULL-Array-Element im Abfrageergebnis vorliegt. Wenn eine NULL-Array-Spalte im Abfrageergebnis vorliegt, speichert Standard-SQL diese als ein leeres Array.
Verschachtelte wiederkehrende Blattfelder auswählen
In Legacy-SQL können Sie in ein verschachteltes wiederkehrendes Feld einen Punkt setzen, ohne berücksichtigen zu müssen, an welcher Stelle die Wiederholung auftritt. In Standard-SQL führt der Versuch, in ein verschachteltes wiederkehrendes Feld einen Punkt zu setzen, zu einem Fehler. Beispiel:
#standardSQL
SELECT
repository.url,
payload.pages.page_name
FROM
`bigquery-public-data.samples.github_nested`
LIMIT 5;
Beim Versuch, diese Abfrage auszuführen, wird Folgendes zurückgegeben:
Cannot access field page_name on a value with type
ARRAY<STRUCT<action STRING, html_url STRING, page_name STRING, ...>>
Verwenden Sie stattdessen eine ARRAY-Unterabfrage, um den Fehler zu beheben und ein Array von page_names im Ergebnis zurückzugeben. Beispiel:
#standardSQL
SELECT
repository.url,
ARRAY(SELECT page_name FROM UNNEST(payload.pages)) AS page_names
FROM
`bigquery-public-data.samples.github_nested`
LIMIT 5;
Weitere Informationen zu Arrays und ARRAY-Unterabfragen finden Sie unter Mit Arrays arbeiten.
Wiederkehrende Felder filtern
In Legacy-SQL können Sie wiederkehrende Felder direkt mit einer WHERE-Klausel filtern. In Standard-SQL können Sie eine ähnliche Logik mit einem JOIN-Kommaoperator gefolgt von einem Filter ausdrücken. Betrachten Sie beispielsweise die folgende Abfrage in Legacy-SQL:
#legacySQL
SELECT
payload.pages.title
FROM
[bigquery-public-data:samples.github_nested]
WHERE payload.pages.page_name IN ('db_jobskill', 'Profession');
Diese Abfrage gibt alle title von Seiten zurück, für die der page_name entweder db_jobskill oder Profession ist. Sie können eine ähnliche Abfrage in Standard-SQL so ausdrücken:
#standardSQL
SELECT
page.title
FROM
`bigquery-public-data.samples.github_nested`,
UNNEST(payload.pages) AS page
WHERE page.page_name IN ('db_jobskill', 'Profession');
Ein Unterschied zwischen der vorherigen Abfrage in Legacy-SQL und der Abfrage in Standard-SQL besteht darin, dass durch Deaktivieren der Option Ergebnisse vereinfachen und Ausführen der Legacy-SQL-Abfrage payload.pages.title in den Abfrageergebnissen REPEATED ist. Verwenden Sie stattdessen eine ARRAY-Unterabfrage, um die gleiche Semantik in Standard-SQL zu erhalten und ein Array für die Spalte title zurückzugeben:
#standardSQL
SELECT
title
FROM (
SELECT
ARRAY(SELECT title FROM UNNEST(payload.pages)
WHERE page_name IN ('db_jobskill', 'Profession')) AS title
FROM
`bigquery-public-data.samples.github_nested`)
WHERE ARRAY_LENGTH(title) > 0;
Diese Abfrage erstellt ein Array mit title, wobei page_name entweder 'db_jobskill' oder 'Profession' lautet. Anschließend werden alle Zeilen unter Verwendung von ARRAY_LENGTH(title) > 0 herausgefiltert, bei denen das Array nicht der Bedingung entspricht.
Weitere Informationen zu Arrays finden Sie unter Mit Arrays arbeiten.
Struktur ausgewählter verschachtelter Blattfelder
In Legacy-SQL wird die Struktur verschachtelter Blattfelder in der SELECT-Liste bei Deaktivieren der Option Ergebnisse vereinfachen beibehalten. Dies ist in Standard-SQL jedoch nicht der Fall. Betrachten Sie beispielsweise die folgende Abfrage in Legacy-SQL:
#legacySQL
SELECT
repository.url,
repository.has_downloads
FROM
[bigquery-public-data.samples.github_nested]
LIMIT 5;
Diese Abfrage gibt url und has_downloads in einem Datensatz mit dem Namen repository zurück, wenn Ergebnisse vereinfachen deaktiviert ist. Betrachten Sie nun die folgende Abfrage in Standard-SQL:
#standardSQL
SELECT
repository.url,
repository.has_downloads
FROM
`bigquery-public-data.samples.github_nested`
LIMIT 5;
Diese Abfrage gibt url und has_downloads als Spalten auf der obersten Ebene zurück. Sie sind nicht Teil eines repository-Datensatzes oder -Structs. Verwenden Sie den STRUCT-Operator, um sie als Teil eines Structs zurückzugeben:
#standardSQL
SELECT
STRUCT(
repository.url,
repository.has_downloads) AS repository
FROM
`bigquery-public-data.samples.github_nested`
LIMIT 5;
Wiederholung mit FLATTEN entfernen
Standard-SQL verfügt über keine FLATTEN-Funktion wie Legacy-SQL, aber Sie können eine ähnliche Semantik durch die Verwendung des JOIN-(Komma-)Operators erreichen. Betrachten Sie beispielsweise die folgende Abfrage in Legacy-SQL:
#legacySQL
SELECT
repository.url,
payload.pages.page_name
FROM
FLATTEN([bigquery-public-data:samples.github_nested], payload.pages.page_name)
LIMIT 5;
Sie können eine ähnliche Abfrage in Standard-SQL so ausdrücken:
#standardSQL
SELECT
repository.url,
page.page_name
FROM
`bigquery-public-data.samples.github_nested`,
UNNEST(payload.pages) AS page
LIMIT 5;
Oder verwenden Sie entsprechend JOIN statt des Kommaoperators (,):
#standardSQL
SELECT
repository.url,
page.page_name
FROM
`bigquery-public-data.samples.github_nested`
JOIN
UNNEST(payload.pages) AS page
LIMIT 5;
Ein wichtiger Unterschied ist, dass die Legacy-SQL-Abfrage eine Zeile zurückgibt, in der payload.pages.page_name NULL ist, wenn payload.pages leer ist. Die Standard-SQL-Abfrage gibt jedoch keine Zeile zurück, wenn payload.pages leer ist. Verwenden Sie LEFT JOIN oder LEFT OUTER JOIN, um dieselbe Semantik zu erhalten. Beispiel:
#standardSQL
SELECT
repository.url,
page.page_name
FROM
`bigquery-public-data.samples.github_nested`
LEFT JOIN
UNNEST(payload.pages) AS page
LIMIT 5;
Weitere Informationen zu Arrays finden Sie unter Mit Arrays arbeiten. Weitere Informationen zu UNNEST finden Sie unter dem Thema UNNEST.
Zeilen mit OMIT RECORD IF filtern
Mit der OMIT IF-Klausel aus Legacy-SQL können Sie Zeilen anhand einer Bedingung filtern, die auf wiederkehrende Felder angewendet werden kann. In Standard-SQL können Sie eine OMIT IF-Klausel mit einer EXISTS-Klausel, IN-Klausel oder einem einfachen Filter formen.
Betrachten Sie beispielsweise die folgende Abfrage in Legacy-SQL:
#legacySQL
SELECT
repository.url,
FROM
[bigquery-public-data:samples.github_nested]
OMIT RECORD IF
EVERY(payload.pages.page_name != 'db_jobskill'
AND payload.pages.page_name != 'Profession');
Die analoge Abfrage in Standard-SQL lautet so:
#standardSQL
SELECT
repository.url
FROM
`bigquery-public-data.samples.github_nested`
WHERE EXISTS (
SELECT 1 FROM UNNEST(payload.pages)
WHERE page_name = 'db_jobskill'
OR page_name = 'Profession');
Hier wird die EXISTS-Klausel als true ausgewertet, wenn es mindestens ein Element von payload.pages gibt, wobei der Seitenname 'db_jobskill' oder 'Profession' ist.
Angenommen, die Abfrage in Legacy-SQL verwendet alternativ dazu IN:
#legacySQL
SELECT
repository.url,
FROM
[bigquery-public-data:samples.github_nested]
OMIT RECORD IF NOT
SOME(payload.pages.page_name IN ('db_jobskill', 'Profession'));
In Standard-SQL können Sie die Abfrage unter Verwendung der EXISTS-Klausel mit IN ausdrücken:
#standardSQL
SELECT
repository.url
FROM
`bigquery-public-data.samples.github_nested`
WHERE EXISTS (
SELECT 1 FROM UNNEST(payload.pages)
WHERE page_name IN ('db_jobskill', 'Profession'));
Betrachten Sie die folgende Abfrage in Legacy-SQL, die Datensätze mit höchstens 80 Seiten filtert:
#legacySQL
SELECT
repository.url,
FROM
[bigquery-public-data:samples.github_nested]
OMIT RECORD IF
COUNT(payload.pages.page_name) <= 80;
In diesem Fall können Sie einen Filter mit ARRAY_LENGTH in Standard-SQL verwenden:
#standardSQL
SELECT
repository.url
FROM
`bigquery-public-data.samples.github_nested`
WHERE
ARRAY_LENGTH(payload.pages) > 80;
Die ARRAY_LENGTH-Funktion wird direkt auf das wiederholte payload.pages-Feld und nicht wie bei der Legacy-SQL-Abfrage auf das verschachtelte Feld payload.pages.page_name angewendet.
Weitere Informationen zu Arrays und ARRAY-Unterabfragen finden Sie unter Mit Arrays arbeiten.
Unterschiede in der Semantik
Die Semantik einiger Vorgänge unterscheidet sich zwischen Legacy- und Standard-SQL.
Automatische Datentypkoersionen
Sowohl Legacy-SQL als auch Standard-SQL unterstützen Koersionen (automatische Konvertierungen) zwischen bestimmten Datentypen. BigQuery wandelt beispielsweise einen Wert vom Typ INT64 in FLOAT64 um, wenn die Abfrage den Typ an eine Funktion übergibt, die als Eingabe FLOAT64 erfordert.
Standard-SQL unterstützt nicht die folgenden Koersionen, die von Legacy-SQL unterstützt werden.
Stattdessen müssen Sie ein explizites CAST verwenden.
INT64literal zuTIMESTAMP. Verwenden Sie stattdessenTIMESTAMP_MICROS(micros_value).STRINGliteral zuINT64,FLOAT64oderBOOL. Verwenden Sie stattdessenCAST(str AS INT64),CAST(str AS FLOAT64)oderCAST(str AS BOOL).STRINGzuBYTES. Verwenden Sie stattdessenCAST(str AS BYTES).
Laufzeitfehler
Einige Funktionen in Legacy-SQL geben den Wert NULL für eine ungültige Eingabe zurück. Dadurch werden möglicherweise Probleme in Abfragen oder in Daten maskiert. Standard-SQL ist im Allgemeinen strenger und löst bei einer ungültigen Eingabe einen Fehler aus.
- Bei allen mathematischen Funktionen und Operatoren führt Legacy-SQL keine Prüfung auf Überläufe durch. Standard-SQL fügt Überlaufprüfungen hinzu und löst einen Fehler aus, wenn eine Berechnung überläuft. Das umfasst die Operatoren
+,-,*, die AggregatfunktionenSUM,AVGundSTDDEVsowie weitere. - Standard-SQL löst bei einer Division durch null einen Fehler aus, während Legacy-SQL
NULLzurückgibt. Wenn Standard-SQL bei einer Division durch null den WertNULLzurückgeben soll, verwenden SieSAFE_DIVIDE. - Standard-SQL löst bei Umwandlungen (
CAST) einen Fehler aus, wenn das Eingabeformat ungültig ist oder außerhalb des Bereichs des Zieltyps liegt. Bei Legacy-SQL wird dagegen der WertNULLzurückgegeben. Verwenden SieSAFE_CAST, um einen Fehler bei einer ungültigen Umwandlung in Standard-SQL zu vermeiden.
Verschachtelte wiederkehrende Ergebnisse
In Standard-SQL ausgeführte Abfragen behalten Verschachtelungen und Wiederholungen von Spalten im Ergebnis bei, wobei die Option Ergebnisse vereinfachen keine Auswirkungen hat. Wenn Spalten der obersten Ebene für verschachtelte Felder zurückgegeben werden sollen, verwenden Sie den Operator .* bei Struct-Spalten. Beispiel:
#standardSQL
SELECT
repository.*
FROM
`bigquery-public-data.samples.github_nested`
LIMIT 5;
Um Spalten der obersten Ebene für wiederkehrende verschachtelte Felder (ARRAYs von STRUCTs) zurückzugeben, verwenden Sie JOIN für das Mengenprodukt aus den Zeilen der Tabelle und den Elementen des wiederkehrenden verschachtelten Felds. Beispiel:
#standardSQL
SELECT
repository.url,
page.*
FROM
`bigquery-public-data.samples.github_nested`
JOIN
UNNEST(payload.pages) AS page
LIMIT 5;
Weitere Informationen zu Arrays und ARRAY-Unterabfragen finden Sie unter Mit Arrays arbeiten.
NOT IN-Bedingungen und NULL
Legacy-SQL erfüllt hinsichtlich der Handhabung von NULL mit NOT IN-Bedingungen nicht den SQL-Standard, während dies bei Standard-SQL der Fall ist. Betrachten Sie die folgende Abfrage im Legacy-SQL-Dialekt, mit der die Anzahl der Wörter ermittelt wird, die in der GitHub-Beispieltabelle nicht als Orte angezeigt werden.
#legacySQL
SELECT COUNT(*)
FROM [bigquery-public-data.samples.shakespeare]
WHERE word NOT IN (
SELECT actor_attributes.location
FROM [bigquery-public-data.samples.github_nested]
);
Bei dieser Abfrage wird die Anzahl 163.716 zurückgegeben.Dies bedeutet, dass 163.716 Wörter in der GitHub-Tabelle scheinbar keine Ortsangaben sind. Betrachten Sie nun die folgende Abfrage in Standard-SQL:
#standardSQL
SELECT COUNT(*)
FROM `bigquery-public-data.samples.shakespeare`
WHERE word NOT IN (
SELECT actor_attributes.location
FROM `bigquery-public-data.samples.github_nested`
);
Diese Abfrage gibt als Anzahl 0 zurück. Der Unterschied resultiert aus der Semantik von NOT IN mit Standard-SQL. Diese gibt NULL zurück, wenn ein Wert auf der rechten Seite NULL ist. Schließen Sie die NULL-Werte mit der WHERE-Klausel aus, um dieselben Ergebnisse wie bei der Abfrage in Legacy-SQL zu erhalten:
#standardSQL
SELECT COUNT(*)
FROM `bigquery-public-data.samples.shakespeare`
WHERE word NOT IN (
SELECT actor_attributes.location
FROM `bigquery-public-data.samples.github_nested`
WHERE actor_attributes.location IS NOT NULL
);
Diese Abfrage gibt als Anzahl 163.716 zurück. Alternativ können Sie eine NOT EXISTS-Bedingung verwenden:
#standardSQL
SELECT COUNT(*)
FROM `bigquery-public-data.samples.shakespeare` AS t
WHERE NOT EXISTS (
SELECT 1
FROM `bigquery-public-data.samples.github_nested`
WHERE t.word = actor_attributes.location
);
Diese Abfrage gibt als Anzahl ebenfalls 163.716 zurück. Weitere Informationen finden Sie in der Dokumentation im Abschnitt zu Vergleichsoperatoren. Darin wird die Semantik von IN, NOT IN, EXISTS und anderen Vergleichsoperatoren erläutert.
Unterschiede bei benutzerdefinierten JavaScript-Funktionen
Das Thema Benutzerdefinierte Funktionen dokumentiert die Verwendung von benutzerdefinierten JavaScript-Funktionen in Standard-SQL. In diesem Abschnitt werden einige der Hauptunterschiede zwischen benutzerdefinierten Funktionen in Legacy- und Standard-SQL beschrieben.
Funktionen im Abfragetext
Bei Standard-SQL verwenden Sie CREATE TEMPORARY FUNCTION als Teil des Abfragetexts, anstatt benutzerdefinierte Funktionen separat anzugeben. Beispiele für die separate Definition von Funktionen sind die Verwendung des UDF-Editors in der Cloud Console oder des Flags --udf_resource im bq-Befehlszeilentool.
Betrachten Sie die folgende Abfrage in Standard-SQL:
#standardSQL
-- Computes the harmonic mean of the elements in 'arr'.
-- The harmonic mean of x_1, x_2, ..., x_n can be expressed as:
-- n / ((1 / x_1) + (1 / x_2) + ... + (1 / x_n))
CREATE TEMPORARY FUNCTION HarmonicMean(arr ARRAY<FLOAT64>)
RETURNS FLOAT64 LANGUAGE js AS """
var sum_of_reciprocals = 0;
for (var i = 0; i < arr.length; ++i) {
sum_of_reciprocals += 1 / arr[i];
}
return arr.length / sum_of_reciprocals;
""";
WITH T AS (
SELECT GENERATE_ARRAY(1.0, x * 4, x) AS arr
FROM UNNEST([1, 2, 3, 4, 5]) AS x
)
SELECT arr, HarmonicMean(arr) AS h_mean
FROM T;
Diese Abfrage definiert eine JavaScript-Funktion mit dem Namen HarmonicMean und wendet sie auf die Arrayspalte arr von T an.
Weitere Informationen zu benutzerdefinierten Funktionen finden Sie unter dem Thema Benutzerdefinierte Standard-SQL-Funktionen.
Funktionen verarbeiten Werte statt Zeilen
In Legacy-SQL verarbeiten JavaScript-Funktionen die Zeilen einer Tabelle. In Standard-SQL (siehe Beispiel oben) verarbeiten JavaScript-Funktionen Werte. Um einen Zeilenwert an eine JavaScript-Funktion unter Verwendung von Standard-SQL zu übergeben, definieren Sie eine Funktion, die eine Struktur des gleichen Zeilentyps wie die Tabelle verwendet. Beispiel:
#standardSQL
-- Takes a struct of x, y, and z and returns a struct with a new field foo.
CREATE TEMPORARY FUNCTION AddField(s STRUCT<x FLOAT64, y BOOL, z STRING>)
RETURNS STRUCT<x FLOAT64, y BOOL, z STRING, foo STRING> LANGUAGE js AS """
var new_struct = new Object();
new_struct.x = s.x;
new_struct.y = s.y;
new_struct.z = s.z;
if (s.y) {
new_struct.foo = 'bar';
} else {
new_struct.foo = 'baz';
}
return new_struct;
""";
WITH T AS (
SELECT x, MOD(off, 2) = 0 AS y, CAST(x AS STRING) AS z
FROM UNNEST([5.0, 4.0, 3.0, 2.0, 1.0]) AS x WITH OFFSET off
)
SELECT AddField(t).*
FROM T AS t;
Diese Abfrage definiert eine JavaScript-Funktion, die ein Struct mit demselben Zeilentyp wie T verwendet und ein neues Struct mit einem zusätzlichen Feld namens foo erstellt. Die SELECT-Anweisung übergibt die Zeile t als Eingabe an die Funktion und verwendet .*, um die Felder des resultierenden Structs in der Ausgabe zurückzugeben.
Highlights von Standard-SQL
In diesem Abschnitt werden einige der Highlights von Standard-SQL im Vergleich zu Legacy-SQL erläutert.
Komponierbarkeit unter Verwendung von WITH-Klauseln
In einigen Standard-SQL-Beispielen auf dieser Seite wird die WITH-Klausel genutzt, mit der benannte Unterabfragen extrahiert oder wiederverwendet werden können. Beispiel:
#standardSQL
WITH T AS (
SELECT x FROM UNNEST([1, 2, 3, 4]) AS x
)
SELECT x / (SELECT SUM(x) FROM T) AS weighted_x
FROM T;
Diese Abfrage definiert eine Namen-Unterabfrage T, die die x-Werte 1, 2, 3 und 4 enthält. Sie wählt die x-Werte von T aus und dividiert diese durch die Summe aller x-Werte in T. Diese Abfrage ist mit einer Abfrage identisch, bei der der Inhalt von T inline ist:
#standardSQL
SELECT
x / (SELECT SUM(x)
FROM (SELECT x FROM UNNEST([1, 2, 3, 4]) AS x)) AS weighted_x
FROM (SELECT x FROM UNNEST([1, 2, 3, 4]) AS x);
Betrachten Sie als weiteres Beispiel diese Abfrage, bei der mehrere Namen-Unterabfragen verwendet werden:
#standardSQL
WITH T AS (
SELECT x FROM UNNEST([1, 2, 3, 4]) AS x
),
TPlusOne AS (
SELECT x + 1 AS y
FROM T
),
TPlusOneTimesTwo AS (
SELECT y * 2 AS z
FROM TPlusOne
)
SELECT z
FROM TPlusOneTimesTwo;
Diese Abfrage definiert eine Reihe von Transformationen der ursprünglichen Daten gefolgt von einer SELECT-Anweisung über TPlusOneTimesTwo. Diese Abfrage ist mit der folgenden Abfrage identisch, bei der die Berechnungen inline erfolgen:
#standardSQL
SELECT (x + 1) * 2 AS z
FROM (SELECT x FROM UNNEST([1, 2, 3, 4]) AS x);
Weitere Informationen finden Sie unter WITH-Klausel.
Komponierbarkeit unter Verwendung von SQL-Funktionen
Standard-SQL unterstützt benutzerdefinierte SQL-Funktionen. Mit benutzerdefinierten SQL-Funktionen können allgemeine Ausdrücke definiert werden, auf die anschließend in der Abfrage verwiesen werden kann. Beispiel:
#standardSQL
-- Computes the harmonic mean of the elements in 'arr'.
-- The harmonic mean of x_1, x_2, ..., x_n can be expressed as:
-- n / ((1 / x_1) + (1 / x_2) + ... + (1 / x_n))
CREATE TEMPORARY FUNCTION HarmonicMean(arr ARRAY<FLOAT64>) AS
(
ARRAY_LENGTH(arr) / (SELECT SUM(1 / x) FROM UNNEST(arr) AS x)
);
WITH T AS (
SELECT GENERATE_ARRAY(1.0, x * 4, x) AS arr
FROM UNNEST([1, 2, 3, 4, 5]) AS x
)
SELECT arr, HarmonicMean(arr) AS h_mean
FROM T;
Diese Abfrage definiert eine SQL-Funktion mit dem Namen HarmonicMean und wendet sie auf die Arrayspalte arr von T an.
Unterabfragen an mehreren Stellen
Standard-SQL unterstützt Unterabfragen in der SELECT-Liste, in der WHERE-Klausel und an weiteren Stellen in der Abfrage, an denen ein Ausdruck erwartet wird. Betrachten Sie beispielsweise die folgende Abfrage in Standard-SQL, die den Bruchteil von warmen Tagen in Seattle im Jahr 2015 berechnet:
#standardSQL
WITH SeattleWeather AS (
SELECT *
FROM `bigquery-public-data.noaa_gsod.gsod2015`
WHERE stn = '994014'
)
SELECT
COUNTIF(max >= 70) /
(SELECT COUNT(*) FROM SeattleWeather) AS warm_days_fraction
FROM SeattleWeather;
Die Wetterstation von Seattle hat die ID '994014'. Die Abfrage berechnet die Anzahl von warmen Tagen anhand der Tage, an denen die Temperatur 70 Grad Fahrenheit oder ca. 21 Grad Celsius erreichte. Diese Anzahl wird dann durch die Gesamtanzahl der im Jahr 2015 aufgezeichneten Tage für diese Station dividiert.
Korrelierte Unterabfragen
In Standard-SQL können Unterabfragen auf korrelierte Spalten verweisen, d. h. Spalten, die von der äußeren Abfrage stammen. Betrachten Sie beispielsweise die folgende Abfrage in Standard-SQL:
#standardSQL
WITH WashingtonStations AS (
SELECT weather.stn AS station_id, ANY_VALUE(station.name) AS name
FROM `bigquery-public-data.noaa_gsod.stations` AS station
INNER JOIN `bigquery-public-data.noaa_gsod.gsod2015` AS weather
ON station.usaf = weather.stn
WHERE station.state = 'WA' AND station.usaf != '999999'
GROUP BY station_id
)
SELECT washington_stations.name,
(SELECT COUNT(*)
FROM `bigquery-public-data.noaa_gsod.gsod2015` AS weather
WHERE washington_stations.station_id = weather.stn
AND max >= 70) AS warm_days
FROM WashingtonStations AS washington_stations
ORDER BY warm_days DESC;
Diese Abfrage berechnet die Namen der Wetterstationen im US-Bundesstaat Washington sowie die Anzahl von Tagen im Jahr 2015, an denen die Temperatur 70 Grad Fahrenheit oder ca. 21 Grad Celsius erreichte. Beachten Sie, dass die Liste SELECT eine Unterabfrage enthält und dass die Unterabfrage auf washington_stations.station_id aus dem Außenbereich verweist, nämlich FROM WashingtonStations AS washington_stations.
Arrays und Structs
ARRAY und STRUCT sind leistungsstarke Konzepte in Standard-SQL. Betrachten Sie beispielsweise die folgende Abfrage, in der beide Konzepte verwendet werden und mit der für jeden Tag die besten zwei Artikel im Dataset HackerNews berechnet werden:
#standardSQL
WITH TitlesAndScores AS (
SELECT
ARRAY_AGG(STRUCT(title, score)) AS titles,
EXTRACT(DATE FROM time_ts) AS date
FROM `bigquery-public-data.hacker_news.stories`
WHERE score IS NOT NULL AND title IS NOT NULL
GROUP BY date)
SELECT date,
ARRAY(SELECT AS STRUCT title, score
FROM UNNEST(titles)
ORDER BY score DESC
LIMIT 2)
AS top_articles
FROM TitlesAndScores
ORDER BY date DESC;
Die WITH-Klausel definiert TitlesAndScores, das zwei Spalten enthält. Die erste Spalte ist ein Array von Structs, wobei ein Feld aus einem Artikeltitel und das zweite Feld aus einer Punktzahl besteht. Der Ausdruck ARRAY_AGG gibt ein Array dieser Structs für jeden Tag zurück.
Die SELECT-Anweisung gefolgt von der WITH-Klausel verwendet eine ARRAY-Unterabfrage, um die besten zwei Artikel in jedem Array in Übereinstimmung mit der Punktzahl score zu sortieren und zurückzugeben. Anschließend werden die Ergebnisse in absteigender Reihenfolge nach Datum zurückgegeben.
Weitere Informationen zu Arrays und ARRAY-Unterabfragen finden Sie unter Mit Arrays arbeiten. Nützlich sind auch die Referenzen zu Arrays und Structs.