AQL in 30 minutes
AQL is a metrics-centric query language that both allows users to query to get a data table output like SQL, and defining independent metrics (aggregation definitions) to be reused in later table queries. In this short tutorial, we will go through a few examples that give you a sense of what AQL is and how to use it.
Prerequisites
First we need to define models/datasets with AML. Given that we already defined data models in AML, we can use AQL to query those models
An AQL expression only works within the context of a dataset as it requires knowledge of the models and relationships between them defined in the dataset
// Define users model
Model users {
...
dimension id {...}
dimension users {...}
dimension age {...}
dimension country_id {...}
}
// Define countries model
Model countries {
...
dimension id {...}
dimension name {...}
}
// Define e_commerce dataset
Dataset e_commerce {
models: [users, countries]
relationships: [relationship(users.country_id > countries.id, true)]
}
The Basic
Here are a few basic queries:
// Querying a table/model
users // equivalent to `select * from {{#users}}`
// Select only subset of columns/fields
select(users, users.id, users.gender)
// Aggregation (without grouping)
avg(users, users.age)
The pipe operator
Unlike SQL where the whole query requires to be a fixed pattern (select...from...where...group by...), AQL allows a query to be broken down into smaller components joined by the "pipe operator". We believe this syntax allows for a more natural way to map user's mental model to the resulting query. Generally pipe works like this: users | avg(users.age)
is equivalent to avg(users, users.age)
. In general, this means the part before the pipe is fed into the function behind the pipe as the first argument.
// Same select expression but with pipe
users | select(users.id, users.gender)
// Same average aggregation with pipe
users | avg(users.age)
// Filtering data
users
| filter(gender == 'Female')
| select(id, name)
// returns a single number representing average age of all users in the table
Cross-model calculation
The key difference between AQL and SQL is that since we already defined relationships in the dataset with AML, user doesn't need to explicit join the required table to get desired result. Examples:
// Select users with country name from joined "country" model
users
| select(users.name, country.name)
// Filtering on another joined model
users
| filter(country.name == 'Singapore')
| select(id, name)
Notice that there is no need for explicitly join between user
and country
tables like in SQL.
Grouping and aggregation
Without grouping, aggregation is evaluated on the whole table e.g. users | avg(users.age)
will returns a single number representing the average age of all users in users table. With grouping, aggregation is evaluated in groups:
users
| group(users.gender)
| select(users.gender, count(users.id))
// Grouping works across models, too
users
| group(countries.name)
| select(countries.name, count(users.id))
The way group works is pretty similar to SQL's "group by". Your subsequent select
must match the grouping previously used in group
. For example the following will throw an error:
users
| group(users.gender)
| select(users.gender, countries.name, count(users.id))
However, there is a difference. The following query will, for example, returns a table with 2 rows "Male" and "Female":
// Grouping without aggregation
users
| group(users.gender)
For more details, you can read the reference page for group
function.
Reusing aggregation with measure
We want to reuse count(users.id) for later use
Model users {
...
measure user_count {
definition: @aql count(users.id);;
}
}
Then we can replace previous query with the following
users
| group(users.gender)
| select(users.gender, users.user_count)
The previous change doesn't seem to gain much benefit for us given the hassle of modifying the AML code. We will see, however, the benefits in subsequent examples.
Table expression vs Metrics expression
In the previous query examples, we always start with a table and end with a table. Those are a kind of AQL expression called Table Expression. Functions like select
, filter
, group
works on table and returns new table and so they are called Table Functions.
We also saw how an aggegration i.e. count(users.id)
can be reused by putting it into the AML measure definition. This kind of aggregation code is call Metrics Expression, representing a reusable business metric. In addition, the functions that work on metrics and returns new metrics are called Metrics Functions. We will learn more complex examples of metrics expressions and functions in the next examples.
Let's say we want to count number of female users, grouped by country. You can use table expression like so:
// Filtering aggregation with `where`
users
| filter(users.gender == 'Female')
| group(countries.name)
| select(countries.name, count(users.id))
This works fine, but what if we want to reuse a metric similar to the example above? With metric functions, we can do it like this:
users
| group(countries.name)
| select(countries.name, count(user.id) | where(users.gender == 'Female'))
Notice the where
part behind the count(user.id)
. This effectively creates a query that is equivalent to the previous query but attaching the filtering logic into the metric itself.
And we can make it reusable:
Model users {
...
measure female_user_count {
definition: @aql count(users.id) | where(users.gender == 'Female') ;;
}
}
// And the resulting query becomes
users
| group(countries.name)
| select(countries.name, users.female_user_count)
Can you see the benefits? By pushing the logic into the reused metric definition, you don't need to worry about where to put the filter
function later on, especially when the query gets more complicated.
What is a metric
A metric is basically just a piece of aggregation logic (represented by aggregation functions such as sum
, count
, avg
, etc.) attached with some context. In the case above, the context is simply the condition of users.gender == 'Male'
, which is attached to the aggregation logic by the where
function.
There are other functions that allow you to manipulate the context of a metric. You can change the condition, the relationship, adding window logic to a metrics with these functions. In the next part we will discuss another type of context modifier called Level of Detail.
Level of Detail
A metric by itself is basically just an a piece of aggregation calculation logic and can't returns data. Only when used in the context of a query with accompanying group
and select
that we can get a table data result. When we allow defining an independent metric with metric expression, however, sometimes it makes sense to add a fixed "grouping" to that metrics, which we call "grains".
For example, we want to calculate "Percentage of users by country over total users". In SQL, you can solve this using a CTE, a subquery or the over
window function. These solutions work, but the aggregation logic is coupled with the implementation details of each solution.
In AQL, we can write a query like this:
users
| group(countries.name)
| select(
countries.name,
count(users.id) / (count(users.id) | of_all(countries))
)
Notice 2 things from the above example:
- We can create a new metric by creating a ratio of two metrics
of_all
is a metric function that allow a metric to "ignore" all the grouping/filtering part of the query. In this case, it allows the users count to be calculated across all countries and makes percentage calculation possible.
The key advantage of this over the SQL solutions is the fact that with this new approach, it is crystal clear that the requirement is basically about creating a new ratio metric where the denominator is the same as the numerator except for the "grains", or "level of detail". The logic also totally decoupled from how it is calculated and can even be maded reusable:
Dataset users_countries {
models: [users, countries]
relationships: [relationship(users.country_id > countries.id, true)]
// ...
metric user_count {
definition: @aql count(users.id) ;;
}
metric user_count_across_countries {
definition: @aql user_count | of_all(countries) ;;
}
metric pct_user_count_across_countries {
definition: @aql user_count / users.user_count_across_countries ;;
}
}
You can learn more about Level of Detail here.
Conclusion
Hope you have a taste of AQL and its power! There are a lot more to learn about AQL. You can read more about Table Expressions and Metric Expression, AQL Functions to build up those expressions. You can also check out the analytic use cases such as Percent of Total or Cohort Analysis to see more AQL in action.
Cheers!