使用Qlik Replicate从OCI同步至Databricks Delta Lake数据异常求助

问题概述 #

使用Qlik Replicate将Oracle Cloud Infrastructure（OCI）中的Oracle数据同步至Databricks Delta Lake表时，出现数据重复和数据缺失异常。Qlik Replicate日志及Databricks查询历史均显示错误，已发现网络相关问题，但无法定位具体根因，需明确缓解方案。

关键错误明细 #

Databricks侧错误 #

• 查询无法调度：HTTP响应码500：集群资源不足、负载过高或服务临时故障导致查询无法被调度。
• [DELTA_CONCURRENT_APPEND] ConcurrentAppendException：Delta Lake表被并发更新，触发写入冲突。冲突提交详情：

  {"timestamp":1722250928325,"userId":"5954939804701401","userName":"aaaaaaa@xyz.com","operation":"MERGE","operationParameters":{"predicate":["((ITEM#22092922 = seg1_repcol#22092878) AND (LOC#22092925 = seg2_repcol#22092879))"],"matchedPredicates":[{"actionType":"update"}],"statsOnLoad":false,"notMatchedBySourcePredicates":[],"notMatchedPredicates":[]},"readVersion":2917,"isolationLevel":"WriteSerializable","isBlindAppend":false,"operationMetrics":{"numTargetRowsCopied":"0","numTargetRowsDeleted":"0","numTargetFilesAdded":"1","numTargetBytesAdded":"1282377","numTargetBytesRemoved":"1005294","numTargetDeletionVectorsAdded":"158","numTargetRowsMatchedUpdated":"28916","executionTimeMs":"209330","numTargetRowsInserted":"0","numTargetRowsMatchedDeleted":"0","numTargetDeletionVectorsUpdated":"158","scanTimeMs":"83197","numTargetRowsUpdated":"28916","numOutputRows":"28916","numTargetDeletionVectorsRemoved":"158","numTargetRowsNotMatchedBySourceUpdated":"0","numTargetChangeFilesAdded":"0","numSourceRows":"29161","numTargetFilesRemoved":"1","numTargetRowsNotMatchedBySourceDeleted":"0","rewriteTimeMs":"84773"},"tags":{"noRowsCopied":"true","delta.rowTracking.preserved":"false","restoresDeletedRows":"false"},"engineInfo":"Databricks-Runtime/15.2.x-photon-scala2.12","txnId":"eb912069-75fd-4f33-9829-a9cb191b4b7e"}
  

Qlik Replicate侧错误 #

• 503响应无Retry-After头：

  02728323: 2024-07-11T00:50:57 [TARGET_LOAD     ]E:  RetCode: SQL_ERROR  SqlState: 08S01 NativeError: 124 Message: [Simba][Hardy] (124) A 503 response was returned but no Retry-After header was provided. Original error: Unknown [1022502]  (ar_odbc_stmt.c:5090)
  

• SSL连接重置：

  02602439: 2024-07-28T18:12:49 [TARGET_APPLY    ]T:  RetCode: SQL_ERROR  SqlState: 08S01 NativeError: 115 Message: [Simba][Hardy] (115) Connection failed with error: SSL_read: Connection reset by peer [1022502]  (ar_odbc_stmt.c:4737)
  02602439: 2024-07-28T18:12:49 [TARGET_APPLY    ]T:  Network error encountered  (ar_odbc_util.c:1242)
  

• 语句执行失败（retcode -1）：

  04171826: 2024-07-29T14:43:45 [TARGET_APPLY    ]T:  Failed (retcode -1) to execute statement
  

排查与缓解步骤 #

1. 解决Delta Lake并发写入冲突 #

• 调整Qlik Replicate重试策略：将自动重试间隔设置为30秒以上，避免短时间内重复发起MERGE操作触发冲突。
• 锁定表写入权限：确保仅Qlik Replicate任务拥有该Delta Lake表的写入权限，排除其他任务或手动操作的干扰。
• 优化Delta Lake MERGE操作：在Databricks执行以下命令降低冲突概率：

  SET spark.databricks.delta.merge.enableLowShuffle = true;
  

  同时检查表的Delta协议版本，确保使用最新版本（通过DESCRIBE EXTENDED <table_name>查看）。

2. 修复网络连接问题 #

• 排查网络链路：用ping、traceroute测试Qlik服务器与Databricks集群的连通性，检查防火墙、VPN是否稳定，排除数据包丢失或延迟过高问题。
• 更新Simba ODBC驱动：升级至Databricks官方推荐的匹配Runtime 15.2.x的最新驱动版本，修复已知的连接重置、503响应处理bug。
• 配置Qlik网络重试：在任务目标端设置5-10次网络错误重试，并启用指数退避间隔，避免临时波动导致同步失败。

3. 定位数据不一致根因 #

• 开启Qlik详细日志：启用目标端DEBUG级日志，记录每条写入语句的执行结果，包括成功/失败行数，定位异常批次。
• 校验源端与目标端数据：选取主键、更新时间等关键字段，定期执行全量或增量校验，明确重复/缺失数据的范围和原因。
• 检查Databricks集群资源：查看CPU、内存、磁盘使用率，若资源不足导致调度失败，需扩容节点或调整任务并发度、批处理大小。

4. 临时恢复数据一致性 #

• 回滚Delta表版本：使用时间旅行功能恢复到冲突前的版本（版本号取自冲突提交的readVersion字段）：

  RESTORE TABLE <table_name> TO VERSION AS OF 2917;
  

• 补全缺失数据：触发Qlik Replicate的全量同步或增量补同步，同步前确保集群资源充足、网络稳定。

内容的提问来源于stack exchange，提问作者Shikha