AML Query Model
Define a data model on top of a custom SQL query in AML, referencing other models, declaring dimensions and measures, and configuring query persistence.
Introduction
Query Model defines a data model that sits on top of a query.
Parameter definition
| Parameter name | Description |
|---|---|
| type | Model type: 'table' for Table Model, 'query' for Query Model |
| label | User-friendly name for the model, shown to consumers of the dataset |
| description | Description of the model. Support Markdown. |
| data_source_name | The data source on which the SQL generated from this model is executed |
| owner | Define who should be in charge of managing the current model |
| models | Add models to be used in the querying |
| query | The query of the model |
| dimension | Define a dimension. |
| measure | Define a measure. |
| param | Define Query Parameters for the model's query. |
| persistence | Define Persistence settings for the model. |
Query Syntax
The general form of a query is as follows:
select
{{ #model_1.field_name }} as field_alias_1,
{{ #model_2_alias.field_name }} as field_alias_2
from {{ #model_1 }}
left join {{ model_2 as model_2_alias }}
on {{ #model_1_alias.field_name }} = {{ model_2.field_name }};;Explanation:
| Syntax | Action |
|---|---|
{{ #model_name }} | Refer to another model. REQUIRED if you want to enforce model dependency. |
{{ #model_name as alias }} | Set an alias for a model reference (place alias inside the curly brackets) |
{{ #model_name.field_name }} or {{ #alias.field_name }} | Field selection (dimension or measure) |
{{ #model_name.* }} | Get all fields of a model |
Note on Field Selection syntax
We recommend you always use Field Selection syntax for better query performance
When compiling the query definition to the final SQL, if you use the Field Selection syntax ({{ #model_name.field_name }}), the compiler can select only the table fields you require from the start.
Without the Field Selection syntax, the default behavior is to select all fields of the underlying table, and then select the required field from the result set.
select
{{ #l.id }}
, {{ #l.name }}
, {{ #l.property_type }}
, {{ #l.room_type }}
from {{ #homestay_listings as l }}The resulting query includes only the necessary fields:
select
l."id"
, l."name"
, l."property_type"
, l."room_type"
from (
SELECT
T0."id" AS "id",
T0."name" AS "name",
T0."property_type" AS "property_type",
T0."room_type" AS "room_type"
FROM "homestay"."listings" T0
) AS lThis is particularly important when you query from "fat tables" with large number of columns.
Code Example
Model location {
type: 'query'
label: 'Location'
data_source_name: 'demodb'
models: [cities, countries]
query: @sql
select
{{ #ci.id }} as city_id,
{{ #ci.name }} as city_name,
{{ #co.code }} as country_code,
{{ #co.name }} as country_name,
{{ #co.continent_name }}
from {{ #cities as ci }}
left join {{ #countries as co }} on {{ #co.code }} = {{ #ci.country_code }};;
persistence: FullPersistence {
schema: 'scratch'
view_name: 'cities_countries'
}
dimension city_id {
label: 'City Id'
type: 'number'
primary_key: true // recommended for query optimization and AQL features
}
dimension city_name {
label: 'City Name'
type: 'text'
}
dimension country_code {
label: 'Country Code'
type: 'text'
}
dimension country_name {
label: 'Country Name'
type: 'text'
}
dimension continent_name {
label: 'Continent Name'
type: 'text'
}
}