Beta Malloy Syntax

[mtoy-googly-moogly]

NOTE: This work discussed here is completed and is now the actual syntax.

Alpha Malloy

As of this writing the alpha version of the Malloy syntax is roughly a year old. It has always been our plan that the syntax would be constantly evolving the language, and we may have hit a point where a more significant change in syntax is warranted.

So what would a beta version of the Malloy syntax look like?

The problems ...

The structure free word salad that is alpha-malloy is designed to be as free of punctuation as possible, following a design principle intuited from SQL, to attempt feel familiar to SQL users.

This experiment has produced a lovely small language, but when presented to experience SQL users, there are a few bumps.

1. SQL people look for hints in the SQL to reveal structure, for example the explicit "group by" helps define the query space and the implicit group by in malloy hides an important detail.
2. (similar to the above) clear distinction between aggregates and grouping
3. filter syntax, while short and readable, is also problematic
4. pipes aren't quite right for expressing the "activate query" gesture
5. query / explorable-thing split is unclear, what is a "space"
6. "turtle" is a problem for everyone except michael and lloyd

One Proposal

In conversation with data-science SQL users, we learn that they mostly know how to read SQL, Python, and JSON. This raises the interesting possibility of going back to a more LookML inspired syntax. The new tao would be something like

... leveraging these two languages we use SQL-familiar words to describe SQL concpepts, but use JSON to make clear the structure of the model that SQL would make hidden

Farewell Space Turtles

The thing which queries start from, like in LookML, is called an "explore".

The thing which is a query, is called a "query".

The word "space" which we started throwing around to distinguish between and explore that was a query and and explore which was something which could be queried is now superfluous.

A "turtle", meaning a query declared in an explore, is now is just "a query declared in an explore".

A "turtle", meaning a query nested in a query, is now just a "nested query".

Basic Declaration Form

The basic declaration form on alpha-malloy was evolving into

NAME is TYPE type-specific-value

The beta-malloy LookML-ish syntax would have one of two forms. For a single declaration like the one above it would simply be

TYPE: NAME type-specific-initializer

as in these statements ...

explore: flights is 'malloydata.faa.flights' { primary key: id }

join: things is thing_table on thing_id

primary key: id

group by: state, county

but will also allow groups of similar types of named objects to be declared with

TYPE: {
    NAME1 type-specific-initializer
    NAME2 type-specific-initializer
}

which will allow grouped blocks of dimensions or joins where that adds readability to a large model

Refinement gesture

Creation of explores in malloy is always a gesture of refinement based on existing object where the simplest object is a table. For example we want flights to mean the malloydta.faa.flights table ...

   explore: flights is 'malloydata.faa.flights' {
       primary key: id
       ... other flights stuff
   }

In beta-malloy any time you name a refineable object you can create an enhanced or extended version of the object simply by following the name with the {} enclosed set of additional properties.

As an example ... this ...

explore: carriers is 'malloydata.faa.carriers' { primary key: id }
explore: flights is 'malloydata.faa.flights' {
  join: carrier is carriers on carrier_id
}

... can be written without having to create an explore for carriers by writing the enhancement inline in the join ...

explore: flights is 'malloydata.faa.flights' {
  join: carrier is 'malloydata.faa.carriers' { primary key: id } on carrier_id
}

This is similar to how alpha-malloy works, except this syntax is more regular and less dependant on special case exceptions in the grammar.

Declaration vs. Invocation

Since an "explore" is not a query but a declaration it also contains declarations of dimensions and measures, which in this new syntax must be declared sperately

explore flights is 'malloydata.faa.flights' {
    dimension: long_flight is dep_time + 1 hour > arr_time
    measure: flight_count is count()
}

Farewell reduce

A query is either a grouping/aggregating gesture, which would look like this ...

query: by_carrier is {
    group by: carrier.nickname
    aggregate: flight_count
}

... or a detail-gesture like this ...

query: flight_details {
    project: carrier.nickname, flight_num, dep_time, arr_time
}

A group by or a project have a list of references, or new dimensions. An aggregate has a list of measures or new measure definitions. The alpha-malloy reduce query is a query which starts with group by: and a project query starts with project: but both are just queries.

Farewell |

The pipe symbols is removed from the language. In an explore definition, there can not be a chain of queries, there is only the declarations of filters, joins, dimensions, measures, and queries.

Filters are where: and having:

The magic : [] syntax for filters is gone. An explore or a project query can have a where: property, and an aggregating query can have a where: and a having:. The value is still a [] bracketed, comma seperated list of malloy expressions.

explore: flights_21st_century is flights {
  where: [ dep_time >= @2001 ]
}

Query syntax (the ->)

There is an operator, ->, which as a left hand side takes en explore and for the right hand side takes a query definition ...

    -- Invent a new query ... by running it ...
    flights->{
        group_by: carrier.nickname
        measure: flight_count
    }

    -- Invoke a query
    flights->by_carrier

    -- Invoke a refined query
    flights->by_carrier {
        aggregate: long_flights is flight_count { where: arr_time > dep_time + 1 hour }
    }

    -- Save redefined query as a top level item
    query: long_flights_by_carrier is flights->by_carrier {
        aggregate: long_flights is flight_count { where: arr_time > dep_time + 1 hour }
    }
    -- above will have a run button ...
    -- TBD: How I invoke a top level query in the scratch query
    -- space of a malloy file like the above invocations

{ where: ... shorthand }

Because filtering is ubiquitous, there is a shorthand for filtering. We expect authors of models to use where: and having: for clarity, but for people writing queries there is a shorthand which is as terse as the alpha-malloy : [ FILTER1, FILTER2 ] but which feels like a natural shortcut based on existing syntax in beta-malloy ...

-- long form
flights {
    where: [ dep_time: @2003 ]
}->by_carrier

-- one line version
flights { where: [ dep_time: @2003 ] }->by_carrier

-- short form
flights {? dep_time: @2003}->by_carrier

-- filtered measures
measure: pct_in_ca is (count() {? state: 'CA'}) / count()

What about turtles ...

Nested queries are declared in an explore with the query: keyword ...

explore: flights is 'malloydata.faa.flights' {
    ...
    query: by_carrier is {
        group by: carrier.nickname
        aggregate: flight_count
    }
}

And to include a nested query in a result set, much like the aggregate: keyword accepts existing names measures, or allows new measures, there is a nest: keyword to nest a query ...

explore: flights is 'malloydata.faa.flights' {
    ...
    query: airport_dashboard is {
      group by:
        code is destination_code,
        destination is destination.full_name
      aggregate:
        flight_count
      nest:
        carriers_by_month, routes_map, delay_by_hour_of_day
    }
}

Explore from Query

Because an explore can no longer have a | reduce in the definition, there needs to be a syntax meaning "the explore which starts with the result of this query". This would be indicated when, anywhere an explore name is legal, to use ( QUERY ) for example.

-- given this explore ...
explore: users is 'schema.users' {
    ...
    join: orders on orders.user_id
    query: user_order_facts is {
        group by: user_id
        aggregate:
            lifetime_value is sum(orders.value)
            orders.order_count
    }
}

-- ... make user->user_order_facts a top level entity for joins etc
-- but add new dimension
explore: user_order_facts is (users->user_order_facts) {
    dimension: super_user is lifetime_value > 1000
}

[christopherswenson]

I really like the direction this is taking us—particularly the more prominent labelling of the parts of a query, the explicit bracing of structures, and the riddance of space turtles.

Below I've written some ideas/concerns regarding this proposed syntax. In some cases I've provided possible alternatives, but I'm sure I haven't thought of all the relevant use cases, so take the suggestions themselves with a grain of salt.

Filter bracketing

Bracketing filters with [] feels out of place where almost everything else uses {}. It makes more sense to me to write flights { where: { dep_time: @2003 } }.

Types vs Properties

In this proposed syntax, there are two things which are fundamentally different but look very similar. One is definitions with types, e.g. explore: flights is ... and another is properties within a query, e.g. aggregate: total is count().

I see two possible concerns with this.

1. Overloading of :

Particularly with its usage in filters, : becomes slightly overloaded. Maybe an alternative would be to use :: for type specification in definitions, e.g. query :: airport_dashboard is ... or airport_dashboard :: query is ....

For property setting, it would be nice to be able to specify filters with no bracketing, especially if there's only one filter; however, where: dep_time: @2003 is very hard to read. Some other syntax for property setting might be appropriate, e.g. where <- { dep_time: @2003 }.

2. Confusion over dimension vs. group by, measure vs. aggregate, query vs. nest

One feature of the alpha syntax of Malloy is that it was very simple to move field definitions from an invoked query into an explore's refinement. For instance, you could take reduce full_name is concat(first_name, ' ', last_name), user_count is count() and easily move the definitions for full_name and user_count into the "model". With the syntax for the property specification very closely matching that of typed definitions, it almost looks like you should just be able to copy the contents of a query block into a refinement block, but you can't because the property names are different from the type names.

I propose we make the syntax for properties and definitions different, e.g.

users :: explore is 'users.table.thing' {
  primary key <- id

  gender :: dimension is gender_raw: 
    pick 'male' when 'M' 
    pick 'female' when 'F' 
    else 'nonbinary'

  by_gender :: query is { 
    group by <- gender
    aggregate <- user_count 
  }
}

Alternatively, we could get rid of group by, aggregate, and nest, and just use dimension, measure, and query, and keep the property setting syntax the same as the defintion syntax. This would preserve the ability to copy and paste definitions from a query into a refinement. This might probably means we would need a different way to distinguish a project from a reduce again somehow...

query by_full_name is { 
  dimension: full_name is concat(first_name, ' ', last_name)
  measure: user_count is count() 
}

Recognizable Right Hand Sides

As a general rule, I believe that right hand sides of definitions should be semantically recognizable as the type of thing being defined, whether or not the syntax requires a type to be specified.

In the proposed syntax, there a several instances where this is not the case.

In-Refinement Queries

For in-refinement queries, we have query: foo is { ... } (vs. query: foo is bar->{ ... } for standalone queries), because the bar is implied in the former, where it must be specified in the latter. I propose we add a keyword to refer to the in-scope explore, perhaps this, and require that the RHS of a query definition be a syntactically valid standalone query that happens to use the in-scope explore:

explore: flights is 'foo.bar.flights' {
  query: foo is this->{ ... }
}

Perhaps a valid syntax sugar for this could be query: foo is ->{ ... }, which feels less strange to me than a hanging block.

This syntax also supports correlated subqueries: query: bar is airports->{ ... }

Tables

Currently a table name is a valid RHS of an explore definition, but it is not semantically distinguishable from a string appearing elsewhere in Malloy code. I propose we add specific syntax to take a string table name and turn it into an explore. Perhaps this is some kind of macro/function, e.g. explore: flights is table('lookerdata.faa.flights').

Joins

I don't quite know how joins fit in to this picture, as they're similar to a definition (of a nested explore), but with some metadata specifying how the joined explore is related (the 'on' clause). Maybe we can think of some sensical way to consider these to be nested explore definitions, e.g. explore: flights is table('flights') { ... explore: carriers is (SOMETHING) }.

We should also consider other kinds of joins at this stage to make sure they fit in with the proposed syntax.

Explore from Query Syntax

Generally I think it's preferable for expressions to stand on their own, rather than requiring enclosing syntax to differentiate them in any way. Here in particular, I don't like the idea that parentheses would be required to differentiate between a table reference and a query definition. Ideally, the grammar for this kind of thing would look like

queryDefinition : QUERY COLON id IS exploreable OBRACE queryDefinitionInner CBRACE

where explorable could either be a query or table name or anything else explorable.
rather than

queryDefinition 
  : QUERY COLON id IS OPAREN query CPAREN OBRACE queryDefinitionInner CBRACE
  | QUERY COLON id IS tableName OBRACE queryDefinitionInner CBRACE

It also seems to me like the syntax in its current proposed form simply doesn't require the parentheses here for disambiguation. Would it not work to allow explore: user_order_facts is users->user_order_facts { ... }?

Arrow Syntax Style/Readability Concerns

I find flights {? dep_time: @2003}->by_carrier quite confusing to read, because my mind wants the arrow to tightly bind {? dep_time: @2003} and by_carrier (due to the lack of interpolated spaces).

This might suggest a recommended style of using spaces, e.g. flights {? dep_time: @2003} -> by_carrier. If we end up using this operator, I would definitely like to promote this as the default style, even for simple statements like flights -> by_carrier for this reason.

Alternatively, it might suggest that parentheses be required around LHS's that are not just identifiers, e.g. flights->by_carrier would be allowed to not have parentheses, but (flights {? dep_time: @2003})->by_carrier would require them.

Another alternative is to just return to the pipe | syntax, which doesn't have the same readability concern, as it naturally suggests a very low-precedence operation, and is more natural to type with spaces: flights {? dep_time: @2003} | by_carrier.

Window Functions

It may be worth considering how window functions work in Malloy at this point, to ensure that their syntax fits in with the new proposed syntax.

[mtoy-googly-moogly]

overloading of : might be confusing

The : is a the middle of a long conversation, it is an attempt to make the entire language more LookML/JSON familiar. In LookML, in filter expressions the left hand side of the : is a field name. In Malloy the left hand size of a : in a filter expression is any legal expression. It is true that in this proposal you need to understand the context of a sentence in order to understand what a : means.

One argument would be that : was ALWAYS misleading in a filter expression, because it implies that a filter expression is a key/value pair, and the correct fix is to use some other operator to mean "apply filter expression".

Another remedy would be to NOT use : in the statement syntax, and there is no need for that syntax if it interferes with readability.

explore: flights is 'project.schema.flights' {
  primary key: id

  join: carrier is carriers on carrier_id

  measure: flight_count is count() 

  query: by_carrier is {
    group_by: carrier.nickname

    aggregate: flight_count
  }
}

Without the :, looking more like alpha malloy ....

explore flights is 'project.schema.flights' {
  primary key id

  join carrier is carriers on carrier_id

  measure flight_count is count() 

  query by_carrier is {
    group_by carrier.nickname

    aggregate flight_count
  }
}

true confession, the : is optional right now in the grammar because while I agree that we need people to love our syntax more, I still love the freedom from punctuation and i wanted to at least experiment with no needing a colon. the model above parses just fine right now. I think the movement of the type to the left works better with : and it might make more sense to iterate more on syntax rather than go with the above syntax

I can think of reasons for both directions, this could use some discussion.

[christopherswenson]

Another point to consider is that currently we would require a single filter (where) to be bracketed ({ where: [ foo: @2003 ] ... }) because it would look too strange to have the mix of colons { where: foo: @2003 }. We could also remove the "apply" operator and just use = instead. { where: foo = @2003 } reads just fine.

[mtoy-googly-moogly]

yeah we talked about this with faces ... where: field: value is definitely a loser, but the most common where expressions will all have only one clause, so it maybe does make sense to give priority that that gesture when deciding where compromises get made

[mtoy-googly-moogly]

Discussion with @lloydtabb the leaning is towards replacing the match/apply operator with something else so that : is only for structuring syntax

[mtoy-googly-moogly]

differentiate "define these named things" statements from "give this property a value" statement, maybe propName <- value

I'm much more comfortable with removing the colon to make all these simply "statements" than to add a second separator like <-

[mtoy-googly-moogly]

@lloydtabb really likes : --- so this is a discussion that needs to happen

[mtoy-googly-moogly]

Bracketing filters with [] feels out of place where almost everything else uses {}.

Interesting. Is there a difference between lists of entities of the same type and collections of entities of multiple types and is it important to reflect those in the language?

Current

{
  -- commas are required between expressions currently
  where: [ expr1, expr2 ]
 -- because "is" statements are statements, commas are optional
  group by: {
      g3 is expr3
      g4 is expr4
  }
}

Square Bracket For All Lists

-- curly here because we are opening up an entity with properties
{
  -- keep square brackets for lists of same type ...
  where: [ expr1, expr2 ]
  -- maybe comma is not optional since it is a list ...
  group by: [
     g3 is expr3,
     g4 is expr4
   ]
}

Curlies for all bracketed expressions

{
  -- keep square brackets for lists of same type ...
  -- commas are not optional between these expressions, because the parser needs them
  where: { expr1, expr2 }
  
  group by: {
     g3 is expr3
     g4 is expr4
   }
}

I find "all curlies all the time" unpleasant, but I feel like each one of these has problems.

[mtoy-googly-moogly]

@lloydtabb is resonating with the idea that we use [] for lists of things and {} for defining or refining the properties of a thing

[mtoy-googly-moogly]

Confusion over dimension / group by, measure / aggregate, query / nest

This is complex ... group by is performing three functions, it is defining what KIND of query by the selection ofgroup by vs project AND it is a place to list some field names, AND it is a place to add dimensions. If you mess with group by, you have to mess with all three of those things. I feel like we need to push hard on this and see if it is learnable. This is something that existing users have asked for, and seem to like so far.

[mtoy-googly-moogly]

subquery declarations made more consistent with this-> or just ->

The query syntax is still fluid. A query is the following things.

• Explorable Space
• Filters /Having
• Dimensions / Measures
• Limits & Sorts

The current meta is that the -> is an operator binds an explorable to a query definition, that "binding" happens in the query invocation exploreName->subQueryName. In the current language, I feel the missing operator is more accurate and correct, however if we mess with queries at all, this would need to be revisited.

[mtoy-googly-moogly]

... and if we had some sort of explore: foo { query: otherQuery is otherExplore->{ .. } } for some reason, then this-> makes a lot of sense

[mtoy-googly-moogly]

Uncomfortable with strings magically being table names at certain locations in the file

True ... we could ...

1. ' always means strings " always means table names
2. use back quotes for table names
3. use table keyword

[mtoy-googly-moogly]

... I kind of like the bare string, but this might be because it is familiar

[mtoy-googly-moogly]

@lloydtabb wants a table keyword too, we'll do that.

[mtoy-googly-moogly]

I loved that tables were such an integral part of Malloy that they didn't even need syntax, they were like integers or strings. This will make me a little sad forever.

[mtoy-googly-moogly]

-> reads worse than |

The clam is, given ...

exploreName{ refineMent }->queryName

vs.

exploreName{ refineMent } | queryName

It is clearer in the second form that the | happens after the refinement. In the first form, some people might assume the -> operator happens before the refinement.

The nice thing about -> is that it makes it clearer that queryName would be found inside of exploreName

@christopherswenson suggestion that we fix this with style ... always surround the arrow with spaces

exploreName{ refineMent } -> queryName

Still thinking on this

[christopherswenson]

The nice thing about -> is that it makes it clearer that queryName would be found inside of exploreName

Very interesting point...

[mtoy-googly-moogly]

@lloydtabb and I are in the middle of a discussion about this ... he always wanted . as the separator but i did not want fights.by_carrier to be a query. -> was a middle ground that we can live with, but if the can of worms is open again I am not sure what happens next. a lot more thinking i guess.

[mtoy-googly-moogly]

when exploring a query, the () seems confusing/odd/unexpected

This one is up in the air a little. Let's say we need to refine a query and the output of the query ...

explore: a is b -> c

If I want to refine BOTH c ( the query ) and the output of the query, in the current proposal you would write ...

explore: a is (b -> c { refine_c_query }) { refine_c_output }

It was a last minute convenience handwave to say "I can tell the parser that in a place where is is expecting an exploreable, you can write ( query ). This may be brilliant or it may be a place we need to think more.

[christopherswenson]

How about:

1. All queries are always "explorable" and can therefore appear in an explore: definition
2. A refinement refines the thing immediately to the left with very high precedence b -> c { refinement } always refines c, not b -> c
3. You can wrap a query in parentheses to create a Query-in-Parentheses AST node
4. A refinement after a Query-in-Parentheses AST node refines the output of the query, not the query itself

This way, the grammar for an explore definition can literally be EXPLORE COLON id IS exploreOrQuery, and not need any kind of special case for refinements or parentheses.

[mtoy-googly-moogly]

I think you are probably right about ^^^

[christopherswenson]

One other point about the "recognizable right hand sides" comment. It would be really nice if we could have one consolidated grammar for definitions, e.g. definitionStatement : type COLON id IS definition, which would match all the different definition statements. This would be great because 1. it would result in a simpler grammar, 2. it would result in a more consistent language where right-hand-sides are always recognizable, 3. it makes type errors a semantic error rather than a bad parse.

I would love it if we could make this a goal/priority for this beta syntax.

[mtoy-googly-moogly]

primary_key instead of primary key --- as per discussion with @christopherswenson and @lloydtabb

[lloydtabb]

Working on converting the tests, I've noticed/wished for:

I would love to be able to put multiple statements on the same line. Perhaps semicolon could mean the same thing as new line or something?

query: flights ->{
  top: 5; order_by: 3 desc; having: origin_count > 5
  group_by:  destination.name
  aggregate: [
    flight_count
    total_distance
  ]
}

  

[mtoy-googly-moogly]

Newline is just a whitespace character ... you could write

query: flights ->{
  top: 5 order_by: 3 desc having: origin_count > 5
  group_by:  destination.name
  aggregate: [
    flight_count
    total_distance
  ]
}

[mtoy-googly-moogly]

This was malloy 3.0, I forgot we did this, closing.