Zu Standard-SQL migrieren

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:

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.

Legacy-SQL Standard-SQL Hinweise
INTEGER(x) SAFE_CAST(x AS INT64)
CAST(x AS INTEGER) SAFE_CAST(x AS INT64)
DATEDIFF(t1, t2) TIMESTAMP_DIFF(t1, t2, DAY)
NOW CURRENT_TIMESTAMP
STRFTIME_UTC_USEC(t, fmt) FORMAT_TIMESTAMP(fmt, t)
UTC_USEC_TO_DAY(t) TIMESTAMP_TRUNC(t, DAY)
REGEXP_MATCH(s, pattern) REGEXP_CONTAINS(s, pattern)
IS_NULL(x) x IS NULL
LEFT(s, len) SUBSTR(s, 0, len)
RIGHT(s, len) SUBSTR(s, -len)
s CONTAINS "foo" STRPOS(s, "foo") > 0 oder s LIKE '%foo%'
x % y MOD(x, y)
NEST(x) ARRAY_AGG(x)
ANY(x) ANY_VALUE(x)
GROUP_CONCAT_UNQUOTED(s, sep) STRING_AGG(s, sep)
SOME(x) IFNULL(LOGICAL_OR(x), false)
EVERY(x) IFNULL(LOGICAL_AND(x), true)
COUNT(DISTINCT x) APPROX_COUNT_DISTINCT(x) siehe Hinweise unten
EXACT_COUNT_DISTINCT(x) COUNT(DISTINCT x) siehe Hinweise unten
QUANTILES(x, buckets + 1) APPROX_QUANTILES(x, buckets)
TOP(x, num), COUNT(*) APPROX_TOP_COUNT(x, num)
NTH(index, arr) WITHIN RECORD arr[SAFE_ORDINAL(index)]
COUNT(arr) WITHIN RECORD ARRAY_LENGTH(arr)
HOST(url) NET.HOST(url) siehe Unterschiede unten
TLD(url) NET.PUBLIC_SUFFIX(url) siehe Unterschiede unten
DOMAIN(url) NET.REG_DOMAIN(url) siehe Unterschiede unten
PARSE_IP(addr_string) NET.IPV4_TO_INT64(NET.IP_FROM_STRING(addr_string))
FORMAT_IP(addr_int64) NET.IP_TO_STRING(NET.IPV4_FROM_INT64(addr_int64 & 0xFFFFFFFF))
PARSE_PACKED_IP(addr_string) NET.IP_FROM_STRING(addr_string)
FORMAT_PACKED_IP(addr_bytes) NET.IP_TO_STRING(addr_bytes)

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_SUFFIX gibt 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_SUFFIX und NET.REG_DOMAIN unterstützen Großbuchstaben und internationalisierte Domainnamen. TLD und DOMAIN bieten 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_SUFFIX und NET.REG_DOMAIN NULL zurück, während TLD und DOMAIN von 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_SUFFIX das öffentliche Suffix zurück, während TLD einen leeren String zurückgibt. Entsprechend gibt NET.REG_DOMAIN NULL zurück, während DOMAIN das öffentliche Suffix zurückgibt.
  • Bei Eingaben mit IPv6-Hosts behält NET.HOST die eckigen Klammern aus dem Ergebnis gemäß RFC 3986 bei.
  • Bei Eingaben mit IPv4-Hosts gibt NET.REG_DOMAIN NULL zurück, während DOMAIN die 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.

  • INT64 literal zu TIMESTAMP. Verwenden Sie stattdessen TIMESTAMP_MICROS(micros_value).
  • STRING literal zu INT64, FLOAT64 oder BOOL. Verwenden Sie stattdessen CAST(str AS INT64), CAST(str AS FLOAT64) oder CAST(str AS BOOL).
  • STRING zu BYTES. Verwenden Sie stattdessen CAST(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 Aggregatfunktionen SUM, AVG und STDDEV sowie weitere.
  • Standard-SQL löst bei einer Division durch null einen Fehler aus, während Legacy-SQL NULL zurückgibt. Wenn Standard-SQL bei einer Division durch null den Wert NULL zurückgeben soll, verwenden Sie SAFE_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 Wert NULL zurückgegeben. Verwenden Sie SAFE_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.