Tabellendefinitionsdatei für eine externe Datenquelle erstellen

Auf dieser Seite wird beschrieben, wie Sie eine Tabellendefinitionsdatei für eine externe Datenquelle erstellen. Eine externe Datenquelle ist eine Datenquelle, für die auch dann direkt eine Abfrage ausgeführt werden kann, wenn die Daten nicht in BigQuery gespeichert sind.

Eine Tabellendefinitionsdatei enthält die Schemadefinition und Metadaten einer externen Tabelle, z. B. das Datenformat und zugehörige Eigenschaften. Beim Erstellen der Tabellendefinitionsdatei können Sie die automatische Schemaerkennung verwenden, um das Schema für eine externe Datenquelle zu definieren. Sie können das Schema inline oder als JSON-Datei mit der Schemadefinition bereitstellen.

Tabellendefinitionsdateien werden mit dem bq-Befehlszeilentool verwendet. Wenn Sie die REST API nutzen, werden die Attribute in einer Tabellendefinitionsdatei auch für das Erstellen einer ExternalDataConfiguration-Konfiguration angewendet. Beim Erstellen einer externen Tabelle mithilfe der Google Cloud Console werden keine Tabellendefinitionsdateien verwendet.

Sie können Tabellendefinitionsdateien erstellen, um eine permanente oder temporäre externe Tabelle für die folgenden externen Datenquellen zu beschreiben:

  • Cloud Storage

    • Kommagetrennte Werte (CSV)
    • Durch Zeilenumbruch getrenntes JSON
    • Avro-Dateien
    • Datastore-Exportdateien
    • ORC-Dateien
    • Parquet-Dateien
    • Firestore-Exportdateien

  • Google Drive

    • Kommagetrennte Werte (CSV)
    • Durch Zeilenumbruch getrenntes JSON
    • Avro-Dateien
    • Google Sheets

  • Bigtable

Hinweise

Zum Erstellen einer Tabellendefinitionsdatei benötigen Sie den URI für die Datenquelle:

  • Für eine Drive-Datenquelle benötigen Sie den Drive-URI
  • Für eine Cloud Storage-Datenquelle benötigen Sie den Cloud Storage-URI
  • Für eine Bigtable-Datenquelle benötigen Sie den Bigtable-URI

Definitionsdatei für CSV-, JSON- oder Google Sheets-Dateien erstellen

Mit einer der folgenden Methoden können Sie in Cloud Storage oder Drive eine Tabellendefinitionsdatei für CSV-, JSON- oder Google Sheets-Dateien erstellen:

Verwenden Sie das Flag autodetect.

Bei Angabe einer CSV-, JSON- oder Google Sheets-Datei ohne Inline-Schemabeschreibung und ohne Schemadatei können Sie mit dem Flag --autodetect und dem Wert true für die Option "autodetect" die automatische Schemaerkennung in der Tabellendefinitionsdatei festlegen. Wenn die automatische Erkennung aktiviert ist, versucht BigQuery, das Schema automatisch abzuleiten. Weitere Informationen finden Sie unter Automatische Schemaerkennung für externe Datenquellen.

Automatische Erkennung mit einer Cloud Storage-Datenquelle verwenden

Erstellen Sie eine Tabellendefinitionsdatei für eine Cloud Storage-Datenquelle:

  1. Verwenden Sie den Befehl bq mkdef mit dem Flag --autodetect, um eine Tabellendefinitionsdatei zu erstellen. Der Befehl mkdef generiert eine Tabellendefinitionsdatei im JSON-Format. Im folgenden Beispiel wird eine Tabellendefinition erstellt und die Ausgabe in die Datei /tmp/file_name geschrieben.

    bq mkdef \
  --autodetect \
  --source_format=SOURCE_FORMAT \
  "URI" > /tmp/FILE_NAME

    Dabei gilt:

    • SOURCE_FORMAT: Ihr Dateiformat
    • FILE_NAME: der Name der Tabellendefinitionsdatei.

    • URI: Der Cloud Storage-URI.

      Beispiel: gs://mybucket/myfile.

  2. (Optional) Öffnen Sie die Tabellendefinitionsdatei in einem Texteditor. So kann die Datei z. B. mit dem Befehl nano /tmp/file_name in Nano geöffnet werden. Die Datei für eine externe CSV-Datenquelle sollte in etwa wie unten dargestellt aussehen. Beachten Sie, dass für "autodetect" der Wert true festgelegt ist.

    {
"autodetect": true,
"csvOptions": {
  "allowJaggedRows": false,
  "allowQuotedNewlines": false,
  "encoding": "UTF-8",
  "fieldDelimiter": ",",
  "quote": "\"",
  "skipLeadingRows": 0
},
"sourceFormat": "CSV",
"sourceUris": [
  "URI"
]
}

  3. Optional: Bearbeiten Sie die Tabellendefinitionsdatei manuell, wenn Sie allgemeine Einstellungen wie maxBadRecords oderignoreUnknownValues ändern, hinzufügen oder löschen möchten. Für JSON-Quelldateien sind keine speziellen Konfigurationseinstellungen vorhanden. Dagegen gibt es entsprechende Einstellungen für CSV-Dateien und Google Sheets-Dateien. Weitere Informationen finden Sie in der API-Referenz unter ExternalDataConfiguration.

Automatische Erkennung mit einer Drive-Datenquelle

Erstellen Sie eine Tabellendefinitionsdatei für eine Drive-Datenquelle:

  1. Verwenden Sie den Befehl bq mkdef mit dem Flag --autodetect, um eine Tabellendefinition zu erstellen. Der Befehl mkdef generiert eine Tabellendefinitionsdatei im JSON-Format. Im folgenden Beispiel wird eine Tabellendefinition erstellt und die Ausgabe in die Datei /tmp/file_name geschrieben.

    bq mkdef \
   --autodetect \
   --source_format=SOURCE_FORMAT \
   "URI" > /tmp/FILE_NAME

    Dabei gilt:

    • SOURCE_FORMAT: Ihr Dateiformat
    • FILE_NAME: der Name der Tabellendefinitionsdatei.

    • URI: Der Drive-URI.

      Beispiel: https://drive.google.com/open?id=123ABCD123AbcD123Abcd.

  2. Öffnen Sie die Datei in einem Texteditor. Mit dem Befehl nano /tmp/file_name wird die Datei z. B. in Nano geöffnet. Die Datei sollte für eine externe Google Sheets-Datenquelle wie unten dargestellt aussehen. Beachten Sie, dass für "autodetect" der Wert true festgelegt ist.

    {
"autodetect": true,
"sourceFormat": "GOOGLE_SHEETS",
"sourceUris": [
  "URI"
]
}

  3. Optional: Bearbeiten Sie die Tabellendefinitionsdatei manuell, wenn Sie allgemeine Einstellungen wie maxBadRecords oderignoreUnknownValues ändern, hinzufügen oder löschen möchten. Für JSON-Quelldateien sind keine speziellen Konfigurationseinstellungen vorhanden. Dagegen gibt es entsprechende Einstellungen für CSV-Dateien und Google Sheets-Dateien. Weitere Informationen finden Sie in der API-Referenz unter ExternalDataConfiguration.

  4. Wenn Sie ein bestimmtes Tabellenblatt oder einen Zellenbereich in einer Google Sheets-Datei angeben möchten, fügen Sie der Tabellendefinitionsdatei das Attribut range hinzu. Zur Abfrage eines speziellen Tabellenblatts geben Sie den Tabellenblattnamen an. Für die Abfrage eines Zellenbereichs geben Sie den Bereich im Format sheet_name!top_left_cell_id:bottom_right_cell_id an, z. B. "Sheet1!A1:B20". Wenn Sie den Parameter range nicht festlegen, wird das erste Tabellenblatt der Datei verwendet.

Inline-Schema verwenden

Wenn Sie die automatische Schemaerkennung nicht verwenden möchten, können Sie eine Tabellendefinitionsdatei erstellen, indem Sie eine Inline-Schemadefinition angeben. Zum Anlegen einer Inline-Schemadefinition müssen Sie die Felder und Datentypen in der Befehlszeile im folgenden Format angeben: FIELD:DATA_TYPE,FIELD:DATA_TYPE.

Inline-Schema mit einer Cloud Storage- oder Drive-Datenquelle verwenden

Erstellen Sie eine Tabellendefinition für eine Cloud Storage- oder Drive-Datenquelle mithilfe einer Inline-Schemadefinition:

  1. Verwenden Sie den Befehl bq mkdef mit dem Flag --noautodetect, um eine Tabellendefinition zu erstellen. Der Befehl mkdef generiert eine Tabellendefinitionsdatei im JSON-Format. Im folgenden Beispiel wird eine Tabellendefinition erstellt und die Ausgabe in die Datei /tmp/file_name geschrieben.

    bq mkdef \
  --noautodetect \
  --source_format=SOURCE_FORMAT \
  "URI" \
  FIELD:DATA_TYPE,FIELD:DATA_TYPE > /tmp/FILE_NAME

    Ersetzen Sie Folgendes:

    • SOURCE_FORMAT ist das Format der Quelldatei

    • URI ist der Cloud Storage-URI oder Ihr Drive-URI.

      z. B. gs://mybucket/myfile für Cloud Storage oder https://drive.google.com/open?id=123ABCD123AbcD123Abcd für Drive.

    • FIELD:DATA_TYPE,FIELD:DATA_TYPE: die Schemadefinition

      Beispiel: Name:STRING,Address:STRING, ....

    • FILE_NAME: der Name der Tabellendefinitionsdatei.

  2. (Optional) Öffnen Sie die Tabellendefinitionsdatei in einem Texteditor. So kann die Datei z. B. mit dem Befehl nano /tmp/file_name in Nano geöffnet werden. Die Datei sollte in etwa wie im Folgenden dargestellt aussehen. Beachten Sie, dass "autodetect" nicht aktiviert ist und die Schemainformationen in die Tabellendefinitionsdatei geschrieben werden.

    {
"schema": {
  "fields": [
    {
      "name": "FIELD",
      "type": "DATA_TYPE"
    },
    {
      "name": "FIELD",
      "type": "DATA_TYPE"
    }
    ...
  ]
},
"sourceFormat": "NEWLINE_DELIMITED_JSON",
"sourceUris": [
  "URI"
]
}

  3. Optional: Bearbeiten Sie die Tabellendefinitionsdatei manuell, wenn Sie allgemeine Einstellungen wie maxBadRecords oderignoreUnknownValues ändern, hinzufügen oder löschen möchten. Für JSON-Quelldateien sind keine speziellen Konfigurationseinstellungen vorhanden. Dagegen gibt es entsprechende Einstellungen für CSV-Dateien und Google Sheets-Dateien. Weitere Informationen finden Sie in der API-Referenz unter ExternalDataConfiguration.

JSON-Schemadatei verwenden

Wenn Sie weder die automatische Erkennung noch eine Inline-Schemadefinition verwenden möchten, können Sie eine JSON-Schemadatei erstellen und beim Erstellen der Tabellendefinition darauf verweisen. Erstellen Sie die JSON-Schemadatei manuell auf Ihrem lokalen Computer. Der Verweis auf eine JSON-Schemadatei in Cloud Storage oder Drive wird nicht unterstützt.

Schemadatei mit einer Cloud Storage-Datenquelle verwenden

Erstellen Sie eine Tabellendefinition für eine Cloud Storage-Datenquelle mithilfe einer JSON-Schemadatei:

  1. Verwenden Sie den Befehl bq mkdef mit dem Flag --noautodetect, um eine Tabellendefinition zu erstellen. Der Befehl mkdef generiert eine Tabellendefinitionsdatei im JSON-Format. Im folgenden Beispiel wird eine Tabellendefinition erstellt und die Ausgabe in die Datei /tmp/file_name geschrieben.

    bq mkdef \
   --noautodetect \
   --source_format=SOURCE_FORMAT \
   "URI" \
  PATH_TO_SCHEMA_FILE > /tmp/FILE_NAME

    Dabei gilt:

    • SOURCE_FORMAT: Ihr Dateiformat
    • FILE_NAME: der Name der Tabellendefinitionsdatei.

    • URI: Der Cloud Storage-URI.

      Beispiel: gs://mybucket/myfile.

    • PATH_TO_SCHEMA_FILE: der Speicherort der JSON-Schemadatei auf Ihrem lokalen Computer

  2. (Optional) Öffnen Sie die Tabellendefinitionsdatei in einem Texteditor. Mit dem Befehl nano /tmp/file_name wird die Datei z. B. in
    Nano geöffnet. Die Datei sollte in etwa wie im Folgenden dargestellt aussehen. Beachten Sie, dass "autodetect" nicht aktiviert ist und die Schemainformationen in die Tabellendefinitionsdatei geschrieben werden.

    {
"schema": {
  "fields": [
    {
      "name": "FIELD",
      "type": "DATA_TYPE"
    },
    {
      "name": "FIELD",
      "type": "DATA_TYPE"
    }
    ...
  ]
},
"sourceFormat": "NEWLINE_DELIMITED_JSON",
"sourceUris": [
  "URI"
]
}

  3. Optional: Bearbeiten Sie die Tabellendefinitionsdatei manuell, wenn Sie allgemeine Einstellungen wie maxBadRecords oderignoreUnknownValues ändern, hinzufügen oder löschen möchten. Für JSON-Quelldateien sind keine speziellen Konfigurationseinstellungen vorhanden. Dagegen gibt es entsprechende Einstellungen für CSV-Dateien und Google Sheets-Dateien. Weitere Informationen finden Sie in der API-Referenz unter ExternalDataConfiguration.

Schemadatei mit einer Drive-Datenquelle verwenden

Erstellen Sie mithilfe einer JSON-Schemadatei eine Tabellendefinition für eine Drive-Datenquelle:

  1. Verwenden Sie den Befehl bq mkdef mit dem Flag --noautodetect, um eine Tabellendefinition zu erstellen. Der Befehl mkdef generiert eine Tabellendefinitionsdatei im JSON-Format. Im folgenden Beispiel wird eine Tabellendefinition erstellt und die Ausgabe in die Datei /tmp/file_name geschrieben.

    bq mkdef \
   --noautodetect \
   --source_format=source_format \
   "URI" \
   PATH_TO_SCHEMA_FILE > /tmp/FILE_NAME

    Dabei gilt:

    • SOURCE_FORMAT ist das Format der Quelldatei

    • URI: Der Drive-URI.

      Beispiel: https://drive.google.com/open?id=123ABCD123AbcD123Abcd.

    • PATH_TO_SCHEMA_FILE: der Speicherort der JSON-Schemadatei auf Ihrem lokalen Computer

    • FILE_NAME: der Name der Tabellendefinitionsdatei.

  2. Öffnen Sie die Datei in einem Texteditor. Mit dem Befehl nano /tmp/file_name wird die Datei z. B. in Nano geöffnet. Die Datei sollte wie unten dargestellt aussehen. Beachten Sie, dass "autodetect" nicht aktiviert ist und die Schemainformationen in die Tabellendefinitionsdatei geschrieben werden.

    {
"schema": {
  "fields": [
    {
      "name": "FIELD",
      "type": "DATA_TYPE"
    },
    {
      "name": "FIELD",
      "type": "DATA_TYPE"
    }
    ...
  ]
},
"sourceFormat": "GOOGLE_SHEETS",
"sourceUris": [
  "URI"
]
}

  3. Optional: Bearbeiten Sie die Tabellendefinitionsdatei manuell, wenn Sie allgemeine Einstellungen wie maxBadRecords oderignoreUnknownValues ändern, hinzufügen oder löschen möchten. Für JSON-Quelldateien sind keine speziellen Konfigurationseinstellungen vorhanden. Dagegen gibt es entsprechende Einstellungen für CSV-Dateien und Google Sheets-Dateien. Weitere Informationen finden Sie in der API-Referenz unter ExternalDataConfiguration.

  4. Wenn Sie ein bestimmtes Tabellenblatt oder einen Zellenbereich in einer Google Sheets-Datei angeben möchten, fügen Sie der Tabellendefinitionsdatei das Attribut range hinzu. Zur Abfrage eines speziellen Tabellenblatts geben Sie den Tabellenblattnamen an. Für die Abfrage eines Zellenbereichs geben Sie den Bereich im Format sheet_name!top_left_cell_id:bottom_right_cell_id an, z. B. "Sheet1!A1:B20". Wenn Sie den Parameter range nicht festlegen, wird das erste Tabellenblatt der Datei verwendet.

Definitionsdatei für selbstbeschreibende Formate erstellen

Avro, Parquet und ORC sind selbstbeschreibende Formate. Datendateien in diesen Formaten enthalten eigene Schemainformationen. Wenn Sie eines dieser Formate als externe Datenquelle verwenden, ruft BigQuery das Schema automatisch über die Quelldaten ab. Sie müssen beim Erstellen einer Tabellendefinition keine automatische Schemaerkennung verwenden und auch keine Inline-Schemadefinition oder Schemadatei angeben.

Sie können eine Tabellendefinitionsdatei für Avro-, Parquet- oder ORC-Daten erstellen, die in Cloud Storage oder Drive gespeichert sind.

  1. Zum Erstellen einer permanenten Tabellendefinition verwenden Sie den Befehl bq mkdef.

    bq mkdef \
    --source_format=FORMAT \
    "URI" > FILE_NAME

    Dabei gilt:

    • FORMAT ist das Quellformat

    • URI ist der Cloud Storage-URI oder Ihr Drive-URI.

      z. B. gs://mybucket/myfile für Cloud Storage oder https://drive.google.com/open?id=123ABCD123AbcD123Abcd für Drive.

    • FILE_NAME: der Name der Tabellendefinitionsdatei.

  2. (Optional) Öffnen Sie die Tabellendefinitionsdatei in einem Texteditor. Die Ausgabe sieht dann ungefähr so aus:

    {
   "sourceFormat": "AVRO",
   "sourceUris": [
   "URI"
    ]
}

  3. Optional: Bearbeiten Sie die Tabellendefinitionsdatei manuell, wenn Sie allgemeine Einstellungen wie maxBadRecords oderignoreUnknownValues ändern, hinzufügen oder löschen möchten. Weitere Informationen finden Sie in der API-Referenz unter ExternalDataConfiguration.

Definitionsdatei für mit Hive partitionierte Daten erstellen

Verwenden Sie den Befehl bq mkdef mit den Flags hive_partitioning_mode und hive_partitioning_source_uri_prefix Flags, um eine Definitionsdatei für mit Hive partitionierte Daten zu erstellen, die in Cloud Storage, Amazon Simple Storage Service (Amazon S3) oder Azure Blob Storage gespeichert sind.

Definitionsdatei für Datastore und Firestore erstellen

Wenn Sie einen Datastore- oder Firestore-Export als externe Datenquelle verwenden, ruft BigQuery das Schema automatisch aus den selbstbeschreibenden Quelldaten ab. Beim Erstellen einer Tabellendefinition müssen Sie keine Inline-Schemadefinition oder Schemadatei angeben.

Sie können eine Tabellendefinitionsdatei für Datastore- und Firestore-Exportdaten erstellen, die in Cloud Storage gespeichert sind.

  1. Zum Erstellen einer permanenten Tabellendefinition verwenden Sie den Befehl bq mkdef. Das Flag --noautodetect muss für Datastore- oder Firestore-Sicherungsdateien nicht verwendet werden. Die automatische Schemaerkennung ist für diese Dateitypen deaktiviert. Der Befehl mkdef erzeugt eine Tabellendefinitionsdatei im JSON-Format. Im folgenden Beispiel wird eine Tabellendefinition erstellt und die Ausgabe in die Datei /tmp/file_name geschrieben.

    bq mkdef \
--source_format=DATASTORE_BACKUP \
"URI" > /tmp/FILE_NAME

    Dabei gilt:

    Das Quellformat DATASTORE_BACKUP wird sowohl für Datastore als auch für Firestore verwendet.

  2. (Optional) Öffnen Sie die Tabellendefinitionsdatei in einem Texteditor. Mit dem Befehl nano /tmp/file_name wird die Datei z. B. in Nano geöffnet. Die Datei sollte in etwa wie im Folgenden dargestellt aussehen. Beachten Sie, dass die Einstellung "autodetect" nicht erforderlich ist.

    {
"sourceFormat": "DATASTORE_BACKUP",
"sourceUris": [
  "gs://URI"
]
}

  3. Optional: Bearbeiten Sie die Tabellendefinitionsdatei manuell, um Einstellungen wie maxBadRecords oder ignoreUnknownValues zu ändern, hinzuzufügen oder zu löschen. Es gibt keine spezifischen Konfigurationseinstellungen für Datastore- und Firestore-Exportdateien. Weitere Informationen finden Sie in der API-Referenz unter ExternalDataConfiguration.

Definitionsdatei für Bigtable erstellen

Eine Tabellendefinitionsdatei für Bigtable erstellen Sie manuell im JSON-Format. Der Befehl mkdef zum Erstellen einer Tabellendefinition wird derzeit für Bigtable-Datenquellen nicht unterstützt. Auch die automatische Schemaerkennung wird für Bigtable nicht unterstützt. Eine Liste der Optionen für Bigtable-Tabellendefinitionen finden Sie in der REST API-Referenz unter BigtableOptions.

Ein Beispiel für eine JSON-Tabellendefinitionsdatei für Bigtable sehen Sie unten. Mit dieser Tabellendefinitionsdatei liest BigQuery Daten aus einer einzelnen Spaltenfamilie und interpretiert dabei die Werte als binär codierte Ganzzahlen.

{
    "sourceFormat": "BIGTABLE",
    "sourceUris": [
        "https://googleapis.com/bigtable/projects/PROJECT_ID/instances/INSTANCE_ID/tables/TABLE_NAME"
    ],
    "bigtableOptions": {
        "columnFamilies" : [
            {
                "familyId": "FAMILY_ID",
                "type": "INTEGER",
                "encoding": "BINARY"
            }
        ]
    }
}

Dabei gilt:

  • PROJECT_ID: das Projekt, das Ihren Bigtable-Cluster enthält
  • INSTANCE_ID: die Bigtable-Instanz-ID
  • TABLE_NAME: Der Name der abzufragenden Tabelle
  • FAMILY_ID: Die ID der Spaltenfamilie

Weitere Informationen finden Sie unter Bigtable-URI abrufen.

Unterstützung von Platzhaltern für Tabellendefinitionsdateien

Wenn Ihre Daten auf mehrere Dateien verteilt sind, können Sie mehrere Dateien mit einem Sternchenplatzhalter (*) auswählen. Die Verwendung des Sternchenplatzhalters muss folgenden Regeln entsprechen:

  • Das Sternchen kann innerhalb oder am Ende des Objektnamens stehen.
  • Die Verwendung mehrerer Sternchen wird nicht unterstützt. Beispiel: Der Pfad gs://mybucket/fed-*/temp/*.csv ist ungültig.
  • Die Verwendung eines Sternchens mit dem Bucket-Namen wird nicht unterstützt.

Beispiele:

  • Im folgenden Beispiel wird gezeigt, wie Sie alle Dateien in allen Ordnern auswählen, die mit dem Präfix gs://mybucket/fed-samples/fed-sample beginnen:

    gs://mybucket/fed-samples/fed-sample*

  • Im folgenden Beispiel wird gezeigt, wie nur Dateien mit der .csv-Erweiterung im Ordner mit dem Namen fed-samples und allen Unterordnern von fed-samples ausgewählt werden:

    gs://mybucket/fed-samples/*.csv

  • Das folgende Beispiel zeigt, wie Sie Dateien mit dem Benennungsmuster fed-sample*.csv im Ordner fed-samples auswählen. In diesem Beispiel werden keine Dateien in Unterordnern von fed-samples ausgewählt.

    gs://mybucket/fed-samples/fed-sample*.csv

Bei der Verwendung des bq-Befehlszeilentools müssen Sie das Sternchen auf einigen Plattformen unter Umständen mit einem Escape-Zeichen versehen.

Wenn Sie einen Sternchenplatzhalter verwenden, setzen Sie den Bucket und den Dateinamen in Anführungszeichen. Wenn Sie beispielsweise zwei Dateien mit den Namen fed-sample000001.csv und fed-sample000002.csv haben und ein Sternchen verwenden möchten, um beide auszuwählen, wäre der Bucket-URI "gs://mybucket/fed-sample*".

Das Platzhalterzeichen * ist nicht zulässig, wenn Tabellendefinitionsdateien für die folgenden Datenquellen erstellt werden:

  • Bigtable. Für Bigtable-Daten kann nur eine Datenquelle angegeben werden. Der URI-Wert muss eine gültige HTTPS-URL für eine Bigtable-Tabelle sein.
  • Datastore oder Firestore. Datastore- oder Firestore-Exporte, die in Cloud Storage gespeichert sind. Für Datastore-Sicherungen kann nur eine Datenquelle angegeben werden. Der URI-Wert muss mit .backup_info oder .export_metadata enden.
  • Drive. In Google Drive gespeicherte Daten.

Nächste Schritte