Requêtes de 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 des concepts et des modèles pour vous aider à préparer votre requête SQL de 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 de 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 de vue matérialisée continue en tant qu'index secondaire global, 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 secondaires globaux, une clause ORDER BY, mais pas les deux.
• Doit utiliser uniquement les fonctions d'agrégation acceptées.
• Possibilité d'avoir plusieurs agrégations par groupe.

Agrégations acceptées

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 comme colonnes de sortie (utilisez TIMESTAMP ou stockez une chaîne).
• DESC dans le résultat
• 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 des clauses GROUP BY ou ORDER BY, ni créer de colonnes de carte. Pour connaître les autres limites, consultez Limites.

Éviter les lignes exclues

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

• Plus d'1 Mio de données est sélectionné 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 colonnes apple et banana 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.
• Les données générées sont plus de 10 fois supérieures à celles sélectionnées.
• La requête ne correspond pas à vos données. Cela inclut les tentatives 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 "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 Métriques.

Détails de la requête

Cette section décrit une requête de vue matérialisée continue et l'apparence des résultats 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 global.

SELECT * n'est pas compatible, contrairement à SELECT COUNT(*).

Comme dans une instruction SELECT typique, vous pouvez effectuer plusieurs agrégations par ensemble de données groupées. Les colonnes non regroupé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 un _key comme clé de ligne pour une vue matérialisée continue. Sinon, les colonnes de la clause GROUP BY forment la clé de 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. (Cette colonne est différente de la colonne _key que vous obtenez lorsque vous exécutez une requête SQL sur une table Bigtable.) Si vous spécifiez un _key, les règles suivantes s'appliquent :

• Vous devez regrouper les données par _key. Vous ne pouvez pas les regrouper par autre chose, sauf (facultativement) par _timestamp. Pour en savoir plus, consultez Horodatages.
• La colonne _key doit être de type BYTES.

Il est utile de spécifier un _key 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 envoyée à une vue avec un _key défini peut avoir besoin de décoder explicitement le _key au lieu de simplement renvoyer des colonnes de clés structurées.

Clés de ligne définies par la 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 clés les noms de votre choix, à condition qu'ils soient 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 global, 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 par b ASC, puis par c ASC.

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 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 qualificatif 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 :

• Le résultat de chaque baz se trouve sur une ligne distincte, dans l'ordre de baz ASC.
• Si un baz donné comporte au moins un foo, la valeur sum_foo de la ligne de sortie est non NULL.
• Si un baz donné comporte au moins un bar, le 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.

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

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, mais pas lorsque vous l'interrogez avec SQL.

Pour utiliser un autre code temporel dans le résultat, 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 le code temporel des autres cellules de la ligne.

Un code temporel 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 cellules 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 code temporel correspondant au jour tronqué.

Si vous interrogez la vue avec SELECT *, le résultat se présente comme suit :

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 des données à partir de la vue à l'aide de ReadRows, vous devez décoder les données agrégées dans votre demande de lecture. Pour en savoir plus sur les requêtes ReadRows, consultez 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.

Étapes suivantes

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