template_ext '.sql' failing to parse CALL statement

[abhishekbhakat]

Apache Airflow Provider(s)

common-sql

Versions of Apache Airflow Providers

apache-airflow-providers-amazon==4.0.0
apache-airflow-providers-apache-hive==3.0.0
apache-airflow-providers-apache-livy==3.0.0
apache-airflow-providers-celery==3.0.0
apache-airflow-providers-cncf-kubernetes==4.1.0
apache-airflow-providers-databricks==3.0.0
apache-airflow-providers-elasticsearch==4.0.0
apache-airflow-providers-ftp==3.0.0
apache-airflow-providers-google==8.1.0
apache-airflow-providers-http==3.0.0
apache-airflow-providers-imap==3.0.0
apache-airflow-providers-microsoft-azure==4.0.0
apache-airflow-providers-postgres==5.0.0
apache-airflow-providers-redis==3.0.0
apache-airflow-providers-snowflake==3.0.0
apache-airflow-providers-sqlite==3.0.0

Apache Airflow version

2.3.4

Operating System

PRETTY_NAME="Debian GNU/Linux 11 (bullseye)" NAME="Debian GNU/Linux" VERSION_ID="11" VERSION="11 (bullseye)" VERSION_CODENAME=bullseye ID=debian

Deployment

Astronomer

Deployment details

Used a docker-compose to run astro-runtime:5.0.8 images.

What happened

The .sql file read by /usr/local/lib/python3.9/site-packages/openlineage/airflow/extractors/postgres_extractor.py.
And sent to /usr/local/lib/python3.9/site-packages/openlineage/common/sql/__init__.py for parsing.
Resulted in

{__init__.py:27} ERROR - SQL parser failed: sql parser error: Expected an SQL statement, found: CALL

The same has been observed by an Astronomer Customer over SnowflakeOperator.
I guess this would happen everywhere as the base sql parsing via openlineage.

What you think should happen instead

No response

How to reproduce

Created a stored procedure on Postgres as:

CREATE OR REPLACE PROCEDURE display_message (INOUT msg TEXT)
AS $$
begin
	RAISE NOTICE 'Procedure Parameter: %', msg ;
end ;
$$
language plpgsql;

Create an sql file(i.e., include/call-sproc1.sql) as:

-- call-sproc1
CALL display_message('ola!');

And a dag file(i.e., dags/stored_proc.py) as:

from airflow import DAG
from airflow.providers.postgres.operators.postgres import PostgresOperator
from datetime import datetime

with DAG('postgres_proc', start_date=datetime(2022,9,1),schedule_interval=None, template_searchpath='/usr/local/airflow/include') as d:
    task1 = PostgresOperator(
        task_id="task_1",
        sql="call-sproc1.sql",
        postgres_conn_id='metadata'
    )

Where conn_id metadata points to the Postgres database with stored procedure is created.

While running the dag got the log : dag_id=postgres_proc_run_id=manual__2022-09-08T11 44 18.018924+00 00_task_id=task_1_attempt=2.log

Anything else

No response

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct

[kaxil]

I don’t think it is related to template_ext:

Did you try with inline SQL instead of that in separate file?

from airflow import DAG
from airflow.providers.postgres.operators.postgres import PostgresOperator
from datetime import datetime

with DAG('postgres_proc', start_date=datetime(2022,9,1),schedule_interval=None, template_searchpath='/usr/local/airflow/include') as d:
    task1 = PostgresOperator(
        task_id="task_1",
        sql="CALL display_message('ola!');",
        postgres_conn_id='metadata'
    )

[abhishekbhakat]

Hi @kaxil,
I wanted to point it's the same behavior with inline SQL too.

[2022-09-08 12:45:57,237] {__init__.py:27} ERROR - SQL parser failed: sql parser error: Expected an SQL statement, found: CALL

from the log shared above.

[eladkal]

Can you please test it with RC version?
#26164

[kaxil]

The error is coming from the below line in openlineage, so going to close this one

https://github.com/OpenLineage/OpenLineage/blob/65a5f021a1ba3035d5198e759587737a05b242e1/integration/common/openlineage/common/sql/__init__.py#L27