CREATE MATERIALIZED VIEW

Cria uma visão materializada com base em uma ou mais tabelas do Amazon Redshift. Você também pode basear visões materializadas em tabelas externas criadas usando Spectrum ou consulta federada. Para obter informações sobre o Spectrum, consulte Amazon Redshift Spectrum. Para obter informações sobre consulta federada, consulte Consultar dados com consultas federadas no Amazon Redshift.

Sintaxe

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

A sintaxe a seguir cria uma visualização materializada que armazena seus dados como uma tabela do Apache Iceberg. Para acessar mais informações, consulte Visualizações materializadas armazenadas como tabelas do 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

em que partition_transform é:

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

Parâmetros

BACKUP

Uma cláusula que especifica se uma visão materializada deve ser incluída em snapshots de cluster manuais ou automáticos.

Para visões materializadas que não contêm dados críticos, especifique BACKUP NO para economizar tempo de processamento ao criar snapshosts, restaurar de snapshots e reduzir o uso de espaço de armazenamento no Amazon Simple Storage Service. A configuração BACKUP NO não tem efeito na replicação automática de dados para outros nós no cluster. Portanto, visões materializadas com BACKUP NO especificado são restauradas em caso de falha de nó. O padrão é BACKUP YES.

table_attributes

Uma cláusula que especifica como os dados na visão materializada são distribuídos, incluindo o seguinte:

• O estilo de distribuição da visão materializada, no formato DISTSTYLE { EVEN | ALL | KEY }. Se você omitir essa cláusula, o estilo da distribuição será EVEN. Para obter mais informações, consulte Estilos de distribuição.

• O código de distribuição da visão materializada, no formato DISTKEY ( distkey_identifier ). Para obter mais informações, consulte Designação de estilos de distribuição.

• A chave de classificação para a visão materializada, no formato SORTKEY ( column_name [, ...] ). Para obter mais informações, consulte Chaves de classificação.

AS query

Uma instrução SELECT válida que define a visão materializada e seu conteúdo. O conjunto de resultados da consulta define as colunas e as linhas da visão materializada. Para obter informações sobre limitações ao criar visões materializadas, consulte Limitações.

Além disso, os constructos de linguagem SQL específicos usados na consulta determinam se a visão materializada pode ser atualizada de maneira incremental ou total. Para obter informações sobre o método de atualização, consulte REFRESH MATERIALIZED VIEW. Para obter informações sobre as limitações da atualização incremental, consulte Limitações para atualização incremental.

Se a consulta contiver um comando SQL incompatível com a atualização incremental, o Amazon Redshift exibirá uma mensagem indicando que a visão materializada usará uma atualização total. A mensagem poderá ou não ser exibida dependendo da aplicação cliente SQL. Verifique a coluna state do STV_MV_INFO para ver o tipo de atualização usado por uma visão materializada.

AUTO REFRESH

Uma cláusula que define se a visão materializada deve ser atualizada automaticamente com as alterações mais recentes de suas tabelas base. O valor padrão é NO. Para obter mais informações, consulte Atualizar uma visualização materializada.

USING ICEBERG

Especifica que a visualização materializada armazene seus dados como uma tabela do Apache Iceberg no Amazon S3. Quando você especifica USING ICEBERG, os dados da visualização materializada são gravados como arquivos Parquet no formato Iceberg e registrados no AWS Glue Data Catalog. Para acessar mais informações, consulte Visualizações materializadas armazenadas como tabelas do Apache Iceberg.

mv_name

Quando você usa USING ICEBERG, o nome da visualização materializada deve usar a notação qualificada pelo catálogo. Você pode usar a notação de três partes (awsdatacatalog.database.mv_name), a notação de Tabelas do Amazon S3 ("bucket@s3tablescatalog".database.mv_name) ou a notação de duas partes com um esquema externo (external_schema.mv_name). O banco de dados de destino não deve existir no AWS Glue Data Catalog.

LOCALIZAÇÃO

O caminho do Amazon S3 no qual os arquivos de dados e metadados do Iceberg são armazenados. O caminho deve apontar para um local vazio. O bucket da Amazon S3 deve estar na mesma região da Região da AWS que o cluster ou grupo de trabalho do Amazon Redshift. Omita esse parâmetro ao usar o Buckets de Tabelas do Amazon S3, porque as Tabelas do Amazon S3 gerenciam o local de armazenamento automaticamente. Para tabelas criadas usando esquemas externos ou o catálogo raiz awsdatacatalog, LOCATION é obrigatório.

PARTITIONED BY (partition_transform [, ...])

Especifica uma ou mais transformações de partição do Iceberg para a visualização materializada. O Amazon Redshift comporta todas as transformações de partição do Iceberg v2, exceto void. Uma única coluna não pode aparecer em várias transformações.

TABLE PROPERTIES ('property_name' = 'property_value' [, ...])

Especifica as propriedades da tabela do Iceberg. Por exemplo, use 'write.parquet.compression-codec' = 'zstd' para definir o codec de compactação Parquet.

Observações de uso

Para criar uma visão materializada, você deve ter os seguintes privilégios:

• Privilégios CREATE para um esquema.

• Privilégio SELECT por tabela ou coluna para as tabelas de base a fim de criar uma visão materializada. Se você tiver privilégios por coluna para colunas específicas, poderá criar uma visão materializada somente para essas colunas.

É possível criar uma visão materializada em um cluster remoto de unidade de compartilhamento de dados fornecendo o nome do banco de dados externo no mv_name.

Visualizações materializadas do Iceberg

Para criar uma visualização materializada do Iceberg, você deve ter a permissão CREATE TABLE no banco de dados AWS Glue Data Catalog de destino. O perfil do IAM associado ao esquema externo (o perfil definidor de MV) deve ter a permissão SELECT em todas as tabelas de origem referidas na consulta.

Todos os identificadores na definição da visualização materializada (nomes de tabelas, nomes de colunas, aliases) devem estar em minúsculas. O AWS Glue Data Catalog armazena identificadores em letras minúsculas para compatibilidade com o Hive. O Amazon Redshift rejeita as instruções CREATE MATERIALIZED VIEW com USING ICEBERG que contêm identificadores em maiúsculas.

A criação ou atualização de visualizações materializadas do Iceberg não é aceita quando enable_case_sensitive_identifier está definida como true. Essa restrição existe porque o AWS Glue Data Catalog não comporta identificadores com distinção entre maiúsculas e minúsculas. Se seu cluster ou grupo de trabalho tiver enable_case_sensitive_identifier habilitado, configure-o como false para a sessão antes de criar ou atualizar as visualizações materializadas do Iceberg:

SET enable_case_sensitive_identifier TO false;

As tabelas de origem devem estar no formato Apache Iceberg, versão 2 ou inferior. Tabelas que não sejam do Iceberg não podem ser usadas como tabelas de origem para visualizações materializadas do Iceberg.

Você não pode usar o seguinte com visualizações materializadas do Iceberg:

• Cláusula BACKUP

• Cláusulas DISTSTYLE, DISTKEY ou SORTKEY

• Referências a tabelas nativas, tabelas temporárias ou tabelas do sistema do Amazon Redshift

• AUTO REFRESH (a atualização automática não é aceita; atualize manualmente as visualizações materializadas do Iceberg)

Para acessar mais informações sobre os recursos e limitações da visualização materializada do Iceberg, consulte Visualizações materializadas armazenadas como tabelas do Apache Iceberg.

Atualização incremental para visões materializadas em uma unidade de compartilhamento de dados

O Amazon Redshift oferece suporte à atualização automática e incremental de visões materializadas em uma unidade de compartilhamento de dados do consumidor quando as tabelas base são compartilhadas. A atualização incremental é uma operação em que o Amazon Redshift identifica alterações em uma ou mais tabelas base que ocorreram após a atualização anterior e atualiza somente os registros correspondentes na visão materializada. Ela é mais rápida do que uma atualização completa e melhora o desempenho da workload. Você não precisa alterar a definição de visão materializada para usar a atualização incremental.

Há algumas limitações a serem observadas para usar a atualização incremental com uma visão materializada:

• A visão materializada deve fazer referência a um banco de dados apenas, seja local ou remoto.

• A atualização incremental está disponível somente em novas visões materializadas. Portanto, você deve descartar as visões materializadas existentes e recriá-las para que ocorra a atualização incremental.

Para obter mais informações sobre a criação de visões materializadas em uma unidade de compartilhamento de dados, consulte Trabalhar com visualizações no compartilhamento de dados do Amazon Redshift, que contém vários exemplos de consulta.

Atualizações em DDL para visões materializadas ou tabelas base

Ao usar visões materializadas no Amazon Redshift, siga estas observações de uso para atualizações em linguagem de definição de dados (DDL) para visões materializadas ou tabelas base.

• É possível adicionar colunas a uma tabela base sem afetar as visões materializadas que fazem referência à tabela-base.

• Algumas operações podem deixar a visão materializada em um estado que não pode ser atualizado de forma alguma. Entre os exemplos estão operações como renomeação ou descarte de uma coluna, alteração do tipo de uma coluna e alteração do nome de um esquema. Essas visões materializadas podem ser consultadas, mas não podem ser atualizadas. Nesse caso, você deve descartar e recriar a visão materializada.

• Em geral, não é possível alterar a definição de uma visão materializada (instrução SQL desta).

• Não é possível renomear uma visão materializada.

Limitações

Você não pode definir uma visão materializada que faça referência ou inclua o seguinte:

• Exibições padrão ou tabelas e exibições do sistema.

• Tabelas temporárias.

• Funções definidas pelo usuário.

• A cláusula ORDER BY, LIMIT ou OFFSET.

• Referências de vinculação tardia a tabelas base. Ou seja, todas as tabelas base ou colunas relacionadas referenciadas na definição da consulta SQL da visão materializada devem existir e serem válidas.

• Funções somente de nó líder: CURRENT_SCHEMA, CURRENT_SCHEMAS, HAS_DATABASE_PRIVILEGE, HAS_SCHEMA_PRIVILEGE, HAS_TABLE_PRIVILEGE.

• Você não pode usar a opção AUTO REFRESH YES quando a definição de visão materializada incluir funções mutáveis ou esquemas externos. Você também não pode usá-lo ao definir uma visão materializada em outra visão materializada.

• Não é necessário executar manualmente o ANALYZE com visões materializadas. Atualmente, isso ocorre somente por meio do AUTO ANALYZE. Para obter mais informações, consulte Análise de tabelas.

• Tabelas protegidas por RLS ou DDM.

• As visões materializadas criadas em clusters remotos de unidades de compartilhamento de dados não são compatíveis com referências em outras visões materializadas, tabelas do Spectrum, tabelas definidas em um cluster diferente do Redshift e UDFs. Isso é possível quando as visões materializadas são criadas no cluster local (produtor).

Para visualizações materializadas do Iceberg criadas com USING ICEBERG, as seguintes limitações adicionais se aplicam:

• As tabelas de origem devem estar no formato Apache Iceberg.

• Todos os identificadores devem estar em minúscula.

• Funções definidas pelo usuário e funções mutáveis não são permitidas.

• As tabelas de origem devem residir na mesma Região da AWS e conta que a visualização materializada.

• Tabelas AWS Lake Formation filtradas (FGAC) não podem ser usadas como tabelas de origem.

Exemplos

O exemplo a seguir cria uma visão materializada de três tabelas base que são unidas e agregadas. Cada linha representa uma categoria com o número de bilhetes vendidos. Ao consultar a visão materializada tickets_mv, os dados pré-calculados da visão materializada tickets_mv são acessados diretamente.

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;

O exemplo a seguir cria uma visão materializada semelhante ao exemplo anterior e usa a função agregada 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;

O exemplo a seguir usa uma cláusula UNION ALL para unir a tabela public_sales do Amazon Redshift e a tabela spectrum.sales do Redshift Spectrum para criar uma visão materializada mv_sales_vw. Para obter informações sobre o comando CREATE EXTERNAL TABLE para Amazon Redshift Spectrum, consulte CREATE EXTERNAL TABLE. A tabela externa do Redshift Spectrum referencia os dados no 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

O exemplo a seguir cria uma visão materializada mv_fq com base em uma tabela externa de consulta federada. Para obter informações sobre consulta federada, consulte 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)

O exemplo a seguir mostra a definição de uma visão materializada.

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;

O exemplo a seguir mostra como definir AUTO REFRESH na definição de visão materializada e também especifica um DISTSTYLE. Primeiro, crie uma tabela base simples.

CREATE TABLE baseball_table (ball int, bat int);

Depois, crie uma visão materializada.

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

Agora você pode consultar a visão materializada de mv_baseball. Para verificar se a opção AUTO REFRESH está ativada para uma visão materializada, consulte STV_MV_INFO.

O exemplo a seguir cria uma visão materializada que faz referência a uma tabela de origem em outro banco de dados. Ele assume que o banco de dados que contém a tabela de origem, database_A, está no mesmo cluster ou grupo de trabalho da visão materializada, que é criada em database_B. (Você pode usar seus próprios bancos de dados no exemplo.) Primeiro, crie uma tabela em database_A chamada cities, com uma coluna cityname. Defina o tipo de dado da coluna como VARCHAR. Depois de criar a tabela de origem, execute o seguinte comando em database_B para criar uma visão materializada cuja origem seja sua tabela cities. Especifique o banco de dados e o esquema da tabela de origem na cláusula FROM:

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

Consulte a visão materializada que você criou. A consulta recupera registros cuja fonte original é a tabela cities em database_A:

select * from cities_mv;

Quando você executa a instrução SELECT, cities_mv retorna os registros. Os registros são atualizados da tabela de origem somente quando uma instrução REFRESH é executada. Além disso, observe que não é possível atualizar registros diretamente na visão materializada. Para obter informações sobre como atualizar os dados em uma visão materializada, consulte REFRESH MATERIALIZED VIEW.

Para obter detalhes sobre a visão geral da visão materializada e os comandos SQL usados para atualizar e descartar visões materializadas, consulte os seguintes tópicos:

• Visões materializadas no Amazon Redshift

• REFRESH MATERIALIZED VIEW

• DROP MATERIALIZED VIEW

O exemplo a seguir cria uma visualização materializada do Iceberg em um bucket de Tabelas do Amazon S3 com particionamento.

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;

O exemplo a seguir cria uma visualização materializada do Iceberg em um bucket do Amazon S3 de uso geral com uma transformação e compactação de partição especificadas.

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;

O exemplo a seguir cria uma visualização materializada do Iceberg usando um esquema externo com notação em duas partes.

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;