deleting data in tables

perton

Hello, I have installed the Vertica cluster. I created a database and two tables. I need to delete data in tables older than ten days automatically. I think that I must use PARTITION BY EXTRACT, but I can't understand how to do it

mosheg

Please consider the following example to keep data from last 7 days, and delete everything else before:

-- Create a partition key for each unique order_date day and divide the data into separate ROS containers accordingly.
CREATE TABLE public.my_table
(
    order_no int,
    order_date timestamp NOT NULL,
    shipper varchar(20),
    ship_date date
)
PARTITION BY DATE(order_date);

\set DEMO_ROWS 10

INSERT INTO public.my_table
WITH myrows AS (SELECT
row_number() over()    AS order_no,
NOW() - RANDOMINT(30) AS order_date
FROM ( SELECT 1 FROM ( SELECT NOW() as se union all
SELECT NOW() + :DEMO_ROWS - 1 AS se) a timeseries ts AS '1 day' OVER (ORDER BY se)) b)
SELECT order_no, order_date, 'something', order_date AS ship_date
FROM myrows
ORDER BY order_no;
COMMIT;

SELECT date(order_date),
       CASE WHEN DATE(order_date) <= DATE(now() -7) THEN 'Will be droped' ELSE 'Will keep' END as Status
FROM public.my_table ORDER BY 1;

--     date    |     Status
-- ------------+----------------
--  2020-04-23 | Will be droped
--  2020-04-24 | Will be droped
--  2020-04-28 | Will be droped
--  2020-04-30 | Will be droped
--  2020-05-03 | Will be droped
--  2020-05-10 | Will be droped
--  2020-05-10 | Will be droped
--  2020-05-12 | Will be droped
--  2020-05-14 | Will keep
--  2020-05-20 | Will keep
--  (10 rows)

-- The following DROP_PARTITIONS statement drops from table my_table all order records associated with partition key 10 days ago to 30 days ago.
SELECT DROP_PARTITIONS ('my_table', DATE(NOW()-365), DATE(now() -7));
--   DROP_PARTITIONS
-- -------------------
--  Partition dropped
-- (1 row)

SELECT date(order_date),
       CASE WHEN DATE(order_date) <= DATE(now() -7) THEN 'Will be droped' ELSE 'Will keep' END as Status
FROM public.my_table ORDER BY 1;
--     date    |  Status
-- ------------+-----------
--  2020-05-14 | Will keep
--  2020-05-20 | Will keep
-- (2 rows)

SELECT COUNT (partition_key) NumPartitions, ros_id, node_name, SUM(ROS_SIZE_BYTES) as ROS_SIZE_BYTES, SUM(ROS_ROW_COUNT) as ROS_ROW_COUNT
FROM PARTITIONS
WHERE projection_name ilike 'my_table%'
GROUP BY ros_id,node_name
ORDER BY ROS_SIZE_BYTES
LIMIT 20;
--  NumPartitions |      ros_id       |     node_name      | ROS_SIZE_BYTES | ROS_ROW_COUNT
-- ---------------+-------------------+--------------------+----------------+---------------
--              1 | 54043195528615460 | v_eon6vdb_node0003 |             99 |             1
--              1 | 54043195528615464 | v_eon6vdb_node0003 |             99 |             1
--  (2 rows)