Create naming rules for a template
In this tutorial, you will learn how to annotate an ADM-template with naming-rules. In particular, we setup naming-rules that only allow a table or other object to be created if its name is fully capitalized. Interested? Let’s get started!
| More background information on naming rules is found here: How-to: develop templates |
Arrange
-
Have a basic ADM-setup on your Git-repo using for example this guide: How-to: setup Gitlab repository
-
A valid ADM license added to the /environment directory
-
We will us ADM template - create_ext_table.sql.j2 for this section
Click to see template - create_ext_table.sql.j2
/**
* Derived from template: {{ template }}
*
* This script solely creates an external table
*
* Jinja variables to set:
* - var_env_list: ARRAY e.g. ["T"], ["T","A"], ["T","A","P"]
* - var_rl_adm: STRING e.g. "RL_DWH_T_ADMIN"
* - var_wh: STRING e.g. "WH_DWH_ETL"
* - var_db: STRING e.g. "DB_DWH_T"
* - var_sch: STRING e.g. "INF_CUSTOMER"
* - var_stg: STRING e.g. "generic"
* - var_ext_tab: STRING e.g. "EXT_S3"
* - var_file_format: STRING e.g. "CSV"
* - var_refresh: BOOLEAN e.g. "TRUE"
**/
{% for var_env in var_env_list %}
SET var_rl_adm = '{{ var_rl_adm | resolve(var_env=var_env) }}';
SET var_wh = '{{ var_wh | resolve(var_env=var_env) }}';
SET var_db = '{{ var_db | resolve(var_env=var_env) }}';
SET var_sch = '{{ var_sch | resolve(var_env=var_env) }}';
SET var_stg = '{{ var_stg | resolve(var_env=var_env) }}';
SET var_ext_tab = '{{ var_ext_tab }}';
SET var_file_format = '{{ var_file_format }}';
SET var_refresh = '{{ var_refresh }}';
/**
* Prerequisites
**/
USE ROLE IDENTIFIER($var_rl_adm);
USE DATABASE IDENTIFIER($var_db);
USE SCHEMA IDENTIFIER($var_sch);
DESC STAGE IDENTIFIER($var_stg);
/**
* Create external table
**/
USE ROLE IDENTIFIER($var_rl_adm);
USE DATABASE IDENTIFIER($var_db);
USE SCHEMA IDENTIFIER($var_sch);
CREATE EXTERNAL TABLE IF NOT EXISTS IDENTIFIER($var_ext_tab)
WITH LOCATION = @{{ var_stg | resolve(var_env=var_env)}}
FILE_FORMAT = (TYPE = {{ var_file_format }} {{ var_form_opt }})
AUTO_REFRESH = {{ var_refresh }}
;
/**
* Validation
**/
USE ROLE IDENTIFIER($var_rl_adm);
USE DATABASE IDENTIFIER($var_db);
USE SCHEMA IDENTIFIER($var_sch);
DESC EXTERNAL TABLE IDENTIFIER($var_ext_tab);
{% endfor %}-
We use ADM template config file that instantiates (or references) create_ext_table.sql.j2
Click to see config file
{
"template": "create_ext_table.sql.j2",
"vars": {
"var_env_list": ["T"],
"var_rl_adm": "RL_DWH_T_ADMIN",
"var_wh": "WH_DWH_ETL",
"var_db": "DB_DWH_T",
"var_sch": "INF_CUSTOMER",
"var_stg": "generic",
"var_ext_tab": "RAW_EXT_S3",
"var_file_format": "CSV",
"var_refresh": "TRUE",
"var_form_opt": "COMPRESSION = AUTO"
}
}Act
Let’s create a naming rule file:
-
var_file_format - used to set a file format for an external table, where only CSV and JSON format are allowed
-
var_ext_tab - used for setting an external table name, where all should be in CAPITALS and start with a specific schema name
[
{
"identifier": "var_file_format", (1)
"rules": [ (2)
"CSV|JSON"
],
"message": (3)
"Unknown fileformat."
},
{
"identifier": "var_ext_tab", (1)
"rules": [ (2)
"^[A-Z_0-9]+$",
"^(META|RAW|DD|DA|TEMP)"
],
"message": (3)
"All tables should be in capital letters and start with a schema name."
},
]| 1 | identifier - the specified variable is subjected to naming rules |
| 2 | rules - when the variable is instantiated, all rules need to match the value being assigned |
| 3 | message - the corresponding error message |
Assert
The Linter stage will give the following error when the naming rule isn’t applied on the following configuration:
{
"template": "create_ext_table.sql.j2",
"vars": {
"var_env_list": ["T"],
"var_rl_adm": "RL_DWH_T_ADMIN"
"var_wh": "WH_DWH_ETL"
"var_db": "DB_DWH_T"
"var_sch": "INF_CUSTOMER"
"var_stg": "generic"
"var_ext_tab": "EXT_S3" (1)
"var_file_format": "PARQUET" (2)
"var_refresh": "TRUE"
}
}| 1 | Contains the table name that is checked against - "^[A-Z_0-9]+$", "^(META|RAW|DD|DA|TEMP)" |
| 2 | Contains the file format that is checked against - "CSV|JSON" |
