How can I define a variable in a SQL query saved in a jinja2 template and have it substituted with a value when the query is run by ansible?

huangapple
go评论151阅读模式
英文:

How can I define a variable in a SQL query saved in a jinja2 template and have it substituted with a value when the query is run by ansible?

问题

在我的Ansible playbook中，有一个任务在我的数据库上运行一个保存在Jinja2模板中的SQL查询。以下是如何编辑我的playbook和Jinja2模板以将该变量传递给查询的步骤：

在Ansible playbook中，确保你已经定义了一个名为input_date的变量，如下所示：

  1. input_date: "2023-05-03"

此变量应该保存在文件playbooks/my_playbook/vars/target_date.yml中。

然后，在你的Ansible playbook中，在community.postgresql.postgresql_copy任务中，你可以使用extra_vars来传递这个变量到Jinja2模板中，如下所示：

  1. - name: generate report
  2.   community.postgresql.postgresql_copy:
  3.     login_host: '{{ db_host }}'
  4.     login_user: '{{ db_username }}'
  5.     login_password: '{{ db_password }}'
  6.     db: '{{ db_database }}'
  7.     port: '{{ db_database_port }}'
  8.     src: "{{ lookup('template', 'my_report_query.sql.j2', variables={'input_date': input_date}) }}"
  9.     copy_to: /tmp/my_report.csv
  10.     options:
  11.       format: csv
  12.       delimiter: ';'
  13.       header: yes

这里，我们使用了variables参数来传递input_date变量到Jinja2模板中。在Jinja2模板中，你可以使用input_date变量来替换SQL查询中的日期值。

这样，你的SQL查询将使用input_date变量中的日期值，而不是硬编码的日期值。这样，你可以轻松地在Ansible playbook中更改日期值而不必修改查询本身。

英文:

In my Ansible playbook, there is a task which runs a SQL query saved in a Jinja2 template on my database.

  1. - hosts: "{{ hosts_list }}"
  2.   gather_facts: no
  4.   vars_files:
  5. 	- vars/main.yml
  6. 	- vars/my_hosts.yml
  7. 	- vars/target_date.yml
  9.   tasks:
  10. 	- name: generate report
  11. 	  community.postgresql.postgresql_copy:
  12. 		login_host: '{{ db_host }}'
  13. 		login_user: '{{ db_username }}'
  14. 		login_password: '{{ db_password }}'
  15. 		db: '{{ db_database }}'
  16. 		port: '{{ db_database_port }}'
  17. 		src: "{{ lookup('template', 'my_report_query.sql.j2') }}"
  18. 		copy_to: /tmp/my_report.csv
  19. 		options:
  20. 		  format: csv
  21. 		  delimiter: ';'
  22. 		  header: yes

The query is something like:

  1. select * from mytable where date='2023-02-03';

Now, I would like to pass to the query a value of the date which is defined in my Ansible playbook.

So, in the case there would be a variable defined in my Ansible playbook as

  1. input_date: "2023-05-03"

saved in file playbooks/my_playbook/vars/target_date.yml

How can I edit my playbook and Jinja2 template in order to pass that variable to the query?

答案1

得分: 3

在Ansible中，你不需要将变量传递给你的模板 - 模板可以访问当前范围内的所有变量。换句话说，你只需要确保已经引用了target_date.yml文件。例如，我们可以使用include_vars模块来实现这一点：

  1. - hosts: localhost
  2.   gather_facts: false
  3.   vars:
  4.     db_host: localhost
  5.     db_username: example_user
  6.     db_password: example_pass
  7.     db_database: example_db
  8.   tasks:
  9.   - name: 读取变量文件
  10.     include_vars:
  11.       file: vars/target_date.yml
  13.   - name: 生成报告
  14.     community.postgresql.postgresql_copy:
  15.       login_host: '{{ db_host }}'
  16.       login_user: '{{ db_username }}'
  17.       login_password: '{{ db_password }}'
  18.       db: '{{ db_database }}'
  19.       port: '{{ db_database_port|default(5432) }}'
  20.       src: "{{ lookup('template', 'my_report_query.j2.sql') }}"
  21.       copy_to: /tmp/my_report.csv
  22.       options:
  23.         format: csv
  24.         delimiter: ';'
  25.         header: yes

我们还可以通过以下方式读取变量文件：

  • 通过在ansible-playbook命令行上使用-e选项：

    1. ansible-playbook playbook.yaml -e @vars/target_date.yaml

  • 通过在播放中使用vars_files选项：

    1. - hosts: localhost
    2.   vars_files:
    3.   - vars/target_date.yml

  • 通过在group_vars或host_vars目录中的适当文件中设置input_date变量。

等等。

请注意，为了使你的playbook正常工作，你需要在查询模板中删除分号;。带有终端分号的复制任务将会失败，如下所示：

  1. Cannot execute SQL 'COPY (select * from mytable where date='2023-05-03';
  2. ) TO '/tmp/my_report.csv' (format csv, delimiter ';', header True)': syntax error at or near ";"
  3. LINE 1: COPY (select * from mytable where date='2023-05-03';
  4.                                                            ^
英文:

In Ansible, you don't pass variables to your templates -- a template has access to all variables that are currently in scope. In other words, you just need to make sure that you've sourced that target_date.yml file. For example, here we're doing that with the include_vars module:

  1. - hosts: localhost
  2.   gather_facts: false
  3.   vars:
  4.     db_host: localhost
  5.     db_username: example_user
  6.     db_password: example_pass
  7.     db_database: example_db
  8.   tasks:
  9.   - name: read vars file
  10.     include_vars:
  11.       file: vars/target_date.yml
  13.   - name: generate report
  14.     community.postgresql.postgresql_copy:
  15.       login_host: '{{ db_host }}'
  16.       login_user: '{{ db_username }}'
  17.       login_password: '{{ db_password }}'
  18.       db: '{{ db_database }}'
  19.       port: '{{ db_database_port|default(5432) }}'
  20.       src: "{{ lookup('template', 'my_report_query.j2.sql') }}"
  21.       copy_to: /tmp/my_report.csv
  22.       options:
  23.         format: csv
  24.         delimiter: ';'
  25.         header: yes

We could also read in the vars file:

  • By using the -e option on the ansible-playbook command line:

    1. ansible-playbook playbook.yaml -e @vars/target_date.yaml

  • By using the vars_files option in the play:

    1. - hosts: localhost
    2.   vars_files:
    3.   - vars/target_date.yml

  • By setting the input_date variable in an appropriate file in the group_vars or host_vars directories.

Etc.

Note that for your playbook to work, you'll need to drop the ; in your query template. With the terminal semicolon your copy task will fail with:

  1. Cannot execute SQL 'COPY (select * from mytable where date='2023-05-03';
  2. ) TO '/tmp/my_report.csv' (format csv, delimiter ';', header True)': syntax error at or near ";"
  3. LINE 1: COPY (select * from mytable where date='2023-05-03';
  4.                                                            ^

答案2

得分: 0

根据 @β.εηοιτ.βε 的建议，只需使用 jinja2 中的表达式标签 {{ ... }} 来替换 jinja2 模板中日期的硬编码值。

  1. select * from mytable where date='{{ input_date }}'

另外，正如 @larsks 指出的，jinja2 模板中的查询语句不能以分号 (;) 结尾。

英文:

As suggested by @β.εηοιτ.βε , it was sufficient to replace the hardcoded value of the date in jinja2 templates with the jinja2 "expression tags" {{ ... }}

  1. select * from mytable where date='{{ input_date }}'

also, as indicated by @larsks, the query in the jinja2 template must not end with column ( ; )

huangapple
  • 本文由 发表于 2023年6月5日 18:59:34
  • 转载请务必保留本文链接：https://go.coder-hub.com/76405777.html
  • ansible
  • ansible-template
  • jinja2
  • postgresql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定