Python
使用jinja2模板渲染sql脚本
poetry 新建项目并与 git 仓库关联
poetry 项目管理工具的使用
python通过jaydebeapi连接pg
sqlalchemy 数据库表映射到 model
python 生成指定区间的日期序列
本站点使用 MrDoc 构建
-
+
首页
使用jinja2模板渲染sql脚本
## 1、目录结构 ``` ├── sql_render.py ├── __init__.py ├── example │ └── hive_create_table.py └── templates └── hive_create_table.sql ``` ## 2、`sql_render.py` render 类 ```python import os, jinja2 class SqlRender: """ 生成器类 """ def __init__(self, template, context, tpl_path="./templates"): self.tpl_path = os.path.abspath(tpl_path) self.template = template self.context = context def render(self): """通过jinja2渲染""" TemplateLoader = jinja2.FileSystemLoader(self.tpl_path) TemplateEnv = jinja2.Environment(loader=TemplateLoader) template = TemplateEnv.get_template(self.template) return template.render(**self.context) ``` ## 3、模板(参考jinja2语法) * `hive_create_table.sql` ```python -------------------------------------------------------------------------- -------------------------------------------------------------------------- -- @ Main : {{table_name}}.hql -- @ Date : {{create_date}} -- @ Author: {{author}} -- @ Desc : -- 1. 创建 {{schema}}.{{table_name}} 表 -------------------------------------------------------------------------- -- 设置执行引擎 mr tez spark set hive.execution.engine={{ engine | default("mr") }}; -- 切换数据库 use {{ schema }}; -- 创建表,LOCATION 'hdfs://usr/warehouse/hive/schema/<table_name>/<partition_name>' CREATE TABLE IF NOT EXISTS {{schema}}.{{ table_name }}( {%- for col in columns %} {%- if loop.last %} {{col.column_name}} {{col.column_type}} COMMENT '{{col.column_comment}}' {%- else %} {{col.column_name}} {{col.column_type}} COMMENT '{{col.column_comment}}', {%- endif %} {%- endfor %} ) COMMENT '{{ table_comment | default("") }}' {%- if partitions %} PARTITIONED BY ( {%- for col in partitions.columns %} {%- if loop.last %} {{col.partition_column}} {{col.partition_column_type}} COMMENT '{{col.partition_column_comment}}' {%- else %} {{col.partition_column}} {{col.partition_column_type}} COMMENT '{{col.partition_column_comment}}', {%- endif %} {%- endfor %} ) {%- endif %} ROW FORMAT DELIMITED FIELDS TERMINATED BY '{{delimited_fields | default("\001")}}' {%- if null_defined or null_defined == "" %} NULL DEFINED AS '{{null_defined}}' {%- endif %} STORED AS {{ store_type | default("TEXTFILE") }} LOCATION '{{schema_location}}/{{ table_name }}' ; -- 删除表 --DROP TABLE IF EXISTS {{schema}}.{{ table_name }}; {% if partitions.partition_values %} -- 添加分区 {%- for item in partitions.partition_values %} ALTER TABLE {{schema}}.{{ table_name }} ADD IF NOT EXISTS PARTITION ({%- for col in partitions.columns %} {%- if loop.last -%} {{col.partition_column}} = '{{item[loop.index0]}}' {%- else -%} {{col.partition_column}} = '{{item[loop.index0]}}', {%- endif %} {%- endfor %}) LOCATION '{{schema_location}}/{{ table_name }}/{{item | join("/")}}'; {% endfor %} --删除分区 {%- for item in partitions.partition_values %} --ALTER TABLE {{schema}}.{{ table_name }} DROP IF EXISTS PARTITION ({%- for col in partitions.columns %} {%- if loop.last -%} {{col.partition_column}} = '{{item[loop.index0]}}' {%- else -%} {{col.partition_column}} = '{{item[loop.index0]}}', {%- endif %} {%- endfor %}); {% endfor %} {%- endif %} ``` ## 4、example ```python ########################################################################## # @ Main : example.py # @ Date : 2023 # @ Author: vleity # @ Desc : 渲染模板生成sql脚本生成器类定义 # 1. xxxx # 2. xxxx ########################################################################## import sys sys.path.append("..") from sql_render import SqlRender from datetime import datetime # context # 单分区 context1 = { "create_date": datetime.now().strftime("%Y-%m-%d"), "author": "vleity", "schema": "schema", "table_name": "dwd_d_xxx", "table_comment": "表注释", "delimited_fields": "\\001", "null_defined": "", "store_type": "TEXTFILE", "schema_location": "hdfs://usr/warehouse/hive/schema", "columns": [ { "column_name": "xxx1", "column_type": "STRING", # 大写 "column_comment": "字段注释" }, { "column_name": "xxx2", "column_type": "STRING", # 大写 "column_comment": "字段注释" }, { "column_name": "xxx3", "column_type": "STRING", # 大写 "column_comment": "字段注释" }, { "column_name": "xxx4", "column_type": "STRING", # 大写 "column_comment": "字段注释" }, { "column_name": "xxx5", "column_type": "STRING", # 大写 "column_comment": "字段注释" }, { "column_name": "xxx6", "column_type": "STRING", # 大写 "column_comment": "字段注释" }, { "column_name": "xxx7", "column_type": "STRING", # 大写 "column_comment": "字段注释" }, { "column_name": "xxx8", "column_type": "STRING", # 大写 "column_comment": "字段注释" }, { "column_name": "xxx9", "column_type": "STRING", # 大写 "column_comment": "字段注释" } ], "partitions": { "columns": [ { "partition_column": "p_acct_month", "partition_column_type": "STRING", "partition_column_comment": "月份" } ], "partition_values": [ ["${V_MONTH}"] ] } } # 双分区 context2 = { "create_date": "2023-01-01", "author": "vleity", "schema": "schema", "table_name": "dwd_d_xxx", "table_comment": "表注释", "delimited_fields": "\\001", "null_defined": "", "store_type": "TEXTFILE", "schema_location": "hdfs://usr/warehouse/hive/schema", "columns": [ { "column_name": "xxx1", "column_type": "STRING", # 大写 "column_comment": "字段注释" }, { "column_name": "xxx2", "column_type": "STRING", # 大写 "column_comment": "字段注释" }, { "column_name": "xxx3", "column_type": "STRING", # 大写 "column_comment": "字段注释" }, { "column_name": "xxx4", "column_type": "STRING", # 大写 "column_comment": "字段注释" }, { "column_name": "xxx5", "column_type": "STRING", # 大写 "column_comment": "字段注释" }, { "column_name": "xxx6", "column_type": "STRING", # 大写 "column_comment": "字段注释" }, { "column_name": "xxx7", "column_type": "STRING", # 大写 "column_comment": "字段注释" }, { "column_name": "xxx8", "column_type": "STRING", # 大写 "column_comment": "字段注释" }, { "column_name": "xxx9", "column_type": "STRING", # 大写 "column_comment": "字段注释" } ], "partitions": { "columns": [ { "partition_column": "p_acct_month", "partition_column_type": "STRING", "partition_column_comment": "月份" }, { "partition_column": "p_city", "partition_column_type": "STRING", "partition_column_comment": "城市" } ], "partition_values": [ ["${V_MONTH}","${V_CITY}"] ] } } # 渲染 sqlrender = SqlRender(template = "hive_create_table.sql", context = context2, tpl_path = "../templates") sql = sqlrender.render() # 写入文件 file_name = 'xxx.sql' with open(file_name, 'w', encoding='utf8') as f: f.write(sql) # 打印结果 print(sql) ```
vleity
2025年7月14日 16:14
转发文档
收藏文档
上一篇
下一篇
手机扫码
复制链接
手机扫一扫转发分享
复制链接
Markdown文件
PDF文档(打印)
分享
链接
类型
密码
更新密码