Better SQL function help strings in query-builder UI

[paul-rogers]

Better SQL function help strings in query-builder UI

Affected Version

Version: 0.21

Description

Consider the "stock" Wikipedia data source and stock Docker Druid cluster. We want to write a query that uses some of the time functions. To see what is described here, you'll want to follow along in the UI.

Start with the following:

SELECT
  __time,
  channel,
  page
FROM "wikiticker-2015-09-12-sampled"

Now, let's round the time to an hour. What function do we use? There is no UI to tell us the available functions, we just have to look at the documentation. Many products do have a list of functions so we can stay in the query builder. So, that's the first UI feature request: list of functions, preferably grouped as in the documentation.

Let's say we find the FLOOR function in the documentation. We start to type:

SELECT
  __time,
 FLOOR
 ...

We get a choice list that includes the FLOOR function, along with this "help":

FLOOR
Syntax:
FLOOR(expr)

Description:
Floor.

So, the second UI request is that the help actually be helpful: at least give the function signature and summary from the documentation:

FLOOR
Syntax:
FLOOR(timestamp_expr TO <unit>)

Description:
Rounds down a timestamp, returning it as a new timestamp. Unit can be SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.

By contrast, if we'd typed TIME_CEIL, the help would have been filled in. So, it seems the help is spotty; sometimes you win, sometimes not.

Let's continue along. We type the opening paren, the UI fills in the closing one and places the caret in between, ready for us to type:

SELECT
  __time,
 FLOOR(|)
 ...

Now, what are the arguments? By now, the help (as weak as it was) is gone, we're left guessing. (Or, in reality, we flip back over to the documentation.)

Thus, the third UI request is to give guidance for the arguments. Three ways this is commonly done are:

• Expression builder: separate dialog to build up the call. Rather old-school.
• Phantom placeholders: when adding the close paren, show the function as FLOOR(<timestamp> TO <unit>) with the two <arg> elements as hints that get replaced as soon as the user starts typing. Great, but only works for new expressions.
• Tool-tip hover hint: hover the cursor over the function to get the help text from earlier. This works well all the time: new expressions and looking at existing queries.

[FrankChen021]

There is a dynamic function list giving you the candidate functions and corresponding docs when typing in the Web Console. The first 2 requests you mentioned are already met.

[paul-rogers]

@FrankChen021, thanks much for the explanation. Yes, I agree that the pop-up list exists: it was noted in the description. The key is that I have to know the function name ahead of time. For example FLOOR is commonly a function for converting floats to ints, so us newbies wouldn't expect it to be the name of a date-time function. Thus, some kind of UI tool to show functions grouped by category would help when we don't know the name.

Note also that this pop-up disappears once I type the opening paren. But, that is exactly the time when the parameters are needed. Type "time_" pick TIME_CEIL, type "(". Pop-up is gone. What are the parameters again? That is one of the issues described here.

Yes, a workaround is to start a new expression with the same function, see the parameter list again, and go back and fill in the parameters for the first function. Highly awkward, but I agree that it technically works.

Of course, I find it easier to keep a tab open with the SQL documentation rather than fuss with the UI. This request is to modify the UI so I don't need to have that doc. tab open.