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 :
Dans Google Cloud Console, accédez à la page BigQuery.
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 exemplemyproject
.DATASET
: nom de l'ensemble de données BigQuery dans lequel vous souhaitez créer la vue matérialisée, par exemplemydataset
. 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 exemplemy_mv
.QUERY_EXPRESSION
: expression de requête GoogleSQL qui définit la vue matérialisée, par exempleSELECT product_id, SUM(clicks) AS sum_clicks FROM mydataset.my_source_table
.
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
:
Pour appliquer votre configuration Terraform dans un projet Google Cloud, suivez les procédures des sections suivantes.
Préparer Cloud Shell
- Lancez Cloud Shell.
-
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).
-
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 exemplemain.tf
. Dans ce tutoriel, le fichier est appelémain.tf
.mkdir DIRECTORY && cd DIRECTORY && touch main.tf
-
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.
- Examinez et modifiez les exemples de paramètres à appliquer à votre environnement.
- Enregistrez les modifications.
-
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
-
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.
-
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).
- 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 exemplemyproject
.DATASET
: nom de l'ensemble de données BigQuery dans lequel vous souhaitez créer la vue matérialisée, par exemplemydataset
. 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 exemplemy_mv
.QUERY_EXPRESSION
: expression de requête GoogleSQL qui définit la vue matérialisée, par exempleSELECT 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.
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 :
À 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 deSTRUCT
)APPROX_COUNT_DISTINCT
ARRAY_AGG
(mais pas à la place deARRAY
ni deSTRUCT
)AVG
BIT_AND
BIT_OR
BIT_XOR
COUNT
COUNTIF
HLL_COUNT.INIT
LOGICAL_AND
LOGICAL_OR
MAX
MIN
MAX_BY
(mais pas à la place deSTRUCT
)MIN_BY
(mais pas à la place deSTRUCT
)SUM
Fonctionnalités SQL non compatibles
Les fonctionnalités SQL suivantes ne sont pas compatibles avec les vues matérialisées :
UNION ALL
(Compatibilité avec la version preview de )LEFT OUTER JOIN
(Compatibilité avec la version preview de )RIGHT/FULL OUTER JOIN
- Autojointures, également appelées jointures
JOIN
sur la même table plusieurs fois - Fonctions de fenêtrage
- Sous-requêtes
ARRAY
- Fonctions non déterministes telles que
RAND()
,CURRENT_DATE()
,SESSION_USER()
ouCURRENT_TIME()
. - Fonctions définies par l'utilisateur
TABLESAMPLE
FOR SYSTEM_TIME AS OF
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 :
Obtenez une table Iceberg à l'aide de l'une des méthodes suivantes :
- Créez une table Iceberg avec le fichier de métadonnées JSON.
- Créez une table Iceberg à l'aide de BigLake Metastore.
- Découvrez-les dans les ensembles de données fédérés AWS Glue.
Exemple
CREATE EXTERNAL TABLE mydataset.myicebergtable WITH CONNECTION `myproject.us.myconnection` OPTIONS ( format = 'ICEBERG', uris = ["gs://mybucket/mydata/mytable/metadata/iceberg.metadata.json"] )
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 } ]
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
etHOUR
. - 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 avecPARTITION 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 terrainsummary
)
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
ouYEAR
.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 :
- Les limites des vues matérialisées s'appliquent.
- Si la vue logique change, la vue matérialisée n'est plus valide et doit être entièrement actualisée.
- Le réglage intelligent n'est pas pris en charge.
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.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.
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 :
Dans la console Google Cloud, accédez à la page BigQuery.
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 FROMmy_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 :
Dans la console Google Cloud, accédez à la page BigQuery.
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.
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'optionallow_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'optionallow_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.