This is a replica of a post on a Github issue as I was hoping to get some answers here:
I am using InfluxDB 1.7.1 and weirdly, I am able to use time filter with SHOW TAG KEYS/VALUES queries as this:
SHOW TAG KEYS ON monasca WHERE (_name = 'tempest-1100328991') AND (_tenant_id =
'7f8b19504b7646c898025fbc0a0e89e6' AND _region = 'RegionOne' AND time >= 1564148465s AND time < 1565012465s)
SHOW TAG VALUES ON monasca WITH KEY = name_1 WHERE (_tenant_id = '7f8b19504b764
6c898025fbc0a0e89e6' AND _region = 'RegionOne' AND time >= 1564148465s AND time < 1565012465s) AND (_tagKey = 'name_1')
However, the peculiarity is that the finest granularity is 1 day… I can’t filter tags on an hourly or a minutely basis… this makes sense from a performance perspective but I cannot find this behaviour documented anywhere. Would anyone be able to shed a light on this?
do you get an error when using a finer granularity ?
I can filter on on an hourly basis like this …
influx -version
InfluxDB shell version: 1.7.6
Enter an InfluxQL query
influx -execute "SHOW TAG KEYS ON mydb where time > now() -480h and time < now()-479h"
name: meas1
tagKey
------
tag1
do you get an error when using a finer granularity ?
No I do not get any errors when I go below 1d granularity.
Can you try executing this now and tell me what you get:
INSERT test,test_key=hello value=1
Now is your newly inserted key visible when you run this query immediately (for me, the answer is yes, which is not as expected if the hourly interval holds true):
SHOW TAG KEYS WHERE time > now()-2h AND time < now()-1h
How about when you run this query (for me, the answer is no, which is as expected):
SHOW TAG KEYS WHERE time > now()-2d AND time < now()-1d
I have played around with interval less than 1d but it looks like the boundary of the bucket starts at midnight and ends just before midnight of the following day.
the reason I could have a 1h precision is my shard duration ,
here is an example to explain …
the tags showed depend on the shard(s) covered by your given time frame in the where …
The minimum shard duration is 1h.
The time frame you specify is translated into a list of shards that contain datapoints
with a timestamp in the time window from your where clause.
influx will then show all the tag keys it finds in those shards.
thank you for this interesting question ,
I will add the example to the course I created …
> CREATE database mydb with duration 7d REPLICATION 1 SHARD DURATION
1h name RP7D_SHARD1H;
> show shards
name: mydb
id database retention_policy shard_group start_time end_time expiry_time owners
-- -------- ---------------- ----------- ---------- -------- ----------- ------
> precision h
> INSERT test,test_key=hello value=1
> select * from test
name: test
time test_key value
---- -------- -----
434820 hello 1
> INSERT test,test_key19_1=hello value=1 434819
> INSERT test,test_key19_2=hello value=1 434819
> INSERT test,test_key18=hello value=1 434818
> INSERT test,test_key17_1=hello value=1 434817
> INSERT test,test_key17_2=hello value=1 434817
> SHOW TAG KEYS ON mydb where time > now() -1h and time < now()
name: test
tagKey
------
test_key
test_key19_1
test_key19_2
> SHOW TAG KEYS ON mydb where time > now() -2h and time < now()-1h
name: test
tagKey
------
test_key18
test_key19_1
test_key19_2
> SHOW TAG KEYS ON mydb where time > now() -3h and time < now()-2h
name: test
tagKey
------
test_key17_1
test_key17_2
test_key18
> SHOW TAG KEYS ON mydb where time > now() -4h and time < now()-3h
name: test
tagKey
------
test_key17_1
test_key17_2
> SHOW TAG KEYS ON mydb where time > now() -5h and time < now()-4h
>
And to find the tag keys within a given shard you can specify the time boundaries
of the shard in the where clause :
> SELECT * FROM test WHERE time >= '2019-08-09T00:00:00Z'
and time < '2019-08-09T10:00:00Z'
name: test
time test_key17_1 test_key17_2 value
---- ------------ ------------ -----
434817 hello 1
434817 hello 1
> SHOW TAG KEYS ON mydb WHERE time >= '2019-08-09T00:00:00Z'
and time < '2019-08-09T10:00:00Z'
name: test
tagKey
------
test_key17_1
test_key17_2
