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. Dabei 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 hat 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 sie 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 Console 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 von Zugriffssteuerungen 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. Daneben 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 IAM-Rollen enthalten die Berechtigungen bigquery.tables.create und bigquery.tables.getData:

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

Die folgenden vordefinierten IAM-Rollen enthalten 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 dafür außerdem bigquery.dataOwner-Zugriff. Mit bigquery.dataOwner-Zugriff hat der Nutzer die Möglichkeit, externe Tabellen im Dataset zu erstellen, zum Abfragen der Daten sind jedoch weiterhin Berechtigungen des Typs bigquery.jobs.create erforderlich.

Weitere Informationen zu 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 des Typs storage.objects.get. Wenn Sie einen URI-Platzhalter verwenden, benötigen Sie außerdem Berechtigungen des Typs storage.objects.list.

Die vordefinierte IAM-Rolle storage.objectViewer kann erteilt werden, um Berechtigungen der Typen storage.objects.get und storage.objects.list zu gewähren.

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 Google Cloud-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 Standard-Compute Engine-Dienstkonto 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:

Wenn Sie eine externe Datenquelle mithilfe einer permanenten Tabelle abfragen möchten, erstellen Sie in einem BigQuery-Dataset eine Tabelle, 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 diese mithilfe von Zugriffssteuerungen für andere Nutzer freigeben, die ebenfalls Zugriff auf die zugrunde liegende externe Datenquelle haben.

Sie haben drei Möglichkeiten, 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 mit dem bq-Befehlszeilentool erstellen, können Sie eine Tabellendefinitionsdatei verwenden, Ihre eigene Schemadatei erstellen und verwenden oder das Schema inline mit dem bq-Tool eingeben. Sie können beim Erstellen der Tabellendefinitionsdatei die automatische Schemaerkennung für unterstützte Datenquellen aktivieren.
  • Wenn Sie die Cloud Console zum Erstellen einer permanenten externen Tabelle verwenden, 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. Öffnen Sie in der Cloud Console die Seite "BigQuery".
    Zur Seite "BigQuery"
  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 Select file from GCS bucket (Datei aus dem Cloud Storage-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 wie das Dataset befinden, das die von Ihnen erstellte Tabelle enthält.

      Datei auswählen

    • 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)
  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:
      • Klicken Sie auf 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 im bq-Befehlszeilentool 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 die Tabelle 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.

bq

Sie erstellen eine Tabelle im bq-Befehlszeilentool mit dem Befehl bq mk. Wenn Sie mit dem bq-Befehlszeilentool eine mit einer externen Datenquelle verknüpfte Tabelle erstellen, 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, das die Tabelle enthält.
  • tableist 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 der Cloud Storage-URI.
  • dataset ist der Name des Datasets, das die Tabelle enthält.
  • tableist 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 der Cloud Storage-URI.
  • dataset ist der Name des Datasets, das die Tabelle enthält.
  • tableist 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 die Tabelle 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.

DDL

Sie können eine permanente externe Tabelle erstellen, indem Sie die DDL-Anweisung CREATE EXTERNAL TABLE ausführen. Sie können das Schema explizit angeben. Wenn Sie kein Schema angeben, verwendet BigQuery die automatische Schemaerkennung, um das Schema aus den externen Daten abzuleiten.

Im folgenden Beispiel wird die automatische Schemaerkennung verwendet, um eine externe Tabelle namens sales zu erstellen, die mit einer in Cloud Storage gespeicherten CSV-Datei verknüpft ist:

CREATE OR REPLACE EXTERNAL TABLE mydataset.sales
OPTIONS (
  format = 'CSV',
  uris = ['gs://mybucket/sales.csv']
)

Im nächsten Beispiel wird ein Schema explizit angegeben und die erste Zeile der CSV-Datei wird übersprungen.

CREATE OR REPLACE EXTERNAL TABLE mydataset.sales
(
  Region STRING,
  Quarter STRING,
  Total_Sales INT64
)
OPTIONS (
  format = 'CSV',
  uris = ['gs://mybucket/sales.csv'],
  skip_leading_rows = 1
)

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.

Java

Bevor Sie dieses Beispiel anwenden, 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.CsvOptions;
import com.google.cloud.bigquery.ExternalTableDefinition;
import com.google.cloud.bigquery.Field;
import com.google.cloud.bigquery.QueryJobConfiguration;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.StandardSQLTypeName;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TableInfo;
import com.google.cloud.bigquery.TableResult;

// Sample to queries an external data source using a permanent table
public class QueryExternalGCSPerm {

  public static void runQueryExternalGCSPerm() {
    // 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/us-states/us-states.csv";
    Schema schema =
        Schema.of(
            Field.of("name", StandardSQLTypeName.STRING),
            Field.of("post_abbr", StandardSQLTypeName.STRING));
    String query =
        String.format("SELECT * FROM %s.%s WHERE name LIKE 'W%%'", datasetName, tableName);
    queryExternalGCSPerm(datasetName, tableName, sourceUri, schema, query);
  }

  public static void queryExternalGCSPerm(
      String datasetName, String tableName, String sourceUri, Schema schema, String query) {
    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();

      // Skip header row in the file.
      CsvOptions csvOptions = CsvOptions.newBuilder().setSkipLeadingRows(1).build();

      TableId tableId = TableId.of(datasetName, tableName);
      // Create a permanent table linked to the GCS file
      ExternalTableDefinition externalTable =
          ExternalTableDefinition.newBuilder(sourceUri, csvOptions).setSchema(schema).build();
      bigquery.create(TableInfo.of(tableId, externalTable));

      // Example query to find states starting with 'W'
      TableResult results = bigquery.query(QueryJobConfiguration.of(query));

      results
          .iterateAll()
          .forEach(row -> row.forEach(val -> System.out.printf("%s,", val.toString())));

      System.out.println("Query on external permanent table performed successfully.");
    } catch (BigQueryException | InterruptedException e) {
      System.out.println("Query not performed \n" + e.toString());
    }
  }
}

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  Python API.

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

# Configure the external data source
dataset_ref = bigquery.DatasetReference(project, 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 Tabellendefinition bzw. dem bereitgestellten Schema erstellt. Anschließend erfolgt die Abfrage der temporären externen Tabelle. Das Abfragen einer externen Datenquelle mithilfe einer temporären Tabelle wird vom bq-Befehlszeilentool und der API unterstützt.

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 IAM-Rollen enthalten bigquery.tables.getData-Berechtigungen:

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

Die folgenden vordefinierten IAM-Rollen enthalten 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 dafür außerdem bigquery.dataOwner-Zugriff. 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 des Typs bigquery.jobs.create erforderlich.

Weitere Informationen zu 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 des Typs storage.objects.get. Wenn Sie einen URI-Platzhalter verwenden, benötigen Sie außerdem Berechtigungen des Typs storage.objects.list.

Die vordefinierte IAM-Rolle storage.objectViewer kann erteilt werden, um Berechtigungen der Typen storage.objects.get und storage.objects.list zu gewähren.

Temporäre Tabelle erstellen und abfragen

Sie können eine temporäre Tabelle erstellen und abfragen, die mit einer externen Datenquelle verknüpft ist, indem Sie das bq-Befehlszeilentool, die API oder die Clientbibliotheken verwenden.

bq

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 eine temporäre Tabelle, die mit einer externen Datenquelle verknüpft ist, mit dem bq-Befehlszeilentool abfragen, können Sie das Schema der Tabelle folgendermaßen 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 beispielsweise BigQuery in der Region "Tokio" verwenden, können Sie den Wert des Flags auf asia-northeast1 setzen. 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 beispielsweise BigQuery in der Region "Tokio" verwenden, können Sie den Wert des Flags auf asia-northeast1 setzen. 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 der 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 beispielsweise BigQuery in der Region "Tokio" verwenden, können Sie den Wert des Flags auf asia-northeast1 setzen. 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 der 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

Java

Bevor Sie dieses Beispiel anwenden, 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.CsvOptions;
import com.google.cloud.bigquery.ExternalTableDefinition;
import com.google.cloud.bigquery.Field;
import com.google.cloud.bigquery.QueryJobConfiguration;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.StandardSQLTypeName;
import com.google.cloud.bigquery.TableResult;

// Sample to queries an external data source using a temporary table
public class QueryExternalGCSTemp {

  public static void runQueryExternalGCSTemp() {
    // TODO(developer): Replace these variables before running the sample.
    String tableName = "MY_TABLE_NAME";
    String sourceUri = "gs://cloud-samples-data/bigquery/us-states/us-states.csv";
    Schema schema =
        Schema.of(
            Field.of("name", StandardSQLTypeName.STRING),
            Field.of("post_abbr", StandardSQLTypeName.STRING));
    String query = String.format("SELECT * FROM %s WHERE name LIKE 'W%%'", tableName);
    queryExternalGCSTemp(tableName, sourceUri, schema, query);
  }

  public static void queryExternalGCSTemp(
      String tableName, String sourceUri, Schema schema, String query) {
    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();

      // Skip header row in the file.
      CsvOptions csvOptions = CsvOptions.newBuilder().setSkipLeadingRows(1).build();

      // Configure the external data source and query job.
      ExternalTableDefinition externalTable =
          ExternalTableDefinition.newBuilder(sourceUri, csvOptions).setSchema(schema).build();
      QueryJobConfiguration queryConfig =
          QueryJobConfiguration.newBuilder(query)
              .addTableDefinition(tableName, externalTable)
              .build();

      // Example query to find states starting with 'W'
      TableResult results = bigquery.query(queryConfig);

      results
          .iterateAll()
          .forEach(row -> row.forEach(val -> System.out.printf("%s,", val.toString())));

      System.out.println("Query on external temporary table performed successfully.");
    } catch (BigQueryException | InterruptedException e) {
      System.out.println("Query not performed \n" + e.toString());
    }
  }
}

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  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. Wenn Sie beispielsweise zwei Dateien mit den Namen fed-sample000001.csv und fed-sample000002.csv haben, lautet dann der Bucket-URI gs://mybucket/fed-sample*. Sie können diesen Platzhalter-URI dann in der Cloud Console, dem bq-Befehlszeilentool, der API oder den Clientbibliotheken verwenden.

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 Bucketnamen 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 Sternchenzeichen ist in folgenden Fällen nicht zulässig:

  • Externe Tabellen erstellen, die mit Datastore- oder Firestore-Exporten verknüpft sind
  • 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. Sie können also keine Spalte mit diesem Namen in Ihren Tabellen erstellen. Um den Wert _FILE_NAME auszuwählen, müssen Sie einen Alias verwenden. Die folgende Beispielabfrage zeigt, wie Sie _FILE_NAME auswählen, indem Sie der Pseudospalte den Alias fn zuweisen.

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.

Wenn die Abfrage ein Filterprädikat in der Pseudospalte _FILE_NAME hat, versucht BigQuery, beim Lesen Dateien zu überspringen, die den Filter nicht erfüllen. Ähnliche Empfehlungen wie beim Abfragen von nach Aufnahmezeit partitionierten Tabellen mithilfe von Pseudospalten gelten bei der Erstellung von Abfrageprädikaten mit der Pseudospalte _FILE_NAME.