| Name | sqlplate JSON |
| Version |
0.0.7
JSON |
| download |
| home_page | None |
| Summary | SQL template generator |
| upload_time | 2025-08-28 02:44:07 |
| maintainer | None |
| docs_url | None |
| author | None |
| requires_python | >=3.10 |
| license | None |
| keywords |
utility
sql
|
| VCS |
 |
| bugtrack_url |
|
| requirements |
No requirements were recorded.
|
| Travis-CI |
No Travis.
|
| coveralls test coverage |
No coveralls.
|
# SQL Template
[](https://github.com/korawica/sqlplate/actions/workflows/tests.yml)
[](https://pypi.org/project/sqlplate/)
[](https://pypi.org/project/sqlplate/)
[](https://github.com/korawica/sqlplate)
[](https://github.com/korawica/sqlplate/blob/main/LICENSE)
A **SQL Template Generator** (aka `SqlPlate`) provide the generator object for
SQL template statements via Python API object.
All SQL template files are store in the [Jinja template](https://jinja.palletsprojects.com/en/stable/templates/)
format that is the powerful template tool package.
> [!NOTE]
> This project keep all SQL use-case that use on Data Engineer works like SCD2
> with `MERGE` operator.
**The layer of SQL template files will be:**
```text
templates/
├─ databricks/
│ ├─ 📂macros/
│ │ ╰─ ⚙️ delta.jinja
│ ╰─ 📂latest/
│ ├─ 📜 etl.delta.sql
│ ├─ 📜 etl.scd2.sql
│ ╰─ 📜 select.sql
├─ sqlite/
│ ╰─ 📂latest/
│ ╰─📜 etl.delta.sql
├─ synapse/
│ ╰─ 📂latest/
│ ╰─📜 etl.delta.sql
╰─ utils/
╰─ ⚙️ etl_vars.jinja
```
> [!IMPORTANT]
> The first object of this project is generate ETL statements for dynamic service
> change. You can change the SQL compute service while the SQL statement does not
> change.
## :package: Installation
```shell
pip install -U sqlplate
```
## :fork_and_knife: Usage
### Generate SQL template
Start passing option parameters before generate the Delta ETL SQL statement that
will use on the Azure Databricks service.
```python
from datetime import datetime
from sqlplate import SQLPlate
statement: str = (
SQLPlate.format('databricks')
.template('etl.delta')
.option('catalog', 'catalog-name')
.option('schema', 'schema-name')
.option('table', 'table-name')
.option('pk', 'pk_col')
.option('columns', ['col01', 'col02'])
.option('query', 'SELECT * FROM catalog-name.schema-name.source-name')
.option('load_src', 'SOURCE_FOO')
.option('load_id', 1)
.option('load_date', datetime(2025, 2, 1, 10))
.option('only_main', True)
.load()
)
print(statement.strip().strip('\n'))
```
The result SQL statement:
```sql
MERGE INTO catalog-name.schema-name.table-name AS target
USING (
WITH change_query AS (
SELECT
src.*,
CASE WHEN tgt.pk_col IS NULL THEN 99
WHEN hash(src.col01, src.col02) <> hash(tgt.col01, tgt.col02) THEN 1
ELSE 0 END AS data_change
FROM ( SELECT * FROM catalog-name.schema-name.source-name ) AS src
LEFT JOIN catalog-name.schema-name.table-name AS tgt
ON tgt.col01 = src.col01
AND tgt.col02 = src.col02
)
SELECT * EXCEPT( data_change ) FROM change_query WHERE data_change IN (99, 1)
) AS source
ON target.pk_col = source.pk_col
WHEN MATCHED THEN UPDATE
SET target.col01 = source.col01
, target.col02 = source.col02
, target.updt_load_src = 'SOURCE_FOO'
, target.updt_load_id = 1
, target.updt_load_date = to_timestamp('20250201', 'yyyyMMdd')
WHEN NOT MATCHED THEN INSERT
(
col01, col02, pk_col, load_src, load_id, load_date, updt_load_src, updt_load_id, updt_load_date
)
VALUES (
source.col01,
source.col02,
source.pk_col,
'SOURCE_FOO',
1,
20250201,
'SOURCE_FOO',
1,
to_timestamp('20250201', 'yyyyMMdd')
)
;
```
### Data Quality
This package handle generate SQL statement only.
For the data quality part, this package can use quality templates instead.
```python
from sqlplate import SQLPlate
statement: str = (
SQLPlate.format('databricks')
.template('quality.check')
.option('catalog', 'catalog-name')
.option('schema', 'schema-name')
.option('table', 'table-name')
.option('filter', "load_date >= to_timestamp('20250201', 'yyyyMMdd')")
.option('unique', ['pk_col'])
.option('notnull', ['col01', 'col02'])
.check("contain", ["col01"], "IN ['A', 'B', 'C']")
.check("gt_10000", ["col03"], "> 10000")
.load()
)
print(statement.strip().strip('\n'))
```
The result SQL statement:
```sql
WITH source AS (
SELECT
*
FROM
catalog-name.schema-name.table-name
WHERE load_date >= to_timestamp('20250201', 'yyyyMMdd')
)
, records AS (
SELECT COUNT(1) AS table_records FROM source
)
SELECT
(SELECT table_records FROM records) AS table_records
, (SELECT COUNT( DISTINCT pk_col ) FROM source) = table_records AS unique_pk_col
, (SELECT COUNT_IF( col01 IS NULL ) FROM source) = 0 AS notnull_col01
, (SELECT COUNT_IF( col02 IS NULL ) FROM source) = 0 AS notnull_col02
, (SELECT COUNT(1) FILTER(WHERE col01 IN ['A', 'B', 'C']) FROM source) = table_records AS contain_col01
, (SELECT COUNT(1) FILTER(WHERE col03 > 10000) FROM source) = table_records AS gt_10000_col03
FROM records
```
## :chains: Support Systems
| System | Progress Status | System Integration Test | Remark |
|:-------------------|:----------------:|:-----------------------:|:----------------------------------|
| databricks | 🟢 | 🟡 | Azure Databricks |
| postgres | 🔴 | 🔴 | |
| mysql | 🔴 | 🔴 | |
| mssql | 🔴 | 🔴 | Microsoft SQL Server |
| synapse | 🔴 | 🔴 | Azure Synapse Dedicate SQL Pool |
| synapse-serverless | 🔴 | 🔴 | Azure Synapse Serverless SQL Pool |
| bigquery | 🟡 | 🔴 | Google BigQuery |
| snowflake | 🔴 | 🔴 | |
| sqlite | 🟡 | 🟡 | |
| duckdb | 🟡 | 🟡 | |
| redshift | 🔴 | 🔴 | Amazon RedShift |
| athena | 🔴 | 🔴 | Amazon Athena |
| trino | 🔴 | 🔴 | Trino |
> [!NOTE]
> - 🟢 Complete
> - 🟡 In progress
> - 🔴 Does not develop yet
> - 🟣 Does not plan to support
## :speech_balloon: Contribute
I do not think this project will go around the world because it has specific propose,
and you can create by your coding without this project dependency for long term
solution. So, on this time, you can open [the GitHub issue on this project :raised_hands:](https://github.com/korawica/sqlplate/issues)
for fix bug or request new feature if you want it.
Raw data
{
"_id": null,
"home_page": null,
"name": "sqlplate",
"maintainer": null,
"docs_url": null,
"requires_python": ">=3.10",
"maintainer_email": null,
"keywords": "utility, sql",
"author": null,
"author_email": "korawica <korawich.anu@gmail.com>",
"download_url": "https://files.pythonhosted.org/packages/93/96/9e2fe7bafe4c78e97444f605855e6c5e312c7901d408bd9a20e7767be237/sqlplate-0.0.7.tar.gz",
"platform": null,
"description": "# SQL Template\n\n[](https://github.com/korawica/sqlplate/actions/workflows/tests.yml)\n[](https://pypi.org/project/sqlplate/)\n[](https://pypi.org/project/sqlplate/)\n[](https://github.com/korawica/sqlplate)\n[](https://github.com/korawica/sqlplate/blob/main/LICENSE)\n\nA **SQL Template Generator** (aka `SqlPlate`) provide the generator object for\nSQL template statements via Python API object.\nAll SQL template files are store in the [Jinja template](https://jinja.palletsprojects.com/en/stable/templates/)\nformat that is the powerful template tool package.\n\n> [!NOTE]\n> This project keep all SQL use-case that use on Data Engineer works like SCD2\n> with `MERGE` operator.\n\n**The layer of SQL template files will be:**\n\n```text\ntemplates/\n \u251c\u2500 databricks/\n \u2502 \u251c\u2500 \ud83d\udcc2macros/\n \u2502 \u2502 \u2570\u2500 \u2699\ufe0f delta.jinja\n \u2502 \u2570\u2500 \ud83d\udcc2latest/\n \u2502 \u251c\u2500 \ud83d\udcdc etl.delta.sql\n \u2502 \u251c\u2500 \ud83d\udcdc etl.scd2.sql\n \u2502 \u2570\u2500 \ud83d\udcdc select.sql\n \u251c\u2500 sqlite/\n \u2502 \u2570\u2500 \ud83d\udcc2latest/\n \u2502 \u2570\u2500\ud83d\udcdc etl.delta.sql\n \u251c\u2500 synapse/\n \u2502 \u2570\u2500 \ud83d\udcc2latest/\n \u2502 \u2570\u2500\ud83d\udcdc etl.delta.sql\n \u2570\u2500 utils/\n \u2570\u2500 \u2699\ufe0f etl_vars.jinja\n```\n\n> [!IMPORTANT]\n> The first object of this project is generate ETL statements for dynamic service\n> change. You can change the SQL compute service while the SQL statement does not\n> change.\n\n## :package: Installation\n\n```shell\npip install -U sqlplate\n```\n\n## :fork_and_knife: Usage\n\n### Generate SQL template\n\nStart passing option parameters before generate the Delta ETL SQL statement that\nwill use on the Azure Databricks service.\n\n```python\nfrom datetime import datetime\nfrom sqlplate import SQLPlate\n\nstatement: str = (\n SQLPlate.format('databricks')\n .template('etl.delta')\n .option('catalog', 'catalog-name')\n .option('schema', 'schema-name')\n .option('table', 'table-name')\n .option('pk', 'pk_col')\n .option('columns', ['col01', 'col02'])\n .option('query', 'SELECT * FROM catalog-name.schema-name.source-name')\n .option('load_src', 'SOURCE_FOO')\n .option('load_id', 1)\n .option('load_date', datetime(2025, 2, 1, 10))\n .option('only_main', True)\n .load()\n)\nprint(statement.strip().strip('\\n'))\n```\n\nThe result SQL statement:\n\n```sql\nMERGE INTO catalog-name.schema-name.table-name AS target\nUSING (\n WITH change_query AS (\n SELECT\n src.*,\n CASE WHEN tgt.pk_col IS NULL THEN 99\n WHEN hash(src.col01, src.col02) <> hash(tgt.col01, tgt.col02) THEN 1\n ELSE 0 END AS data_change\n FROM ( SELECT * FROM catalog-name.schema-name.source-name ) AS src\n LEFT JOIN catalog-name.schema-name.table-name AS tgt\n ON tgt.col01 = src.col01\nAND tgt.col02 = src.col02\n )\n SELECT * EXCEPT( data_change ) FROM change_query WHERE data_change IN (99, 1)\n) AS source\n ON target.pk_col = source.pk_col\nWHEN MATCHED THEN UPDATE\n SET target.col01 = source.col01\n , target.col02 = source.col02\n , target.updt_load_src = 'SOURCE_FOO'\n , target.updt_load_id = 1\n , target.updt_load_date = to_timestamp('20250201', 'yyyyMMdd')\nWHEN NOT MATCHED THEN INSERT\n (\n col01, col02, pk_col, load_src, load_id, load_date, updt_load_src, updt_load_id, updt_load_date\n )\n VALUES (\n source.col01,\n source.col02,\n source.pk_col,\n 'SOURCE_FOO',\n 1,\n 20250201,\n 'SOURCE_FOO',\n 1,\n to_timestamp('20250201', 'yyyyMMdd')\n )\n;\n```\n\n### Data Quality\n\nThis package handle generate SQL statement only.\nFor the data quality part, this package can use quality templates instead.\n\n```python\nfrom sqlplate import SQLPlate\n\nstatement: str = (\n SQLPlate.format('databricks')\n .template('quality.check')\n .option('catalog', 'catalog-name')\n .option('schema', 'schema-name')\n .option('table', 'table-name')\n .option('filter', \"load_date >= to_timestamp('20250201', 'yyyyMMdd')\")\n .option('unique', ['pk_col'])\n .option('notnull', ['col01', 'col02'])\n .check(\"contain\", [\"col01\"], \"IN ['A', 'B', 'C']\")\n .check(\"gt_10000\", [\"col03\"], \"> 10000\")\n .load()\n)\nprint(statement.strip().strip('\\n'))\n```\n\nThe result SQL statement:\n\n```sql\nWITH source AS (\n SELECT\n *\n FROM\n catalog-name.schema-name.table-name\n WHERE load_date >= to_timestamp('20250201', 'yyyyMMdd')\n)\n, records AS (\n SELECT COUNT(1) AS table_records FROM source\n)\nSELECT\n (SELECT table_records FROM records) AS table_records\n , (SELECT COUNT( DISTINCT pk_col ) FROM source) = table_records AS unique_pk_col\n , (SELECT COUNT_IF( col01 IS NULL ) FROM source) = 0 AS notnull_col01\n , (SELECT COUNT_IF( col02 IS NULL ) FROM source) = 0 AS notnull_col02\n , (SELECT COUNT(1) FILTER(WHERE col01 IN ['A', 'B', 'C']) FROM source) = table_records AS contain_col01\n , (SELECT COUNT(1) FILTER(WHERE col03 > 10000) FROM source) = table_records AS gt_10000_col03\nFROM records\n```\n\n## :chains: Support Systems\n\n| System | Progress Status | System Integration Test | Remark |\n|:-------------------|:----------------:|:-----------------------:|:----------------------------------|\n| databricks | \ud83d\udfe2 | \ud83d\udfe1 | Azure Databricks |\n| postgres | \ud83d\udd34 | \ud83d\udd34 | |\n| mysql | \ud83d\udd34 | \ud83d\udd34 | |\n| mssql | \ud83d\udd34 | \ud83d\udd34 | Microsoft SQL Server |\n| synapse | \ud83d\udd34 | \ud83d\udd34 | Azure Synapse Dedicate SQL Pool |\n| synapse-serverless | \ud83d\udd34 | \ud83d\udd34 | Azure Synapse Serverless SQL Pool |\n| bigquery | \ud83d\udfe1 | \ud83d\udd34 | Google BigQuery |\n| snowflake | \ud83d\udd34 | \ud83d\udd34 | |\n| sqlite | \ud83d\udfe1 | \ud83d\udfe1 | |\n| duckdb | \ud83d\udfe1 | \ud83d\udfe1 | |\n| redshift | \ud83d\udd34 | \ud83d\udd34 | Amazon RedShift |\n| athena | \ud83d\udd34 | \ud83d\udd34 | Amazon Athena |\n| trino | \ud83d\udd34 | \ud83d\udd34 | Trino |\n\n> [!NOTE]\n> - \ud83d\udfe2 Complete\n> - \ud83d\udfe1 In progress\n> - \ud83d\udd34 Does not develop yet\n> - \ud83d\udfe3 Does not plan to support\n\n## :speech_balloon: Contribute\n\nI do not think this project will go around the world because it has specific propose,\nand you can create by your coding without this project dependency for long term\nsolution. So, on this time, you can open [the GitHub issue on this project :raised_hands:](https://github.com/korawica/sqlplate/issues)\nfor fix bug or request new feature if you want it.\n\n",
"bugtrack_url": null,
"license": null,
"summary": "SQL template generator",
"version": "0.0.7",
"project_urls": {
"Homepage": "https://github.com/korawica/sqlplate/",
"Source": "https://github.com/korawica/sqlplate/"
},
"split_keywords": [
"utility",
" sql"
],
"urls": [
{
"comment_text": null,
"digests": {
"blake2b_256": "fdd8c1490dfd006b6af3bf74398ee893bbe4706b8ef2eed7b9c66c5b71aa6aa4",
"md5": "1ec4a686149c929d9a57b217e5efa8e3",
"sha256": "8a0e60b64faada08196e432a786301efd526fc1e06f1e94b127390a4397a593a"
},
"downloads": -1,
"filename": "sqlplate-0.0.7-py3-none-any.whl",
"has_sig": false,
"md5_digest": "1ec4a686149c929d9a57b217e5efa8e3",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": ">=3.10",
"size": 9466,
"upload_time": "2025-08-28T02:44:06",
"upload_time_iso_8601": "2025-08-28T02:44:06.363276Z",
"url": "https://files.pythonhosted.org/packages/fd/d8/c1490dfd006b6af3bf74398ee893bbe4706b8ef2eed7b9c66c5b71aa6aa4/sqlplate-0.0.7-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": null,
"digests": {
"blake2b_256": "93969e2fe7bafe4c78e97444f605855e6c5e312c7901d408bd9a20e7767be237",
"md5": "cb07a3836ea1b65807c4a3b4b2ec218c",
"sha256": "6dacd5919a52b8151cd69d8ad0fd64372c0bf88aa9ff191afef91e2183346572"
},
"downloads": -1,
"filename": "sqlplate-0.0.7.tar.gz",
"has_sig": false,
"md5_digest": "cb07a3836ea1b65807c4a3b4b2ec218c",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.10",
"size": 14494,
"upload_time": "2025-08-28T02:44:07",
"upload_time_iso_8601": "2025-08-28T02:44:07.807372Z",
"url": "https://files.pythonhosted.org/packages/93/96/9e2fe7bafe4c78e97444f605855e6c5e312c7901d408bd9a20e7767be237/sqlplate-0.0.7.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2025-08-28 02:44:07",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "korawica",
"github_project": "sqlplate",
"travis_ci": false,
"coveralls": false,
"github_actions": true,
"lcname": "sqlplate"
}
