Cloud Storage-Daten abfragen

BigQuery unterstützt die Abfrage von Cloud Storage-Daten in den folgenden Formaten:

  • Kommagetrennte Werte (CSV)
  • JSON (durch Zeilenumbruch getrennt)
  • Avro
  • ORC
  • Parquet
  • Datastore-Exporte
  • Firestore-Exporte

BigQuery unterstützt die Abfrage von Cloud Storage-Daten aus den folgenden Speicherklassen:

  • Standard
  • Nearline
  • Coldline
  • Archivieren

Soll eine externe Cloud Storage-Datenquelle abgefragt werden, geben Sie den Cloud Storage-URI-Pfad zu den Daten an und erstellen Sie eine externe Tabelle, die auf diese Datenquelle verweist. Bei der Tabelle, die als Referenz für die Cloud Storage-Datenquelle verwendet wird, kann es sich um eine permanente Tabelle oder eine temporäre Tabelle handeln.

Beachten Sie den Standort Ihres Datasets und Ihres Cloud Storage-Buckets bei der Abfrage von in Cloud Storage gespeicherten Daten.

Cloud Storage-URI abrufen

Wenn Sie eine externe Tabelle mit einer Cloud Storage-Datenquelle erstellen möchten, müssen Sie den Cloud Storage-URI angeben.

Der Cloud Storage-URI enthält den Namen Ihres Buckets und Ihr Objekt (Dateiname). Wenn der Cloud Storage-Bucket beispielsweise den Namen mybucket und die Datendatei den Namen myfile.csv hat, lautet der Bucket-URI gs://mybucket/myfile.csv. Wenn Ihre Daten auf mehrere Dateien verteilt sind, können Sie im URI einen Platzhalter verwenden. Weitere Informationen dazu finden Sie unter Anfrage-URIs für Cloud Storage.

BigQuery unterstützt keine Quell-URIs, die nach dem anfänglichen doppelten Schrägstrich weitere, aufeinanderfolgende Schrägstriche enthalten. Cloud Storage-Objektnamen können mehrere aufeinanderfolgende Schrägstriche ("/") enthalten. BigQuery wandelt diese jedoch in einen einzelnen Schrägstrich um. Der folgende Quell-URI ist beispielsweise in Cloud Storage gültig, funktioniert aber nicht in BigQuery: gs://bucket/my//object//name.

So rufen Sie den Cloud Storage-URI ab:

  1. Öffnen Sie die Cloud Storage-Konsole.

    Cloud Storage-Konsole

  2. Gehen Sie zum Standort des Objekts (Datei), das die Quelldaten enthält.

  3. Am oberen Rand der Cloud Storage-Konsole sehen Sie den Pfad zum Objekt. Wenn Sie den URI erstellen möchten, ersetzen Sie gs://bucket/file durch den entsprechenden Pfad, z. B. gs://mybucket/myfile.json. bucket ist der Name des Cloud Storage-Buckets und file der Name des Objekts (Datei), das die Daten enthält.

Permanente und temporäre externe Tabellen

Sie können eine externe Datenquelle in BigQuery mithilfe einer permanenten Tabelle oder einer temporären Tabelle abfragen. Eine permanente Tabelle ist eine Tabelle, die in einem Dataset erstellt und mit Ihrer externen Datenquelle verknüpft wird. Da die Tabelle permanent ist, können Sie sie mithilfe der Zugriffssteuerung auf Dataset-Ebene für andere Nutzer freigeben, die ebenfalls Zugriff auf die zugrunde liegende externe Datenquelle haben. Außerdem können Sie die Tabelle jederzeit abfragen.

Wenn Sie eine externe Datenquelle mithilfe einer temporären Tabelle abfragen, senden Sie einen Befehl, der eine Abfrage enthält und durch den eine nicht permanente, mit der externen Datenquelle verknüpfte Tabelle erstellt wird. Wenn Sie eine temporäre Tabelle verwenden, erstellen Sie keine Tabelle in einem Ihrer BigQuery-Datasets. Da die Tabelle nicht permanent in einem Dataset gespeichert wird, kann sie nicht für andere Nutzer freigegeben werden. Das Abfragen einer externen Datenquelle mithilfe einer temporären Tabelle eignet sich für einmalige Ad-hoc-Abfragen von externen Daten sowie für ETL-Vorgänge (Extraktion, Transformation, Laden).

Cloud Storage-Daten mit permanenten externen Tabellen abfragen

Erforderliche Berechtigungen und Bereiche

Wenn Sie externe Daten in Cloud Storage mithilfe einer permanenten Tabelle abfragen, benötigen Sie Berechtigungen zum Ausführen eines Abfragejobs auf Projektebene oder höher. Außerdem benötigen Sie Berechtigungen, mit denen Sie eine Tabelle erstellen können, die auf die externen Daten verweist, sowie Berechtigungen, die Ihnen Zugriff auf die Tabellendaten gewähren. Wenn Ihre externen Daten in Cloud Storage gespeichert sind, benötigen Sie auch Berechtigungen für den Zugriff auf die Daten im Cloud Storage-Bucket.

BigQuery-Berechtigungen

Zum Erstellen und Abfragen einer externen Tabelle in BigQuery sind mindestens die folgenden Berechtigungen erforderlich:

  • bigquery.tables.create
  • bigquery.tables.getData
  • bigquery.jobs.create

Diese vordefinierten Cloud IAM-Rollen enthalten sowohl die Berechtigung bigquery.tables.create als auch bigquery.tables.getData:

  • bigquery.dataEditor
  • bigquery.dataOwner
  • bigquery.admin

Die folgenden vordefinierten Cloud IAM-Rollen umfassen Berechtigungen vom Typ bigquery.jobs.create:

  • bigquery.user
  • bigquery.jobUser
  • bigquery.admin

Wenn ein Nutzer mit Berechtigungen vom Typ bigquery.datasets.create ein Dataset erstellt, hat er darauf außerdem Zugriff als bigquery.dataOwner. Mit bigquery.dataOwner-Zugriff hat der Nutzer die Möglichkeit, externe Tabellen im Dataset zu erstellen und auf sie zuzugreifen. Zum Abfragen der Daten sind jedoch weiterhin Berechtigungen vom Typ bigquery.jobs.create erforderlich.

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

Cloud Storage-Berechtigungen

Zum Abfragen externer Daten in einem Cloud Storage-Bucket benötigen Sie die Berechtigungen vom Typ storage.objects.get. Wenn Sie einen URI-Platzhalter verwenden, benötigen Sie außerdem die Berechtigung storage.objects.list.

Mit der vordefinierten Cloud IAM-Rolle storage.objectViewer können die Berechtigungen storage.objects.get und storage.objects.list gewährt werden.

Bereiche für Compute Engine-Instanzen

Wenn Sie eine Compute Engine-Instanz erstellen, können Sie eine Liste der Bereiche für die Instanz festlegen. Mit den Bereichen steuern Sie den Zugriff der Instanz auf GCP-Produkte wie Cloud Storage. Anwendungen, die auf der VM ausgeführt werden, rufen die Google Cloud APIs über das mit der Instanz verknüpfte Dienstkonto auf.

Wenn Sie eine Compute Engine-Instanz als Standardkonto für das Compute Engine-Dienstkonto einrichten und dieses Dienstkonto auf eine externe Tabelle zugreift, die mit einer Cloud Storage-Datenquelle verknüpft ist, benötigt die Instanz Lesezugriff auf Cloud Storage. Dem Compute Engine-Standarddienstkonto wird automatisch der Bereich https://www.googleapis.com/auth/devstorage.read_only zugewiesen. Wenn Sie ein eigenes Dienstkonto erstellen, wenden Sie den Cloud Storage-Lesebereich auf die Instanz an.

Wie Sie Bereiche auf eine Compute Engine-Instanz anwenden, erfahren Sie unter Dienstkonto und Zugriffsbereiche für eine Instanz ändern. Weitere Informationen zu Compute Engine-Dienstkonten finden Sie unter Dienstkonten.

Permanente externe Tabelle erstellen und abfragen

So können Sie eine permanente Tabelle erstellen, die mit der externen Datenquelle verknüpft ist:

  • Mit der Cloud Console oder der klassischen BigQuery-Web-UI
  • Mit dem Befehl mk des Befehlszeilentools
  • Durch Erstellen einer ExternalDataConfiguration, wenn Sie die API-Methode tables.insert verwenden
  • Mit den Clientbibliotheken

Wenn Sie eine externe Datenquelle mithilfe einer permanenten Tabelle abfragen möchten, erstellen Sie eine Tabelle in einem BigQuery-Dataset, die mit der externen Datenquelle verknüpft ist. Die Daten werden nicht in der BigQuery-Tabelle gespeichert. Da die Tabelle permanent ist, können Sie sie mithilfe von Zugriffssteuerungen auf Dataset-Ebene für andere Nutzer freigeben, die ebenfalls Zugriff auf die zugrunde liegende externe Datenquelle haben.

Sie haben drei Möglichkeiten, um beim Erstellen einer permanenten externen Tabelle in BigQuery Schemainformationen anzugeben:

  • Wenn Sie die permanente externe Tabelle mithilfe der API-Methode tables.insert anlegen, erstellen Sie eine Tabellenressource, die eine Schemadefinition und eine ExternalDataConfiguration enthält. Setzen Sie den Parameter autodetect auf true, um die automatische Schemaerkennung für unterstützte Datenquellen zu aktivieren.
  • Wenn Sie die permanente externe Tabelle über die Befehlszeile erstellen, können Sie wahlweise eine Tabellendefinitionsdatei verwenden, Ihre eigene Schemadatei erstellen und verwenden oder das Schema inline in die Befehlszeile eingeben. Sie können beim Erstellen der Tabellendefinitionsdatei die automatische Schemaerkennung für unterstützte Datenquellen aktivieren.
  • Wenn Sie die Console oder die klassische BigQuery-Web-UI verwenden, um eine permanente externe Tabelle zu erstellen, können Sie das Tabellenschema manuell eingeben oder die automatische Schemaerkennung für unterstützte Datenquellen verwenden.

So erstellen Sie eine externe Tabelle:

Console

  1. Rufen Sie in der Cloud Console die BigQuery-Web-UI auf.
    Zur Cloud Console
  2. Maximieren Sie im Navigationsbereich im Abschnitt Ressourcen Ihr Projekt und wählen Sie ein Dataset aus.
  3. Klicken Sie rechts im Fenster auf Tabelle erstellen Tabelle erstellen
  4. Gehen Sie auf der Seite Tabelle erstellen im Abschnitt Quelle so vor:

    • Wählen Sie unter Tabelle erstellen aus die Option "Google Cloud Storage" aus.

    • Suchen Sie im Feld Datei aus GCS-Bucket auswählen nach der Datei oder dem Cloud Storage-Bucket oder geben Sie den Cloud Storage-URI ein. In der Cloud Console kann zwar nur ein URI eingefügt werden, aber Platzhalter werden unterstützt. Der Cloud Storage-Bucket muss sich am selben Standort befinden wie das Dataset, das Ihre Tabelle enthält.

      Datei auswählen

    • Wählen Sie bei Dateiformat das Format Ihrer Daten aus. Gültige Formate für externe Cloud Storage-Daten sind:

      • Kommagetrennte Werte (CSV)
      • JSON (durch Zeilenvorschub getrennt)
      • Avro
      • Datastore-Sicherung (wird auch für Firestore verwendet)
  5. Gehen Sie auf der Seite Tabelle erstellen im Abschnitt Ziel so vor:

    • Wählen Sie für Dataset-Name das passende Dataset aus.

      Dataset auswählen

    • Prüfen Sie, ob Tabellentyp auf Externe Tabelle festgelegt ist.

    • Geben Sie im Feld Tabellenname den Namen der Tabelle ein, die Sie in BigQuery erstellen.

  6. Geben Sie im Abschnitt Schema die Schemadefinition ein.

    • Bei JSON- und CSV-Dateien können Sie die Option Automatisch erkennen anklicken, um die automatische Schemaerkennung zu aktivieren. Die automatische Erkennung ist für Datastore-Exporte, Firestore-Exporte und Avro-Dateien nicht verfügbar. Die Schemainformationen für diese Dateitypen werden automatisch aus den selbstbeschreibenden Quelldaten abgerufen.
    • Für CSV- und JSON-Dateien können Sie Schemainformationen so manuell eingeben:
      • Aktivieren Sie Als Text bearbeiten und geben Sie das Tabellenschema als JSON-Array ein. Hinweis: Sie können sich das Schema einer vorhandenen Tabelle im JSON-Format anzeigen lassen. Geben Sie dafür folgenden Befehl über die Befehlszeile ein: bq show --format=prettyjson dataset.table.
      • Geben Sie das Schema mit Feld hinzufügen manuell ein.
  7. Klicken Sie auf Tabelle erstellen.

Nachdem die permanente Tabelle erstellt wurde, können Sie sie wie eine native BigQuery-Tabelle abfragen. Nach Abschluss der Abfrage können Sie die Ergebnisse als CSV- oder JSON-Datei exportieren oder als Tabelle bzw. in Google Tabellen speichern.

Klassische UI

  1. BigQuery-Web-UI öffnen
    Zur BigQuery-Web-UI

  2. Bewegen Sie den Mauszeiger im Navigationsbereich auf ein Dataset. Klicken Sie auf den Abwärtspfeil Abwärtspfeilsymbol und dann auf Create new table.

  3. Gehen Sie auf der Seite Tabelle erstellen im Abschnitt Quelldaten so vor:

    • Wählen Sie als Location die Option Cloud Storage aus und geben Sie im Quellfeld den Cloud Storage URI ein. Für Cloud Storage-URIs werden auch Platzhalter unterstützt.
    • Wählen Sie bei Dateiformat das Format Ihrer Daten aus. Gültige Formate für Cloud Storage-Daten sind:
      • Kommagetrennte Werte (CSV)
      • JSON (durch Zeilenvorschub getrennt)
      • Avro
      • Datastore-Sicherung (wird auch für Firestore verwendet)
  4. Gehen Sie auf der Seite Tabelle erstellen im Abschnitt Zieltabelle so vor:

    • Wählen Sie für Tabellenname das entsprechende Dataset aus und geben Sie in das Feld für den Tabellennamen den Namen der permanenten Tabelle ein, die Sie in BigQuery erstellen.
    • Prüfen Sie, ob Tabellentyp auf Externe Tabelle festgelegt ist.
  5. Geben Sie im Abschnitt Schema die Schemainformationen ein.

    • Bei JSON- und CSV-Dateien können Sie auf die Option Automatisch erkennen klicken, um die automatische Schemaerkennung zu aktivieren. Die automatische Erkennung ist für Datastore-Exporte, Firestore-Exporte und Avro-Dateien nicht verfügbar. Die Schemainformationen für diese Dateitypen werden automatisch aus den selbstbeschreibenden Quelldaten abgerufen.

    • Sie können CSV- oder JSON-Schemainformationen auch manuell eingeben:

      • Klicken Sie auf Als Text bearbeiten und geben Sie das Tabellenschema im JSON-Format ein.
      • Geben Sie das Schema mit Add field manuell ein.
  6. Wählen Sie die entsprechenden Elemente im Abschnitt Optionen aus und klicken Sie auf Tabelle erstellen.

Nachdem die permanente Tabelle erstellt wurde, können Sie sie wie eine native BigQuery-Tabelle abfragen. Nach Abschluss der Abfrage können Sie die Ergebnisse als CSV- oder JSON-Datei exportieren oder als Tabelle bzw. in Google Tabellen speichern.

Befehlszeile

Sie erstellen eine Tabelle im BigQuery-Befehlszeilentool mit dem Befehl bq mk. Wenn Sie über die Befehlszeile eine Tabelle erstellen, die mit einer externen Datenquelle verknüpft ist, können Sie das Schema der Tabelle so identifizieren:

  • Mit einer Tabellendefinitionsdatei, die auf Ihrem lokalen Rechner gespeichert ist
  • Mit einer Inline-Schemadefinition
  • Mit einer JSON-Schemadatei, die auf Ihrem lokalen Rechner gespeichert ist

Geben Sie den folgenden Befehl ein, um eine permanente Tabelle zu erstellen, die mit Ihrer Cloud Storage-Datenquelle über eine Tabellendefinitionsdatei verknüpft ist:

    bq mk \
    --external_table_definition=definition_file \
    dataset.table
    

Dabei gilt:

  • definition_file ist der Pfad zur Tabellendefinitionsdatei auf Ihrem lokalen Rechner.
  • dataset ist der Name des Datasets mit der Tabelle.
  • table ist der Name der Tabelle, die Sie erstellen.

Mit dem folgenden Befehl wird beispielsweise eine permanente Tabelle mit dem Namen mytable mithilfe der Tabellendefinitionsdatei mytable_def erstellt.

bq mk --external_table_definition=/tmp/mytable_def mydataset.mytable
    

Geben Sie den folgenden Befehl ein, um eine mit der externen Datenquelle verknüpfte permanente Tabelle mit einer Inline-Schemadefinition zu erstellen:

    bq mk \
    --external_table_definition=schema@source_format=Cloud Storage URI \
    dataset.table
    

Dabei gilt:

  • schema ist die Schemadefinition im Format field:data_type,field:data_type.
  • source_format ist CSV, NEWLINE_DELIMITED_JSON, AVRO oder DATASTORE_BACKUP (DATASTORE_BACKUP wird auch für Filestore verwendet).
  • Cloud Storage-URI ist Ihr Cloud Storage-URI.
  • dataset ist der Name des Datasets mit der Tabelle.
  • table ist der Name der Tabelle, die Sie erstellen.

Mit dem folgenden Befehl wird beispielsweise eine permanente Tabelle namens sales erstellt, die mit einer in Cloud Storage gespeicherten CSV-Datei mit der folgenden Schemadefinition verknüpft ist: Region:STRING,Quarter:STRING,Total_sales:INTEGER

bq mk \
    --external_table_definition=Region:STRING,Quarter:STRING,Total_sales:INTEGER@CSV=gs://mybucket/sales.csv \
    mydataset.sales
    

Geben Sie den folgenden Befehl ein, um eine mit der externen Datenquelle verknüpfte permanente Tabelle mit einer JSON-Schemadatei zu erstellen:

    bq mk \
    --external_table_definition=schema@source_format=Cloud Storage URI \
    dataset.table
    

Dabei gilt:

  • schema ist der Pfad zur JSON-Schemadatei auf Ihrem lokalen Rechner.
  • source_format ist CSV, NEWLINE_DELIMITED_JSON, AVRO oder DATASTORE_BACKUP (DATASTORE_BACKUP wird auch für Firestore verwendet).
  • Cloud Storage-URI ist Ihr Cloud Storage-URI.
  • dataset ist der Name des Datasets mit der Tabelle.
  • table ist der Name der Tabelle, die Sie erstellen.

Mit dem folgenden Befehl wird beispielsweise eine Tabelle mit dem Namen sales erstellt, die über die Schemadatei /tmp/sales_schema.json mit einer in Cloud Storage gespeicherten CSV-Datei verknüpft ist.

bq mk \
    --external_table_definition=/tmp/sales_schema.json@CSV=gs://mybucket/sales.csv \
    mydataset.sales
    

Nachdem die permanente Tabelle erstellt wurde, können Sie sie wie eine native BigQuery-Tabelle abfragen. Nach Abschluss der Abfrage können Sie die Ergebnisse als CSV- oder JSON-Datei exportieren oder als Tabelle bzw. in Google Tabellen speichern.

API

Erstellen Sie eine ExternalDataConfiguration, wenn Sie die API-Methode tables.insert verwenden. Geben Sie das Attribut schema an oder setzen Sie das Attribut autodetect auf true, um die automatische Schemaerkennung für unterstützte Datenquellen zu aktivieren.

Python

Bevor Sie dieses Beispiel anwenden, folgen Sie den Schritten zur Einrichtung von Python in der BigQuery-Kurzanleitung: Clientbibliotheken verwenden. Weitere Informationen finden Sie in der Referenzdokumentation zur BigQuery Python API.

# from google.cloud import bigquery
    # client = bigquery.Client()
    # dataset_id = 'my_dataset'

    # Configure the external data source
    dataset_ref = client.dataset(dataset_id)
    table_id = "us_states"
    schema = [
        bigquery.SchemaField("name", "STRING"),
        bigquery.SchemaField("post_abbr", "STRING"),
    ]
    table = bigquery.Table(dataset_ref.table(table_id), schema=schema)
    external_config = bigquery.ExternalConfig("CSV")
    external_config.source_uris = [
        "gs://cloud-samples-data/bigquery/us-states/us-states.csv"
    ]
    external_config.options.skip_leading_rows = 1  # optionally skip header row
    table.external_data_configuration = external_config

    # Create a permanent table linked to the GCS file
    table = client.create_table(table)  # API request

    # Example query to find states starting with 'W'
    sql = 'SELECT * FROM `{}.{}` WHERE name LIKE "W%"'.format(dataset_id, table_id)

    query_job = client.query(sql)  # API request

    w_states = list(query_job)  # Waits for query to finish
    print("There are {} states with names starting with W.".format(len(w_states)))

Cloud Storage-Daten mit temporären Tabellen abfragen

Wenn Sie eine externe Datenquelle abfragen möchten, ohne eine permanente Tabelle zu erstellen, führen Sie einen Befehl aus, um Folgendes zu kombinieren:

  • Eine Tabellendefinitionsdatei mit einer Abfrage
  • Eine Inline-Schemadefinition mit einer Abfrage
  • Eine JSON-Schemadefinitionsdatei mit einer Abfrage

Die temporäre externe Tabelle wird mit der Tabellendefinitionsdatei bzw. dem bereitgestellten Schema erstellt. Anschließend erfolgt die Abfrage der temporären externen Tabelle. Unterstützt wird die Abfrage externer Datenquellen mithilfe einer temporären Tabelle von der BigQuery-Befehlszeile und der API.

Wenn Sie eine temporäre externe Tabelle verwenden, erstellen Sie keine Tabelle in einem Ihrer BigQuery-Datasets. Da die Tabelle nicht permanent in einem Dataset gespeichert wird, kann sie nicht für andere Nutzer freigegeben werden. Das Abfragen einer externen Datenquelle mithilfe einer temporären Tabelle eignet sich für einmalige Ad-hoc-Abfragen von externen Daten sowie für ETL-Vorgänge (Extraktion, Transformation, Laden).

Erforderliche Berechtigung

Wenn Sie externe Daten in Cloud Storage mithilfe einer temporären Tabelle abfragen, benötigen Sie Berechtigungen zum Ausführen eines Abfragejobs auf Projektebene oder höher. Daneben benötigen Sie Zugriff auf das Dataset, das die Tabelle enthält, die auf die externen Daten verweist. Wenn Sie Daten in Cloud Storage abfragen, benötigen Sie auch Berechtigungen für den Zugriff auf den Bucket, der Ihre Daten enthält.

BigQuery-Berechtigungen

Zum Abfragen einer externen Tabelle in BigQuery mithilfe einer temporären Tabelle sind mindestens die folgenden Berechtigungen erforderlich.

  • bigquery.tables.getData
  • bigquery.jobs.create

Die folgenden vordefinierten Cloud IAM-Rollen umfassen Berechtigungen vom Typ bigquery.tables.getData:

  • bigquery.dataEditor
  • bigquery.dataOwner
  • bigquery.admin

Die folgenden vordefinierten Cloud IAM-Rollen umfassen Berechtigungen vom Typ bigquery.jobs.create:

  • bigquery.user
  • bigquery.jobUser
  • bigquery.admin

Wenn ein Nutzer mit Berechtigungen vom Typ bigquery.datasets.create ein Dataset erstellt, hat er darauf außerdem Zugriff als bigquery.dataOwner. Mit bigquery.dataOwner-Zugriff hat der Nutzer die Möglichkeit, externe Tabellen im Dataset zu erstellen und auf sie zuzugreifen. Zum Abfragen der Daten sind jedoch weiterhin Berechtigungen vom Typ bigquery.jobs.create erforderlich.

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

Cloud Storage-Berechtigungen

Zum Abfragen externer Daten in einem Cloud Storage-Bucket benötigen Sie die Berechtigungen vom Typ storage.objects.get. Wenn Sie einen URI-Platzhalter verwenden, benötigen Sie außerdem die Berechtigung storage.objects.list.

Mit der vordefinierten Cloud IAM-Rolle storage.objectViewer können die Berechtigungen storage.objects.get und storage.objects.list gewährt werden.

Temporäre Tabelle erstellen und abfragen

Mit der Befehlszeile, API oder den Clientbibliotheken können Sie eine temporäre Tabelle erstellen und abfragen, die mit einer externen Datenquelle verknüpft ist.

Befehlszeile

Geben Sie den Befehl bq query mit dem Flag --external_table_definition ein, um eine mit der externen Datenquelle verknüpfte temporäre Tabelle abzufragen. Wenn Sie über die Befehlszeile eine temporäre Tabelle abfragen, die mit einer externen Datenquelle verknüpft ist, können Sie das Schema der Tabelle so identifizieren:

  • Mit einer Tabellendefinitionsdatei, die auf Ihrem lokalen Rechner gespeichert ist
  • Mit einer Inline-Schemadefinition
  • Mit einer JSON-Schemadatei, die auf Ihrem lokalen Rechner gespeichert ist

(Optional) Geben Sie das Flag --location an und legen Sie als Wert Ihren Standort fest:

Geben Sie den folgenden Befehl ein, um eine mit der externen Datenquelle verknüpfte temporäre Tabelle mit einer Tabellendefinitionsdatei abzufragen:

    bq --location=location query \
    --external_table_definition=table::definition_file \
    'query'
    

Dabei gilt:

  • location ist der 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.
  • table ist der Name der temporären Tabelle, die Sie erstellen.
  • definition_file ist der Pfad zur Tabellendefinitionsdatei auf Ihrem lokalen Rechner.
  • query ist die Abfrage, die Sie an die temporäre Tabelle stellen.

Mit dem folgenden Befehl wird beispielsweise die temporäre Tabelle sales mithilfe der Tabellendefinitionsdatei sales_def erstellt und abgefragt.

bq query \
    --external_table_definition=sales::sales_def \
    'SELECT
      Region,
      Total_sales
    FROM
      sales'
    

Geben Sie den folgenden Befehl ein, um eine mit der externen Datenquelle verknüpfte temporäre Tabelle mit einer Inline-Schemadefinition abzufragen:

    bq --location=location query \
    --external_table_definition=table::schema@source_format=Cloud Storage URI \
    'query'
    

Dabei gilt:

  • location ist der 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.
  • table ist der Name der temporären Tabelle, die Sie erstellen.
  • schema ist die Inline-Schemadefinition im Format field:data_type,field:data_type.
  • source_format ist CSV, NEWLINE_DELIMITED_JSON, AVRO oder DATASTORE_BACKUP (DATASTORE_BACKUP wird auch für Firestore verwendet).
  • Cloud Storage-URI ist Ihr Cloud Storage-URI.
  • query ist die Abfrage, die Sie an die temporäre Tabelle stellen.

Mit dem folgenden Befehl wird beispielsweise die temporäre Tabelle sales erstellt und abgefragt. Die Tabelle ist mit einer in Cloud Storage gespeicherten CSV-Datei verknüpft und verwendet die Schemadefinition Region:STRING,Quarter:STRING,Total_sales:INTEGER.

bq query \
    --external_table_definition=sales::Region:STRING,Quarter:STRING,Total_sales:INTEGER@CSV=gs://mybucket/sales.csv \
    'SELECT
      Region,
      Total_sales
    FROM
      sales'
    

Geben Sie den folgenden Befehl ein, um eine mit der externen Datenquelle verknüpfte temporäre Tabelle mit einer JSON-Schemadatei abzufragen:

    bq --location=location query \
    --external_table_definition=schema_file@source_format=Cloud Storage URI \
    'query'
    

Dabei gilt:

  • location ist der 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.
  • schema_file ist der Pfad zur JSON-Schemadatei auf Ihrem lokalen Rechner.
  • source_format ist CSV, NEWLINE_DELIMITED_JSON, AVRO oder DATASTORE_BACKUP (DATASTORE_BACKUP wird auch für Firestore verwendet).
  • Cloud Storage-URI ist Ihr Cloud Storage-URI.
  • query ist die Abfrage, die Sie an die temporäre Tabelle stellen.

Mit dem folgenden Befehl wird beispielsweise die temporäre Tabelle sales erstellt und abgefragt. Die Tabelle ist mit einer in Cloud Storage gespeicherten CSV-Datei verknüpft und verwendet die Schemadatei /tmp/sales_schema.json.

bq query
--external_table_definition=sales::/tmp/sales_schema.json@CSV=gs://mybucket/sales.csv
'SELECT Region, Total_sales FROM sales'

API

Python

Bevor Sie dieses Beispiel anwenden, folgen Sie den Schritten zur Einrichtung von Python in der BigQuery-Kurzanleitung: Clientbibliotheken verwenden. Weitere Informationen finden Sie in der Referenzdokumentation zur BigQuery Python API.

from google.cloud import bigquery

    # Construct a BigQuery client object.
    client = bigquery.Client()

    # Configure the external data source and query job.
    external_config = bigquery.ExternalConfig("CSV")
    external_config.source_uris = [
        "gs://cloud-samples-data/bigquery/us-states/us-states.csv"
    ]
    external_config.schema = [
        bigquery.SchemaField("name", "STRING"),
        bigquery.SchemaField("post_abbr", "STRING"),
    ]
    external_config.options.skip_leading_rows = 1
    table_id = "us_states"
    job_config = bigquery.QueryJobConfig(table_definitions={table_id: external_config})

    # Example query to find states starting with 'W'.
    sql = 'SELECT * FROM `{}` WHERE name LIKE "W%"'.format(table_id)

    query_job = client.query(sql, job_config=job_config)  # Make an API request.

    w_states = list(query_job)  # Wait for the job to complete.
    print("There are {} states with names starting with W.".format(len(w_states)))

Extern partitionierte Daten abfragen

Weitere Informationen hierzu finden Sie unter Extern partitionierte Daten abfragen.

Unterstützung von Platzhaltern für Cloud Storage-URIs

Wenn Ihre Cloud Storage-Daten in mehrere Dateien mit einem gemeinsamen Basisnamen aufgeteilt sind, können Sie im URI in der Tabellendefinitionsdatei einen Platzhalter verwenden. Einen Platzhalter können Sie auch nutzen, wenn Sie eine externe Tabelle ohne Tabellendefinitionsdatei erstellen.

Hängen Sie als Platzhalter im Cloud Storage-URI ein Sternchen (*) an den Basisnamen an. Beispiel: Bei zwei Dateien mit den Namen fed-sample000001.csv und fed-sample000002.csv wäre der Bucket-URI gs://mybucket/fed-sample*. Dieser Platzhalter-URI kann dann in der Cloud Console, der klassischen Web-UI, der Befehlszeile, der API oder den Clientbibliotheken verwendet werden.

Sie können nur einen Platzhalter für Objekte (Dateinamen) in Ihrem Bucket verwenden. Der Platzhalter kann innerhalb oder am Ende des Objektnamens stehen. Das Anhängen eines Platzhalters an den Bucket-Namen wird nicht unterstützt.

Bei Google Datastore-Exporten kann nur ein URI angegeben werden, der außerdem auf .backup_info oder .export_metadata enden muss.

Das Platzhalterzeichen * ist nicht zulässig, wenn Sie

  • Externe Tabellen erstellen, die mit Datastore- oder Firestore-Exporten verknüpft sind, oder
  • Datastore- oder Firestore-Exportdaten aus Cloud Storage laden

Pseudospalte _FILE_NAME

Auf externen Datenquellen basierende Tabellen enthalten die Pseudospalte _FILE_NAME. In dieser Spalte wird der vollqualifizierte Pfad zu der Datei angegeben, zu der die Zeile gehört. Diese Spalte ist nur für Tabellen verfügbar, die auf externe Daten verweisen, die in Cloud Storage und Google Drive gespeichert sind.

Der Spaltenname _FILE_NAME ist reserviert, d. h. Sie können keine Spalte mit diesem Namen in Ihren Tabellen erstellen. Zum Auswählen des Werts _FILE_NAME müssen Sie einen Alias verwenden. Mit der folgenden Abfrage wählen Sie beispielsweise _FILE_NAME aus. Dabei wird der Pseudospalte der Alias fn zugewiesen:

    bq query \
    --project_id=project_id \
    --use_legacy_sql=false \
    'SELECT
       name,
       _FILE_NAME AS fn
     FROM
       `dataset.table_name`
     WHERE
       name contains "Alex"' 

Dabei gilt:

  • project_id ist eine gültige Projekt-ID. Dieses Flag ist nicht erforderlich, wenn Sie Cloud Shell verwenden oder im Cloud SDK ein Standardprojekt einrichten.
  • dataset ist der Name des Datasets, in dem die permanente externe Tabelle gespeichert ist.
  • table_name ist der Name der permanenten externen Tabelle.