Esecuzione di query sulle tabelle in cluster

Quando crei una tabella in cluster in BigQuery, i dati della tabella vengono organizzati automaticamente in base ai contenuti di una o più colonne lo schema della tabella. Le colonne specificate vengono utilizzate per collocare i dati correlati. Quando raggruppi una tabella utilizzando più colonne, l'ordine delle colonne si ottiene specificare se è importante. L'ordine delle colonne specificate determina nell'ordine dei dati.

Per ottimizzare le prestazioni quando esegui query su tabelle in cluster, utilizza un'istanza espressione che filtra in base a una colonna in cluster o a più colonne in cluster nell'ordine in cui vengono specificate le colonne in cluster. Query che filtrano in base a le colonne in cluster solitamente hanno un rendimento migliore rispetto alle query che filtrano solo in base a e le colonne non in cluster.

BigQuery ordina i dati in una tabella in cluster in base ai valori nelle colonne di clustering e li organizza in blocchi.

Quando invii una query che contiene un filtro su una colonna in cluster, BigQuery utilizza le informazioni di clustering per determinare in modo efficiente se un blocco contiene dati pertinenti per la query. Questo permette a BigQuery di scansionare solo i blocchi pertinenti, un processo noto come eliminazione dei blocchi.

Puoi eseguire query sulle tabelle in cluster per:

  • Utilizzo della console Google Cloud
  • Utilizzo del comando bq query dello strumento a riga di comando bq
  • Chiamata al metodo jobs.insert e la configurazione di un job di query
  • Utilizzo delle librerie client

Al momento, puoi utilizzare solo GoogleSQL con le tabelle in cluster.

Vai

Prima di provare questo esempio, segui le istruzioni per la configurazione di Go nel Guida rapida di BigQuery con librerie client. Per ulteriori informazioni, consulta API Go BigQuery documentazione di riferimento.

Per eseguire l'autenticazione su BigQuery, configura Credenziali predefinite dell'applicazione. Per ulteriori informazioni, vedi Configura l'autenticazione per le librerie client. 

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

Prima di provare questo esempio, segui le istruzioni per la configurazione di Java nel Guida rapida di BigQuery con librerie client. Per ulteriori informazioni, consulta API Java BigQuery documentazione di riferimento.

Per eseguire l'autenticazione su BigQuery, configura Credenziali predefinite dell'applicazione. Per ulteriori informazioni, vedi Configura l'autenticazione per le librerie client. 

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

Prima di provare questo esempio, segui le istruzioni per la configurazione di Python nel Guida rapida di BigQuery con librerie client. Per ulteriori informazioni, consulta API Python BigQuery documentazione di riferimento.

Per eseguire l'autenticazione su BigQuery, configura Credenziali predefinite dell'applicazione. Per ulteriori informazioni, vedi Configura l'autenticazione per le librerie client. 

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

Autorizzazioni obbligatorie

Per eseguire una query job, è necessario bigquery.jobs.create Autorizzazione Identity and Access Management (IAM) per il progetto che esegue il job di query.

Ciascuno dei seguenti ruoli IAM predefiniti include autorizzazioni necessarie per eseguire un job di query:

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

Devi avere anche l'autorizzazione bigquery.tables.getData su tutte le tabelle e le viste a cui fa riferimento la query. Inoltre, quando esegui query una vista è necessaria questa autorizzazione per tutte le tabelle e le viste sottostanti. Tuttavia, se utilizzi le visualizzazioni autorizzate o set di dati autorizzati, non devi l'accesso ai dati di origine sottostanti.

Ciascuno dei seguenti ruoli IAM predefiniti include necessaria per tutte le tabelle e le viste a cui fa riferimento la query:

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

Per ulteriori informazioni sui ruoli IAM in BigQuery, vedi Autorizzazioni e ruoli predefiniti.

Best practice

Per ottenere le migliori prestazioni dalle query sulle tabelle in cluster, utilizza seguendo le best practice.

Per il contesto, la tabella di esempio utilizzata negli esempi di best practice è un in cluster creata utilizzando un'istruzione DDL. L'istruzione DDL crea una tabella denominata ClusteredSalesData. La tabella è raggruppata in base al colonne seguenti: customer_id, product_id, order_id, in questo ordinamento.

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

Filtra le colonne in cluster per ordinamento

Quando specifichi un filtro, utilizza le espressioni che filtrano nelle colonne in cluster. in ordine. L'ordinamento è quello delle colonne indicato nella clausola CLUSTER BY. Per ottenere i vantaggi del clustering, includi tutte le istanze o un sottoinsieme di colonne nell'ordinamento da sinistra a destra, iniziando con nella prima colonna. Ad esempio, se l'ordinamento delle colonne è A, B, C, un query che filtra in base a A e B potrebbe trarre vantaggio dal clustering, ma una query che filtra per B e C non lo fa. L'ordine dei nomi delle colonne all'interno l'espressione di filtro non influisce sulle prestazioni.

L'esempio seguente esegue query sulla tabella in cluster ClusteredSalesData creato nell'esempio precedente. La query include un filtro espressione che filtra in base a customer_id e poi a product_id. Questa query ottimizza le prestazioni filtrando le colonne in cluster in ordina ordine: l'ordine delle colonne specificato nella clausola CLUSTER BY.

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

La seguente query non filtra le colonne in cluster in base all'ordinamento. Come significa che le prestazioni della query non sono ottimali. Questa query filtra in base a product_id poi in order_id (saltando customer_id).

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

Non utilizzare colonne in cluster in espressioni di filtro complesse

Se utilizzi una colonna in cluster in un'espressione di filtro complessa, il rendimento la query non è ottimizzata perché non è possibile applicare l'eliminazione dei blocchi.

Ad esempio, la seguente query non eliminerà i blocchi perché un cluster colonna—customer_id—è utilizzata in una funzione nel filtro un'espressione di base.

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

Per ottimizzare le prestazioni delle query eliminando i blocchi, utilizza semplici espressioni di filtro come mostrato di seguito. In questo esempio, viene applicato un filtro semplice colonna in cluster: customer_id.

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

Non confrontare le colonne in cluster con altre colonne

Se un'espressione di filtro confronta una colonna in cluster con un'altra colonna (una in cluster o in una colonna non in cluster), le prestazioni della query non ottimizzato perché non è possibile applicare l'eliminazione dei blocchi.

La seguente query non elimina i blocchi perché l'espressione di filtro confronta una colonna in cluster (customer_id) a un'altra colonna (order_id).

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

Sicurezza dei tavoli

Per controllare l'accesso alle tabelle in BigQuery, consulta Introduzione ai controlli di accesso alle tabelle.

Passaggi successivi