Externe Google Drive-Tabellen erstellen

In diesem Dokument wird beschrieben, wie eine externe Tabelle über Daten erstellt wird, die in Google Drive gespeichert sind.

BigQuery unterstützt externe Tabellen sowohl für persönliche Dateien in Drive als auch für freigegebene Dateien. Weitere Informationen zu Google Drive finden Sie unter Google Drive-Schulungen und -Hilfe.

Sie können externe Tabellen in Drive erstellen, die die folgenden Formate haben:

Kommagetrennte Werte (CSV)
Durch Zeilenumbruch getrenntes JSON
Avro
Google Tabellen

Hinweis

Sammeln Sie vor dem Erstellen einer externen Tabelle einige Informationen und prüfen Sie, ob Sie die Berechtigung zum Erstellen der Tabelle haben.

Drive-URIs abrufen

Sie müssen den Drive-URI angeben, um eine externe Tabelle für eine Drive-Datenquelle zu erstellen. Sie können den Drive-URI direkt aus der URL Ihrer Drive-Daten abrufen:

URI-Format

https://docs.google.com/spreadsheets/d/FILE_ID

oder
https://drive.google.com/open?id=FILE_ID

Dabei ist FILE_ID die alphanumerische ID der Drive-Datei.

Drive-Zugriff aktivieren

Für den Zugriff auf in Drive gehostete Daten ist ein zusätzlicher OAuth-Bereich erforderlich. Dieser ist standardmäßig nicht aktiviert ist, kann aber in der Google Cloud Console, im bq-Befehlszeilentool oder über die API mit den folgenden Methoden hinzugefügt werden:

Console

Führen Sie beim Erstellen einer permanenten Tabelle in der Cloud Console eine webbasierte Authentifizierung aus. Klicken Sie, wenn Sie dazu aufgefordert werden, auf Zulassen, um BigQuery-Clienttools Zugriff auf Google Drive zu gewähren.

gcloud

So aktivieren Sie den Zugriff auf Google Drive:

Geben Sie den folgenden Befehl ein, um sicherzustellen, dass Sie die neueste Version der Google Cloud CLI verwenden.
```
gcloud components update
```
Geben Sie den folgenden Befehl ein, um sich bei Drive zu authentifizieren.
```
gcloud auth login --enable-gdrive-access
```

API

Fordern Sie bei Verwendung der BigQuery API zusätzlich zum Bereich für BigQuery den OAuth-Bereich für Drive an.

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
import google.auth

# Create credentials with Drive & BigQuery API scopes.
# Both APIs must be enabled for your project before running this code.
#
# If you are using credentials from gcloud, you must authorize the
# application first with the following command:
#
# gcloud auth application-default login \
#   --scopes=https://www.googleapis.com/auth/drive,https://www.googleapis.com/auth/cloud-platform
credentials, project = google.auth.default(
    scopes=[
        "https://www.googleapis.com/auth/drive",
        "https://www.googleapis.com/auth/cloud-platform",
    ]
)

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

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.

GoogleCredentials credentials =
    ServiceAccountCredentials.getApplicationDefault()
        .createScoped(
            ImmutableSet.of(
                "https://www.googleapis.com/auth/bigquery",
                "https://www.googleapis.com/auth/drive"));

// 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.newBuilder().setCredentials(credentials).build().getService();

Erforderliche Rollen

Zum Erstellen einer externen Tabelle benötigen Sie die IAM-Berechtigung (BigQuery Identity and Access Management) bigquery.tables.create.

Jede der folgenden vordefinierten Rollen für das Identity and Access Management enthält diese Berechtigung:

BigQuery Datenmitbearbeiter (roles/bigquery.dataEditor)
BigQuery Dateninhaber (roles/bigquery.dataOwner)
BigQuery Administrator (roles/bigquery.admin)

Wenn Sie in keiner dieser Rollen ein Hauptkonto sind, bitten Sie Ihren Administrator, Ihnen Zugriff zu gewähren oder die externe Tabelle für Sie zu erstellen.

Weitere Informationen zu Rollen und Berechtigungen für das Identity and Access Management in BigQuery finden Sie unter Vordefinierte Rollen und Berechtigungen.

Externe Tabellen erstellen

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

Google Cloud Console verwenden
Befehl mk des bq-Befehlszeilentools verwenden
Durch Erstellen einer ExternalDataConfiguration, wenn Sie die API-Methode tables.insert verwenden
Mithilfe der Clientbibliotheken

So erstellen Sie eine externe Tabelle:

Console

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

BigQuery aufrufen

Maximieren Sie im Bereich Explorer Ihr Projekt und wählen Sie ein Dataset aus.
Maximieren Sie die Option Aktionen und klicken Sie auf Öffnen.
Klicken Sie im Detailfeld auf Tabelle erstellen.
Gehen Sie auf der Seite Tabelle erstellen im Abschnitt Quelle so vor:
- Wählen Sie unter Tabelle erstellen aus die Option Drive aus.
- Geben Sie im Feld Drive-URI auswählen den Google Drive-URI ein. Beachten Sie, dass Platzhalter bei Google Drive-URIs nicht unterstützt werden.
- Wählen Sie bei Dateiformat das Format Ihrer Daten aus. Gültige Formate für Drive-Daten sind:
  - Kommagetrennte Werte (CSV)
  - Durch Zeilenumbruch getrenntes JSON
  - Avro
  - Tabellen
  Hinweis: Wenn Sie in der Google Cloud Console Avro- oder Datastore-Sicherungen auswählen, wird die Option Automatisch erkennen ausgeblendet, da diese Dateitypen selbstbeschreibend sind.
(Optional) Wenn Sie Google Tabellen auswählen, geben Sie im Feld Tabellenbereich (optional) den abzufragenden Tabellen- und Zellenbereich an. Sie können einen Tabellennamen angeben oder einen Zellenbereich im Format sheet_name!top_left_cell_id:bottom_right_cell_id angeben, beispielsweise "Sheet1! A1:B20". Wenn bei Tabellenbereich nichts angegeben ist, wird das erste Tabellenblatt in der Datei verwendet.
Gehen Sie auf der Seite Tabelle erstellen im Abschnitt Ziel folgendermaßen vor:
- Wählen Sie unter Dataset name (Dataset-Name) das entsprechende Dataset aus. Geben Sie dann unter Table name (Tabellenname) den Namen der in BigQuery erstellten Tabelle ein.
- Prüfen Sie, ob Tabellentyp auf Externe Tabelle festgelegt ist.
Geben Sie im Abschnitt Schema die Schemadefinition 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.
- So können Sie Schemainformationen manuell eingeben:
  - Klicken Sie auf Als Text bearbeiten und geben Sie das Tabellenschema als JSON-Array ein. Hinweis: Sie können das Schema einer vorhandenen Tabelle im JSON-Format ansehen. 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.
Klicken Sie auf Tabelle erstellen.
Wählen Sie ggf. Ihr Konto aus und klicken Sie auf Zulassen, um den BigQuery-Clienttools Zugriff auf Google Drive zu gewähren.

Sie können die Tabelle anschließend wie eine Standard-BigQuery-Tabelle abfragen. Dabei gelten die Einschränkungen für externe Datenquellen.

Nach Abschluss der Abfrage können Sie die Ergebnisse im CSV- oder JSON-Format herunterladen oder als Tabelle bzw. in Google Tabellen speichern. Weitere Informationen finden Sie unter Daten herunterladen, speichern und exportieren.

bq

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

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

Um eine permanente Tabelle zu erstellen, die über eine Tabellendefinitionsdatei mit Ihrer Drive-Datenquelle verknüpft ist, geben Sie den folgenden Befehl ein.

bq mk \
--external_table_definition=DEFINITION_FILE \
DATASET.TABLE

Wobei:

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=DRIVE_URI \
DATASET.TABLE

Wobei:

SCHEMA ist die Schemadefinition im Format FIELD:DATA_TYPE,FIELD:DATA_TYPE.
SOURCE_FORMAT ist CSV, NEWLINE_DELIMITED_JSON, AVRO oder GOOGLE_SHEETS.
DRIVE_URI ist der Drive-URI.
DATASET ist der Name des Datasets, das die Tabelle enthält.
TABLE der Name der Tabelle ist, die Sie erstellen.

Mit dem folgenden Befehl wird beispielsweise eine permanente Tabelle namens sales erstellt, die mit einer in Google Drive gespeicherten Google Tabellen-Datei verknüpft ist und dabei die folgende Schemadefinition hat: Region:STRING,Quarter:STRING,Total_sales:INTEGER

bq mk \
--external_table_definition=Region:STRING,Quarter:STRING,Total_sales:INTEGER@GOOGLE_SHEETS=https://drive.google.com/open?id=1234_AbCD12abCd \
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_FILE@SOURCE_FORMAT=DRIVE_URI \
DATASET.TABLE

Wobei:

SCHEMA_FILE ist der Pfad zur JSON-Schemadatei auf Ihrem lokalen Rechner.
SOURCE_FORMAT ist CSV, NEWLINE_DELIMITED_JSON, AVRO oder GOOGLE_SHEETS.
DRIVE_URI ist der Drive-URI.
DATASET ist der Name des Datasets, das die Tabelle enthält.
TABLE der Name der Tabelle ist, 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 Drive gespeicherten CSV-Datei verknüpft ist.

bq mk \
--external_table_definition=/tmp/sales_schema.json@CSV=https://drive.google.com/open?id=1234_AbCD12abCd \
mydataset.sales

Nachdem die permanente Tabelle erstellt worden ist, können Sie dafür wie bei einer Standard-BigQuery-Tabelle eine Abfrage ausführen. Diese unterliegt den Beschränkungen für externe Datenquellen.

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

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
import google.auth

# Create credentials with Drive & BigQuery API scopes.
# Both APIs must be enabled for your project before running this code.
#
# If you are using credentials from gcloud, you must authorize the
# application first with the following command:
#
# gcloud auth application-default login \
#   --scopes=https://www.googleapis.com/auth/drive,https://www.googleapis.com/auth/cloud-platform
credentials, project = google.auth.default(
    scopes=[
        "https://www.googleapis.com/auth/drive",
        "https://www.googleapis.com/auth/bigquery",
    ]
)

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

# TODO(developer): Set dataset_id to the ID of the dataset to fetch.
# dataset_id = "your-project.your_dataset"

# Configure the external data source.
dataset = client.get_dataset(dataset_id)
table_id = "us_states"
schema = [
    bigquery.SchemaField("name", "STRING"),
    bigquery.SchemaField("post_abbr", "STRING"),
]
table = bigquery.Table(dataset.table(table_id), schema=schema)
external_config = bigquery.ExternalConfig("GOOGLE_SHEETS")
# Use a shareable link or grant viewing access to the email address you
# used to authenticate with BigQuery (this example Sheet is public).
sheet_url = (
    "https://docs.google.com/spreadsheets"
    "/d/1i_QCL-7HcSyUZmIbP9E6lO_T5u3HnpLe7dnpHaijg_E/edit?usp=sharing"
)
external_config.source_uris = [sheet_url]
options = external_config.google_sheets_options
assert options is not None
options.skip_leading_rows = 1  # Optionally skip header row.
options.range = (
    "us-states!A20:B49"  # Optionally set range of the sheet to query from.
)
table.external_data_configuration = external_config

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

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

results = client.query_and_wait(sql)  # Make an API request.

# Wait for the query to complete.
w_states = list(results)
print(
    "There are {} states with names starting with W in the selected range.".format(
        len(w_states)
    )
)

Java

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.auth.oauth2.GoogleCredentials;
import com.google.auth.oauth2.ServiceAccountCredentials;
import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.ExternalTableDefinition;
import com.google.cloud.bigquery.Field;
import com.google.cloud.bigquery.GoogleSheetsOptions;
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;
import com.google.common.collect.ImmutableSet;
import java.io.IOException;

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

  public static void main(String[] args) {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    String sourceUri =
        "https://docs.google.com/spreadsheets/d/1i_QCL-7HcSyUZmIbP9E6lO_T5u3HnpLe7dnpHaijg_E/edit?usp=sharing";
    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);
    queryExternalSheetsPerm(datasetName, tableName, sourceUri, schema, query);
  }

  public static void queryExternalSheetsPerm(
      String datasetName, String tableName, String sourceUri, Schema schema, String query) {
    try {

      // Create credentials with Drive & BigQuery API scopes.
      // Both APIs must be enabled for your project before running this code.
      GoogleCredentials credentials =
          ServiceAccountCredentials.getApplicationDefault()
              .createScoped(
                  ImmutableSet.of(
                      "https://www.googleapis.com/auth/bigquery",
                      "https://www.googleapis.com/auth/drive"));

      // 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.newBuilder().setCredentials(credentials).build().getService();

      // Skip header row in the file.
      GoogleSheetsOptions sheetsOptions =
          GoogleSheetsOptions.newBuilder()
              .setSkipLeadingRows(1) // Optionally skip header row.
              .setRange("us-states!A20:B49") // Optionally set range of the sheet to query from.
              .build();

      TableId tableId = TableId.of(datasetName, tableName);
      // Create a permanent table linked to the Sheets file.
      ExternalTableDefinition externalTable =
          ExternalTableDefinition.newBuilder(sourceUri, sheetsOptions).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 | IOException e) {
      System.out.println("Query not performed \n" + e.toString());
    }
  }
}

Externe Tabellen abfragen

Weitere Informationen finden Sie unter Drive-Daten abfragen.

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.