Optimierte SQL-Funktionen und -Operatoren

BigQuery BI Engine optimiert folgende GoogleSQL-Funktionen und -Operatoren, wenn BI-Tools (Business Intelligence) über benutzerdefinierte Abfragen oder Ansichten mit BigQuery verbunden werden. Abfragen, die Funktionen und Operatoren verwenden, die nicht in dieser Liste enthalten sind, werden möglicherweise langsamer ausgeführt.

Eine Liste der nicht unterstützten SQL-Funktionen finden Sie im Abschnitt Nicht unterstützte Funktionen.

Weitere Informationen zur Verwendung benutzerdefinierter Abfragen und Ansichten in Looker Studio finden Sie unter Mit BigQuery verbinden. Weitere Informationen zum Verbinden anderer BI-Tools mit BigQuery finden Sie unter Übersicht über die BigQuery BI Engine SQL-Schnittstelle.

Eine Liste aller GoogleSQL-Operatoren und -Funktionen finden Sie unter GoogleSQL-Funktionen und -Operatoren.

Optimierte Operatoren

• ORDER BY
• LIMIT count [OFFSET skip_rows]
• UNION ALL
• HAVING
• GROUP BY
• WHERE
• INNER, LEFT und RIGHT JOIN (mit kleineren Größenbeschränkungen)

Hinweis: Bei Joins in der BI Engine-SQL-Schnittstelle ist die Beschleunigung auf Unterabfragen auf Blattebene mit INNER und LEFT OUTER JOINS beschränkt. Dabei gilt: Eine große (Fakten)tabelle wird mit bis zu vier kleinen Tabellen verknüpft. Jede kleine Tabelle muss weniger als 5 Millionen Zeilen haben. Jede kleine Tabelle muss bei nicht partitionierten Tabellen kleiner als 5 GB und bei partitionierten Tabellen für die referenzierten Spalten kleiner als 1 GB sein.

Optimierte Funktionen

Vergleich

• =
• <>
• <
• <=
• >
• >=
• BETWEEN
• IN
• LEAST
• GREATEST
• CASE
• COALESCE
• IF
• ISNULL
• IS [NOT] NULL
• IS [NOT] TRUE
• IS [NOT] FALSE
• IFNULL
• NULLIF

Logisch

• NOT
• AND
• OR

Mathematik

• - (Unäres Minus)
• +
• - (binäres Minus)
• *
• /
• IEEE_DIVIDE
• DIV
• MOD
• ROUND
• TRUNC
• FLOOR
• CEIL
• ABS
• SIGN
• SQRT
• EXP
• POW
• IS_INF
• IS_NAN
• SIN
• SINH
• ASIN
• ASINH
• COS
• COSH
• ACOS
• ACOSH
• TAN
• TANH
• ATAN
• ATAN2
• ATANH
• LOG
• LN
• LOG10

Bitweise

• BIT_COUNT
• ~ (bitweise NOT)
• & (bitweise AND)
• | (bitweise OR)
• ^ (bitweise XOR)
• << (Bitweise Linksverschiebung)
• >> (Bitweise Rechtsverschiebung)

Zeichenfolgen und Bytes

• ASCII
• BYTE_LENGTH
• CHAR_LENGTH
• CHR
• CODE_POINTS_TO_BYTES
• CODE_POINTS_TO_STRING
• CONCAT
• ENDS_WITH
• FORMAT
• FROM_BASE32
• FROM_BASE64
• FROM_HEX
• INITCAP
• INSTR
• JSON_EXTRACT_SCALAR
• JSON_EXTRACT
• LEFT
• LENGTH
• LOWER
• LPAD
• LTRIM
• NORMALIZE
• NORMALIZE_AND_CASEFOLD
• REGEXP_CONTAINS
• REGEXP_EXTRACT
• REGEXP_INSTR
• REGEXP_REPLACE
• REPEAT
• REPLACE
• REVERSE
• RIGHT
• RPAD
• RTRIM
• SAFE_CONVERT_BYTES_TO_STRING
• SOUNDEX
• STARTS_WITH
• STRING_LIKE
• STRPOS
• SUBSTR
• TO_BASE32
• TO_BASE64
• TO_HEX
• TRANSLATE
• TRIM
• UNICODE
• UPPER

Zeitstempel, Daten und Zeiten

Wichtig: Zeitzonenargumente für die folgenden Funktionen müssen const sein.

• Zeitstempelfunktionen
  • TIMESTAMP_MICROS
  • TIMESTAMP_MILLIS
  • TIMESTAMP_SECONDS
  • TIMESTAMP
  • STRING(timestamp_expression,[timestamp_expression])
  • UNIX_SECONDS
  • UNIX_MILLIS
  • UNIX_MICROS
  • FORMAT_TIMESTAMP
  • PARSE_TIMESTAMP
  • TIMESTAMP_ADD
  • TIMESTAMP_SUB
  • TIMESTAMP_DIFF
  • EXTRACT
  • DATE
  • TIME
  • DATETIME
  • TIMESTAMP_TRUNC
• Datumsfunktionen
  • DATE(Jahr, Monat, Tag)
  • DATE_FROM_UNIX_DATE
  • UNIX_DATE
  • FORMAT_DATE
  • PARSE_DATE
  • DATE_ADD
  • DATE_SUB
  • DATE_DIFF
  • EXTRACT (von DATE)
  • DATE_TRUNC
  • CURRENT_TIMESTAMP
• Zeitfunktionen
  • EXTRACT
  • FORMAT_TIME
  • PARSE_TIME
  • TIME_ADD
  • TIME_SUB
  • TIME_DIFF
  • TIME_TRUNC
  • CURRENT_TIME
• Datums-/Zeitfunktionen
  • DATETIME
  • EXTRACT
  • FORMAT_DATETIME
  • PARSE_DATETIME
  • DATETIME_ADD
  • DATETIME_SUB
  • DATETIME_DIFF
  • DATETIME_TRUNC
  • CURRENT_DATETIME

Hashing

• FARM_FINGERPRINT
• MD5
• SHA1
• SHA256
• SHA512

Netzwerk

• NET_IP_IN_NET
• NET_MAKE_NET
• NET.HOST
• NET.REG_DOMAIN
• NET.PUBLIC_SUFFIX
• NET.IP_FROM_STRING
• NET.IP_TO_STRING
• NET.IP_NET_MASK
• NET.IP_TRUNC
• NET.IPV4_FROM_INT64
• NET.IPV4_TO_INT64

Geografie

• Konstruktoren
  • ST_GEOGPOINT
  • ST_MAKELINE
  • ST_MAKEPOLYGON
  • ST_MAKEPOLYGONORIENTED
• Parser
  • ST_GEOGFROMGEOJSON
  • ST_GEOGFROMTEXT
  • ST_GEOGFROMWKB
  • ST_GEOGFROMWKB
  • ST_GEOGPOINTFROMGEOHASH
• Formatierer
  • ST_ASBINARY
  • ST_ASGEOJSON
  • ST_ASKML
  • ST_GEOHASH
• Transformationen
  • ST_BOUNDARY
  • ST_CENTROID
  • ST_CLOSESTPOINT
  • ST_CONVEXHULL
  • ST_DIFFERENCE
  • ST_EXTERIORRING
  • ST_INTERSECTION
  • ST_SIMPLIFY
  • ST_SNAPTOGRID
• Zugriffsfunktionen
  • ST_DIMENSION
  • ST_ENDPOINT
  • ST_GEOMETRYTYPE
  • ST_ISCOLLECTION
  • ST_ISEMPTY
  • ST_NPOINTS
  • ST_NUMGEOMETRIES
  • ST_NUMPOINTS
  • ST_POINTN
  • ST_STARTPOINT
  • ST_X
  • ST_Y
• Prädikate
  • ST_CONTAINS
  • ST_COVEREDBY
  • ST_COVERS
  • ST_DISJOINT
  • ST_DWITHIN
  • ST_EQUALS
  • ST_INTERSECTS
  • ST_INTERSECTSBOX
  • ST_TOUCHES
  • ST_WITHIN
• Messung
  • ST_ANGLE
  • ST_AREA
  • ST_AZIMUTH
  • ST_DISTANCE
  • ST_LENGTH
  • ST_MAXDISTANCE
  • ST_PERIMETER

SAFE-Funktionen

• Mathematik
  • MOD
  • ROUND
  • ABS
  • SQRT
  • POW
  • SINH
  • ASIN
  • COSH
  • ACOS
  • ACOSH
  • ATANH
  • LN
  • LOG
  • LOG10
  • FLOOR
  • CEIL
  • DIV
  • EXP
  • ROUND
  • SAFE_NEGATE
  • SAFE_ADD
  • SAFE_SUBTRACT
  • SAFE_MULTIPLY
  • SAFE_DIVIDE
• Strings
  • REGEXP_CONTAINS
  • REGEXP_EXTRACT
  • REGEXP_REPLACE
  • FORMAT
  • SUBSTR
  • ASCII
  • SAFE_CONVERT_BYTES_TO_STRING
  • INSTR
  • LEFT
  • RIGHT
  • TRANSLATE
  • INITCAP
• Zeitstempel, Daten und Zeiten
  • DATE_FROM_UNIX_DATE
  • PARSE_DATE
  • PARSE_DATETIME
  • PARSE_TIME
  • PARSE_TIMESTAMP
  • FORMAT_TIMESTAMP
  • TIMESTAMP_SECONDS
  • TIMESTAMP_MILLIS
  • TIMESTAMP_MICROS
• Netzwerk
  • NET.SAFE_IP_FROM_STRING
• Andere
  • SAFE_CAST

Aggregationsfunktionen

• SUM
• COUNT
• COUNTIF
• ANY_VALUE
• AVG
• MAX
• MIN
• HLL_COUNT.INIT
• HLL_COUNT.MERGE
• HLL_COUNT.MERGE_PARTIAL
• Die GROUP BY-Klausel
• BIT_AND
• BIT_OR
• BIT_XOR
• LOGICAL_AND
• LOGICAL_OR
• DISJUNCTION_AGG
• STRING_AGG
• CORR
• COVAR_POP
• COVAR_SAMP
• STDDEV_POP
• STDDEV_SAMP
• STDDEV
• VAR_POP
• VAR_SAMP
• VARIANCE
• APPROX_COUNT_DISTINCT

Fensterfunktionen

Für Fensterfunktionen, die auch als Analysefunktionen bezeichnet werden, gelten die folgenden Einschränkungen, wenn sie von BigQuery BI Engine beschleunigt werden:

• Die Eingabephasen werden von BigQuery BI Engine beschleunigt, wenn sie keine Fensterfunktionen haben. In diesem Fall INFORMATION_SCHEMA.JOBS rufen Sie die Berichte bi_engine_statistics auf.acceleration_mode als FULL_INPUT.
• Die Eingabephasen von Abfragen mit Fensterfunktionen in ihren Eingabephasen werden von BI Engine beschleunigt, können jedoch nicht die im Abschnitt zu Einschränkungen für BI Engine-Fensterfunktionen beschriebenen Einschränkungen haben. In diesem Fall werden die Eingabephasen oder die vollständige Abfrage in BI Engine ausgeführt. In diesem Fall INFORMATION_SCHEMA.JOBS rufen Sie die Berichte bi_engine_statistics auf.acceleration_mode als FULL_INPUT oder FULL_QUERY.

Weitere Informationen zum Feld BiEngineStatistics finden Sie in der Jobreferenz.

Einschränkungen für BI Engine-Fensterfunktionen

Abfragen mit Fensterfunktionen werden in BI Engine nur dann ausgeführt, wenn alle folgenden Bedingungen erfüllt sind:

• Bei der Abfrage wird genau eine Tabelle gescannt.
  • Die Tabelle ist nicht partitioniert.
  • Die Tabelle enthält weniger als fünf Millionen Zeilen.
• Die Abfrage enthält keine JOIN-Operatoren.
• Die Größe der gescannten Tabelle mal der Anzahl der Operatoren der Fensterfunktion überschreitet 300 MB nicht. Zwei Fensterfunktionen mit identischen OVER-Klauseln und denselben direkten Eingaben können denselben Fensterfunktionsoperator nutzen. Beispiel:
  • Die Abfrage SELECT ROW_NUMBER() OVER (ORDER BY x), SUM(x) OVER (ORDER BY x) FROM my_table hat nur einen Operator der Fensterfunktion.
  • Die Abfrage SELECT ROW_NUMBER() OVER (ORDER BY x), SUM(x) OVER (PARTITION BY y ORDER BY x) FROM my_table hat zwei Operatoren für Fensterfunktionen, da die beiden Funktionen nicht dieselbe OVER-Klausel haben.
  • Die Abfrage SELECT ROW_NUMBER() OVER (ORDER BY x) FROM (SELECT SUM(x) OVER (ORDER BY x) AS x FROM my_table) hat zwei Operatoren für Fensterfunktionen, da die beiden Funktionen unterschiedliche direkte Eingaben haben, obwohl die OVER-Klauseln identisch sind.
• Die referenzierten Fensterfunktionen befinden sich in der folgenden Liste der unterstützten Funktionen.

Liste der unterstützten Fensterfunktionen:

• ANY_VALUE
• AVG
• BIT_AND
• BIT_OR
• BIT_XOR
• CORR
• COUNT
• COUNTIF
• COVAR_POP
• COVAR_SAMP
• CUME_DIST
• DENSE_RANK
• FIRST_VALUE
• LAG
• LAST_VALUE
• LEAD
• LOGICAL_AND
• LOGICAL_OR
• MAX
• MIN
• NTH_VALUE
• NTILE
• PERCENT_RANK
• PERCENTILE_CONT
• PERCENTILE_DISC
• RANK
• ROW_NUMBER
• ST_CLUSTERDBSCAN
• STDDEV_POP
• STDDEV_SAMP
• STDDEV
• STRING_AGG
• SUM
• VAR_POP
• VAR_SAMP

• VARIANCE

  Weitere Funktionen

• CAST

• HLL_COUNT.EXTRACT

• Der Punktoperator für STRUCT-Felder

• ARRAY_LENGTH

Nicht unterstützte Funktionen

Die BI Engine-Beschleunigung ist für die folgenden Features nicht verfügbar:

• JavaScript-UDFs
• Externe Tabellen
• Native JSON-Daten abfragen
• Ergebnisse in eine permanente BigQuery-Tabelle schreiben
• Tabellen, die Upserts über BigQuery Change Data Capture enthalten
• Transaktionen
• Abfragen, die mehr als 1 GB an Daten zurückgeben. Für latenzempfindliche Anwendungen wird eine Antwortgröße von weniger als 1 MB empfohlen.

Die folgenden Features werden in Looker Studio nur eingeschränkt unterstützt:

• SQL-UDFs
• ARRAY-Spalten abfragen

Während einige SQL-Funktionen in benutzerdefinierten Abfragen von BigQuery BI Engine nicht unterstützt werden, gibt es eine verfügbare Problemumgehung:

1. Erstellen Sie eine benutzerdefinierte Abfrage in BigQuery.
2. Speichern Sie die Ergebnisse der Abfrage in einer Tabelle.
3. Planen Sie Ihre benutzerdefinierte Abfrage, um die Tabelle regelmäßig zu aktualisieren. Eine stündliche oder tägliche Aktualisierungsrate funktioniert am besten, da eine Aktualisierung jede Minute den BI Engine-Cache häufiger ungültig macht.
4. Verweisen Sie in leistungskritischen Abfragen auf diese Tabelle.