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.
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
Richten Sie Standardanmeldedaten für Anwendungen (Application Standard Credentials, ADC) mit den erforderlichen Bereichen in Ihrer lokalen Umgebung folgendermaßen ein:
Installieren Sie die Google Cloud CLI und initialisieren Sie sie mit folgendem Befehl:
gcloud init
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
Richten Sie Standardanmeldedaten für Anwendungen (Application Standard Credentials, ADC) mit den erforderlichen Bereichen in Ihrer lokalen Umgebung folgendermaßen ein:
Installieren Sie die Google Cloud CLI und initialisieren Sie sie mit folgendem Befehl:
gcloud init
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-Methodetables.insert
verwenden - Mithilfe der Clientbibliotheken
So erstellen Sie eine externe Tabelle:
Console
- Öffnen Sie in der Google Cloud Console die Seite „BigQuery“.
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
(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 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:
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.TABLE
ist der Name der Tabelle, die Sie erstellen.
Mit dem folgenden Befehl wird beispielsweise eine permanente Tabelle mit dem Namen mytable
mithilfe der Tabellendefinitionsdatei mytable_def
erstellt.
bq mk --external_table_definition=/tmp/mytable_def mydataset.mytable
Geben Sie den folgenden Befehl ein, um eine mit der externen Datenquelle verknüpfte permanente Tabelle mit einer Inline-Schemadefinition zu erstellen:
bq mk \ --external_table_definition=SCHEMA@SOURCE_FORMAT=DRIVE_URI \ DATASET.TABLE
Wobei:
SCHEMA
ist die Schemadefinition im FormatFIELD:DATA_TYPE,FIELD:DATA_TYPE
.SOURCE_FORMAT
istCSV
,NEWLINE_DELIMITED_JSON
,AVRO
oderGOOGLE_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
Wobei:
SCHEMA_FILE
ist der Pfad zur JSON-Schemadatei auf Ihrem lokalen Rechner.SOURCE_FORMAT
istCSV
,NEWLINE_DELIMITED_JSON
,AVRO
oderGOOGLE_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.
Wenn Ihre Tabellendefinitionsdatei eine Google Tabellen-spezifische Konfiguration enthält, können Sie vorangestellte Zeilen überspringen und einen definierten Tabellenbereich angeben.
Im folgenden Beispiel wird 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.
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.
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.
Die 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.