CREATE MATERIALIZED VIEW

Crée une vue matérialisée basée sur une ou plusieurs tables Amazon Redshift. Vous pouvez également baser les vues matérialisées sur des tables externes créées à l’aide de Spectrum ou d’une requête fédérée. Pour obtenir des informations sur Spectrum, consultez Amazon Redshift Spectrum. Pour obtenir des informations sur la requête fédérée, consultez Interrogation de données avec requête fédérée dans Amazon Redshift.

Syntaxe

CREATE MATERIALIZED VIEW mv_name
[ BACKUP { YES | NO } ]
[ table_attributes ]
[ AUTO REFRESH { YES | NO } ]
AS query 

La syntaxe suivante crée une vue matérialisée qui stocke ses données sous forme de table Apache Iceberg. Pour plus d'informations, consultez la section Vues matérialisées stockées sous forme de tables Apache Iceberg.

CREATE MATERIALIZED VIEW mv_name
USING ICEBERG
[LOCATION 's3://bucket/path/']
[PARTITIONED BY (partition_transform [, ...])]
[TABLE PROPERTIES ('property_name' = 'property_value' [, ...])]
AS query

où partition_transform est :

    column_name |
    YEAR( column_name ) |
    MONTH( column_name ) |
    DAY( column_name ) |
    HOUR( column_name ) |
    BUCKET( integer, column_name ) |
    TRUNCATE( integer, column_name )

Parameters

BACKUP

Clause qui spécifie si la vue matérialisée doit être incluse dans les instantanés de cluster automatiques et manuels.

Pour les vues matérialisées qui ne contiennent pas de données critiques, spécifiez BACKUP NO pour économiser du temps de traitement lorsque la création d’instantanés et la restauration à partir d’instantanés, et pour réduire l’espace de stockage sur Amazon Simple Storage Service. Comme le paramètre BACKUP NO n’a aucun effet sur la réplication automatique des données sur d’autres nœuds au sein du cluster, les vues matérialisées pour lesquelles BACKUP NO est spécifié sont restaurées en cas de défaillance de nœud. La valeur par défaut est BACKUP YES.

table_attributes

Clause qui spécifie la manière dont les données de la vue matérialisée sont distribuées, notamment :

• Style de distribution de la vue matérialisée, au format DISTSTYLE { EVEN | ALL | KEY }. Si vous omettez cette clause, le style de distribution est EVEN. Pour plus d'informations, consultez Styles de distribution.

• Clé de distribution de la vue matérialisée, au format DISTKEY ( distkey_identifier ). Pour plus d'informations, consultez Détermination des styles de distribution.

• Clé de tri de la vue matérialisée, au format SORTKEY ( column_name [, ...] ). Pour plus d'informations, consultez Clés de tri.

AS requête

Une instruction SELECT valide qui définit la vue matérialisée et son contenu. L’ensemble de résultats de la requête définit les colonnes et les lignes de la vue matérialisée. Pour obtenir des informations sur les limitations lors de la création de vues matérialisées, consultez Limitations.

En outre, des constructions de langage SQL spécifiques utilisées dans la requête déterminent si la vue matérialisée peut être reproduite de manière incrémentielle ou complète. Pour obtenir des informations sur la méthode d’actualisation, consultez REFRESH MATERIALIZED VIEW. Pour obtenir des informations sur les limitations de l’actualisation progressive, consultez Limites d’actualisation incrémentielle.

Si la requête contient une commande SQL qui ne prend pas en charge l’actualisation incrémentielle, Amazon Redshift affiche un message indiquant que la vue matérialisée utilisera une actualisation complète. Le message peut ou non s’afficher, selon l’application cliente SQL. Cochez la colonne state de STV_MV_INFO pour voir le type d’actualisation utilisé par une vue matérialisée.

AUTO REFRESH

Clause qui définit si la vue matérialisée doit être automatiquement actualisée avec les dernières modifications de ses tables de base ou non. La valeur par défaut est NO. Pour plus d’informations, consultez Actualisation d’une vue matérialisée.

EN UTILISANT ICEBERG

Spécifie que la vue matérialisée stocke ses données sous forme de table Apache Iceberg dans Amazon S3. Lorsque vous spécifiez USING ICEBERG, les données de la vue matérialisée sont écrites sous forme de fichiers Parquet au format Iceberg et enregistrées dans le. AWS Glue Data Catalog Pour plus d'informations, consultez la section Vues matérialisées stockées sous forme de tables Apache Iceberg.

mv_name

Lorsque vous utilisez USING ICEBERG, le nom de la vue matérialisée doit utiliser une notation qualifiée par catalogue. Vous pouvez utiliser la notation en trois parties (awsdatacatalog.database.mv_name), la notation Amazon S3 Tables ("bucket@s3tablescatalog".database.mv_name) ou la notation en deux parties avec un schéma externe ()external_schema.mv_name. La base de données cible doit exister dans le AWS Glue Data Catalog.

LOCATION

Le chemin Amazon S3 où les données et les fichiers de métadonnées Iceberg sont stockés. Le chemin doit pointer vers un emplacement vide. Le compartiment Amazon S3 doit se trouver dans le même emplacement Région AWS que le cluster ou le groupe de travail Amazon Redshift. Omettez ce paramètre lorsque vous utilisez des compartiments de table Amazon S3, car Amazon S3 Tables gère automatiquement l'emplacement de stockage. Pour les tables créées à l'aide de schémas externes ou du catalogue awsdatacatalog racine, LOCATION est obligatoire.

PARTITIONNÉ PAR (partition_transform [,...])

Spécifie une ou plusieurs transformations de partition Iceberg pour la vue matérialisée. Amazon Redshift prend en charge toutes les transformations de partitions Iceberg v2, à l'exception de. void Une seule colonne ne peut pas apparaître dans plusieurs transformations.

PROPRIÉTÉS DE LA TABLE ('property_name' = 'property_value' [,...])

Spécifie les propriétés de la table Iceberg. Par exemple, utilisez-le 'write.parquet.compression-codec' = 'zstd' pour définir le codec de compression Parquet.

Notes d’utilisation

Pour créer une vue matérialisée, vous devez disposer des privilèges suivants :

• Privilèges CREATE pour un schéma.

• Table-level ou le privilège SELECT au niveau des colonnes sur les tables de base pour créer une vue matérialisée. Si vous disposez de privilèges au niveau de colonnes spécifiques, vous pouvez créer une vue matérialisée uniquement avec ces colonnes.

Vous pouvez créer une vue matérialisée à partir d'un cluster de partage de données distant en fournissant le nom de la base de données externe dans le. mv_name

Vues matérialisées par des icebergs

Pour créer une vue matérialisée Iceberg, vous devez disposer de l'autorisation CREATE TABLE dans la base de données cible AWS Glue Data Catalog . Le rôle IAM associé au schéma externe (le rôle de définition MV) doit disposer de l'autorisation SELECT sur toutes les tables sources référencées dans la requête.

Tous les identifiants de la définition de la vue matérialisée (noms de table, noms de colonnes, alias) doivent être en minuscules. AWS Glue Data Catalog Stocke les identifiants en minuscules pour garantir la compatibilité avec Hive. Amazon Redshift rejette les instructions CREATE MATERIALIZED VIEW contenant des identifiants en majuscules avec USING ICEBERG.

La création ou l'actualisation de vues matérialisées d'Iceberg n'est pas prise en charge lorsque ce paramètre enable_case_sensitive_identifier est défini sur. true Cette restriction existe parce qu'elle AWS Glue Data Catalog ne prend pas en charge les identificateurs distinguant majuscules et minuscules. Si votre cluster ou groupe de travail est enable_case_sensitive_identifier activé, définissez-le sur false pour la session avant de créer ou d'actualiser les vues matérialisées d'Iceberg :

SET enable_case_sensitive_identifier TO false;

Les tables sources doivent être au format Apache Iceberg version 2 ou inférieure. Non-Iceberg les tables ne peuvent pas être utilisées comme tables sources pour les vues matérialisées d'Iceberg.

Vous ne pouvez pas utiliser les éléments suivants avec les vues matérialisées d'Iceberg :

• Clause BACKUP

• Clauses DISTSTYLE, DISTKEY ou SORTKEY

• Références aux tables natives, aux tables temporaires ou aux tables système d'Amazon Redshift

• ACTUALISATION AUTOMATIQUE (l'actualisation automatique n'est pas prise en charge ; actualisez les vues matérialisées d'Iceberg manuellement)

Pour plus d'informations sur les capacités et les limites des vues matérialisées d'Iceberg, voir Vues matérialisées stockées sous forme de tables Apache Iceberg.

Actualisation incrémentielle des vues matérialisées dans une unité de partage des données

Amazon Redshift prend en charge l’actualisation automatique et incrémentielle des vues matérialisées dans une unité de partage des données consommateur lorsque les tables de base sont partagées. L’actualisation incrémentielle est une opération au cours de laquelle Amazon Redshift identifie les modifications apportées à la ou les tables de base après l’actualisation précédente et met à jour uniquement les enregistrements correspondants dans la vue matérialisée. Cela s’exécute plus rapidement qu’une actualisation complète et améliore les performances de la charge de travail. Il n’est pas nécessaire de modifier la définition de votre vue matérialisée pour tirer parti de l’actualisation incrémentielle.

Il existe quelques limites à prendre en compte pour tirer parti de l’actualisation incrémentielle avec une vue matérialisée :

• La vue matérialisée ne doit référencer qu’une seule base de données, locale ou distante.

• L’actualisation incrémentielle n’est disponible que sur les nouvelles vues matérialisées. Par conséquent, vous devez supprimer les vues matérialisées existantes et les recréer pour qu’une actualisation incrémentielle ait lieu.

Pour plus d’informations sur la création de vues matérialisées dans une unité de partage des données, consultez Utiliser les vues dans le partage de données Amazon Redshift, qui contient plusieurs exemples de requêtes.

Mises à jour DDL des vues matérialisées ou des tables de base

Lorsque vous utilisez des vues matérialisées dans Amazon Redshift, suivez ces notes d’utilisation pour les mises à jour du langage de définition de données (DDL) vers des vues matérialisées ou des tables de base.

• Vous pouvez ajouter des colonnes à une table de base sans affecter les vues matérialisées qui référencent cette table.

• Certaines opérations peuvent laisser la vue matérialisée dans un état qui ne peut pas du tout être actualisé. Par exemple, des opérations telles que le renommage ou la suppression d’une colonne, le modification d’un type de colonne et le changement de nom d’un schéma. Ce type de vue matérialisée peut être interrogé mais pas actualisé. Dans ce cas, vous devez annuler et recréer la vue matérialisée.

• En général, vous ne pouvez pas modifier la définition d’une vue matérialisée (son instruction SQL).

• Vous ne pouvez pas renommer une vue matérialisée.

Limitations

Vous ne pouvez pas définir de vue matérialisée qui référence ou comprend l’un des éléments suivants :

• Vues standard ou tables et vues système.

• Tables temporaires.

• User-defined fonctions.

• La clause ORDER BY, LIMIT ou OFFFET.

• Late-binding références aux tables de base. En d’autres termes, toute table de base ou colonne associée référencée dans la définition de la requête SQL de la vue matérialisée doit exister et être valide.

• Fonctions de nœud leader uniquement : CURRENT_SCHEMA, CURRENT_SCHEMAS, HAS_DATABASE_PRIVILEGE, HAS_SCHEMA_PRIVILEGE, HAS_TABLE_PRIVILEGE.

• Vous ne pouvez pas utiliser l’option AUTO REFRESH YES lorsque la définition de la vue matérialisée inclut des fonctions mutables ou des schémas externes. Vous ne pouvez pas non plus l’utiliser lorsque vous définissez une vue matérialisée sur une autre vue matérialisée.

• Vous n’avez pas besoin d’exécuter manuellement ANALYSE sur les vues matérialisées. Cela se produit actuellement uniquement en utilisant AUTO ANALYZE. Pour plus d’informations, consultez Analyse des tables.

• RLS-protected ou DDM-protected des tables.

• La création de vues matérialisées à partir de clusters de partage de données distants ne prend pas en charge les références sur d'autres vues matérialisées, les tables Spectrum, les tables définies dans un autre cluster Redshift et les UDF. Ils sont pris en charge pour la création de vues matérialisées à partir du cluster local (producteur).

Pour les vues matérialisées d'Iceberg créées à l'aide d'ICEBERG, les restrictions supplémentaires suivantes s'appliquent :

• Les tables sources doivent être au format Apache Iceberg.

• Tous les identifiants doivent être en minuscules.

• User-defined les fonctions et les fonctions mutables ne sont pas autorisées.

• Les tables sources doivent se trouver dans le même format Région AWS et être prises en compte que la vue matérialisée.

• AWS Lake Formation les tables filtrées (FGAC) ne peuvent pas être utilisées comme tables sources.

Exemples

L’exemple suivant montre comment créer une vue matérialisée à partir de trois tables de base qui sont jointes et agrégées. Chaque ligne représente une catégorie avec le nombre de billets vendus. Lorsque vous interrogez la vue matérialisée tickets_mv, vous accédez directement aux données précalculées dans la vue matérialisée tickets_mv.

CREATE MATERIALIZED VIEW tickets_mv AS
    select   catgroup,
    sum(qtysold) as sold
    from     category c, event e, sales s
    where    c.catid = e.catid
    and      e.eventid = s.eventid
    group by catgroup;

L’exemple suivant crée une vue matérialisée similaire à l’exemple précédent et utilise la fonction d’agrégation MAX().

CREATE MATERIALIZED VIEW tickets_mv_max AS
    select   catgroup,
    max(qtysold) as sold
    from     category c, event e, sales s
    where    c.catid = e.catid
    and      e.eventid = s.eventid
    group by catgroup;

SELECT name, state FROM STV_MV_INFO;

L’exemple suivant utilise une clause UNION ALL pour joindre la table public_sales Amazon Redshift et la table spectrum.sales Redshift Spectrum pour créer une vue matérialisée mv_sales_vw. Pour obtenir des informations sur la commande CREATE EXTERNAL TABLE pour Amazon Redshift Spectrum, consultez CREATE EXTERNAL TABLE. La table externe Redshift Spectrum fait référence aux données sur Amazon S3.

CREATE MATERIALIZED VIEW mv_sales_vw as
select salesid, qtysold, pricepaid, commission, saletime from public.sales
union all
select salesid, qtysold, pricepaid, commission, saletime from spectrum.sales

L’exemple suivant crée une vue matérialisée mv_fq basée sur une table externe de requête fédérée. Pour obtenir des informations sur la requête fédérée, consultez CREATE EXTERNAL SCHEMA.

CREATE MATERIALIZED VIEW mv_fq as select firstname, lastname from apg.mv_fq_example;

select firstname, lastname from mv_fq;
 firstname | lastname
-----------+----------
 John      | Day
 Jane      | Doe
(2 rows)

L’exemple suivant montre la définition d’une vue matérialisée.

SELECT pg_catalog.pg_get_viewdef('mv_sales_vw'::regclass::oid, true);

pg_get_viewdef
---------------------------------------------------
create materialized view mv_sales_vw as select a from t;

L’exemple suivant montre comment définir AUTO REFRESH dans la définition de la vue matérialisée et indique également un DISTSTYLE. Commencez par créer une table de base simple.

CREATE TABLE baseball_table (ball int, bat int);

Créez ensuite une vue matérialisée.

CREATE MATERIALIZED VIEW mv_baseball DISTSTYLE ALL AUTO REFRESH YES AS SELECT ball AS baseball FROM baseball_table;

Vous pouvez maintenant interroger la vue matérialisée mv_baseball. Pour vérifier si AUTO REFRESH est activé pour une vue matérialisée, consultez STV_MV_INFO.

L’exemple suivant crée une vue matérialisée qui fait référence à une table source dans une autre base de données. Cela suppose que la base de données contenant la table source, database_A, se trouve dans le même cluster ou groupe de travail que votre vue matérialisée, que vous créez dans database_B. (Vous pouvez remplacer vos propres bases de données par l’exemple.) Tout d’abord, créez une table dans database_A appelée cities pour les villes, avec une colonne cityname pour les noms de villes. Définissez le type de données de la colonne comme VARCHAR. Après avoir créé la table source, exécutez la commande suivante dans database_B pour créer une vue matérialisée dont la source est votre table cities. Assurez-vous de spécifier la base de données et le schéma de la table source dans la clause FROM :

CREATE MATERIALIZED VIEW cities_mv AS
SELECT  cityname
FROM    database_A.public.cities;

Interrogez la vue matérialisée que vous avez créée. La requête extrait les enregistrements dont la source d’origine est la table cities dans database_A :

select * from cities_mv;

Lorsque vous exécutez l’instruction SELECT, cities_mv renvoie les enregistrements. Les enregistrements sont actualisés à partir de la table source uniquement lorsqu’une instruction REFRESH est exécutée. Notez également que vous ne pouvez pas mettre à jour les enregistrements directement dans la vue matérialisée. Pour plus d’informations sur l’actualisation des données dans une vue matérialisée, consultez REFRESH MATERIALIZED VIEW.

Pour en savoir plus sur la présentation des vues matérialisées et les commandes SQL utilisées pour actualiser et supprimer les vues matérialisées, consultez les rubriques suivantes :

• Vues matérialisées dans Amazon Redshift

• REFRESH MATERIALIZED VIEW

• DROP MATERIALIZED VIEW

L'exemple suivant crée une vue matérialisée Iceberg dans un compartiment de table Amazon S3 avec partitionnement.

CREATE MATERIALIZED VIEW "my-bucket@s3tablescatalog".analytics.sales_by_region
USING ICEBERG
PARTITIONED BY (region)
AS SELECT region, count(*) as order_count, sum(amount) as total_sales
   FROM "my-bucket@s3tablescatalog".production.orders
   GROUP BY region;

L'exemple suivant crée une vue matérialisée Iceberg dans un compartiment Amazon S3 à usage général avec une transformation et une compression de partition spécifiées.

CREATE MATERIALIZED VIEW awsdatacatalog.mydb.daily_revenue
USING ICEBERG
LOCATION 's3://my-analytics-bucket/mvs/daily_revenue/'
PARTITIONED BY (day(order_date))
TABLE PROPERTIES ('write.parquet.compression-codec' = 'zstd')
AS SELECT order_date, sum(amount) as revenue, count(*) as num_orders
   FROM awsdatacatalog.mydb.orders
   GROUP BY order_date;

L'exemple suivant crée une vue matérialisée d'Iceberg à l'aide d'un schéma externe avec une notation en deux parties.

CREATE MATERIALIZED VIEW my_ext_schema.customer_totals
USING ICEBERG
LOCATION 's3://my-analytics-bucket/mvs/customer_totals/'
AS SELECT customer_id, count(*) as order_count, sum(amount) as total_spent
   FROM my_ext_schema.orders
   GROUP BY customer_id;