使用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)

```