Créer des vues matérialisées

Ce document explique comment créer des vues matérialisées dans BigQuery. Avant de lire ce document, familiarisez-vous avec la page Présentation des vues matérialisées.

Avant de commencer

Attribuez aux utilisateurs des rôles IAM (Identity and Access Management) incluant les autorisations nécessaires pour effectuer l'ensemble des tâches du présent document.

Autorisations requises

Pour créer des vues matérialisées, vous devez disposer de l'autorisation IAM bigquery.tables.create.

Chacun des rôles IAM prédéfinis suivants inclut les autorisations dont vous avez besoin pour créer une vue matérialisée :

  • bigquery.dataEditor
  • bigquery.dataOwner
  • bigquery.admin

Pour en savoir plus sur la gestion de l'authentification et des accès (IAM) dans BigQuery, consultez la page Contrôle des accès avec IAM.

Créer des vues matérialisées

Pour créer une vue matérialisée, sélectionnez l'une des options suivantes :

SQL

Utilisez l'instruction CREATE MATERIALIZED VIEW. L'exemple suivant crée une vue matérialisée pour le nombre de clics pour chaque ID de produit :

  1. Dans Google Cloud Console, accédez à la page BigQuery.

    Accéder à BigQuery

  2. Dans l'éditeur de requête, saisissez l'instruction suivante :

    CREATE MATERIALIZED VIEW PROJECT_ID.DATASET.MATERIALIZED_VIEW_NAME AS (
  QUERY_EXPRESSION
);

    Remplacez les éléments suivants :

    • PROJECT_ID : nom du projet dans lequel vous souhaitez créer la vue matérialisée, par exemple myproject.
    • DATASET : nom de l'ensemble de données BigQuery dans lequel vous souhaitez créer la vue matérialisée, par exemple mydataset. Si vous créez une vue matérialisée sur une table BigLake Amazon Simple Storage Service (Amazon S3) (preview), assurez-vous que l'ensemble de données se trouve dans une région compatible.
    • MATERIALIZED_VIEW_NAME : nom de la vue matérialisée que vous souhaitez créer, par exemple my_mv.
    • QUERY_EXPRESSION : expression de requête GoogleSQL qui définit la vue matérialisée, par exemple SELECT product_id, SUM(clicks) AS sum_clicks FROM mydataset.my_source_table.

  3. Cliquez sur Exécuter.

Pour en savoir plus sur l'exécution des requêtes, consultez Exécuter une requête interactive.

Exemple

L'exemple suivant crée une vue matérialisée pour le nombre de clics pour chaque ID de produit :

CREATE MATERIALIZED VIEW myproject.mydataset.my_mv_table AS (
  SELECT
    product_id,
    SUM(clicks) AS sum_clicks
  FROM
    myproject.mydataset.my_base_table
  GROUP BY
    product_id
);

Terraform

Utilisez la ressource google_bigquery_table.

Pour vous authentifier auprès de BigQuery, configurez le service Identifiants par défaut de l'application. Pour en savoir plus, consultez la page Configurer l'authentification pour les bibliothèques clientes.

L'exemple suivant crée une vue nommée my_materialized_view :

resource "google_bigquery_dataset" "default" {
  dataset_id                      = "mydataset"
  default_partition_expiration_ms = 2592000000  # 30 days
  default_table_expiration_ms     = 31536000000 # 365 days
  description                     = "dataset description"
  location                        = "US"
  max_time_travel_hours           = 96 # 4 days

  labels = {
    billing_group = "accounting",
    pii           = "sensitive"
  }
}

resource "google_bigquery_table" "default" {
  dataset_id          = google_bigquery_dataset.default.dataset_id
  table_id            = "my_materialized_view"
  deletion_protection = false # set to "true" in production

  materialized_view {
    query                            = "SELECT ID, description, date_created FROM `myproject.orders.items`"
    enable_refresh                   = "true"
    refresh_interval_ms              = 172800000 # 2 days
    allow_non_incremental_definition = "false"
  }

}

Pour appliquer votre configuration Terraform dans un projet Google Cloud, suivez les procédures des sections suivantes.

Préparer Cloud Shell

  1. Lancez Cloud Shell.

  2. Définissez le projet Google Cloud par défaut dans lequel vous souhaitez appliquer vos configurations Terraform.

    Vous n'avez besoin d'exécuter cette commande qu'une seule fois par projet et vous pouvez l'exécuter dans n'importe quel répertoire.

    export GOOGLE_CLOUD_PROJECT=PROJECT_ID

    Les variables d'environnement sont remplacées si vous définissez des valeurs explicites dans le fichier de configuration Terraform.

Préparer le répertoire

Chaque fichier de configuration Terraform doit avoir son propre répertoire (également appelé module racine).

  1. Dans Cloud Shell, créez un répertoire et un nouveau fichier dans ce répertoire. Le nom du fichier doit comporter l'extension .tf, par exemple main.tf. Dans ce tutoriel, le fichier est appelé main.tf. 
    mkdir DIRECTORY && cd DIRECTORY && touch main.tf

  2. Si vous suivez un tutoriel, vous pouvez copier l'exemple de code dans chaque section ou étape.

    Copiez l'exemple de code dans le fichier main.tf que vous venez de créer.

    Vous pouvez également copier le code depuis GitHub. Cela est recommandé lorsque l'extrait Terraform fait partie d'une solution de bout en bout.

  3. Examinez et modifiez les exemples de paramètres à appliquer à votre environnement.
  4. Enregistrez les modifications.
  5. Initialisez Terraform. Cette opération n'est à effectuer qu'une seule fois par répertoire. 
    terraform init

    Vous pouvez également utiliser la dernière version du fournisseur Google en incluant l'option -upgrade : 

    terraform init -upgrade

Appliquer les modifications

  1. Examinez la configuration et vérifiez que les ressources que Terraform va créer ou mettre à jour correspondent à vos attentes : 
    terraform plan

    Corrigez les modifications de la configuration si nécessaire.

  2. Appliquez la configuration Terraform en exécutant la commande suivante et en saisissant yes lorsque vous y êtes invité : 
    terraform apply

    Attendez que Terraform affiche le message "Apply completed!" (Application terminée).

  3. Ouvrez votre projet Google Cloud pour afficher les résultats. Dans la console Google Cloud, accédez à vos ressources dans l'interface utilisateur pour vous assurer que Terraform les a créées ou mises à jour.

API

Appelez la méthode tables.insert et transmettez une ressource Table avec un champ materializedView défini :

{
  "kind": "bigquery#table",
  "tableReference": {
    "projectId": "PROJECT_ID",
    "datasetId": "DATASET",
    "tableId": "MATERIALIZED_VIEW_NAME"
  },
  "materializedView": {
    "query": "QUERY_EXPRESSION"
  }
}

Remplacez les éléments suivants :

  • PROJECT_ID : nom du projet dans lequel vous souhaitez créer la vue matérialisée, par exemple myproject.
  • DATASET : nom de l'ensemble de données BigQuery dans lequel vous souhaitez créer la vue matérialisée, par exemple mydataset. Si vous créez une vue matérialisée sur une table BigLake Amazon Simple Storage Service (Amazon S3) (preview), assurez-vous que l'ensemble de données se trouve dans une région compatible.
  • MATERIALIZED_VIEW_NAME : nom de la vue matérialisée que vous souhaitez créer, par exemple my_mv.
  • QUERY_EXPRESSION : expression de requête GoogleSQL qui définit la vue matérialisée, par exemple SELECT product_id, SUM(clicks) AS sum_clicks FROM mydataset.my_source_table.

Exemple

L'exemple suivant crée une vue matérialisée pour le nombre de clics pour chaque ID de produit :

{
  "kind": "bigquery#table",
  "tableReference": {
    "projectId": "myproject",
    "datasetId": "mydataset",
    "tableId": "my_mv"
  },
  "materializedView": {
    "query": "select product_id,sum(clicks) as
                sum_clicks from myproject.mydataset.my_source_table
                group by 1"
  }
}

Java

Avant d'essayer cet exemple, suivez les instructions de configuration pour Java du guide de démarrage rapide de BigQuery : Utiliser les bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence de l'API BigQuery pour Java.

Pour vous authentifier auprès de BigQuery, configurez le service Identifiants par défaut de l'application. Pour en savoir plus, consultez la page Configurer l'authentification pour les bibliothèques clientes. 

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.MaterializedViewDefinition;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TableInfo;

// Sample to create materialized view
public class CreateMaterializedView {

  public static void main(String[] args) {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    String materializedViewName = "MY_MATERIALIZED_VIEW_NAME";
    String query =
        String.format(
            "SELECT MAX(TimestampField) AS TimestampField, StringField, "
                + "MAX(BooleanField) AS BooleanField "
                + "FROM %s.%s GROUP BY StringField",
            datasetName, tableName);
    createMaterializedView(datasetName, materializedViewName, query);
  }

  public static void createMaterializedView(
      String datasetName, String materializedViewName, String query) {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      TableId tableId = TableId.of(datasetName, materializedViewName);

      MaterializedViewDefinition materializedViewDefinition =
          MaterializedViewDefinition.newBuilder(query).build();

      bigquery.create(TableInfo.of(tableId, materializedViewDefinition));
      System.out.println("Materialized view created successfully");
    } catch (BigQueryException e) {
      System.out.println("Materialized view was not created. \n" + e.toString());
    }
  }
}

Une fois la vue matérialisée créée, elle apparaît dans le volet Explorer de BigQuery dans la console Google Cloud. L'exemple suivant illustre un schéma de vue matérialisée :

Schéma d'une vue matérialisée dans la console Google Cloud

À moins que vous ne désactiviez les actualisations automatiques, BigQuery lance une actualisation complète asynchrone pour la vue matérialisée. La requête se termine rapidement, mais l'actualisation initiale peut continuer à s'exécuter.

Contrôle des accès

Vous pouvez accorder l'accès à une vue matérialisée au niveau de l'ensemble de données, au niveau de la vue ou au niveau de la colonne. Vous pouvez également définir un niveau d'accès supérieur dans la hiérarchie des ressources IAM.

L'interrogation d'une vue matérialisée nécessite un accès à la vue ainsi qu'à ses tables de base. Pour partager une vue matérialisée, vous pouvez accorder des autorisations aux tables de base ou configurer une vue matérialisée en tant que vue autorisée. Pour en savoir plus, consultez la page Vues autorisées.

Pour contrôler l'accès aux vues dans BigQuery, consultez la page Vues autorisées.

Compatibilité avec les requêtes de vues matérialisées

Les vues matérialisées utilisent une syntaxe SQL limitée. Les requêtes doivent respecter le format suivant :

[ WITH cte [, …]]
SELECT  [{ ALL | DISTINCT }]
  expression [ [ AS ] alias ] [, ...]
FROM from_item [, ...]
[ WHERE bool_expression ]
[ GROUP BY expression [, ...] ]

from_item:
    {
      table_name [ as_alias ]
      | { join_operation | ( join_operation ) }
      | field_path
      | unnest_operator
      | cte_name [ as_alias ]
    }

as_alias:
    [ AS ] alias

Limites des requêtes

Les vues matérialisées présentent les limites suivantes.

Exigences globales

Les agrégats contenus dans la requête sur la vue matérialisée doivent être générés. Le calcul, le filtrage ou la jointure en fonction d'une valeur agrégée n'est pas accepté. Par exemple, il n'est pas possible de créer une vue à partir de la requête suivante, car elle génère une valeur calculée à partir d'une valeur agrégée (COUNT(*) / 10 as cnt).

SELECT TIMESTAMP_TRUNC(ts, HOUR) AS ts_hour, COUNT(*) / 10 AS cnt
FROM mydataset.mytable
GROUP BY ts_hour;

Seules les fonctions d'agrégation suivantes sont actuellement acceptées :

  • ANY_VALUE (mais pas à la place de STRUCT)
  • APPROX_COUNT_DISTINCT
  • ARRAY_AGG (mais pas à la place de ARRAY ni de STRUCT)
  • AVG
  • BIT_AND
  • BIT_OR
  • BIT_XOR
  • COUNT
  • COUNTIF
  • HLL_COUNT.INIT
  • LOGICAL_AND
  • LOGICAL_OR
  • MAX
  • MIN
  • MAX_BY (mais pas à la place de STRUCT)
  • MIN_BY (mais pas à la place de STRUCT)
  • SUM

Fonctionnalités SQL non compatibles

Les fonctionnalités SQL suivantes ne sont pas compatibles avec les vues matérialisées :

Disponibilité de LEFT OUTER JOIN et UNION ALL

Pour tout commentaire ou assistance pour cette fonctionnalité, envoyez un e-mail à l'adresse bq-mv-help@google.com.

Les vues matérialisées incrémentielles sont compatibles avec LEFT OUTER JOIN et UNION ALL. Les vues matérialisées avec des instructions LEFT OUTER JOIN et UNION ALL partagent les limites des autres vues matérialisées incrémentielles. En outre, le réglage intelligent n'est pas compatible avec les vues matérialisées comportant une jointure "union all" ou une jointure externe gauche.

Examples

L'exemple suivant crée une vue matérialisée incrémentielle agrégée avec un LEFT JOIN. Cette vue est mise à jour de manière incrémentielle lorsque des données sont ajoutées à la table de gauche.

CREATE MATERIALIZED VIEW dataset.mv
AS (
  SELECT
    s_store_sk,
    s_country,
    s_zip,
    SUM(ss_net_paid) AS sum_sales,
  FROM dataset.store_sales
  LEFT JOIN dataset.store
    ON ss_store_sk = s_store_sk
  GROUP BY 1, 2, 3
);

L'exemple suivant crée une vue matérialisée incrémentielle agrégée avec un UNION ALL. Cette vue est mise à jour de manière incrémentielle lorsque des données s'ajoutent à l'une des tables, ou aux deux. Pour en savoir plus sur les mises à jour incrémentielles, consultez la section Mises à jour incrémentielles.

CREATE MATERIALIZED VIEW dataset.mv PARTITION BY DATE(ts_hour)
AS (
  SELECT
    SELECT TIMESTAMP_TRUNC(ts, HOUR) AS ts_hour, SUM(sales) sum_sales
  FROM
    (SELECT ts, sales from dataset.table1 UNION ALL
     SELECT ts, sales from dataset.table2)
  GROUP BY 1
);

Restrictions de contrôle des accès

  • Si la requête d'un utilisateur sur une vue matérialisée inclut des colonnes de table de base auxquelles il ne peut pas accéder en raison de la sécurité au niveau des colonnes, la requête échoue avec le message Access Denied.
  • Si un utilisateur interroge une vue matérialisée mais ne dispose pas d'un accès complet à toutes les lignes des tables de base des vues matérialisées, BigQuery exécute la requête sur les tables de base au lieu de lire les données de la vue matérialisée. Cela garantit que la requête respecte toutes les contraintes de contrôle d'accès. Cette limitation s'applique également aux requêtes portant sur des tables contenant des colonnes masquées.

Clause WITH et expressions de table courantes (CTE)

Les vues matérialisées sont compatibles avec les clauses WITH et les expressions de table courantes. Les vues matérialisées comportant des clauses WITH doivent toujours respecter le modèle et les limites des vues matérialisées sans clauses WITH.

Examples

L'exemple suivant illustre une vue matérialisée à l'aide d'une clause WITH :

WITH tmp AS (
  SELECT TIMESTAMP_TRUNC(ts, HOUR) AS ts_hour, *
  FROM mydataset.mytable
)
SELECT ts_hour, COUNT(*) AS cnt
FROM tmp
GROUP BY ts_hour;

L'exemple suivant montre une vue matérialisée à l'aide d'une clause WITH qui n'est pas compatible, car elle contient deux clauses GROUP BY :

WITH tmp AS (
  SELECT city, COUNT(*) AS population
  FROM mydataset.mytable
  GROUP BY city
)
SELECT population, COUNT(*) AS cnt
GROUP BY population;

Vues matérialisées sur des tables BigLake

Pour créer des vues matérialisées sur des tables BigLake, la mise en cache des métadonnées doit être activée sur les données Cloud Storage et la vue matérialisée de la table BigLake doit utiliser une valeur supérieure à la table de base pour l'option max_staleness. Les vues matérialisées sur des tables BigLake acceptent le même ensemble de requêtes que les autres vues matérialisées.

Exemple

Pour créer une vue globale simple sur une table de base BigLake, procédez comme suit :

CREATE MATERIALIZED VIEW sample_dataset.sample_mv
    OPTIONS (max_staleness=INTERVAL "0:30:0" HOUR TO SECOND)
AS SELECT COUNT(*) cnt
FROM dataset.biglake_base_table;

Pour en savoir plus sur les limites des vues matérialisées sur des tables BigLake, consultez la page Vues matérialisées sur des tables BigLake.

Vues matérialisées sur des tables Apache Iceberg

Pour tout commentaire ou assistance pour cette fonctionnalité, envoyez un e-mail à l'adresse bq-mv-help@google.com.

Vous pouvez référencer de grandes tables Iceberg dans des vues matérialisées au lieu de migrer ces données vers un espace de stockage géré BigQuery.

Créer une vue matérialisée sur une table Iceberg

Pour créer une vue matérialisée sur une table Iceberg, procédez comme suit :

  1. Obtenez une table Iceberg à l'aide de l'une des méthodes suivantes :

    Exemple

    CREATE EXTERNAL TABLE mydataset.myicebergtable
  WITH CONNECTION `myproject.us.myconnection`
  OPTIONS (
        format = 'ICEBERG',
        uris = ["gs://mybucket/mydata/mytable/metadata/iceberg.metadata.json"]
  )

  2. Référencez votre table Iceberg avec les spécifications de partitionnement suivantes :

    "partition-specs" : [ {
   "spec-id" : 0,
   "fields" : [ {
    "name" : "birth_month",
    "transform" : "month",
    "source-id" : 3,
    "field-id" : 1000
} ]

  3. Créez une vue matérialisée alignée sur une partition :

    CREATE MATERIALIZED VIEW mydataset.myicebergmv
  PARTITION BY DATE_TRUNC(birth_month, MONTH)
AS
  SELECT * FROM mydataset.myicebergtable;

Limites

Outre les limites des tables Iceberg standards, les vues matérialisées sur des tables Iceberg présentent les limites suivantes :

  • Vous pouvez créer une vue matérialisée alignée sur les partitions de la table de base. Toutefois, la vue matérialisée n'est compatible qu'avec la transformation de partition basée sur le temps, par exemple YEAR, MONTH, DAY et HOUR.
  • La précision de la partition de la vue matérialisée ne peut pas être plus précise que celle de la partition de la table de base. Par exemple, si vous partitionnez la table de base chaque année à l'aide de la colonne birth_date, la création d'une vue matérialisée avec PARTITION BY DATE_TRUNC(birth_date, MONTH) ne fonctionne pas.
  • Toute modification de schéma invalide la vue matérialisée.
  • Les évolutions de partition sont acceptées. Toutefois, la modification des colonnes de partitionnement d'une table de base sans recréer la vue matérialisée peut entraîner une invalidation complète qui ne peut pas être corrigée par actualisation.
  • La table de base doit contenir au moins un instantané.
  • La table Iceberg doit être une table BigLake, par exemple une table externe autorisée.
  • Si VPC Service Controls est activé, les comptes de service de la table externe autorisée doivent être ajoutés à vos règles d'entrée. Sinon, VPC Service Controls bloque l'actualisation automatique en arrière-plan pour la table matérialisée.

Le fichier metadata.json de votre table Iceberg doit avoir les spécifications suivantes. Sans ces spécifications, vos requêtes analysent la table de base et ne parviennent pas à utiliser le résultat matérialisé.

  • Dans les métadonnées de table :

    • current-snapshot-id
    • current-schema-id
    • snapshots
    • snapshot-log

  • Dans les instantanés :

    • parent-snapshot-id (si disponible)
    • schema-id
    • operation (sur le terrain summary)

  • Partitionnement (pour la vue matérialisée partitionnée)

Vues matérialisées partitionnées

Les vues matérialisées sur des tables partitionnées peuvent être partitionnées. Le partitionnement d'une vue matérialisée est semblable à celui d'une table normale, car il offre des avantages lorsque les requêtes accèdent souvent à un sous-ensemble de partitions. En outre, le partitionnement d'une vue matérialisée peut améliorer le comportement de la vue lorsque des données sont modifiées ou supprimées dans la ou les tables de base. Pour en savoir plus, consultez la section Alignement des partitions.

Si la table de base est partitionnée, vous pouvez partitionner une vue matérialisée suivant la même colonne de partitionnement. Pour les partitions temporelles, la précision doit correspondre (horaire, quotidienne, mensuelle ou annuelle). Pour les partitions par plages d'entiers, la spécification de plage doit correspondre exactement. Vous ne pouvez pas partitionner une vue matérialisée sur une table de base non partitionnée.

Si la table de base est partitionnée par date d'ingestion, une vue matérialisée peut être regroupée en fonction de la colonne _PARTITIONDATE de la table de base et partitionnée par celle-ci. Si vous ne spécifiez pas explicitement le partitionnement lorsque vous créez la vue matérialisée, la vue matérialisée n'est pas partitionnée.

Si la table de base est partitionnée, envisagez également de partitionner votre vue matérialisée pour réduire le coût de maintenance de la tâche d'actualisation et le coût des requêtes.

Expiration de la partition

L'expiration de la partition ne peut pas être définie sur les vues matérialisées. Une vue matérialisée hérite implicitement de l'heure d'expiration de la partition de la table de base. Les partitions de la vue matérialisée sont alignées sur celles de la table de base. Elles expirent donc de manière synchrone.

Exemple 1

Dans cet exemple, la table de base est partitionnée suivant la colonne transaction_time avec des partitions quotidiennes. La vue matérialisée est partitionnée suivant la même colonne et mise en cluster sur la colonne employee_id.

CREATE TABLE my_project.my_dataset.my_base_table(
  employee_id INT64,
  transaction_time TIMESTAMP)
  PARTITION BY DATE(transaction_time)
  OPTIONS (partition_expiration_days = 2);

CREATE MATERIALIZED VIEW my_project.my_dataset.my_mv_table
  PARTITION BY DATE(transaction_time)
  CLUSTER BY employee_id
AS (
  SELECT
    employee_id,
    transaction_time,
    COUNT(employee_id) AS cnt
  FROM
    my_dataset.my_base_table
  GROUP BY
    employee_id, transaction_time
);

Exemple 2

Dans cet exemple, la table de base est partitionnée par date d'ingestion avec des partitions quotidiennes. La vue matérialisée sélectionne la date d'ingestion en tant que colonne nommée date. La vue matérialisée est regroupée par la colonne date et partitionnée par la même colonne.

CREATE MATERIALIZED VIEW my_project.my_dataset.my_mv_table
  PARTITION BY date
  CLUSTER BY employee_id
AS (
  SELECT
    employee_id,
    _PARTITIONDATE AS date,
    COUNT(1) AS count
  FROM
    my_dataset.my_base_table
  GROUP BY
    employee_id,
    date
);

Exemple 3

Dans cet exemple, la table de base est partitionnée suivant une colonne TIMESTAMP nommée transaction_time, avec des partitions quotidiennes. La vue matérialisée définit une colonne nommée transaction_hour à l'aide de la fonction TIMESTAMP_TRUNC pour tronquer la valeur à l'heure la plus proche. La vue matérialisée est regroupée par transaction_hour et partitionnée par celle-ci.

Veuillez noter les points suivants :

  • La fonction de troncature appliquée à la colonne de partitionnement doit être au moins aussi précise que le partitionnement de la table de base. Par exemple, si la table de base utilise des partitions quotidiennes, la fonction de troncature ne peut pas utiliser la précision MONTH ou YEAR.

  • Dans la spécification de partition de la vue matérialisée, la précision doit correspondre à celle de la table de base.

CREATE TABLE my_project.my_dataset.my_base_table (
  employee_id INT64,
  transaction_time TIMESTAMP)
  PARTITION BY DATE(transaction_time);

CREATE MATERIALIZED VIEW my_project.my_dataset.my_mv_table
  PARTITION BY DATE(transaction_hour)
AS (
  SELECT
    employee_id,
    TIMESTAMP_TRUNC(transaction_time, HOUR) AS transaction_hour,
    COUNT(employee_id) AS cnt
  FROM
    my_dataset.my_base_table
  GROUP BY
    employee_id,
    transaction_hour
);

Vues matérialisées en cluster

Vous pouvez mettre en cluster les vues matérialisées en fonction de leurs colonnes de sortie, en tenant compte des limites relatives aux tables en cluster de BigQuery. Les colonnes de résultat agrégé ne peuvent pas être utilisées comme colonnes de clustering. L'ajout de colonnes de clustering à des vues matérialisées peut améliorer les performances des requêtes qui incluent des filtres sur ces colonnes.

Vues logiques de référence

Pour tout commentaire ou assistance pour cette fonctionnalité, envoyez un e-mail à l'adresse bq-mv-help@google.com.

Les requêtes de vue matérialisée peuvent faire référence à des vues logiques, mais sont soumises aux limites suivantes :

Considérations liées à la création de vues matérialisées

Les vues matérialisées à créer

Lorsque vous créez une vue matérialisée, assurez-vous que votre définition de vue matérialisée reflète les modèles de requête par rapport aux tables de base. Étant donné que chaque table ne peut contenir que 20 vues matérialisées au maximum, nous vous déconseillons de créer une vue matérialisée pour chaque permutation d'une requête. Créez plutôt des vues matérialisées pour diffuser un ensemble plus large de requêtes.

Prenons l'exemple d'une requête sur une table dans laquelle les utilisateurs appliquent souvent des filtres sur les colonnes user_id ou department. Vous pouvez regrouper ces colonnes et éventuellement les mettre en cluster, au lieu d'ajouter des filtres tels que user_id = 123 dans la vue matérialisée.

Autre exemple : les utilisateurs se servent souvent de filtres de date, soit par date spécifique (WHERE order_date = CURRENT_DATE()), soit par plage de dates (WHERE order_date BETWEEN '2019-10-01' AND '2019-10-31'). Ajoutez un filtre de plage de dates dans la vue matérialisée qui couvre les plages de dates attendues dans la requête :

CREATE MATERIALIZED VIEW ...
  ...
  WHERE date > '2019-01-01'
  GROUP BY date

Jointures

Les recommandations suivantes s'appliquent aux vues matérialisées avec JOIN.

Placer la table la plus fréquemment modifiée en premier

Assurez-vous que la plus grande table ou la table la plus fréquemment modifiée est la première table la plus à gauche et/ou la plus référencée dans la requête de vue. Les vues matérialisées associées à des jointures sont compatibles avec les requêtes incrémentielles, et sont actualisées lorsque la première table ou la table la plus à gauche de la requête est ajoutée. Cependant, les modifications apportées aux autres tables invalident complètement le cache de vues. Dans les schémas en étoile ou en flocon de neige, la première table (ou la plus à gauche) doit généralement être la table de faits.

Éviter les jointures sur des clés de clustering

Les vues matérialisées avec jointures fonctionnent mieux dans les cas où les données sont fortement agrégées ou la requête de jointure d'origine est coûteuse. Pour les requêtes sélectives, BigQuery est souvent déjà en mesure d'effectuer la jointure de manière efficace, et aucune vue matérialisée n'est nécessaire. Prenons l'exemple des définitions de vues matérialisées suivantes.

CREATE MATERIALIZED VIEW dataset.mv
  CLUSTER BY s_market_id
AS (
  SELECT
    s_market_id,
    s_country,
    SUM(ss_net_paid) AS sum_sales,
    COUNT(*) AS cnt_sales
  FROM dataset.store_sales
  INNER JOIN dataset.store
    ON ss_store_sk = s_store_sk
  GROUP BY s_market_id, s_country
);

Si store_sales est mis en cluster sur ss_store_sk et que vous exécutez souvent des requêtes telles que les suivantes :

SELECT
  SUM(ss_net_paid)
FROM dataset.store_sales
INNER JOIN dataset.store
ON ss_store_sk = s_store_sk
WHERE s_country = 'Germany';

Alors la vue matérialisée peut ne pas être aussi efficace que la requête d'origine. Pour de meilleurs résultats, testez un ensemble représentatif de requêtes, avec et sans vue matérialisée.

Utiliser les vues matérialisées avec l'option max_staleness

L'option de vue matérialisée max_staleness vous permet d'atteindre systématiquement des performances élevées avec des coûts contrôlés lors du traitement de grands ensembles de données qui changent fréquemment. Avec le paramètre max_staleness, vous pouvez ajuster l'actualisation des résultats pour affiner les performances des requêtes. Ce comportement peut être utile pour les tableaux de bord et les rapports pour lesquels la fraîcheur des données n'est pas essentielle.

Obsolescence des données

Lorsque vous interrogez des vues matérialisées à l'aide de max_staleness, BigQuery renvoie des données cohérentes avec le résultat d'une requête de vue matérialisée exécutée dans l'intervalle max_staleness.

La requête est exécutée selon les conditions suivantes :

  • Si la dernière actualisation est comprise dans l'intervalle max_staleness, BigQuery renvoie les données directement à partir de la vue matérialisée sans lire les tables de base.

    Dernière actualisation dans l'intervalle d'obsolescence.

  • Si la dernière actualisation se situe en dehors de l'intervalle max_staleness, la requête lit les données des tables de base pour renvoyer les résultats dans l'intervalle d'obsolescence.

    Dernière actualisation en dehors de l'intervalle d'obsolescence.

Créer avec l'option max_staleness

Sélectionnez l'une des options suivantes :

SQL

Pour créer une vue matérialisée avec l'option max_staleness, ajoutez une clause OPTIONS à l'instruction LDD lors de la création de la vue matérialisée :

  1. Dans la console Google Cloud, accédez à la page BigQuery.

    Accéder à BigQuery

  2. Dans l'éditeur de requête, saisissez l'instruction suivante :

    CREATE MATERIALIZED VIEW  project-id.my_dataset.my_mv_table
  OPTIONS (enable_refresh = true, refresh_interval_minutes = 60,
    max_staleness = INTERVAL "4:0:0" HOUR TO SECOND)
AS SELECT
  employee_id,
  DATE(transaction_time),
  COUNT(1) AS count
FROM my_dataset.my_base_table
GROUP BY 1, 2;

    Remplacez les éléments suivants :

    • project-id est l'ID de votre projet.
    • my_dataset est l'ID d'un ensemble de données dans votre projet.
    • my_mv_table est l'ID de la vue matérialisée que vous créez.
    • my_base_table est l'ID d'une table de votre ensemble de données qui sert de table de base pour votre vue matérialisée.

    • Cliquez sur Exécuter.

Pour en savoir plus sur l'exécution des requêtes, consultez Exécuter une requête interactive.

API

Appelez la méthode tables.insert avec une ressource materializedView définie dans le cadre de votre requête API. La ressource materializedView contient un champ query. Exemple :

{
  "kind": "bigquery#table",
  "tableReference": {
    "projectId": "project-id",
    "datasetId": "my_dataset",
    "tableId": "my_mv_table"
  },
  "materializedView": {
    "query": "select product_id,sum(clicks) as
                sum_clicks from project-id.my_dataset.my_base_table
                group by 1"
  }
  "maxStaleness": "4:0:0"
}

Remplacez les éléments suivants :

  • project-id est l'ID de votre projet.
  • my_dataset est l'ID d'un ensemble de données dans votre projet.
  • my_mv_table est l'ID de la vue matérialisée que vous créez.
  • my_base_table est l'ID d'une table de votre ensemble de données qui sert de table de base pour votre vue matérialisée.
  • product_id est une colonne de la table de base.
  • clicks est une colonne de la table de base.
  • sum_clicks est une colonne de la vue matérialisée que vous créez.

Appliquer l'option max_staleness

Vous pouvez appliquer ce paramètre aux vues matérialisées existantes en utilisant l'instruction ALTER MATERIALIZED VIEW. Exemple :

ALTER MATERIALIZED VIEW project-id.my_dataset.my_mv_table
SET OPTIONS (enable_refresh = true, refresh_interval_minutes = 120,
  max_staleness = INTERVAL "8:0:0" HOUR TO SECOND);

Requête avec max_staleness

Vous pouvez interroger les vues matérialisées à l'aide de l'option max_staleness, comme vous le feriez pour toute autre vue matérialisée, vue logique ou table.

Exemple :

SELECT * FROM  project-id.my_dataset.my_mv_table

Cette requête renvoie les données de la dernière actualisation si elles ne sont pas antérieures au paramètre max_staleness. Si la vue matérialisée n'a pas été actualisée dans l'intervalle max_staleness, BigQuery fusionne les résultats de la dernière actualisation disponible avec les modifications de la table de base pour renvoyer les résultats dans l'intervalle max_staleness.

Flux de données et résultats pour max_staleness

Si vous insérez des données en flux continu dans les tables de base d'une vue matérialisée à l'aide de l'option max_staleness, la requête de vue matérialisée peut exclure les enregistrements qui ont été insérés en flux continu dans ses tables avant le début de l'intervalle d'obsolescence. Par conséquent, une vue matérialisée qui inclut les données de plusieurs tables et l'option max_staleness peut ne pas représenter un instantané à un moment précis de ces tables.

Réglage intelligent et option max_staleness

La fonctionnalité de réglage intelligent réécrit automatiquement les requêtes pour utiliser les vues matérialisées autant que possible, indépendamment de l'option max_staleness, même si la requête ne fait pas référence à une vue matérialisée. L'option max_staleness d'une vue matérialisée n'a pas d'incidence sur les résultats de la requête réécrite. L'option max_staleness n'affecte que les requêtes qui interrogent directement la vue matérialisée.

Gérer l'obsolescence et la fréquence d’actualisation

Vous devez définir max_staleness en fonction de vos besoins. Pour éviter de lire les données des tables de base, configurez l'intervalle d'actualisation afin que l'actualisation ait lieu dans l'intervalle d'obsolescence. Vous pouvez tenir compte de la durée d'exécution moyenne et d'une marge de croissance.

Par exemple, si vous avez besoin d'une heure pour actualiser votre vue matérialisée et que vous souhaitez un tampon d'une heure pour la croissance, vous devez définir l'intervalle d'actualisation sur deux heures. Cette configuration garantit que l'actualisation a lieu dans le délai d'obsolescence maximal de quatre heures de votre rapport.

CREATE MATERIALIZED VIEW project-id.my_dataset.my_mv_table
OPTIONS (enable_refresh = true, refresh_interval_minutes = 120, max_staleness =
INTERVAL "4:0:0" HOUR TO SECOND)
AS SELECT
  employee_id,
  DATE(transaction_time),
  COUNT(1) AS cnt
FROM my_dataset.my_base_table
GROUP BY 1, 2;

Vues matérialisées non incrémentielles

Les vues matérialisées non incrémentielles sont compatibles avec la plupart des requêtes SQL, y compris les clauses OUTER JOIN, UNION et HAVING, ainsi que les fonctions analytiques. Pour déterminer si une vue matérialisée a été utilisée dans votre requête, vérifiez les estimations de coûts à l'aide d'un dry run. Dans les cas où l'obsolescence des données est acceptable, par exemple pour le traitement ou la création de rapports par lot, les vues matérialisées non incrémentielles peuvent améliorer les performances des requêtes et réduire les coûts. À l'aide de l'option max_staleness, vous pouvez créer des vues matérialisées arbitraires et complexes qui sont automatiquement gérées et disposent de garanties d'obsolescence intégrées.

Utiliser des vues matérialisées non incrémentielles

Vous pouvez créer des vues matérialisées non incrémentielles à l'aide de l'option allow_non_incremental_definition. Cette option doit être accompagnée de l'option max_staleness. Pour garantir une actualisation périodique de la vue matérialisée, vous devez également configurer une règle d'actualisation. Sans règle d'actualisation, vous devez actualiser manuellement la vue matérialisée.

La vue matérialisée représente toujours l'état des tables de base dans l'intervalle max_staleness. Si la dernière actualisation est trop obsolète et ne représente pas les tables de base dans l'intervalle max_staleness, la requête lit les tables de base. Pour en savoir plus sur les conséquences possibles sur les performances, consultez la section Obsolescence des données.

Créer avec allow_non_incremental_definition

Pour créer une vue matérialisée avec l'option allow_non_incremental_definition, procédez comme suit : Une fois la vue matérialisée créée, vous ne pouvez plus modifier l'option allow_non_incremental_definition. Par exemple, vous ne pouvez pas remplacer la valeur true par false ni supprimer l'option allow_non_incremental_definition de la vue matérialisée.

SQL

Ajoutez une clause OPTIONS à l'instruction LDD lorsque vous créez la vue matérialisée :

  1. Dans la console Google Cloud, accédez à la page BigQuery.

    Accéder à BigQuery

  2. Dans l'éditeur de requête, saisissez l'instruction suivante :

    CREATE MATERIALIZED VIEW my_project.my_dataset.my_mv_table
OPTIONS (
  enable_refresh = true, refresh_interval_minutes = 60,
  max_staleness = INTERVAL "4" HOUR,
    allow_non_incremental_definition = true)
AS SELECT
  s_store_sk,
  SUM(ss_net_paid) AS sum_sales,
  APPROX_QUANTILES(ss_net_paid, 2)[safe_offset(1)] median
FROM my_project.my_dataset.store
LEFT OUTER JOIN my_project.my_dataset.store_sales
  ON ss_store_sk = s_store_sk
GROUP BY s_store_sk
HAVING median < 40 OR median is NULL ;

    Remplacez les éléments suivants :

    • my_project est l'ID de votre projet.
    • my_dataset est l'ID d'un ensemble de données dans votre projet.
    • my_mv_table est l'ID de la vue matérialisée que vous créez.
    • my_dataset.store et my_dataset.store_sales sont les ID des tables de votre ensemble de données qui servent de tables de base pour votre vue matérialisée.

  3. Cliquez sur Exécuter.

Pour en savoir plus sur l'exécution des requêtes, consultez Exécuter une requête interactive.

API

Appelez la méthode tables.insert avec une ressource materializedView définie dans le cadre de votre requête API. La ressource materializedView contient un champ query. Exemple :

{
  "kind": "bigquery#table",
  "tableReference": {
    "projectId": "my_project",
    "datasetId": "my_dataset",
    "tableId": "my_mv_table"
  },
  "materializedView": {
    "query": "`SELECT`
        s_store_sk,
        SUM(ss_net_paid) AS sum_sales,
        APPROX_QUANTILES(ss_net_paid, 2)[safe_offset(1)] median
      FROM my_project.my_dataset.store
      LEFT OUTER JOIN my_project.my_dataset.store_sales
        ON ss_store_sk = s_store_sk
      GROUP BY s_store_sk
      HAVING median < 40 OR median is NULL`",
    "allowNonIncrementalDefinition": true
  }
  "maxStaleness": "4:0:0"
}

Remplacez les éléments suivants :

  • my_project est l'ID de votre projet.
  • my_dataset est l'ID d'un ensemble de données dans votre projet.
  • my_mv_table est l'ID de la vue matérialisée que vous créez.
  • my_dataset.store et my_dataset.store_sales sont les ID des tables de votre ensemble de données qui servent de tables de base pour votre vue matérialisée.

Requête avec allow_non_incremental_definition

Vous pouvez interroger les vues matérialisées non incrémentielles comme vous le feriez pour toute autre vue matérialisée, vue logique ou table.

Exemple :

SELECT * FROM  my_project.my_dataset.my_mv_table

Si les données ne sont pas antérieures au paramètre max_staleness, cette requête renvoie les données de la dernière actualisation. Pour plus de détails sur l'obsolescence et l'actualisation des données, consultez la section Obsolescence des données.

Limites spécifiques aux vues matérialisées non incrémentielles

Les limites suivantes s'appliquent uniquement aux vues matérialisées avec l'option allow_non_incremental_definition. À l'exception des limites applicables à la syntaxe des requêtes acceptée, toutes les limites des vues matérialisées s'appliquent.

  • Les réglages intelligents ne sont pas appliqués aux vues matérialisées qui incluent l'option allow_non_incremental_definition. Le seul moyen de bénéficier des vues matérialisées avec l'option allow_non_incremental_definition consiste à les interroger directement.
  • Les vues matérialisées sans l'option allow_non_incremental_definition peuvent actualiser un sous-ensemble de leurs données de manière incrémentielle. Les vues matérialisées avec l'option allow_non_incremental_definition doivent être actualisées dans leur intégralité.
  • Les vues matérialisées avec l'option "max_staleness" valide la présence des contraintes de sécurité au niveau des colonnes lors de l'exécution de la requête. Pour en savoir plus, consultez Contrôle des accès au niveau des colonnes.

Étapes suivantes