Verbundene Tabellenblätter

Mit verbundenen Tabellenblättern können Sie Petabyte an Daten direkt in Google Tabellen analysieren. Sie können Ihre Tabellen mit einem BigQuery Data Warehouse verbinden und die Analyse mit vertrauten Google Tabellen-Tools wie Pivot-Tabellen, Diagrammen und Formeln durchführen.

In dieser Anleitung wird das öffentliche Dataset Shakespeare verwendet, um zu zeigen, wie verbundene Tabellenblätter verwendet werden. Das Dataset enthält die folgenden Informationen:

Field Typ Beschreibung
Wort STRING Ein einzelnes eindeutiges Wort, bei dem Leerzeichen das Trennzeichen sind, das aus einem Korpus extrahiert wurde.
word_count INTEGER Die Häufigkeit, mit der dieses Wort in diesem Korpus vorkommt.
Korpus STRING Das Werk, aus dem dieses Wort extrahiert wurde.
corpus_date INTEGER Das Jahr, in dem dieser Korpus veröffentlicht wurde.

Wenn Ihre Anwendung Daten aus verbundenen Tabellenblättern anfordert, muss sie zusätzlich zu den anderen Bereichen, die für eine reguläre Google Sheets API-Anfrage erforderlich sind, ein OAuth 2.0-Token bereitstellen, das den Bereich bigquery.readonly gewährt. Weitere Informationen finden Sie unter Google Sheets API-Bereiche auswählen.

Datenquellen verwalten

Eine Datenquelle gibt einen externen Speicherort an, an dem Daten gefunden werden. Die Datenquelle wird dann mit der Tabellenkalkulation verbunden.

BigQuery-Datenquelle hinzufügen

Wenn Sie eine Datenquelle hinzufügen möchten, geben Sie mit der Methode spreadsheets.batchUpdate ein AddDataSourceRequest an. Im Anfragetext sollte ein dataSource-Feld vom Typ DataSource-Objekt angegeben werden.

"addDataSource":{
   "dataSource":{
      "spec":{
         "bigQuery":{
            "projectId":"PROJECT_ID",
            "tableSpec":{
               "tableProjectId":"bigquery-public-data",
               "datasetId":"samples",
               "tableId":"shakespeare"
            }
         }
      }
   }
}

Ersetzen Sie PROJECT_ID durch eine gültige Google Cloud-Projekt-ID.

Nachdem eine Datenquelle erstellt wurde, wird ein verknüpftes DATA_SOURCE-Tabellenblatt erstellt, um eine Vorschau mit bis zu 500 Zeilen anzuzeigen. Die Vorschau ist nicht sofort verfügbar. Eine Ausführung wird asynchron ausgelöst, um die BigQuery-Daten zu importieren.

Der AddDataSourceResponse enthält die folgenden Felder:

  • dataSource: Das erstellte DataSource-Objekt. Die dataSourceId ist eine eindeutige ID auf Tabellenebene. Sie wird ausgefüllt und referenziert, um jedes DataSource-Objekt aus der Datenquelle zu erstellen.

  • dataExecutionStatus: Der Status einer Ausführung, bei der BigQuery-Daten in die Vorschautabelle importiert werden. Weitere Informationen finden Sie im Abschnitt Datenausführungsstatus.

Datenquellen aktualisieren oder löschen

Verwenden Sie die Methode spreadsheets.batchUpdate und geben Sie entsprechend eine UpdateDataSourceRequest- oder DeleteDataSourceRequest-Anfrage an.

Datenquellenobjekte verwalten

Nachdem Sie der Tabelle eine Datenquelle hinzugefügt haben, kann daraus ein Datenquellenobjekt erstellt werden. Ein Datenquellenobjekt ist ein reguläres Tool für Google Tabellen, z. B. Pivot-Tabellen, Diagramme und Formeln, das in verbundene Tabellenblätter eingebunden ist, um Ihre Datenanalyse zu unterstützen.

Es gibt vier Typen von Objekten:

  • DataSource Tabelle
  • DataSource pivotTable
  • Diagramm zu DataSource
  • DataSource Formel

Datenquellentabelle hinzufügen

Das Tabellenobjekt wird im Tabelleneditor als „Extrahieren“ bezeichnet und importiert einen statischen Datendump aus der Datenquelle in Google Tabellen. Ähnlich wie bei einer Pivot-Tabelle wird die Tabelle angegeben und in der oberen linken Zelle verankert.

Im folgenden Codebeispiel wird gezeigt, wie Sie mit der Methode spreadsheets.batchUpdate und einem UpdateCellsRequest eine Datenquellentabelle mit bis zu 1.000 Zeilen aus zwei Spalten (word und word_count) erstellen.

"updateCells":{
   "rows":{
      "values":[
         {
            "dataSourceTable":{
               "dataSourceId":"DATA_SOURCE_ID",
               "columns":[
                  {
                     "name":"word"
                  },
                  {
                     "name":"word_count"
                  }
               ],
               "rowLimit":{
                  "value":1000
               },
               "columnSelectionType":"SELECTED"
            }
         }
      ]
   },
   "fields":"dataSourceTable"
}

Ersetzen Sie DATA_SOURCE_ID durch eine eindeutige ID auf Tabellenebene, die die Datenquelle identifiziert.

Nachdem eine Datenquellentabelle erstellt wurde, sind die Daten nicht sofort verfügbar. Im Google Tabellen-Editor wird er als Vorschau angezeigt. Sie müssen die Datenquellentabelle aktualisieren, um die BigQuery-Daten abzurufen. Sie können innerhalb derselben batchUpdate einen RefreshDataSourceRequest angeben. Alle Datenquellenobjekte funktionieren ähnlich. Weitere Informationen finden Sie unter Datenquellenobjekt aktualisieren.

Nachdem die Aktualisierung abgeschlossen ist und die BigQuery-Daten abgerufen wurden, wird die Datenquellentabelle so ausgefüllt:

Datenquellentabelle mit Daten aus dem öffentlichen Shakespeare-Dataset.

Datenquelle-Pivot-Tabelle hinzufügen

Im Gegensatz zu herkömmlichen Pivot-Tabellen wird eine Pivot-Tabelle aus einer Datenquelle durch eine Datenquelle unterstützt und verweist anhand des Spaltennamens auf die Daten. Das folgende Codebeispiel zeigt, wie die Methode spreadsheets.batchUpdate und ein UpdateCellsRequest zum Erstellen einer Pivot-Tabelle verwendet werden, in der die Gesamtwortzahl nach Korpus angezeigt wird.

"updateCells":{
   "rows":{
      "values":[
         {
            "pivotTable":{
               "dataSourceId":"DATA_SOURCE_ID",
               "rows":{
                  "dataSourceColumnReference":{
                     "name":"corpus"
                  },
                  "sortOrder":"ASCENDING"
               },
               "values":{
                  "summarizeFunction":"SUM",
                  "dataSourceColumnReference":{
                     "name":"word_count"
                  }
               }
            }
         }
      ]
   },
   "fields":"pivotTable"
    }

Ersetzen Sie DATA_SOURCE_ID durch eine eindeutige ID auf Tabellenebene, die die Datenquelle identifiziert.

Nachdem die BigQuery-Daten abgerufen wurden, wird die Pivot-Tabelle der Datenquelle wie folgt ausgefüllt:

Datenquellen-Pivot-Tabelle mit Daten aus dem öffentlichen Shakespeare-Dataset

Datenquellendiagramm hinzufügen

Das folgende Codebeispiel zeigt, wie Sie die Methode spreadsheets.batchUpdate und AddChartRequest zum Erstellen eines Datenquellendiagramms mit einem chartType von COLUMN verwenden, wobei die Gesamtwortzahl nach Korpus angezeigt wird.

"addChart":{
   "chart":{
      "spec":{
         "title":"Corpus by word count",
         "basicChart":{
            "chartType":"COLUMN",
            "domains":[
               {
                  "domain":{
                     "columnReference":{
                        "name":"corpus"
                     }
                  }
               }
            ],
            "series":[
               {
                  "series":{
                     "columnReference":{
                        "name":"word_count"
                     },
                     "aggregateType":"SUM"
                  }
               }
            ]
         }
      },
      "dataSourceChartProperties":{
         "dataSourceId":"DATA_SOURCE_ID"
      }
   }
}

Ersetzen Sie DATA_SOURCE_ID durch eine eindeutige ID auf Tabellenebene, die die Datenquelle identifiziert.

Nachdem die BigQuery-Daten abgerufen wurden, wird das Datenquellendiagramm wie folgt gerendert:

Datenquellendiagramm mit Daten aus dem öffentlichen Shakespeare-Dataset.

Datenquellenformel hinzufügen

Das folgende Codebeispiel zeigt, wie Sie mit der Methode spreadsheets.batchUpdate und einem UpdateCellsRequest eine Datenquellenformel erstellen und so die durchschnittliche Wortzahl berechnen können.

"updateCells":{
   "rows":[
      {
         "values":[
            {
               "userEnteredValue":{
                  "formulaValue":"=AVERAGE(shakespeare!word_count)"
               }
            }
         ]
      }
   ],
   "fields":"userEnteredValue"
}

Nachdem die BigQuery-Daten abgerufen wurden, wird die Datenquellenformel wie folgt ausgefüllt:

Datenquellenformel, die Daten aus dem öffentlichen Shakespeare-Dataset zeigt.

Datenquellenobjekt aktualisieren

Sie können ein Datenquellenobjekt aktualisieren, um die neuesten Daten aus BigQuery basierend auf den aktuellen Spezifikationen der Datenquelle und der Objektkonfigurationen abzurufen. Sie können die Methode spreadsheets.batchUpdate verwenden, um den RefreshDataSourceRequest aufzurufen. Geben Sie dann mit dem Objekt DataSourceObjectReferences einen oder mehrere Objektverweise an, die aktualisiert werden sollen.

Sie können Datenquellenobjekte in einer einzigen batchUpdate-Anfrage erstellen und aktualisieren.

Status der Datenausführung

Wenn Sie Datenquellen erstellen oder Datenquellenobjekte aktualisieren, wird eine Hintergrundausführung erstellt, um die Daten aus BigQuery abzurufen und eine Antwort mit DataExecutionStatus zurückzugeben. Wenn die Ausführung erfolgreich gestartet wurde, hat der DataExecutionState normalerweise den Status RUNNING.

Da der Prozess asynchron ist, sollte Ihre Anwendung ein Abfragemodell implementieren, um den Status der Datenquellenobjekte regelmäßig abzurufen. Verwenden Sie die Methode spreadsheets.get, bis der Status entweder den Status SUCCEEDED oder FAILED zurückgibt. Die Ausführung wird in den meisten Fällen schnell abgeschlossen, das hängt jedoch von der Komplexität Ihrer Datenquelle ab. Normalerweise dauert die Ausführung nicht länger als 10 Minuten.