Extern partitionierte Daten abfragen

BigQuery unterstützt die Abfrage extern partitionierter Daten in den Formaten Avro, Parquet, ORC, JSON und CSV, die unter Verwendung eines Standardlayouts für Hive-Partitionierung in Cloud Storage gespeichert wurden. Die Unterstützung für Hive-Partitionierung kann durch Festlegung der entsprechenden Optionen in der Tabellendefinitionsdatei aktiviert werden. Sie können Tabellen mit der Cloud Console, dem bq-Befehlszeilentool und der BigQuery API erstellen und ändern.

Eine Anleitung zum Abfragen von verwalteten partitionierten Tabellen finden Sie unter Einführung in partitionierte Tabellen.

Beschränkungen

  • Bei der Unterstützung der Hive-Partitionierung wird vorausgesetzt, dass alle URI unmittelbar vor der Partitionscodierung ein gemeinsames Präfix für den Quell-URI haben: gs://BUCKET/PATH_TO_TABLE/.
  • Es wird angenommen, dass in der Verzeichnisstruktur einer Hive-partitionierten Tabelle dieselben Partitionierungsschlüssel in derselben Reihenfolge vorhanden sind. In einer Tabelle darf es dabei höchstens zehn Partitionierungsschlüssel geben.
  • Die Daten müssen einem Standardlayout für Hive-Partitionierung entsprechen.
  • Es darf keine Überschneidungen zwischen den Hive-Partitionierungsschlüsseln und den Spalten in den zugrunde liegenden Dateien geben.
  • Für die Abfrage externer Quellen von Daten, die in Cloud Storage gespeichert sind, gelten die üblichen Einschränkungen.
  • Es wird ausschließlich Standard-SQL unterstützt.

Unterstützte Datenlayouts

Die Daten müssen einem Standardlayout für Hive-Partitionierung entsprechen. Die folgenden Dateien folgen beispielsweise dem Standardlayout. Die Schlüssel/Wert-Paare werden als Verzeichnisse mit dem Zeichen = als Trennzeichen konfiguriert und die Partitionierungsschlüssel haben immer dieselbe Reihenfolge:

gs://myBucket/myTable/dt=2019-10-31/lang=en/foo
gs://myBucket/myTable/dt=2018-10-31/lang=fr/bar

Das gemeinsame Präfix für den Quell-URI in diesem Beispiel ist gs://myBucket/myTable.

Nicht unterstützte Datenlayouts

Wenn die Namen der Partitionierungsschlüssel nicht im Verzeichnispfad codiert sind, schlägt die Partitionierung des Partitionsschemas fehl. Sehen Sie sich zum Beispiel den folgenden Pfad an, in dem die Namen der Partitionsschlüssel nicht codiert sind:

gs://myBucket/myTable/2019-10-31/en/foo

Dateien, deren Schema nicht in einheitlicher Reihenfolge angegeben ist, werden ebenfalls nicht erkannt. Sehen Sie sich zum Beispiel die folgenden beiden Dateien an, bei denen die Partitionierungsschlüssel in umgekehrter Reihenfolge codiert sind:

gs://myBucket/myTable/dt=2019-10-31/lang=en/foo
gs://myBucket/myTable/lang=fr/dt=2018-10-31/bar

Für diese Dateien schlägt die Partitionierung des Partitionierungsschemas fehl.

Arten der Erkennung des Partitionierungsschemas

Hive-Partitionierungsschlüssel werden beim Laden von Daten aus Cloud Storage als normale Spalten angezeigt. BigQuery unterstützt drei Arten der Erkennung von Hive-Partitionsschemas:

  • AUTO: Schlüsselnamen und -typen werden automatisch erkannt. Die folgenden Typen können erkannt werden: STRING, INTEGER, DATE und TIMESTAMP.
  • STRINGS: Schlüsselnamen werden automatisch in den Typ STRING konvertiert.
  • CUSTOM: Das Schema des Partitionierungsschlüssels ist entsprechend der Angabe im Präfix des Quell-URI codiert.

Benutzerdefiniertes Schema für Partitionierungsschlüssel bereitstellen

Wenn Sie ein CUSTOM-Schema verwenden möchten, müssen Sie das Schema im Feld für das Quell-URI-Präfix angeben. Mit einem CUSTOM-Schema können Sie den Typ für jeden Partitionierungsschlüssel angeben. Wenn sich die Werte nicht korrekt als der angegebene Typ parsen lassen, tritt beim Laden ein Fehler auf.

Wenn Sie beispielsweise das Flag source_uri_prefix auf gs://myBucket/myTable/{dt:DATE}/{val:STRING} setzen, behandelt BigQuery val als STRING, dt als DATE und verwendet gs://myBucket/myTable als Quell-URI-Präfix für die übereinstimmenden Dateien

Partitionsbereinigung

BigQuery bereinigt Partitionen nach Möglichkeit mithilfe von Abfrageprädikaten für die Partitionierungsschlüssel. So vermeiden Sie es, dass BigQuery unnötige Dateien liest, was die Leistung verbessert.

Prädikatfilter für Partitionsschlüssel in Abfragen anfordern

Wenn Sie eine extern partitionierte Tabelle erstellen, können Sie die Verwendung von Prädikatfiltern für Partitionsschlüssel anfordern, wenn Sie die Option requirePartitionFilter unter HivePartitioningOptions aktivieren.

Wenn diese Option aktiviert ist, führen Versuche, die extern partitionierte Tabelle ohne Angabe einer WHERE-Klausel abzufragen, zu folgendem Fehler: Cannot query over table <table_name> without a filter over column(s) <partition key names> that can be used for partition elimination.

Externe Tabelle für Hive-partitionierte Daten erstellen

So können Sie eine externe Tabelle für Hive-partitionierte Daten erstellen:

  • Mit der Cloud Console
  • Mit dem bq-Befehlszeilentool:
  • Mithilfe der Clientbibliotheken

Console

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

    Zur Seite "BigQuery"

  2. Maximieren Sie im Bereich Explorer Ihr Projekt und wählen Sie ein Dataset aus.

  3. Maximieren Sie die Option Aktionen und klicken Sie auf Öffnen.

  4. Klicken Sie im Detailfeld auf Tabelle erstellen.

  5. Gehen Sie auf der Seite Tabelle erstellen im Abschnitt Quelle so vor:

    1. Wählen Sie in der Drop-down-Liste Tabelle erstellen die Option Cloud Storage aus.
    2. Geben Sie im Feld Datei aus dem Cloud Storage-Bucket auswählen den Pfad zum Cloud Storage-Ordner im Platzhalterformat ein. Beispiel: my_bucket/my_files*
    3. Wählen Sie in der Drop-down-Liste Dateiformat den Dateityp aus.
    4. Klicken Sie das Kästchen Quelldatenpartitionierung an.
    5. Geben Sie im Feld Quell-URI-Präfix auswählen das Cloud Storage-URI-Präfix ein. Beispiel: gs://my_bucket/my_files
    6. Wählen Sie einen Partitionsinferenzmodus aus. Wenn Sie Meine Daten bereitstellen auswählen, geben Sie Schemainformationen für die Partitionierungsschlüssel ein.
    7. Optional: Wenn Sie möchten, dass alle Abfragen für diese Tabelle einen Prädikatfilter verwenden müssen, aktivieren Sie Partitionsfilter anfordern. Weitere Informationen finden Sie unter Prädikatfilter für Partitionsschlüssel in Abfragen erforderlich.
  6. Gehen Sie auf der Seite Tabelle erstellen im Abschnitt Ziel so vor:

    1. Wählen Sie den Projektnamen und den Dataset-Namen aus.
    2. Wählen Sie in der Drop-down-Liste Tabellentyp die Option Externe Tabelle aus.
    3. Geben Sie im Feld Tabellenname einen Namen für die externe Tabelle ein.
  7. Geben Sie auf der Seite Tabelle erstellen im Abschnitt Schema die Schemainformationen ein. BigQuery unterstützt für einige Formate die automatische Schemaerkennung. Weitere Informationen finden Sie unter Automatische Schemaerkennung verwenden.

  8. Klicken Sie auf Tabelle erstellen.

bq

  • Setzen Sie das Flag --hive_partitioning_mode auf AUTO, um die automatische Erkennung des Partitionierungsschlüssels festzulegen. Beispiel:
bq mkdef --source_format=ORC --hive_partitioning_mode=AUTO \
--hive_partitioning_source_uri_prefix=GCS_URI_SHARED_PREFIX \
GCS_URIS > TABLE_DEF_FILE
  • Setzen Sie das Flag --hive_partitioning_mode auf STRINGS, um die Erkennung von Stringschlüsseln für den Partitionierungstyp festzulegen. Beispiel:
bq mkdef --source_format=PARQUET --hive_partitioning_mode=STRINGS \
--hive_partitioning_source_uri_prefix=GCS_URI_SHARED_PREFIX \
GCS_URIS > TABLE_DEF_FILE
  • Zum Festlegen eines benutzerdefinierten Partitionierungsschlüsselschemas mit dem Flag --source_uri_prefix setzen Sie das Flag --hive_partitioning_mode auf CUSTOM. Beispiel:
bq mkdef --source_format=NEWLINE_DELIMITED_JSON --hive_partitioning_mode=CUSTOM \
--hive_partitioning_source_uri_prefix=GCS_URI_SHARED_PREFIX/CUSTOM_SCHEMA_ENCODING \
GCS_URIS FILE_SCHEMA > TABLE_DEF_FILE

Geben Sie für das Flag --hive_partitioning_source_uri_prefix den Teil <var>CUSOM_SCHEMA_ENCODING</var> des Arguments unmittelbar nach dem Abschnitt <var>GCS_URI_SHARED_PREFIX</var> an:

GCS_URI_SHARED_PREFIX/{KEY1:TYPE1}/{KEY2:TYPE2}/...

Beispiel:

--hive_partitioning_source_uri_prefix=gs://myBucket/myTable/{dt:DATE}/{val:STRING}

  • Wenn Sie die Verwendung eines Prädikatfilters zum Zeitpunkt der Abfrage erzwingen möchten, fügen Sie --require_hive_partition_filter=True in den Befehl bq mkdef ein. Zum Beispiel:
bq mkdef --source_format=ORC --hive_partitioning_mode=AUTO \
--hive_partitioning_source_uri_prefix=GCS_URI_SHARED_PREFIX \
--require_hive_partition_filter=True \
GCS_URIS > TABLE_DEF_FILE

API

Wenn Sie die Hive-Partitionierung mithilfe der BigQuery API festlegen möchten, fügen Sie einhivePartitioning-Objekt in derExternalDataConfiguration ein, wenn Sie dieTabellendefinitionsdatei erstellen.

Wenn Sie das Feld hivePartitioningOptions.mode auf CUSTOM festlegen, müssen Sie das Schema für den Partitionierungsschlüssel im Feld hivePartitioningOptions.sourceUriPrefix folgendermaßen codieren: gs://BUCKET/PATH_TO_TABLE/{KEY1:TYPE1}/{KEY2:TYPE2}/...

Wenn Sie die Verwendung eines Prädikatfilters zum Zeitpunkt der Abfrage erzwingen möchten, legen Sie das Feld hivePartitioningOptions.requirePartitionFilter auf true fest.

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.ExternalTableDefinition;
import com.google.cloud.bigquery.FormatOptions;
import com.google.cloud.bigquery.HivePartitioningOptions;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TableInfo;

// Sample to create external table using hive partitioning
public class SetHivePartitioningOptions {

  public static void main(String[] args) {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    String sourceUri = "gs://cloud-samples-data/bigquery/hive-partitioning-samples/customlayout/*";
    String sourceUriPrefix =
        "gs://cloud-samples-data/bigquery/hive-partitioning-samples/customlayout/{pkey:STRING}/";
    setHivePartitioningOptions(datasetName, tableName, sourceUriPrefix, sourceUri);
  }

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

      // Configuring partitioning options
      HivePartitioningOptions hivePartitioningOptions =
          HivePartitioningOptions.newBuilder()
              .setMode("CUSTOM")
              .setRequirePartitionFilter(true)
              .setSourceUriPrefix(sourceUriPrefix)
              .build();

      TableId tableId = TableId.of(datasetName, tableName);
      ExternalTableDefinition customTable =
          ExternalTableDefinition.newBuilder(sourceUri, FormatOptions.parquet())
              .setAutodetect(true)
              .setHivePartitioningOptions(hivePartitioningOptions)
              .build();
      bigquery.create(TableInfo.of(tableId, customTable));
      System.out.println("External table created using hivepartitioningoptions");
    } catch (BigQueryException e) {
      System.out.println("External table was not created" + e.toString());
    }
  }
}

Extern partitionierte Tabellen aktualisieren

Partitionierungsschlüssel müssen bei der Aktualisierung permanenter externer Tabellen unveränderlich sein.