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

Les requêtes SQL qui utilisent des fonctions non déterministes sont sémantiquement différentes des autres requêtes sur les vues matérialisées continues. Pour en savoir plus, consultez la section Requêtes non déterministes de ce document.

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 la plupart des autres fonctions non déterministes, à quelques exceptions près.
  • 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ées. 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 pas regrouper par autre chose, 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 simplement renvoyer 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.

Votre filtre SQL doit éliminer les valeurs NULL potentielles ou d'autres valeurs non valides pouvant entraîner des erreurs. Une ligne non valide, telle qu'une ligne 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;

Le résultat 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 colonne, il est omis des résultats.

Ensuite, si vous interrogez la vue avec SELECT *, le résultat se présente comme 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 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 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 se présente comme 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

Requêtes non déterministes

Les vues matérialisées continues sont compatibles avec les fonctions SQL non déterministes suivantes .

Fonction d'attribut de cluster

La fonction CLUSTER_ATTRIBUTE() vous permet d'accéder par programmation aux métadonnées du cluster Bigtable qui exécute la requête sur la vue matérialisée continue. L'attribut cluster_id renvoie l'identifiant de chaîne du cluster qui traite la vue.

Isoler une vue sur un seul cluster

Pour isoler le calcul et le stockage des vues matérialisées continues sur un seul cluster, tel qu'un cluster d'analyse ou de création de rapports dédié, comparez CLUSTER_ATTRIBUTE('cluster_id') à un littéral de chaîne constant contenant l' identifiant du cluster :

SELECT
  metrics['sensor_id'] AS sensor_id,
  COUNT(1) AS reading_count
FROM `TABLE_ID`
WHERE CLUSTER_ATTRIBUTE('cluster_id') = 'CLUSTER_ID'
GROUP BY 1

Remplacez les éléments suivants :

  • TABLE_ID: identifiant unique de la table Bigtable.
  • CLUSTER_ID: identifiant du cluster dans lequel vous souhaitez matérialiser la vue.

Cette requête se comporte comme suit dans votre instance :

  • Cluster désigné : sur le cluster correspondant à CLUSTER_ID, la condition WHERE est évaluée sur true. Bigtable agrège toutes les données de la table source, quel que soit le cluster qui les a ingérées ou répliquées, et matérialise ces données dans la copie de la vue de ce cluster.
  • Autres clusters : sur tous les autres clusters de l'instance, CLUSTER_ATTRIBUTE('cluster_id') est évalué sur leurs ID de cluster respectifs, ce qui rend la condition WHERE false. Ces clusters ne calculent ni ne stockent aucune ligne pour la vue matérialisée continue, ce qui permet d'économiser des ressources de calcul et de stockage.

Filtrer les données par origine du cluster

Si votre application écrit des métadonnées de cluster dans les lignes de la table source (par exemple, dans une colonne nommée origin_cluster), vous pouvez faire correspondre le cluster d'origine de la ligne au cluster de traitement :

SELECT
  metrics['sensor_id'] AS sensor_id,
  COUNT(1) AS reading_count
FROM `TABLE_ID`
WHERE metrics['origin_cluster'] = CLUSTER_ATTRIBUTE('cluster_id')
GROUP BY 1

Dans cette requête, metrics['origin_cluster'] est une propriété des données stockées dans la ligne, tandis que CLUSTER_ATTRIBUTE('cluster_id') est une propriété du cluster traitant la vue. La réplique de chaque cluster de la vue matérialisée continue n'agrège que les lignes où le origin_cluster stocké correspond à l'ID de ce cluster.

Si certaines lignes ne comportent pas de cluster d'origine spécifié, vous pouvez utiliser IF_NULL pour attribuer un cluster de secours :

SELECT
  metrics['sensor_id'] AS sensor_id,
  COUNT(1) AS reading_count
FROM `TABLE_ID`
WHERE IF_NULL(metrics['origin_cluster'], 'DEFAULT_CLUSTER_ID') = CLUSTER_ATTRIBUTE('cluster_id')
GROUP BY 1

Caractéristiques de la requête

Les requêtes utilisant CLUSTER_ATTRIBUTE présentent les caractéristiques suivantes :

  • Étant donné que sensor_id se trouve dans la clause GROUP BY, il fait partie de la clé de ligne dans la vue matérialisée continue.
  • Pour utiliser cette fonction non déterministe, vous devez définir l'indicateur ignore_warnings sur true lorsque vous créez la vue. Pour en savoir plus, consultez la section Créer une vue matérialisée continue.

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

sensor_id reading_count
sensor-1234 25
sensor-5678 68

Limites

L'utilisation de la fonction non déterministe CLUSTER_ATTRIBUTE introduit des exigences de routage strictes :

  • Le routage multicluster n'est pas compatible : étant donné que la sortie de la fonction dépend du cluster physique qui exécute la requête, le routage multicluster produirait des résultats non déterministes.
  • Utilisez le routage à cluster unique : lorsque vous interrogez une vue matérialisée continue qui utilise CLUSTER_ATTRIBUTE, utilisez un profil d'application configuré pour le routage à cluster unique.
  • Acheminez vers le cluster désigné : si vous isolez une vue sur un cluster spécifique, tel que WHERE CLUSTER_ATTRIBUTE('cluster_id') = 'my-reporting-cluster', pointez le profil d'application directement vers ce cluster. L'interrogation d'un autre cluster renvoie des résultats vides, car la vue n'est pas remplie sur les autres clusters.

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) Big-endian 64 bits
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