Geclusterte Tabellen abfragen

Wenn Sie eine geclusterte Tabelle in BigQuery erstellen, werden die Tabellendaten automatisch basierend auf dem Inhalt einer oder mehrerer Spalten im Tabellenschema organisiert. Die von Ihnen angegebenen Spalten werden für die Zusammenstellung verwandter Daten verwendet. Wenn Sie eine Tabelle mit mehreren Spalten clustern, ist die Reihenfolge der von Ihnen angegebenen Spalten wichtig. Die Reihenfolge der angegebenen Spalten bestimmt die Sortierreihenfolge der Daten.

Wenn Sie die Leistung optimieren möchten, verwenden Sie beim Ausführen von Abfragen für geclusterte Tabellen einen Ausdruck, der nach einer geclusterten Spalte oder nach mehreren geclusterten Spalten in der Reihenfolge sortiert ist, in der die geclusterten Spalten angegeben sind. Abfragen, die nach geclusterten Spalten filtern, sind im Allgemeinen besser als Abfragen, die nur nach nicht geclusterten Spalten filtern.

BigQuery sortiert die Daten in einer geclusterten Tabelle basierend auf den Werten in den Clustering-Spalten und organisiert sie in Blöcken.

Wenn Sie eine Abfrage senden, die einen Filter für eine geclusterte Spalte enthält, verwendet BigQuery die Clusterinformationen, um zu ermitteln, ob ein Block für die Abfrage relevante Daten enthält. So kann BigQuery ausschließlich relevante Blöcke scannen – ein Prozess, der als Blockbereinigung bezeichnet wird.

So können Sie geclusterte Tabellen abfragen:

  • Google Cloud Console verwenden
  • Den Befehl bq query des bq-Befehlszeilentools verwenden
  • Durch Aufrufen der Methode jobs.insert und Konfigurieren eines Abfragejobs
  • Mithilfe der Clientbibliotheken

Derzeit können Sie nur GoogleSQL mit geclusterten Tabellen verwenden.

Go

Bevor Sie dieses Beispiel anwenden, folgen Sie den Schritten zur Einrichtung von Go in der BigQuery-Kurzanleitung zur Verwendung von Clientbibliotheken. Weitere Angaben finden Sie in der Referenzdokumentation zur BigQuery Go API.

Richten Sie zur Authentifizierung bei BigQuery die Standardanmeldedaten für Anwendungen ein. Weitere Informationen finden Sie unter Authentifizierung für Clientbibliotheken einrichten. 

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

Bevor Sie dieses Beispiel anwenden, folgen Sie den Schritten zur Einrichtung von Java in der BigQuery-Kurzanleitung zur Verwendung von Clientbibliotheken. Weitere Angaben finden Sie in der Referenzdokumentation zur BigQuery Java API.

Richten Sie zur Authentifizierung bei BigQuery die Standardanmeldedaten für Anwendungen ein. Weitere Informationen finden Sie unter Authentifizierung für Clientbibliotheken einrichten. 

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

Bevor Sie dieses Beispiel anwenden, folgen Sie den Schritten zur Einrichtung von Python in der BigQuery-Kurzanleitung zur Verwendung von Clientbibliotheken. Weitere Angaben finden Sie in der Referenzdokumentation zur BigQuery Python API.

Richten Sie zur Authentifizierung bei BigQuery die Standardanmeldedaten für Anwendungen ein. Weitere Informationen finden Sie unter Authentifizierung für Clientbibliotheken einrichten. 

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

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.

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 Sortierfolge 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 alle geclusterten Spalten oder eine Teilmenge der Spalten von links nach rechts ein, beginnend mit der ersten Spalte. Beispiel: Wenn die Spaltensortierreihenfolge A, B, C ist, kann eine Abfrage, die nach A und B filtert, von Clustering profitieren, aber eine Abfrage, bei der nach B und C gefiltert wird, kann das nicht. 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 Einführung in die Tabellenzugriffssteuerung.

Nächste Schritte