[系统运维]批量给分区表添加表组

一、需求：

需要将已经创建好的分区表加入到表组中，跨schema添加表组不支持，刚好最近需要迁移数据，计划在迁移数据前把相关表通过重建的方式加入到表组中。

二、实现步骤

1、创建相应的表组

create tablegroup TG_AAB001_64_PART  partition by hash partitions 64;
create tablegroup TG_AAC001_64_PART  partition by hash partitions 64;
create tablegroup TG_AAZ010_64_PART  partition by hash partitions 64;
create tablegroup TG_BAZ002_64_PART  partition by hash partitions 64;
create tablegroup TG_AAZ257_64_PART  partition by hash partitions 64;
create tablegroup TG_AAZ257_128_PART  partition by hash partitions 128;
create tablegroup TG_AAC996_64_PART  partition by hash partitions 64;

2、按schema.table_name.tableroup_name生成分区表清单

#cat part_table_list.txt 
SICPCENTER_EINP.AB10_OB_PT.TG_AAB001_64_PART
EIINFO.AB36_OBPT.TG_AAB001_64_PART
ESESTORE.AE98_OBPT.TG_AAZ010_64_PART
EORE.AE97_OBPT.TG_AAZ010_64_PART
ESICINFO.AE06_OBPT.TG_AAZ010_64_PART
LGBGYY.BE08_OB_PT.TG_BAZ002_64_PART

3、导入表结果并在partition by 关键词前加入表组信息

#cat back_partddl.sh 
#!/bin/sh
#****************************************************************#
# ScriptName: back_partdd.sh
# Author:xx
# Create Date: 2022-04-02 17:39
# Modify Author:xx
# Modify Date: 2022-04-02 17:39
# Function: 
#***************************************************************#
#define variables
v_db_name=''
v_table_name=''
v_dump_dir='/opt/dump/'`date +%Y%m%d`
v_tablegroup=''

v_new_table_name=''
v_full_name=''
v_tmp='_'


#mkdir dump dir
if [ ! -d  $dump_dir ] ;
then
        mkdir -p $v_dump_dir
fi
#backup ddl 
for line in `cat part_table_list.txt`
do
  v_db_name=`echo $line| awk -F '.' '{print $1}'` 
  v_table_name=`echo $line| awk -F '.' '{print $2}'` 
  v_tablegroup=`echo $line| awk -F '.' '{print $3}'` 
  mkdir -p $v_dump_dir/$v_tablegroup/$v_db_name/$v_table_name
 ./obdumper -h10.10.52.47  -usys  -tcqrsdev -D $v_db_name -P2883 -ccqtest -pAAli88@@1688  --ddl --table "$v_table_name" -f $v_dump_dir/$v_tablegroup/$v_db_name/$v_table_name --sys-user root --sys-password AAli88@@1688
done

#get sql file 

find $v_dump_dir/* |grep -E '*.sql' >parttable.sql

for line in `cat parttable.sql`
do
    v_tablegroup=`echo $line |awk -F '/' '{print $5}'`
    v_db_name=`echo $line |awk -F '/' '{print $6}'`
    v_table_name=`echo $line |awk -F '/' '{print $7}'`
    v_full_name="$v_db_name"."$v_table_name"
    sed -i 's/"//g' $line
    sed -i "/^CREATE TABLE/{s/$v_table_name/$v_full_name/g;}" $line
    sed -i "/^COMMENT/{s/$v_table_name/$v_full_name/g;}" $line
    sed -i "/PARTITION BY HASH/i tablegroup='$v_tablegroup'"  $line 
    sed -i "/^CREATE INDEX/{s/IDX/$v_db_name.IDX/g;s/$v_table_name (/$v_full_name (/g}" $line
done
# create execute scripts
#source xxx/IC12-schema.sql
sed -i 's/^/source /g' parttable.sql
sed -i 's/.sql/.sql; /g' parttable.sql

4、生成的可执行脚本如下

#cat parttable.sql 
source /opt/dump/202/TG_AAB001_64_PART/SCTIONCENTER_EINP/AB10_OB_PT/data/SCTIONCENTER_EINP/TABLE/AB10_OB_PT-schema.sql; 
source /opt/dump/202/TG_AAB001_64_PART/ENFO/AB36_OBPT/data/EICINFO/TABLE/AB36_OBPT-schema.sql; 
source /opt/dump/202/TG_AAZ010_64_PART/EORE/AE98_OBPT/data/EESTORE/TABLE/AE98_OBPT-schema.sql; 
source /opt/dump/202/TG_AAZ010_64_PART/EORE/AE97_OBPT/data/EESTORE/TABLE/AE97_OBPT-schema.sql; 
source /opt/dump/202/TG_AAZ010_64_PART/ENFO/AE06_OBPT/data/EICINFO/TABLE/AE06_OBPT-schema.sql; 
source /opt/dump/202/TG_BAZ002_64_PART/LGYY/BE08_OB_PT/data/LGYY/TABLE/BE08_OB_PT-schema.sql; 

5、通过obclient 执行parttable.sql 脚本

三、总结

1、对于已经创建好的表，不支持跨schema给表设置表组
2、生成的表创建脚本中，在表名前面添加了schema是为了在一个用户下执行脚本时，可以在对应的schema下创建相应的业务表
3、执行生成好parttable.sql脚本时，需确认脚本中的业务表已经删除