Geclusterte Tabellen erstellen und verwenden

In diesem Dokument wird beschrieben, wie geclusterte Tabellen in BigQuery erstellt und verwendet werden. Eine Übersicht über die Unterstützung für geclusterte Tabellen in BigQuery finden Sie unter Einführung in geclusterte Tabellen.

Geclusterte Tabellen erstellen

Mit den folgenden Methoden können Sie eine geclusterte Tabelle erstellen:

• Tabelle aus einem Abfrageergebnis erstellen:

  • Führen Sie eine DDL-Anweisung des Typs CREATE TABLE AS SELECT aus.
  • Führen Sie eine Abfrage aus, mit der eine geclusterte Zieltabelle erstellt wird.

• Verwenden Sie die DDL-Anweisung CREATE TABLE mit der Klausel CLUSTER BY, die clustering_column_list enthält.

• Führen Sie den Befehl bq mk des bq-Befehlszeilentools aus:

• Rufen Sie die API-Methode tables.insert auf.

• Daten in BigQuery laden.

• Verwenden Sie die Client-Bibliotheken.

Tabellennamen

Wenn Sie eine Tabelle in BigQuery erstellen, muss der Tabellenname pro Dataset eindeutig sein. Der Tabellenname kann:

• Zeichen mit insgesamt bis zu 1.024 UTF-8-Byte enthalten.
• Unicode-Zeichen in Kategorie L (Buchstabe), M (Zeichen), N (Zahl), Pc (Verbindungselement, einschließlich Unterstrich), Pd (Strich), Zs (Leerzeichen) enthalten Weitere Informationen finden Sie unter Allgemeine Kategorie.

Beispiele für gültige Tabellennamen: table 01, ग्राहक, 00_お客様, étudiant-01.

Vorsichtsmaßnahmen:

• Bei Tabellennamen wird standardmäßig zwischen Groß- und Kleinschreibung unterschieden. mytable und MyTable können im selben Dataset gleichzeitig vorhanden sein, es sei denn, sie sind Teil eines Datasets mit deaktivierter Berücksichtigung der Groß-/Kleinschreibung.
• Einige Tabellennamen und Präfixe für Tabellennamen sind reserviert. Wenn Ihnen eine Fehlermeldung angezeigt wird, dass der Tabellenname oder das Präfix reserviert ist, wählen Sie einen anderen Namen aus und versuchen Sie es noch einmal.

• Wenn Sie mehrere Punktoperatoren (.) in eine Sequenz aufnehmen, werden die doppelten Operatoren implizit entfernt.

  Beispiel: project_name....dataset_name..table_name

  Dieses Tag wird folgendermaßen geändert: project_name.dataset_name.table_name

Erforderliche Berechtigungen

Zum Erstellen einer Tabelle benötigen Sie die folgenden IAM-Berechtigungen:

• bigquery.tables.create
• bigquery.tables.updateData
• bigquery.jobs.create

Außerdem ist möglicherweise die Berechtigung bigquery.tables.getData erforderlich, um auf die Daten zuzugreifen, die Sie in die Tabelle schreiben.

Alle folgenden vordefinierten IAM-Rollen enthalten die Berechtigungen, die Sie für das Erstellen einer Tabelle benötigen:

• roles/bigquery.dataEditor
• roles/bigquery.dataOwner
• roles/bigquery.admin (einschließlich der Berechtigung bigquery.jobs.create)
• roles/bigquery.user (einschließlich der Berechtigung bigquery.jobs.create)
• roles/bigquery.jobUser (einschließlich der Berechtigung bigquery.jobs.create)

Wenn Sie die Berechtigung bigquery.datasets.create haben, können Sie außerdem Tabellen in den von Ihnen erstellten Datasets anlegen und aktualisieren.

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

So erstellen Sie eine leere geclusterte Tabelle mit einer Schemadefinition:

Clustering-Spalten werden beim Erstellen einer Tabelle in BigQuery angegeben. Nachdem die Tabelle erstellt wurde, können Sie die Clustering-Spalten auch wieder ändern. Weitere Informationen dazu finden Sie unter Clustering-Spezifikation ändern.

Clustering-Spalten müssen Spalten der obersten Ebene sein, die nicht wiederholt werden. Sie müssen außerdem einem der folgenden einfachen Datentypen entsprechen:

• DATE
• BOOLEAN
• GEOGRAPHY
• INTEGER
• NUMERIC
• BIGNUMERIC
• STRING
• TIMESTAMP
• RANGE (Vorschau)

Sie können bis zu vier Clustering-Spalten angeben. Wenn Sie mehrere Spalten angeben, wird anhand der Reihenfolge der Spalten festgelegt, wie die Daten sortiert werden. Wenn die Tabelle beispielsweise nach den Spalten a, b und c geclustert ist, werden die Daten in der gleichen Reihenfolge sortiert – zuerst nach Spalte a, dann nach Spalte b und dann nach Spalte c. Als Best Practice sollte die am häufigsten gefilterte oder aggregierte Spalte möglichst an erster Stelle stehen.

Die Reihenfolge Ihrer Clustering-Spalten wirkt sich auch auf die Abfrageleistung und die Preise aus. Weitere Informationen zu Best Practices für Abfragen von geclusterten Tabellen finden Sie unter Geclusterte Tabellen abfragen.

So erstellen Sie eine leere geclusterte Tabelle mit einer Schemadefinition:

Console

1. Öffnen Sie in der Google Cloud Console die Seite BigQuery.

   BigQuery aufrufen

2. Maximieren Sie im Bereich Explorer Ihr Projekt und wählen Sie dann ein Dataset aus.
3. Klicken Sie im Abschnitt Dataset-Informationen auf Tabelle erstellen.
4. Geben Sie im Bereich Tabelle erstellen die folgenden Details an:
1. Wählen Sie im Abschnitt Quelle in der Liste Tabelle erstellen aus die Option Leere Tabelle aus.
2. Geben Sie im Bereich Ziel die folgenden Details an:
   1. Wählen Sie bei Dataset das Dataset aus, in dem Sie die Tabelle erstellen möchten.
   2. Geben Sie im Feld Tabelle den Namen der Tabelle ein, die Sie erstellen möchten.
   3. Achten Sie darauf, dass das Feld Tabellentyp auf Native Tabelle eingestellt ist.
3. Geben Sie im Abschnitt Schema die Schemadefinition ein. Sie können Schemainformationen manuell mit einer der folgenden Methoden eingeben:
   • Option 1: Klicken Sie auf Als Text bearbeiten und fügen Sie das Schema in Form eines JSON-Arrays ein. Generieren Sie das Schema mit demselben Verfahren wie beim Erstellen einer JSON-Schemadatei, wenn Sie ein JSON-Array verwenden. Sie können das Schema einer vorhandenen Tabelle im JSON-Format ansehen. Geben Sie dafür folgenden Befehl ein:

         bq show --format=prettyjson dataset.table
         

   • Option 2: Klicken Sie auf Feld hinzufügen und geben Sie das Tabellenschema ein. Geben Sie für jedes Feld Name, Typ und Modus an.
4. Geben Sie unter Clustering-Reihenfolge ein bis vier kommagetrennte Spaltennamen ein.
5. Optional: Wählen Sie die Option Vom Kunden verwalteten Verschlüsselungsschlüssel (CMEK) verwenden, wenn Sie im Abschnitt Erweiterte Optionen einen vom Kunden verwalteten Verschlüsselungsschlüssel verwenden möchten. Standardmäßig verschlüsselt BigQuery inaktive Kundendaten mit einem Google-eigenen und von Google verwalteten Schlüssel.
6. Klicken Sie auf Tabelle erstellen.

SQL

Verwenden Sie den DDL-Anweisungsbefehl CREATE TABLE mit der Option CLUSTER BY. Im folgenden Beispiel wird in eine geclusterte Tabelle namens myclusteredtable in mydataset erstellt.

1. Öffnen Sie in der Google Cloud Console die Seite BigQuery.

   BigQuery aufrufen

2. Geben Sie im Abfrageeditor die folgende Anweisung ein:

   CREATE TABLE mydataset.myclusteredtable
   (
     customer_id STRING,
     transaction_amount NUMERIC
   )
   CLUSTER BY
     customer_id
     OPTIONS (
       description = 'a table clustered by customer_id');
   

3. Klicken Sie auf Ausführen.

Informationen zum Ausführen von Abfragen finden Sie unter Interaktive Abfrage ausführen.

bq

Verwenden Sie den Befehl bq mk mit den folgenden Flags:

• --table (oder das Kürzel -t).
• --schema. Sie können die Schemadefinition der Tabelle inline oder über eine JSON-Schemadatei bereitstellen.
• --clustering_fields. Sie können bis zu vier Clustering-Spalten angeben.

Zu den optionalen Parametern gehören --expiration, --description, --time_partitioning_type, --time_partitioning_field, --time_partitioning_expiration, --destination_kms_key und --label.

Wenn Sie eine Tabelle in einem anderen Projekt als dem Standardprojekt erstellen, fügen Sie dem Dataset die Projekt-ID im folgenden Format hinzu: project_id:dataset.

--destination_kms_key wird hier nicht angewendet. Weitere Informationen zu --destination_kms_key finden Sie unter Vom Kunden verwaltete Verschlüsselungsschlüssel.

Geben Sie den folgenden Befehl ein, um eine leere geclusterte Tabelle mit einer Schemadefinition zu erstellen:

bq mk \
    --table \
    --expiration INTEGER1 \
    --schema SCHEMA \
    --clustering_fields CLUSTER_COLUMNS \
    --description "DESCRIPTION" \
    --label KEY:VALUE,KEY:VALUE \
    PROJECT_ID:DATASET.TABLE

Ersetzen Sie Folgendes:

• INTEGER1: die Standardlebensdauer der Tabelle in Sekunden. Der Mindestwert beträgt 3.600 Sekunden bzw. eine Stunde. Die Ablaufzeit entspricht der aktuellen UTC-Zeit plus dem ganzzahligen Wert. Wenn Sie die Ablaufzeit beim Erstellen der Tabelle festlegen, wird die Standardeinstellung des Datasets für die Ablaufzeit der Tabelle ignoriert. Wenn Sie diesen Wert festlegen, wird die Tabelle nach der angegebenen Zeit gelöscht.
• SCHEMA: eine Inline-Schemadefinition im Format COLUMN:DATA_TYPE,COLUMN:DATA_TYPE oder der Pfad zur JSON-Schemadatei auf Ihrem lokalen Computer.
• CLUSTER_COLUMNS: eine durch Kommas getrennte Liste mit bis zu vier Clustering-Spalten. Die Liste darf keine Leerzeichen enthalten.
• DESCRIPTION: eine Beschreibung der Tabelle in Anführungszeichen.
• KEY:VALUE: das Schlüssel/Wert-Paar, das ein Label darstellt. Mit einer durch Kommas getrennten Liste können Sie mehrere Labels eingeben.
• PROJECT_ID: Ihre Projekt-ID.
• DATASET: ein Dataset in Ihrem Projekt.
• TABLE: Der Name der Tabelle, die Sie erstellen.

Wenn Sie das Schema in der Befehlszeile angeben, können Sie den Typ RECORD (STRUCT) und die Spaltenbeschreibung nicht einfügen. Außerdem ist es dann nicht möglich, den Modus der Spalte anzugeben. Als Modus ist standardmäßig NULLABLE eingestellt. Wenn Sie Beschreibungen, Modi und RECORD-Typen einbinden möchten, müssen Sie stattdessen eine JSON-Schemadatei bereitstellen.

Beispiele:

Mit dem folgenden Befehl können Sie eine geclusterte Tabelle mit dem Namen myclusteredtable in mydataset in Ihrem Standardprojekt erstellen. Die Ablaufzeit der Tabelle ist auf 2.592.000 (ein 30-Tage-Monat), die Beschreibung auf This is my clustered table und das Label auf organization:development festgelegt. Der Befehl verwendet das Kürzel -t anstelle von --table.

So wird das Schema inline angegeben: timestamp:timestamp,customer_id:string,transaction_amount:float. Das angegebene Clustering-Feld customer_id wird verwendet, um die Tabelle zu clustern.

bq mk \
    -t \
    --expiration 2592000 \
    --schema 'timestamp:timestamp,customer_id:string,transaction_amount:float' \
    --clustering_fields customer_id \
    --description "This is my clustered table" \
    --label org:dev \
    mydataset.myclusteredtable

Geben Sie den folgenden Befehl ein, um eine geclusterte Tabelle mit dem Namen myclusteredtable in myotherproject und nicht in Ihrem Standardprojekt zu erstellen. Die Beschreibung ist auf This is my clustered table und das Label auf organization:development festgelegt. Der Befehl verwendet das Kürzel -t anstelle von --table. Dieser Befehl legt keinen Tabellenablauf fest. Wenn im Dataset ein Standardablaufwert für Tabellen festgelegt ist, wird dieser angewendet. Wenn im Dataset kein Standardtabellenablauf festgelegt ist, läuft die Tabelle nie ab.

Das Schema wird in der lokalen JSON-Datei /tmp/myschema.json angegeben. Das Feld customer_id dient zum Clustern der Tabelle.

bq mk \
    -t \
    --expiration 2592000 \
    --schema /tmp/myschema.json \
    --clustering_fields=customer_id \
    --description "This is my clustered table" \
    --label org:dev \
    myotherproject:mydataset.myclusteredtable

Nachdem die Tabelle erstellt wurde, können Sie die Beschreibung und die Labels der Tabelle aktualisieren.

Terraform

Verwenden Sie die Ressource google_bigquery_table:

Hinweis: Zum Erstellen von BigQuery-Objekten mit Terraform müssen Sie die Cloud Resource Manager API aktivieren.

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

Im folgenden Beispiel wird eine Tabelle mit dem Namen mytable erstellt, die über die Spalten ID und Created geclustert ist:

resource "google_bigquery_dataset" "default" {
  dataset_id                      = "mydataset"
  default_partition_expiration_ms = 2592000000  # 30 days
  default_table_expiration_ms     = 31536000000 # 365 days
  description                     = "dataset description"
  location                        = "US"
  max_time_travel_hours           = 96 # 4 days

  labels = {
    billing_group = "accounting",
    pii           = "sensitive"
  }
}

resource "google_bigquery_table" "default" {
  dataset_id          = google_bigquery_dataset.default.dataset_id
  table_id            = "mytable"
  deletion_protection = false # set to "true" in production

  clustering = ["ID", "Created"]

  schema = <<EOF
[
  {
    "name": "ID",
    "type": "INT64",
    "description": "Item ID"
  },
  {
    "name": "Item",
    "type": "STRING",
    "mode": "NULLABLE"
  },
 {
   "name": "Created",
   "type": "TIMESTAMP"
 }
]
EOF

}

Führen Sie die Schritte in den folgenden Abschnitten aus, um Ihre Terraform-Konfiguration auf ein Google Cloud-Projekt anzuwenden.

Cloud Shell vorbereiten

1. Rufen Sie Cloud Shell auf.

2. Legen Sie das Google Cloud-Standardprojekt fest, auf das Sie Ihre Terraform-Konfigurationen anwenden möchten.

   Sie müssen diesen Befehl nur einmal pro Projekt und in jedem beliebigen Verzeichnis ausführen.

   export GOOGLE_CLOUD_PROJECT=PROJECT_ID

   Umgebungsvariablen werden überschrieben, wenn Sie in der Terraform-Konfigurationsdatei explizite Werte festlegen.

Verzeichnis vorbereiten

Jede Terraform-Konfigurationsdatei muss ein eigenes Verzeichnis haben (auch als Stammmodul bezeichnet).

1. Erstellen Sie in Cloud Shell ein Verzeichnis und eine neue Datei in diesem Verzeichnis. Der Dateiname muss die Erweiterung .tf haben, z. B. main.tf. In dieser Anleitung wird die Datei als main.tf bezeichnet.

   mkdir DIRECTORY && cd DIRECTORY && touch main.tf

2. Wenn Sie einer Anleitung folgen, können Sie den Beispielcode in jedem Abschnitt oder Schritt kopieren.

   Kopieren Sie den Beispielcode in das neu erstellte main.tf.

   Kopieren Sie optional den Code aus GitHub. Dies wird empfohlen, wenn das Terraform-Snippet Teil einer End-to-End-Lösung ist.

3. Prüfen und ändern Sie die Beispielparameter, die auf Ihre Umgebung angewendet werden sollen.
4. Speichern Sie die Änderungen.
5. Initialisieren Sie Terraform. Dies ist nur einmal für jedes Verzeichnis erforderlich.

   terraform init

   Fügen Sie optional die Option -upgrade ein, um die neueste Google-Anbieterversion zu verwenden:

   terraform init -upgrade

Änderungen anwenden

1. Prüfen Sie die Konfiguration und prüfen Sie, ob die Ressourcen, die Terraform erstellen oder aktualisieren wird, Ihren Erwartungen entsprechen:

   terraform plan

   Korrigieren Sie die Konfiguration nach Bedarf.

2. Wenden Sie die Terraform-Konfiguration an. Führen Sie dazu den folgenden Befehl aus und geben Sie yes an der Eingabeaufforderung ein:

   terraform apply

   Warten Sie, bis Terraform die Meldung „Apply complete“ anzeigt.

3. Öffnen Sie Ihr Google Cloud-Projekt, um die Ergebnisse aufzurufen. Rufen Sie in der Google Cloud Console Ihre Ressourcen in der Benutzeroberfläche auf, um sicherzustellen, dass Terraform sie erstellt oder aktualisiert hat.

Hinweis: Bei Terraform-Beispielen wird in der Regel davon ausgegangen, dass die erforderlichen APIs in Ihrem Google Cloud-Projekt aktiviert sind.

API

Rufen Sie die Methode tables.insert mit einer definierten Tabellenressource auf, die die Attribute clustering.fields und schema festlegt.

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 table to create.
# table_id = "your-project.your_dataset.your_table_name"

schema = [
    bigquery.SchemaField("full_name", "STRING"),
    bigquery.SchemaField("city", "STRING"),
    bigquery.SchemaField("zipcode", "INTEGER"),
]

table = bigquery.Table(table_id, schema=schema)
table.clustering_fields = ["city", "zipcode"]
table = client.create_table(table)  # Make an API request.
print(
    "Created clustered table {}.{}.{}".format(
        table.project, table.dataset_id, table.table_id
    )
)

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"
	"time"

	"cloud.google.com/go/bigquery"
)

// createTableClustered demonstrates creating a BigQuery table with advanced properties like
// partitioning and clustering features.
func createTableClustered(projectID, datasetID, tableID string) error {
	// projectID := "my-project-id"
	// datasetID := "mydatasetid"
	// tableID := "mytableid"
	ctx := context.Background()

	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %v", err)
	}
	defer client.Close()

	sampleSchema := bigquery.Schema{
		{Name: "timestamp", Type: bigquery.TimestampFieldType},
		{Name: "origin", Type: bigquery.StringFieldType},
		{Name: "destination", Type: bigquery.StringFieldType},
		{Name: "amount", Type: bigquery.NumericFieldType},
	}
	metaData := &bigquery.TableMetadata{
		Schema: sampleSchema,
		TimePartitioning: &bigquery.TimePartitioning{
			Field:      "timestamp",
			Expiration: 90 * 24 * time.Hour,
		},
		Clustering: &bigquery.Clustering{
			Fields: []string{"origin", "destination"},
		},
	}
	tableRef := client.Dataset(datasetID).Table(tableID)
	if err := tableRef.Create(ctx, metaData); err != nil {
		return err
	}
	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.Clustering;
import com.google.cloud.bigquery.Field;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.StandardSQLTypeName;
import com.google.cloud.bigquery.StandardTableDefinition;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TableInfo;
import com.google.cloud.bigquery.TimePartitioning;
import com.google.common.collect.ImmutableList;

public class CreateClusteredTable {
  public static void runCreateClusteredTable() {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    createClusteredTable(datasetName, tableName);
  }

  public static void createClusteredTable(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();

      TableId tableId = TableId.of(datasetName, tableName);

      TimePartitioning partitioning = TimePartitioning.of(TimePartitioning.Type.DAY);

      Schema schema =
          Schema.of(
              Field.of("name", StandardSQLTypeName.STRING),
              Field.of("post_abbr", StandardSQLTypeName.STRING),
              Field.of("date", StandardSQLTypeName.DATE));

      Clustering clustering =
          Clustering.newBuilder().setFields(ImmutableList.of("name", "post_abbr")).build();

      StandardTableDefinition tableDefinition =
          StandardTableDefinition.newBuilder()
              .setSchema(schema)
              .setTimePartitioning(partitioning)
              .setClustering(clustering)
              .build();
      TableInfo tableInfo = TableInfo.newBuilder(tableId, tableDefinition).build();

      bigquery.create(tableInfo);
      System.out.println("Clustered table created successfully");
    } catch (BigQueryException e) {
      System.out.println("Clustered table was not created. \n" + e.toString());
    }
  }
}

Geclusterte Tabelle aus einem Abfrageergebnis erstellen

Es gibt zwei Möglichkeiten, eine geclusterte Tabelle aus einem Abfrageergebnis anzulegen:

• Schreiben Sie die Ergebnisse in eine neue Zieltabelle und geben Sie die Clustering-Spalten an.
• Mithilfe einer DDL-Anweisung des Typs CREATE TABLE AS SELECT. Weitere Informationen zu dieser Methode finden Sie auf der Seite DDL-Anweisungen verwenden unter Geclusterte Tabelle aus dem Ergebnis einer Abfrage erstellen.

Eine geclusterte Tabelle kann durch Abfrage einer partitionierten Tabelle oder einer nicht partitionierten Tabelle erstellt werden. Es ist nicht möglich, eine vorhandene Tabelle mithilfe von Abfrageergebnissen in eine geclusterte Tabelle zu ändern.

Wenn Sie eine geclusterte Tabelle aus einem Abfrageergebnis erstellen, müssen Sie Standard-SQL verwenden. Legacy-SQL wird derzeit nicht zum Abfragen geclusterter Tabellen oder zum Schreiben von Abfrageergebnissen in geclusterte Tabellen unterstützt.

SQL

Verwenden Sie die DDL-Anweisung CREATE TABLE mit der Option CLUSTER BY, um eine geclusterte Tabelle aus einem Abfrageergebnis zu erstellen. Im folgenden Beispiel wird eine neue Tabelle erstellt, die nach customer_id geclustert ist. Dazu wird eine vorhandene nicht geclusterte Tabelle abgefragt:

1. Öffnen Sie in der Google Cloud Console die Seite BigQuery.

   BigQuery aufrufen

2. Geben Sie im Abfrageeditor die folgende Anweisung ein:

   CREATE TABLE mydataset.clustered_table
   (
     customer_id STRING,
     transaction_amount NUMERIC
   )
   CLUSTER BY
     customer_id
   AS (
     SELECT * FROM mydataset.unclustered_table
   );
   

3. Klicken Sie auf Ausführen.

Informationen zum Ausführen von Abfragen finden Sie unter Interaktive Abfrage ausführen.

bq

Geben Sie den folgenden Befehl ein, um eine neue geclusterte Zieltabelle aus einem Abfrageergebnis zu erstellen:

bq --location=LOCATION query \
    --use_legacy_sql=false 'QUERY'

Dabei gilt:

• LOCATION: Name Ihres Standorts. Das Flag --location ist optional. Wenn Sie BigQuery beispielsweise in der Region Tokio verwenden, können Sie für das Flag den Wert asia-northeast1 festlegen. Mit der Datei .bigqueryrc können Sie einen Standardwert für den Standort festlegen.
• QUERY: eine Abfrage in der GoogleSQL-Syntax. Derzeit können Sie kein Legacy-SQL zum Abfragen von geclusterten Tabellen oder zum Schreiben von Abfrageergebnissen in geclusterte Tabellen verwenden. Die Abfrage kann die DDL CREATE TABLE enthalten, mit der die Optionen zum Erstellen Ihrer geclusterten Tabelle angegeben werden. Sie können DDL anstelle der einzelnen Befehlszeilen-Flags verwenden.

Beispiele:

Mit dem folgenden Befehl schreiben Sie Abfrageergebnisse in eine Zieltabelle mit dem Namen myclusteredtable in mydataset. mydataset befindet sich in Ihrem Standardprojekt. Mit der Abfrage werden Daten aus "mytable", einer nicht partitionierten Tabelle, abgerufen. Die Spalte customer_id der Tabelle dient dazu, die Tabelle zu clustern. Die Spalte timestamp der Tabelle wird zum Erstellen einer partitionierten Tabelle verwendet.

bq query --use_legacy_sql=false \
    'CREATE TABLE
       mydataset.myclusteredtable
     PARTITION BY
       DATE(timestamp)
     CLUSTER BY
       customer_id
     AS (
       SELECT
         *
       FROM
         `mydataset.mytable`
     );'

API

Rufen Sie zum Speichern von Abfrageergebnissen in einer geclusterten Tabelle die Methode jobs.insert auf, konfigurieren Sie einen query-Job und fügen Sie die DDL CREATE TABLE ein, mit der die geclusterte Tabelle erstellt wird.

Geben Sie im Abschnitt jobReference der Jobressource Ihren Standort im Attribut location an.

Geclusterte Tabelle beim Laden von Daten erstellen

Sie können eine geclusterte Tabelle erstellen, indem Sie beim Laden von Daten in eine neue Tabelle Clustering-Spalten angeben. Sie müssen keine leere Tabelle erstellen, bevor Sie die Daten laden. Sie können die geclusterte Tabelle erstellen und gleichzeitig Daten in diese Tabelle laden.

Weitere Informationen zum Laden von Daten finden Sie unter Einführung in das Laden von Daten in BigQuery.

So definieren Sie Clustering, wenn Sie einen Ladejob definieren:

SQL

Verwenden Sie die Anweisung LOAD DATA. Im folgenden Beispiel werden AVRO-Daten geladen, um eine Tabelle zu erstellen, die nach dem Feld transaction_date partitioniert und nach dem Feld customer_id geclustert ist. Außerdem werden die Partitionen so konfiguriert, dass sie nach drei Tagen ablaufen.

1. Öffnen Sie in der Google Cloud Console die Seite BigQuery.

   BigQuery aufrufen

2. Geben Sie im Abfrageeditor die folgende Anweisung ein:

   LOAD DATA INTO mydataset.mytable
   PARTITION BY transaction_date
   CLUSTER BY customer_id
     OPTIONS (
       partition_expiration_days = 3)
   FROM FILES(
     format = 'AVRO',
     uris = ['gs://bucket/path/file.avro']);
   

3. Klicken Sie auf Ausführen.

Informationen zum Ausführen von Abfragen finden Sie unter Interaktive Abfrage ausführen.

API

Wenn Sie beim Erstellen einer Tabelle über einen Ladejob die Clustering-Konfiguration definieren, können Sie die Clustering-Attribute für die Tabelle darstellen.

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"

	"cloud.google.com/go/bigquery"
)

// importClusteredTable demonstrates creating a table from a load job and defining partitioning and clustering
// properties.
func importClusteredTable(projectID, destDatasetID, destTableID 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()

	gcsRef := bigquery.NewGCSReference("gs://cloud-samples-data/bigquery/sample-transactions/transactions.csv")
	gcsRef.SkipLeadingRows = 1
	gcsRef.Schema = bigquery.Schema{
		{Name: "timestamp", Type: bigquery.TimestampFieldType},
		{Name: "origin", Type: bigquery.StringFieldType},
		{Name: "destination", Type: bigquery.StringFieldType},
		{Name: "amount", Type: bigquery.NumericFieldType},
	}
	loader := client.Dataset(destDatasetID).Table(destTableID).LoaderFrom(gcsRef)
	loader.TimePartitioning = &bigquery.TimePartitioning{
		Field: "timestamp",
	}
	loader.Clustering = &bigquery.Clustering{
		Fields: []string{"origin", "destination"},
	}
	loader.WriteDisposition = bigquery.WriteEmpty

	job, err := loader.Run(ctx)
	if err != nil {
		return err
	}
	status, err := job.Wait(ctx)
	if err != nil {
		return err
	}

	if status.Err() != nil {
		return fmt.Errorf("job completed with error: %v", status.Err())
	}
	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.Clustering;
import com.google.cloud.bigquery.Field;
import com.google.cloud.bigquery.FormatOptions;
import com.google.cloud.bigquery.Job;
import com.google.cloud.bigquery.JobInfo;
import com.google.cloud.bigquery.LoadJobConfiguration;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.StandardSQLTypeName;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TimePartitioning;
import com.google.common.collect.ImmutableList;

public class LoadTableClustered {

  public static void runLoadTableClustered() throws Exception {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    String sourceUri = "/path/to/file.csv";
    loadTableClustered(datasetName, tableName, sourceUri);
  }

  public static void loadTableClustered(String datasetName, String tableName, String sourceUri)
      throws Exception {
    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();

      TableId tableId = TableId.of(datasetName, tableName);

      Schema schema =
          Schema.of(
              Field.of("name", StandardSQLTypeName.STRING),
              Field.of("post_abbr", StandardSQLTypeName.STRING),
              Field.of("date", StandardSQLTypeName.DATE));

      TimePartitioning partitioning = TimePartitioning.of(TimePartitioning.Type.DAY);

      Clustering clustering =
          Clustering.newBuilder().setFields(ImmutableList.of("name", "post_abbr")).build();

      LoadJobConfiguration loadJobConfig =
          LoadJobConfiguration.builder(tableId, sourceUri)
              .setFormatOptions(FormatOptions.csv())
              .setSchema(schema)
              .setTimePartitioning(partitioning)
              .setClustering(clustering)
              .build();

      Job loadJob = bigquery.create(JobInfo.newBuilder(loadJobConfig).build());

      // Load data from a GCS parquet file into the table
      // Blocks until this load table job completes its execution, either failing or succeeding.
      Job completedJob = loadJob.waitFor();

      // Check for errors
      if (completedJob == null) {
        throw new Exception("Job not executed since it no longer exists.");
      } else if (completedJob.getStatus().getError() != null) {
        // You can also look at queryJob.getStatus().getExecutionErrors() for all
        // errors, not just the latest one.
        throw new Exception(
            "BigQuery was unable to load into the table due to an error: \n"
                + loadJob.getStatus().getError());
      }
      System.out.println("Data successfully loaded into clustered table during load job");
    } catch (BigQueryException | InterruptedException e) {
      System.out.println("Data not loaded into clustered table during load job \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 table to create.
# table_id = "your-project.your_dataset.your_table_name"

job_config = bigquery.LoadJobConfig(
    skip_leading_rows=1,
    source_format=bigquery.SourceFormat.CSV,
    schema=[
        bigquery.SchemaField("timestamp", bigquery.SqlTypeNames.TIMESTAMP),
        bigquery.SchemaField("origin", bigquery.SqlTypeNames.STRING),
        bigquery.SchemaField("destination", bigquery.SqlTypeNames.STRING),
        bigquery.SchemaField("amount", bigquery.SqlTypeNames.NUMERIC),
    ],
    time_partitioning=bigquery.TimePartitioning(field="timestamp"),
    clustering_fields=["origin", "destination"],
)

job = client.load_table_from_uri(
    ["gs://cloud-samples-data/bigquery/sample-transactions/transactions.csv"],
    table_id,
    job_config=job_config,
)

job.result()  # Waits for the job to complete.

table = client.get_table(table_id)  # Make an API request.
print(
    "Loaded {} rows and {} columns to {}".format(
        table.num_rows, len(table.schema), table_id
    )
)

Zugriff auf geclusterte Tabellen steuern

Wenn Sie den Zugriff auf Tabellen und Ansichten konfigurieren möchten, können Sie einer Entität auf den folgenden Ebenen, geordnet vom größten zulässigen Ressourcenbereich zum kleinsten, eine IAM-Rolle zuweisen:

• auf einer übergeordneten Ebene in der Google Cloud-Ressourcenhierarchie, z. B. der Projekt-, Ordner- oder Organisationsebene
• auf der Dataset-Ebene
• auf der Tabellen- oder Ansichtsebene

Sie können den Datenzugriff innerhalb von Tabellen auch mithilfe der folgenden Methoden einschränken:

• Sicherheit auf Spaltenebene
• Spaltendatenmaskierung
• Sicherheit auf Zeilenebene

Der Zugriff über eine durch IAM geschützte Ressource ist additiv. Wenn eine Entität beispielsweise keinen Zugriff auf übergeordneter Ebene (z. B. Projektebene) hat, können Sie der Entität Zugriff auf Dataset-Ebene gewähren. Die Entität hat dann Zugriff auf die Tabellen und Ansichten im Dataset. Wenn die Entität dagegen weder Zugriff auf übergeordneter Ebene noch auf Dataset-Ebene hat, können Sie der Entität Zugriff auf Tabellen- oder Ansichtsebene gewähren.

Wenn Sie in der Google Cloud-Ressourcenhierarchie IAM-Rollen auf einer höheren Ebene zuweisen, z. B. auf Projekt-, Ordner- oder Organisationsebene, erhält die Entität Zugriff auf eine Vielzahl von Ressourcen. Wenn Sie beispielsweise einer Entität auf Projektebene eine Rolle zuweisen, erhält diese Entität Berechtigungen, die für alle Datasets im gesamten Projekt gelten.

Durch das Zuweisen einer Rolle auf Dataset-Ebene werden die Vorgänge angegeben, die eine Entität für Tabellen und Ansichten in diesem bestimmten Dataset ausführen darf, auch wenn die Entität keinen Zugriff auf einer höheren Ebene hat. Informationen zum Konfigurieren von Zugriffssteuerungen auf Dataset-Ebene finden Sie unter Zugriff auf Datasets steuern.

Durch das Zuweisen einer Rolle auf Tabellen- oder Ansichtsebene werden die Vorgänge angegeben, die eine Entität für bestimmte Tabellen und Ansichten ausführen darf, auch wenn die Entität keinen Zugriff auf einer höheren Ebene hat. Informationen zum Konfigurieren von Zugriffssteuerungen auf Tabellenebene finden Sie unter Zugriff auf Tabellen und Ansichten steuern.

Sie können auch benutzerdefinierte IAM-Rollen erstellen. Wenn Sie eine benutzerdefinierte Rolle erstellen, hängen die erteilten Berechtigungen von den spezifischen Vorgängen ab, die die Entität ausführen soll.

Sie können für eine durch IAM geschützte Ressource keine "Ablehnen"-Berechtigung festlegen.

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

Geclusterte Tabellen verwenden

Informationen zu geclusterten Tabellen abrufen

So erhalten Sie Informationen zu Tabellen:

• Google Cloud Console verwenden
• Den Befehl bq show des bq-Befehlszeilentools verwenden
• Durch Aufruf der API-Methode tables.get
• Abfragen von INFORMATION_SCHEMA-Ansichten

Erforderliche Berechtigungen

Sie benötigen mindestens Berechtigungen vom Typ bigquery.tables.get, um Informationen zu Tabellen abzurufen. Die folgenden vordefinierten IAM-Rollen enthalten bigquery.tables.get-Berechtigungen:

• bigquery.metadataViewer
• 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 dem bigquery.dataOwner-Zugriff hat der Nutzer die Möglichkeit, Informationen über Tabellen in einem Dataset abzurufen.

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

Informationen zu geclusterten Tabellen abrufen

So rufen Sie Informationen zu einer geclusterten Tabelle ab:

Console

1. Wechseln Sie in der Google Cloud Console zum Bereich Ressourcen. Klicken Sie auf den Dataset-Namen, um das Dataset einzublenden. Klicken Sie dann auf den Namen der Tabelle, die Sie aufrufen möchten.

2. Klicken Sie auf Details. Auf dieser Seite werden die Details der Tabelle einschließlich der Cluster-Spalten angezeigt.

   

SQL

Bei geclusterten Tabellen können Sie in der Ansicht INFORMATION_SCHEMA.COLUMNS die Spalte CLUSTERING_ORDINAL_POSITION abfragen, um den 1-indexierten Versatz der Clustering-Spalten in der Tabelle zu ermitteln:

1. Öffnen Sie in der Google Cloud Console die Seite BigQuery.

   BigQuery aufrufen

2. Geben Sie im Abfrageeditor die folgende Anweisung ein:

   CREATE TABLE mydataset.data (column1 INT64, column2 INT64)
   CLUSTER BY column1, column2;
   SELECT
     column_name, clustering_ordinal_position
   FROM
     mydataset.INFORMATION_SCHEMA.COLUMNS;
   

3. Klicken Sie auf Ausführen.

Informationen zum Ausführen von Abfragen finden Sie unter Interaktive Abfrage ausführen.

Die Ordinalposition des Clusters ist 1 für column1 und 2 für column2. Weitere Tabellenmetadaten sind über die Ansichten TABLES, TABLE_OPTIONS, COLUMNS und COLUMN_FIELD_PATH in INFORMATION_SCHEMA verfügbar.

bq

Führen Sie den Befehl bq show aus, um alle Tabelleninformationen aufzurufen. Verwenden Sie das Flag --schema, wenn nur Informationen zum Tabellenschema angezeigt werden sollen. Mit dem Flag --format kann die Ausgabe gesteuert werden.

Wenn Sie Informationen zu einer Tabelle in einem anderen Projekt als Ihrem Standardprojekt abrufen, fügen Sie dem Dataset die Projekt-ID im folgenden Format hinzu: project_id:dataset

bq show \
    --schema \
    --format=prettyjson \
    PROJECT_ID:DATASET.TABLE

Ersetzen Sie Folgendes:

• PROJECT_ID: Ihre Projekt-ID
• DATASET: der Name des Datasets
• TABLE: der Name der Tabelle

Beispiele:

Geben Sie den folgenden Befehl ein, um alle Informationen zu myclusteredtable in mydataset aufzurufen. mydataset in Ihrem Standardprojekt.

bq show --format=prettyjson mydataset.myclusteredtable

Die Ausgabe sollte so in etwa aussehen:

{
  "clustering": {
    "fields": [
      "customer_id"
    ]
  },
...
}

API

Mithilfe der Methode bigquery.tables.get geben Sie alle relevanten Parameter an.

Geclusterte Tabellen in einem Dataset auflisten

So können Sie geclusterte Tabellen in Datasets auflisten:

• Google Cloud Console verwenden
• Den Befehl bq ls des bq-Befehlszeilentools verwenden
• Durch Aufrufen der API-Methode tables.list
• Mithilfe der Clientbibliotheken
• Durch Abfragen der Spalte CLUSTERING_ORDINAL_POSITION in der Ansicht INFORMATION_SCHEMA.COLUMNS

Die Berechtigungen und die Vorgehensweise zum Auflisten von geclusterten Tabellen sind dieselben wie für partitionierte Tabellen. Weitere Informationen zum Auflisten von Tabellen finden Sie unter Tabellen in einem Dataset auflisten.

Clustering-Spezifikation ändern

Sie können die Clustering-Spezifikationen einer Tabelle ändern oder entfernen oder die Gruppe geclusterter Spalten in einer geclusterten Tabelle ändern. Diese Methode zum Aktualisieren der Gruppe von Clustering-Spalten ist nützlich für Tabellen, die kontinuierliche Streaming-Insert-Anweisungen verwenden, da diese Tabellen durch andere Methoden nicht einfach ausgetauscht werden können.

Führen Sie die folgenden Schritte aus, um eine neue Clustering-Spezifikation auf nicht partitionierte oder partitionierte Tabellen anzuwenden.

1. Aktualisieren Sie im bq-Tool die Clustering-Spezifikation Ihrer Tabelle, damit sie dem neuen Clustering entspricht:

    bq update --clustering_fields=CLUSTER_COLUMN DATASET.ORIGINAL_TABLE 

   Ersetzen Sie Folgendes:

   • CLUSTER_COLUMN: die Spalte, nach der Sie Cluster gruppieren, z. B. mycolumn
   • DATASET: der Name des Datasets, das die Tabelle enthält, z. B. mydataset
   • ORIGINAL_TABLE: der Name der ursprünglichen Tabelle, z. B. mytable

   Sie können auch die API-Methode tables.update oder tables.patch aufrufen, um die Clustering-Spezifikation zu ändern.

2. Führen Sie die folgende UPDATE-Anweisung aus, um alle Zeilen gemäß der neuen Clustering-Spezifikation zu clustern:

   UPDATE DATASET.ORIGINAL_TABLE SET CLUSTER_COLUMN=CLUSTER_COLUMN WHERE true
   

   Hinweis: Wenn eine neue Clustering-Spezifikation auf eine Tabelle im Langzeitspeicher angewendet wird, werden für die Tabelle wieder die aktiven Speicherpreise verwendet. Weitere Informationen finden Sie unter Speicherpreise.

Tabellensicherheit

Informationen zum Steuern des Zugriffs auf Tabellen in BigQuery finden Sie unter Einführung in die Tabellenzugriffssteuerung.

Nächste Schritte

• Geclusterte Tabellen abfragen
• Einführung in partitionierte Tabellen
• Weitere Informationen finden Sie unter Partitionierte Tabellen erstellen.
• Einen Überblick über INFORMATION_SCHEMAfinden Sie unter Einführung in INFORMATION_SCHEMA von BigQuery