Google Drive-Daten abfragen

Auf dieser Seite 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 Google Drive als auch für freigegebene Dateien. Weitere Informationen zu Google Drive finden Sie im G Suite-Schulungscenter.

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

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

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

Google Drive-URI abrufen

Um eine externe Tabelle für eine Google Drive-Datenquelle zu erstellen, müssen Sie den Google Drive-URI angeben. So rufen Sie den Google Drive-URI ab:

  1. Rufen Sie Google Drive auf.

  2. Klicken Sie mit der rechten Maustaste auf die Datei und wählen Sie Link zum Teilen abrufen aus. Der URI sollte so aussehen: https://drive.google.com/open?id=file_id.

    Dabei gilt:

    • file_id ist die alphanumerische ID der Google Drive-Datei.

Alternativ können Sie den URI abrufen, indem Sie die Datei öffnen. So rufen Sie beispielsweise den URI für eine Google Tabellen-Datei ab:

  1. Rufen Sie Google Tabellen auf.

  2. Öffnen Sie Ihre Tabelle und kopieren Sie den URI in der Adressleiste des Browsers. Der URI sollte so aussehen: https://docs.google.com/spreadsheets/d/file_id.

Zugriff auf Google Drive aktivieren

Der Zugriff auf Daten, die in Google 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. Obwohl er standardmäßig nicht aktiviert ist, kann er wie folgt in der Benutzeroberfläche, in der CLI oder über die API aufgenommen werden:

Console

Führen Sie beim Erstellen einer permanenten Tabelle über die BigQuery-Web-UI eine webbasierte Authentifizierung durch. Wenn Sie dazu aufgefordert werden, klicken Sie auf Zulassen, um BigQuery-Clienttools Zugriff auf Google Drive zu gewähren.

Klassische UI

Führen Sie beim Erstellen einer permanenten Tabelle über die BigQuery-Web-UI eine webbasierte Authentifizierung durch. Wenn Sie dazu aufgefordert werden, klicken Sie auf Zulassen, um BigQuery-Clienttools Zugriff auf Google Drive zu gewähren.

Befehlszeile

So aktivieren Sie den Zugriff auf Google Drive:

  1. Geben Sie den folgenden Befehl ein, um sicherzustellen, dass Sie die neueste Version des Befehlszeilentools verwenden.

    gcloud components update
    
  2. Geben Sie den folgenden Befehl ein, um sich bei Google 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 Google Drive an.

Python

Bevor Sie dieses Beispiel anwenden, 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",
    ]
)

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

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 Google 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 Cloud IAM-Rollen enthalten die Berechtigungen bigquery.tables.create und bigquery.tables.getData:

  • bigquery.dataEditor
  • bigquery.dataOwner
  • bigquery.admin

Die folgenden vordefinierten Cloud IAM-Rollen enthalten Berechtigungen des Typs 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.

Google 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 Google 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 Google Drive-Datenquelle verknüpft ist, müssen Sie der Instanz den OAuth-Bereich für Google Drive (https://www.googleapis.com/auth/drive) 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:

  • Mit der Cloud Console oder der klassischen BigQuery-Web-UI
  • Mit dem Befehl mk des 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 in einem BigQuery-Dataset eine Tabelle, 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.

Sie haben drei Möglichkeiten, beim Erstellen einer permanenten externen Tabelle in BigQuery Schemainformationen anzugeben:

  • Wenn Sie die permanente externe Tabelle mithilfe der API-Methode tables.insert anlegen, erstellen Sie eine Tabellenressource, die eine Schemadefinition und eine ExternalDataConfiguration enthält. Setzen Sie den Parameter autodetect auf true, um die automatische Schemaerkennung für unterstützte Datenquellen zu aktivieren.
  • Wenn Sie die permanente externe Tabelle über die Befehlszeile erstellen, können Sie wahlweise eine Tabellendefinitionsdatei verwenden, Ihre eigene Schemadatei erstellen und verwenden oder das Schema inline in die Befehlszeile eingeben. Sie können beim Erstellen der Tabellendefinitionsdatei die automatische Schemaerkennung für unterstützte Datenquellen aktivieren.
  • Wenn Sie die GCP Console oder die klassische BigQuery-Web-UI verwenden, um eine permanente externe Tabelle zu erstellen, können Sie das Tabellenschema manuell eingeben oder die automatische Schemaerkennung für unterstützte Datenquellen verwenden.

So erstellen Sie eine externe Tabelle:

Console

  1. Rufen Sie in der Cloud Console die BigQuery-Web-UI auf.
    Zur Cloud Console

  2. Maximieren Sie im Navigationsbereich im Abschnitt Ressourcen Ihr Projekt und wählen Sie ein Dataset aus. Klicken Sie rechts im Fenster auf Create table (Tabelle erstellen).

    Tabelle erstellen

  3. 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 Google Drive-Daten sind:

      • Kommagetrennte Werte (CSV)
      • Durch Zeilenumbruch getrenntes JSON
      • Avro
      • Google Tabellen
  4. (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.

  5. Gehen Sie auf der Seite Tabelle erstellen im Abschnitt Destination (Ziel) so vor: Wählen Sie unter Dataset name (Dataset-Name) das entsprechende Dataset aus. Geben Sie dann unter Tabellenname den Namen der in BigQuery erstellten Tabelle ein.

    Dataset auswählen

    • Prüfen Sie, ob für Tabellentyp die Option Externe Tabelle ausgewählt ist.
  6. 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 über die Befehlszeile ein: bq show --format=prettyjson dataset.table.
      • Geben Sie das Schema mit Feld hinzufügen manuell ein.
  7. Klicken Sie auf Tabelle erstellen.

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

Klassische UI

  1. Öffnen Sie die BigQuery-Web-UI.

    Zur BigQuery-Web-UI

  2. Bewegen Sie den Mauszeiger im Navigationsbereich auf ein Dataset, klicken Sie auf den Abwärtspfeil Abwärtspfeilsymbol und dann auf Create new table (Neue Tabelle erstellen).

  3. Gehen Sie auf der Seite Tabelle erstellen im Abschnitt Quelldaten so vor:

    • Wählen Sie Google Drive als Speicherort aus und geben Sie im Quellfeld den Google Drive-URI ein. Beachten Sie, dass Platzhalter für Google Drive-URIs nicht unterstützt werden.
    • Wählen Sie bei Dateiformat das Format Ihrer Daten aus. Gültige Formate für Google Drive-Daten sind:

      • Kommagetrennte Werte (CSV)
      • JSON (durch Zeilenvorschub getrennt)
      • Avro
      • Google Tabellen
  4. Gehen Sie auf der Seite Tabelle erstellen im Bereich Zieltabelle so vor:

    • Wählen Sie für Tabellenname das entsprechende Dataset aus und geben Sie in das Feld für den Tabellennamen den Namen der permanenten Tabelle ein, die Sie in BigQuery erstellen.
    • Prüfen Sie, ob Table type (Tabellentyp) auf External table (Externe Tabelle) festgelegt ist.
  5. Geben Sie im Abschnitt Schema die Schemainformationen ein.

    • Bei JSON- und CSV-Dateien können Sie auf die Option Automatisch erkennen klicken, um die automatische Schemaerkennung zu aktivieren. Auf der Webbenutzeroberfläche wird die automatische Schemaerkennung derzeit nicht für Google Tabellen unterstützt, ist aber über die Kommandozeile und die API verfügbar. Für Avro-Datenquellen ist die automatische Erkennung ebenfalls nicht verfügbar. Aus Avro-Dateien werden Schemainformationen automatisch abgerufen.

    • Für CSV-, JSON- oder Google Tabellen-Dateien können Sie Schemainformationen so manuell eingeben:

      • Klicken Sie auf Als Text bearbeiten und geben Sie das Tabellenschema im JSON-Format ein.
      • Geben Sie das Schema mit Feld hinzufügen manuell ein.
  6. Wählen Sie die entsprechenden Elemente im Abschnitt Optionen aus und klicken Sie dann auf Tabelle erstellen.

  7. Wählen Sie Ihr Konto aus und klicken Sie auf Allow (Zulassen), um den BigQuery-Clienttools Zugriff auf Google Drive zu gewähren.

    Dialogfeld für Clienttoolzugriff

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.

Befehlszeile

Sie erstellen eine Tabelle im BigQuery-Befehlszeilentool mit dem Befehl bq mk. Wenn Sie eine mit einer externen Datenquelle verknüpfte Tabelle über die Befehlszeile erstellen, können Sie das Schema der Tabelle so 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

Um eine permanente Tabelle zu erstellen, die über eine Tabellendefinitionsdatei mit Ihrer Google 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 Google Drive-URI.
  • 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 sales erstellt. Diese ist mit einer in Google Drive gespeicherten Google Tabellen-Datei verknüpft und verwendet die folgende Schemadefinition: 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 Google Drive-URI.
  • 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 Tabelle mit dem Namen sales erstellt, die über die Schemadatei /tmp/sales_schema.json mit einer in Google 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 anwenden, 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",
    ]
)

# TODO(developer): 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)
    )
)

Google 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. Unterstützt wird die Abfrage externer Datenquellen mithilfe einer temporären Tabelle von der BigQuery-Befehlszeile und der API.

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. 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 Cloud IAM-Rollen enthalten Berechtigungen vom Typ bigquery.tables.getData:

  • bigquery.dataEditor
  • bigquery.dataOwner
  • bigquery.admin

Diese vordefinierten Cloud IAM-Rollen enthalten die Berechtigung 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.

Google 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 die Befehlszeile, die API oder die Clientbibliotheken verwenden.

Befehlszeile

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 mit einer externen Datenquelle verknüpfte Tabelle über die Befehlszeile abfragen, können Sie das Schema der Tabelle so 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 Google 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 Google 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_format=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_format ist CSV, NEWLINE_DELIMITED_JSON, AVRO oder GOOGLE_SHEETS.
  • drive_uri ist der Google 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 Google 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: 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",
    ]
)

# TODO(developer): 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)
    )
)

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

Dabei gilt:

  • project_id ist eine gültige Projekt-ID. Dieses Flag ist nicht erforderlich, wenn Sie Cloud Shell verwenden oder im Cloud SDK 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.