Airflow PostgresOperator SQL parameter syntax error

Question

I have the following task in my DAG:

create_features_table = PostgresOperator(
   task_id="create_features_table",
   postgres_conn_id="featuredb",
   sql="src/test.sql "
)

But when I test the task, I get this error:

psycopg2.errors.SyntaxError: syntax error at or near "src"
LINE 1: src/test.sql

The content of the test.sql script is:

CREATE TABLE test(
C1 int,
C2 int,
);

I can't point out the error in the syntax, but that's because it is my first DAG. Any help would be greatly appreciated.

If I run the script directly from the postgres container's psql using "\i src/text.sql" it works fine.

I have tested the connection from the airflow web server and the connection works.

I found that I had to put a space before closing the quotes to avoid a jinja2.exeptions.TemplateNotFound error, but haven't been able to find the syntax error.

Michal Volešíni · Accepted Answer · 2022-12-15 23:19:52Z

0

According to documentation (https://airflow.apache.org/docs/apache-airflow-providers-postgres/stable/_api/airflow/providers/postgres/operators/postgres/index.html#airflow.providers.postgres.operators.postgres.PostgresOperator) if you are defining your sql script path, it must ends with .sql

You have a white space in your path in the end so Operatort things it’s a query to be executed on your postgre, not file with query. You can see it in the response from postgre. Run this query on your postgre instance src/test.sql and you will get the same syntax error.

You can fix it easily by removing that white space

sql="src/test.sql"

answered Dec 15, 2022 at 23:19

Michal Volešíni

1216 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Carl Over a year ago

Thank you for your answer, was not sure if that was a problem. The thing is that when I remove the space at the end I receive the following error: jinja2.exceptions.TemplateNotFound: /src/test.sql

Carl Over a year ago

Thank you for your answer, was not sure if that was a problem. The thing is that when I remove the space at the end I receive the following error: 'jinja2.exceptions.TemplateNotFound: /src/test.sql', but I am sure the file is there. I have a shared volume between the airflow and the postgres containers, and the file is at "/src/" in the postgres container and at "/opt/airflow/files/" in the airflow container. None of hese paths work .

Collectives™ on Stack Overflow

Airflow PostgresOperator SQL parameter syntax error

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related