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?