CODEWITHSUNDEEP
influxdbretentioninfluxql
Rajan
Rajan

Reputation: 426

Is it possible to execute a delete query on the default retention policy in influxdb?

Influx database named "metrics" with two retention policy and assign one as default out of the two.

CREATE RETENTION POLICY basic ON "metrics" DURATION 2h  REPLICATION 1 SHARD DURATION 6m DEFAULT
CREATE RETENTION POLICY downsample ON "metrics" DURATION 60d  REPLICATION 1 SHARD DURATION 2h

There is also bunch of continuous queries runs at every 10 mins and downsample the data to write influx downsample retention policy tier. The entries available at the basic retention policy tier got cleared every 2h and downsample retention policy clears after 60 days.

Continuous query sample looks like this

CREATE CONTINUOUS QUERY "cq_reads" ON "metrics" BEGIN SELECT sum(reads) as reads INTO downsample.stats_io FROM "metrics".basic.stats_io GROUP BY time(10m),* END

metrics is the database name and stats_io is the measurement here.

I have memory constraint of specified GB allowed for influx in my product. At some point influx started to fail in scaling to hold 60 days data.

I am trying to clear oldest 10 mins data in basic retention policy after hitting my memory limits. The corresponding downsampled oldest 10 mins data are still need to be available at downsample retention policy.

Following command deletes last ten mins data in both basic and downsample retention policies.

delete stats_io where time>1609457374835790623 and time < 1609457374835790623 + 10m

I don't want to delete the downsampled data available at downsample retention policy. So tried to delete basic retention policy alone.

delete basic.stats_io where time>1609457374835790623 and time < 1609457374835790623 + 10m

unfortunately this query ends up in error and doesn't allow to delete based on retention policies.

ERR: error parsing query: found basic, expected FROM, WHERE at line 1, char 8

However select query works properly with specific retention policy. But delete doesn't work as the same way. Example: select query

select count(*) from basic.stats_io // Properly get the count for the measurement with specified retention policy

I am wondering why the delete doesn't support retention policy based deletes. Is it any other approach or queries exist to clear the default retention policy data alone ?

Any suggestions are much appreciated. Thanks!

Upvotes: 1

Views: 1004

Answers (1)

Richard
Richard

Reputation: 3100

I think you need to use the syntax delete FROM

delete FROM basic.stats_io where time>1609457374835790623 and time < 1609457374835790623 + 10m

More info here in DELETE documentation

Upvotes: 0

Related Questions