Query Model

Knowledge Checkpoint

Related docs:

• Query Model

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 that appears in the Dataset exploration UI
description	Description of the model. Support Markdown.
data_source_name	The database on which Holistics will execute the SQL generated from this model
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

tip

We recommend you always use Field Selection syntax for better query performance

When converting from Holistics's query syntax to the final SQL, if you use the Field Selection syntax ({{ #model_name.field_name }}), Holistics's engine will be able to 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.

With Field Selection

select
  {{ #l.id }}
  , {{ #l.name }}
  , {{ #l.property_type }}
  , {{ #l.room_type }}
from {{ #homestay_listings as l }}

The resulted query includes only necessary fields:

Without Field Selection

select
  id
  , name
  , property_type
  , room_type
from {{ #homestay_listings }}

The resulted query includes all the fields:

This 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'
  }

  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'
  }
}