deleting data in tables

maks

Hello, I have installed vertica cluster. I created database and 2 tables. I need to automatically delete data in tables older 10 days. I think that I must use PARTITION BY EXTRACT, but I can't understand how do it

mosheg

Consider the following example:

                CREATE TABLE public.my_table
            (
                order_no int,
                order_date timestamp NOT NULL,
                shipper varchar(20),
                ship_date date
            )
            PARTITION BY DATE(order_date);
            CREATE TABLE

        \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;

             OUTPUT
            --------
                 10
            (1 row)

            COMMIT;
            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)

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)

maks

How can I divide the table by days into partitions?

marcothesane

As ship_date is a DATE type - meaning one different value per possible day -

  ... PARTITION BY ship_date
 ;

will do.