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.

Hinweis

Erteilen Sie IAM-Rollen (Identity and Access Management), die Nutzern die erforderlichen Berechtigungen zum Ausführen der einzelnen Aufgaben in diesem Dokument geben. Die Berechtigungen, die zum Ausführen einer Aufgabe erforderlich sind (sofern zutreffend), werden im Abschnitt "Erforderliche Berechtigungen" der Aufgabe aufgelistet.

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

Zum Abfragen externer Daten in Cloud Storage mithilfe einer permanenten Tabelle benötigen Sie Berechtigungen zum Ausführen der folgenden Aufgaben:

  • Abfragejob auf Projektebene oder höher ausführen
  • Eine Tabelle erstellen, die auf die externen Daten verweist
  • Auf die Tabelle zugreifen

Wenn Ihre externen Daten in Cloud Storage gespeichert sind, benötigen Sie auch Berechtigungen zum Zugreifen auf den Bucket, in dem die Daten enthalten sind.

Berechtigungen zum Erstellen und Abfragen von externen Tabellen in BigQuery

Zum Erstellen und Abfragen einer externen Tabelle in BigQuery benötigen Sie die folgenden IAM-Berechtigungen:

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

Die folgenden vordefinierten IAM-Rollen enthalten jeweils die Berechtigungen, die Sie zum Erstellen und Abfragen einer externen Tabelle in BigQuery benötigen:

  • roles/bigquery.dataEditor
  • roles/bigquery.dataOwner
  • roles/bigquery.admin (einschließlich der Berechtigung bigquery.jobs.create)
  • roles/bigquery.user (einschließlich der Berechtigung bigquery.jobs.create)
  • roles/bigquery.jobUser (einschließlich der Berechtigung bigquery.jobs.create)

Wenn Sie die Berechtigung bigquery.datasets.create haben, können Sie außerdem in den von Ihnen erstellten Datasets externe Tabellen erstellen und aufrufen. Sie benötigen jedoch weiterhin die Berechtigung bigquery.jobs.create, um die Daten abzufragen.

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

Berechtigungen zum Abfragen von externen Daten in einem Cloud Storage-Bucket

Zum Abfragen externer Daten in einem Cloud Storage-Bucket benötigen Sie die folgenden IAM-Berechtigungen:

  • storage.objects.get
  • storage.objects.list (erforderlich, wenn Sie einen URI-Platzhalter verwenden)

Die vordefinierte IAM-Rolle roles/storage.objectViewer umfasst alle Berechtigungen, die Sie zum Abfragen externer Daten in einem Cloud Storage-Bucket benötigen.

Zugriffsbereiche für Compute Engine-Instanzen

Wenn Sie von einer Compute Engine-Instanz aus eine externe Tabelle abfragen müssen, die mit einer Cloud Storage-Quelle verknüpft ist, muss die Instanz mindestens den Lesezugriff auf Cloud Storage haben (https://www.googleapis.com/auth/devstorage.read_only).

Mit den Bereichen steuern Sie den Zugriff der Compute Engine-Instanz auf Google Cloud-Produkte wie Cloud Storage. Anwendungen, die auf der Instanz ausgeführt werden, rufen die Google Cloud APIs über das mit der Instanz verknüpfte Dienstkonto auf.

Wenn Sie eine Compute Engine-Instanz als Standard-Compute Engine-Dienstkonto einrichten, wird der Instanz standardmäßig eine Reihe von Standardbereichen zugewiesen, einschließlich des Bereichs https://www.googleapis.com/auth/devstorage.read_only.

Wenn Sie die Instanz stattdessen mit einem benutzerdefinierten Dienstkonto einrichten, müssen Sie der Instanz explizit den Bereich https://www.googleapis.com/auth/devstorage.read_only zuweisen.

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 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 diese mithilfe von Zugriffssteuerungen für andere Nutzer freigeben, die ebenfalls Zugriff auf die zugrunde liegende externe Datenquelle haben.

Beim Erstellen einer permanenten externen Tabelle können Sie das Schema so angeben:

So erstellen Sie eine externe Tabelle:

Console

  1. Öffnen Sie in der Cloud Console die Seite „BigQuery“.

BigQuery aufrufen

  1. Maximieren Sie im Bereich Explorer Ihr Projekt und wählen Sie ein Dataset aus.

  2. Maximieren Sie die Option Aktionen und klicken Sie auf Tabelle erstellen.

  3. 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 dem 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 wie das Dataset befinden, das die von Ihnen erstellte Tabelle enthält.

    • Wählen Sie bei Dateiformat das Format Ihrer Daten aus.

  4. Gehen Sie auf der Seite Tabelle erstellen im Abschnitt Ziel folgendermaßen vor:

    • Wählen Sie für Dataset das entsprechende Dataset aus.
    • Prüfen Sie, ob Tabellentyp auf Externe Tabelle festgelegt ist.
    • Geben Sie im Feld Tabelle den Namen der Tabelle ein, die Sie in BigQuery erstellen.

  5. Im Abschnitt Schema können Sie entweder die automatische Schemaerkennung aktivieren oder ein Schema manuell angeben.

    • Klicken Sie auf die Option Automatisch erkennen, um die automatische Schemaerkennung zu aktivieren.

    • Wenn Sie ein Schema manuell angeben möchten, klicken Sie die Option Automatisch erkennen nicht an und führen dann einen der folgenden Schritte aus:

      • Klicken Sie auf Als Text bearbeiten und geben Sie das Tabellenschema als JSON-Array ein.

  6. 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.

SQL

Sie können eine permanente externe Tabelle erstellen, indem Sie die CREATE EXTERNAL TABLEDDL-Anweisung 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:

  1. Rufen Sie in der Cloud Console die Seite BigQuery auf.

    BigQuery aufrufen

  2. Geben Sie im Abfrageeditor die folgende Anweisung ein:

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

  3. Klicken Sie auf Ausführen.

Informationen zum Ausführen von Abfragen finden Sie unter Interaktive Abfragen ausführen.

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);

bq

Verwenden Sie zum Erstellen einer externen Tabelle den Befehl bq mk mit dem --external_table_definition-Flag. Dieses Flag enthält entweder einen Pfad zu einer Tabellendefinitionsdatei oder eine Inline-Tabellendefinition.

Option 1: Tabellendefinitionsdatei

Verwenden Sie den Befehl bq mkdef, um eine Tabellendefinitionsdatei zu erstellen, und übergeben Sie dann den Dateipfad an den bq mk-Befehl so:

bq mkdef --source_format=SOURCE_FORMAT \
  BUCKET_URL > DEFINITION_FILE

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

Hierbei gilt:

  • SOURCE_FORMAT ist das Format der externen Datenquelle, z. B. CSV.
  • BUCKET_URI ist der Cloud Storage-URI.
  • DEFINITION_FILE ist der Pfad zur Tabellendefinitionsdatei auf Ihrem lokalen Rechner.
  • DATASET_NAME ist der Name des Datasets, das die Tabelle enthält.
  • TABLE_NAME ist der Name der Tabelle ist, die Sie erstellen.
  • SCHEMA gibt einen Pfad zu einer JSON-Schemadatei oder das Schema im Format field:data_type,field:data_type,... an.

Beispiel:

bq mkdef --source_format=CSV gs://mybucket/sales.csv > mytable_def

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

Um die automatische Schemaerkennung zu verwenden, geben Sie im mkdef-Befehl das --autodetect=true-Flag an und lassen das Schema weg:

bq mkdef --source_format=CSV --autodetect=true \
  gs://mybucket/sales.csv > mytable_def

bq mk --table --external_table_definition=mytable_def \
  mydataset.mytable

Option 2: Inline-Tabellendefinition

Anstatt eine Tabellendefinitionsdatei zu erstellen, können Sie die Tabellendefinition direkt an den bq mk-Befehl übergeben:

bq mk --table \
  --external_table_definition=@SOURCE_FORMAT=BUCKET_URI \
  DATASET_NAME.TABLE_NAME \
  SCHEMA

Hierbei gilt:

  • SOURCE_FORMAT ist das Format der externen Datenquelle, z. B. CSV.
  • BUCKET_URI ist der Cloud Storage-URI.
  • DATASET_NAME ist der Name des Datasets, das die Tabelle enthält.
  • TABLE_NAME ist der Name der Tabelle ist, die Sie erstellen.
  • SCHEMA gibt einen Pfad zu einer JSON-Schemadatei oder das Schema im Format field:data_type,field:data_type,... an. Wenn Sie die automatische Schemaerkennung verwenden möchten, lassen Sie dieses Argument weg.

Beispiel:

bq mkdef --source_format=CSV gs://mybucket/sales.csv > mytable_def
bq mk --table --external_table_definition=mytable_def \
  mydataset.mytable \
  Region:STRING,Quarter:STRING,Total_sales:INTEGER

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 ausprobieren, 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());
    }
  }
}

Node.js

Bevor Sie dieses Beispiel ausprobieren, folgen Sie den Schritten zur Einrichtung von Node.js in der BigQuery-Kurzanleitung: Clientbibliotheken verwenden. Weitere Angaben finden Sie in der Referenzdokumentation zur BigQuery Node.js API. 

// Import the Google Cloud client library and create a client
const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery();

async function queryExternalGCSPerm() {
  // Queries an external data source using a permanent table

  /**
   * TODO(developer): Uncomment the following lines before running the sample.
   */
  // const datasetId = "my_dataset";
  // const tableId = "my_table";

  // Configure the external data source
  const dataConfig = {
    sourceFormat: 'CSV',
    sourceUris: ['gs://cloud-samples-data/bigquery/us-states/us-states.csv'],
    // Optionally skip header row
    csvOptions: {skipLeadingRows: 1},
  };

  // For all options, see https://cloud.google.com/bigquery/docs/reference/v2/tables#resource
  const options = {
    schema: schema,
    externalDataConfiguration: dataConfig,
  };

  // Create an external table linked to the GCS file
  const [table] = await bigquery
    .dataset(datasetId)
    .createTable(tableId, options);

  console.log(`Table ${table.id} created.`);

  // Example query to find states starting with 'W'
  const query = `SELECT post_abbr
  FROM \`${datasetId}.${tableId}\`
  WHERE name LIKE 'W%'`;

  // Run the query as a job
  const [job] = await bigquery.createQueryJob(query);
  console.log(`Job ${job.id} started.`);

  // Wait for the query to finish
  const [rows] = await job.getQueryResults();

  // Print the results
  console.log('Rows:');
  console.log(rows);
}

Python

Bevor Sie dieses Beispiel ausprobieren, 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 Berechtigungen

Zum Abfragen von externen Daten in Cloud Storage mithilfe einer temporären Tabelle benötigen Sie Berechtigungen zum Ausführen eines Abfragejobs auf Projektebene oder höher. Außerdem benötigen Sie Zugriff auf das Dataset mit der Tabelle, die auf die externen Daten verweist. Zum Abfragen von Daten in Cloud Storage benötigen Sie außerdem Berechtigungen zum Zugreifen auf den Bucket, in dem die Daten enthalten sind.

Berechtigungen zum Abfragen von externen Tabellen in BigQuery

Zum Abfragen einer externen Tabelle in BigQuery mithilfe einer temporären Tabelle benötigen Sie die folgenden IAM-Berechtigungen:

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

Die folgenden vordefinierten IAM-Rollen enthalten jeweils die Berechtigungen, die zum Abfragen einer externen Tabelle in BigQuery mithilfe einer temporären Tabelle erforderlich sind:

  • roles/bigquery.dataEditor
  • roles/bigquery.dataOwner
  • roles/bigquery.admin (einschließlich der Berechtigung bigquery.jobs.create)
  • roles/bigquery.user (einschließlich der Berechtigung bigquery.jobs.create)
  • roles/bigquery.jobUser (einschließlich der Berechtigung bigquery.jobs.create)

Wenn Sie die Berechtigung bigquery.datasets.create haben, können Sie außerdem in den von Ihnen erstellten Datasets externe Tabellen erstellen und aufrufen. Sie benötigen jedoch weiterhin die Berechtigung bigquery.jobs.create, um die Daten abzufragen.

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

Berechtigungen zum Abfragen von externen Daten in einem Cloud Storage-Bucket

Zum Abfragen externer Daten in einem Cloud Storage-Bucket benötigen Sie die folgenden IAM-Berechtigungen:

  • storage.objects.get
  • storage.objects.list (erforderlich, wenn Sie einen URI-Platzhalter verwenden)

Die vordefinierte IAM-Rolle roles/storage.objectViewer umfasst alle Berechtigungen, die Sie zum Abfragen externer Daten in einem Cloud Storage-Bucket benötigen.

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'

Hierbei 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'

Hierbei 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 das Format der externen Datenquelle, z. B. CSV.
  • 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'

Hierbei 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 das Format der externen Datenquelle, z. B. CSV.
  • 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 ausprobieren, 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());
    }
  }
}

Node.js

Bevor Sie dieses Beispiel ausprobieren, folgen Sie den Schritten zur Einrichtung von Node.js in der BigQuery-Kurzanleitung: Clientbibliotheken verwenden. Weitere Angaben finden Sie in der Referenzdokumentation zur BigQuery Node.js API. 

// Import the Google Cloud client library and create a client
const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery();

async function queryExternalGCSTemp() {
  // Queries an external data source using a temporary table.

  const tableId = 'us_states';

  // Configure the external data source
  const externalDataConfig = {
    sourceFormat: 'CSV',
    sourceUris: ['gs://cloud-samples-data/bigquery/us-states/us-states.csv'],
    // Optionally skip header row.
    csvOptions: {skipLeadingRows: 1},
    schema: {fields: schema},
  };

  // Example query to find states starting with 'W'
  const query = `SELECT post_abbr
  FROM \`${tableId}\`
  WHERE name LIKE 'W%'`;

  // For all options, see https://cloud.google.com/bigquery/docs/reference/v2/tables#resource
  const options = {
    query,
    tableDefinitions: {[tableId]: externalDataConfig},
  };

  // Run the query as a job
  const [job] = await bigquery.createQueryJob(options);
  console.log(`Job ${job.id} started.`);

  // Wait for the query to finish
  const [rows] = await job.getQueryResults();

  // Print the results
  console.log('Rows:');
  console.log(rows);
}

Python

Bevor Sie dieses Beispiel ausprobieren, 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.

Im Cloud Storage-URI hängen Sie dabei als Platzhalter ein Sternchen (*) an den Basisnamen an.

Beispiele:

  • Mit dem folgenden Platzhalter-URI werden alle Dateien in allen Ordnern ausgewählt, die mit dem Präfix gs://mybucket/fed-samples/fed-sample beginnen:

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

  • Mit dem folgenden Platzhalter-URI werden nur Dateien mit der Erweiterung .csv im Ordner namens fed-samples und allen Unterordnern von fed-samples ausgewählt:

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

  • Mit dem folgenden Platzhalter-URI werden Dateien mit dem Benennungsmuster fed-sample*.csv im Ordner fed-samples ausgewählt. 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.

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. Mehrere Platzhalter werden im Quell-URI nicht unterstützt. Der Pfad gs://mybucket/fed-*/temp/*.csv ist beispielsweise ungültig.

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"'

Hierbei gilt:

  • project_id ist eine gültige Projekt-ID. Dieses Flag ist nicht erforderlich, wenn Sie Cloud Shell verwenden oder in der Google Cloud CLI 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.