【阿里云DMS-数据导出】JAVA-快速实现创建SQL集工单进行导出

ZuanWu

已于 2024-12-24 18:01:23 修改

阅读量1k

点赞数 23

文章标签：阿里云 java sql 数据库 database

于 2024-12-24 17:57:17 首次发布

本文链接：https://blog.csdn.net/ZuanWu/article/details/144690315

版权

抛去常用的easyExcel、easyPoi等，另一种数据导出实现方案：
使用阿里云DMS数据导出 创建SQL结果集导出工单
实现下载列表的功能，类似下图：
在这里插入图片描述
导出后的文件长这样，目前没找到修改名称的方法，提交了工单也没有解决。导出的文件为压缩包 ↓↓↓
解压后是一个csv或者xlsx文件，根据执行导出时设置的
↓↓↓
我使用的两种格式
1.CSV格式
导出的文本格式样式无法自定义，工单沟通后无果，感觉自由度比较低
2.XLSX格式
在这里插入图片描述
导出文件类型根据下面的字段区分

导出的内容根据你执行的sql决定，不支持自定义表头跟样式。

导出步骤：阿里云接口文档调试地址

创建SQL结果集导出工单(CreateDataExportOrder)
提交工单审批(SubmitOrderApproval)，此步骤仅在安全协同模式需要，此模式无法自动审批,只能手动审批工单，才能进行下一步操作。
获取数据库导出工单详情(GetDatabaseExportOrderDetail)
执行数据导出工单(ExecuteDataExport)
获取数据导出结果附件下载地址(GetDataExportDownloadURL)

1. 创建SQL结果集导出工单(CreateDataExportOrder)

直接速成->安装SDK->复制代码CreateDataExportOrder

其中有个参数比较重要
.dbId，你的数据库id，这个填错会导致导不到数据，导出到别的环境的数据，谨慎填写（建议用只读库实例）。
我获取的方式：
第一步：登录DMS控制中心录入你的数据库

第二部：打开阿里云调试获取数据库详细信息

导出的服务region，自行参考DMS数据管理区域配置

其中部分字段我没有细扣，可以参考我的方式再进行优化

@Data
public class ExportDataRequest {

    private Long affectRows = 10000000L; // 影响的行数
    private String classify; // 分类
    private Long instanceId; // 实例 ID
    private Long dbId; // 数据库 ID
    private Boolean logic = false; // 逻辑标志
    private Boolean ignoreAffectRows = false; // 忽略影响的行数标志
    private String exeSQL; // 执行的 SQL 语句

    private Byte clientType; // 客户端类型:1-管理端,2-商户端
    private Byte orderType; // 导出订单类型:1-支付当日,2-支付历史,3-代付当日,4-代付历史,5-资金明细
    private String infoNo; // 用户编号
}

@Slf4j
@Component
public class AliYunDms {

    private static final Logger LOGGER = LoggerFactory.getLogger(AliYunDms.class);


    @Value("${ailyn.dms.accessKeyId}")
    private String accessKeyId;
    @Value("${ailyn.dms.accessKeySecret}")
    private String accessKeySecret;
    @Value("${ailyn.dms.region}")
    private String region;
    @Value("${ailyn.dms.endpointOverride}")
    private String endpointOverride;
    @Value("${ailyn.dms.dbId}")
    private Long dbId;

    // 获取DMS客户端的方法
    private  AsyncClient getAcsClientForMail() {
        AsyncClient client = null;
        try {
            StaticCredentialProvider provider = StaticCredentialProvider.create(Credential.builder()
                    .accessKeyId(accessKeyId)
                    .accessKeySecret(accessKeySecret)
                    .build());

            client = AsyncClient.builder()
                    .region(region) // Region ID
                    .credentialsProvider(provider)
                    .overrideConfiguration(
                            ClientOverrideConfiguration.create()
                                    .setEndpointOverride(endpointOverride)
                    )
                    .build();
        } catch (Exception e) {
            LOGGER.info("获取DMS客户端异常:" + e.getMessage());
        }
        return client;
    }

    /**
     * 创建SQL结果集导出工单
     * @param request
     * @return
     */
    public Long createDataExportOrder(ExportDataRequest request) {
        Long orderId = null;
        AsyncClient client = null;
        long startTime = System.currentTimeMillis(); // 记录开始时间

        try {
            // 记录入参
            LOGGER.info("Entering createDataExportOrder with request: {}", new Gson().toJson(request));

            // 获取DMS客户端
            client = getAcsClientForMail();

            CreateDataExportOrderRequest.PluginParam pluginParam = CreateDataExportOrderRequest.PluginParam.builder()
                    .affectRows(request.getAffectRows())
                    .classify(request.getClassify())
                    .instanceId(request.getInstanceId())
                    .dbId(request.getDbId())
                    .logic(request.getLogic())
                    .ignoreAffectRows(request.getIgnoreAffectRows())
                    .exeSQL(request.getExeSQL())
                    .build();
            CreateDataExportOrderRequest createDataExportOrderRequest = CreateDataExportOrderRequest.builder()
                    .comment("订单导出")
                    .pluginParam(pluginParam)
                    .build();

            CompletableFuture<CreateDataExportOrderResponse> response = client.createDataExportOrder(createDataExportOrderRequest);
            CreateDataExportOrderResponse resp = response.get();
            LOGGER.info("Response received: {}", new Gson().toJson(resp));

            List<Long> orderIds = resp.getBody().getCreateOrderResult().getCreateOrderResult();
            orderId = orderIds.get(0);

        } catch (Exception e) {
            LOGGER.error("Error occurred while creating data export order: {}", e.getMessage(), e);
        } finally {
            if (client != null) {
                client.close();
            }
            long endTime = System.currentTimeMillis(); // 记录结束时间
            LOGGER.info("Exiting createDataExportOrder with orderId: {}, execution time: {} ms", orderId, (endTime - startTime));
        }
        return orderId;
    }
}

2. 提交工单审批(SubmitOrderApproval)

阿里云的数据导出支持三种模式，具体差异或收费查看官方文档数据库导出

自由操作（免费，默认），每日有导出限额，自由操作模式下，我们创建工单后是自动审批，不需要人为再进行审批操作，可直接进行下一步。
稳定变更，没用过
安全协同（收费），无导出上限，如果数据量较大就按照这个来，在第一步生成工单后，工单会处于待审批状态，需要手动触发审批。

安全协同手动审批接口SubmitOrderApproval
参数OrderId取的第一步创建工单的响应

审批代码，这几个方法我都写一起了，client等参数就用第一步的就行

/**
     * 提交工单审批
     *
     * @param orderId
     * @return
     */
    public Boolean SubmitOrderApproval(Long orderId) {
        AsyncClient client = null;
        long startTime = System.currentTimeMillis(); // 记录开始时间
        Boolean br = Boolean.FALSE;
        try {
            // 记录入参
            LOGGER.info("SubmitOrderApproval with orderId: {}", orderId);

            client = getAcsClientForMail();

            SubmitOrderApprovalRequest submitOrderApprovalRequest = SubmitOrderApprovalRequest.builder()
                    .orderId(orderId)
                    .build();

            CompletableFuture<SubmitOrderApprovalResponse> response = client.submitOrderApproval(submitOrderApprovalRequest);
            SubmitOrderApprovalResponse resp = response.get();
            System.out.println(new Gson().toJson(resp));
            LOGGER.info("SubmitOrderApproval response received: {}", new Gson().toJson(resp));
            br = resp.getBody().getSuccess();
        } catch (Exception e) {
            LOGGER.error("Abnormal submission of approval work order : {}", e.getMessage(), e);
        } finally {
            if (client != null) {
                client.close();
            }
            long endTime = System.currentTimeMillis(); // 记录结束时间
            LOGGER.info("SubmitOrderApproval with orderId: {}, execution time: {} ms", orderId, (endTime - startTime));
        }
        return br;
    }

3. 获取数据库导出工单详情(GetDatabaseExportOrderDetail)

审批通过后没有直接执行导出会有几率查询不到工单，我用的是Java异步的方式，可能存在延迟。
所以先查工单详情GetDatabaseExportOrderDetail判断是否可以执行导出，根据以下statusDesc来判断工单状态。

可以参考我的状态判断（里面包括一些我的业务代码尝试补充审批、补充执行导出，可以忽略）
状态介绍：
工单已完成。：导出完成，可以进行最后一步获取下载链接
正在执行导出…：文件还在导出
工单初始化中：还未进行审批的工单
工单审批完成！：审批通过，但还未执行导出
导出失败xxx：明确导出失败，报错原因就在这四个字后面

                    // 查询工单状态
                    String detail = exportService.getDatabaseExportOrderDetail(exportOrderRecord.getOrderId())
                            .getBody().getDatabaseExportOrderDetail().getStatusDesc();
                    if ("工单已完成。".equals(detail)) {
                        log.info("订单{},工单已完成", exportOrderRecord.getOrderId());
                        exportOrderRecord.setCompleteStatus((byte) 2);
                        exportOrderRecordService.updateById(exportOrderRecord);
                    } else if ("正在执行导出...".equals(detail)) {
                        log.info("订单{},正在执行导出", exportOrderRecord.getOrderId());
                        // 文件大 正在导出 正常
                    } else if ("工单初始化中".equals(detail)) {
                        // 工单初始化中,没有自动审批,需要手动执行审批
                        log.info("订单{},工单初始化中,补充审批开始", exportOrderRecord.getOrderId());
                        Boolean approval = exportService.SubmitOrderApproval(exportOrderRecord.getOrderId());
                        if (approval) {
                            exportService.executeDataExport(exportOrderRecord.getOrderId());
                        }
                    } else if ("工单审批完成！".equals(detail)) {
                        // 审批完成,但是没直接执行导出 走到这里是有问题的
                        // 执行导出 - 补偿机制
                        log.info("订单{},工单审批完成，未执行导出，补充导出执行", exportOrderRecord.getOrderId());
                        exportService.executeDataExport(exportOrderRecord.getOrderId());
                    } else if (detail.startsWith("导出失败")) {
                        // 明确导出失败  导出异常
                        log.info("订单{},导出失败, detail:{}", exportOrderRecord.getOrderId(), detail);
                        exportOrderRecord.setCompleteStatus((byte) 3);
                        exportOrderRecord.setReason(detail);
                        exportOrderRecordService.updateById(exportOrderRecord);
                    }

/**
     * 获取数据库导出工单详情
     * @param orderId
     * @return
     */
    public GetDatabaseExportOrderDetailResponse getDatabaseExportOrderDetail(Long orderId) {
        GetDatabaseExportOrderDetailResponse resp = null;
        AsyncClient client = null;
        long startTime = System.currentTimeMillis(); // 记录开始时间

        try {
            // 记录入参
            LOGGER.info("Entering getDatabaseExportOrderDetail with orderId: {}", orderId);

            // 获取DMS客户端
            client = getAcsClientForMail();

            GetDatabaseExportOrderDetailRequest getDatabaseExportOrderDetailRequest = GetDatabaseExportOrderDetailRequest.builder()
                    .orderId(orderId)
                    .build();

            CompletableFuture<GetDatabaseExportOrderDetailResponse> response = client.getDatabaseExportOrderDetail(getDatabaseExportOrderDetailRequest);
            resp = response.get();
            LOGGER.info("Export order detail response received: {}", new Gson().toJson(resp));

        } catch (Exception e) {
            LOGGER.error("Error occurred while getting database export order detail: {}", e.getMessage(), e);
        } finally {
            if (client != null) {
                client.close();
            }
            long endTime = System.currentTimeMillis(); // 记录结束时间
            LOGGER.info("Exiting getDatabaseExportOrderDetail with orderId: {}, execution time: {} ms", orderId, (endTime - startTime));
        }
        return resp;
    }

4. 执行数据导出工单(ExecuteDataExport)

经过以上操作，确认工单状态为 ‘工单审批完成！’ 再执行ExecuteDataExport进行导出。
参数：
“mode”: “FAST” -执行会稍微快一些，无法终止/停掉任务
“fileType”: “CSV” -上面讲到我使用的两种CSV或XLSX，两者区别不大都可以用excel打开，样式存在差异且不可调整

代码参考，我使用的安全协同，在发起一个导出工单后，我一般会尝试三次提交工单审批，状态拿到 ‘工单审批完成！’ 再执行导出，存在延时性，可参考以下重试方法(不放心再搞个定时任务)。

    /**
     * 循环执行导出  等待自动审批完成 60 秒间隔 三次尝试
     *
     * @param orderId
     * @return
     */
    public Boolean executeDataExportFor(Long orderId) {
        int maxAttempts = 3;
        int attempt = 0;
        boolean exportSuccess = false;

        while (attempt < maxAttempts) {
            attempt++;
            // 提交工单审批
            Boolean approve = aliYunDms.SubmitOrderApproval(orderId);
            if (approve) {
                GetDatabaseExportOrderDetailResponse databaseExportOrderDetail = getDatabaseExportOrderDetail(orderId);

                if (isWorkflowCompleted(databaseExportOrderDetail)) {
                    exportSuccess = aliYunDms.executeDataExport(orderId);
                }

                if (exportSuccess) {
                    return true;
                }
            }
            // 等待 60 秒再重试 三次
            try {
                Thread.sleep(60 * 1000);
            } catch (InterruptedException e) {
                Thread.currentThread().interrupt(); // 恢复中断状态
                throw new RuntimeException("Thread was interrupted", e);
            }
        }
        log.info("已达到尝试三次提交工单审批 , 异常[orderId:" + orderId + "]");
        System.out.println("，已达到最大尝试次数。");
        return false;
    }

/**
     * 执行数据导出工单
     *
     * @param orderId
     * @return
     */
    public Boolean executeDataExport(Long orderId) {
        AsyncClient client = null;
        Boolean result = false;
        long startTime = System.currentTimeMillis(); // 记录开始时间

        try {
            // 记录入参
            LOGGER.info("Entering executeDataExport with orderId: {}", orderId);

            // 获取DMS客户端
            client = getAcsClientForMail();

            ExecuteDataExportRequest executeDataExportRequest = ExecuteDataExportRequest.builder()
                    .orderId(orderId)
                    .actionDetail(CommonUtil.buildMap(
                            new TeaPair("encoding", "UTF8"),
                            new TeaPair("fileType", "CSV"),
                            new TeaPair("mode", "FAST") // 快速导出,不可中断
                    ))
                    .build();

            CompletableFuture<ExecuteDataExportResponse> response = client.executeDataExport(executeDataExportRequest);
            ExecuteDataExportResponse resp = response.get();
            LOGGER.info("Export response received: {}", new Gson().toJson(resp));

            result = resp.getBody().getSuccess();

        } catch (Exception e) {
            LOGGER.error("Error occurred during data export: {}", e.getMessage(), e);
        } finally {
            if (client != null) {
                client.close();
            }
            long endTime = System.currentTimeMillis(); // 记录结束时间
            LOGGER.info("Exiting executeDataExport with orderId: {}, success: {}, execution time: {} ms", orderId, result, (endTime - startTime));
        }
        return result;
    }

5. 获取数据导出结果附件下载地址(GetDataExportDownloadURL)

根据步骤三查询工单详情，明确获取工单状态为工单已完成。 则可执行GetDataExportDownloadURL获取下载链接。

代码参考

/**
     * 获取数据导出结果附件下载地址
     *
     * @param orderId
     * @return
     */
    public String getDataExportDownloadURL(Long orderId) {
        AsyncClient client = null;
        String str = "";
        long startTime = System.currentTimeMillis(); // 记录开始时间

        try {
            // 记录入参
            LOGGER.info("Entering getDataExportDownloadURL with orderId: {}", orderId);

            // 获取DMS客户端
            client = getAcsClientForMail();

            GetDataExportDownloadURLRequest getDataExportDownloadURLRequest = GetDataExportDownloadURLRequest.builder()
                    .orderId(orderId)
                    .build();

            CompletableFuture<GetDataExportDownloadURLResponse> response = client.getDataExportDownloadURL(getDataExportDownloadURLRequest);
            GetDataExportDownloadURLResponse resp = response.get();

            if (resp.getBody().getDownloadURLResult().getHasResult()) {
                str = resp.getBody().getDownloadURLResult().getURL();
            } else {
                str = resp.getBody().getDownloadURLResult().getTipMessage();
            }

            LOGGER.info("Download URL response received: {}", new Gson().toJson(resp));

        } catch (Exception e) {
            LOGGER.error("Error occurred while getting data export download URL: {}", e.getMessage(), e);
        } finally {
            if (client != null) {
                client.close();
            }
            long endTime = System.currentTimeMillis(); // 记录结束时间
            LOGGER.info("Exiting getDataExportDownloadURL with orderId: {}, execution time: {} ms", orderId, (endTime - startTime));
        }
        return str;
    }

至此，导出流程结束，以上代码仅供参考，大家有更好的实现方案或者哪里有问题都可以留言沟通。

总结：

阿里云DMS数据导出还是蛮不错的，基本可以满足我的业务需求，数据超过百万以后导出也会需要十几二十分钟以上，频次不高的情况下，几百万数据慢慢导出也是支持的，如果考虑速度的话还我这种方案好像不太合适，不过正常使用下来还是没有问题的，可以从业务逻辑上限制导出的数量，比如只让查询三天的数据等。
默认导出的文件存放在OSS，可以更换自己的OSS，来统一管理文件数据。
优点：
异步执行，调接口就行，导出任务在阿里云，可以在阿里云DMS数据管理平台拿着你的工单号查你导出的详情信息，查问题比较方便，能看到你执行的sql，还有执行失败的原因，和操作日志详情。
提交工单，客服老师响应蛮快的，基本能解决的问题都会给你快速解决，可以要求电话沟通，不知道安全协同怎么配置的同学可以直接提工单，态度还不错。

缺点：
导出的文件不支持自定义文件名称，不支持导出表头样式设置，数据导出的字段类型也无法指定，如果对字段有强烈要求样式或者字段类型要求的可以可以看看我下面的方案能否解决再考虑是否要换方案。

在这里插入图片描述