Using calculated fields in schema

The formula field property can be used to add calculated fields to your connector's schema.

The full list of functions that are supported in formulas can be found at Function List. Additionally, there is further documentation for:

These function names cannot be used as the name property for a field.

semanticType for calculated fields

To set the semanticType for a formula:

  • If the output is a number, set semanticType to NUMBER or PERCENT.
  • If the output is a string and matches a DATE_AND_TIME or GEO semanticType, set the semanticType to the corresponding enum. For example, if the output for the calculated field is "20180327", set semanticType to YEAR_MONTH_DAY.
  • For all other string outputs, set the semanticType to TEXT.

Examples of calculated fields in schema

The following schema contains two calculated fields, t_fahrenheit and weath. Note that their formula properties prepend a $ to names they refer to.

{
  "schema": [
    {
      "name": "date",
      "label": "Date",
      "description": "Date of measurement",
      "dataType": "STRING",
      "semantics": {
        "conceptType": "DIMENSION",
        "semanticType": "YEAR_MONTH_DAY",
      }
    },
    {
      "name": "t_celsius",
      "label": "Temperature (Celsius)",
      "description": "Temperature in Celsius",
      "dataType": "NUMBER",
      "semantics": {
        "conceptType": "METRIC",
        "semanticType": "NUMBER",
      }
    },
    {
      "name": "t_fahrenheit",
      "label": "Temperature (Fahrenheit)",
      "description": "Temperature in Fahrenheit",
      "dataType": "NUMBER",
      "formula": "$t_celsius/5*9+32",
      "semantics": {
        "conceptType": "METRIC",
        "semanticType": "NUMBER",
      }
    },
    {
      "name": "weather",
      "label": "Weather (based on temp)",
      "description": "Temperature in Fahrenheit",
      "dataType": "STRING",
      "formula": "CASE WHEN $t_celsius<0 THEN \"Freezing\" WHEN $t_celsius>40 THEN \"Too warm\" ELSE \"Not bad\" END",
      "semantics": {
        "conceptType": "DIMENSION",
        "semanticType": "TEXT"
      }
    }
  ]
}