You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@airflow.apache.org by GitBox <gi...@apache.org> on 2022/10/16 06:49:23 UTC
[GitHub] [airflow] kameshkotwani opened a new issue, #27077: Not able to use Jinja template in PostgresOperator Airflow in SQL file naming
kameshkotwani opened a new issue, #27077:
URL: https://github.com/apache/airflow/issues/27077
### Apache Airflow version
main (development)
### What happened
I am trying to pass a sql parameter as a Jinja Template, but not able to. I am a bit new to Airflow, here is the code, I am not sure if the Jinja templating works in PostgresOperator or not. If there is any other way we can pass please let me know, again I am not trying to pass the jinja template inside the SQL query or file, I am passing the name of the SQL file using the Jinja template which it is not able to render.
### What you think should happen instead
The Jinja Template should be rendered at `sql` parameter in PostgresOperator.
### How to reproduce
```
write_to_postgres = PostgresOperator(
task_id ="write_to_postgres",
postgres_conn_id="mypostgres",
#TODO investigate, why this is not working
sql ="queries{{ execution_date.hour }}{{ execution_date.day }}.sql"
)
```
Output of rendered in PostgresOperator
However, the BashOperator is able to render it properly
```bash
delete_queries_file = BashOperator(
task_id ="delete_queries_file",
bash_command="cd /opt/airflow/dags && rm queries{{ execution_date.hour }}{{ execution_date.day }}.sql"
)
```
Output of the BashOperator with proper rendered of Jinja Template
### Operating System
Windows 10
### Versions of Apache Airflow Providers
apache-airflow-providers-common-sql==1.2.0
apache-airflow-providers-ftp==3.1.0
apache-airflow-providers-http==4.0.0
apache-airflow-providers-imap==3.0.0
apache-airflow-providers-postgres==5.2.2
apache-airflow-providers-sqlite==3.2.1
### Deployment
Docker-Compose
### Deployment details
_No response_
### Anything else
_No response_
### Are you willing to submit PR?
- [ ] Yes I am willing to submit a PR!
### Code of Conduct
- [X] I agree to follow this project's [Code of Conduct](https://github.com/apache/airflow/blob/main/CODE_OF_CONDUCT.md)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] boring-cyborg[bot] commented on issue #27077: Not able to use Jinja template in PostgresOperator Airflow in SQL file naming
Posted by GitBox <gi...@apache.org>.
boring-cyborg[bot] commented on issue #27077:
URL: https://github.com/apache/airflow/issues/27077#issuecomment-1279905902
Thanks for opening your first issue here! Be sure to follow the issue template!
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] uranusjr commented on issue #27077: Not able to use Jinja template in PostgresOperator sql parameter as filename
Posted by GitBox <gi...@apache.org>.
uranusjr commented on issue #27077:
URL: https://github.com/apache/airflow/issues/27077#issuecomment-1280494612
Unfortunately this is not possible in the current implementation. Maybe we could support it though, it’s not particularly easy to imagine people are actively naming files like Jinja templates that this feature addition would break compatibility (and we can always add a config for this if we are worried).
In the mean time, I’m changing this to a feature request instead.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] eladkal commented on issue #27077: Not able to use Jinja template in PostgresOperator sql parameter as filename
Posted by GitBox <gi...@apache.org>.
eladkal commented on issue #27077:
URL: https://github.com/apache/airflow/issues/27077#issuecomment-1280895637
I'm not sure how you verify the file will exist on the same worker as the downstream task but lets keep that a side.
In this case you should push the file name in Xcom.
I'm converting this to Discussion as for the moment this falls into utalizing Xcoms.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] eladkal commented on issue #27077: Not able to use Jinja template in PostgresOperator sql parameter as filename
Posted by GitBox <gi...@apache.org>.
eladkal commented on issue #27077:
URL: https://github.com/apache/airflow/issues/27077#issuecomment-1280655772
> I am trying to create a filename which is dynamic based on its creation time
What process creates the actual file?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] kameshkotwani commented on issue #27077: Not able to use Jinja template in PostgresOperator sql parameter as filename
Posted by GitBox <gi...@apache.org>.
kameshkotwani commented on issue #27077:
URL: https://github.com/apache/airflow/issues/27077#issuecomment-1280510250
Thank you for considering it, meanwhile I was able to do the required task using vanilla python and PythonOperator, I have attached the code, might help someone with same requirement as mine.
```python
def _write_to_postgres(execution_date):
hour = execution_date.hour
day = execution_date.day
filename = f"queries{hour}{day}.sql"
import psycopg2
try:
conn = psycopg2.connect(database="airflow",user='airflow', password='airflow', host='postgres', port= '5432')
except:
raise AirflowFailException
else:
cursor = conn.cursor()
with open(f"/opt/airflow/dags/{filename}","r") as f:
for statement in f.readlines():
print(statement)
cursor.execute(statement)
conn.commit()
cursor.close()
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] chenglongyan commented on issue #27077: Not able to use Jinja template in PostgresOperator Airflow in SQL file naming
Posted by GitBox <gi...@apache.org>.
chenglongyan commented on issue #27077:
URL: https://github.com/apache/airflow/issues/27077#issuecomment-1280017217
A string with an ```.sql``` suffix is considered a file rather than a jinjia template.
```python
write_to_postgres = PostgresOperator(
task_id ="write_to_postgres",
postgres_conn_id="mypostgres",
#TODO investigate, why this is not working
sql ="queries{{ execution_date.hour }}{{ execution_date.day }}.sql"
)
```
---
Refer to the ```template_ext``` field of [PostgresOperator](https://airflow.apache.org/docs/apache-airflow-providers-postgres/stable/_api/airflow/providers/postgres/operators/postgres/index.html#airflow.providers.postgres.operators.postgres.PostgresOperator.template_ext) and [templating-with-jinja](https://airflow.apache.org/docs/apache-airflow/stable/tutorial/fundamentals.html#templating-with-jinja)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] kameshkotwani commented on issue #27077: Not able to use Jinja template in PostgresOperator Airflow in SQL file naming
Posted by GitBox <gi...@apache.org>.
kameshkotwani commented on issue #27077:
URL: https://github.com/apache/airflow/issues/27077#issuecomment-1280033652
I am trying to create a filename which is dynamic based on its creation time, so I want to use Jinja Template to specify the filename dynamically in the `sql` parameter, for example I create a file `queries1215.sql` (here 12 is hour, 15 is date and it contains multiple queries and saved as a file) using PythonOperator, and the file name changes based on time and date, this works in BashOperator as when deleting the file as seen in the above code, but when same logic is being used in PostgresOperator, template is not rendering. I am not able to achieve the desired result due to that. Is there any other way we can do this where the filename is dynamic??
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] kameshkotwani commented on issue #27077: Not able to use Jinja template in PostgresOperator sql parameter as filename
Posted by GitBox <gi...@apache.org>.
kameshkotwani commented on issue #27077:
URL: https://github.com/apache/airflow/issues/27077#issuecomment-1280864106
There is a python function which creates the file, here is the code:
```python
def _fetch_pageviews(pagenames,execution_date,**_):
result = dict.fromkeys(pagenames,0)
with open(f"/opt/airflow/dags/wikipageviews{execution_date.hour}{execution_date.day}",'r') as f:
for line in f:
domain_code,page_title,view_counts,_ = line.split(" ")
if domain_code == "en" and page_title in pagenames:
result[page_title] = view_counts
with open(f"/opt/airflow/dags/queries{execution_date.hour}{ execution_date.day }.sql",'w') as f:
for pagename,pageviewcount in result.items():
f.write(
f"INSERT INTO pageview_counts VALUES ('{pagename}' ,{pageviewcount}, '{execution_date}' );\n"
)
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] eladkal closed issue #27077: Not able to use Jinja template in PostgresOperator sql parameter as filename
Posted by GitBox <gi...@apache.org>.
eladkal closed issue #27077: Not able to use Jinja template in PostgresOperator sql parameter as filename
URL: https://github.com/apache/airflow/issues/27077
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org