AQL Overview
Introduction
AQL (Analytical Querying Language) is both a query language and a metric definition language. It leverages the data semantic model defined with AML to allow you to query your data in a higher abstraction manner, especially composing and reusing metric-based queries.
With AQL, you can quickly implement analytics use cases that are typically complicated in SQL. Things like Percent of Total, Nested Aggregations, Level of Details, among other things.
AQL is designed based on Metrics-centric Thinking paradigm. In AQL, metrics are elevated to first-class status, which means they can be defined, manipulated and reused, independently from tables and models.
When executed, AQL compiles to SQL and thus works with most databases that use SQL. Currently supported databases are PostgreSQL, Amazon Redshift, Google BigQuery, Snowflake, Databricks, Microsoft SQLServer, Clickhouse; and more are being added.
Why AQL
In the previous version of Holistics' semantic layer (3.0), metrics were defined using SQL. This approach had the advantage of being familiar to data analysts and addressed SQL's scalability and reusability issues. However, our experience with common yet more advanced analytics use cases—such as time comparisons and metrics with varied Level of Details—reveals that the effort required is still not ideal.
To solve those use cases, instead of working directly with the SQL definition of a metric, users often need to return to build additional models and datasets as a foundation for the metric to function properly. This not only increases the maintenance burden for data analysts but also requires business users to remember to use the correct dataset for each specific use case. This adds mental friction to the adoption of self-service analytics.
The root cause of the problem is due to the limitation of SQL in terms of composability. Ideally, we want a solution where metrics can be easily composed to solve common analytics use cases above, without the need to constantly go back to SQL to build additional models and datasets. It should be simple enough that even more savvy business users can use. Yet, it needs to be able to compile to SQL to fit with Holistics' SQL-based approach. AQL was conceived as that ideal solution.
Design principles
- Locality: Common metrics-based analytics use cases can be done locally within the metric definition, without the need to modify the underlying data models and datasets using custom SQL.
- Composability: Metrics logic can be broken down into smaller components and incrementally composed. This enhances maintainability and reusability.
- Built-in analytical abilities: Most common metrics analytics use case can be usually done using a single pre-built AQL function, while more complex ones can be easily composed from a few functions.
- Instant feedback: Robust type system and IDE should allow faster feedback loop during development compared to SQL.
- Query Performance: Semantic understanding of the metrics should allow more fine-grained performance optimization.
Documentation Structure
AQL documentation are organized into the following sections:
- AQL (Querying Language): Basic overview and introduction to AQL
- AQL Components: Reference pages for the low-level building blocks of AQL
- AQL Metrics: High-level overview of Metrics in AQL
- AQL Functions: Reference pages for analytical functions supported in AQL
- Use Cases: List of specific use cases that can be solved using AQL
Useful links
To quickly get used to AQL, we suggest you checking out the following pages:
Getting Started with AQL: Instructions on how to set up AQL and a short tutorial on basic capabilities of the language
AQL Expressions: For a basic understanding of AQL syntax
AQL Metrics by Examples: For more concrete examples of AQL metrics in real scenarios
Other useful links: