Google Data Fusion管道运行失败：MS SQL Server同步至BigQuery问题排查

问题 #

正在构建Google Data Fusion管道，将运行在GCP VM上的MS SQL Server 2016 Standard数据加载至BigQuery：

• 已通过公网IP正常连接SQL实例
• 使用Data Fusion Hub中的Microsoft SQL Server JDBC Driver v6.0.jre7作为源驱动
• BigQuery作为数据输出sink
• Data Fusion实例信息：
  • 版本类型：BASIC
  • 版本号：6.8.1（最新版）
  • 已禁用私有IP

管道运行失败，错误日志如下：

04/07/2023 0:42:40
INFO
Launch main class org.apache.spark.executor.YarnCoarseGrainedExecutorBackend.main([--driver-url, spark://CoarseGrainedScheduler@cdap-mypipe-98803685-d4b2-11ed-a81e-26b0641a83bd-w-1.us-central1-f.c.marine-fusion-270120.internal:35779, --executor-id, 1, --hostname, cdap-mypipe-98803685-d4b2-11ed-a81e-bd-w-0.us-central1-f.c.marine-fusion-2.internal, --cores, 1, --app-id, application_1680809963358_0002, --resourceProfileId, 0, --user-class-path, file:/hadoop/yarn/nm-local-dir/usercache/yarn/appcache/application_1680809963358_0002/container_1680809963358_0002_01_000002/__app__.jar])
04/07/2023 0:42:46
WARN
Cannot load filesystem: java.util.ServiceConfigurationError: org.apache.hadoop.fs.FileSystem: Provider org.apache.hadoop.hdfs.web.HftpFileSystem not found
04/07/2023 0:42:46
WARN
Cannot load filesystem: java.util.ServiceConfigurationError: org.apache.hadoop.fs.FileSystem: Provider org.apache.hadoop.hdfs.web.HsftpFileSystem not found
04/07/2023 0:42:48
ERROR
Aborting task
04/07/2023 0:42:48
ERROR
Task attempt_202304061942308162483986510619117_0003_r_000000_0 aborted.
04/07/2023 0:42:48
ERROR
Exception in task 0.0 in stage 0.0 (TID 0)
04/07/2023 0:42:48
WARN
Lost task 0.0 in stage 0.0 (TID 0) (cdap-mypipe-98803685-d4b2-11ed-a81e-26b0641a83bd-w-1.us-central1-f.c.marine-fusion-270120.internal executor 2): org.apache.spark.SparkException: Task failed while writing rows at org.apache.spark.internal.io.SparkHadoopWriter$.executeTask(SparkHadoopWriter.scala:162) at org.apache.spark.internal.io.SparkHadoopWriter$.$anonfun$write$1(SparkHadoopWriter.scala:88) at org.apache.spark.scheduler.ResultTask.runTask(ResultTask.scala:90) at org.apache.spark.scheduler.Task.run(Task.scala:131) at org.apache.spark.executor.Executor$TaskRunner.$anonfun$run$3(Executor.scala:505) at org.apache.spark.util.Utils$.tryWithSafeFinally(Utils.scala:1439) at org.apache.spark.executor.Executor$TaskRunner.run(Executor.scala:508) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:750) Caused by: org.apache.avro.file.DataFileWriter$AppendWriteException: org.apache.avro.UnresolvedUnionException: Not in union ["long","null"]: 1 at org.apache.avro.file.DataFileWriter.append(DataFileWriter.java:308) at io.cdap.plugin.gcp.bigquery.sink.AvroRecordWriter.write(AvroRecordWriter.java:90) at io.cdap.plugin.gcp.bigquery.sink.AvroRecordWriter.write(AvroRecordWriter.java:37) at io.cdap.plugin.gcp.bigquery.sink.BigQueryRecordWriter.write(BigQueryRecordWriter.java:58) at io.cdap.plugin.gcp.bigquery.sink.BigQueryRecordWriter.write(BigQueryRecordWriter.java:32) at io.cdap.cdap.etl.spark.io.TrackingRecordWriter.write(TrackingRecordWriter.java:41) at org.apache.spark.internal.io.HadoopMapReduceWriteConfigUtil.write(SparkHadoopWriter.scala:367) at org.apache.spark.internal.io.SparkHadoopWriter$.$anonfun$executeTask$1(SparkHadoopWriter.scala:137) at org.apache.spark.util.Utils$.tryWithSafeFinallyAndFailureCallbacks(Utils.scala:1473) at org.apache.spark.internal.io.SparkHadoopWriter$.executeTask(SparkHadoopWriter.scala:134) ... 9 more Caused by: org.apache.avro.UnresolvedUnionException: Not in union ["long","null"]: 1 at org.apache.avro.generic.GenericData.resolveUnion(GenericData.java:740) at org.apache.avro.generic.GenericDatumWriter.resolveUnion(GenericDatumWriter.java:205) at org.apache.avro.generic.GenericDatumWriter.writeWithoutConversion(GenericDatumWriter.java:123) at org.apache.avro.generic.GenericDatumWriter.write(GenericDatumWriter.java:75) at org.apache.avro.reflect.ReflectDatumWriter.write(ReflectDatumWriter.java:159) at org.apache.avro.generic.GenericDatumWriter.writeField(GenericDatumWriter.java:166) at org.apache.avro.specific.SpecificDatumWriter.writeField(SpecificDatumWriter.java:90) at org.apache.avro.reflect.ReflectDatumWriter.writeField(ReflectDatumWriter.java:191) at org.apache.avro.generic.GenericDatumWriter.writeRecord(GenericDatumWriter.java:156) at org.apache.avro.generic.GenericDatumWriter.writeWithoutConversion(GenericDatumWriter.java:118) at org.apache.avro.generic.GenericDatumWriter.write(GenericDatumWriter.java:75) at org.apache.avro.reflect.ReflectDatumWriter.write(ReflectDatumWriter.java:159) at org.apache.avro.generic.GenericDatumWriter.write(GenericDatumWriter.java:62) at org.apache.avro.file.DataFileWriter.append(DataFileWriter.java:302) ... 18 more
04/07/2023 0:42:49
ERROR
Aborting task
04/07/2023 0:42:49
ERROR
Task attempt_202304061942308162483986510619117_0003_r_000000_1 aborted.
04/07/2023 0:42:49
ERROR
Exception in task 0.1 in stage 0.0 (TID 1)
04/07/2023 0:42:51
WARN
Cannot load filesystem: java.util.ServiceConfigurationError: org.apache.hadoop.fs.FileSystem: Provider org.apache.hadoop.hdfs.web.HftpFileSystem not found
04/07/2023 0:42:51
WARN
Cannot load filesystem: java.util.ServiceConfigurationError: org.apache.hadoop.fs.FileSystem: Provider org.apache.hadoop.hdfs.web.HsftpFileSystem not found
04/07/2023 0:42:53
ERROR
Aborting task

故障排查与解决方案 #

核心故障定位 #

错误栈的根因是Avro类型不匹配：Not in union ["long","null"]: 1，说明某个字段定义的Avro类型为long或null的联合类型，但实际传入的数值是int类型的1，导致Avro解析失败，进而触发任务终止。

具体修复步骤 #

1. 校验源表与目标表的字段类型及空值配置

   • 排查MS SQL Server源表中对应字段的类型（如INT/SMALLINT），确认BigQuery目标表的字段类型是否匹配（MS SQL INT对应BigQuery INT64）。
   • 检查字段的nullable属性一致性：若源表字段允许为空，目标表需同步设置为允许为空；反之则需确保源表无空值或目标表字段设为非空。

2. 调整Data Fusion管道的类型转换配置

   • 在MS SQL Server源插件中，手动指定字段类型，避免自动推断偏差。例如将推断为int的字段强制指定为long，确保传递给BigQuery sink的数值类型符合预期。
   • 在BigQuery sink插件中，确认字段映射规则，禁用自动类型推断，手动绑定源字段与目标字段的类型对应关系。

3. 次要警告说明

   • 日志中的Cannot load filesystem属于环境依赖警告，在Data Fusion BASIC版本中无需处理，不会影响管道核心功能。

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