Drive-Daten abfragen

Drive-Daten abfragen

In diesem Dokument wird beschrieben, wie Sie in Google Drive gespeicherte Daten mit BigQuery abfragen können.

BigQuery unterstützt Abfragen sowohl für persönliche Dateien in Drive als auch für freigegebene Dateien. Weitere Informationen zu Drive finden Sie im G Suite-Schulungscenter.

Sie können Dateien in Drive in den folgenden Formaten abfragen:

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

Soll eine externe Drive-Datenquelle abgefragt werden, geben Sie den Drive-URI-Pfad zu den Daten an und erstellen Sie eine externe Tabelle, die auf diese Datenquelle verweist. Bei der Tabelle, die auf die Drive-Datenquelle verweist, kann es sich um eine permanente Tabelle oder eine temporäre Tabelle handeln.

Beschränkung

Eine BigQuery-Abfrage kann Google Tabellen überlasten, was zu einem Fehler wie Resources exceeded during query execution: Google Sheets service overloaded. führt. Vereinfachen Sie Ihre Tabelle, z. B. durch die Minimierung der Verwendung von Formeln.

Drive-URI abrufen

Um eine externe Tabelle für eine Drive-Datenquelle zu erstellen, müssen Sie den Drive-URI angeben. Informationen zum Abrufen des Drive-URI finden Sie unter Link zur Datei freigeben.

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

Der Zugriff auf Daten, die in Drive gehostet werden, erfordert einen zusätzlichen OAuth-Bereich, sowohl beim Definieren der föderierten Quelle als auch während der Ausführung von Abfragen. Dieser ist standardmäßig nicht aktiviert ist, kann aber in der 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:

  1. Geben Sie den folgenden Befehl ein, um sicherzustellen, dass Sie die neueste Version der Google Cloud CLI verwenden.

    gcloud components update

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

# Create credentials with Drive & BigQuery API scopes.
# Both APIs must be enabled for your project before running this code.
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)

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. 

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

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

Google Drive-Daten mit permanenten externen Tabellen abfragen

Erforderliche Berechtigungen und Bereiche

Wenn Sie externe Daten in Drive 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 den Zugriff auf die Tabellendaten erteilen. Wenn die externen Daten in Google Drive gespeichert sind, benötigen Sie auch Berechtigungen für den Zugriff auf die Google Drive-Datei, die mit Ihrer externen Tabelle verknüpft ist.

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.

Drive-Berechtigungen

Zum Abfragen von externen Daten in Google Drive muss Ihnen mindestens View-Zugriff auf die Google Drive-Datei erteilt worden sein, die mit der externen Tabelle verknüpft ist.

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 einschließlich Drive. Auf der VM ausgeführte Anwendungen rufen Google Cloud APIs über das Dienstkonto auf.

Wenn Sie eine Compute Engine-Instanz einrichten, die als Dienstkonto ausgeführt werden soll, und das Dienstkonto auf eine externe Tabelle zugreift, die mit einer Drive-Datenquelle verknüpft ist, müssen Sie der Instanz den OAuth-Bereich für Google Drive (https://www.googleapis.com/auth/drive.readonly) hinzufügen.

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:

  • Cloud Console verwenden
  • Mit dem Befehl mk des bq-Befehlszeilentools
  • Durch Erstellen einer ExternalDataConfiguration, wenn Sie die API-Methode tables.insert verwenden
  • Mit den Clientbibliotheken

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 Öffnen.

  3. Klicken Sie im Detailfeld auf Tabelle erstellen.

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

  5. (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.

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

      Dataset auswählen

    • Prüfen Sie, ob Tabellentyp auf Externe Tabelle festgelegt ist.

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

  8. Klicken Sie auf Tabelle erstellen.

  9. 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 native 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

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

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 wurde, können Sie wie bei einer nativen BigQuery-Tabelle eine Abfrage ausführen, die den Einschränkungen für externe Datenquellen unterliegt.

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.

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

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

# Create credentials with Drive & BigQuery API scopes.
# Both APIs must be enabled for your project before running this code.
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]
external_config.options.skip_leading_rows = 1  # Optionally skip header row.
external_config.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)

query_job = client.query(sql)  # Make an API request.

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

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

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

Wenn Sie externe Daten in Google Drive 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 , um die Option zu aktivieren. Wenn Sie Daten in Google Drive abfragen, benötigen Sie außerdem Berechtigungen für den Zugriff auf die Google Drive-Datei, die 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.

Drive-Berechtigungen

Zum Abfragen von externen Daten in Google Drive muss Ihnen mindestens View-Zugriff auf die Google Drive-Datei erteilt worden sein, die mit der externen Tabelle verknüpft ist.

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

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'

Wobei:

  • LOCATION ist Ihr Standort. Das Flag --location ist optional.
  • 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=DRIVE_URI \
'QUERY'

Wobei:

  • LOCATION ist Ihr Standort. Das Flag --location ist optional.
  • 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 GOOGLE_SHEETS.
  • DRIVE_URI ist der Drive-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 Drive gespeicherten CSV-Datei verknüpft und verwendet die Schemadefinition Region:STRING,Quarter:STRING,Total_sales:INTEGER.

bq --location=US query \
--external_table_definition=sales::Region:STRING,Quarter:STRING,Total_sales:INTEGER@CSV=https://drive.google.com/open?id=1234_AbCD12abCd \
'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_FORMT=DRIVE_URI \
'QUERY'

Wobei:

  • LOCATION ist Ihr Standort. Das Flag --location ist optional.
  • SCHEMA_FILE ist der Pfad zur JSON-Schemadatei auf Ihrem lokalen Rechner.
  • SOURCE_FILE ist CSV, NEWLINE_DELIMITED_JSON, AVRO oder GOOGLE_SHEETS.
  • DRIVE_URI ist der Drive-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 Drive gespeicherten CSV-Datei verknüpft und verwendet die Schemadatei /tmp/sales_schema.json.

bq query \
--external_table_definition=sales::/tmp/sales_schema.json@CSV=https://drive.google.com/open?id=1234_AbCD12abCd \
'SELECT
   Total_sales
 FROM
   sales'

API

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

# Create credentials with Drive & BigQuery API scopes.
# Both APIs must be enabled for your project before running this code.
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)

# Configure the external data source and query job.
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]
external_config.schema = [
    bigquery.SchemaField("name", "STRING"),
    bigquery.SchemaField("post_abbr", "STRING"),
]
external_config.options.skip_leading_rows = 1  # Optionally skip header row.
external_config.options.range = (
    "us-states!A20:B49"  # Optionally set range of the sheet to query from.
)
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.

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

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.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.TableResult;
import com.google.common.collect.ImmutableSet;
import java.io.IOException;

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

  public static void main(String[] args) {
    // TODO(developer): Replace these variables before running the sample.
    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 WHERE name LIKE 'W%%'", tableName);
    queryExternalSheetsTemp(tableName, sourceUri, schema, query);
  }

  public static void queryExternalSheetsTemp(
      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();

      // Configure the external data source and query job.
      ExternalTableDefinition externalTable =
          ExternalTableDefinition.newBuilder(sourceUri, sheetsOptions).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 | IOException e) {
      System.out.println("Query not performed \n" + e.toString());
    }
  }
}

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.