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 et RIGHT 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 de DATE)
    • 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 signale bi_engine_statistics.acceleration_mode comme étant défini sur FULL_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 signale bi_engine_statistics.acceleration_mode comme étant défini sur FULL_INPUT ou FULL_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 clause OVER.
    • 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 clauses OVER semblent identiques.
  • 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 :

  1. Créez une requête personnalisée dans BigQuery.
  2. Enregistrez les résultats de la requête dans une table.
  3. 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.
  4. Référencez cette table dans vos requêtes critiques.