Influxdb: SHOW MEASUREMENTS should group by retention policies

[beckettsean]

currently show measurements lists all measurements on a database but doesn't give the RP context. That can lead to confusion when select * from <measurement> fails to work because the measurement is not in the default RP.

[beckettsean]

Alternately show measurements could give the full path: db.rp.measurement for each measurement.

[jjfazio]

+1

[sbengo]

+1, related with #6789.

[jwilder]

7198 may remove the need for this functionality.

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[stale[bot]]

This issue has been automatically closed because it has not had recent activity. Please reopen if this issue is still important to you. Thank you for your contributions.

[mvadu]

Is this under consideration @pauldix ? I see all old contributors in your team have moved on, may be assign it to some one who is familiar with current codebase?

[ayang64]

@beckettsean , @timhallinflux, @dgnorton -- Exactly how would you like this output to look? And should this be a flag or additional command to preserve the original functionality?

[timhallinflux]

current output of show measurements on telegraf (as an example)

{"results":[{"statement_id":0,"series":[{"name":"measurements","columns":["name"],"values":[["cpu"],["cpu_load_short"],["disk"].... and so on

big concern is maintaining compatibility. Chronograf and Grafana both rely heavily upon the existing functionality.

I would suggest that issuing show measurements on <db> should return the measurements for the default retention policy only. I believe this is what happens today?

We could suggest that adding .* is the way to obtain all the measurements grouped by db.rp.

Example:
db: telegraf
rps: autogen, 5_min_mean

show measurements on telegraf.*
In this case, I would alter the output like this:

{"results":[{"statement_id":0,"series":[{"db.rp name":"telegraf.autogen","columns":["measurement names"],"values":[["cpu"],["cpu_load_short"],["disk"].... followed by {"db.rp name":"telegraf.5_min_mean","columns":["measurement names"],"values":[["cpu"],["cpu_load_short"],["disk"]....and so on

We could also suggest that specifying the rp would then narrow the results to measurements within that specific rp.
So, ultimately this should be the same way that we group other outputs.

[dgnorton]

I would suggest that issuing show measurements on should return the measurements for the default retention policy only. I believe this is what happens today?

Just tested to be sure and it returns measurements from all retention policies:

> insert into foo.autogen m v=1
> insert into foo.rp0 z v=2
> show measurements
name: measurements
name
----
m
z

> show measurements on foo
name: measurements
name
----
m
z

With the proposed show measurements on telegraf.* syntax, how would we write that in the case where no database is specified in the query? E.g.,

show measurements on .*
or
show measurements .*
etc.

We could also suggest that specifying the rp would then narrow the results to measurements within that specific rp.
So, ultimately this should be the same way that we group other outputs.

Maybe that would work and the .* syntax is only available when on <dbname> is specified:

show measurements on foo.autogen

on my example above would output:

name: measurements
name
----
foo.autogen.m

show measurements on foo.rp0



md5-aa8601cee09d8d4fc0291d90a5002f00



name: measurements
name
----
foo.rp0.z



md5-50d433a3ee2e21fbd582cabd46275b60



show measurements on foo.*



md5-aa8601cee09d8d4fc0291d90a5002f00



name: measurements
name
----
foo.autogen.m
foo.rp0.z

[timhallinflux]

In the first example, it looks like this is through the CLI, so you must have had to specify the database before you can run show measurements.

Otherwise you get:

ERR: database name required
Warning: It is possible this error is due to not setting a database.
Please set a database with the command "use <database>".

through the API
{"results":[{"statement_id":0,"error":"database name required"}]}

So, I believe database is required, generally speaking. Within the CLI, since you can use <db> this is supplied when you issue the show measurements command without the specified. But, I think this a convenience mechanisms introduced through the CLI itself to reduce typing.

I think I would disallow:
show measurements .*
and
show measurements on .*

I think we would allow:
show measurements on *.* -- all databases and all retention policies
show measurements on <db>.* -- specific database and all RPs
show measurements on <db>.<rp> -- specific database and specific RP
show measurements on <db> -- should continue to default to .* to preserve existing behavior (yes, this contradicts what I wrote earlier...), but if that is the way it works....let's preserve it.
show measurements -- is only valid within the CLI, after use <db> has been set. It should continue to default to .* to preserve existing behavior

[Esity]

@timhallinflux thanks for reconsidering this one

I agree with all your allow/disallow comments above. I think the same logic could be added to the API. I know our use case would utilize the API and not the CLI in this example

[dgnorton]

Tim, you're right. I was using the CLI and it passes along the database name in an URL query parameter, which is then passed along and filled in during query normalization (prior to execution).

I like the proposed syntax addition.

One clarification:

show measurements on -- should continue to default to .* to preserve existing behavior (yes, this contradicts what I wrote earlier...), but if that is the way it works....let's preserve it.

show measurements on <db> should continue to return all measurements from all RPs in the current format (not broken out by DB and RP)
show measurements on <db>.* will return all measurements from all RPs in the new format that (broken out by DB and RP)

Both old and new format shown in Tim's comment above: https://github.com/influxdata/influxdb/issues/3318#issuecomment-719733051