Drive-Daten abfragen

In diesem Dokument wird beschrieben, wie Sie Daten abfragen, die in einer externen Tabelle von Google Drive gespeichert sind.

BigQuery unterstützt Abfragen sowohl für persönliche Dateien in Drive als auch für freigegebene Dateien. Weitere Informationen zu Google Drive finden Sie in der Schulung und Hilfe zu Google Drive.

Sie können Drive-Daten aus einer permanenten externen Tabelle oder aus einer temporären externen Tabelle abfragen, die Sie beim Ausführen der Abfrage erstellen.

Erforderliche Rollen

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

  • BigQuery Datenbetrachter (roles/bigquery.dataViewer)
  • BigQuery-Nutzer (roles/bigquery.user)

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

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

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.

Google Drive-Daten mit permanenten externen Tabellen abfragen

Nachdem Sie eine externe Drive-Tabelle erstellt haben, können Sie sie mit der Google SQL-Syntax abfragen, so als wäre sie eine Standard-BigQuery-Tabelle. Beispiel: SELECT field1, field2 FROM mydataset.my_drive_table;.

Drive-Daten mit temporären 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:

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.

Temporäre Tabellen 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'

Dabei gilt:

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

Dabei gilt:

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

Dabei gilt:

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

Beschränkungen

Eine BigQuery-Abfrage kann Google Sheets ü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. Weitere Informationen finden Sie unter Einschränkungen für externe Tabellen.

Nächste Schritte