Overview

The user needs to retire or permanently delete data within a specific time range from an SLS/EDW instance. 

Solution

The atquery RETIRE command also allows specifying a time range by using the UPLOADS syntax as documented in the attached Administration Guide on pages 131 to 133. You can obtain the upload IDs by querying the uploads_info system table as follows:

SELECT uploadid, min_ts, max_ts from upload_info where original_tablename = '<namespace.table>';

For example:

SELECT uploadid, min_ts, max_ts from upload_info where original_tablename = 'test.syslog';


+------------------------------------------------------------------------------------------------------------------------+
| Results for SQL file >sql: SELECT uploadid, min_ts, max_ts from upload_info where original_tablename = 'test.syslog';< |
+--------------------------------+---------------------------+---------------------------+-------------------------------+
|            uploadid            |          min_ts           |          max_ts           |
|           (varchar)            |        (timestamp)        |        (timestamp)        |
+--------------------------------+---------------------------+---------------------------*
|292D6B904625F6569CFBE026483BDFB9|2001-08-26T11:02:00.000000Z|2001-09-26T23:51:25.000000Z|
|62CE403691A596E508A3905171825CF2|2001-08-26T11:02:00.000000Z|2001-09-26T23:51:25.000000Z|
|7C3522B409DE01D869503B2B281CC956|2001-08-26T11:02:00.000000Z|2001-09-26T23:51:25.000000Z|
|B31C5818123A869AC009F04F6F474A31|2001-08-26T11:02:00.000000Z|2001-09-26T23:51:25.000000Z|
|E14437B1557892AC421C002D627ED3BD|2001-08-26T11:02:00.000000Z|2001-09-26T23:51:25.000000Z|
|EC983D380A0B71C0E6ADF1EB98DAA987|2001-08-26T11:02:00.000000Z|2001-09-26T23:51:25.000000Z|
|F11B392F6BCBA4BD12E2607AFE01DB8C|2001-08-26T11:02:00.000000Z|2001-09-26T23:51:25.000000Z|
+--------------------------------+---------------------------+---------------------------+
Elapsed time: 1.5 sec   Number of rows: 7

You can filter the uploadIDs containing the desired time ranges to retire the data, and then perform the retire using the uploadIDs and the time range you would like to retire data from, as follows:

RETIRE FROM syslog UPLOADS '292D6B904625F6569CFBE026483BDFB9' DURING _time('AUG 26 00:00:00 2001'), _time('SEP 26 23:59:59 2001') FORCE