Como consultar tabelas em cluster

Quando você cria uma tabela em cluster no BigQuery, os dados são organizados automaticamente com base no conteúdo de uma ou mais colunas no esquema da tabela. As colunas especificadas são usadas para colocar dados relacionados. Quando você cria uma tabela em cluster usando várias colunas, a ordem das colunas especificadas é importante. A ordem das colunas especificadas determina a ordem de classificação dos dados.

Para otimizar o desempenho de consultas em tabelas em cluster, use uma expressão que filtre em uma coluna em cluster ou em várias colunas em cluster, na ordem em que essas colunas estiverem especificadas. Consultas que filtram colunas em cluster geralmente têm melhor desempenho que as que filtram somente colunas que não estão em cluster.

O BigQuery classifica os dados em uma tabela em cluster com base nos valores existentes nas colunas em cluster e os organiza em blocos.

Quando você envia uma consulta que contém um filtro em uma coluna em cluster, o BigQuery usa as informações do cluster para determinar com eficiência se há dados relevantes para a consulta em um bloco. Desse modo, o BigQuery analisa somente os blocos relevantes, um processo conhecido como remoção de blocos.

Para consultar tabelas em cluster:

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

No momento, só é possível usar o GoogleSQL com tabelas em cluster.

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.
query_job = client.query(sql, job_config=job_config)  # Make an API request.
query_job.result()  # Wait for the 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."
    )

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.

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 conseguir os benefícios do clustering, inclua todas as colunas em cluster ou um subconjunto de colunas por ordem de classificação, da esquerda para a direita, começando pela primeira coluna. Por exemplo, se a ordem de classificação da coluna for A, B, C, uma consulta que filtra A e B pode se beneficiar do armazenamento em cluster, mas uma consulta que filtra B e C não. 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 Introdução aos controles de acesso a tabelas.

A seguir