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

É possível criar visualizações materializadas do BigQuery por meio do Console do Google Cloud, da ferramenta de linha de comando bq ou da API do BigQuery.

Para os exemplos a seguir, suponha que sua tabela base se chame my_base_table e tenha o seguinte esquema:

Nome da coluna Tipo
product_id integer
clicks integer

Suponha também que você queira uma visualização materializada que resuma o número de cliques por product_id. As etapas a seguir criam uma visualização materializada chamada my_mv_table que tem o esquema abaixo.

Nome da coluna Tipo
product_id integer
sum_clicks integer

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, acesse a página do BigQuery:

    Ir para o BigQuery

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

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

  3. Clique em Executar.

Para informações sobre como executar consultas, consulte Como executar consultas interativas.

bq

Use o comando bq query e forneça a instrução DDL como o parâmetro de consulta.

bq query --use_legacy_sql=false '
CREATE MATERIALIZED VIEW project-id.my_dataset.my_mv_table
AS SELECT product_id, SUM(clicks) AS sum_clicks
FROM project-id.my_dataset.my_base_table
GROUP BY 1'

em que:

  • 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.

Como alternativa, é possível usar o comando bq mk com o argumento --materialized_view para criar uma visualização materializada. Os argumentos a seguir funcionam com o argumento --materialized_view:

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

em que:

  • 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.

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.

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. As visualizações materializadas são listadas no painel do Explorer no Console do Cloud. Como exemplo, veja a seguir como o esquema de uma visualização materializada é exibido:

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 completa assíncrona para a visualização materializada. A consulta será concluída rapidamente, mas a atualização inicial poderá 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 às visualizações no BigQuery, consulte Como controlar o acesso às visualizações.

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 é possível computar ou filtrar 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
  • SUM

Recursos do SQL incompatíveis

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

  • Junções externas esquerda/direita/completas.
  • Mesclagens automáticas (com a mesma tabela mais de uma vez).
  • Funções analíticas
  • Funções não determinísticas como RAND(), CURRENT_DATE() ou CURRENT_TIME().
  • funções definidas pelo usuário (UDFs)
  • TABLESAMPLE.
  • FOR SYSTEM_TIME AS OF.

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.

Examples

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 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.

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.

Próximas etapas