Malloy Documentation

SQL Expressions

Malloy allows you to call native database functions using !type. For example if you wanted to call the duckdb function bit_length that returns a number, in Malloy you couild write bitlength!number("this string).

Sometimes the SQL expression you want to write can't be expressed this way. For example in DUCKDB, the extract function looks like.

extract(part from date)

In order to make Malloy write an expression like this you can escape to a sql_<type> function. In the string parameter you can reference dimensions using the substitution operator ${dimension_name}.

SQL functions are

sql_string
sql_number
sql_date
sql_boolean

##! experimental{sql_functions}
source: flights is duckdb.table('../data/flights.parquet') extend {
    dimension:
        dep_year is sql_number(""" extract('year' from ${dep_time}) """)
    measure: flight_count is count()
}

run: flights -> dep_year + flight_count

QUERY RESULTS

[
  {
    "dep_year": 2005,
    "flight_count": 71789
  },
  {
    "dep_year": 2004,
    "flight_count": 68397
  },
  {
    "dep_year": 2003,
    "flight_count": 58676
  },
  {
    "dep_year": 2002,
    "flight_count": 49648
  },
  {
    "dep_year": 2001,
    "flight_count": 49175
  }
]

SELECT 
   extract('year' from base."dep_time")  as "dep_year",
   COUNT(1) as "flight_count"
FROM '../data/flights.parquet' as base
GROUP BY 1
ORDER BY 2 desc NULLS LAST

Reference the SQL Alias

Sometimes you want to access a field directly in SQL, for example, arrays are unnested when referenced in Malloy. You might want to access a column directly. In this case ${TABLE} returns the alias for flights2 in the SQL query.

source: flights2 is duckdb.table('../data/flights.parquet') extend  {
  dimension:
    c is sql_string(""" ${TABLE}.carrier """)
  measure: flight_count is count()
}

run: flights2 -> c + flight_count

QUERY RESULTS

[
  {
    "c": "WN",
    "flight_count": 88751
  },
  {
    "c": "US",
    "flight_count": 37683
  },
  {
    "c": "AA",
    "flight_count": 34577
  },
  {
    "c": "NW",
    "flight_count": 33580
  },
  {
    "c": "UA",
    "flight_count": 32757
  }
]

SELECT 
   base.carrier  as "c",
   COUNT(1) as "flight_count"
FROM '../data/flights.parquet' as base
GROUP BY 1
ORDER BY 2 desc NULLS LAST