Try to use an assignment in Big Query

Google Cloud Data Analytics
1

I’m trying to use my slots reservation for all my queries in Big Query but some of queries of type SCRIPT are being created without reservation_id assigned. So, these queries are charged as a on demand query. I have an assignment created for my project of job type QUERY.

Does any one had this kind of issue?

4

Howdy Joao. Let’s see if we can’t get to the bottom of the puzzle. I am assuming that you have created a BigQuery reservation and associated a GCP project with that reservation. When you then run BigQuery jobs in that project, they should then be billed and run against that reservation.

I then hear you say that some queries of type script are still running with “on-demand” association. How are you determining that? The way I would look is to examine INFORMATION_SCHEMA.JOBS and see the record for the job.

Can you reply back with screenshots or text attachments of the evidence you have that points to on-demand execution … remember to redact anything you don’t want public.

5

Yes, I’m using the query below. Attached is the return of it.

SELECT
  job_id,
  parent_job_id,
  reservation_id,
  total_bytes_billed,
  creation_time, statement_type
FROM
  (PII Removed by Staff)
WHERE
  creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)
and user_email = '<user_email>'
ORDER BY
  creation_time DESC

image
image1920×434 190 KB

6

Howdy Joao,
I am guessing that the remove table in your query was an INFORMATION_SCHEMA table. Typically, when we query such tables we explicitly remove rows where statement_type = SCRIPT

Here is a link to a section of the documentation that explicitly says that you should expect SCRIPT statements to NOT report their reservation_id….

So … what does this mean. Imagine you submit a SQL statement that looked something like:

INSERT INTO …;
INSERT INTO …;

This is a multistatement job … also known as a “script”. When you submit a job which is a multistatement query, BigQuery breaks each statement into its own job and runs them one after another. It also creates a row in INFORMATION_SCHEMA for the script as a whole (this is what we are talking about in this post) AND it creates extra rows … one for each statement in the script. This then forms a heirarchy. For most purposes, when you query the INFORMATION_SCHEMA, include a filter to remove rows where type is SCRIPT … and the puzzle goes away.

1 Like
7

Perfect. Makes sense.

Actually I’m trying to find why I have costs associated with on demand SKUs (analysis sku to be more acurate) and my first guess was jobs without reservation_id.

But if it’s not the SCRIPT statement I don’t have any further idea..