Skip to main content

SQL Template Variables

This section introduces SQL Template Variables in Timbr, enabling dynamic data manipulation and query customization through Jinja templating and ontology variable settings. These variables enhance query flexibility, allowing users to adapt queries based on variable data without altering the query logic.

Setting Ontology Variables​

Modify ontology configurations by setting variables. These variables influence queries and operations within the specific ontology context.

Required information for setting ontology variables (the curly brackets {} should not be an input, they are used only as a variable substitution):

  • {ontology_name} - The ontology to which the variable will be added.
  • {variable_name} - The variable name to set.
  • {value} - The value assigned to the variable.
ALTER ONTOLOGY {ontology_name} SET `variable.{variable_name}` = '{value}';

Example:

ALTER ONTOLOGY timbr_calls SET `variable.maxCalls` = '100';

Unsetting Ontology Variables​

Eliminate a variable's impact on an ontology by removing it.

Required information for removing ontology variables (the curly brackets {} should not be an input, they are used only as a variable substitution):

  • {ontology_name} - The ontology from which to remove the variable.
  • {variable_name} - The variable name to unset.
ALTER ONTOLOGY {ontology_name} UNSET `variable.{variable_name}`;

Example:

ALTER ONTOLOGY timbr_calls UNSET `variable.num`;

Showing Variables​

To view all set variables within the ontology, particularly to identify custom configurations:

SHOW CONFIG;

Setting User Session Variables​

Dynamically modify SQL queries within a user's session by setting session-specific variables.

Required information for setting session-specific variables (the curly brackets {} should not be an input, they are used only as a variable substitution):

  • {variable_name} - The session variable name.
  • {value} - The value for the session variable.
SET variable.{variable_name} = {value};

Examples:

SET variable.num = 10;
SELECT '{{num}}';

SET variable.person_count_user_session = (SELECT count(1) FROM timbr.person);
SELECT {{person_count_user_session}};

Jinja Templating for Dynamic SQL Queries​

Timbr supports Jinja templating for integrating variables into SQL queries, providing capabilities for conditional logic, loops, and dynamic data filtering.

Using Jinja Templates

Examples:

  • Int Value in Jinja Template:
SELECT {{num}} AS num;
  • String Value in Jinja Template:
SELECT '{{num}}' AS num;
  • Query Variable in Jinja Template:
SELECT {{count_person}} AS num_of_person FROM timbr.person;
  • Arrays Loop in Jinja Templates:
SELECT * FROM timbr.person WHERE age IN (
  {% for id in my_array %} '{{id}}'{% if not loop.last %}, {% endif %}{% endfor %}
);
  • Default Timbr Variables:
SELECT '{{timbr_username}}';
SELECT timbr_username();
SELECT '{{timbr_roles}}';
SELECT concat_ws(',', timbr_roles());