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:
Verwenden Sie den Befehl
bq mkdefmit dem Flag--autodetect, um eine Tabellendefinitionsdatei zu erstellen. Der Befehlmkdefgeneriert eine Tabellendefinitionsdatei im JSON-Format. Im folgenden Beispiel wird eine Tabellendefinition erstellt und die Ausgabe in die Datei/tmp/file_namegeschrieben.bq mkdef \ --autodetect \ --source_format=SOURCE_FORMAT \ "URI" > /tmp/FILE_NAME
Dabei gilt:
SOURCE_FORMAT: Ihr DateiformatFILE_NAME: der Name Ihrer TabellendefinitionsdateiURI: Der Cloud Storage-URI.Beispiel:
gs://mybucket/myfile.
(Optional) Öffnen Sie die Tabellendefinitionsdatei in einem Texteditor. So kann die Datei z. B. mit dem Befehl
nano /tmp/file_namein 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 Werttruefestgelegt ist.{ "autodetect": true, "csvOptions": { "allowJaggedRows": false, "allowQuotedNewlines": false, "encoding": "UTF-8", "fieldDelimiter": ",", "quote": "\"", "skipLeadingRows": 0 }, "sourceFormat": "CSV", "sourceUris": [ "URI" ] }
Optional: Bearbeiten Sie die Tabellendefinitionsdatei manuell, wenn Sie allgemeine Einstellungen wie
maxBadRecordsoderignoreUnknownValuesä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 unterExternalDataConfiguration.
Automatische Erkennung mit einer Drive-Datenquelle
Erstellen Sie eine Tabellendefinitionsdatei für eine Drive-Datenquelle:
Verwenden Sie den Befehl
bq mkdefmit dem Flag--autodetect, um eine Tabellendefinition zu erstellen. Der Befehlmkdefgeneriert eine Tabellendefinitionsdatei im JSON-Format. Im folgenden Beispiel wird eine Tabellendefinition erstellt und die Ausgabe in die Datei/tmp/file_namegeschrieben.bq mkdef \ --autodetect \ --source_format=SOURCE_FORMAT \ "URI" > /tmp/FILE_NAME
Dabei gilt:
SOURCE_FORMAT: Ihr DateiformatFILE_NAME: der Name Ihrer TabellendefinitionsdateiURI: Der Drive-URI.Beispiel:
https://drive.google.com/open?id=123ABCD123AbcD123Abcd.
Öffnen Sie die Datei in einem Texteditor. Mit dem Befehl
nano /tmp/file_namewird 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 Werttruefestgelegt ist.{ "autodetect": true, "sourceFormat": "GOOGLE_SHEETS", "sourceUris": [ "URI" ] }
Optional: Bearbeiten Sie die Tabellendefinitionsdatei manuell, wenn Sie allgemeine Einstellungen wie
maxBadRecordsoderignoreUnknownValuesä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 unterExternalDataConfiguration.Wenn Sie ein bestimmtes Tabellenblatt oder einen Zellenbereich in einer Google Sheets-Datei angeben möchten, fügen Sie der Tabellendefinitionsdatei die Eigenschaft
rangedemGoogleSheetsOptions-Objekt hinzu. Zur Abfrage eines speziellen Tabellenblatts geben Sie den Tabellenblattnamen an. Für die Abfrage eines Zellenbereichs geben Sie den Bereich im Formatsheet_name!top_left_cell_id:bottom_right_cell_idan, z. B."Sheet1!A1:B20". Wenn Sie den Parameterrangenicht 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:
Verwenden Sie den Befehl
bq mkdefmit dem Flag--noautodetect, um eine Tabellendefinition zu erstellen. Der Befehlmkdefgeneriert eine Tabellendefinitionsdatei im JSON-Format. Im folgenden Beispiel wird eine Tabellendefinition erstellt und die Ausgabe in die Datei/tmp/file_namegeschrieben.bq mkdef \ --noautodetect \ --source_format=SOURCE_FORMAT \ "URI" \ FIELD:DATA_TYPE,FIELD:DATA_TYPE > /tmp/FILE_NAME
Ersetzen Sie Folgendes:
SOURCE_FORMAT: das QuelldateiformatURI: Der Cloud Storage-URI oder der Drive-URI.z. B.
gs://mybucket/myfilefür Cloud Storage oderhttps://drive.google.com/open?id=123ABCD123AbcD123Abcdfür Drive.FIELD:DATA_TYPE,FIELD:DATA_TYPE: die SchemadefinitionBeispiel:
Name:STRING,Address:STRING, ....FILE_NAME: der Name Ihrer Tabellendefinitionsdatei
(Optional) Öffnen Sie die Tabellendefinitionsdatei in einem Texteditor. So kann die Datei z. B. mit dem Befehl
nano /tmp/file_namein 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" ] }
Optional: Bearbeiten Sie die Tabellendefinitionsdatei manuell, wenn Sie allgemeine Einstellungen wie
maxBadRecordsoderignoreUnknownValuesä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 unterExternalDataConfiguration.
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:
Verwenden Sie den Befehl
bq mkdefmit dem Flag--noautodetect, um eine Tabellendefinition zu erstellen. Der Befehlmkdefgeneriert eine Tabellendefinitionsdatei im JSON-Format. Im folgenden Beispiel wird eine Tabellendefinition erstellt und die Ausgabe in die Datei/tmp/file_namegeschrieben.bq mkdef \ --noautodetect \ --source_format=SOURCE_FORMAT \ "URI" \ PATH_TO_SCHEMA_FILE > /tmp/FILE_NAME
Dabei gilt:
SOURCE_FORMAT: Ihr DateiformatFILE_NAME: der Name Ihrer TabellendefinitionsdateiURI: Der Cloud Storage-URI.Beispiel:
gs://mybucket/myfile.PATH_TO_SCHEMA_FILE: Der Speicherort der JSON-Schemadatei auf Ihrem lokalen Computer.
(Optional) Öffnen Sie die Tabellendefinitionsdatei in einem Texteditor. Mit dem Befehl
nano /tmp/file_namewird 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" ] }
Optional: Bearbeiten Sie die Tabellendefinitionsdatei manuell, wenn Sie allgemeine Einstellungen wie
maxBadRecordsoderignoreUnknownValuesä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 unterExternalDataConfiguration.
Schemadatei mit einer Drive-Datenquelle verwenden
Erstellen Sie mithilfe einer JSON-Schemadatei eine Tabellendefinition für eine Drive-Datenquelle:
Verwenden Sie den Befehl
bq mkdefmit dem Flag--noautodetect, um eine Tabellendefinition zu erstellen. Der Befehlmkdefgeneriert eine Tabellendefinitionsdatei im JSON-Format. Im folgenden Beispiel wird eine Tabellendefinition erstellt und die Ausgabe in die Datei/tmp/file_namegeschrieben.bq mkdef \ --noautodetect \ --source_format=source_format \ "URI" \ PATH_TO_SCHEMA_FILE > /tmp/FILE_NAME
Dabei gilt:
SOURCE_FORMAT: das QuelldateiformatURI: 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 Ihrer Tabellendefinitionsdatei
Öffnen Sie die Datei in einem Texteditor. Mit dem Befehl
nano /tmp/file_namewird 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" ] }
Optional: Bearbeiten Sie die Tabellendefinitionsdatei manuell, wenn Sie allgemeine Einstellungen wie
maxBadRecordsoderignoreUnknownValuesä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 unterExternalDataConfiguration.Wenn Sie ein bestimmtes Tabellenblatt oder einen Zellenbereich in einer Google Sheets-Datei angeben möchten, fügen Sie der Tabellendefinitionsdatei die Eigenschaft
rangedemGoogleSheetsOptions-Objekt hinzu. Zur Abfrage eines speziellen Tabellenblatts geben Sie den Tabellenblattnamen an. Für die Abfrage eines Zellenbereichs geben Sie den Bereich im Formatsheet_name!top_left_cell_id:bottom_right_cell_idan, z. B."Sheet1!A1:B20". Wenn Sie den Parameterrangenicht 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.
Zum Erstellen einer permanenten Tabellendefinition verwenden Sie den Befehl
bq mkdef.bq mkdef \ --source_format=FORMAT \ "URI" > FILE_NAME
Dabei gilt:
FORMATist das QuellformatURI: Der Cloud Storage-URI oder der Drive-URI.z. B.
gs://mybucket/myfilefür Cloud Storage oderhttps://drive.google.com/open?id=123ABCD123AbcD123Abcdfür Drive.FILE_NAME: der Name Ihrer Tabellendefinitionsdatei
(Optional) Öffnen Sie die Tabellendefinitionsdatei in einem Texteditor. Die Ausgabe sieht dann ungefähr so aus:
{ "sourceFormat": "AVRO", "sourceUris": [ "URI" ] }
Optional: Bearbeiten Sie die Tabellendefinitionsdatei manuell, wenn Sie allgemeine Einstellungen wie
maxBadRecordsoderignoreUnknownValuesändern, hinzufügen oder löschen möchten. Weitere Informationen finden Sie in der API-Referenz unterExternalDataConfiguration.
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.
Zum Erstellen einer permanenten Tabellendefinition verwenden Sie den Befehl
bq mkdef. Das Flag--noautodetectmuss für Datastore- oder Firestore-Sicherungsdateien nicht verwendet werden. Die automatische Schemaerkennung ist für diese Dateitypen deaktiviert. Der Befehlmkdeferzeugt eine Tabellendefinitionsdatei im JSON-Format. Im folgenden Beispiel wird eine Tabellendefinition erstellt und die Ausgabe in die Datei/tmp/file_namegeschrieben.bq mkdef \ --source_format=DATASTORE_BACKUP \ "URI" > /tmp/FILE_NAME
Dabei gilt:
URI: Der Cloud Storage-URI.FILE_NAME: der Name Ihrer Tabellendefinitionsdatei
Das Quellformat
DATASTORE_BACKUPwird sowohl für Datastore als auch für Firestore verwendet.(Optional) Öffnen Sie die Tabellendefinitionsdatei in einem Texteditor. Mit dem Befehl
nano /tmp/file_namewird 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" ] }
Optional: Bearbeiten Sie die Tabellendefinitionsdatei manuell, um Einstellungen wie
maxBadRecordsoderignoreUnknownValueszu ä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 unterExternalDataConfiguration.
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[/appProfiles/APP_PROFILE_ID]/tables/TABLE_NAME" ], "bigtableOptions": { "columnFamilies" : [ { "familyId": "FAMILY_ID", "type": "INTEGER", "encoding": "BINARY" } ] } }
Dabei gilt:
PROJECT_ID: Das Projekt, das Ihren Bigtable-Cluster enthältINSTANCE_ID: die Bigtable-Instanz-IDAPP_PROFILE_ID(optional): Die ID des Anwendungsprofils, das Sie zum Lesen Ihrer Bigtable-Daten verwenden möchten. Die Einstellungen für Anwendungsprofile geben an, ob für die Berechnung Data Boost oder bereitgestellte Knoten verwendet werden.TABLE_NAME: Der Name der abzufragenden TabelleFAMILY_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/*.csvist 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-samplebeginnen:gs://mybucket/fed-samples/fed-sample*Im folgenden Beispiel wird gezeigt, wie nur Dateien mit der
.csv-Erweiterung im Ordner mit dem Namenfed-samplesund allen Unterordnern vonfed-samplesausgewählt werden:gs://mybucket/fed-samples/*.csvDas folgende Beispiel zeigt, wie Sie Dateien mit dem Benennungsmuster
fed-sample*.csvim Ordnerfed-samplesauswählen. In diesem Beispiel werden keine Dateien in Unterordnern vonfed-samplesausgewä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_infooder.export_metadataenden. - Drive. In Google Drive gespeicherte Daten.