<p dir="auto"><strong>Apache Airflow version</strong>: 2.0.1 (possibly any?)</p>
<p dir="auto"><strong>Kubernetes version (if you are using kubernetes)</strong> (use <code class="notranslate">kubectl version</code>): N/A</p>
<p dir="auto"><strong>Environment</strong>: Any</p>
<ul dir="auto">
<li><strong>Cloud provider or hardware configuration</strong>: Any</li>
<li><strong>OS</strong> (e.g. from /etc/os-release): Any</li>
<li><strong>Kernel</strong> (e.g. <code class="notranslate">uname -a</code>): Any</li>
<li><strong>Install tools</strong>: Any</li>
<li><strong>Others</strong>: Any</li>
</ul>
<p dir="auto"><strong>What happened</strong>:</p>
<p dir="auto">From SQLAlchemy version 1.4.0b1, <a href="https://docs.sqlalchemy.org/en/14/changelog/changelog_14.html#change-3687655465c25a39b968b4f5f6e9170b" rel="nofollow">SQLAlchemy removed the support for the <code class="notranslate">postgres</code> prefix</a> in URIs (used for example in <code class="notranslate">create_engine</code>), and now it supports only <code class="notranslate">postgresql</code>.</p>
<p dir="auto">This affects all Airflow jobs that:<br>
(A) Use SQLAlchemy version &gt;= 1.4.0<br>
(B) Obtain/use a Postgres URI from the <code class="notranslate">get_uri</code> method of a <code class="notranslate">PostgresHook</code> (method defined in the <code class="notranslate">DbApiHook</code> - <code class="notranslate">airflow/hooks/dbapi.py</code>).</p>
<p dir="auto">This is because the <code class="notranslate">PostgresHook</code> (<code class="notranslate">airflow/providers/postgres/hooks/postgres.py</code>) class, defines a class attribute <code class="notranslate">conn_type = 'postgres'</code> instead of <code class="notranslate">postgresql</code>.</p>
<p dir="auto">Right now my airflow jobs with the updated SQLAlchemy that are using SQLAlchemy <code class="notranslate">create_engine</code> with a postgres db are broken.</p>
<p dir="auto"><strong>What you expected to happen</strong>:</p>
<p dir="auto">That Airflow jobs using SQLAlchemy &gt;= 1.4.0 and SQLAlchemy's create_engine with Postgres databases work correctly, and that <code class="notranslate">get_uri</code> generates a postgres URI starting with <code class="notranslate">postgresql://</code>.</p>
<p dir="auto">Of course I could use an older SQLAlchemy version or manipulated the returned URI, but I guess it'd be nice if Airflow adapts to the updated prefix.</p>
<p dir="auto"><strong>How to reproduce it</strong>:</p>
<p dir="auto">Use the <code class="notranslate">get_uri</code> method of any <code class="notranslate">PostgresHook</code>.</p>
<p dir="auto"><strong>Anything else we need to know</strong>:</p>
<p dir="auto">N/A</p>
<h3 dir="auto">Description</h3>
<ul dir="auto">
<li>I met LoadJob bug in <code class="notranslate">BigQueryInsertJobOperator</code> and tried to use <code class="notranslate">GCSToBigQueryOperator</code> instead when update from Airflow 2.2.5 to 2.3.2.</li>
</ul>
<h3 dir="auto">Use case/motivation</h3>
<ul dir="auto">
<li>most BigQuery-related operator recommend that we should use <code class="notranslate">insert_job()</code>, but gcs_to_bq_operator still use <code class="notranslate">run_load ()</code>.</li>
<li><code class="notranslate">GCSToBigQueryOperator</code> still uses lots of parameter to build config. Not like <code class="notranslate">BigQueryInsertJobOperator</code> which uses a clean configuration. The configuration is much straightforward and easy to follow.</li>
<li>I would like to know whether to  keep <code class="notranslate">GCSToBigQueryOperator</code> and replace <code class="notranslate">run_load</code> with <code class="notranslate">insert_job</code>, Or have <code class="notranslate">BigQueryInsertJobOperator</code>. If we could replace <code class="notranslate">run_load</code> with <code class="notranslate">insert_job</code> in <code class="notranslate">GCSToBigQueryOperator</code> , all BigQuery-related operator could be more consistent.</li>
</ul>
<h3 dir="auto">Related issues</h3>
<p dir="auto"><em>No response</em></p>
<h3 dir="auto">Are you willing to submit a 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>