StarRocks新老版本数据迁移方案

1、使用StarRocks的EXPORT 导出数据

        使用该模式进行导出时，数据类型要严格要求，如：int类型数据不能为NULL，不然数据导入不进去到新StarRocks集群中。列数不够导致报错

导出代码为：

EXPORT TABLE user.user_profix_info
PARTITION ("p20230315")
TO "hdfs://nnmm:8020//starrocks/backup/dt=2023-03-15/single_group_" 
PROPERTIES
(
    "column_separator"=",",
    "load_mem_limit"="2147483648",
    "timeout" = "3600"
)
WITH BROKER "broker"
(
    "username" = "root",
    "password" = "******"
);

如果不能严格使用数据类型，这个方式不建议使用,导入不进去数据，你会很蛋疼。(主键模型不支持)

EXPORT TABLE dm.dm_users 
PARTITION ("******")
TO "hdfs://nnmm:8020//starrocks/backup/dt=2023-03-15/dm_users_" 
PROPERTIES
(
    "column_separator"=",",
    "load_mem_limit"="2147483648",
    "timeout" = "3600"
)
WITH BROKER "broker"
(
    "username" = "root",
    "password" = "******"
);

查看导出的状态：来判断导出是否正常

SELECT LAST_QUERY_ID() ;
SHOW EXPORT WHERE queryid = "d2bd5c5f-de92-11ed-8fd5-00163e378862";

2、使用StarRocks的备份于还原方式

这种模式，如果两个StarRocks版本差异比较大，也是支持不是很好，弄不进去,此路还是不通。

1、创建仓库

在两个StarRocks集群中，都创建这个仓库

CREATE READ ONLY REPOSITORY dm
WITH BROKER broker
ON LOCATION "hdfs://hadoop-01:8020/starrocks/backup/dm"
PROPERTIES(
    "username" = "root",
    "password" = "******"
);



自建：
CREATE READ ONLY REPOSITORY dm
WITH BROKER broker
ON LOCATION "hdfs://hadoop-01:8020/starrocks/backup/dm"
PROPERTIES(
    "username" = "root",
    "password" = "******"
);

2、备份表

BACKUP SNAPSHOT dm.dm_users
TO dm
ON (dm_users)
PROPERTIES ("type" = "full");

2.1、查看备份状态

SHOW BACKUP;
SHOW SNAPSHOT ON dm;

3、恢复表

RESTORE SNAPSHOT dm.dm_users
FROM dm
ON (dm_users)
PROPERTIES (
    "backup_timestamp"="2023-04-13-13-58-24-246",
    "replication_num" = "1"
);

3.1、查看恢复状态

SHOW RESTORE;

4、小结

以上两种方法在版本差异不大的情况可以实现，其余不行哦