Reputation: 4977
I am using App Engine Standard Environment (autoscaled), which means I have a limit of 10 mins before a request is cancelled.
Goal is to query data from BigQuery in regular intervals and, for each record, create a task in the task queue, so that records can be processed in the background.
Instructions at https://cloud.google.com/bigquery/create-simple-app-api state to wait for a job like this:
// Create a job ID so that we can safely retry.
JobId jobId = JobId.of(UUID.randomUUID().toString());
Job queryJob = bigquery.create(JobInfo.newBuilder(queryConfig).setJobId(jobId).build());
// Wait for the query to complete.
queryJob = queryJob.waitFor();
Problem is the 10-minute limit, as BigQuery queries are processed in the background and it may take some time until the result becomes available, so I may not be able to process the response in the same endpoint call.
I know I can configure App Engine to extend the maximum time per request, but that can hardly be the solution.
Upvotes: 2
Views: 2665
Reputation: 3898
The best option is handling long-running tasks as BQ does: provide a job-id and allow clients to query it, returning a 202 while the query has not finished, and a 200 with the result once the result is ready to be consumed by the client.
Furthermore, the 202 can return a body, so you can set different status to the clients (e.g. "Queued", "Running", "Processing results", ...).
On the server side, you start a query and, as soon as BQ returns a job ID, store it in some persistent storage (I would choose Datastore, but it could be memcache, a cloudSQL instance, or even a file in GCS).
Then you just need to create a cron job that checks BQ for the status of the unfinished queries, and updates their status in your persistent storage accordingly. Once the BQ job is finished, you can retrieve the results and store them to have them ready when the client checks your service.
As an example, this are the BQ API queries you should do within your app (in here done with curl to provide an example, you can later translate to any language using the idiomatic libraries):
Create the job, retrieve the job id from the response, and store it:
PROJECT=$(gcloud config get-value project)
QUERY='SELECT * FROM `bigquery-samples.wikipedia_benchmark.Wiki1k` limit 0'
curl -H"Authorization: Bearer $(gcloud auth print-access-token)" -H'content-type:application/json' https://www.googleapis.com/bigquery/v2/projects/$PROJECT/jobs -d"
{
\"configuration\": {
\"query\": {
\"query\": \"$QUERY\",
\"useLegacySql\": false
}
},
\"jobReference\": {
\"projectId\": \"$PROJECT\"
}
}"|jq -r .jobReference.jobId >> running_jobs
Keep querying the BQ API for the job status. (this could be your cron job):
for job in $(cat running_jobs); do
if [ $(curl -H"Authorization: Bearer $(gcloud auth print-access-token)" https://www.googleapis.com/bigquery/v2/projects/$PROJECT/jobs/$job|jq -r .status.state) = "DONE" ]; then
# here your processing part including your callback
# then remove the job from the list of running jobs
sed -i "/$job/d" ./running_jobs
fi
done
You can try this in the cloud shell.
Upvotes: 1
Reputation: 39814
One posible approach is to handle the job lifecycle management inside your app code instead of relying on wrappers that do that for you automatically (which are the ones blocking until the job completes).
From Running jobs:
To run a job programmatically:
Start the job by calling the
jobs.insert
method using a unique job ID generated by your client code. The server generates a job ID for you if you omit it, but it is a best practice to generate the job ID on the client side to allow reliable retry of thejobs.insert
call.When you call the
jobs.insert
method, include a job resource representation containing a child property that specifies the job type — load, query, extract, or copy.Check job status by calling
jobs.get
with the job ID and check thestatus.state
value to learn the job status. Whenstatus.state
isDONE
, the job has stopped running; however, aDONE
status does not mean that the job completed successfully, only that it is no longer running.Note: There are some wrapper functions that manage job status requests for you. For example, running
jobs.query
creates a job and periodically polls forDONE
status for a specified period of time.Check for job success. If the job has a
status.errorResult
property, the job has failed. Thestatus.errorResult
property holds information describing what went wrong in a failed job. Ifstatus.errorResult
is absent, the job finished successfully, although there might have been some non-fatal errors, such as problems importing a few rows in a load job. Non-fatal errors are returned in the job'sstatus.errors
list.
Basically instead of using a single blocking call to wait for the job to complete (queryJob.waitFor();
in your case, the equivalent to jobs.query
mentioned in the quote) you can launch the background job, then make repeated calls to query for the job's state (for example in delayed push queue tasks) and when it is complete you actually start processing the results.
Probably also of interest would be the Introduction to BigQuery Jobs and Managing BigQuery Jobs.
Note: the answer is based solely on the docs, I am not yet a BigQuery user.
Upvotes: 0