Geclusterte Tabellen abfragen

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:

  • Mit der Cloud Console
  • Mit dem Befehl bq query des bq-Befehlszeilentools
  • Durch Aufrufen der API-Methode jobs.insert und Konfigurieren eines Abfragejobs
  • Mit den Clientbibliotheken

Derzeit können Sie nur Standard-SQL mit geclusterten Tabellen verwenden.

Go

Bevor Sie dieses Beispiel ausprobieren, 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.

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 ausprobieren, folgen Sie den Schritten zur Einrichtung von Java in der BigQuery-Kurzanleitung: Clientbibliotheken verwenden. Weitere Informationen finden Sie in der Referenzdokumentation zur BigQuery Java API.

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 ausprobieren, folgen Sie den Schritten zur Einrichtung von Python in der BigQuery-Kurzanleitung: Clientbibliotheken verwenden. Weitere Informationen finden Sie in der Referenzdokumentation zur  Python API.

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 Berechtigung

Zum Abfragen einer Tabelle benötigen Sie mindestens Berechtigungen des Typs bigquery.tables.getData.

Die folgenden vordefinierten IAM-Rollen enthalten bigquery.tables.getData-Berechtigungen:

  • bigquery.dataViewer
  • bigquery.dataOwner
  • bigquery.dataEditor
  • bigquery.admin

Wenn ein Nutzer mit Berechtigungen vom Typ bigquery.datasets.create ein Dataset erstellt, hat er dafür außerdem bigquery.dataOwner-Zugriff. Mit bigquery.dataOwner-Zugriff hat der Nutzer die Möglichkeit, Tabellen und Ansichten im Dataset abzufragen.

Sie benötigen außerdem Berechtigungen des Typs bigquery.jobs.create, um Abfragejobs ausführen zu können. Die folgenden vordefinierten IAM-Rollen enthalten bigquery.jobs.create-Berechtigungen:

  • bigquery.user
  • bigquery.jobUser
  • bigquery.admin

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

Best Practices

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

In den Beispielen verwendete Beispieltabelle

Die in den Beispielen auf dieser Seite 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. 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.

Die folgende 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 Sortierreihenfolge gefiltert werden.

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 (geclusterten oder nicht geclusterten 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.

Mehr zur Verwendung der Play Console erfahren