doubei2340 2016-10-27 08:40
浏览 60
已采纳

BigQuery-我可以对查询运行查询吗?

Background

I am developing a management system web app.

On one of the pages, the client shows a report of some aggregated values. The client report has pagination, sorting and filtering.

The server side is written in Go, and the data is stored in a large dataset in BigQuery (each table is for one day). The server code communicates with BQ using the library "google.golang.org/api/bigquery/v2".

Implementation

Since the main query takes a lot of time, I use the Query API to run the query and then cache the JobID for subsequent calls.

query := &bigquery.QueryRequest{
        DefaultDataset: "myDataSet",
        Kind:           "json",
        Query:          queryStr,
        UseQueryCache:  true,
    }
qr, err := service.Jobs.Query(project, query).Do()

// cache the job id
key := getMD5Hash(queryStr)
item := &memcache.Item{
    Key:        key,
    Value:      []byte(qr.JobReference.JobId),
    Expiration: time.Hour * 24,
}

err := memcache.Set(c.ctx, item)

Then I use the cached jobID and then use getQueryResults to get pages of the data.

qrc := service.Jobs.GetQueryResults(project, jobId)
if maxResults > 0 {
    qrc.MaxResults(int64(maxResults))
}
qrc.StartIndex(uint64(startIndex))
qrslice, err := qrc.Do()

Question

I want to filter and sort the data, but without repeating the underlying (heavy) query. Is the an option to run another query on the temporary table that was created by the original query?

Meaning that if my original table is A and I ran a query on it, resulting in a temporary table TEMP_JOB; is it possible to execute an SQL query on TEMP_JOB?

  • 写回答

1条回答 默认 最新

  • donglu1971 2016-10-27 08:51
    关注

    You can set the destinationTable property and even place in a separate dataset where you set a default expiration time eg 1 hour, 2days or whatever for the entire dataset. This way whatever table you create under that dataset automatically expires.

    This way you control the name of the name of the table you created, and this way it will automatically expire and you don't need to build a script to delete it and won't incure costs.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?