Mit Sammlungen den Überblick behalten Sie können Inhalte basierend auf Ihren Einstellungen speichern und kategorisieren.

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 Google 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 Google 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

Wählen Sie eine der folgenden Optionen aus, um eine externe Tabelle für partitionierte Hive-Daten zu erstellen:

Console

  1. Wechseln Sie in der Google Cloud Console zu BigQuery.

    BigQuery aufrufen

  2. Maximieren Sie im Bereich Explorer Ihr Projekt und wählen Sie ein Dataset aus.
  3. Klicken Sie auf Aktionen anzeigen und dann auf Öffnen.
  4. Klicken Sie im Detailbereich auf Tabelle erstellen.
  5. Geben Sie auf der Seite Tabelle erstellen die folgenden Details an:
    1. Wählen Sie im Abschnitt Quelle unter Tabelle erstellen aus die Option Google Cloud Storage aus. Führen Sie anschließend folgende Schritte aus:
      1. Geben Sie unter Datei aus Cloud Storage-Bucket auswählen den Pfad zum Cloud Storage-Ordner mithilfe von Platzhaltern ein. Beispiel: my_bucket/my_files* Der Cloud Storage-Bucket muss sich am selben Standort wie das Dataset befinden, das die Tabelle enthält, die Sie erstellen, anhängen oder überschreiben möchten.
      2. Wählen Sie in der Liste Dateiformat den Dateityp aus.
      3. Klicken Sie auf das Kästchen Quelldatenpartitionierung und geben Sie dann für Quell-URI-Präfix auswählen das Cloud Storage-URI-Präfix ein. Beispiel: gs://my_bucket/my_files
      4. Wählen Sie im Bereich Partitionsinferenzmodus eine der folgenden Optionen aus:
        • Typen automatisch ableiten: Legen Sie den Erkennungsmodus des Partitionsschemas auf AUTO fest.
        • Alle Spalten sind Strings: Legen Sie den Modus für die Erkennung des Partitionsschemas auf STRINGS fest.
        • Eigene bereitstellen: Legen Sie den Erkennungsmodus für das Partitionsschema auf CUSTOM fest und geben Sie die Schemainformationen für die Partitionierungsschlüssel manuell ein. Weitere Informationen finden Sie unter Benutzerdefiniertes Partitionierungsschlüsselschema bereitstellen.
      5. Optional: Wenn Sie einen Partitionsfilter für alle Abfragen für diese Tabelle benötigen, klicken Sie das Kästchen Partitionsfilter anfordern an. Der Partitionsfilter, den Sie dadurch obligatorisch machen, kann die Kosten senken und die Leistung verbessern. Weitere Informationen finden Sie unter Prädikatfilter für Partitionsschlüssel in Abfragen erforderlich.
    2. Geben Sie im Bereich Ziel die folgenden Details an:
      1. Wählen Sie bei Dataset-Name 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 Externe Tabelle eingestellt ist.
    3. Geben Sie im Abschnitt Schema die Schemadefinition ein.
    4. Wählen Sie Automatisch erkennen aus, um die automatische Erkennung des Schemas zu aktivieren.
    5. Klicken Sie auf Tabelle erstellen.

SQL

Verwenden Sie die DDL-Anweisung CREATE EXTERNAL TABLE.

Das folgende Beispiel verwendet die automatische Erkennung von Hive-Partitionsschlüsseln:

CREATE EXTERNAL TABLE `PROJECT_ID.DATASET.EXTERNAL_TABLE_NAME`
WITH PARTITION COLUMNS
OPTIONS (
format = 'SOURCE_FORMAT',
uris = ['GCS_URIS'],
hive_partition_uri_prefix = 'GCS_URI_SHARED_PREFIX',
require_hive_partition_filter = BOOLEAN);

Dabei gilt:

  • SOURCE_FORMAT ist das Format der externen Datenquelle, z. B. PARQUET
  • GCS_URIS ist der Pfad zum Cloud Storage-Ordner im Platzhalterformat
  • GCS_URI_SHARED_PREFIX ist das Präfix des Quell-URI ohne den Platzhalter
  • BOOLEAN gibt an, ob ein Prädikatfilter zur Zeit der Abfrage erforderlich ist Dieses Flag ist optional. Der Standardwert ist false.

Das folgende Beispiel verwendet benutzerdefinierte Hive-Partitionsschlüssel und -Typen, die in der WITH PARTITION COLUMNS-Klausel aufgelistet werden:

CREATE EXTERNAL TABLE `PROJECT_ID.DATASET.EXTERNAL_TABLE_NAME`
WITH PARTITION COLUMNS (PARTITION_COLUMN_LIST)
OPTIONS (
format = 'SOURCE_FORMAT',
uris = ['GCS_URIS'],
hive_partition_uri_prefix = 'GCS_URI_SHARED_PREFIX',
require_hive_partition_filter = BOOLEAN);

Dabei gilt:

  • PARTITION_COLUMN_LIST ist eine Liste an Spalten, die der Reihenfolge im Pfad des Cloud Storage-Ordners folgen und folgendes Format haben:
KEY1 TYPE1, KEY2 TYPE2

Im folgenden Beispiel wird eine extern partitionierte Tabelle erstellt. Mit der automatischen Schemaerkennung wird sowohl das Dateisystem als auch das Hive-Partitionierungslayout ermittelt. Ist der externe Pfad gs://bucket/path/field_1=first/field_2=1/data.parquet, werden die Partitionsspalten als field_1 (STRING) und field_2 (INT64) erkannt.

CREATE EXTERNAL TABLE dataset.AutoHivePartitionedTable
WITH PARTITION COLUMNS
OPTIONS (
uris = ['gs://bucket/path/*'],
format = 'PARQUET',
hive_partition_uri_prefix = 'gs://bucket/path',
require_hive_partition_filter = false);

Im folgenden Beispiel wird eine extern partitionierte Tabelle erstellt, indem die Partitionsspalten explizit angegeben werden. In diesem Beispiel wird davon ausgegangen, dass der externe Dateipfad das Muster gs://bucket/path/field_1=first/field_2=1/data.parquet hat.

CREATE EXTERNAL TABLE dataset.CustomHivePartitionedTable
WITH PARTITION COLUMNS (
field_1 STRING, -- column order must match the external path
field_2 INT64)
OPTIONS (
uris = ['gs://bucket/path/*'],
format = 'PARQUET',
hive_partition_uri_prefix = 'gs://bucket/path',
require_hive_partition_filter = false);

bq

Verwenden Sie zuerst den Befehl bq mkdef, um eine Tabellendefinitionsdatei zu erstellen:

bq mkdef \
--source_format=SOURCE_FORMAT \
--hive_partitioning_mode=PARTITIONING_MODE \
--hive_partitioning_source_uri_prefix=GCS_URI_SHARED_PREFIX \
--require_hive_partition_filter=BOOLEAN \
 GCS_URIS > DEFINITION_FILE

Wobei:

  • SOURCE_FORMAT ist das Format der externen Datenquelle, z. B. CSV.
  • PARTITIONING_MODE ist der Hive-Partitionierungsmodus. Verwenden Sie einen der folgenden Werte:
    • AUTO: Schlüsselnamen und -typen automatisch erkennen.
    • STRINGS: Schlüsselnamen automatisch in Strings konvertieren.
    • CUSTOM: Schlüsselschema im Präfix des Quell-URI codieren.
  • GCS_URI_SHARED_PREFIX ist das Quell-URI-Präfix.
  • BOOLEAN gibt an, ob ein Prädikatfilter zum Zeitpunkt der Abfrage erforderlich ist. Dieses Flag ist optional. Der Standardwert ist false.
  • GCS_URIS ist der Pfad zum Cloud Storage-Ordner im Platzhalterformat.
  • DEFINITION_FILE ist der Pfad zur Tabellendefinitionsdatei auf Ihrem lokalen Rechner.

Wenn PARTITIONING_MODE den Wert CUSTOM hat, fügen Sie das Partitionsschlüsselschema im Präfix des Quell-URI im folgenden Format ein:

--hive_partitioning_source_uri_prefix=GCS_URI_SHARED_PREFIX/{KEY1:TYPE1}/{KEY2:TYPE2}/...

Verwenden Sie nach dem Erstellen der Tabellendefinitionsdatei den Befehl bq mk, um die externe Tabelle zu erstellen:

bq mk --external_table_definition=DEFINITION_FILE \
DATASET_NAME.TABLE_NAME \
SCHEMA

Wobei:

  • DEFINITION_FILE ist der Pfad zur Tabellendefinitionsdatei.
  • DATASET_NAME ist der Name des Datasets, das die Tabelle enthält.
  • TABLE_NAME ist der Name der Tabelle ist, die Sie erstellen.
  • SCHEMA gibt einen Pfad zu einer JSON-Schemadatei oder das Schema im Format field:data_type,field:data_type,... an. Wenn Sie die automatische Schemaerkennung verwenden möchten, lassen Sie dieses Argument weg.

Beispiele

Im folgenden Beispiel wird der Hive-Partitionierungsmodus AUTO verwendet:

bq mkdef --source_format=CSV \
  --hive_partitioning_mode=AUTO \
  --hive_partitioning_source_uri_prefix=gs://myBucket/myTable \
  gs://myBucket/myTable/* > mytable_def

bq mk --external_table_definition=mytable_def \
  mydataset.mytable \
  Region:STRING,Quarter:STRING,Total_sales:INTEGER

Im folgenden Beispiel wird der Hive-Partitionierungsmodus STRING verwendet:

bq mkdef --source_format=CSV \
  --hive_partitioning_mode=STRING \
  --hive_partitioning_source_uri_prefix=gs://myBucket/myTable \
  gs://myBucket/myTable/* > mytable_def

bq mk --external_table_definition=mytable_def \
  mydataset.mytable \
  Region:STRING,Quarter:STRING,Total_sales:INTEGER

Im folgenden Beispiel wird der Hive-Partitionierungsmodus CUSTOM verwendet:

bq mkdef --source_format=CSV \
  --hive_partitioning_mode=CUSTOM \
  --hive_partitioning_source_uri_prefix=gs://myBucket/myTable/{dt:DATE}/{val:STRING} \
  gs://myBucket/myTable/* > mytable_def

bq mk --external_table_definition=mytable_def \
  mydataset.mytable \
  Region:STRING,Quarter:STRING,Total_sales:INTEGER

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 der Java-Einrichtungsanleitung in der BigQuery-Kurzanleitung zur Verwendung von Clientbibliotheken. 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 können beim Aktualisieren von permanenten externen Tabellen nicht geändert werden.