Criar visualizações materializadas

Neste documento, descrevemos como criar visualizações no BigQuery. Antes de ler este documento, veja a Introdução às visualizações materializadas.

Antes de começar

Atribua papéis do Identity and Access Management (IAM) que concedam aos usuários as permissões necessárias para realizar cada tarefa deste documento.

Permissões necessárias

Para criar visualizações materializadas, você precisa da permissão bigquery.tables.create do IAM.

Cada um dos seguintes papéis predefinidos do IAM inclui as permissões necessárias para criar uma visualização materializada:

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

Para mais informações sobre o gerenciamento de identidade e acesso (IAM) do BigQuery, consulte Controle de acesso com o IAM.

Criar visualizações materializadas

Para criar uma visualização materializada, selecione uma das seguintes opções:

SQL

Use a instrução CREATE MATERIALIZED VIEW. O exemplo a seguir cria uma visualização materializada para o número de cliques para cada ID do produto:

  1. No Console do Google Cloud, acesse a página BigQuery.

    Ir para o BigQuery

  2. No editor de consultas, digite a seguinte instrução:

    CREATE MATERIALIZED VIEW PROJECT_ID.DATASET.MATERIALIZED_VIEW_NAME AS (
  QUERY_EXPRESSION
);

    Substitua:

    • PROJECT_ID: o nome do projeto em que você quer criar a visualização materializada. Por exemplo, myproject.
    • DATASET: o nome do conjunto de dados do BigQuery em que você quer criar a visualização materializada. Por exemplo, mydataset. Ao criar uma visualização materializada em uma tabela do BigLake no Amazon Simple Storage Service (Amazon S3) (pré-lançamento), verifique se o conjunto de dados está em uma região com suporte.
    • MATERIALIZED_VIEW_NAME: o nome da visualização materializada que você quer criar. Por exemplo, my_mv.
    • QUERY_EXPRESSION: a expressão de consulta do GoogleSQL que define a visualização materializada. Por exemplo, SELECT product_id, SUM(clicks) AS sum_clicks FROM mydataset.my_source_table.

  3. Clique em Executar.

Para mais informações sobre como executar consultas, acesse Executar uma consulta interativa.

Exemplo

O exemplo a seguir cria uma visualização materializada para o número de cliques para cada ID do produto:

CREATE MATERIALIZED VIEW myproject.mydataset.my_mv_table AS (
  SELECT
    product_id,
    SUM(clicks) AS sum_clicks
  FROM
    myproject.mydataset.my_base_table
  GROUP BY
    product_id
);

Terraform

Use o recurso google_bigquery_table.

Para autenticar no BigQuery, configure o Application Default Credentials. Para mais informações, acesse Configurar a autenticação para bibliotecas de cliente.

No exemplo a seguir, criamos uma visualização chamada my_materialized_view:

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

}

Para aplicar a configuração do Terraform em um projeto do Google Cloud, conclua as etapas nas seções a seguir.

Preparar o Cloud Shell

  1. Inicie o Cloud Shell.

  2. Defina o projeto padrão do Google Cloud em que você quer aplicar as configurações do Terraform.

    Você só precisa executar esse comando uma vez por projeto, e ele pode ser executado em qualquer diretório.

    export GOOGLE_CLOUD_PROJECT=PROJECT_ID

    As variáveis de ambiente serão substituídas se você definir valores explícitos no arquivo de configuração do Terraform.

Preparar o diretório

Cada arquivo de configuração do Terraform precisa ter o próprio diretório, também chamado de módulo raiz.

  1. No Cloud Shell, crie um diretório e um novo arquivo dentro dele. O nome do arquivo precisa ter a extensão .tf, por exemplo, main.tf. Neste tutorial, o arquivo é chamado de main.tf. 
    mkdir DIRECTORY && cd DIRECTORY && touch main.tf

  2. Se você estiver seguindo um tutorial, poderá copiar o exemplo de código em cada seção ou etapa.

    Copie o exemplo de código no main.tf recém-criado.

    Se preferir, copie o código do GitHub. Isso é recomendado quando o snippet do Terraform faz parte de uma solução de ponta a ponta.

  3. Revise e modifique os parâmetros de amostra para aplicar ao seu ambiente.
  4. Salve as alterações.
  5. Inicialize o Terraform. Você só precisa fazer isso uma vez por diretório. 
    terraform init

    Opcionalmente, para usar a versão mais recente do provedor do Google, inclua a opção -upgrade: 

    terraform init -upgrade

Aplique as alterações

  1. Revise a configuração e verifique se os recursos que o Terraform vai criar ou atualizar correspondem às suas expectativas: 
    terraform plan

    Faça as correções necessárias na configuração.

  2. Para aplicar a configuração do Terraform, execute o comando a seguir e digite yes no prompt: 
    terraform apply

    Aguarde até que o Terraform exiba a mensagem "Apply complete!".

  3. Abra seu projeto do Google Cloud para ver os resultados. No console do Google Cloud, navegue até seus recursos na IU para verificar se foram criados ou atualizados pelo Terraform.

API

Chame o método tables.insert e transmita um recurso Table com um campo materializedView definido:

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

Substitua:

  • PROJECT_ID: o nome do projeto em que você quer criar a visualização materializada. Por exemplo, myproject.
  • DATASET: o nome do conjunto de dados do BigQuery em que você quer criar a visualização materializada. Por exemplo, mydataset. Ao criar uma visualização materializada em uma tabela do BigLake no Amazon Simple Storage Service (Amazon S3) (pré-lançamento), verifique se o conjunto de dados está em uma região com suporte.
  • MATERIALIZED_VIEW_NAME: o nome da visualização materializada que você quer criar. Por exemplo, my_mv.
  • QUERY_EXPRESSION: a expressão de consulta do GoogleSQL que define a visualização materializada. Por exemplo, SELECT product_id, SUM(clicks) AS sum_clicks FROM mydataset.my_source_table.

Exemplo

O exemplo a seguir cria uma visualização materializada para o número de cliques para cada ID do produto:

{
  "kind": "bigquery#table",
  "tableReference": {
    "projectId": "myproject",
    "datasetId": "mydataset",
    "tableId": "my_mv"
  },
  "materializedView": {
    "query": "select product_id,sum(clicks) as
                sum_clicks from myproject.mydataset.my_source_table
                group by 1"
  }
}

Java

Antes de testar esta amostra, siga as instruções de configuração do Java no Guia de início rápido do BigQuery: como usar bibliotecas de cliente. Para mais informações, consulte a documentação de referência da API BigQuery em Java.

Para autenticar no BigQuery, configure o Application Default Credentials. Para mais informações, acesse Configurar a autenticação para bibliotecas de cliente. 

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

Quando a visualização materializada é criada, ela aparece no painel Explorer do BigQuery no console do Google Cloud. O exemplo abaixo mostra um esquema de visualização materializada:

Esquema de visualização materializada no Console do Google Cloud

A menos que você desative as atualizações automáticas, o BigQuery iniciará uma atualização assíncrona completa para a visualização materializada. A consulta é concluída rapidamente, mas a atualização inicial pode continuar em execução.

Controle de acesso

É possível conceder acesso a uma visualização materializada no nível do conjunto de dados, no nível da visualização ou no nível da coluna. Também é possível definir o acesso em um nível superior na hierarquia de recursos do IAM.

Consultar uma visualização materializada requer acesso à visualização, bem como às tabelas base dela. Para compartilhar uma visualização materializada, conceda permissões às tabelas base ou configure uma visualização materializada como uma visualização autorizada. Para mais informações, consulte Visualizações autorizadas.

Para controlar o acesso a visualizações no BigQuery, consulte Visualizações autorizadas.

Compatibilidade com consultas de visualizações materializadas

As visualizações materializadas usam uma sintaxe SQL restrita. As consultas precisam 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 de consulta

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

Requisitos agregados

Os agregados na consulta de visualização materializada precisam ser saídas. Não há suporte para computação, filtragem ou mesclagem com base em um valor agregado. Por exemplo, não é possível criar uma visualização pela consulta a seguir porque ela produz um valor calculado com base em 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;

No momento, apenas as seguintes funções de agregação são compatíveis:

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

Recursos do SQL incompatíveis

Os seguintes recursos do SQL não são compatíveis com as visualizações materializadas:

Compatibilidade de LEFT OUTER JOIN e UNION ALL

Para solicitar feedback ou suporte para esse recurso, envie um e-mail para bq-mv-help@google.com.

Visualizações materializadas incrementais são compatíveis com LEFT OUTER JOIN e UNION ALL. As visualizações materializadas com instruções LEFT OUTER JOIN e UNION ALL compartilham as limitações de outras visualizações materializadas incrementais. Além disso, o ajuste inteligente não é compatível com visualizações materializadas com união total ou junção externa à esquerda.

Exemplos

O exemplo a seguir cria uma visualização materializada incremental agregada com um LEFT JOIN. Essa visualização é atualizada gradualmente 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 a seguir cria uma visualização materializada incremental agregada com um UNION ALL. Essa visualização é atualizada gradualmente quando os dados são anexados a uma ou a ambas as tabelas. Para mais informações sobre atualizações incrementais, consulte 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 controle de acesso

  • Se a consulta de uma visualização materializada de um usuário incluir colunas de tabela de base que não podem ser acessadas devido à segurança no nível da coluna, a consulta falhará com a mensagem Access Denied.
  • Se um usuário consulta uma visualização materializada, mas não tem acesso total a todas as linhas das tabelas de base das visualizações materializadas, o BigQuery executa a consulta nas tabelas de base em vez de ler dados de visualizações materializadas. Isso garante que a consulta respeite todas as restrições de controle de acesso. Essa limitação também se aplica à consulta de tabelas com colunas com máscara de dados.

Cláusulas WITH e expressões comuns da tabela (CTEs, na sigla em inglês)

As visualizações materializadas são compatíveis com cláusulas WITH e expressões comuns de tabelas. As visualizações materializadas com cláusulas WITH ainda precisam seguir o padrão e as limitações das visualizações materializadas sem cláusulas WITH.

Exemplos

O exemplo a seguir mostra uma visualização materializada usando 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 a seguir mostra uma visualização materializada usando uma cláusula WITH que não é compatível 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;

Visualizações materializadas sobre tabelas do BigLake

Para criar visualizações materializadas sobre as tabelas do BigLake, a tabela do BigLake precisa ter o cache de metadados ativado sobre os dados do Cloud Storage e a visualização materializada precisa ter um valor de opção max_staleness maior do que a tabela base. As visualizações materializadas em tabelas do BigLake aceitam o mesmo conjunto de consultas que outras visualizações materializadas.

Exemplo

Criação de uma visualização agregada simples usando 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 detalhes sobre as limitações das visualizações materializadas sobre as tabelas do BigLake, consulte visualizações materializadas sobre tabelas do BigLake.

Visualizações materializadas sobre tabelas do Apache Iceberg

Para solicitar feedback ou suporte para esse recurso, envie um e-mail para bq-mv-help@google.com.

É possível referenciar grandes tabelas do Iceberg em visualizações materializadas em vez de migrar esses dados para o armazenamento gerenciado do BigQuery.

Criar uma visualização materializada sobre uma tabela do Iceberg

Para criar uma visualização materializada de um Iceberg, siga estas etapas:

  1. Extraia uma tabela do Iceberg usando um dos seguintes métodos:

    Exemplo

    CREATE EXTERNAL TABLE mydataset.myicebergtable
  WITH CONNECTION `myproject.us.myconnection`
  OPTIONS (
        format = 'ICEBERG',
        uris = ["gs://mybucket/mydata/mytable/metadata/iceberg.metadata.json"]
  )

  2. Consulte sua tabela Iceberg com as seguintes especificações de partição:

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

  3. Crie uma visualização materializada alinhada à partição:

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

Limitações

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

  • É possível criar uma visualização materializada alinhada à partição da tabela base. No entanto, a visualização materializada só oferece suporte à transformação de partição baseada em tempo, por exemplo, YEAR, MONTH, DAY e HOUR.
  • A granularidade da partição da visualização materializada não pode ser menor do que a granularidade da partição da tabela base. Por exemplo, se você particionar a tabela base anualmente usando a coluna birth_date, a criação de uma visualização materializada com PARTITION BY DATE_TRUNC(birth_date, MONTH) não vai funcionar.
  • Qualquer mudança de esquema invalida a visualização materializada.
  • As evoluções de partição têm suporte. No entanto, alterar as colunas de particionamento de uma tabela base sem recriar a visualização materializada pode resultar em uma invalidação completa que não pode ser corrigida com a atualização.
  • É preciso que haja pelo menos um snapshot na tabela base.
  • A tabela do Iceberg precisa ser uma tabela do BigLake, por exemplo, uma tabela externa autorizada.
  • Se o VPC Service Controls estiver ativado, as contas de serviço da tabela externa autorizada precisarão ser adicionadas às regras de entrada. Caso contrário, o VPC Service Controls bloqueará a atualização automática em segundo plano da visualização materializada.

O arquivo metadata.json da tabela Iceberg precisa ter as especificações a seguir. Sem essas especificações, suas consultas verificam a tabela base, e falham ao usar o resultado materializado.

  • Nos metadados da tabela:

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

  • Em snapshots:

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

  • Particionamento (para a visualização materializada particionada)

Visualizações materializadas particionadas

As visualizações materializadas em tabelas particionadas podem ser particionadas. O particionamento de uma visualização materializada é semelhante ao particionamento de uma tabela normal, visto que é vantajoso quando as consultas costumam acessar um subconjunto das partições. Além disso, particionar uma visualização materializada pode melhorar o comportamento da visualização quando os dados na tabela ou tabelas base forem modificados ou excluídos. Para mais informações, consulte Alinhamento de partição.

Se a tabela base for particionada, particione uma visualização materializada na mesma coluna de particionamento. Para partições baseadas em tempo, a granularidade precisa corresponder (por hora, dia, mês ou ano). Para partições por intervalo de números inteiros, a especificação do intervalo precisa corresponder exatamente. Não é possível particionar uma visualização materializada em uma tabela base não particionada.

Se a tabela base é particionada por tempo de processamento, uma visualização materializada pode ser agrupada pela coluna _PARTITIONDATE da tabela base e também particionada por ela. Se você não especificar explicitamente o particionamento ao criar a visualização materializada, ela não será particionada.

Se a tabela base for particionada, considere particionar sua visualização materializada para reduzir os custos de atualização do job de atualização e o custo de consulta.

Expiração da partição

Nas visualizações materializadas, a expiração da partição não pode ser configurada. Uma visualização materializada herda implicitamente o prazo de validade da partição da tabela base. As partições de visualização materializadas são alinhadas com as partições de tabela base, por isso expiram de forma síncrona.

Exemplo 1

Neste exemplo, a tabela base é particionada na coluna transaction_time com partições diárias. A visualização 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 processamento com partições diárias. A visualização materializada seleciona o tempo de ingestão como uma coluna chamada date. A visualização 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 em uma coluna TIMESTAMP chamada transaction_time, com partições diárias. A visualização materializada define uma coluna chamada transaction_hour, usando a função TIMESTAMP_TRUNC para truncar o valor para a hora mais próxima. A visualização materializada é agrupada por transaction_hour e também particionada por ela.

Observações:

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

  • Na especificação da partição da visualização materializada, a granularidade precisa 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
);

Visualizações materializadas do cluster

É possível agrupar visualizações materializadas por colunas de saída, sujeitas às limitações de tabelas em cluster do BigQuery. Não é possível usar colunas de saída agregadas como colunas de clustering. Adicionar colunas de clustering a visualizações materializadas pode melhorar o desempenho das consultas que incluem filtros nessas colunas.

Visualizações lógicas de referência

Para solicitar feedback ou suporte para esse recurso, envie um e-mail para bq-mv-help@google.com.

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

Considerações ao criar visualizações materializadas

Quais visualizações materializadas serão criadas

Ao criar uma visualização materializada, verifique se a definição de visualização materializada reflete os padrões de consulta nas tabelas base. Como há no máximo 20 visualizações materializadas por tabela, não crie uma visualização materializada para cada troca de uma consulta. Em vez disso, crie visualizações materializadas para veicular um conjunto mais amplo de consultas.

Por exemplo, considere uma consulta em uma tabela em que os usuários geralmente filtram pelas colunas user_id ou department. É possível agrupar por essas colunas e, opcionalmente, realizar o clustering por elas, em vez de adicionar filtros como user_id = 123 à visualização materializada.

Como outro exemplo, os usuários geralmente usam filtros de data, por data específica, como WHERE order_date = CURRENT_DATE(), ou por período, como WHERE order_date BETWEEN '2019-10-01' AND '2019-10-31'. Adicione um filtro por período à visualização materializada que abrange os períodos esperados na consulta:

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

Mesclagens

As recomendações a seguir se aplicam a visualizações materializadas com JOINs.

Colocar primeiro a tabela que muda com mais frequência

Garanta que a maior tabela ou a mais alterada seja a primeira/mais à esquerda referenciada na consulta de visualização. As visualizações materializadas com junções são compatíveis com consultas incrementais e atualizadas quando a primeira tabela ou a mais à esquerda na consulta é anexada, mas alterações em outras tabelas invalidam totalmente o cache da visualização. Em esquemas em estrela ou floco de neve, a primeira tabela ou a tabela mais à esquerda geralmente precisa ser a tabela de fatos.

Evitar mesclagem em chaves de clustering

As visualizações materializadas com mesclas funcionam melhor nos casos em que os dados são muito agregados ou a consulta de mescla original é cara. Para consultas seletivas, o BigQuery, em geral, já é capaz de executar a mescla com eficiência e nenhuma visualização materializada é necessária. Por exemplo, considere as seguintes definições de visualização 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
);

Suponha que store_sales esteja em cluster em ss_store_sk e você geralmente execute consultas como esta:

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 visualização materializada pode não ser tão eficiente quanto a consulta original. Para melhores resultados, execute um conjunto representativo de consultas, com e sem a visualização materializada.

Usar visualizações materializadas com a opção max_staleness

A opção de visualização materializada max_staleness ajuda você a ter um desempenho de consulta consistentemente alto com custos controlados ao processar grandes conjuntos de dados que mudam com frequência. Com o parâmetro max_staleness, é possível reduzir o custo e a latência nas consultas definindo um intervalo de tempo em que a obsolescência dos dados dos resultados da consulta é aceitável. Esse comportamento pode ser útil para painéis e relatórios em que os resultados da consulta totalmente atualizados não são essenciais.

Inatividade dos dados

Quando você consulta uma visualização materializada com a opção max_staleness definida, o BigQuery retorna o resultado com base no valor de max_staleness e no horário em que a última atualização ocorreu.

Se a última atualização estiver no intervalo max_staleness, o BigQuery retornará dados diretamente da visualização materializada sem ler as tabelas base. Por exemplo, isso se aplica 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 max_staleness, o BigQuery vai ler os dados da visualização materializada, combiná-los com as mudanças na tabela base desde a última atualização e retornar o resultado combinado. Esse resultado combinado pode ainda estar desatualizado, até o intervalo max_staleness. Por exemplo, isso se aplica se o intervalo de max_staleness for de 4 horas e a última atualização tiver ocorrido há 7 horas.

Criar com a opção max_staleness

Selecione uma das seguintes opções:

SQL

Para criar uma visualização materializada com a opção max_staleness, adicione uma cláusula OPTIONS à instrução DDL ao criar a visualização materializada:

  1. No Console do Google Cloud, acesse a página BigQuery.

    Ir para o BigQuery

  2. No editor de consultas, digite a seguinte instrução:

    CREATE MATERIALIZED VIEW  project-id.my_dataset.my_mv_table
  OPTIONS (enable_refresh = true, refresh_interval_minutes = 60,
    max_staleness = INTERVAL "4:0:0" HOUR TO SECOND)
AS SELECT
  employee_id,
  DATE(transaction_time),
  COUNT(1) AS count
FROM my_dataset.my_base_table
GROUP BY 1, 2;

    Substitua:

    • project-id é o ID do projeto.
    • my_dataset é o id de um conjunto de dados no projeto;
    • my_mv_table é o id da visualização materializada que você está criando;
    • my_base_table é o id de uma tabela no conjunto de dados que serve como a tabela base para a visualização materializada;

    • Clique em Executar.

Para mais informações sobre como executar consultas, acesse Executar uma consulta interativa.

API

Chame o método tables.insert com um recurso materializedView definido como parte da sua solicitação de API. O recurso materializedView contém um campo query. Exemplo:

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

Substitua:

  • project-id é o ID do projeto.
  • my_dataset é o id de um conjunto de dados no projeto;
  • my_mv_table é o id da visualização materializada que você está criando;
  • my_base_table é o id de uma tabela no conjunto de dados que serve como a tabela base para a visualização materializada;
  • product_id é uma coluna da tabela base;
  • clicks é uma coluna da tabela base;
  • sum_clicks é uma coluna na visualização materializada que você está criando.

Aplicar opção max_staleness

É possível aplicar esse parâmetro às visualizações materializadas atuais usando a instrução ALTER MATERIALIZED VIEW. 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

É possível consultar visualizações materializadas com a opção max_staleness, como você consultaria qualquer visualização materializada, visualização lógica ou tabela.

Exemplo:

SELECT * FROM  project-id.my_dataset.my_mv_table

Essa consulta retorna dados da última atualização se eles não forem mais antigos que o parâmetro max_staleness. Se a visualização materializada não tiver sido atualizada no intervalo max_staleness, o BigQuery vai mesclar os resultados da atualização mais recente disponível com as mudanças na tabela base para retornar resultados dentro do intervalo max_staleness.

Streaming de dados e resultados da max_staleness

Se você transmitir dados para as tabelas base de uma visualização materializada com a opção max_staleness, a consulta da visualização materializada poderá excluir registros que foram transmitidos para as tabelas antes do início do intervalo de inatividade. Como resultado, uma visualização materializada que inclui dados de várias tabelas e a opção max_staleness pode não representar um snapshot pontual dessas tabelas.

Ajuste inteligente e a opção max_staleness

O ajuste inteligente reescreve automaticamente as consultas para usar visualizações materializadas sempre que possível, independentemente da opção max_staleness, mesmo que a consulta não faça referência a uma visualização materializada. A opção max_staleness em uma visualização materializada não afeta os resultados da consulta reescrita. A opção max_staleness afeta apenas as consultas que consultam diretamente a visualização materializada.

Gerenciar a inatividade e atualizar a frequência

Defina 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 no intervalo de inatividade. É possível considerar o tempo de execução médio de atualização além de uma margem para crescimento.

Por exemplo, se uma hora for necessária para atualizar a visualização materializada e você quiser um buffer de uma hora para crescimento, defina o intervalo de atualização como duas horas. Essa configuração garante que a atualização ocorra dentro do máximo de quatro horas do seu relatório para inatividade.

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;

Visualizações materializadas não incrementais

As visualizações materializadas não incrementais são compatíveis com a maioria das consultas SQL, incluindo cláusulas OUTER JOIN, UNION e HAVING e funções analíticas. Para determinar se uma visualização materializada foi usada na sua consulta, verifique as estimativas de custo usando uma simulação. Em cenários em que a inatividade de dados é aceitável, por exemplo, para processamento de dados em lote, as visualizações materializadas não incrementais podem melhorar o desempenho da consulta e reduzir o custo. Ao usar a opção max_staleness, é possível criar visualizações materializadas arbitrárias e complexas que são mantidas automaticamente e têm garantias de desatualização integradas.

Usar visualizações materializadas não incrementais

É possível criar visualizações materializadas não incrementais usando a opção allow_non_incremental_definition. Essa opção precisa ser acompanhada pela opção max_staleness. Para garantir uma atualização periódica da visualização materializada, configure também uma política de atualização. Sem uma política, você precisa atualizar manualmente a visualização materializada.

A visualização materializada sempre representa o estado das tabelas base no intervalo max_staleness. Se a última atualização estiver muito desatualizada e não representar as tabelas base no intervalo max_staleness, a consulta lerá as tabelas base. Para saber mais sobre possíveis implicações de desempenho, consulte Inatividade de dados.

Criar usando allow_non_incremental_definition

Para criar uma visualização materializada com a opção allow_non_incremental_definition, siga estas etapas. Depois de criar a visualização materializada, não é possível modificar a opção allow_non_incremental_definition. Por exemplo, não é possível mudar o valor true para false nem remover a opção allow_non_incremental_definition da visualização materializada.

SQL

Adicione uma cláusula OPTIONS à instrução DDL ao criar a visualização materializada:

  1. No Console do Google Cloud, acesse a página BigQuery.

    Ir para o BigQuery

  2. No editor de consultas, digite a seguinte instrução:

    CREATE MATERIALIZED VIEW my_project.my_dataset.my_mv_table
OPTIONS (
  enable_refresh = true, refresh_interval_minutes = 60,
  max_staleness = INTERVAL "4" HOUR,
    allow_non_incremental_definition = true)
AS SELECT
  s_store_sk,
  SUM(ss_net_paid) AS sum_sales,
  APPROX_QUANTILES(ss_net_paid, 2)[safe_offset(1)] median
FROM my_project.my_dataset.store
LEFT OUTER JOIN my_project.my_dataset.store_sales
  ON ss_store_sk = s_store_sk
GROUP BY s_store_sk
HAVING median < 40 OR median is NULL ;

    Substitua:

    • my_project é o ID do projeto.
    • my_dataset é o id de um conjunto de dados no projeto;
    • my_mv_table é o id da visualização materializada que você está criando;
    • my_dataset.store e my_dataset.store_sales são os IDs das tabelas no conjunto de dados que servem como as tabelas base para a visualização materializada.

  3. Clique em Executar.

Para mais informações sobre como executar consultas, acesse Executar uma consulta interativa.

API

Chame o método tables.insert com um recurso materializedView definido como parte da sua solicitação de API. O recurso materializedView contém um campo query. Exemplo:

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

Substitua:

  • my_project é o ID do projeto.
  • my_dataset é o id de um conjunto de dados no projeto;
  • my_mv_table é o id da visualização materializada que você está criando;
  • my_dataset.store e my_dataset.store_sales são os IDs das tabelas no conjunto de dados que servem como as tabelas base para a visualização materializada.

Consultar com allow_non_incremental_definition

É possível consultar visualizações materializadas não incrementais como qualquer outra visualização materializada, visualização lógica ou tabela.

Exemplo:

SELECT * FROM  my_project.my_dataset.my_mv_table

Se os dados não forem mais antigos que o parâmetro max_staleness, essa consulta retornará dados da última atualização. Para detalhes sobre a inatividade e a atualização dos dados, consulte Inatividade de dados.

Limitações específicas para visualizações materializadas não incrementais

As limitações a seguir se aplicam apenas a visualizações materializadas com a opção allow_non_incremental_definition. Com exceção das limitações da sintaxe de consulta com suporte, todas as limitações de visualização materializadas ainda se aplicam.

  • O ajuste inteligente não é aplicado às visualizações materializadas que incluem a opção allow_non_incremental_definition. A única maneira de se beneficiar das visualizações materializadas com a opção allow_non_incremental_definition é consultá-las diretamente.
  • As visualizações materializadas sem a opção allow_non_incremental_definition podem atualizar de maneira incremental um subconjunto dos dados. As visualizações materializadas com a opção allow_non_incremental_definition precisam ser atualizadas totalmente.
  • As visualizações materializadas com a opção max_staleness validam a presença das restrições de segurança no nível da coluna durante a execução da consulta. Conheça mais detalhes sobre isso em controle de acesso no nível da coluna

A seguir