<p dir="auto">Feature to put a future instance task ON HOLD is missing in Airflow.  Is there any way to achieve this functionality in Airflow. Appreciate any help.</p>
<h3 dir="auto">Apache Airflow Provider(s)</h3>
<p dir="auto">google</p>
<h3 dir="auto">Versions of Apache Airflow Providers</h3>
<p dir="auto">google-cloud-bigquery==2.34.4</p>
<h3 dir="auto">Apache Airflow version</h3>
<p dir="auto">2.5.2+astro.2</p>
<h3 dir="auto">Operating System</h3>
<p dir="auto">OS X</p>
<h3 dir="auto">Deployment</h3>
<p dir="auto">Astronomer</p>
<h3 dir="auto">Deployment details</h3>
<p dir="auto"><em>No response</em></p>
<h3 dir="auto">What happened</h3>
<p dir="auto">When using <code class="notranslate">SQLExecuteQueryOperator()</code> it is not possible to pass in the <code class="notranslate">priority</code> argument which is <a href="https://cloud.google.com/bigquery/docs/running-queries" rel="nofollow">important for running jobs on bigquery</a>. By default, bigquery operators will run in 'INTERACTIVE' priority rather than 'BATCH' priority.</p>
<p dir="auto">The typical way to specify this is to pass dictionary through <code class="notranslate">configuration</code> or <code class="notranslate">api_resource_configs</code>. When using <code class="notranslate">SQLExecuteQueryOperator()</code> I could not find a satisfactory workaround other than switching to use the bigquery specific <code class="notranslate">BigQueryInsertJobOperator()</code></p>
<h3 dir="auto">What you think should happen instead</h3>
<p dir="auto">The most straightforward workaround would be to use provider specific QueryOperator, however, I think it would be better to have more standardization around using <code class="notranslate">common</code> provider.</p>
<p dir="auto">Other possible solutions are</p>
<ol dir="auto">
<li>tweak <code class="notranslate">_api_resource_configs_duplication_check</code> to allow priority that's passed in as config to overwrite</li>
<li>remove <code class="notranslate">priority: str = "INTERACTIVE",</code> from <code class="notranslate">run_query</code> function definition and instead default it (if unset) right before execution in <code class="notranslate">insert_job</code> (allowing it to pass duplicate check)</li>
<li>make config and priority more of first class parameters within bigquery hook/operators (it's missing in places like <code class="notranslate">get_pandas_df</code>) - this is prob a much longer term solution</li>
</ol>
<h3 dir="auto">How to reproduce</h3>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="SQLExecuteQueryOperator(
...
        hook_params={
            &quot;use_legacy_sql&quot;: False,
            &quot;location&quot;: &quot;us&quot;,
            &quot;api_resource_configs&quot;: {&quot;query&quot;: {&quot;useQueryCache&quot;: False, &quot;priority&quot;: &quot;BATCH&quot;}},
        },
)"><pre class="notranslate"><span class="pl-v">SQLExecuteQueryOperator</span>(
...
        <span class="pl-s1">hook_params</span><span class="pl-c1">=</span>{
            <span class="pl-s">"use_legacy_sql"</span>: <span class="pl-c1">False</span>,
            <span class="pl-s">"location"</span>: <span class="pl-s">"us"</span>,
            <span class="pl-s">"api_resource_configs"</span>: {<span class="pl-s">"query"</span>: {<span class="pl-s">"useQueryCache"</span>: <span class="pl-c1">False</span>, <span class="pl-s">"priority"</span>: <span class="pl-s">"BATCH"</span>}},
        },
)</pre></div>
<p dir="auto">will fail with</p>
<div class="highlight highlight-text-adblock notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="Traceback (most recent call last):
  File &quot;&lt;stdin&gt;&quot;, line 1, in &lt;module&gt;
  File &quot;/usr/local/lib/python3.9/site-packages/airflow/providers/common/sql/operators/sql.py&quot;, line 260, in execute
    output = hook.run(
  File &quot;/usr/local/lib/python3.9/site-packages/airflow/providers/common/sql/hooks/sql.py&quot;, line 349, in run
    self._run_command(cur, sql_statement, parameters)
  File &quot;/usr/local/lib/python3.9/site-packages/airflow/providers/common/sql/hooks/sql.py&quot;, line 380, in _run_command
    cur.execute(sql_statement)
  File &quot;/usr/local/lib/python3.9/site-packages/airflow/providers/google/cloud/hooks/bigquery.py&quot;, line 2701, in execute
    self.job_id = self.hook.run_query(sql)
  File &quot;/usr/local/lib/python3.9/site-packages/airflow/providers/google/cloud/hooks/bigquery.py&quot;, line 2141, in run_query
    _api_resource_configs_duplication_check(param_name, param, configuration[&quot;query&quot;])
  File &quot;/usr/local/lib/python3.9/site-packages/airflow/providers/google/cloud/hooks/bigquery.py&quot;, line 2942, in _api_resource_configs_duplication_check
    raise ValueError(
ValueError: Values of priority param are duplicated. api_resource_configs contained priority param in `query` config and priority was also provided with arg to run_query() method. Please remove duplicates."><pre class="notranslate">Traceback (most recent call last):
  File "&lt;stdin&gt;", line 1, in &lt;module&gt;
  File "/usr/local/lib/python3.9/site-packages/airflow/providers/common/sql/operators/sql.py", line 260, in execute
    output = hook.run(
  File "/usr/local/lib/python3.9/site-packages/airflow/providers/common/sql/hooks/sql.py", line 349, in run
    self._run_command(cur, sql_statement, parameters)
  File "/usr/local/lib/python3.9/site-packages/airflow/providers/common/sql/hooks/sql.py", line 380, in _run_command
    cur.execute(sql_statement)
  File "/usr/local/lib/python3.9/site-packages/airflow/providers/google/cloud/hooks/bigquery.py", line 2701, in execute
    self.job_id = self.hook.run_query(sql)
  File "/usr/local/lib/python3.9/site-packages/airflow/providers/google/cloud/hooks/bigquery.py", line 2141, in run_query
    _api_resource_configs_duplication_check(param_name, param, configuration["query"])
  File "/usr/local/lib/python3.9/site-packages/airflow/providers/google/cloud/hooks/bigquery.py", line 2942, in _api_resource_configs_duplication_check
    raise ValueError(
ValueError: Values of priority param are duplicated. api_resource_configs contained priority param in `query` config and priority was also provided with arg to run_query() method. Please remove duplicates.</pre></div>
<p dir="auto">Since <code class="notranslate">run_query()</code> has a default value for <code class="notranslate">priority</code> and also getting it from <code class="notranslate">configuration</code></p>
<p dir="auto">Although the run_query function within hook has parameter for priority, the hook itself does not have this parameter so the following fails</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="SQLExecuteQueryOperator(
...
        hook_params={
            &quot;use_legacy_sql&quot;: False,
            &quot;location&quot;: &quot;us&quot;,
            &quot;priority&quot;: &quot;BATCH&quot;,
            &quot;api_resource_configs&quot;: {&quot;query&quot;: {&quot;useQueryCache&quot;: False}},
        },
)"><pre class="notranslate"><span class="pl-v">SQLExecuteQueryOperator</span>(
...
        <span class="pl-s1">hook_params</span><span class="pl-c1">=</span>{
            <span class="pl-s">"use_legacy_sql"</span>: <span class="pl-c1">False</span>,
            <span class="pl-s">"location"</span>: <span class="pl-s">"us"</span>,
            <span class="pl-s">"priority"</span>: <span class="pl-s">"BATCH"</span>,
            <span class="pl-s">"api_resource_configs"</span>: {<span class="pl-s">"query"</span>: {<span class="pl-s">"useQueryCache"</span>: <span class="pl-c1">False</span>}},
        },
)</pre></div>
<p dir="auto">error msg</p>
<div class="highlight highlight-text-adblock notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="Traceback (most recent call last):
  File &quot;&lt;stdin&gt;&quot;, line 1, in &lt;module&gt;
  File &quot;/usr/local/lib/python3.9/site-packages/airflow/providers/common/sql/operators/sql.py&quot;, line 255, in execute
    hook = self.get_db_hook()
  File &quot;/usr/local/lib/python3.9/site-packages/airflow/providers/common/sql/operators/sql.py&quot;, line 179, in get_db_hook
    return self._hook
  File &quot;/usr/local/lib/python3.9/functools.py&quot;, line 993, in __get__
    val = self.func(instance)
  File &quot;/usr/local/lib/python3.9/site-packages/airflow/providers/common/sql/operators/sql.py&quot;, line 142, in _hook
    hook = conn.get_hook(hook_params=self.hook_params)
  File &quot;/usr/local/lib/python3.9/site-packages/airflow/models/connection.py&quot;, line 344, in get_hook
    return hook_class(**{hook.connection_id_attribute_name: self.conn_id}, **hook_params)
TypeError: __init__() got an unexpected keyword argument 'priority'"><pre class="notranslate">Traceback (most recent call last):
  File "&lt;stdin&gt;", line 1, in &lt;module&gt;
  File "/usr/local/lib/python3.9/site-packages/airflow/providers/common/sql/operators/sql.py", line 255, in execute
    hook = self.get_db_hook()
  File "/usr/local/lib/python3.9/site-packages/airflow/providers/common/sql/operators/sql.py", line 179, in get_db_hook
    return self._hook
  File "/usr/local/lib/python3.9/functools.py", line 993, in __get__
    val = self.func(instance)
  File "/usr/local/lib/python3.9/site-packages/airflow/providers/common/sql/operators/sql.py", line 142, in _hook
    hook = conn.get_hook(hook_params=self.hook_params)
  File "/usr/local/lib/python3.9/site-packages/airflow/models/connection.py", line 344, in get_hook
    return hook_class(<span class="pl-k">**</span>{hook.connection_id_attribute_name: self.conn_id}, <span class="pl-k">**</span>hook_params)
TypeError: __init__() got an unexpected keyword argument 'priority'</pre></div>
<p dir="auto">I put in a PR to add <code class="notranslate">priority</code> to the hook but maybe this isn't the right general solution <a class="issue-link js-issue-link" data-error-text="Failed to load title" data-id="1669216886" data-permission-text="Title is private" data-url="https://github.com/apache/airflow/issues/30655" data-hovercard-type="pull_request" data-hovercard-url="/apache/airflow/pull/30655/hovercard" href="https://github.com/apache/airflow/pull/30655">#30655</a></p>
<h3 dir="auto">Anything else</h3>
<p dir="auto"><em>No response</em></p>
<h3 dir="auto">Are you willing to submit PR?</h3>
<ul class="contains-task-list">
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox" checked=""> Yes I am willing to submit a PR!</li>
</ul>
<h3 dir="auto">Code of Conduct</h3>
<ul class="contains-task-list">
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox" checked=""> I agree to follow this project's <a href="https://github.com/apache/airflow/blob/main/CODE_OF_CONDUCT.md">Code of Conduct</a></li>
</ul>