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

Question

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?

Answer 1

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.