借助关联工作表,您可以直接在 Google 表格中分析 PB 级的数据。您可以将电子表格与 BigQuery 数据仓库相关联,然后使用熟悉的表格工具(如数据透视表、图表和公式)进行分析。
本指南使用 Shakespeare
公共数据集来展示如何使用关联工作表。该数据集包含以下信息:
字段 | 类型 | 说明 |
---|---|---|
字词 | STRING |
从语料库中提取的单个唯一单词(其中空格为分隔符)。 |
word_count | INTEGER |
该字词在此语料库中出现的次数。 |
corpus | STRING |
从中提取此字词的作品。 |
corpus_date | INTEGER |
此语料库的发布年份。 |
如果您的应用请求任何“关联工作表”数据,除了常规 Google Tables API 请求所需的其他范围之外,还必须提供一个 OAuth 2.0 令牌,用于授予 bigquery.readonly
范围。如需了解详情,请参阅选择 Google 表格 API 范围。
管理数据源
数据源指定发现数据的外部位置。然后,将数据源连接到电子表格。
添加 BigQuery 数据源
如需添加数据源,请使用 spreadsheets.batchUpdate
方法提供 AddDataSourceRequest
。请求正文应指定类型为 DataSource
对象的 dataSource
字段。
"addDataSource":{
"dataSource":{
"spec":{
"bigQuery":{
"projectId":"PROJECT_ID",
"tableSpec":{
"tableProjectId":"bigquery-public-data",
"datasetId":"samples",
"tableId":"shakespeare"
}
}
}
}
}
将 PROJECT_ID 替换为有效的 Google Cloud 项目 ID。
创建数据源后,系统会创建一个关联的 DATA_SOURCE
工作表,以提供最多 500 行的预览。预览不会立即显示。系统会异步触发执行来导入 BigQuery 数据。
AddDataSourceResponse
包含以下字段:
dataSource
:创建的DataSource
对象。dataSourceId
是电子表格范围的唯一 ID。系统会填充和引用该对象,以便从数据源创建每个DataSource
对象。dataExecutionStatus
:将 BigQuery 数据导入预览表格的执行作业的状态。如需了解详情,请参阅数据执行状态部分。
更新或删除数据源
使用 spreadsheets.batchUpdate
方法并相应地提供 UpdateDataSourceRequest
或 DeleteDataSourceRequest
请求。
管理数据源对象
将数据源添加到电子表格后,您便可以根据该数据源创建数据源对象。数据源对象是一种与“关联工作表”集成的常规表格工具(例如数据透视表、图表和公式),可为您的数据分析提供支持。
对象有以下四种类型:
DataSource
个表DataSource
pivotTableDataSource
图表DataSource
公式
添加数据源表
表对象在 Google 表格编辑器中称为“提取”,可将数据源中的数据的静态转储导入 Google 表格。与数据透视表类似,该表也可指定并锚定到左上角的单元格。
以下代码示例展示了如何使用 spreadsheets.batchUpdate
方法和 UpdateCellsRequest
创建一个不超过 1,000 行、两列(word
和 word_count
)的数据源表。
"updateCells":{
"rows":{
"values":[
{
"dataSourceTable":{
"dataSourceId":"DATA_SOURCE_ID",
"columns":[
{
"name":"word"
},
{
"name":"word_count"
}
],
"rowLimit":{
"value":1000
},
"columnSelectionType":"SELECTED"
}
}
]
},
"fields":"dataSourceTable"
}
将 DATA_SOURCE_ID 替换为用于标识数据源的电子表格范围的唯一 ID。
创建数据源表后,系统并不会立即显示相关数据。在表格编辑器中,它显示为预览。您需要刷新数据源表才能提取 BigQuery 数据。您可以在同一 batchUpdate
中指定 RefreshDataSourceRequest
。请注意,所有数据源对象的工作原理都类似。
如需了解详情,请参阅刷新数据源对象。
刷新完成并提取 BigQuery 数据后,系统将按如下所示填充数据源表:
添加数据源数据透视表
与传统的数据透视表不同,数据源数据透视表由数据源提供支持,并通过列名称引用数据。以下代码示例演示了如何使用 spreadsheets.batchUpdate
方法和 UpdateCellsRequest
创建数据透视表,以按语料库显示总字数。
"updateCells":{
"rows":{
"values":[
{
"pivotTable":{
"dataSourceId":"DATA_SOURCE_ID",
"rows":{
"dataSourceColumnReference":{
"name":"corpus"
},
"sortOrder":"ASCENDING"
},
"values":{
"summarizeFunction":"SUM",
"dataSourceColumnReference":{
"name":"word_count"
}
}
}
}
]
},
"fields":"pivotTable"
}
将 DATA_SOURCE_ID 替换为用于标识数据源的电子表格范围的唯一 ID。
提取 BigQuery 数据后,数据源数据透视表将填充如下所示:
添加数据源图表
以下代码示例演示了如何使用 spreadsheets.batchUpdate
方法和 AddChartRequest
创建一个 chartType
为 COLUMN 的数据源图表,该图表按语料库显示总字数。
"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"
}
}
}
将 DATA_SOURCE_ID 替换为用于标识数据源的电子表格范围的唯一 ID。
提取 BigQuery 数据后,数据源图表将如下所示:
添加数据源公式
以下代码示例展示了如何使用 spreadsheets.batchUpdate
方法和 UpdateCellsRequest
创建数据源公式来计算平均字数。
"updateCells":{
"rows":[
{
"values":[
{
"userEnteredValue":{
"formulaValue":"=AVERAGE(shakespeare!word_count)"
}
}
]
}
],
"fields":"userEnteredValue"
}
提取 BigQuery 数据后,数据源公式将填充,如下所示:
刷新数据源对象
您可以刷新数据源对象,以根据当前数据源规范和对象配置从 BigQuery 提取最新数据。您可以使用 spreadsheets.batchUpdate
方法来调用 RefreshDataSourceRequest
。然后,使用 DataSourceObjectReferences
对象指定要刷新的一个或多个对象引用。
请注意,您可以在单个 batchUpdate
请求中创建和刷新数据源对象。
数据执行状态
创建数据源或刷新数据源对象时,系统会创建一个后台执行,以从 BigQuery 提取数据并返回包含 DataExecutionStatus
的响应。如果执行成功开始,DataExecutionState
通常处于 RUNNING
状态。
由于该过程是异步进行的,因此您的应用应实现轮询模型,以定期检索数据源对象的状态。使用 spreadsheets.get
方法,直到状态返回 SUCCEEDED
或 FAILED
状态。在大多数情况下,执行都能快速完成,但具体取决于数据源的复杂程度。执行时间通常不会超过 10 分钟。