CLOE SQL Composer

The CLOE SQL composer is a lightweight framework aiming to automate and standardize the creation of SQL statements such as table DDLs and procedures. Simple SQL statements can be generated fully automated based on provided metadata. The metadata will be converted into a standardized YAML format. From these YAML models the SQL statements will be derived. For more complex SQL statements custom YAML models can be created or existing once adjusted.

Installation

This tool is a small Python package and thus can be installed via pip.

pip install cloe-sql-composer --index-url https://pkgs.dev.azure.com/initions-consulting/Public/_packaging/ics-py/pypi/simple/

User guide

The SQL composer tool provides two base functionalities:

1. It can retrieve metadata from a given source and convert it into a standardized YAML format.
2. It can read the YAML models and generate SQL statements from them.

Basic usage

The tool ships with an easy-to-use command line interface. After the package has been installed creating models and SQL statements is as simple as executing the program in a terminal. Additional information is provided via YAML configuration files and optionally custom Jinja templates.

Compose models

Creating YAML models storing given metadata in a standardized way, can be achieved by executing the following command.

cloecompose model [OPTIONS] ./path/to/config.yaml

Options:
    --output: root directory where the generated YAML files will be stored # (1)!
    --env: path to .env file storing required environment variables # (2)!

1. Default: ./models

2. E.g.: when fetching metadata from Snowflake, provide connection parameters:

   Variable name	Description
   CLOE_SNOWFLAKE_USER	Snowflake user
   CLOE_SNOWFLAKE_PASSWORD	user's password
   CLOE_SNOWFLAKE_ACCOUNT	Account identifier
   CLOE_SNOWFLAKE_WAREHOUSE	Warehouse used to execute queries
   CLOE_SNOWFLAKE_ROLE	Snowflake role to use in session
   CLOE_SNOWFLAKE_DATABASE	Database name

Additional information required to retrieve the metadata and generate appropriate model files must be provided in a configuration YAML file. A detailed explanation of the configuration file can be found here.

Compose SQL

Whereas the first functionality provides you with an option to standardize the format of stored metadata, the second function of the SQL composer will actually generate SQL statements for you. This part picks up the YAML models providing the metadata for specific SQL objects (e.g. tables, procedures). These can be autogenerated or customized models. In combination with a set of Jinja templates this step will provide the final SQL statements.

cloecompose sql [OPTIONS] ./path/to/models # (1)!

Options:
    --output: root directory where the generated SQL statements will be stored # (2)!
    --templates: path to directory storing custom Jinja templates

1. This can be either a directory storing multiple YAML files or a single YAML file. In case of a directory the program will recursively search for valid models in the directory and all subdirectories and generate SQL statements.
2. Default: ./sql_output

Detailed information on the models can be found here. Information on the possibility to use custom templates can be found here.

Developer guide

To be done.