def test_plan_partition_changes_bespoke_names(self): planned = _plan_partition_changes( MockDatabase(), Table("table"), [mkPPart("p_start", 100), mkTailPart("p_future")], mkPos(50), datetime(2021, 1, 6, tzinfo=timezone.utc), timedelta(days=7), 2, ) self.assertEqual( planned, [ ChangePlannedPartition(mkPPart("p_start", 100)), ChangePlannedPartition(mkTailPart("p_future")).set_position([ 170 ]).set_timestamp(datetime(2021, 1, 8, tzinfo=timezone.utc)), NewPlannedPartition().set_columns(1).set_timestamp( datetime(2021, 1, 15, tzinfo=timezone.utc)), ], ) output = list( generate_sql_reorganize_partition_commands(Table("table"), planned)) self.assertEqual( output, [ "ALTER TABLE `table` REORGANIZE PARTITION `p_future` INTO " "(PARTITION `p_20210108` VALUES LESS THAN (170), " "PARTITION `p_20210115` VALUES LESS THAN MAXVALUE);" ], )
def test_plan_andgenerate_sql_reorganize_partition_commands_with_future_partition( self): planned = _plan_partition_changes( MockDatabase(), Table("table"), [ mkPPart("p_20201231", 100), mkPPart("p_20210104", 200), mkTailPart("future"), ], mkPos(50), datetime(2021, 1, 1, tzinfo=timezone.utc), timedelta(days=7), 2, ) self.assertEqual( list( generate_sql_reorganize_partition_commands( Table("water"), planned)), [ "ALTER TABLE `water` REORGANIZE PARTITION `future` INTO " "(PARTITION `p_20210105` VALUES LESS THAN MAXVALUE);", "ALTER TABLE `water` REORGANIZE PARTITION `p_20210104` INTO " "(PARTITION `p_20210102` VALUES LESS THAN (200));", ], )
def testgenerate_sql_reorganize_partition_commands_maintain_new_partition( self): self.assertEqual( list( generate_sql_reorganize_partition_commands( Table("table"), [ ChangePlannedPartition( mkTailPart("future")).set_position([ 800 ]).set_timestamp( datetime(2021, 1, 14, tzinfo=timezone.utc)), NewPlannedPartition().set_position( [1000]).set_timestamp( datetime(2021, 1, 16, tzinfo=timezone.utc)), NewPlannedPartition().set_position( [1200]).set_timestamp( datetime(2021, 1, 23, tzinfo=timezone.utc)), NewPlannedPartition().set_columns(1).set_timestamp( datetime(2021, 1, 30, tzinfo=timezone.utc)), ], )), [ "ALTER TABLE `table` REORGANIZE PARTITION `future` INTO " "(PARTITION `p_20210114` VALUES LESS THAN (800), " "PARTITION `p_20210116` VALUES LESS THAN (1000), " "PARTITION `p_20210123` VALUES LESS THAN (1200), " "PARTITION `p_20210130` VALUES LESS THAN MAXVALUE);" ], )
def testgenerate_sql_reorganize_partition_commands_no_change(self): self.assertEqual( list( generate_sql_reorganize_partition_commands( Table("table"), [ChangePlannedPartition(mkPPart("p_20210102", 200))])), [], )
def testgenerate_sql_reorganize_partition_commands_single_change(self): self.assertEqual( list( generate_sql_reorganize_partition_commands( Table("table"), [ ChangePlannedPartition(mkPPart( "p_20210102", 200, 200)).set_position( [542, 190]).set_timestamp( datetime(2021, 1, 16, tzinfo=timezone.utc)) ], )), [ "ALTER TABLE `table` REORGANIZE PARTITION `p_20210102` INTO " "(PARTITION `p_20210116` VALUES LESS THAN (542, 190));" ], )
def testgenerate_sql_reorganize_partition_commands_with_duplicate(self): with self.assertRaises(DuplicatePartitionException): list( generate_sql_reorganize_partition_commands( Table("table_with_duplicate"), [ ChangePlannedPartition( mkTailPart("future")).set_position([ 800 ]).set_timestamp( datetime(2021, 1, 14, tzinfo=timezone.utc)), NewPlannedPartition().set_position( [1000]).set_timestamp( datetime(2021, 1, 14, tzinfo=timezone.utc)), NewPlannedPartition().set_position( [1200]).set_timestamp( datetime(2021, 1, 15, tzinfo=timezone.utc)), ], ))
def test_plan_partition_changes_short_names(self): self.maxDiff = None planned = _plan_partition_changes( MockDatabase(), Table("table"), [ mkPPart("p_2019", 1912499867), mkPPart("p_2020", 8890030931), mkPPart("p_20210125", 12010339136), mkTailPart("p_future"), ], mkPos(10810339136), datetime(2021, 1, 30, tzinfo=timezone.utc), timedelta(days=7), 2, ) self.assertEqual( planned, [ ChangePlannedPartition(mkPPart( "p_20210125", 12010339136)).set_position([12010339136]), ChangePlannedPartition(mkTailPart("p_future")).set_position([ 12960433003 ]).set_timestamp(datetime(2021, 2, 1, tzinfo=timezone.utc)), NewPlannedPartition().set_columns(1).set_timestamp( datetime(2021, 2, 8, tzinfo=timezone.utc)), ], ) output = list( generate_sql_reorganize_partition_commands(Table("table"), planned)) self.assertEqual( output, [ "ALTER TABLE `table` REORGANIZE PARTITION `p_future` INTO " "(PARTITION `p_20210201` VALUES LESS THAN (12960433003), " "PARTITION `p_20210208` VALUES LESS THAN MAXVALUE);" ], )
def testgenerate_sql_reorganize_partition_commands_out_of_order(self): with self.assertRaises(AssertionError): list( generate_sql_reorganize_partition_commands( Table("table_with_out_of_order_changeset"), [ ChangePlannedPartition( mkTailPart("past")).set_position([ 800 ]).set_timestamp( datetime(2021, 1, 14, tzinfo=timezone.utc)), NewPlannedPartition().set_position( [1000]).set_timestamp( datetime(2021, 1, 15, tzinfo=timezone.utc)), ChangePlannedPartition( mkTailPart("future")).set_position([ 1200 ]).set_timestamp( datetime(2021, 1, 16, tzinfo=timezone.utc)), ], ))
def testgenerate_sql_reorganize_partition_commands_new_partitions(self): self.assertEqual( list( generate_sql_reorganize_partition_commands( Table("table"), [ ChangePlannedPartition(mkPPart("p_20210102", 200)), NewPlannedPartition().set_position( [542]).set_timestamp( datetime(2021, 1, 16, tzinfo=timezone.utc)), NewPlannedPartition().set_position( [662]).set_timestamp( datetime(2021, 1, 23, tzinfo=timezone.utc)), ], )), [ "ALTER TABLE `table` REORGANIZE PARTITION `p_20210102` INTO " "(PARTITION `p_20210102` VALUES LESS THAN (200), " "PARTITION `p_20210116` VALUES LESS THAN (542), " "PARTITION `p_20210123` VALUES LESS THAN (662));" ], )
def calculate_sql_alters_from_state_info(conf, in_fp): """ Using the config and the input yaml file-like object, return the SQL statements to bootstrap the tables in config that also have data in the input yaml as a dictionary of { Table -> list(SQL ALTER statements) } """ log = logging.getLogger("calculate_sql_alters") log.info("Reading prior state information") prior_data = yaml.safe_load(in_fp) time_delta = (conf.curtime - prior_data["time"]) / RATE_UNIT if time_delta <= 0: raise ValueError(f"Time delta is too small: {conf.curtime} - " f"{prior_data['time']} = {time_delta}") commands = dict() for table_name, prior_pos in prior_data["tables"].items(): table = None for t in conf.tables: if t.name == table_name: table = t if not table: log.info( f"Skipping {table_name} as it is not in the current config") continue map_data = _get_map_data_from_config(conf, table) current_positions = pm_tap.get_current_positions( conf.dbcmd, table, map_data["range_cols"]) columns = [r["Field"] for r in pm_tap.get_columns(conf.dbcmd, table)] ordered_current_pos = [ current_positions[name] for name in map_data["range_cols"] ] ordered_prior_pos = [ prior_pos[name] for name in map_data["range_cols"] ] delta_positions = list( map(operator.sub, ordered_current_pos, ordered_prior_pos)) rate_of_change = list( map(lambda pos: pos / time_delta, delta_positions)) max_val_part = map_data["partitions"][-1] if not isinstance(max_val_part, partitionmanager.types.MaxValuePartition): log.error(f"Expected a MaxValue partition, got {max_val_part}") raise Exception("Unexpected part?") log.info( f"{table}, {time_delta:0.1f} hours, {ordered_prior_pos} - {ordered_current_pos}, " f"{delta_positions} pos_change, {rate_of_change}/hour") part_duration = conf.partition_period if table.partition_period: part_duration = table.partition_period # Choose the times for each partition that we are configured to # construct, beginning in the near future (see MINIMUM_FUTURE_DELTA), # to provide a quick changeover into the new partition schema. time_offsets = _get_time_offsets(1 + conf.num_empty, MINIMUM_FUTURE_DELTA, part_duration) changes = _plan_partitions_for_time_offsets( conf.curtime, time_offsets, rate_of_change, ordered_current_pos, max_val_part, ) table_new = partitionmanager.types.Table( f"{table.name}_new_{conf.curtime:%Y%m%d}") alter_commands_iter = pm_tap.generate_sql_reorganize_partition_commands( table_new, changes) commands[table.name] = list( _generate_sql_copy_commands(table, map_data, columns, table_new, alter_commands_iter)) return commands