1. Home
  2. Docs
  3. Models
  4. Use variables and arrays

Use variables and arrays

Use vars #

released: v0.21.0

You can define and use simple variables. First parameter is the variable name, the second the value.

{{block "CONFIG" . }}
  {{setvar "key1" "value1" }}
  {{setvar "key2" "value2" }}
{{end}}

In the SELECT block, you can use the variables

{{block "SELECT" . }}
  SELECT
    CONCAT(foo, " ", {{.Vars.key1 }}) as foo_advanced1,
    CONCAT(foo, " ", {{.Vars.key2 }}) as foo_advanced2
  FROM {{ ref "table1" }}
{{end}}

Use arrays #

released: v0.21.0

First parameter is the array name, all following the values.

{{ block "CONFIG" .}}
  {{ setarray "columns" "dimension1" "dimension2" "kpi1" "kpi2"}}
{{end}}

{{block "SELECT" . }}
  SELECT
    {{range .Arrays.columns}}{{.}}, {{end}}
    NOW() as update_ts
  FROM
    {{ .Project }}.{{ ref "test2" }}
  WHERE
    "date" BETWEEN '{{ .StartDate }}' and '{{ .EndDate }}'
{{end}}

This is a little bit useless example, as it only adds overhead. But as soon as you use the column names at two or more places within a model, this totally makes sense, as you have to define the columns only once. The full power of variables can be achieved in combination with global models. You can reuse global models for different projects even if they differ a bit. In this case you would define a varibale in model which then can be used in the global model.

Named 2 dimensional arrays #

released: v0.21.10

With named 2 dimensional arrays you can build more complex variable structures. First parameter is the array name, all following are pairs of keys and values while keys names can be used multiple times

{{ block "CONFIG" .}}
  {{setMultiDimArray2 "multiDimArray2Name" "keyName" "A" "keyName" "B" "keyName" "C" "keyName2" "L" "keyName2" "M" "keyName2" "N"}}
{{end}}

{{block "SELECT" . }}
  {{ range $index, $element := .MultiDimArrays2.multiDimArray2Name}}
    {{ range $index2, $element2 := $element}}
    "{{$index}}_index{{ $index2 }}_value{{$element2}}",
    {{- end}}
  {{- end}}
{{end}}

This example creates following statement. Even if it is not useful in the sense of SQL it shows what you can do with it.


  "keyName_index0_valueA",
  "keyName_index1_valueB",
  "keyName_index2_valueC",
  
  "keyName2_index0_valueL",
  "keyName2_index1_valueM",
  "keyName2_index2_valueN",

Variables in refs #

released: v0.21.0

Variables like {{ .Project }} or variable set with setvar can be used in refs as well. Therefore we have use the printf function:

{{block "CONFIG" . }}
  {{setvar "key1" "value1" }}
{{end}}

{{block "SELECT" . }}
  SELECT
    foo,
    bar
  FROM {{ ref ( printf  "%s_table1_%s" .Project .Vars.key1 ) }}
{{end}}

This will reference the table mycustomer_table1_value1, if you are in the project called mycustomer.

Default/fallback values for variables #

released: v0.21.0

You can define default/fallback values, which are used if the variable is not defined in the CONFIG block.


{{block "CONFIG" . }}
{{end}}

{{block "SELECT" . }}
  SELECT
    CONCAT(foo, " ", {{.Vars.key1 | default "bla" }}) as foo_advanced1,
    CONCAT(foo, " ", {{.Vars.key2 | default "blub" }}) as foo_advanced2
  FROM {{ ref "table1" }}
{{end}}