1

If I create a very common query snippet. I want use it like variable.
For example:
I create a snippet, named dau_metrics

select dau_d, uid, level 
from profile.users 
where today()-30 <= d

And I use the snippet like that:

select count(*) cnt, uid 
from work_exp 
inner join (${dau_metrics}) using uid group by uid

Use variable instead of just copy the snippet code here.

The good part is:

  1. if I change dau_metrics, all the query depend on dau_metrics will be changed on the execute runtime
  2. if I join many tables, make the SQL looks more short and clear

The bad part is:

  1. maybe hard implement
  2. debug the final generated long SQL maybe difficult
1 Like
2

Supporting Dynamic Snippets is something I wanted to implement for a long time. If indeed issue #2 (debugging the final generated SQL) will be hard, we can help with that by making it possible to see the “rendered query” with the snippets embedded.

In terms of actual implementation, it makes sense to use the templating engine we use for parameters. Currently it’s Mustache, it’s somewhat limited but has support for partials, which we can use to implement this.

Partials look like this in the template {{>dau_metrics}}. The Engine itself has support for passing an object that defines how to load the partials, and I even have an experiment implementation for this (consists of 7 lines of code, but needs more work to be “production grade”).

The downsides I can see with such implementation:

  1. No support for insertion points (see here), which regular snippets support. In your example, you might want the number of days to be something the user can pass to the snippet.
  2. The syntax is not very welcoming, but that’s not a huge thing.
  3. It’s makes it harder for us to migrate away from Mustache if we would like to implement more sophisticated features in the future (like Optional Parameters).
3

Thx for reply

  1. the user can pass to the snippet

for this, can I write something like this today()-{{day_range}} <= d in the snippet ?

  1. It’s makes it harder for us to migrate away from Mustache

for this, I dont have any good idea, it’s something like compile the user code get a result and compile the result code again, get the final SQL code.

dau_metrics=select dau_d, uid, level from profile.users where today()-{{day_range}} <= d

👇

select * from ${dau_metric}

👇engine compile or provide a [generate] button on the GUI

select * from (select dau_d, uid, level from profile.users where today()-{{day_range}} <= d)

👇user typed 30

select * from (select dau_d, uid, level from profile.users where today()-30 <= d)
4

You could solve the de-coupling issue by writing your own functions to do this. Python and JS both have decent parameter substitution syntax (f strings in Python3 and string literal templates in JS). The template language gives you input safety – but this can still be handled with custom code.

Personally, I think it makes sense to use Mustache. And just figure the migration overhead into the cost / benefit analysis when switching template languages in the future.

In my experience, migrating this kind of functionality from one platform to another is pretty straightforward as long as you have a solid suite of unit tests.

5

Periscope Data does it with what I would describe as “function syntax”. You define a query snippet with a function signature very similar to a programming language:

snippet_name(param1, param2, param3)

And then use it in your query by wrapping in square brackets:

SELECT * FROM events WHERE [snippet_name('a', 'b', 'c')]

It’s quite functional. But unless you have really descriptive query snippet names it can make queries hard to reason about.