Como trabalhar com exibições materializadas¶

Vantagens de exibições materializadas¶

A implementação de exibições materializadas do Snowflake fornece uma série de características únicas:

• Exibições materializadas podem melhorar o desempenho de consultas que utilizam repetidamente os mesmos resultados de subconsultas.

• Exibições materializadas são mantidas automaticamente e com transparência pelo Snowflake. Um serviço de fundo atualiza a exibição materializada após a realização de alterações na tabela base. Esse processo é mais eficiente e menos sujeito a erros do que manter manualmente o equivalente a uma exibição materializada no nível do aplicativo.

• Os dados acessados através de exibições materializadas são sempre atuais, independentemente da quantidade de DML que tenha sido realizada na tabela base. Se uma consulta é executada antes que a exibição materializada esteja atualizada, o Snowflake atualiza a exibição materializada ou usa as partes atualizadas da exibição materializada e recupera quaisquer dados mais recentes necessários da tabela base.

Como decidir quando criar uma exibição materializada ou uma exibição comum¶

Em geral, ao decidir se deve ser criada uma exibição materializada ou uma exibição comum, use os seguintes critérios:

• Crie uma exibição materializada quando todos os seguintes forem verdadeiros:

  • Os resultados de consulta da exibição não mudam com frequência. Isso quase sempre significa que a tabela subjacente/base da exibição não muda com frequência, ou pelo menos que o subconjunto de linhas da tabela base usada na exibição materializada não muda com freqüência.

  • Os resultados da exibição são usados com frequência (normalmente com mais frequência do que os resultados da consulta mudam).

  • A consulta consome muitos recursos. Normalmente, isso significa que a consulta consome muito tempo de processamento ou créditos, mas também pode significar que a consulta consome muito espaço de armazenamento para resultados intermediários.

• Crie uma exibição comum quando qualquer um dos seguintes for verdadeiro:

  • Os resultados da exibição mudam com frequência.

  • Os resultados não são utilizados com frequência (em relação ao ritmo em que os resultados mudam).

  • A consulta não consome muitos recursos, portanto não é dispendioso executá-la novamente.

Esses critérios são apenas diretrizes. Uma exibição materializada pode proporcionar benefícios mesmo que não seja utilizada com frequência —, especialmente se os resultados mudarem com menos frequência do que o uso da exibição.

Além disso, há outros fatores a considerar ao decidir se você deve utilizar uma exibição comum ou uma exibição materializada.

Por exemplo, o custo de armazenamento da exibição materializada é um fator; se os resultados não forem usados com muita frequência (mesmo que sejam usados com mais frequência do que mudam), os custos adicionais de armazenamento podem não valer o ganho de desempenho.

Comparação com tabelas, exibições comuns e resultados em cache¶

Exibições materializadas são semelhantes às tabelas em alguns aspectos e semelhantes às exibições comuns (isso é, não-materializadas) em outros. Além disso, exibições materializadas têm algumas semelhanças com os resultados em cache, particularmente porque ambos permitem armazenar os resultados da consulta para reutilização futura.

Esta seção descreve algumas das semelhanças e diferenças entre esses objetos em áreas específicas, inclusive:

• Desempenho da consulta.

• Segurança da consulta.

• Redução da complexidade lógica da consulta.

• Clustering de dados (relacionado ao desempenho da consulta).

• Custos de armazenamento e manutenção.

O Snowflake armazena os resultados de consulta em cache por um curto período de tempo após uma consulta ter sido executada. Em algumas situações, se a mesma consulta for executada novamente e se nada tiver mudado na(s) tabela(s) que a consulta acessa, o Snowflake pode simplesmente retornar os mesmos resultados sem executar novamente a consulta. Essa é a forma mais rápida e mais eficiente de reutilização, mas também a menos flexível. Para obter mais detalhes, consulte Uso de resultados de consultas persistentes.

Tanto as exibições materializadas quanto os resultados da consulta em cache proporcionam benefícios em relação ao desempenho da consulta:

• Exibições materializadas são mais flexíveis do que os resultados em cache, mas normalmente são mais lentas.

• Exibições materializadas são mais rápidas que as tabelas por causa de seu “cache” (ou seja, os resultados da consulta para a exibição); além disso, se os dados tiverem mudado, elas podem usar seu “cache” para dados que não mudaram e usar a tabela base para quaisquer dados que tenham mudado.

Exibições comuns não armazenam dados e, portanto, não podem melhorar o desempenho através do cache. No entanto, em alguns casos, as exibições ajudam o Snowflake a gerar um plano de consulta mais eficiente. Além disso, tanto as exibições materializadas quanto as comuns aumentam a segurança de dados, permitindo que os dados sejam expostos ou ocultos no nível da linha ou da coluna.

A tabela a seguir mostra as principais semelhanças e diferenças entre tabelas, exibições comuns, resultados de consultas em cache e exibições materializadas:

Exemplos de casos de uso para exibições materializadas¶

Esta seção descreve alguns cenários gerais de uso que também fornecem uma visão geral conceitual das exibições materializadas:

• Suponha que, todos os dias, você execute uma consulta Q que inclua uma subconsulta S. Se S consumir muitos recursos e consultar dados que mudam apenas uma vez por semana, você poderia melhorar o desempenho da consulta externa Q executando S e colocando os resultados em cache em uma tabela chamada CT:

  • Você atualizaria a tabela apenas uma vez por semana.

  • No resto do tempo, quando você executasse Q, a consulta se referiria aos resultados da subconsulta de S que foram armazenados na tabela.

  Isso funcionaria bem desde que os resultados da subconsulta S mudassem previsivelmente (por exemplo, no mesmo horário todas as semanas).

  Entretanto, se os resultados de S mudarem imprevisivelmente, o cache dos resultados em uma tabela seria arriscado; algumas vezes sua consulta principal Q retornaria resultados desatualizados se os resultados da subconsulta S estivessem desatualizados (e assim os resultados da tabela em cache CT estivessem desatualizados).

  O ideal seria ter um tipo especial de cache para resultados que raramente mudam, mas para os quais o momento da mudança é imprevisível. Olhando de outra forma, seria bom forçar sua subconsulta S a ser executada novamente (e sua tabela de cache CT a ser atualizada) quando necessário.

  Uma exibição materializada implementa uma aproximação do melhor dos dois mundos. Você define uma consulta para sua exibição materializada, e os resultados da consulta são armazenados em cache (como se fossem armazenados em uma tabela interna), mas o Snowflake atualiza o cache quando há uma atualização na tabela na qual a exibição materializada é definida. Assim, os resultados de sua subconsulta estão prontamente disponíveis para um rápido desempenho.

• Como exemplo menos abstrato, suponha que você administra uma pequena filial de uma grande farmácia, e sua filial estoca centenas de medicamentos de um total de dezenas de milhares de medicamentos aprovados pelo FDA.

  Suponha também que você tenha uma lista completa de todos os medicamentos que cada um de seus clientes toma, e que quase todos esses clientes peçam apenas medicamentos que estejam em estoque (ou seja, pedidos especiais são raros).

  Nesse cenário, você poderia criar uma exibição materializada que lista apenas as interações entre os medicamentos que você mantém em estoque. Quando um cliente pede um medicamento que nunca usou antes, se tanto esse medicamento quanto todos os outros que ele toma estiverem cobertos por sua exibição materializada, você não precisa verificar todo o banco de dados do FDA para interações medicamentosas; você pode verificar apenas a exibição materializada para que a busca seja mais rápida.

• Você pode usar uma exibição materializada por si só, ou pode usá-la em uma junção.

  Continuando com o exemplo da farmácia, suponha que você tenha uma tabela que lista todos os medicamentos que cada um de seus clientes toma; você pode juntar essa tabela à exibição materializada das interações medicamentosas para descobrir quais dos medicamentos atuais do cliente podem interagir com o novo medicamento.

  Você pode usar uma junção externa para certificar-se de listar todos os medicamentos do cliente, independentemente de eles estarem na sua exibição materializada; se a junção externa mostrar que algum dos medicamentos atuais não está na exibição materializada, você pode executar novamente a consulta na tabela completa de interações medicamentosas.

Como o otimizador de consulta usa exibições materializadas¶

Você não precisa especificar uma exibição materializada em uma instrução SQL para que a exibição possa ser utilizada. O otimizador de consultas pode reescrever automaticamente as consultas na tabela base ou exibições comuns para usar a exibição materializada em seu lugar.

Por exemplo, suponha que uma exibição materializada contenha todas as linhas e colunas que são necessárias para uma consulta em uma tabela base. O otimizador pode decidir reescrever a consulta para usar a exibição materializada em vez da tabela base. Isso pode acelerar drasticamente uma consulta, especialmente se a tabela base contiver uma grande quantidade de dados históricos.

Como outro exemplo, em uma junção de várias mesas, o otimizador pode decidir usar uma exibição materializada em vez de uma tabela para uma das tabelas da junção.

Uma exibição materializada também pode ser usada como fonte de dados para uma subconsulta.

Quando o otimizador escolhe usar uma exibição materializada implicitamente, a exibição materializada é listada no plano EXPLAIN ou no Perfil da Consulta em vez da tabela base. Você pode usar essas informações para experimentar e entender quais consultas podem se beneficiar das exibições materializadas existentes.

Comandos DDL para exibições materializadas¶

As exibições materializadas são objetos de banco de dados de primeira classe. O Snowflake fornece os seguintes comandos DDL para criar e manter exibições materializadas:

• CREATE MATERIALIZED VIEW

• ALTER MATERIALIZED VIEW

• DROP MATERIALIZED VIEW

• DESCRIBE MATERIALIZED VIEW

• SHOW MATERIALIZED VIEWS

Operações DML em exibições materializadas¶

O Snowflake não permite DML (por exemplo, INSERT, UPDATE, DELETE) padrão sobre exibições materializadas. O Snowflake não permite que os usuários trunquem exibições materializadas.

Consulte Limitações ao trabalho com exibições materializadas (neste tópico) para obter mais detalhes.

Privilégios de controle de acesso¶

Há três tipos de privilégios que estão relacionados a exibições materializadas:

• Privilégios sobre o esquema que contém a exibição materializada.

• Privilégios diretamente em relação à própria exibição materializada.

• Privilégios em relação aos objetos de banco de dados (por exemplo, tabelas) que a exibição materializada acessa.

Você pode usar os comandos padrão para conceder e revogar privilégios sobre exibições materializadas:

• GRANT <privilégios>

• REVOKE <privilégios>

GRANT CREATE MATERIALIZED VIEW ON SCHEMA <schema_name> TO ROLE <role_name>;

Exibições materializadas seguras¶

Exibições materializadas podem ser exibições seguras.

A maioria das informações sobre exibições seguras se aplica a exibições materializadas seguras. Há alguns casos em que as exibições materializadas seguras são diferentes das exibições não-materializadas seguras. As diferenças incluem:

• O comando para descobrir se uma exibição é segura.

  • Para exibições não-materializadas, verifique a coluna IS_SECURE na saída do comando SHOW VIEWS.

  • Para exibições materializadas, verifique a coluna IS_SECURE na saída do comando SHOW MATERIALIZED VIEWS.

Para obter mais informações sobre exibições seguras, consulte Como trabalhar com exibições seguras.

A sintaxe para criar exibições materializadas seguras está documentada em CREATE MATERIALIZED VIEW.

Planejamento para criar uma exibição materializada¶

Ao decidir criar uma exibição materializada, considere fazer algumas análises para determinar a necessidade da exibição:

1. Examine os filtros, projeções e agregações de consultas que são frequentes ou caras.

2. Use o Perfil de Consulta e o comando EXPLAIN para ver se as exibições materializadas existentes já estão sendo usadas pelo recurso de reescrita automática da consulta. Você pode descobrir que não é necessário criar novas exibições materializadas se houver exibições existentes que se encaixem bem nas consultas.

3. Antes de adicionar qualquer exibição materializada, registre os custos e o desempenho atual da consulta para que você possa avaliar a diferença após criar a nova exibição materializada.

4. Se você encontrar consultas com filtros muito seletivos que não se beneficiem do clustering da tabela, uma exibição materializada contendo os mesmos filtros pode ajudar as consultas a evitar percorrer muitos dados.

   Da mesma forma, se você encontrar consultas que utilizam agregação ou que contêm expressões que são muito caras para avaliar (por exemplo, chamadas de funções caras ou operações caras sobre dados semiestruturados), uma exibição materializada que utiliza a(s) mesma(s) expressão(ões) ou agregação(ões) pode ser beneficial.

5. Execute o comando EXPLAIN nas consultas originais, ou execute as consultas e verifique o Perfil de Consulta, para ver se a nova exibição materializada está sendo utilizada.

6. Monitore a consulta combinada e os custos da exibição materializada e avalie se o desempenho ou os benefícios de custo justificam o custo de manutenção da exibição materializada.

   Examine também os custos de consulta da tabela base. Nos casos em que o otimizador pode reescrever a consulta para utilizar uma exibição materializada, a compilação da consulta pode consumir mais tempo e recursos. (O otimizador tem um número maior de possibilidades a considerar).

7. Lembre que você pode sempre fazer referência direta a exibições materializadas se isso simplificar suas consultas ou se você souber que uma exibição materializada lhe dará um melhor desempenho. Entretanto, na maioria dos casos, você pode simplesmente consultar a tabela base, e o recurso de reescrita automática da consulta fará isso por você.

Como criar uma exibição materializada¶

Use o comando CREATE MATERIALIZED VIEW para criar uma exibição materializada. Para obter um exemplo, consulte Exemplo básico: Como criar uma exibição materializada (neste tópico).

Ao criar uma exibição materializada, observe o seguinte:

• Tratamento de nomes de colunas que não são permitidos em exibições materializadas

• Referência à tabela base

• Especificação de filtros para otimização de consulta

CREATE OR REPLACE MATERIALIZED VIEW mv AS
  SELECT SYSTEM$ALPHA AS col1, ...

Limitações na criação de exibições materializadas¶

As seguintes limitações se aplicam à criação de exibições materializadas:

• Uma exibição materializada pode consultar apenas uma única tabela.

• Não há suporte para junções, incluindo autojunções.

• Uma exibição materializada não pode realizar consultas:

  • Uma exibição materializada.

  • Uma exibição não-materializada.

  • Uma UDTF (função de tabela definida pelo usuário).

• Uma exibição materializada não pode incluir:

  • UDFs (essa limitação se aplica a todos os tipos de funções definidas pelo usuário, incluindo funções externas).

  • Funções de janela.

  • Cláusulas HAVING.

  • Cláusula ORDER BY.

  • Cláusula LIMIT.

  • Chaves GROUP BY que não estão dentro da lista SELECT. Todas as chaves GROUP BY em uma exibição materializada devem fazer parte da lista SELECT.

  • GROUP BY GROUPING SETS.

  • GROUP BY ROLLUP.

  • GROUP BY CUBE.

  • Aninhamento de subconsultas dentro de uma exibição materializada.

  • Os operadores de conjuntos MINUS, EXCEPT ou INTERSECT.

• Muitas funções agregadas não são permitidas em uma definição de exibição materializada.

  • As funções agregadas que são suportadas em exibições materializadas são:

    • APPROX_COUNT_DISTINCT (HLL).

    • AVG (exceto quando usado em PIVOT).

    • BITAND_AGG.

    • BITOR_AGG.

    • BITXOR_AGG.

    • COUNT.

    • COUNT_IF.

    • MAX.

    • MIN.

    • STDDEV.

    • STDDEV_POP.

    • STDDEV_SAMP.

    • SUM.

    • VARIANCE (VARIANCE_SAMP, VAR_SAMP).

    • VARIANCE_POP (VAR_POP).

    As outras funções agregadas não são suportadas em exibições materializadas.

  Nota

  Funções agregadas que são permitidas em exibições materializadas ainda têm algumas restrições:

  • Funções agregadas não podem ser aninhadas.

  • Funções agregadas usadas em expressões complexas (por exemplo, (sum(salary)/10)) só podem ser usadas no nível mais externo de uma consulta, não em uma subconsulta ou uma exibição inline.

    Por exemplo, o seguinte é permitido:

    create materialized view mv1 as
        select
            sum(x) + 100
          from t;
    

    Copy

    O seguinte não é permitido:

    create materialized view mv2 as
        select
            y + 10
          from (
            select
              sum(x) as y
            from t
          );
    

    Copy

  • DISTINCT não pode ser combinado com funções agregadas.

  • Em uma exibição materializada, as funções agregadas AVG, COUNT, COUNT_IF, MIN, MAX e SUM podem ser usadas como funções agregadas, mas não como funções de janela. Em uma exibição materializada, essas funções não podem ser utilizadas com a cláusula OVER:

    OVER ( [ PARTITION BY <expr1> ] [ ORDER BY <expr2> ] )
    

    Copy

  • Se uma função agregada está em uma subconsulta, a exibição materializada não pode criar uma expressão por cima da(s) coluna(s) agregada(s) a partir dessa subconsulta. Por exemplo, considere a seguinte definição de exibição materializada:

    create or replace materialized view mv1 as
        select c1 + 10 as c1new, c2
            from (select sum(c1) as c1, c2 from t group by c2);
    

    Copy

    A expressão “c1 + 10” é uma expressão por cima de uma função agregada em uma subconsulta e, portanto, causa uma mensagem de erro.

    Note que mesmo um operador de igualdade conta como uma expressão, o que significa que expressões CASE que utilizam colunas que representam funções agregadas em uma subconsulta também são proibidas.

    Para contornar essa limitação, crie uma exibição materializada sem a expressão e depois crie uma exibição não-materializada que inclua a expressão, por exemplo:

    create or replace materialized view mv1 as
        select c1, c2
            from (select sum(c1) as c1, c2 from t group by c2);
    
    create or replace view expr_v1 as
        select c1 + 10 as c1new, c2
            from (select c1, c2 from mv1);
    

    Copy

• As funções utilizadas em uma exibição materializada devem ser determinísticas. Por exemplo, não é permitido usar CURRENT_TIME ou CURRENT_TIMESTAMP.

• Uma exibição materializada não deve ser definida usando uma função que produza resultados diferentes para configurações diferentes de parâmetros, como o parâmetro em nível de sessão TIMESTAMP_TYPE_MAPPING.

  Por exemplo, suponha que uma exibição seja definida da seguinte forma:

  create materialized view bad_example (ts1) as
      select to_timestamp(n) from t1;
  

  Copy

  O tipo de dados do valor de retorno de TO_TIMESTAMP(n) depende do parâmetro TIMESTAMP_TYPE_MAPPING; portanto, o conteúdo da exibição materializada depende do valor de TIMESTAMP_TYPE_MAPPING no momento em que a exibição foi criada.

  Quando uma exibição materializada é criada, a expressão que define cada uma de suas colunas é avaliada e armazenada. Se a definição de uma coluna depende de uma variável de sessão específica, e a variável de sessão muda, a expressão não é reavaliada, e a exibição materializada não é atualizada. Se a exibição materializada depende de um valor particular de uma variável de sessão e o valor da variável de sessão muda, as consultas sobre a exibição materializada falham.

  Para evitar esse problema, force a expressão a assumir um valor que não dependa de nenhuma variável da sessão. O exemplo abaixo converte a saída para um determinado tipo de dados, independentemente do parâmetro TIMESTAMP_TYPE_MAPPING:

  create materialized view good_example (ts1) as
      select to_timestamp(n)::TIMESTAMP_NTZ from t1;
  

  Copy

  Essa questão é específica para exibições materializadas. Exibições não-materializadas geram sua saída dinamicamente com base nas configurações dos parâmetros atuais, de modo que os resultados não podem ser estagnados.

• Na definição de uma exibição materializada, não há suporte para a seleção da coluna SEQ na saída da função FLATTEN.

  Não há garantia de que os valores na coluna SEQ sejam ordenados de forma alguma quando selecionados em uma exibição materializada. Se você selecionar esta coluna na definição da exibição materializada, a saída poderá não ser determinística.

• Exibições materializadas não podem ser criadas usando o recurso Time Travel.

Exemplo básico: Como criar uma exibição materializada¶

Esta seção contém um exemplo básico de criação e utilização de uma exibição materializada:

CREATE OR REPLACE MATERIALIZED VIEW mv1 AS
  SELECT My_ResourceIntensive_Function(binary_col) FROM table1;

SELECT * FROM mv1;

Copy

Exemplos mais detalhados são fornecidos em Exemplos (neste tópico).

Como as exibições materializadas são mantidas¶

Depois de criar uma exibição materializada, um processo no fundo mantém automaticamente os dados na exibição materializada. Observe o seguinte:

• A manutenção de exibições materializadas é realizada por um processo em segundo plano, e o tempo é otimizado com base na carga de trabalho na tabela base e na exibição materializada.

  • Este processo atualiza a exibição materializada com as alterações feitas por operações DML para a tabela base (inserções, atualizações e exclusões).

    Além disso, o clustering na tabela base também pode resultar em atualizações de uma exibição materializada. Consulte Práticas recomendadas para o clustering de exibições materializadas e suas tabelas base.

  • Quando as linhas são inseridas na tabela base, o processo executa uma operação de «atualização» para inserir as novas linhas na exibição materializada.

  • Quando as linhas são excluídas na tabela base, o processo executa uma operação de «compactação» na exibição materializada, excluindo essas linhas da exibição materializada.

• Para ver a última vez que uma exibição materializada foi atualizada, execute o comando SHOW MATERIALIZED VIEWS.

  Verifica as colunas REFRESHED_ON e BEHIND_BY na saída:

  • As colunas REFRESHED_ON e COMPACTED_ON mostram o carimbo de data/hora da última operação DML na tabela base que foi processada pelas operações de atualização e compactação, respectivamente.

  • A coluna BEHIND_BY indica a quantidade de tempo que as atualizações para a exibição materializada estão por trás das atualizações para a tabela base.

• Se a manutenção ficar para trás, as consultas podem ser executadas mais lentamente do que quando as exibições estão atualizadas, mas os resultados sempre serão atualizados.

  Se algumas micropartições da exibição materializada estiverem desatualizadas, o Snowflake pulará essas partições e consultará os dados na tabela base.

• Se o processo em segundo plano encontrar determinados erros do usuário (por exemplo, a consulta da exibição resulta em um erro de «divisão por zero»), o processo invalidará a exibição materializada.

  Consultar uma exibição materializada inválida resulta em erro. A mensagem de erro inclui o motivo pelo qual a exibição materializada foi invalidada. Por exemplo:

  Failure during expansion of view 'MY_MV':
    SQL compilation error: Materialized View MY_MV is invalid.
    Invalidation reason: Division by zero
  

  Se isso ocorrer, resolva o problema descrito na mensagem de erro (por exemplo, exclua as linhas que apresentam o erro «dividir por zero») e retome a exibição materializada usando o comando ALTER MATERIALIZED VIEW … RESUME.

Como suspender e retomar a manutenção em uma exibição materializada¶

Caso necessite suspender a manutenção e uso de uma exibição materializada, execute o comando ALTER MATERIALIZED VIEW com o parâmetro SUSPEND:

ALTER MATERIALIZED VIEW <name> SUSPEND

Se você suspender a manutenção de uma exibição, você não poderá consultar a exibição até retomar a manutenção.

Para retomar a manutenção e uso de uma exibição materializada, execute o comando ALTER MATERIALIZED VIEW com o parâmetro RESUME:

ALTER MATERIALIZED VIEW <name> RESUME

Para obter um exemplo, consulte Como suspender atualizações para uma exibição materializada.

Como exibir informações sobre exibições materializadas¶

O seguinte comando e exibição fornecem informações sobre exibições materializadas:

• O comando SHOW VIEWS retorna informações tanto sobre exibições materializadas como sobre exibições comuns.

• A exibição INFORMATION_SCHEMA.TABLES mostra exibições materializadas. A coluna TABLE_TYPE mostra “MATERIALIZED VIEW”. A coluna IS_INSERTABLE é sempre “NO”, porque não se pode inserir diretamente em uma exibição materializada.

  Nota

  A exibição INFORMATION_SCHEMA.VIEWS não mostra exibições materializadas. Exibições materializadas são mostradas por INFORMATION_SCHEMA.TABLES.

Limitações ao trabalho com exibições materializadas¶

As seguintes limitações se aplicam ao uso de exibições materializadas:

• Para garantir que as exibições materializadas permaneçam consistentes com a tabela base na qual elas são definidas, não é possível realizar a maioria das operações DML em uma exibição materializada em si. Por exemplo, não é possível inserir linhas diretamente em uma exibição materializada (embora você possa inserir linhas na tabela base, é claro). As operações DML proibidas incluem:

  • COPY

  • DELETE

  • INSERT

  • MERGE

  • UPDATE

  A truncagem de uma exibição materializada não é suportada.

• Você não pode clonar diretamente uma exibição materializada usando o comando CREATE MATERIALIZED VIEW ... CLONE.... Entretanto, se você clonar um esquema ou um banco de dados que contenha uma exibição materializada, a exibição materializada será clonada e incluída no novo esquema ou banco de dados.

• O Snowflake não oferece suporte para o uso do recurso Time Travel para consultar exibições materializadas em um ponto no passado (por exemplo, usando a cláusula AT ao consultar uma exibição materializada).

  Entretanto, você pode usar o Time Travel para clonar um banco de dados ou esquema contendo uma exibição materializada em um ponto no passado. Para obter mais detalhes, consulte Exibições materializadas e Time Travel.

• Exibições materializadas não são monitoradas pelo Snowflake Como trabalhar com monitores de recursos.

Efeitos das mudanças nas tabelas de base sobre as exibições materializadas¶

As seções a seguir explicam como as exibições materializadas são afetadas pelas alterações nas tabelas de base.

• Adição de colunas à tabela de base

• Alteração ou descarte de colunas na tabela de base

• Como renomear ou trocar a tabela de base

• Descarte da tabela de base

Exibições materializadas em esquemas e bancos de dados clonados¶

Se você clonar um esquema ou um banco de dados que contenha uma exibição materializada, a exibição materializada será clonada.

Se você clonar a exibição materializada e a tabela base correspondente ao mesmo tempo (como parte da mesma operação CREATE SCHEMA ... CLONE ou CREATE DATABASE ... CLONE), a exibição materializada clonada se referirá à tabela base clonada.

Se você clonar a exibição materializada sem clonar a tabela base (por exemplo, se a tabela estiver em Database1.Schema1 e a exibição estiver em Database1.Schema2, e você clonar apenas Schema2 em vez de toda a Database1), a exibição clonada se referirá à tabela base original.

Estimativa e controle de custos¶

Não há ferramentas para estimar os custos de manutenção das exibições materializadas. Em geral, os custos são proporcionais a:

• ao número de exibições materializadas criadas em cada tabela base e à quantidade de dados que muda em cada uma dessas exibições materializadas quando a tabela base muda. Qualquer alteração nas micropartições da tabela base pode exigir uma manutenção da exibição materializada, não importando se essas alterações são necessárias devido a reclustering ou a instruções DML executadas sobre a tabela base.

• O número dos exibições materializadas que estão clusterizadas. A manutenção do clustering (seja de uma tabela ou de uma exibição materializada) acrescenta custos.

  Se uma exibição materializada for clusterizada de forma diferente da tabela base, o número de micropartições alteradas na exibição materializada poderá ser consideravelmente maior do que o número de micropartições alteradas na tabela base.

  Por exemplo, considere o caso em que a tabela base é modificada em grande parte pela inserção (anexação) de dados, e não é clusterizada, de modo que a tabela base está em grande parte na ordem em que as linhas foram inseridas na tabela. Imagine que a exibição materializada seja clusterizada por uma coluna independente, por exemplo, o código postal. Se 100 novas linhas forem adicionadas à tabela base, estas poderão ir para uma ou duas novas micropartições, deixando as outras micropartições na mesa base intocadas. Mas essas 100 linhas podem exigir a reescrita de 100 micropartições na exibição materializada clusterizada.

  Como outro exemplo, considere exclusões. Excluir as linhas mais antigas de uma tabela de base não clusterizada pode excluir apenas as micropartições mais antigas, mas pode exigir mudanças para um número muito maior de micropartições em uma exibição materializada que não está clusterizada por idade.

  (Para obter mais detalhes sobre o clustering de exibições materializadas, consulte Exibições materializadas e clustering).

Você pode controlar o custo de manter as exibições materializadas escolhendo cuidadosamente quantas exibições criar, em quais tabelas criá-las e a definição de cada exibição (incluindo o número de linhas e colunas naquela exibição).

Você também pode controlar os custos suspendendo ou retomando a exibição materializada; no entanto, suspender a manutenção normalmente apenas adia os custos em vez de reduzi-los. Quanto mais tempo a manutenção tiver sido adiada, mais manutenção haverá a fazer.

Consulte também Práticas recomendadas para manter exibições materializadas.

Visualização dos custos¶

Você pode visualizar os custos cobrados para manter as exibições materializadas usando Snowsight, a Classic Console ou SQL:

Snowsight

Como um usuário com os privilégios adequados, selecione Admin » Usage.

Classic Console

Como administrador da conta, selecione Account » Billing & Usage.

Os custos de crédito são rastreados em um warehouse virtual fornecido pelo Snowflake chamado MATERIALIZED_VIEW_MAINTENANCE.

SQL

Consulte um dos seguintes:

• Função de tabela MATERIALIZED_VIEW_REFRESH_HISTORY (no Snowflake Information Schema).

  Por exemplo:

  SELECT * FROM TABLE(INFORMATION_SCHEMA.MATERIALIZED_VIEW_REFRESH_HISTORY());
  

  Copy

• Exibição Exibição MATERIALIZED_VIEW_REFRESH_HISTORY (em Account Usage).

  As seguintes consultas podem ser executadas na exibição MATERIALIZED_VIEW_REFRESH_HISTORY:

  Consulta: Histórico de custos das exibições materializadas (por dia, por objeto)

  Esta consulta fornece uma lista completa de exibições materializadas e o volume de créditos consumidos através do serviço durante os últimos 30 dias, separados por dia. Qualquer irregularidade no consumo do crédito ou consumo consistentemente alto são sinais para investigações adicionais.

  SELECT TO_DATE(start_time) AS date,
    database_name,
    schema_name,
    table_name,
    SUM(credits_used) AS credits_used
  FROM snowflake.account_usage.materialized_view_refresh_history
  WHERE start_time >= DATEADD(month,-1,CURRENT_TIMESTAMP())
  GROUP BY 1,2,3,4
  ORDER BY 5 DESC;
  

  Copy

  Consulta: Histórico de exibições materializadas e média de m dias

  Esta consulta mostra a média de créditos diários consumidos pelas exibições materializadas agrupados por semana durante o último ano. Ela pode ajudar a identificar anomalias nas médias diárias ao longo do ano, assim você pode investigar mais a fundo os picos ou mudanças inesperadas no consumo.

  WITH credits_by_day AS (
    SELECT TO_DATE(start_time) AS date,
      SUM(credits_used) AS credits_used
    FROM snowflake.account_usage.materialized_view_refresh_history
    WHERE start_time >= DATEADD(year,-1,CURRENT_TIMESTAMP())
    GROUP BY 1
    ORDER BY 2 DESC
  )
  
  SELECT DATE_TRUNC('week',date),
    AVG(credits_used) AS avg_daily_credits
  FROM credits_by_day
  GROUP BY 1
  ORDER BY 1;
  

  Copy

Práticas recomendadas para a criação de exibições materializadas¶

• A maioria das exibições materializadas devem fazer uma ou ambas as coisas a seguir:

  • Filtrar dados. Você pode fazer isso:

    • Filtrando linhas (por exemplo, definindo a exibição materializada de modo que apenas dados muito recentes sejam incluídos). Em alguns aplicativos, os melhores dados a serem armazenados são os dados anormais. Por exemplo, se você estiver monitorando a pressão em um gasoduto para estimar quando as tubulações podem falhar, você pode armazenar todos os dados de pressão na tabela base, e armazenar apenas medições de pressão anormalmente altas na exibição materializada. Da mesma forma, se você estiver monitorando o tráfego da rede, sua tabela base poderá armazenar todas as informações de monitoramento, enquanto sua exibição materializada poderá armazenar apenas informações incomuns e suspeitas (por exemplo, de endereços IP conhecidos para lançar ataques DOS (Negação de Serviço)).

    • Filtrando colunas (por exemplo, selecionando colunas específicas em vez de “SELECT * …”). Usar SELECT * ... para definir uma exibição materializada normalmente é caro. Isso também pode levar a erros futuros; se as colunas forem adicionadas posteriormente à tabela base (por exemplo, ALTER TABLE ... ADD COLUMN ...), a exibição materializada não incorporará as novas colunas automaticamente.

  • Realizar operações que usam muitos recursos e armazenar os resultados para que as operações que usam muitos recursos não precisem ser realizadas com a mesma frequência.

• Você pode criar mais de uma exibição materializada para a mesma tabela base. Por exemplo, você pode criar uma exibição materializada que contenha apenas os dados mais recentes, e outra exibição materializada que armazena dados incomuns. Você pode então criar uma exibição não-materializada que une as duas tabelas e mostra dados recentes que correspondem a dados históricos incomuns para que você possa detectar rapidamente situações incomuns, tais como um ataque DOS (negação de serviço) que está sendo preparado.

  O Snowflake recomenda a exibição materializada de dados incomuns somente quando:

  • A tabela base não está clusterizada, ou as colunas que contêm os dados incomuns ainda não fazem parte da chave de clustering da tabela base.

  • Os dados são incomuns o suficiente para que sejam fáceis de isolar, mas não tão incomuns que sejam raramente utilizados. (Se os dados forem usados raramente, o custo de manter a exibição materializada provavelmente ultrapassará o benefício de desempenho e a economia de custos de poder acessá-los rapidamente quando eles são utilizados).

Práticas recomendadas para manter exibições materializadas¶

• O Snowflake recomenda efetuar operações DML na tabela base em lote:

  • DELETE: Se as tabelas armazenam dados para o período de tempo mais recente (por exemplo, o dia ou semana ou mês mais recente), quando você reduz sua tabela base apagando dados antigos, as mudanças na tabela base são propagadas para a exibição materializada. Dependendo de como os dados são distribuídos pelas micropartições, isso poderia fazer com que você pague mais por atualizações de fundo das exibições materializadas. Em alguns casos, você pode conseguir reduzir custos excluindo com menos frequência (por exemplo, diariamente e não de hora em hora, ou de hora em hora e não a cada 10 minutos).

    Se você não precisar manter uma quantidade específica de dados antigos, você deve fazer testes para encontrar o melhor equilíbrio entre custo e funcionalidade.

  • INSERT, UPDATE e MERGE: A combinação desses tipos de instruções DML na tabela base pode reduzir o custo de manter as exibições materializadas.

Práticas recomendadas para o clustering de exibições materializadas e suas tabelas base¶

• Se você criar uma exibição materializada em uma tabela base, e se as exibições materializadas forem acessadas com frequência e a tabela base não for acessada com frequência, geralmente é mais eficiente evitar o clustering da tabela base.

  Se você criar uma exibição materializada em uma tabela clusterizada, considere remover qualquer clustering na tabela base, porque qualquer mudança no clustering da tabela base em algum momento exigirá uma atualização da exibição materializada, o que aumenta os custos de manutenção da exibição materializada.

• O clustering de exibições materializadas, especialmente exibições materializadas em tabelas de base que mudam com frequência, aumenta os custos. Não clusterize mais exibições materializadas do que o necessário.

• Quase todas as informações sobre clustering de tabelas também se aplicam ao clustering de exibições materializadas. Para obter mais informações sobre clustering de tabelas, consulte Estratégias para a seleção de chaves de clustering.

Exibição materializada simples¶

Esse primeiro exemplo ilustra uma exibição materializada simples e uma consulta simples na exibição.

Crie a tabela e carregue os dados, depois crie a exibição:

CREATE TABLE inventory (product_ID INTEGER, wholesale_price FLOAT,
  description VARCHAR);
    
CREATE OR REPLACE MATERIALIZED VIEW mv1 AS
  SELECT product_ID, wholesale_price FROM inventory;

INSERT INTO inventory (product_ID, wholesale_price, description) VALUES 
    (1, 1.00, 'cog');

Copy

Selecione os dados a partir da exibição:

SELECT product_ID, wholesale_price FROM mv1;
+------------+-----------------+
| PRODUCT_ID | WHOLESALE_PRICE |
|------------+-----------------|
|          1 |               1 |
+------------+-----------------+

Copy

Junção em uma exibição materializada¶

Você pode unir uma exibição materializada a uma tabela ou a outra exibição. Esse exemplo expande o exemplo anterior criando uma tabela adicional e depois uma exibição não-materializada que mostra lucros ao unir a exibição materializada a uma tabela:

CREATE TABLE sales (product_ID INTEGER, quantity INTEGER, price FLOAT);

INSERT INTO sales (product_ID, quantity, price) VALUES 
   (1,  1, 1.99);

CREATE or replace VIEW profits AS
  SELECT m.product_ID, SUM(IFNULL(s.quantity, 0)) AS quantity,
      SUM(IFNULL(quantity * (s.price - m.wholesale_price), 0)) AS profit
    FROM mv1 AS m LEFT OUTER JOIN sales AS s ON s.product_ID = m.product_ID
    GROUP BY m.product_ID;

Copy

Selecione os dados a partir da exibição:

SELECT * FROM profits;
+------------+----------+--------+
| PRODUCT_ID | QUANTITY | PROFIT |
|------------+----------+--------|
|          1 |        1 |   0.99 |
+------------+----------+--------+

Copy

Como suspender atualizações para uma exibição materializada¶

O exemplo a seguir suspende temporariamente o uso (e manutenção) da exibição materializada mv1 e mostra que as consultas nessa exibição geram uma mensagem de erro enquanto a visualização materializada está suspensa:

ALTER MATERIALIZED VIEW mv1 SUSPEND;
    
INSERT INTO inventory (product_ID, wholesale_price, description) VALUES 
    (2, 2.00, 'sprocket');

INSERT INTO sales (product_ID, quantity, price) VALUES 
   (2, 10, 2.99),
   (2,  1, 2.99);

Copy

Selecione dados da exibição materializada:

SELECT * FROM profits ORDER BY product_ID;

Copy

Saída:

002037 (42601): SQL compilation error:
Failure during expansion of view 'PROFITS': SQL compilation error:
Failure during expansion of view 'MV1': SQL compilation error: Materialized View MV1 is invalid.

Copy

Retomar:

ALTER MATERIALIZED VIEW mv1 RESUME;

Copy

Selecione dados da exibição materializada:

SELECT * FROM profits ORDER BY product_ID;
+------------+----------+--------+
| PRODUCT_ID | QUANTITY | PROFIT |
|------------+----------+--------|
|          1 |        1 |   0.99 |
|          2 |       11 |  10.89 |
+------------+----------+--------+

Copy

Clustering de uma exibição materializada¶

Este exemplo cria uma exibição materializada e depois a clusteriza:

Essas instruções criam duas tabelas que rastreiam informações sobre segmentos de um gasoduto (por exemplo, para gás natural).

Os segmentos com maior probabilidade de terem falha no futuro próximo frequentemente são os mais antigos, ou aqueles que são feitos de materiais que corroem facilmente, ou que sofreram períodos de pressão anormalmente alta. Portanto, esse exemplo monitora a idade, a pressão e o material (ferro, cobre, plástico PVC, etc.) de cada tubo.

CREATE TABLE pipeline_segments (
    segment_ID BIGINT,
    material VARCHAR, -- e.g. copper, cast iron, PVC.
    installation_year DATE,  -- older pipes are more likely to be corroded.
    rated_pressure FLOAT  -- maximum recommended pressure at installation time.
    );
    
INSERT INTO pipeline_segments 
    (segment_ID, material, installation_year, rated_pressure)
  VALUES
    (1, 'PVC', '1994-01-01'::DATE, 60),
    (2, 'cast iron', '1950-01-01'::DATE, 120)
    ;

CREATE TABLE pipeline_pressures (
    segment_ID BIGINT,
    pressure_psi FLOAT,  -- pressure in Pounds per Square Inch
    measurement_timestamp TIMESTAMP
    );
INSERT INTO pipeline_pressures 
   (segment_ID, pressure_psi, measurement_timestamp) 
  VALUES
    (2, 10, '2018-09-01 00:01:00'),
    (2, 95, '2018-09-01 00:02:00')
    ;

Copy

Os segmentos de gasodutos não mudam com muita frequência, e os segmentos de gasodutos mais antigos são os segmentos mais propensos a falhar. Portanto, crie uma exibição materializada dos segmentos mais antigos.

CREATE MATERIALIZED VIEW vulnerable_pipes 
  (segment_ID, installation_year, rated_pressure) 
  AS
    SELECT segment_ID, installation_year, rated_pressure
        FROM pipeline_segments 
        WHERE material = 'cast iron' AND installation_year < '1980'::DATE;

Copy

Você pode adicionar clustering ou mudar a chave de clustering. Por exemplo, para realizar um clustering em installation_year:

ALTER MATERIALIZED VIEW vulnerable_pipes CLUSTER BY (installation_year);

Copy

Novas medições de pressão chegam frequentemente (talvez a cada 10 segundos), então manter uma exibição materializada das medições de pressão seria caro. Portanto, embora o alto desempenho (obtenção rápida) de dados de pressão recentes seja importante, a tabela pipeline_pressures começa sem uma exibição materializada.

Se o desempenho for muito lento, você pode criar uma exibição materializada que contenha apenas dados de pressão recentes, ou que contenha apenas dados sobre eventos anormais de alta pressão.

Crie uma exibição (não-materializada) que combine as informações da exibição materializada e da tabela pipeline_pressures:

CREATE VIEW high_risk AS
    SELECT seg.segment_ID, installation_year, measurement_timestamp::DATE AS measurement_date, 
         DATEDIFF('YEAR', installation_year::DATE, measurement_timestamp::DATE) AS age, 
         rated_pressure - age AS safe_pressure, pressure_psi AS actual_pressure
       FROM vulnerable_pipes AS seg INNER JOIN pipeline_pressures AS psi 
           ON psi.segment_ID = seg.segment_ID
       WHERE pressure_psi > safe_pressure
       ;

Copy

Agora liste os segmentos de gasodutos de alto risco:

SELECT * FROM high_risk;
+------------+-------------------+------------------+-----+---------------+-----------------+
| SEGMENT_ID | INSTALLATION_YEAR | MEASUREMENT_DATE | AGE | SAFE_PRESSURE | ACTUAL_PRESSURE |
|------------+-------------------+------------------+-----+---------------+-----------------|
|          2 | 1950-01-01        | 2018-09-01       |  68 |            52 |              95 |
+------------+-------------------+------------------+-----+---------------+-----------------+

Copy

Isso mostra que o segmento de gasodutos com segment_id = 2, que é feito de um material que corrói, é antigo. Esse segmento nunca sofreu uma pressão maior do que a pressão máxima no momento em que foi instalado, mas devido ao potencial de corrosão, seu “limite seguro” diminuiu com o tempo, e a pressão mais alta que ele sofreu é maior do que a pressão que era recomendada para um tubo tão antigo quanto ele era no momento da medição da pressão.

Como criar uma exibição materializada de dados compartilhados¶

Você pode criar uma exibição materializada de dados compartilhados.

Account1:

create or replace table db1.schema1.table1(c1 int);
create or replace share sh1;
grant usage on database db1 to share sh1;
alter share sh1 add accounts = account2;
grant usage on schema db1.schema1 to share sh1;
grant select on table db1.schema1.table1 to share sh1;

Account2:

create or replace database dbshared from share account1.sh1;
create or replace materialized view mv1 as select * from dbshared.schema1.table1 where c1 >= 50;

Como compartilhar uma exibição materializada¶

Você pode usar o recurso de compartilhamento de dados do Snowflake para compartilhar uma exibição materializada.

Para obter mais informações sobre compartilhamento de dados, consulte Visão geral do compartilhamento de dados no Snowflake.

Erro de compilação: Failure during expansion of view '<nome>': SQL compilation error: Materialized View <nome> is invalid.¶

Causas possíveis

• A exibição materializada foi suspensa. Para obter mais informações sobre como suspender e retomar exibições, consulte ALTER MATERIALIZED VIEW.

• Uma alteração na tabela base da visão materializada invalidou a exibição materializada. Por exemplo, esse erro é retornado se:

  • A tabela base é removida.

  • Uma coluna na coluna da tabela base foi descartada.

• O processo em segundo plano encontrou um erro de um tipo específico (por exemplo, um erro de «divisão por zero») e não conseguiu atualizar a exibição materializada.

Possíveis soluções

• Se a exibição tiver sido suspensa:

  • Considere retomar a exibição executando ALTER MATERIALIZED VIEW nome_exibição RESUME.

  • Considere a possibilidade de comparar a consulta com a tabela base. No entanto, isso provavelmente consumirá mais créditos e demorará mais tempo do que a consulta na exibição materializada.

• Se a tabela base tiver sido modificada ou removida:

  • Se a tabela base foi removida, remova a exibição materializada.

  • Se a tabela base tiver sido modificada (por exemplo, uma coluna referenciada pela exibição tiver sido removida), e se a exibição materializada ainda for útil com a nova versão da tabela, considere remover e recriar a exibição materializada usando as colunas que permanecem na tabela base.

  • Se não houver nenhuma outra causa aparente para a mensagem de erro, considere a possibilidade de remover e recriar a exibição materializada.

  • Considere a possibilidade de comparar a consulta com a tabela base. No entanto, isso provavelmente consumirá mais créditos e demorará mais tempo do que a consulta na exibição materializada.

• Se o processo em segundo plano não conseguiu atualizar a exibição materializada devido a um erro, a mensagem de erro deverá incluir detalhes sobre o motivo pelo qual a exibição materializada foi invalidada. Por exemplo:

  Failure during expansion of view 'MY_MV':
    SQL compilation error: Materialized View MY_MV is invalid.
    Invalidation reason: Division by zero
  

Se isso ocorrer, resolva o problema descrito na mensagem de erro e retome a exibição materializada usando o comando ALTER MATERIALIZED VIEW … RESUME.

O comando SHOW MATERIALIZED VIEWS mostra exibições materializadas que não estão atualizadas¶

Possível causa

Uma causa possível é que a atualização falhou porque a instrução SELECT na definição da exibição falhou.

Como a atualização é feita pelo processo em segundo plano, você não verá uma mensagem de erro no momento em que a atualização for tentada. Em vez disso, você verá a mensagem de erro ao consultar a exibição materializada ou ao executar SHOW MATERIALIZED VIEWS.

Possível solução

Se a coluna invalid for true, verifique na coluna invalid_reason o motivo pelo qual a exibição foi invalidada.

Em alguns casos, você pode conseguir depurar o problema executando manualmente a instrução SELECT na definição da exibição materializada, ou executando uma instrução SELECT mais simples (menos cara) na tabela referenciada na definição da exibição materializada.

Se você não sabe a definição exata da exibição materializada, você pode encontrá-la na saída de SHOW MATERIALIZED VIEWS ou usando a função GET_DDL.