Table Retention Policies

the {{ocient}} system supports table retention policies, an automated way to manage and delete data after a specific time these policies help maintain storage efficiency on a schedule using ddl sql statements, you can modify individual tables to specify their own retention policies for how long the system should keep data before automatic removal requirements to add table retention policies, you must have alter and delete privileges for the system and the specific table in addition, a database table must meet these conditions to support retention policies have a designated {{timekey}} column (for details, see docid 7asr7i8qb0jlunvq9tzq ) be a user created table or system persistent table you cannot edit virtual system tables table retention properties table retention properties define the specific parameters that control how long data remains in a table before being automatically deleted these properties determine both the duration of retention and the granularity at which the system evaluates data for removal, ensuring that retention policies operate consistently and predictably by configuring these properties, you can fine tune how the system balances data availability with storage efficiency table retention and timekey retention policies of a table are directly linked to the column defined as the timekey of the table for each row in the table, the timekey column represents its age, which the system evaluates to determine whether to retain or delete the row table retention policies cannot function properly if the timekey column does not represent the age of each row additionally, the timekey bucket value also represents the frequency of evaluating the age of each row in the table for example, a table with a timekey bucket of 1 hour and a retention period of 1 day would evaluate each row every hour to determine if it is more than one day old based on the timekey bucket frequency for culling older records, the system can potentially retain a row for slightly longer than the set period of a retention policy, up to a maximum retention time of the sum of the retention period and the timekey bucket the table retention policy also deletes older rows of any dependent global dictionary compression tables defined on columns of the original table table retention tasks the system deletes older records as a distributed task, which is visible in the docid\ tbjvq0xtd tcxq17hm nc and docid\ tbjvq0xtd tcxq17hm nc catalog tables additionally, each table retention policy creates a scheduled process, which is visible in the docid\ tbjvq0xtd tcxq17hm nc system catalog table for details, see docid\ lp3hitukekekfy1vmalei retention and workload management retention policies execute periodically to delete data in two separate phases a truncate operation and a delete operation the truncate operation interfaces directly with the storage layer of the system and is not affected by any workload management configuration in contrast, the delete operation runs as a delete sql statement and uses a designated retention policy service class for retention policies this service class is a built in class that administrative users can edit all table retention policies use the same service class default retention policies a subset of persistent system tables has table retention policies set by default all retention policies in the system, including those set by default, are visible in the docid\ tbjvq0xtd tcxq17hm nc you can also view a retention policy for an individual table by executing an docid\ yhp4b1irv haf8f3df ww statement add retention policies to tables you can add table retention policies using a create table or alter table sql statement table retention policies are optional for all user defined tables if a table lacks a retention policy, the default behavior is to retain data in perpetuity retention policies follow this syntax as part of either a docid\ yhp4b1irv haf8f3df ww or an docid\ yhp4b1irv haf8f3df ww statement retention policy age retention granularity retention value parameter type description retention granularity numeric a number that represents the amount of time before the system deletes data in the table you must pair this number with a specific time type, represented by the retention value retention value string the unit of time that represents how long the table retains data supported values are \[ week, day, hour, minute, second, millisecond ] for example, 1 day schedules the table retention to a granularity of one day example this example creates a retention policy that keeps rows only if they are less than one day old, as specified by the last line, retention policy age 1 day the retention policy depends on the created at column because it is the timekey column the timekey bucket value (1, hour) determines how frequently the system checks for any rows to remove create table if not exists "transact data" ( "created at" timestamp time key bucket(1, hour) not null, "user id" int not null default 0, "sell id" bigint not null, "purchase amount" bigint not null default 0, "buyer name" varchar(1048576) clustering key "primary index" ("sell id", "user id") ) retention policy age 1 day related links docid\ yhp4b1irv haf8f3df ww docid\ veeoxedu opmz9xm0qqac