分页BigQuery [英] Paginating BigQuery

问题描述

我试图创建类似于Google的BigQuery仪表板，除了预定义的查询/视图。我遇到的问题是对数据进行分页。

tabledata endpoint 支持分页，因为您可以指定一个开始索引或使用一个页面标记，允许我做这样的事情：

'pre> query_reply = table_data_job.list（专案编号= settings.PROJECT_ID，
集编号= settings.DATASET_ID，
TABLEID =表，
的startIndex = offset，
maxResults = page_size）.execute（）


这个问题是我想运行特定的查询（或者，至少订购表数据结果）。

  query_data = {' query'：'SELECT * FROM my_dataset.foo_table LIMIT％s'％page_size} 
 query_reply = job_collection.query（projectId = settings.PROJEC T_ID，
 body = query_data）.execute（）
  

据我所知，没有办法用上面的代码做一个偏移量。这只是BigQuery不适合的吗？我想另一种方法是在内存中执行分页，并在较小的结果集上工作？ 解决方案

BigQuery查询结果是表格。因此，您可以运行查询并从结果中获取目标表，然后使用tabledata.list（）api查看结果。或者，您可以从回复中获取作业ID，然后使用 jobs.getQueryResults（） ，它有分页支持。

I'm trying to create something similar to Google's BigQuery dashboard except with predefined queries/views. The problem I'm running into is paginating the data.

The tabledata endpoint supports pagination in that you can specify a start index or use a page token, allowing me to do something like this:

query_reply = table_data_job.list(projectId=settings.PROJECT_ID,
                                  datasetId=settings.DATASET_ID,
                                  tableId=table,
                                  startIndex=offset,
                                  maxResults=page_size).execute()

The problem with this is that I would like to run specific queries (or, at the very least, order the table data results).

query_data = {'query': 'SELECT * FROM my_dataset.foo_table LIMIT %s' % page_size}
query_reply = job_collection.query(projectId=settings.PROJECT_ID,
                                   body=query_data).execute()

To my knowledge, there's no way to do an offset with the above code. Is this just something BigQuery is not suited for? I guess the alternative would be to do the pagination in memory and work on smaller result sets?

解决方案

BigQuery query results are tables. So you can run a query and get the destination table from the result and then page through the results using the tabledata.list() api. Alternately you can get the job id from the reply and use jobs.getQueryResults(), which has pagination support.

这篇关于分页BigQuery的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！