btbytes.com

Malloy

2 min read

Malloy a modern language for analyzing,
transforming, and modeling data.

run: duckdb.table('../data/airports.parquet') -> {
  select:
    id
    code
    city
  limit: 10
}

is equivalent to SELECT id, code, city FROM airports LIMIT 10 in SQL.

In Malloy, all output fields have names.

One of the main benefits of Malloy is the ability to save common calculations into a data model. The data model is made of sources, which can be thought of as tables or views, but with additional information, such as joins, dimensions and measures.

source: airports is duckdb.table('../data/airports.parquet') extend {
  dimension: county_and_state is concat(county, ', ', state)
  measure: airport_count is count()
  measure: average_elevation is avg(elevation)
}

source can be imported into another using an import statement — import "airports.malloy"

Sources can also contain named views.

Joins are also declared as part of sources:

source: flights is duckdb.table('../data/flights.parquet') extend {
  join_one: aircraft on tail_num = aircraft.tail_num
}

where is used for filtering. Filtering can be applied to  aggregate calculation (a mesure)

nested views produce with subtables.

Literals of type date and timestamp are notated with an @, e.g. @2003-03-29 or @1994-07-14 10:23:59. Similarly, years (@2021), quarters (@2020-Q1), months (@2019-03), weeks (@WK2021-08-01), and minutes (@2017-01-01 10:53) can be expressed.

There is a special time literal now, referring to the current timestamp, which allows for relative time filters.

Numeric values can be extracted from time values, e.g. day_of_year(some_date) or minute(some_time)

Time ranges — @2003 to @2005

The output from one stage of a query can be passed into another stage using -> ( Pipeline )

More Reading

Graph View