# sqlmeshsm [EXPERIMENTAL]
[](https://pypi.org/project/sqlmeshsm/)
[](https://opensource.org/licenses/MIT)
[](https://www.python.org)
[](https://codecov.io/gh/datnguye/sqlmesh-snow-mask)
[SQLMesh macros](https://sqlmesh.readthedocs.io/en/stable/concepts/macros/sqlmesh_macros/) used for ❄️ [Dynamic Masking Policies](https://docs.snowflake.com/en/user-guide/security-column-ddm-use) Implementation ✏️
**_List of macros_** 🚧 _(currently blocked by awaiting for more supports from the sqlmesh's Macro Context)_
- `create_masking_policy` ([source](./sqlmeshsm/macros/create_masking_policy.py))
- `apply_masking_policy` ([source](./sqlmeshsm/macros/apply_masking_policy.py))
And, the Snowflake Hooker CLI (`hook`) ⭐
**_Hooks_** ✅
- `hook drop_masking_policy -c {config.yml} -mp {func}`
## Data Masking Development
### 1. Installation
```bash
pip install sqlmeshsm --upgrade
```
In your `(sqlmesh-project-dir)/macros/__init__.py`, let's import our lib:
```python
from sqlmeshsm import macros
```
### 2. Create masking policy functions
_For example_, the `customer` table needs the following masking policies:
- First Name: mask with `*` except the first 3 characters, fixed length of 10, no masking of `null`
- Last Name: mask with the first character of Full Name, no masking of `null`
There are 2 **masking functions**, they **must be created with following requirements**:
- 📂 Files located under `(your-sqlmesh-project)/macros/snow-mask-ddl`
- 🆎 File name format: `{mp_schema}.{mp_function_name}`
```sql
-- /snow-mask-ddl/mp_schema.mp_first_name.sql
CREATE MASKING POLICY IF NOT EXISTS @schema.mp_first_name AS (
masked_column string
) RETURNS string ->
LEFT(CASE
WHEN masked_column IS NOT NULL THEN LEFT(masked_column, 3)
ELSE NULL
END || '**********', 10);
```
```sql
-- /snow-mask-ddl/mp_schema.mp_last_name.sql
CREATE MASKING POLICY IF NOT EXISTS @schema.mp_last_name AS (
masked_column string,
full_name_column string
) RETURNS string ->
CASE
WHEN masked_column IS NOT NULL THEN LEFT(full_name_column, 1)
ELSE NULL
END;
```
> `@schema` is the keyword to indicate the schema name which matches to the first part of the file name
## 3. Decide to mask model's columns
```sql
/* /models/my_customer_model.sql */
MODEL(
name my_schema.my_customer_model
kind FULL
...
)
/* MODEL SQL CODE HERE */
/* OPTIONAL, ADD this if mp_schema schema is not part of any models */
CREATE SCHEMA IF NOT EXISTS mp_schema;
/* REGISTER the masking funcs */
@create_masking_policy(mp_schema.mp_first_name)
@create_masking_policy(mp_schema.mp_last_name)
/* USE the masking funcs */
@apply_masking_policy(first_name, mp_schema.mp_first_name)
@apply_masking_policy(my_schema.my_customer_model, last_name, mp_schema.mp_last_name, ['full_name'])
```
Let's plan and apply it now: `sqlmesh plan --select-model my_schema.my_customer_model`
## 4. (Optional) Decide to clean up the masking policies
Let's run the built-in hooks:
```bash
hook drop_masking_policy -c /path/to/sqlmesh/config.yml -mp you_mp_function_name
# for example: hook drop_masking_policy -c C:\Users\DAT\.sqlmesh\config.yml -mp common.mp_first_name
```
> Try `hook -h` for more options.
**_Voila! Happy Masking 🎉_**
## Contribution
[](https://www.buymeacoffee.com/datnguye)
If you've ever wanted to contribute to this tool, and a great cause, now is your chance!
See the contributing docs [CONTRIBUTING](./CONTRIBUTING.md) for more information.
Our **_Contributors_**:
<a href="https://github.com/datnguye/sqlmesh-snow-mask/graphs/contributors">
<img src="https://contrib.rocks/image?repo=datnguye/sqlmesh-snow-mask" />
</a>
Raw data
{
"_id": null,
"home_page": "https://github.com/datnguye/sqlmesh-snow-mask",
"name": "sqlmeshsm",
"maintainer": "",
"docs_url": null,
"requires_python": ">=3.9,<4.0",
"maintainer_email": "",
"keywords": "flake8,markdown,lint",
"author": "Dat Nguyen",
"author_email": "datnguyen.it09@gmail.com",
"download_url": "https://files.pythonhosted.org/packages/cf/9d/4bbb765b3e36307541186f2dfe61b55955366f07bb285fe4bf20a68048c7/sqlmeshsm-0.1.0b5.tar.gz",
"platform": null,
"description": "# sqlmeshsm [EXPERIMENTAL]\n\n[](https://pypi.org/project/sqlmeshsm/)\n[](https://opensource.org/licenses/MIT)\n[](https://www.python.org)\n[](https://codecov.io/gh/datnguye/sqlmesh-snow-mask)\n\n[SQLMesh macros](https://sqlmesh.readthedocs.io/en/stable/concepts/macros/sqlmesh_macros/) used for \u2744\ufe0f [Dynamic Masking Policies](https://docs.snowflake.com/en/user-guide/security-column-ddm-use) Implementation \u270f\ufe0f\n\n**_List of macros_** \ud83d\udea7 _(currently blocked by awaiting for more supports from the sqlmesh's Macro Context)_\n\n- `create_masking_policy` ([source](./sqlmeshsm/macros/create_masking_policy.py))\n- `apply_masking_policy` ([source](./sqlmeshsm/macros/apply_masking_policy.py))\n\nAnd, the Snowflake Hooker CLI (`hook`) \u2b50\n\n**_Hooks_** \u2705\n\n- `hook drop_masking_policy -c {config.yml} -mp {func}`\n\n## Data Masking Development\n\n### 1. Installation\n\n```bash\npip install sqlmeshsm --upgrade\n```\n\nIn your `(sqlmesh-project-dir)/macros/__init__.py`, let's import our lib:\n\n```python\nfrom sqlmeshsm import macros\n```\n\n### 2. Create masking policy functions\n\n_For example_, the `customer` table needs the following masking policies:\n\n- First Name: mask with `*` except the first 3 characters, fixed length of 10, no masking of `null`\n- Last Name: mask with the first character of Full Name, no masking of `null`\n\nThere are 2 **masking functions**, they **must be created with following requirements**:\n\n- \ud83d\udcc2 Files located under `(your-sqlmesh-project)/macros/snow-mask-ddl`\n- \ud83c\udd8e File name format: `{mp_schema}.{mp_function_name}`\n\n```sql\n-- /snow-mask-ddl/mp_schema.mp_first_name.sql\nCREATE MASKING POLICY IF NOT EXISTS @schema.mp_first_name AS (\n masked_column string\n) RETURNS string ->\n LEFT(CASE\n WHEN masked_column IS NOT NULL THEN LEFT(masked_column, 3)\n ELSE NULL\n END || '**********', 10);\n```\n\n```sql\n-- /snow-mask-ddl/mp_schema.mp_last_name.sql\nCREATE MASKING POLICY IF NOT EXISTS @schema.mp_last_name AS (\n masked_column string,\n full_name_column string\n) RETURNS string ->\n CASE\n WHEN masked_column IS NOT NULL THEN LEFT(full_name_column, 1)\n ELSE NULL\n END;\n```\n\n> `@schema` is the keyword to indicate the schema name which matches to the first part of the file name\n\n## 3. Decide to mask model's columns\n\n```sql\n/* /models/my_customer_model.sql */\nMODEL(\n name my_schema.my_customer_model\n kind FULL\n ...\n)\n\n/* MODEL SQL CODE HERE */\n\n/* OPTIONAL, ADD this if mp_schema schema is not part of any models */\nCREATE SCHEMA IF NOT EXISTS mp_schema;\n\n/* REGISTER the masking funcs */\n@create_masking_policy(mp_schema.mp_first_name)\n@create_masking_policy(mp_schema.mp_last_name)\n\n/* USE the masking funcs */\n@apply_masking_policy(first_name, mp_schema.mp_first_name)\n@apply_masking_policy(my_schema.my_customer_model, last_name, mp_schema.mp_last_name, ['full_name'])\n```\n\nLet's plan and apply it now: `sqlmesh plan --select-model my_schema.my_customer_model`\n\n## 4. (Optional) Decide to clean up the masking policies\n\nLet's run the built-in hooks:\n\n```bash\nhook drop_masking_policy -c /path/to/sqlmesh/config.yml -mp you_mp_function_name\n# for example: hook drop_masking_policy -c C:\\Users\\DAT\\.sqlmesh\\config.yml -mp common.mp_first_name\n```\n\n> Try `hook -h` for more options.\n\n**_Voila! Happy Masking \ud83c\udf89_**\n\n## Contribution\n\n[](https://www.buymeacoffee.com/datnguye)\n\nIf you've ever wanted to contribute to this tool, and a great cause, now is your chance!\n\nSee the contributing docs [CONTRIBUTING](./CONTRIBUTING.md) for more information.\n\nOur **_Contributors_**:\n\n<a href=\"https://github.com/datnguye/sqlmesh-snow-mask/graphs/contributors\">\n <img src=\"https://contrib.rocks/image?repo=datnguye/sqlmesh-snow-mask\" />\n</a>\n",
"bugtrack_url": null,
"license": "MIT",
"summary": "SQLMesh macros used for \u2744\ufe0f Dynamic Masking Policies implementation \u270f\ufe0f, and the Snowflake Hooker CLI (hook) \u2b50",
"version": "0.1.0b5",
"project_urls": {
"Homepage": "https://github.com/datnguye/sqlmesh-snow-mask",
"Repository": "https://github.com/datnguye/sqlmesh-snow-mask"
},
"split_keywords": [
"flake8",
"markdown",
"lint"
],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "f015583cead505a9b678a107f91c559de30fd26f9498b98206f546d88301ab3b",
"md5": "c257268d4d7836fffd5549eee6d723ab",
"sha256": "cd0ad281b725c1449d648e3a1a6e9e1298a024d8c50c0ed79682d0373f7a74b6"
},
"downloads": -1,
"filename": "sqlmeshsm-0.1.0b5-py3-none-any.whl",
"has_sig": false,
"md5_digest": "c257268d4d7836fffd5549eee6d723ab",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": ">=3.9,<4.0",
"size": 12269,
"upload_time": "2023-09-23T07:11:11",
"upload_time_iso_8601": "2023-09-23T07:11:11.495511Z",
"url": "https://files.pythonhosted.org/packages/f0/15/583cead505a9b678a107f91c559de30fd26f9498b98206f546d88301ab3b/sqlmeshsm-0.1.0b5-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "cf9d4bbb765b3e36307541186f2dfe61b55955366f07bb285fe4bf20a68048c7",
"md5": "f52b75f0c443681f6cc1a9a68210b1c0",
"sha256": "7f17d5cfca31fa12c9c87ea5cdc3bcac7e9abc9b8d1e3d044d9937dee5a8b468"
},
"downloads": -1,
"filename": "sqlmeshsm-0.1.0b5.tar.gz",
"has_sig": false,
"md5_digest": "f52b75f0c443681f6cc1a9a68210b1c0",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.9,<4.0",
"size": 8497,
"upload_time": "2023-09-23T07:11:13",
"upload_time_iso_8601": "2023-09-23T07:11:13.384463Z",
"url": "https://files.pythonhosted.org/packages/cf/9d/4bbb765b3e36307541186f2dfe61b55955366f07bb285fe4bf20a68048c7/sqlmeshsm-0.1.0b5.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2023-09-23 07:11:13",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "datnguye",
"github_project": "sqlmesh-snow-mask",
"travis_ci": false,
"coveralls": false,
"github_actions": true,
"lcname": "sqlmeshsm"
}
JSON
