Consultar tabelas em cluster

A filtragem em colunas em cluster permite melhorar o desempenho e reduzir os custos ao consultar tabelas em cluster no BigQuery. Quando uma consulta filtra uma ou mais colunas em cluster, o BigQuery usa as informações de clustering para verificar apenas os blocos relevantes, um processo chamado remoção de blocos.

Neste documento, descrevemos as permissões e as práticas recomendadas para consultar tabelas em cluster, incluindo por que a ordem das colunas em cluster nos filtros é importante. Só é possível consultar tabelas em cluster usando o GoogleSQL.

Permissões necessárias

Para executar um job de consulta, é necessário ter a permissão bigquery.jobs.create do gerenciamento de identidade e acesso (IAM) no projeto que executa o job de consulta.

Cada um dos seguintes papéis predefinidos do IAM inclui as permissões necessárias para executar um job de consulta:

  • roles/bigquery.admin
  • roles/bigquery.jobUser
  • roles/bigquery.user

Você também precisa da permissão bigquery.tables.getData em todas as tabelas e visualizações referenciadas pela consulta. Além disso, ao consultar uma visualização, você precisa dessa permissão em todas as tabelas e visualizações subjacentes. No entanto, se você usa visualizações autorizadas ou conjuntos de dados autorizados, não precisa de acesso aos dados de origem subjacentes.

Cada um dos papéis predefinidos do IAM a seguir inclui a permissão necessária em todas as tabelas e visualizações referenciadas pela consulta:

  • roles/bigquery.admin
  • roles/bigquery.dataOwner
  • roles/bigquery.dataEditor
  • roles/bigquery.dataViewer

Para mais informações sobre os papéis do IAM no BigQuery, consulte Papéis e permissões predefinidos.

Consultar uma tabela em cluster

Para consultar tabelas em cluster:

  • Como usar o console do Google Cloud
  • use o comando bq query na ferramenta de linha de comando bq;
  • chame o método jobs.insert e configure um job de consulta;
  • Como usar bibliotecas de cliente

Go

Antes de testar esta amostra, siga as instruções de configuração do Go 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 Go.

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

import (
	"context"
	"fmt"
	"io"

	"cloud.google.com/go/bigquery"
	"google.golang.org/api/iterator"
)

// queryClusteredTable demonstrates querying a table that has a clustering specification.
func queryClusteredTable(w io.Writer, projectID, datasetID, tableID string) error {
	// projectID := "my-project-id"
	// datasetID := "mydataset"
	// tableID := "mytable"
	ctx := context.Background()
	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %v", err)
	}
	defer client.Close()

	q := client.Query(fmt.Sprintf(`
	SELECT
	  COUNT(1) as transactions,
	  SUM(amount) as total_paid,
	  COUNT(DISTINCT destination) as distinct_recipients
    FROM
	  `+"`%s.%s`"+`
	 WHERE
	    timestamp > TIMESTAMP('2015-01-01')
		AND origin = @wallet`, datasetID, tableID))
	q.Parameters = []bigquery.QueryParameter{
		{
			Name:  "wallet",
			Value: "wallet00001866cb7e0f09a890",
		},
	}
	// Run the query and print results when the query job is completed.
	job, err := q.Run(ctx)
	if err != nil {
		return err
	}
	status, err := job.Wait(ctx)
	if err != nil {
		return err
	}
	if err := status.Err(); err != nil {
		return err
	}
	it, err := job.Read(ctx)
	for {
		var row []bigquery.Value
		err := it.Next(&row)
		if err == iterator.Done {
			break
		}
		if err != nil {
			return err
		}
		fmt.Fprintln(w, row)
	}
	return nil
}

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.QueryJobConfiguration;
import com.google.cloud.bigquery.TableResult;

public class QueryClusteredTable {

  public static void runQueryClusteredTable() throws Exception {
    // TODO(developer): Replace these variables before running the sample.
    String projectId = "MY_PROJECT_ID";
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    queryClusteredTable(projectId, datasetName, tableName);
  }

  public static void queryClusteredTable(String projectId, String datasetName, String tableName) {
    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();

      String sourceTable = "`" + projectId + "." + datasetName + "." + tableName + "`";
      String query =
          "SELECT word, word_count\n"
              + "FROM "
              + sourceTable
              + "\n"
              // Optimize query performance by filtering the clustered columns in sort order
              + "WHERE corpus = 'romeoandjuliet'\n"
              + "AND word_count >= 1";

      QueryJobConfiguration queryConfig = QueryJobConfiguration.newBuilder(query).build();

      TableResult results = bigquery.query(queryConfig);

      results
          .iterateAll()
          .forEach(row -> row.forEach(val -> System.out.printf("%s,", val.toString())));

      System.out.println("Query clustered table performed successfully.");
    } catch (BigQueryException | InterruptedException e) {
      System.out.println("Query not performed \n" + e.toString());
    }
  }
}

Python

Antes de testar esta amostra, siga as instruções de configuração do Python 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 Python.

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

from google.cloud import bigquery

# Construct a BigQuery client object.
client = bigquery.Client()

# TODO(developer): Set table_id to the ID of the destination table.
# table_id = "your-project.your_dataset.your_table_name"

sql = "SELECT * FROM `bigquery-public-data.samples.shakespeare`"
cluster_fields = ["corpus"]

job_config = bigquery.QueryJobConfig(
    clustering_fields=cluster_fields, destination=table_id
)

# Start the query, passing in the extra configuration.
client.query_and_wait(
    sql, job_config=job_config
)  # Make an API request and wait for job to complete.

table = client.get_table(table_id)  # Make an API request.
if table.clustering_fields == cluster_fields:
    print(
        "The destination table is written using the cluster_fields configuration."
    )

Práticas recomendadas

Para conseguir o melhor desempenho das consultas em tabelas em cluster, use as seguintes práticas recomendadas.

Para contextualizar, a tabela de amostra usada nos exemplos de práticas recomendadas é uma tabela em cluster criada usando uma instrução DDL. A instrução DDL cria uma tabela chamada ClusteredSalesData. A tabela é agrupada pelas seguintes colunas: customer_id, product_id, order_id, nessa ordem de classificação.

CREATE TABLE
  `mydataset.ClusteredSalesData`
PARTITION BY
  DATE(timestamp)
CLUSTER BY
  customer_id,
  product_id,
  order_id AS
SELECT
  *
FROM
  `mydataset.SalesData`

Filtrar colunas em cluster por ordem de classificação

Ao especificar um filtro, use expressões que filtrem as colunas em cluster por ordem de classificação. A ordem de classificação é a ordem de coluna da cláusula CLUSTER BY. Para aproveitar os benefícios do clustering, inclua uma ou mais colunas em cluster na ordem de classificação da esquerda para a direita, começando pela primeira coluna. Na maioria dos casos, a primeira coluna de clustering é a mais eficaz na remoção de blocos, seguida pela segunda e pela terceira. Ainda é possível usar a segunda ou terceira coluna sozinha na consulta, mas a remoção de bloco provavelmente não será tão eficaz. A ordem dos nomes das colunas dentro da expressão de filtro não afeta o desempenho.

No exemplo a seguir, consultamos a tabela em cluster ClusteredSalesData criada no exemplo anterior. A consulta inclui uma expressão que filtra customer_id e depois product_id. Essa consulta otimiza o desempenho filtrando as colunas em cluster na ordem de classificação, a ordem da coluna fornecida na cláusula CLUSTER BY.

SELECT
  SUM(totalSale)
FROM
  `mydataset.ClusteredSalesData`
WHERE
  customer_id = 10000
  AND product_id LIKE 'gcp_analytics%'

A consulta a seguir não filtra as colunas em cluster na ordem de classificação. Por isso, o desempenho da consulta não é o ideal. Essa consulta filtra product_id e depois order_id (ignorando customer_id).

SELECT
  SUM(totalSale)
FROM
  `mydataset.ClusteredSalesData`
WHERE
  product_id LIKE 'gcp_analytics%'
  AND order_id = 20000

Não use colunas em cluster nas expressões de filtro complexas

Se você usar uma expressão de filtro complexa em uma coluna em cluster, o desempenho da consulta não será otimizado porque a remoção de bloco não será aplicada.

Por exemplo, a consulta a seguir não remove os blocos porque uma coluna em cluster (customer_id) foi usada em uma função na expressão de filtro.

SELECT
  SUM(totalSale)
FROM
  `mydataset.ClusteredSalesData`
WHERE
  CAST(customer_id AS STRING) = "10000"

Para otimizar o desempenho da consulta removendo blocos, use expressões de filtro simples, como as seguintes. Neste exemplo, um filtro simples é aplicado à coluna em cluster (customer_id).

SELECT
  SUM(totalSale)
FROM
  `mydataset.ClusteredSalesData`
WHERE
  customer_id = 10000

Não compare colunas em cluster com outras colunas

Se uma expressão de filtro comparar uma coluna em cluster a outra (coluna em cluster ou não), o desempenho da consulta não será otimizado porque a remoção de bloco não poderá ser aplicada.

A consulta a seguir não remove os blocos porque a expressão de filtro compara uma coluna em cluster (customer_id) a outra coluna (order_id).

SELECT
  SUM(totalSale)
FROM
  `mydataset.ClusteredSalesData`
WHERE
  customer_id = order_id

Segurança de tabelas

Para controlar o acesso a tabelas no BigQuery, consulte Controlar o acesso a recursos com o IAM.

A seguir