Delete data automatically every 10 minutes

Hi,

How can I tell omniscidb to delete data from a table every x minutes/seconds. Does omniscidb support trigger or any other way to implement this ?

Hi @dipanwita2019,

it’s not supported, and you aren’t going to recover any disk space doing that, but you can specify the maximum number of rows for a table; so if you know that each day you are going to have 1B of records, create the table with 1B value and add a timestamp filed you will use to filter data in the queries.

when the 1B value will be reached the system will reuse the older fragment in a circular way; don’t worry about the performances because omnisci db collect themix and max value for each fragment, so when you will ask for the last 10 minutes, only fragments containing that data will be read from the disk/used by the query.

Regards,
Candido

1 Like

Thank you for the reply. I see a property called max_rows which is used primarily for streaming datasets to limit the number of rows in a table. Can I get an example of a statement with the property max rows.

well,

quite simple; it’s a property that you have to specify into WITH CLAUSE of the CREATE TABLE statement.

so

CREATE TABLE test_max_rows AS
(f1 INT, f2 float…)
WITH (max_rows=1000000000, fragment_size=8000000)

changed the fragment_size to 8M to maximize the effect of computed statistics, so when 10 minutes of the table data asked, assuming 24H are around 1B of records, just 2 or 3 fragments processed for a total of 24M rows; using the default of 32M, you would probably process 64M of records or so.

are you on CPUs or GPU hardware? if you are on CPU using many cores or using multiple GPUs, probably you should shard the table (and change the max_rows parameter).

Candido

1 Like

Thank you. That is very helpful ! Currently I am on CPU hardware.

-Dipanwita.