Cloud Storage-Daten in externen Tabellen abfragen

In diesem Dokument wird gezeigt, wie Sie Daten abfragen, die in einer externen Cloud Storage-Tabelle gespeichert sind.

Hinweis

Prüfen Sie, ob die externe Cloud Storage-Tabelle vorhanden ist.

Erforderliche Rollen

Zum Abfragen externer Cloud Storage-Tabellen benötigen Sie die folgenden Rollen:

• BigQuery Datenbetrachter (roles/bigquery.dataViewer)
• BigQuery-Nutzer (roles/bigquery.user)
• Storage-Objekt-Betrachter (roles/storage.objectViewer)

Abhängig von Ihren Berechtigungen können Sie diese Rollen selbst zuweisen oder Ihren Administrator bitten, sie Ihnen zu gewähren. Weitere Informationen zum Gewähren von Rollen finden Sie unter Zuweisbare Rollen für Ressourcen aufrufen.

Wenn Sie die genauen BigQuery-Berechtigungen sehen möchten, die zum Abfragen externer Tabellen erforderlich sind, maximieren Sie den Abschnitt Erforderliche Berechtigungen:

Erforderliche Berechtigungen

• bigquery.jobs.create
• bigquery.readsessions.create (Nur erforderlich, wenn Sie Daten mit der BigQuery Storage Read API lesen)
• bigquery.tables.get
• bigquery.tables.getData

Sie können diese Berechtigungen auch mit benutzerdefinierten Rollen oder anderen vordefinierten Rollen erhalten.

Dauerhafte externe Tabellen abfragen

Nachdem Sie eine externe Cloud Storage-Tabelle erstellt haben, können Sie sie mit der GoogleSQL-Syntax abfragen, so als wäre sie eine BigQuery-Standardtabelle. Beispiel: SELECT field1, field2 FROM mydataset.my_cloud_storage_table;.

Temporäre externe Tabellen abfragen

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

Wenn Sie eine externe Datenquelle abfragen möchten, ohne eine permanente Tabelle zu erstellen, geben Sie eine Tabellendefinition für die temporäre Tabelle an und verwenden Sie diese Tabellendefinition dann in einem Befehl oder Aufruf, um die temporäre Tabelle abzufragen. Sie können die Tabellendefinition auf eine der folgenden Arten angeben:

• Eine Tabellendefinitionsdatei
• Mit einer Inline-Schemadefinition
• Eine JSON-Schemadatei

Die temporäre externe Tabelle wird mit der Tabellendefinition bzw. dem bereitgestellten Schema erstellt. Anschließend erfolgt die Abfrage der temporären externen Tabelle.

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.

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

Verwenden Sie den Befehl bq query mit dem Flag --external_table_definition, um eine mit einer 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: 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: der Name der vorläufigen Tabelle, die Sie erstellen.
• DEFINITION_FILE: der Pfad zur Tabellendefinitionsdatei auf Ihrem lokalen Rechner.
• QUERY: 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=BUCKET_PATH \
'QUERY'

Dabei gilt:

• LOCATION: 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: der Name der vorläufigen Tabelle, die Sie erstellen.
• SCHEMA: die Inline-Schemadefinition im Format field:data_type,field:data_type.
• SOURCE_FORMAT: das Format der externen Datenquelle, z. B. CSV.

• BUCKET_PATH: der Pfad zum Cloud Storage-Bucket, der die Daten für die Tabelle im Format gs://bucket_name/[folder_name/]file_pattern enthält.

  Sie können mehrere Dateien aus dem Bucket auswählen, indem Sie im file_pattern ein Sternchenzeichen (*) angeben. Beispiel: gs://mybucket/file00*.parquet. Weitere Informationen finden Sie unter Unterstützung von Platzhaltern für Cloud Storage-URIs.

  Sie können mehrere Buckets für die Option uris angeben, indem Sie mehrere Pfade angeben.

  Die folgenden Beispiele zeigen gültige uris-Werte:

  • gs://bucket/path1/myfile.csv
  • gs://bucket/path1/*.parquet
  • gs://bucket/path1/file1*, gs://bucket1/path1/*

  Wenn Sie uris-Werte angeben, die auf mehrere Dateien abzielen, müssen alle diese Dateien ein kompatibles Schema verwenden.

  Weitere Informationen zur Verwendung von Cloud Storage-URIs in BigQuery finden Sie unter Cloud Storage-Ressourcenpfad.

• QUERY: 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=BUCKET_PATH \
'QUERY'

Dabei gilt:

• LOCATION: 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: der Pfad zur JSON-Schemadatei auf Ihrem lokalen Rechner.
• SOURCE_FORMAT: das Format der externen Datenquelle, z. B. CSV.

• BUCKET_PATH: der Pfad zum Cloud Storage-Bucket, der die Daten für die Tabelle im Format gs://bucket_name/[folder_name/]file_pattern enthält.

  Sie können mehrere Dateien aus dem Bucket auswählen, indem Sie im file_pattern ein Sternchenzeichen (*) angeben. Beispiel: gs://mybucket/file00*.parquet. Weitere Informationen finden Sie unter Unterstützung von Platzhaltern für Cloud Storage-URIs.

  Sie können mehrere Buckets für die Option uris angeben, indem Sie mehrere Pfade angeben.

  Die folgenden Beispiele zeigen gültige uris-Werte:

  • gs://bucket/path1/myfile.csv
  • gs://bucket/path1/*.parquet
  • gs://bucket/path1/file1*, gs://bucket1/path1/*

  Wenn Sie uris-Werte angeben, die auf mehrere Dateien abzielen, müssen alle diese Dateien ein kompatibles Schema verwenden.

  Weitere Informationen zur Verwendung von Cloud Storage-URIs in BigQuery finden Sie unter Cloud Storage-Ressourcenpfad.

• QUERY: 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

So führen Sie eine Abfrage mithilfe der API aus:

1. Erstellen Sie ein Job-Objekt.
2. Füllen Sie den Abschnitt configuration des Objekts Job mit einem JobConfiguration-Objekt.
3. Füllen Sie den Abschnitt query des Objekts JobConfiguration mit einem JobConfigurationQuery-Objekt.
4. Füllen Sie den Abschnitt tableDefinitions des Objekts JobConfigurationQuery mit einem ExternalDataConfiguration-Objekt.
5. Rufen Sie die Methode jobs.insert auf, um die Abfrage asynchron auszuführen, oder die Methode jobs.query, um die Abfrage synchron auszuführen. Übergeben Sie dabei das Job-Objekt.

Java

Bevor Sie dieses Beispiel anwenden, folgen Sie den Schritten zur Einrichtung von Java in der BigQuery-Kurzanleitung zur Verwendung von Clientbibliotheken. Weitere Angaben finden Sie in der Referenzdokumentation zur BigQuery Java API.

Richten Sie zur Authentifizierung bei BigQuery die Standardanmeldedaten für Anwendungen ein. Weitere Informationen finden Sie unter Authentifizierung für Clientbibliotheken einrichten.

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 anwenden, folgen Sie den Schritten zur Einrichtung von Node.js in der BigQuery-Kurzanleitung zur Verwendung von Clientbibliotheken. Weitere Angaben finden Sie in der Referenzdokumentation zur BigQuery Node.js API.

Richten Sie zur Authentifizierung bei BigQuery die Standardanmeldedaten für Anwendungen ein. Weitere Informationen finden Sie unter Authentifizierung für Clientbibliotheken einrichten.

// 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 anwenden, folgen Sie den Schritten zur Einrichtung von Python in der BigQuery-Kurzanleitung zur Verwendung von Clientbibliotheken. Weitere Angaben finden Sie in der Referenzdokumentation zur BigQuery Python API.

Richten Sie zur Authentifizierung bei BigQuery die Standardanmeldedaten für Anwendungen ein. Weitere Informationen finden Sie unter Authentifizierung für Clientbibliotheken einrichten.

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

Führen Sie eine Abfrage an der Pseudospalte _FILE_NAME aus:

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, Google Drive, Amazon S3 oder Azure Blob Storage gespeichert sind.

Der Spaltenname _FILE_NAME ist reserviert, d. h. Sie können keine Spalte mit diesem Namen in Ihren Tabellen erstellen. Zur Auswahl des Werts _FILE_NAME müssen Sie ein 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"' 

Ersetzen Sie Folgendes:

• 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 nicht dem Filter entsprechen. Ä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.

Nächste Schritte

• SQL in BigQuery verwenden
• Externe Tabellen
• BigQuery-Kontingente