Consultar tabelas agrupadas

Quando cria uma tabela agrupada no BigQuery, os dados da tabela são organizados automaticamente com base no conteúdo de uma ou mais colunas no esquema da tabela. As colunas que especificar são usadas para colocar dados relacionados no mesmo local. Quando agrupa uma tabela com várias colunas, a ordem das colunas que especifica é importante. A ordem das colunas especificadas determina a ordem de ordenação dos dados.

Para otimizar o desempenho quando executa consultas em tabelas agrupadas, use uma expressão que filtre uma coluna agrupada ou várias colunas agrupadas pela ordem em que as colunas agrupadas são especificadas. As consultas que filtram colunas agrupadas geralmente têm um desempenho melhor do que as consultas que filtram apenas colunas não agrupadas.

O BigQuery ordena os dados numa tabela agrupada com base nos valores das colunas de agrupamento e organiza-os em blocos.

Quando envia uma consulta que contém um filtro numa coluna agrupada, o BigQuery usa as informações de agrupamento para determinar eficientemente se um bloco contém dados relevantes para a consulta. Isto permite que o BigQuery analise apenas os blocos relevantes, um processo denominado eliminação de blocos.

Pode consultar tabelas agrupadas por:

  • Usar a Google Cloud consola
  • Usar o comando bq query da ferramenta de linhas de comando bq
  • Chamar o método jobs.insert e configurar uma tarefa de consulta
  • Usar as bibliotecas de cliente

Só pode usar o GoogleSQL com tabelas agrupadas.

Go

Antes de experimentar este exemplo, siga as Goinstruções de configuração no início rápido do BigQuery com bibliotecas cliente. Para mais informações, consulte a API Go BigQuery documentação de referência.

Para se autenticar no BigQuery, configure as Credenciais padrão da aplicação. Para mais informações, consulte o artigo Configure 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 experimentar este exemplo, siga as Javainstruções de configuração no início rápido do BigQuery com bibliotecas cliente. Para mais informações, consulte a API Java BigQuery documentação de referência.

Para se autenticar no BigQuery, configure as Credenciais padrão da aplicação. Para mais informações, consulte o artigo Configure 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 experimentar este exemplo, siga as Pythoninstruções de configuração no início rápido do BigQuery com bibliotecas cliente. Para mais informações, consulte a API Python BigQuery documentação de referência.

Para se autenticar no BigQuery, configure as Credenciais padrão da aplicação. Para mais informações, consulte o artigo Configure 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."
    )

Autorizações necessárias

Para executar uma tarefa de consulta, precisa da autorização de gestão de identidade e de acesso (IAM) no projeto que executa a tarefa de consulta.bigquery.jobs.create

Cada uma das seguintes funções de IAM predefinidas inclui as autorizações de que precisa para executar uma tarefa de consulta:

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

Também precisa da autorização bigquery.tables.getData em todas as tabelas e vistas a que a sua consulta faz referência. Além disso, quando consulta uma vista, precisa desta autorização em todas as tabelas e vistas subjacentes. No entanto, se estiver a usar visualizações autorizadas ou conjuntos de dados autorizados, não precisa de aceder aos dados de origem subjacentes.

Cada uma das seguintes funções de IAM predefinidas inclui a autorização de que precisa em todas as tabelas e vistas a que a consulta faz referência:

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

Para mais informações sobre as funções de IAM no BigQuery, consulte o artigo Funções e autorizações predefinidas.

Práticas recomendadas

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

Para contexto, a tabela de exemplo usada nos exemplos de práticas recomendadas é uma tabela agrupada criada através de uma declaração DDL. A declaração DDL cria uma tabela com o nome ClusteredSalesData. A tabela está agrupada pelas seguintes colunas: customer_id, product_id e order_id, nessa ordem de ordenação.

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

Filtre colunas agrupadas por ordem de ordenação

Quando especifica um filtro, use expressões que filtram as colunas agrupadas por ordem de ordenação. A ordem de ordenação é a ordem das colunas indicada na cláusula CLUSTER BY. Para usufruir das vantagens da agrupagem, inclua uma ou mais colunas agrupadas por ordem de ordenação da esquerda para a direita, começando pela primeira coluna. Na maioria dos casos, a primeira coluna de agrupamento é a mais eficaz na remoção de blocos, seguida da segunda coluna e, depois, da terceira. Pode continuar a usar a segunda ou a terceira coluna sozinha na consulta, mas a eliminação de blocos provavelmente não será tão eficaz. A ordem dos nomes das colunas na expressão de filtro não afeta o desempenho.

O exemplo seguinte consulta a ClusteredSalesDatatabela agrupada que foi criada no exemplo anterior. A consulta inclui uma expressão de filtro que filtra por customer_id e, em seguida, por product_id. Esta consulta otimiza o desempenho filtrando as colunas agrupadas na ordem—a ordem das colunas indicada na cláusula CLUSTER BY.

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

A seguinte consulta não filtra as colunas agrupadas por ordem de ordenação. Como tal, o desempenho da consulta não é o ideal. Esta consulta filtra por product_id e, em seguida, por 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 agrupadas em expressões de filtro complexas

Se usar uma coluna agrupada numa expressão de filtro complexa, o desempenho da consulta não é otimizado porque não é possível aplicar a eliminação de blocos.

Por exemplo, a seguinte consulta não remove blocos porque é usada uma coluna agrupada, customer_id, numa função na expressão de filtro.

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

Para otimizar o desempenho das consultas através da remoção de blocos, use expressões de filtro simples, como as seguintes. Neste exemplo, é aplicado um filtro simples à coluna agrupada: customer_id.

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

Não compare colunas agrupadas com outras colunas

Se uma expressão de filtro comparar uma coluna agrupada com outra coluna (uma coluna agrupada ou uma coluna não agrupada), o desempenho da consulta não é otimizado porque não é possível aplicar a eliminação de blocos.

A consulta seguinte não remove blocos porque a expressão de filtro compara uma coluna agrupada, customer_id, com outra coluna, order_id.

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

Segurança da mesa

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

O que se segue?