Requêtes sur les vues matérialisées continues

Pour créer une vue matérialisée continue d'une table Bigtable, exécutez une requête SQL qui définit la vue matérialisée continue.

Ce document décrit les concepts et les modèles qui vous aideront à préparer votre requête SQL sur une vue matérialisée continue. Avant de lire ce document, vous devez connaître les vues matérialisées continues et GoogleSQL pour Bigtable.

Les vues matérialisées continues utilisent une syntaxe SQL limitée. Le modèle suivant montre comment créer une requête SQL sur une vue matérialisée continue :

SELECT
  expression AS alias [, ...]
FROM from_item
[ WHERE bool_expression ]
GROUP BY expression [, ...];

from_item:
    {
      table_name [ as_alias ]
      | field_path
      }

as_alias:
    [ AS ] alias

Si vous souhaitez créer une requête SQL sur une vue matérialisée continue en tant qu'index secondaire asynchrone, utilisez la clause ORDER BY :

SELECT
  expression AS alias [, ...]
FROM from_item
[ WHERE bool_expression ]
ORDER BY expression [, ...];

from_item:
    {
      table_name [ as_alias ]
      | field_path
      }

as_alias:
    [ AS ] alias

Limites des requêtes

Les règles suivantes s'appliquent à une requête SQL utilisée pour créer une vue matérialisée continue :

• Doit être une instruction SELECT.
• Doit comporter une clause GROUP BY ou, pour les requêtes d'index secondaire asynchrone, une clause ORDER BY, mais pas les deux.
• Ne doit utiliser que les fonctions d'agrégation compatibles.
• Peut comporter plusieurs agrégations par groupe.

Agrégations compatibles

Vous pouvez utiliser les fonctions d'agrégation suivantes dans une requête SQL qui définit une vue matérialisée continue :

• COUNT
• SUM
• MIN
• MAX
• HLL_COUNT.INIT
• HLL_COUNT.MERGE
• HLL_COUNT.MERGE_PARTIAL
• ANY_VALUE
• BIT_AND
• BIT_OR
• BIT_XOR
• AVG

Si vous SELECT COUNT(*) , vous devez définir une clé de ligne, comme dans l'exemple suivant :

SELECT
  '*' AS _key,
  COUNT(*) AS count
FROM
  foo
GROUP BY
  _key;

Fonctionnalités SQL non compatibles

Vous ne pouvez pas utiliser les fonctionnalités SQL suivantes :

• Toute fonctionnalité non compatible avec GoogleSQL pour Bigtable
• ARRAY
• ARRAY_AGG
• ARRAY_CONCAT_AGG
• COUNT_IF
• CURRENT_TIME et autres fonctions non déterministes
• DATE, DATETIME en tant que colonnes de sortie (utilisez TIMESTAMP ou stockez une chaîne).
• Tri DESC dans la sortie
• Option DISTINCT, comme dans SUM(*DISTINCT* value))
• LIMIT/OFFSET
• SELECT *
• Clause OVER pour créer une agrégation de fenêtrage
• STRUCT

Vous ne pouvez pas non plus imbriquer les clauses GROUP BY ou ORDER BY, ni créer de colonnes de carte. Pour connaître les autres limites, consultez la section Limites.

Éviter les lignes exclues

Les lignes d'entrée sont exclues d'une vue matérialisée continue dans les cas suivants :

• Plus de 1 Mio de données sont sélectionnées dans la ligne. Par exemple, si votre requête est SELECT apple AS apples , SUM(banana) AS sum_bananas FROM my_table GROUP BY apples, toute ligne contenant plus de 1 Mio de données dans les apple et banana colonnes est exclue de la vue matérialisée continue.
• Plus de 1 Mio de données sont générées à partir de la ligne. Cela peut se produire lorsque vous utilisez des requêtes telles que SELECT REPEAT(apple, 1000) ou des constantes volumineuses.
• Plus de 10 fois plus de données sont générées que celles sélectionnées.
• La requête ne correspond pas à vos données. Cela inclut la tentative de division par zéro, le dépassement de capacité d'un entier ou l'attente d'un format de clé de ligne qui n'est pas utilisé dans chaque clé de ligne.

Les lignes exclues incrémentent la métrique des erreurs utilisateur lors de leur premier traitement. Pour en savoir plus sur les métriques qui peuvent vous aider à surveiller vos vues matérialisées continues, consultez la section Métriques.

Détails de la requête

Cette section décrit une requête sur une vue matérialisée continue et comment les résultats peuvent s'afficher lorsque la vue est interrogée. Les données de la table source sont les entrées, et les données de résultat de la vue matérialisée continue sont les sorties. Les données de sortie sont agrégées ou non agrégées (dans la clé définie).

Instruction SELECT

L'instruction select configure les colonnes et les agrégations utilisées dans la vue matérialisée continue. L'instruction doit utiliser une clause GROUP BY pour agréger les lignes ou une clause ORDER BY pour créer un index secondaire asynchrone.

SELECT * n'est pas compatible, mais SELECT COUNT(*) l'est.

Comme dans une instruction SELECT classique, vous pouvez avoir plusieurs agrégations par ensemble de données groupé. Les colonnes non groupées doivent être un résultat d'agrégation.

Voici un exemple de requête d'agrégation GROUP BY standard en SQL :

SELECT
  myfamily["node"] AS node,
  myfamily["type"] AS type,
  COUNT(clicks) AS clicks_per_key
FROM
  mytable
GROUP BY
  node,
  type

Clés de ligne et données non agrégées

Vous pouvez spécifier une _key comme clé de ligne pour une vue matérialisée continue. Si vous ne le faites pas, les colonnes de la clause GROUP BY forment la clé dans la vue.

Clés de ligne définies par une colonne _key

Vous pouvez éventuellement spécifier une colonne _key lorsque vous définissez votre vue matérialisée continue. (Elle est différente de la _key colonne que vous obtenez lorsque vous exécutez une requête SQL sur une table Bigtable.) Si vous spécifiez une _key, les règles suivantes s'appliquent :

• Vous devez regrouper par _key, et vous ne pouvez rien regrouper d'autre, sauf (éventuellement) par _timestamp. Pour en savoir plus, consultez la section Horodatages.
• La colonne _key doit être de type BYTES.

Spécifier une _key est utile si vous prévoyez de lire la vue avec ReadRows plutôt qu'avec SQL, car cela vous permet de contrôler le format de la clé de ligne. En revanche, une requête SQL sur une vue avec une _key définie peut avoir besoin de décoder explicitement la _key au lieu de renvoyer uniquement des colonnes de clé structurées.

Clés de ligne définies par une clause GROUP BY ou ORDER BY

Si vous ne spécifiez pas de _key, les colonnes non agrégées de votre liste SELECT deviennent la clé de ligne dans la vue. Vous pouvez attribuer aux colonnes de clé tous les noms compatibles avec les conventions SQL. Utilisez cette approche si vous prévoyez d'utiliser SQL pour interroger la vue plutôt qu'une requête ReadRows.

Les colonnes de sortie non agrégées de la liste SELECT doivent être incluses dans la clause GROUP BY. L'ordre dans lequel les colonnes sont écrites dans la clause GROUP BY correspond à l'ordre dans lequel les données sont stockées dans la clé de ligne de la vue matérialisée continue. Par exemple, GROUP BY a, b, c est implicitement ORDER BY a ASC, b ASC, c ASC.

Si vous utilisez une clause ORDER BY au lieu d'une clause GROUP BY pour créer un index secondaire asynchrone, les colonnes de votre liste SELECT qui font partie de la clause ORDER BY deviennent la clé de ligne dans la vue. L'ordre dans lequel les colonnes sont écrites dans la clause ORDER BY correspond à l'ordre dans lequel les données sont stockées dans la clé de ligne de la vue matérialisée continue. Par exemple, ORDER BY a, b, c stocke les données avec des clés de ligne triées par a ASC, puis b ASC, puis c ASC.

Important : Les colonnes qui forment la clé de ligne dans une vue matérialisée continue ne doivent pas être NULL. Une valeur NULL dans une colonne de clé entraîne l'exclusion des données de la ligne correspondante dans la table source de la vue.

Votre filtre SQL doit éliminer les valeurs NULL potentielles ou d'autres valeurs non valides pouvant entraîner des erreurs. Une ligne non valide, par exemple celle contenant une colonne de clé NULL, est omise des résultats et comptabilisée dans la métrique materialized_view/user_errors. Pour déboguer les erreurs utilisateur, essayez d'exécuter la requête SQL en dehors d'une vue matérialisée continue.

Données agrégées

Les colonnes d'agrégation de la requête définissent les calculs qui génèrent les données dans la vue matérialisée continue.

L'alias d'une colonne d'agrégation est traité comme un qualificateur de colonne dans la vue matérialisée continue.

Prenons l'exemple suivant :

SELECT
  fam["baz"] AS baz,
  SUM(fam["foo"]) AS sum_foo,
  SUM(fam["bar"]) AS sum_bar
FROM
  TABLE

GROUP BY
  baz;

La sortie de la requête présente les caractéristiques suivantes :

• La sortie de chaque baz se trouve dans une ligne distincte dans l'ordre baz ASC.
• Si un baz donné comporte au moins un foo, la sum_foo de la ligne de sortie est une valeur non NULL.
• Si un baz donné comporte au moins un bar, la sum_bar de la ligne de sortie est une valeur non NULL.
• Si un baz donné n'a aucune valeur pour l'une ou l'autre des colonnes, il est omis des résultats.

Ensuite, si vous interrogez la vue avec SELECT *, le résultat ressemble à ce qui suit :

baz	sum_foo	sum_bar
baz1	sum_foo1	sum_bar1
baz2	sum_foo2	sum_bar2

Horodatages

L'horodatage par défaut d'une cellule de sortie dans une vue matérialisée continue est 0 (1970-01-01 00:00:00Z). Il est visible lorsque vous lisez la vue avec ReadRows et non lorsque vous l'interrogez avec SQL.

Pour utiliser un autre horodatage dans la sortie, vous pouvez ajouter une colonne de type TIMESTAMP à la liste SELECT de la requête et la nommer _timestamp. Si vous interrogez la vue matérialisée continue à l'aide de ReadRows, _timestamp devient l'horodatage des autres cellules de la ligne.

Un horodatage ne doit pas être NULL, doit être supérieur ou égal à zéro et doit être un multiple de 1 000 (précision en millisecondes). Bigtable n'accepte pas les horodatages de cellule antérieurs à l'époque Unix (1970-01-01T00:00:00Z).

Prenons l'exemple suivant, qui rééchantillonne les données agrégées par jour. La requête utilise la fonction UNPACK.

SELECT
  _key,
  TIMESTAMP_TRUNC(_timestamp, DAY) AS _timestamp,
  SUM(sum_family["sum_column"]) AS sum_column,
  SUM(sum_family["foo"]) AS second_sum_column
FROM
  UNPACK(
  SELECT
    *
  FROM
    my_table(with_history => TRUE))
GROUP BY
  1,
  2

Si un SUM donné comporte une entrée non vide pour un jour donné, la ligne de sortie contient une valeur agrégée avec un horodatage correspondant au jour tronqué.

Si vous interrogez la vue avec SELECT *, le résultat ressemble à ce qui suit :

_key	_timestamp	sum_column	second_sum_column
1	2024-05-01 00:00:00Z	23	99
2	2024-05-02 00:00:00Z	45	201
3	2024-05-03 00:00:00Z	NULL	56
4	2024-05-04 00:00:00Z	8	NULL

Encodage

Si vous interrogez votre vue matérialisée continue avec SQL, vous n'avez pas besoin de savoir comment les valeurs agrégées sont encodées, car SQL expose les résultats sous forme de colonnes typées.

Si vous lisez la vue à l'aide de ReadRows, vous devez décoder les données agrégées dans votre requête de lecture. Pour en savoir plus sur les requêtes ReadRows, consultez la section Lectures.

Les valeurs agrégées d'une vue matérialisée continue sont stockées à l'aide de l'encodage décrit dans le tableau suivant, en fonction du type de sortie de la colonne de la définition de la vue.

Type	Encodage
BOOL	Valeur de 1 octet, 1 = true, 0 = false
BYTES	Aucun encodage
INT64 (ou INT, SMALLINT, INTEGER, BIGINT, TINYINT, BYTEINT)	64 bits big-endian
FLOAT64	IEEE 754 64 bits, à l'exclusion de NaN et +/-inf
STRING	UTF-8
TIME/TIMESTAMP	Entier de 64 bits représentant le nombre de microsecondes depuis l'époque Unix (conforme à GoogleSQL)

Pour en savoir plus, consultez la section Encodage dans la documentation de référence de l'API Data.

Étape suivante

• Créer et gérer des vues matérialisées continues
• Documentation de référence de GoogleSQL pour Bigtable