Geclusterte Tabellen abfragen

Durch das Filtern nach geclusterten Spalten können Sie die Leistung verbessern und die Kosten senken, wenn Sie geclusterte Tabellen in BigQuery abfragen. Wenn eine Abfrage nach einer oder mehreren geclusterten Spalten filtert, verwendet BigQuery die Clustering-Informationen, um nur die relevanten Blöcke zu scannen. Dieser Vorgang wird als Blockbereinigung bezeichnet.

In diesem Dokument werden die Berechtigungen und Best Practices für das Abfragen geclusterter Tabellen beschrieben, einschließlich der Gründe, warum die Reihenfolge der geclusterten Spalten in Ihren Filtern wichtig ist. Geclusterte Tabellen können nur mit GoogleSQL abgefragt werden.

Erforderliche Berechtigungen

Zum Ausführen eines Abfragejobs benötigen Sie die IAM-Berechtigung (Identity and Access Management) bigquery.jobs.create für das Projekt, in dem der Abfragejob ausgeführt wird.

Jede der folgenden vordefinierten IAM-Rollen umfasst die Berechtigungen, die Sie zum Ausführen eines Abfragejobs benötigen:

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

Außerdem benötigen Sie die Berechtigung bigquery.tables.getData für alle Tabellen und Ansichten, auf die Ihre Abfrage verweist. Wenn Sie eine Ansicht abfragen, benötigen Sie außerdem diese Berechtigung für alle zugrunde liegenden Tabellen und Ansichten. Wenn Sie jedoch autorisierte Ansichten oder autorisierte Datasets verwenden, benötigen Sie keinen Zugriff auf die zugrunde liegenden Quelldaten.

Jede der folgenden vordefinierten IAM-Rollen enthält die Berechtigung, die Sie für alle Tabellen und Ansichten benötigen, auf die die Abfrage verweist:

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

Weitere Informationen zu IAM-Rollen in BigQuery finden Sie unter Vordefinierte Rollen und Berechtigungen.

Geclusterte Tabellen abfragen

So können Sie geclusterte Tabellen abfragen:

• Cloud de Confiance Console verwenden
• Mit dem Befehl bq query im bq-Befehlszeilentool.
• Durch Aufrufen der Methode jobs.insert und Konfigurieren eines Abfragejobs
• Mit den Clientbibliotheken

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
}

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

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

Best Practices

Verwenden Sie die folgenden Best Practices, um die beste Leistung bei Abfragen für geclusterte Tabellen zu erzielen.

Die in den Best Practice-Beispielen verwendete Beispieltabelle ist eine geclusterte Tabelle, die mithilfe einer DDL-Anweisung erstellt wird. Mit der DDL-Anweisung wird eine Tabelle mit dem Namen ClusteredSalesData erstellt. Die Tabelle wird nach den folgenden Spalten geclustert: customer_id, product_id, order_id, in dieser Sortierreihenfolge.

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

Geclusterte Spalten nach Sortierreihenfolge filtern

Wenn Sie einen Filter angeben, verwenden Sie Ausdrücke, die nach den geclusterten Spalten in der Sortierreihenfolge filtern. Die Sortierreihenfolge ist die Spaltenreihenfolge in der CLUSTER BY-Klausel. Damit Sie die Vorteile des Clustering erhalten, fügen Sie eine oder mehrere der geclusterten Spalten in der Sortierreihenfolge von links nach rechts ein, beginnend mit der ersten Spalte. In den meisten Fällen ist die erste Clustering-Spalte am effektivsten für das Block-Pruning, gefolgt von der zweiten und dann der dritten Spalte. Sie können die zweite oder dritte Spalte weiterhin allein in der Abfrage verwenden, aber die Blockbereinigung ist wahrscheinlich nicht so effektiv. Die Reihenfolge der Spaltennamen innerhalb des Filterausdrucks wirkt sich nicht auf die Leistung aus.

Im folgenden Beispiel wird die geclusterte Tabelle ClusteredSalesData abgefragt, die im vorherigen Beispiel erstellt wurde. Die Abfrage enthält einen Filterausdruck, der nach customer_id und dann nach product_id filtert. Diese Abfrage optimiert die Leistung, da die geclusterten Spalten in der Sortierfolge gefiltert werden – der Spaltenreihenfolge in der CLUSTER BY-Klausel.

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

Mit der folgenden Abfrage werden die geclusterten Spalten nicht in der Sortierreihenfolge gefiltert. Daher ist die Leistung der Abfrage nicht optimal. Diese Abfrage filtert nach product_id und anschließend nach order_id, wobei customer_id übersprungen wird.

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

Keine geclusterten Spalten in komplexen Filterausdrücken verwenden

Wenn Sie eine geclusterte Spalte in einem komplexen Filterausdruck verwenden, wird die Leistung der Abfrage nicht optimiert, da die Blockbereinigung nicht angewendet werden kann.

Die folgende Abfrage bereinigt beispielsweise keine Blöcke, da in einer Funktion im Filterausdruck eine geclusterte Spalte – customer_id – verwendet wird.

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

Sie verwenden einfache Filterausdrücke wie den folgenden, um die Abfrageleistung durch das Bereinigen von Blöcken zu optimieren. In diesem Beispiel wird auf die geclusterte Spalte – customer_id – ein einfacher Filter angewendet.

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

Keine geclusterten Spalten mit anderen Spalten vergleichen

Wenn ein Filterausdruck eine geclusterte Spalte mit einer anderen Spalte (geclusterte oder nicht geclusterte Spalte) vergleicht, wird die Leistung der Abfrage nicht optimiert, da die Blockbereinigung nicht angewendet werden kann.

Mit der folgenden Abfrage werden keine Blöcke bereinigt, da der Filterausdruck eine geclusterte Spalte – customer_id – mit einer anderen Spalte – order_id – vergleicht.

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

Tabellensicherheit

Informationen zum Steuern des Zugriffs auf Tabellen in BigQuery finden Sie unter Zugriff auf Ressourcen mit IAM steuern.

Nächste Schritte

• Interaktive Abfragen und Batch-Abfragen ausführen
• Geclusterte Tabellen erstellen und verwenden
• Einführung in partitionierte Tabellen
• Informationen zum Erstellen von partitionierten Tabellen finden Sie unter Partitionierte Tabellen erstellen.