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 Sheets

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 authentifizieren und aktivieren

Für den Zugriff auf in Drive gehostete Daten ist ein zusätzlicher OAuth-Bereich erforderlich. So authentifizieren Sie sich bei BigQuery und aktivieren den Zugriff auf Drive:

Console

Folgen Sie den Schritten zur webbasierten Authentifizierung, wenn Sie eine externe Tabelle in der Google Cloud Console erstellen. Klicken Sie, wenn Sie dazu aufgefordert werden, auf Zulassen, um BigQuery-Clienttools Zugriff auf Google Drive zu gewähren.

gcloud

In the Google Cloud console, activate Cloud Shell.

Activate Cloud Shell

At the bottom of the Google Cloud console, a Cloud Shell session starts and displays a command-line prompt. Cloud Shell is a shell environment with the Google Cloud CLI already installed and with values already set for your current project. It can take a few seconds for the session to initialize.
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 zusätzlich zum Bereich für BigQuery den entsprechenden OAuth-Bereich für Drive an:

Melden Sie sich mit dem gcloud auth login --enable-gdrive-access-Befehl an.
Rufen Sie mit dem gcloud auth print-access-token-Befehl das OAuth-Zugriffstoken mit dem Drive-Bereich ab, das für Ihre API verwendet wird.

Python

OAuth-Client-ID generieren.
Richten Sie Standardanmeldedaten für Anwendungen (Application Standard Credentials, ADC) mit den erforderlichen Bereichen in Ihrer lokalen Umgebung folgendermaßen ein:
1. Installieren Sie die Google Cloud CLI und initialisieren Sie sie mit folgendem Befehl:
```
gcloud init
```
2. Erstellen Sie lokale Anmeldedaten zur Authentifizierung für Ihr Google-Konto:
```
gcloud auth application-default login \
    --client-id-file=CLIENT_ID_FILE \
    --scopes=https://www.googleapis.com/auth/drive,https://www.googleapis.com/auth/cloud-platform
```
  Ersetzen Sie CLIENT_ID_FILE durch die Datei mit Ihrer OAuth-Client-ID.
  
  Weitere Informationen finden Sie unter Nutzeranmeldedaten, die über die gcloud CLI bereitgestellt werden.

Java

OAuth-Client-ID generieren.
Richten Sie Standardanmeldedaten für Anwendungen (Application Standard Credentials, ADC) mit den erforderlichen Bereichen in Ihrer lokalen Umgebung folgendermaßen ein:
1. Installieren Sie die Google Cloud CLI und initialisieren Sie sie mit folgendem Befehl:
```
gcloud init
```
2. Erstellen Sie lokale Anmeldedaten zur Authentifizierung für Ihr Google-Konto:
```
gcloud auth application-default login \
    --client-id-file=CLIENT_ID_FILE \
    --scopes=https://www.googleapis.com/auth/drive,https://www.googleapis.com/auth/cloud-platform
```
  Ersetzen Sie CLIENT_ID_FILE durch die Datei mit Ihrer OAuth-Client-ID.
  
  Weitere Informationen finden Sie unter Nutzeranmeldedaten, die über die gcloud CLI bereitgestellt werden.

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

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=DRIVE_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 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 Sheets-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

Dabei gilt:

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

from google.cloud import bigquery
import google.auth

credentials, project = google.auth.default()

# 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

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 {

      GoogleCredentials credentials =
          ServiceAccountCredentials.getApplicationDefault();

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

Externe Google Drive-Tabellen erstellen

Hinweis

Drive-URIs abrufen

Drive-Zugriff authentifizieren und aktivieren

Console

gcloud

API

Python

Java

Erforderliche Rollen

Externe Tabellen erstellen

Console

bq

API

Python

Java

Externe Tabellen abfragen

Pseudospalte _FILE_NAME

Pseudospalte `_FILE_NAME`