Skip to content

Instantly share code, notes, and snippets.

@CodMonk

CodMonk/CodeMonk_Part#Man.sql

Created November 12, 2015 10:39
Show Gist options
  • Save CodMonk/4b89294bbb48eb1edb31 to your computer and use it in GitHub Desktop.
Save CodMonk/4b89294bbb48eb1edb31 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. CodMonk created this gist Nov 12, 2015.
    175 changes: 175 additions & 0 deletions CodeMonk_Part#Man.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,175 @@
    CREATE DEFINER=`root`@`localhost` PROCEDURE `Cmonk_partition_manager`(in partition_frequency varchar(100), in db_schema varchar(100),in input_table_name varchar(100), in partition_column varchar(100))
    BEGIN
    -- Author - Code Monk
    -- Version - 1.0
    -- Procedure for automated partitioning of table
    -- Inputs :

    -- 1- Partition_frequency : Options-(Daily,Monthly,Weekly)
    -- 2- db_schema : Name of Database schema
    -- 3- input_table_name : Table Name
    -- 4- partition_column : Table column


    -- Limitations:
    -- 1- Only range partitioning is supported.
    -- 2- Partition column type should be Datetime

    -- Variables to set:
    -- no_of_partitions: number of partitions after last partition.

    -- Eg: If current date ='2015-11-07' ,frequency ="daily" and no_of_partitions=4
    -- then it will also create partitions for next coming 4 days from current_date,
    -- which means last date of partition will be "2015-11-11".

    -- Note: For Automatic partitioning, Schedule it using MySQL Event.



    declare partitions_count int;
    declare last_partition_value datetime;
    declare partition_query text;
    declare no_of_partitions int;
    declare first_day_of_current_month datetime;
    declare partition_name varchar(256);
    declare partition_start_date date;
    declare partition_last_date date;
    declare partition_names text;
    declare partition_date date;
    declare temp_current_partition_count int;

    -- First day of current month. Eg: '2015-11-01'
    set first_day_of_current_month=date(date_sub(now(),interval dayofmonth(now())-1 day));
    set no_of_partitions=2;
    set partition_names=' ';

    -- Increase group concat maximum length.
    set @@group_concat_max_len = 100000;

    -- Get last partition detail of input table
    drop temporary table if exists temp_partition_information;
    create temporary table temp_partition_information
    select table_name, PARTITION_NAME,PARTITION_DESCRIPTION
    from information_schema.PARTITIONS
    where TABLE_SCHEMA=db_schema and table_name=input_table_name
    and PARTITION_DESCRIPTION is not null
    order by PARTITION_DESCRIPTION desc limit 1;


    set temp_current_partition_count=(select count(*) from temp_partition_information);

    -- If currently there is no partitions exists for table
    if temp_current_partition_count=0
    then
    -- Get minimum value of partition column
    set @temp_query=concat('set @min_value_of_partition=(','select ',partition_column,' from ',db_schema,'.',input_table_name,' order by cast(',partition_column,' as datetime) asc limit 1',')');

    select @temp_query;
    prepare sql_statement from @temp_query;
    execute sql_statement;
    deallocate prepare sql_statement;

    -- if there is no record in table
    if @min_value_of_partition is null
    then
    -- set minimum value current system date
    set @min_value_of_partition=current_date;
    end if;

    else
    -- Get partition descripion of last partition created on table
    set @min_value_of_partition= (select from_days(partition_description) from temp_partition_information);
    end if;

    select @min_value_of_partition;

    -- Calaculate start and last date of partitions based on the frequency
    case when partition_frequency='daily'
    then
    select @min_value_of_partition;
    set partition_start_date=case when temp_current_partition_count>0
    then
    date_ADD(date(@min_value_of_partition), interval 1 day)
    ELSE date_sub(date(@min_value_of_partition), interval 1 day) END;

    set partition_last_date=date_add(date(current_date), interval no_of_partitions+1 day);
    when partition_frequency ='weekly'
    then
    set partition_start_date=date_sub(date_add(date(@min_value_of_partition), interval 6-(WEEKDAY(date(@min_value_of_partition))) day),interval 7 day);
    set partition_last_date=date_add(date_add(date(current_date), interval 6-(WEEKDAY(date(current_date))) day),interval no_of_partitions*7 day);
    when partition_frequency='monthly'
    then
    set partition_start_date=case when temp_current_partition_count>0
    then
    date_Add(date_sub(date(@min_value_of_partition), interval dayofmonth(@min_value_of_partition)-1 day),interval 1 month)
    else
    date_sub(date(@min_value_of_partition), interval dayofmonth(@min_value_of_partition)-1 day)
    end ;
    set partition_last_date=date_add(date(first_day_of_current_month), interval no_of_partitions+1 month);
    end case;


    set partition_date=partition_start_date;
    select partition_start_date,partition_last_date,partition_date;

    -- Temporary table to hold the dynamically created partition details
    drop temporary table if exists temp_partition_name;
    create temporary table temp_partition_name
    (
    partition_list varchar(256)

    );

    -- Loop create partition query for each partition
    loop_to_make_partition_query:REPEAT

    set partition_name=concat('p',year(partition_date),
    if( length(month(partition_date))=1,concat('0',month(partition_date)),month(partition_date)),
    if( length(day(partition_date))=1,concat('0',day(partition_date)),day(partition_date) )
    );
    -- Prepare partition values and store it to the temp table
    insert into temp_partition_name values( concat('PARTITION ', partition_name,' VALUES LESS THAN (','to_days(','''',partition_date,'''',')',')'));

    -- Calaculate next partition date based on the frequency of partitioning
    case when partition_frequency='daily'
    then
    set partition_date=date_Add(partition_date, interval 1 day);
    when partition_frequency ='weekly'
    then
    set partition_date=date_Add(partition_date, interval 7 day);
    when partition_frequency='monthly'
    then
    set partition_date=date_Add(partition_date, interval 1 month);
    end case;


    -- set partition_date=date_Add(partition_date, interval 1 month);
    -- Prepare partition query till it reach to the last partition date
    until partition_last_date<=partition_date
    END REPEAT loop_to_make_partition_query;

    -- select * from temp_partition_name;
    -- After collecting all partition details in the temporary table. concat all partition query in comma seperated format
    select group_concat(partition_list) into partition_names from temp_partition_name;

    -- Preparing final partition query and store it to the variable "partition_query"
    if temp_current_partition_count=0
    then
    set partition_query=concat('alter table ',db_schema,'.',input_table_name,' partition by range(to_days(',partition_column,'))',
    '(',partition_names,');');
    else
    set partition_query=concat('alter table ',db_schema,'.',input_table_name,' add partition(',partition_names,');');
    end if;

    select partition_query ;
    select partition_query into @partition_query;
    SELECT * FROM temp_partition_name;

    IF(partition_last_date>=partition_date)
    THEN
    -- Execute query to add new partitions to table
    prepare stmt from @partition_query;
    execute stmt;
    deallocate prepare stmt;
    END IF;
    END