deleting data in tables

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

Answers

  • moshegmosheg Employee
    edited May 20

    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)
    
  • How can I divide the table by days into partitions?

  • marcothesanemarcothesane Employee

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

      ... PARTITION BY ship_date
     ;
    

    will do.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.