SQL-query templates
Prerequisites
You must understand how Zenskar handles metering before proceeding with this guide.
What is a template engine?
Definitions
Template is a mold or pattern.
Template engine is a software that generates dynamic content by replacing placeholders or variables in a template with contextual data.
A generic example
To understand templates and template engine, let us explore an example. A marketing team can create an email template, as shown below:
Hello {{customer_name}},
{{product_name}} is the best way to {{feature_set}}. We are currently offerring a discount of {{discount}}%.
Hurry! This offer is only valid till {{discount_end_date}}.
Here {{customer_name}}
, {{product_name}}
, {{feature_set}}
, {{discount}}
, and {{discount_end_date}}
are placeholders.
A properly configured template engine can replace placeholders with data derived from a given context. Let us now set some context: a customer, John Doe, had shown some interest in Awesome Software, which is now available at a discount of 20% till 23 August 2023.
Placeholder | Context |
---|---|
{{customer_name}} | John Doe |
{{product_name}} | Awesome Software |
{{feature_set}} | file your taxes and attain nirvana |
{{discount}} | 20 |
{{discount_end_date}} | 23 August 2023 |
The template engine can merge the template and the context to generate the following email:
Hello John Doe,
Awesome Software is the best way to file your taxes and attain nirvana. We are currently offerring a discount of 20%.
Hurry! This offer is only valid till 23 August 2023.
Let us now set another context: a customer, Jane Doe, was interested in Yet Another Awesome Software, which is now available at a discount of 30% till 23 August 2023.
Placeholder | Context |
---|---|
{{customer_name}} | Jane Doe |
{{product_name}} | Yet Another Awesome Software |
{{feature_set}} | order food online and attain nirvana |
{{discount}} | 30 |
{{discount_end_date}} | 23 August 2023 |
The template engine can merge the template and the context to generate the following email:
Hello Jane Doe,
Yet Another Awesome Software is the best way to order food online and attain nirvana. We are currently offerring a discount of 30%.
Hurry! This offer is only valid till 23 August 2023.
Understanding SQL-query templates in Zenskar
The aim of the example presented in the above section is to help you understand the basic concepts related to templating. This section will help you understand how Zenskar leverages templates to provide you with potentially unlimited flexibility.
Definition
SQL template engine generates SQL queries by replacing placeholders or variables in an SQL query template with contextual data.
Zenskar wants you to have total control over how you query various data sources to extract meaningful and actionable data. One tool we employ to achieve this flexibility is a SQL template engine.
Fig. 1: SQL template engine generates valid SQL queries using templates and dynamic runtime data.
There are two types of queries you can write:
- Aggregate query: SQL query to create a intermediate aggregate table,
aggregates
, to hold billable aggregates for all customers in a given billing period.
Aggregate query is optional
If your source data already contains aggregates (not raw metrics), this SQL query is not needed.
![Fig. 2: Aggregate query is optional if data source already contains aggregates](https://files.readme.io/0641388-image.png)
Fig. 2: Aggregate query is optional if data source already contains aggregates.
- Customer-specific query: SQL query to fetch customer-specific billable aggregates from the
aggregates
table.
Output of the customer-specific query
The intermediate table
aggregates
contains aggregate data of all customers in a given billing period. The customer-specific query extracts customer-specific billable aggregate value (a single row) from theaggregates
table.
Example 1: Deriving aggregates from raw metrics
Step 1: Define schema and send data to Zenskar
Let us assume that you have submitted the following schema to Zenskar by navigating to Metering > Raw Metrics > + ADD RAW METRICS:
{
"data": {
"loan_id": "int",
"loan_status": "string",
"amount_financed": "float"
},
"timestamp": "timestamp",
"customer_id": "string"
}
Let us also assume that you are using the following HTTP POST
API to send raw metrics to Zenskar:
curl --location --request POST 'https://api.zenskar.com/usage/loan_demo' \
-H 'organisation: 888ae523-8878-4ed7-85cc-6c0a54320568' \
-H 'authorization: Bearer eyJhbGciOiJSUzI1NiIsInR5cCIgOiAiSlI6ImFjY291bnQiLCJzdW
-H 'Content-Type: application/json' \
--data-raw '{"data":{"loan_id":114,"loan_status":"sample_data_1","amount_financed":47.08293585087888},"timestamp":"2023-09-18 10:10:41","customer_id":"sample_data_1"}'
The above steps can be visualized using the following diagram:
![Fig. 3: From defining data schema to invoice.](https://files.readme.io/c84ea77-image.png)
Fig. 3: From defining data schema to invoice.
Step 2: Create aggregate query
- Navigate to Metering > Usage Aggregates and click on the + ADD NEW AGGREGATE button.
- Input a name for the aggregate.
- Copy the name of the raw metrics table (eg.,
raw_metric_loan
) from which you would like to derive aggregate. - Activate the SQL Aggregator tab.
- Select Raw events.
- Create an aggregate query to retrieve the desired aggregate from the
raw_metric_loan
table.
Structure of the raw metrics table, raw_metric_loan
, in Zenskar:
data.loan_id | data.loan_status | data.amount_financed | timestamp | customer_id |
---|---|---|---|---|
977 | active | 500 | 2023-07-11 21:51:13.000 | 1001 |
943 | active | 600 | 2023-07-07 00:27:20.000 | 1002 |
889 | active | 2100 | 2023-08-05 12:33:07.000 | 1001 |
117 | active | 300 | 2023-07-15 16:35:21.000 | 1001 |
444 | active | 1000 | 2023-08-27 06:00:20.000 | 1002 |
432 | active | 350 | 2023-08-20 21:53:46.000 | 1002 |
212 | active | 3500 | 2023-07-20 05:21:12.000 | 1002 |
An example aggregate query:
select "customer_id",count(*) as quantity
from raw_metric_loan
where "data.loan_status" = 'active'
and "data.amount_financed" >=500 and "data.amount_financed" <= 2000
and Date("timestamp") >= Date({{start_date}}) and Date("timestamp") <= Date({{end_date}})
group by "customer_id"
The above query will get the total number of loans where:
- the
amount_financed
is between 500-2000 USD, - the
loan_status
isactive
, and - the sanctioning date was between a specified start date (
Date("timestamp") >= Date({{start_date}})
) and end date (Date("timestamp") <= Date({{end_date}}
) grouped bycustomer_id
.
Special variables
{{start_date}}
and{{end_date}}
The
{{start_date}}
and{{end_date}}
are determined by the SQL template engine at the time of contract execution. Usually,{{start_date}}
will be correspond automatically to the start date of the billing cycle whereas{{end_date}}
will correspond to the end date of the billing cycle.You must not manipulate these variables.
The output of the aggregate query will be stored in an intermediate table called aggregates
. Assuming that the {{start_date}}
is set to 2023-07-01
and {{end_date}}
is set to 2023-08-31
, the result will look like the below table:
customer_id | quantity |
---|---|
1001 | 1 |
1002 | 2 |
Step 3: Create customer-specific query
In Step 2, you derived the desired aggregates. However, the aggregates you derived were for all customers. Now, you must retrieve the customer-specific value for billing:
select cast(quantity as decimal) as quantity
from aggregates
where "customer_id" = {{customer.external_id}}
The result of the customer-specific query for customer_id
set to 1001
will be:
quantity |
---|
1 |
SQL template engine's customer object
The SQL template engine has a context object called
customer
with all the properties that define a customer in Zenskar. Therefore, the SQL variablecustomer_id
can be set to{{customer.email}}
if you would like to treat email as key or it can be set to{{customer.external_id}}
if you would like to use Zenskar's Customer Id field as key.
Step 4: Finish
Click on the CREATE AGGREGATE button.
You can preview the output of the queries on the Usage Aggregate page by clicking on Preview.
Updated about 2 months ago