CODEWITHSUNDEEP
partitioningverticadatabase-partitioning
Bastian
Bastian

Reputation: 129

Dropping Partitions in Vertica

I have a table like below in Vertica, 

Seq_No  CO_NO   DATE
1       PQ01    01-Sep-15
2       XY01    01-Oct-15
3       AB01    01-Nov-15
4       PQ02    01-Dec-15
.       .       .
.       .       .
.       .       .
14      XYZ9    01-Oct-16

And table has Partition by Month and Year based on the DATE column.

At any point of time there has to be only 13 partitions ie 13 months of data. If the current months data comes in(Oct-16) then we need to drop last years SEP month partition(SEP-15) by keeping only 13 partitions on the table(ie Latest 13 months of data).

How can we achieve this in Vertica?

Upvotes: 2

Views: 1789

Answers (2)

woot
woot

Reputation: 7616

I'm assuming your focus is on the "At any point of time" part of your question. One of two solutions, I guess.

  1. Add a script to your loading job that finds any partitions older than your threshold and drops them (look at the partitions system view, if you are trying to come up with a more generic approach you can extract the partition expression from the tables system view).

  2. Instead of having to be on top of the partition drops, you could just create a view around your table and use that instead to only show the past 1 year of data. Example: 

    create view myview
as
select * from mytable
where mydate >= current_timestamp - interval '1 year'

Or something similar, like trunc(current_timestamp - interval '1 year','MM'), etc. Then you can drop partitions at your leisure.

Upvotes: 0

Up_One
Up_One

Reputation: 5271

To do this use the drop partition procedure

SELECT DROP_PARTITION('schema.table',CAST(TO_CHAR(ADD_MONTHS(SYSDATE,-13),'YYYYMM') AS INTEGER));

What you need is cron job that will run every beginning of the month.

Before drop all partitions prior to 13 manually and them let the job do it`s work.

Note: your table must be partitioned like :

PARTITION BY (((date_part('year', Datecol) * 100) + date_part('month', Datecol)))
  • test the drop partition before using it, create a dummy table and run it.

Upvotes: 1

Related Questions