Crie vistas materializadas

Este documento descreve como criar vistas materializadas no BigQuery. Antes de ler este documento, familiarize-se com a Introdução às vistas materializadas.

Antes de começar

Conceda funções de gestão de identidade e acesso (IAM) que dão aos utilizadores as autorizações necessárias para realizar cada tarefa neste documento.

Autorizações necessárias

Para criar vistas materializadas, precisa da autorização de IAM.bigquery.tables.create

Cada uma das seguintes funções de IAM predefinidas inclui as autorizações de que precisa para criar uma vista materializada:

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

Para mais informações sobre a gestão de identidade e acesso (IAM) do BigQuery, consulte o artigo Controlo de acesso com a IAM.

Crie vistas materializadas

Para criar uma vista materializada, selecione uma das seguintes opções:

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
);

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

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

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

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

}

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

{
  "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"
  }
}

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

// Sample to create materialized view
public class CreateMaterializedView {

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

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

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

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

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

Depois de a vista materializada ser criada com êxito, é apresentada no painel do Explorador do BigQuery na Trusted Cloud consola. O exemplo seguinte mostra um esquema de vista materializada:

A menos que desative a atualização automática, o BigQuery inicia uma atualização completa assíncrona para a vista materializada. A consulta termina rapidamente, mas a atualização inicial pode continuar a ser executada.

Controlo de acesso

Pode conceder acesso a uma vista materializada ao nível do conjunto de dados, ao nível da vista ou ao nível da coluna. Também pode definir o acesso a um nível superior na hierarquia de recursos do IAM.

A consulta de uma vista materializada requer acesso à vista, bem como às respetivas tabelas base. Para partilhar uma vista materializada, pode conceder autorizações às tabelas base ou configurar uma vista materializada como uma vista autorizada. Para mais informações, consulte o artigo Vistas autorizadas.

Para controlar o acesso a vistas no BigQuery, consulte o artigo Vistas autorizadas.

Suporte de consultas de vistas materializadas

As vistas materializadas usam uma sintaxe SQL restrita. As consultas têm de usar o seguinte padrão:

[ 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

Limitações das consultas

As vistas materializadas têm as seguintes limitações.

Requisitos agregados

Os agregados na consulta da vista materializada têm de ser resultados. A computação, a filtragem ou a junção com base num valor agregado não são suportadas. Por exemplo, a criação de uma vista a partir da seguinte consulta não é suportada porque produz um valor calculado a partir de um agregado, COUNT(*) / 10 as cnt.

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

Atualmente, apenas são suportadas as seguintes funções de agregação:

• ANY_VALUE (mas não superior a STRUCT)
• APPROX_COUNT_DISTINCT
• ARRAY_AGG (mas não superior a ARRAY nem STRUCT)
• AVG
• BIT_AND
• BIT_OR
• BIT_XOR
• COUNT
• COUNTIF
• HLL_COUNT.INIT
• LOGICAL_AND
• LOGICAL_OR
• MAX
• MIN
• MAX_BY (mas não superior a STRUCT)
• MIN_BY (mas não superior a STRUCT)
• SUM

Funcionalidades SQL não suportadas

As seguintes funcionalidades de SQL não são suportadas em visualizações de propriedades materializadas:

• UNION ALL. (Suporte na science pré-visualização)
• LEFT OUTER JOIN (suporte na sciencepré-visualização)
• RIGHT/FULL OUTER JOIN.
• Junções automáticas, também conhecidas como a utilização de um JOIN na mesma tabela mais do que uma vez.
• Funções de janela.
• ARRAY subconsultas.
• Funções não determinísticas, como RAND(), CURRENT_DATE(), SESSION_USER() ou CURRENT_TIME().
• Funções definidas pelo utilizador (FDUs).
• TABLESAMPLE.
• FOR SYSTEM_TIME AS OF.

Compatibilidade com LEFT OUTER JOIN e UNION ALL

Para pedir feedback ou apoio técnico para esta funcionalidade, envie um email para bq-mv-help @google.com.

As visualizações de propriedades materializadas incrementais suportam LEFT OUTER JOIN e UNION ALL. As vistas materializadas com declarações LEFT OUTER JOIN e UNION ALL partilham as limitações de outras vistas materializadas incrementais. Além disso, a ajustagem inteligente não é suportada para vistas materializadas com union all ou left outer join.

Exemplos

O exemplo seguinte cria uma vista materializada incremental agregada com um LEFT JOIN. Esta vista é atualizada incrementalmente quando os dados são anexados à tabela à esquerda.

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
);

O exemplo seguinte cria uma vista materializada incremental agregada com um UNION ALL. Esta vista é atualizada de forma incremental quando são acrescentados dados a uma ou a ambas as tabelas. Para mais informações sobre as atualizações incrementais, consulte o artigo Atualizações incrementais.

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
);

Restrições de controlo de acesso

• Se a consulta de um utilizador de uma vista materializada incluir colunas da tabela base às quais não consegue aceder devido à segurança ao nível da coluna, a consulta falha com a mensagem Access Denied.
• Se um utilizador consultar uma vista materializada, mas não tiver acesso total a todas as linhas nas tabelas base da vista materializada, o BigQuery executa a consulta nas tabelas base em vez de ler os dados da vista materializada. Isto garante que a consulta respeita todas as restrições de controlo de acesso. Esta limitação também se aplica quando consulta tabelas com colunas com ocultação de dados.

WITH e expressões de tabelas comuns (CTEs)

As vistas materializadas suportam cláusulas WITH e expressões de tabelas comuns. As vistas materializadas com cláusulas WITH continuam a ter de seguir o padrão e as limitações das vistas materializadas sem cláusulas WITH.

Exemplos

O exemplo seguinte mostra uma vista materializada que usa uma cláusula 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;

O exemplo seguinte mostra uma vista materializada que usa uma cláusula WITH que não é suportada porque contém duas cláusulas GROUP BY:

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

Vistas materializadas sobre tabelas do BigLake

Para criar vistas materializadas sobre tabelas BigLake, a tabela BigLake tem de ter a colocação em cache de metadados ativada sobre os dados do Cloud Storage, e a vista materializada tem de ter um valor da opção max_staleness superior ao da tabela base. As visualizações materializadas sobre tabelas do BigLake suportam o mesmo conjunto de consultas que outras visualizações materializadas.

Exemplo

Criação de uma vista agregada simples com uma tabela base do BigLake:

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;

Para ver detalhes sobre as limitações das vistas materializadas sobre tabelas BigLake, consulte o artigo Vistas materializadas sobre tabelas BigLake.

Vistas materializadas sobre tabelas externas do Apache Iceberg

Pode fazer referência a tabelas Iceberg grandes em vistas materializadas em vez de migrar esses dados para o armazenamento gerido pelo BigQuery.

Crie uma vista materializada sobre uma tabela Iceberg

O exemplo seguinte cria uma vista materializada alinhada com a partição numa tabela Iceberg base particionada:

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

A tabela Iceberg subjacente myicebergtabletem de ter uma especificação de partição como a seguinte:

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

Limitações

Além das limitações das tabelas Iceberg padrão, as vistas materializadas sobre tabelas Iceberg têm as seguintes limitações:

• Pode criar uma vista materializada alinhada com a partição da tabela base. No entanto, a vista materializada só suporta a transformação de partições baseada no tempo, por exemplo, YEAR, MONTH, DAY e HOUR.
• A granularidade da partição da vista materializada não pode ser mais detalhada do que a granularidade da partição da tabela base. Por exemplo, se particionar a tabela base anualmente através da coluna birth_date, a criação de uma vista materializada com PARTITION BY DATE_TRUNC(birth_date, MONTH) não é suportada.
• Se as tabelas Iceberg base tiverem alterações em mais de 4000 partições, a vista materializada é totalmente invalidada após a atualização, mesmo que esteja particionada.
• As evoluções das partições são suportadas. No entanto, a alteração das colunas de partição de uma tabela base sem recriar a vista materializada pode resultar numa invalidação total que não pode ser corrigida atualizando a vista materializada.
• Tem de existir, pelo menos, uma imagem instantânea na tabela base.
• A tabela Iceberg tem de ser uma tabela BigLake, por exemplo, uma tabela externa autorizada.
• A consulta sobre a vista materializada pode falhar se o ficheiro metadata.json da sua tabela Iceberg estiver danificado.
• Se os VPC Service Controls estiverem ativados, as contas de serviço da tabela externa autorizada têm de ser adicionadas às regras de entrada. Caso contrário, os VPC Service Controls bloqueiam a atualização automática em segundo plano para a vista materializada.

O ficheiro metadata.json da sua tabela Iceberg tem de ter as seguintes especificações. Sem estas especificações, as suas consultas analisam a tabela base, não usando o resultado materializado.

• Nos metadados da tabela:

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

• Nos instantâneos:

  • parent-snapshot-id (se disponível)
  • schema-id
  • operation (no campo summary)

• Partição (para a vista materializada particionada)

Vistas materializadas particionadas

As vistas materializadas em tabelas particionadas podem ser particionadas. A partição de uma vista materializada é semelhante à partição de uma tabela normal, uma vez que oferece vantagens quando as consultas acedem frequentemente a um subconjunto das partições. Além disso, a partição de uma vista materializada pode melhorar o comportamento da vista quando os dados na tabela ou tabelas base são modificados ou eliminados. Para mais informações, consulte o artigo Alinhamento de partições.

Se a tabela base for particionada, pode particionar uma vista materializada na mesma coluna de partição. Para partições baseadas no tempo, o nível de detalhe tem de corresponder (por hora, por dia, por mês ou por ano). Para partições de intervalo de números inteiros, a especificação do intervalo tem de corresponder exatamente. Não pode particionar uma vista materializada numa tabela base não particionada.

Se a tabela base estiver particionada por tempo de carregamento, uma vista materializada pode agrupar pela coluna _PARTITIONDATE da tabela base e também particioná-la. Se não especificar explicitamente a partição quando cria a vista materializada, esta não é particionada.

Se a tabela base estiver particionada, considere também particionar a vista materializada para reduzir o custo de manutenção da tarefa de atualização e o custo da consulta.

Expiração da partição

Não é possível definir a validade da partição em vistas materializadas. Uma vista materializada herda implicitamente o tempo de validade da partição da tabela base. As partições da vista materializada estão alinhadas com as partições da tabela base, pelo que expiram de forma síncrona.

Exemplo 1

Neste exemplo, a tabela base é particionada na coluna transaction_time com partições diárias. A vista materializada é particionada na mesma coluna e agrupada na coluna 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
);

Exemplo 2

Neste exemplo, a tabela base é particionada por tempo de ingestão com partições diárias. A vista materializada seleciona a hora de carregamento como uma coluna denominada date. A vista materializada é agrupada pela coluna date e particionada pela mesma coluna.

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
);

Exemplo 3

Neste exemplo, a tabela base é particionada numa coluna TIMESTAMP denominada transaction_time, com partições diárias. A vista materializada define uma coluna denominada transaction_hour, usando a função TIMESTAMP_TRUNC para truncar o valor para a hora mais próxima. A vista materializada é agrupada por transaction_hour e também particionada por este.

Tenha em conta o seguinte:

• A função de truncagem aplicada à coluna de partição tem de ser, pelo menos, tão detalhada quanto a partição da tabela base. Por exemplo, se a tabela base usar partições diárias, a função de truncagem não pode usar a granularidade MONTH ou YEAR.

• Na especificação de partição da vista materializada, o nível de detalhe tem de corresponder à tabela 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
);

Vistas materializadas de cluster

Pode agrupar as vistas materializadas pelas respetivas colunas de saída, sujeitas às limitações da tabela agrupada do BigQuery. Não é possível usar colunas de resultados agregados como colunas de agrupamento. A adição de colunas de agrupamento às vistas materializadas pode melhorar o desempenho das consultas que incluem filtros nessas colunas.

Referencie vistas lógicas

Para pedir feedback ou apoio técnico para esta funcionalidade, envie um email para bq-mv-help@google.com.

As consultas de vistas materializadas podem referenciar vistas lógicas, mas estão sujeitas às seguintes limitações:

• Aplicam-se limitações de vistas materializadas.
• Se a vista lógica for alterada, a vista materializada torna-se inválida e tem de ser totalmente atualizada.
• A sintonia inteligente não é suportada.

Considerações ao criar vistas materializadas

Que vistas materializadas criar

Quando criar uma vista materializada, certifique-se de que a definição da vista materializada reflete os padrões de consulta nas tabelas base. As vistas materializadas são mais eficazes quando publicam um conjunto amplo de consultas em vez de apenas um padrão de consulta específico.

Por exemplo, considere uma consulta numa tabela em que os utilizadores filtram frequentemente pelas colunas user_id ou department. Pode agrupar por estas colunas e, opcionalmente, agrupá-las, em vez de adicionar filtros como user_id = 123 à vista materializada.

Como outro exemplo, pode usar filtros de data determinísticos, seja por data específica, como WHERE order_date = '2019-10-01', ou intervalo de datas, como WHERE order_date BETWEEN '2019-10-01' AND '2019-10-31'. Adicione um filtro de intervalo de datas na vista materializada que abranja os intervalos de datas esperados na consulta:

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

Aderir

As seguintes recomendações aplicam-se a vistas materializadas com JOINs.

Coloque primeiro a tabela que muda com maior frequência

Certifique-se de que a tabela maior ou que muda com mais frequência é a primeira/mais à esquerda tabela referenciada na consulta de visualização. As vistas materializadas com junções suportam consultas incrementais e a atualização quando a primeira ou a tabela mais à esquerda na consulta é anexada, mas as alterações a outras tabelas invalidam totalmente a cache de visualização. Nos esquemas de estrela ou floco de neve, a primeira ou a tabela mais à esquerda deve ser geralmente a tabela de factos.

Evite a junção em chaves de agrupamento

As vistas materializadas com junções funcionam melhor nos casos em que os dados estão fortemente agregados ou a consulta de junção original é dispendiosa. Para consultas seletivas, o BigQuery costuma já conseguir realizar a junção de forma eficiente e não é necessária nenhuma visualização materializada. Por exemplo, considere as seguintes definições de vista materializada.

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
);

Suponhamos que store_sales está agrupado em ss_store_sk e que executa frequentemente consultas como as seguintes:

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

A vista materializada pode não ser tão eficiente quanto a consulta original. Para obter os melhores resultados, experimente um conjunto representativo de consultas, com e sem a vista materializada.

Use vistas materializadas com a opção max_staleness

A max_stalenessopção de vista materializada ajuda a alcançar um desempenho de consulta consistentemente elevado com custos controlados quando processa conjuntos de dados grandes e que mudam com frequência. Com o parâmetro max_staleness, pode reduzir o custo e a latência das suas consultas definindo um intervalo de tempo em que a desatualização dos dados dos resultados da consulta é aceitável. Este comportamento pode ser útil para painéis de controlo e relatórios para os quais os resultados da consulta totalmente atualizados não são essenciais.

Obsolecência dos dados

Quando consulta uma vista materializada com a opção max_staleness definida, o BigQuery devolve o resultado com base no valor max_staleness e na hora em que ocorreu a última atualização.

Se a última atualização ocorreu no intervalo de max_staleness, o BigQuery devolve dados diretamente da vista materializada sem ler as tabelas base. Por exemplo, isto aplica-se se o intervalo de max_staleness for de 4 horas e a última atualização tiver ocorrido há 2 horas.

Se a última atualização ocorreu fora do intervalo de max_staleness, o BigQuery lê os dados da vista materializada, combina-os com as alterações à tabela base desde a última atualização e devolve o resultado combinado. Este resultado combinado pode continuar desatualizado até ao seu intervalo de max_staleness atualização. Por exemplo, isto aplica-se se o intervalo max_staleness for de 4 horas e a última atualização tiver ocorrido há 7 horas.

Crie com a opção max_staleness

Selecione uma das seguintes opções:

{
  "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"
}

Aplicar opção max_staleness

Pode aplicar este parâmetro às vistas materializadas existentes através da declaração ALTER MATERIALIZED VIEW. Por exemplo:

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);

Consultar com max_staleness

Pode consultar vistas materializadas com a opção max_staleness, tal como consultaria qualquer outra vista materializada, vista lógica ou tabela.

Por exemplo:

SELECT * FROM  project-id.my_dataset.my_mv_table

Esta consulta devolve dados da atualização mais recente se os dados não forem mais antigos do que o parâmetro max_staleness. Se a vista materializada não tiver sido atualizada no intervalo de max_staleness, o BigQuery une os resultados da atualização mais recente disponível com as alterações da tabela base para devolver resultados no intervalo de max_staleness.

Streaming de dados e resultados do max_staleness

Se transmitir dados para as tabelas base de uma vista materializada com a opção max_staleness, a consulta da vista materializada pode excluir registos que foram transmitidos para as respetivas tabelas antes do início do intervalo de dados desatualizados. Como tal, uma vista materializada que inclua dados de várias tabelas e a opção max_staleness pode não representar uma imagem instantânea dessas tabelas num determinado momento.

Ajuste inteligente e a opção max_staleness

A otimização inteligente reescreve automaticamente as consultas para usar vistas materializadas sempre que possível, independentemente da opção max_staleness, mesmo que a consulta não faça referência a uma vista materializada. A opção max_staleness numa vista materializada não afeta os resultados da consulta reescrita. A opção max_staleness só afeta as consultas que consultam diretamente a vista materializada.

Faça a gestão da obsolescência e da frequência de atualização

Deve definir max_staleness com base nos seus requisitos. Para evitar a leitura de dados das tabelas base, configure o intervalo de atualização para que a atualização ocorra dentro do intervalo de desatualização. Pode ter em conta o tempo de execução da atualização médio, além de uma margem para o crescimento.

Por exemplo, se for necessária uma hora para atualizar a vista materializada e quiser um intervalo de uma hora para o crescimento, deve definir o intervalo de atualização para duas horas. Esta configuração garante que a atualização ocorre no prazo máximo de quatro horas de desatualização do relatório.

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;

Vistas materializadas não incrementais

As vistas materializadas não incrementais suportam a maioria das consultas SQL, incluindo as cláusulas OUTER JOIN, UNION e HAVING, e as funções analíticas. Para determinar se foi usada uma vista materializada na sua consulta, verifique as estimativas de custos através de um teste de execução. Em cenários em que a obsolescência dos dados é aceitável, por exemplo, para o processamento de dados em lote ou a criação de relatórios, as vistas materializadas não incrementais podem melhorar o desempenho das consultas e reduzir o custo. Ao usar a opção max_staleness, pode criar vistas materializadas arbitrárias e complexas que são mantidas automaticamente e têm garantias de desatualização incorporadas.

Use vistas materializadas não incrementais

Pode criar vistas materializadas não incrementais usando a opção allow_non_incremental_definition. Esta opção tem de ser acompanhada pela opção max_staleness. Para garantir uma atualização periódica da vista materializada, também deve configurar uma política de atualização. Sem uma política de atualização, tem de atualizar manualmente a vista materializada.

A vista materializada representa sempre o estado das tabelas de base no intervalo max_staleness. Se a última atualização estiver demasiado desatualizada e não representar as tabelas base no intervalo max_staleness, a consulta lê as tabelas base. Para saber mais sobre as possíveis implicações no desempenho, consulte o artigo Obsolecência dos dados.

Crie com allow_non_incremental_definition

Para criar uma vista materializada com a opção allow_non_incremental_definition, siga estes passos. Depois de criar a vista materializada, não pode modificar a opção allow_non_incremental_definition. Por exemplo, não pode alterar o valor true para false nem remover a opção allow_non_incremental_definition da vista materializada.

{
  "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"
}

Consultar com allow_non_incremental_definition

Pode consultar vistas materializadas não incrementais como consultaria qualquer outra vista materializada, vista lógica ou tabela.

Por exemplo:

SELECT * FROM  my_project.my_dataset.my_mv_table

Se os dados não forem mais antigos do que o parâmetro max_staleness, esta consulta devolve dados da atualização mais recente. Para ver detalhes sobre a desatualização e a atualidade dos dados, consulte o artigo Desatualização dos dados.

Limitações específicas das vistas materializadas não incrementais

As seguintes limitações aplicam-se apenas às vistas materializadas com a opção allow_non_incremental_definition. Com exceção das limitações na sintaxe de consulta suportada, todas as limitações da vista materializada continuam a aplicar-se.

• A otimização inteligente não é aplicada às vistas materializadas que incluem a opção allow_non_incremental_definition. A única forma de beneficiar das vistas materializadas com a opção allow_non_incremental_definition é consultá-las diretamente.
• As vistas materializadas sem a opção allow_non_incremental_definition podem atualizar incrementalmente um subconjunto dos respetivos dados. As vistas materializadas com a opção allow_non_incremental_definition têm de ser atualizadas na sua totalidade.
• As vistas materializadas com a opção max_staleness validam a presença das restrições de segurança ao nível da coluna durante a execução da consulta. Veja mais detalhes acerca deste aspeto no controlo de acesso ao nível da coluna

O que se segue?

• Faça a gestão das vistas materializadas.
• Use vistas materializadas.