Opérateurs et fonctions SQL optimisés
BigQuery BI Engine optimise les fonctions et opérateurs GoogleSQL suivants lors de la connexion d'outils d'informatique décisionnelle à BigQuery à l'aide de requêtes ou de vues personnalisées. Les requêtes qui utilisent des fonctions et des opérateurs qui ne figurent pas dans cette liste peuvent s'exécuter lentement.
Pour obtenir la liste des fonctionnalités SQL non compatibles, consultez la section Fonctionnalités non compatibles.
Pour plus d'informations sur l'utilisation des requêtes et des vues personnalisées dans Looker Studio, consultez Se connecter à BigQuery. Pour en savoir plus sur la connexion d'autres outils d'informatique décisionnelle à BigQuery, consultez la page Présentation de l'interface SQL de BigQuery BI Engine.
Pour obtenir la liste de tous les opérateurs et de toutes les fonctions GoogleSQL, consultez la page Fonctions et opérateurs GoogleSQL.
Opérateurs optimisés
ORDER BY
LIMIT
count [OFFSET
skip_rows]UNION ALL
HAVING
GROUP BY
WHERE
INNER
,LEFT
etRIGHT JOIN
(avec des limites de taille inférieures)
Fonctions optimisées
Comparaison
=
<>
<
<=
>
>=
BETWEEN
IN
LEAST
GREATEST
CASE
COALESCE
IF
ISNULL
IS [NOT] NULL
IS [NOT] TRUE
IS [NOT] FALSE
IFNULL
NULLIF
Logique
NOT
AND
OR
Math
-
(moins unaire)+
-
(moins binaire)*
/
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
Bit à bit
BIT_COUNT
~
(NOT
bit à bit)&
(AND
bit à bit)|
(OR
bit à bit)^
(XOR
bit à bit)<<
(décalage à gauche bit à bit)>>
(décalage à droite bit à bit)
Chaînes et octets
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
Horodatages, dates et heures
- Fonctions d'horodatage
TIMESTAMP_MICROS
TIMESTAMP_MILLIS
TIMESTAMP_SECONDS
TIMESTAMP
STRING
(timestamp_expression,[fuseau horaire])UNIX_SECONDS
UNIX_MILLIS
UNIX_MICROS
FORMAT_TIMESTAMP
PARSE_TIMESTAMP
TIMESTAMP_ADD
TIMESTAMP_SUB
TIMESTAMP_DIFF
EXTRACT
DATE
TIME
DATETIME
TIMESTAMP_TRUNC
- Fonctions de date
DATE
(année, mois, jour)DATE_FROM_UNIX_DATE
UNIX_DATE
FORMAT_DATE
PARSE_DATE
DATE_ADD
DATE_SUB
DATE_DIFF
EXTRACT
(à partir deDATE
)DATE_TRUNC
CURRENT_TIMESTAMP
- Fonctions TIME
EXTRACT
FORMAT_TIME
PARSE_TIME
TIME_ADD
TIME_SUB
TIME_DIFF
TIME_TRUNC
CURRENT_TIME
- Fonctions DATETIME
DATETIME
EXTRACT
FORMAT_DATETIME
PARSE_DATETIME
DATETIME_ADD
DATETIME_SUB
DATETIME_DIFF
DATETIME_TRUNC
CURRENT_DATETIME
Hachage
FARM_FINGERPRINT
MD5
SHA1
SHA256
SHA512
Réseau
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
Géographie
- Constructeurs
ST_GEOGPOINT
ST_MAKELINE
ST_MAKEPOLYGON
ST_MAKEPOLYGONORIENTED
- Analyseurs
ST_GEOGFROMGEOJSON
ST_GEOGFROMTEXT
ST_GEOGFROMWKB
ST_GEOGFROMWKB
ST_GEOGPOINTFROMGEOHASH
- Outils de mise en forme
ST_ASBINARY
ST_ASGEOJSON
ST_ASKML
ST_GEOHASH
- Transformations
ST_BOUNDARY
ST_CENTROID
ST_CLOSESTPOINT
ST_CONVEXHULL
ST_DIFFERENCE
ST_EXTERIORRING
ST_INTERSECTION
ST_SIMPLIFY
ST_SNAPTOGRID
- Accesseurs
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édicats
ST_CONTAINS
ST_COVEREDBY
ST_COVERS
ST_DISJOINT
ST_DWITHIN
ST_EQUALS
ST_INTERSECTS
ST_INTERSECTSBOX
ST_TOUCHES
ST_WITHIN
- Mesures
ST_ANGLE
ST_AREA
ST_AZIMUTH
ST_DISTANCE
ST_LENGTH
ST_MAXDISTANCE
ST_PERIMETER
Fonctions SAFE
- Mathématiques
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
- Chaînes
REGEXP_CONTAINS
REGEXP_EXTRACT
REGEXP_REPLACE
FORMAT
SUBSTR
ASCII
SAFE_CONVERT_BYTES_TO_STRING
INSTR
LEFT
RIGHT
TRANSLATE
INITCAP
- Horodatages, dates et heures
DATE_FROM_UNIX_DATE
PARSE_DATE
PARSE_DATETIME
PARSE_TIME
PARSE_TIMESTAMP
FORMAT_TIMESTAMP
TIMESTAMP_SECONDS
TIMESTAMP_MILLIS
TIMESTAMP_MICROS
- Mise en réseau
NET.SAFE_IP_FROM_STRING
- Autre
SAFE_CAST
Fonctions d'agrégation
SUM
COUNT
COUNTIF
ANY_VALUE
AVG
MAX
MIN
HLL_COUNT.INIT
HLL_COUNT.MERGE
HLL_COUNT.MERGE_PARTIAL
- Clause
GROUP BY
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
Fonctions de fenêtrage
Le fenêtrage, terme qui désigne également les fonctions analytiques, présente les limites suivantes lorsqu'il bénéficie de l'accélération BigQuery BI Engine :
- Les étapes d'entrée sont accélérées par BigQuery BI Engine si elles ne comportent pas de fenêtrage. Dans ce cas, la vue
INFORMATION_SCHEMA.JOBS
signalebi_engine_statistics
.acceleration_mode
comme étant défini surFULL_INPUT
. - Les requêtes dont les étapes d'entrée comportent du fenêtrage sont accélérées par BI Engine, mais ne sont pas soumises aux limites décrites dans la section Limites applicables au fenêtrage BI Engine.
Dans ce cas, les étapes d'entrée ou la requête complète sont exécutées dans BI Engine. Et dans ce cas, la vue
INFORMATION_SCHEMA.JOBS
signalebi_engine_statistics
.acceleration_mode
comme étant défini surFULL_INPUT
ouFULL_QUERY
.
Pour en savoir plus sur le champ BiEngineStatistics
, consultez la documentation de référence relative aux tâches.
Limites applicables au fenêtrage BI Engine
Les requêtes avec fenêtrage ne sont exécutées dans BI Engine que si toutes les conditions suivantes sont remplies :
- La requête analyse exactement une table.
- La table n'est pas partitionnée.
- La table contient moins de cinq millions de lignes.
- La requête ne comporte aucun opérateur
JOIN
. - La taille de la table analysée multipliée par le nombre d'opérateurs de fonction analytique ne dépasse pas 300 Mo. Deux fonctions analytiques ayant des clauses
OVER
identiques et les mêmes entrées directes peuvent partager le même opérateur de fonction analytique. Exemple :- La requête
SELECT ROW_NUMBER() OVER (ORDER BY x), SUM(x) OVER (ORDER BY x) FROM my_table
ne comporte qu'un seul opérateur de fonction analytique. - La requête
SELECT ROW_NUMBER() OVER (ORDER BY x), SUM(x) OVER (PARTITION BY y ORDER BY x) FROM my_table
comporte deux opérateurs de fonction analytique, car ces deux fonctions n'ont pas la même clauseOVER
. - La requête
SELECT ROW_NUMBER() OVER (ORDER BY x) FROM (SELECT SUM(x) OVER (ORDER BY x) AS x FROM my_table)
comporte deux opérateurs de fonction analytique, car les deux fonctions ont des entrées directes différentes, même si leurs clausesOVER
semblent identiques.
- La requête
- Les fonctions analytiques référencées figurent dans la liste suivante des fonctions compatibles.
Liste des fonctions analytiques compatibles :
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
Autres fonctions
CAST
HLL_COUNT.EXTRACT
Opérateur point pour les champs
STRUCT
ARRAY_LENGTH
Fonctionnalités non compatibles
L'accélération de BI Engine n'est pas disponible pour les fonctionnalités suivantes :
- Fonctions JavaScript définies par l'utilisateur
- Tables externes
- Interroger des données JSON natives
- Écrire des résultats dans une table BigQuery permanente
- Tables contenant des opérations upsert via une capture de données modifiées de BigQuery
- Transactions
- Requêtes renvoyant plus de 1 Go de données. Pour les applications sensibles à la latence, nous vous recommandons d'utiliser une taille de réponse inférieure à 1 Mo.
Les fonctionnalités suivantes sont limitées dans Looker Studio :
- Fonctions SQL définies par l'utilisateur
- Interrogation des colonnes
ARRAY
Bien que certaines fonctionnalités SQL ne soient pas disponibles pour les requêtes personnalisées de BigQuery BI Engine, une solution de contournement est disponible :
- Créez une requête personnalisée dans BigQuery.
- Enregistrez les résultats de la requête dans une table.
- Planifiez votre requête personnalisée pour mettre à jour la table régulièrement. Une fréquence d'actualisation horaire ou quotidienne fonctionne mieux, car actualiser toutes les minutes rend plus fréquemment non valide le cache de BI Engine.
- Référencez cette table dans vos requêtes critiques.