Bigquery: Enable User to Choose Job Priority in Profile

[davidjinlee]

Feature

Feature description

This would allow users to define the job priority (batch vs interactive) in their Bigquery profile. When a model is run using the profile with the job priority defined as "batch", DBT would block until the query is actually executed. This would allow for

The benefits of having ability to define the job priority:

1. Allows users to queue up their jobs
2. Lets users run lower priority queries without impacting higher priority ones
3. Does not count towards concurrency quota

[drewbanin]

Hey @davidjinlee - cool idea! Every query that dbt executes on BQ should go through the raw_execute() method. I think this will be the place to add this config.

The BQ docs seem to indicate that job priority can be configured with:

job_config = bigquery.QueryJobConfig()
job_config.priority = bigquery.QueryPriority.BATCH

We should be able to add a priority key to the profile config. That might look like:

my-profile:
  target: dev
  outputs:
    dev:
      type: bigquery
      method: oauth
      project: '....'
      priority: batch # one of batch|interactive, if provided

Then from the raw_execute method, we can access this config with:

priority = conn.credentials.get('priority', 'interactive')

How does all of that sound?

[davidjinlee]

Hey @drewbanin just a couple questions! If we define job_config.priority = bigquery.QueryPriority.BATCH would that default all queries ran as "batch"? Would we want to have all queries default to "Interactive" unless the user has define "priority: batch" in the profile config? Or is that what priority = conn.credentials.get('priority', 'interactive') is doing?

[drewbanin]

You're absolutely right @davidjinlee!

This line of code will get the priority from the user's profiles.yml target. If no priority is specified, then we'll default to interactive (the existing behavior)

priority = conn.credentials.get('priority', 'interactive')

Next, we'll need to do a check to see if priority == 'interactive' or if priority == 'batch'.

If it's interactive, then we'll want to do:

job_config.priority = bigquery.QueryPriority.INTERACTIVE

and if it's batch, we'll want to do:

job_config.priority = bigquery.QueryPriority.BATCH

[drewbanin]

closed by #1673