DirectRunner正常但DataflowRunner无法从Dataflow上传数据至MS SQL

问题场景 #

• 采用Dataflow弹性环境，将数据从BigQuery上传至Microsoft SQL Server，已在自定义Docker镜像中安装pyodbc和msodbcsql18
• Docker容器交互式会话中，使用DirectRunner运行脚本完全正常：可读取BigQuery数据、完成转换并上传至SQL Server
• 使用DataflowRunner在交互式会话运行同一脚本，或把镜像/模板部署到GCP项目时，出现驱动文件找不到的错误

错误信息 #

Traceback (most recent call last):
  File "/usr/local/lib/python3.7/site-packages/apache_beam/runners/worker/sdk_worker.py", line 267, in _execute
    response = task()
  File "/usr/local/lib/python3.7/site-packages/apache_beam/runners/worker/sdk_worker.py", line 340, in <lambda> 
    lambda: self.create_worker().do_instruction(request), request)
  File "/usr/local/lib/python3.7/site-packages/apache_beam/runners/worker/sdk_worker.py", line 581, in do_instruction
    getattr(request, request_type), request.instruction_id)
  File "/usr/local/lib/python3.7/site-packages/apache_beam/runners/worker/sdk_worker.py", line 618, in process_bundle
    bundle_processor.process_bundle(instruction_id))
  File "/usr/local/lib/python3.7/site-packages/apache_beam/runners/worker/bundle_processor.py", line 996, in process_bundle
    element.data)
  File "/usr/local/lib/python3.7/site-packages/apache_beam/runners/worker/bundle_processor.py", line 221, in process_encoded
    self.output(decoded_value)
  File "apache_beam/runners/worker/operations.py", line 346, in apache_beam.runners.worker.operations.Operation.output
  File "apache_beam/runners/worker/operations.py", line 348, in apache_beam.runners.worker.operations.Operation.output
  File "apache_beam/runners/worker/operations.py", line 215, in apache_beam.runners.worker.operations.SingletonConsumerSet.receive
  File "apache_beam/runners/worker/operations.py", line 707, in apache_beam.runners.worker.operations.DoOperation.process
  File "apache_beam/runners/worker/operations.py", line 708, in apache_beam.runners.worker.operations.DoOperation.process
  File "apache_beam/runners/common.py", line 1200, in apache_beam.runners.common.DoFnRunner.process
  File "apache_beam/runners/common.py", line 1281, in apache_beam.runners.common.DoFnRunner._reraise_augmented
  File "apache_beam/runners/common.py", line 1198, in apache_beam.runners.common.DoFnRunner.process
  File "apache_beam/runners/common.py", line 718, in apache_beam.runners.common.PerWindowInvoker.invoke_process
  File "apache_beam/runners/common.py", line 843, in apache_beam.runners.common.PerWindowInvoker._invoke_process_per_window
  File "/usr/local/lib/python3.7/site-packages/mssql.py", line 30, in process
    connection = mssql_client.connect(host, database, userid, password)
  File "/usr/local/lib/python3.7/site-packages/mssql_client.py", line 5, in connect
    connection = pyodbc.connect(connection_string)
pyodbc.Error: ("01000 [while running 'Upload to SQL server in 10 threads-ptransform-22']", "[01000] [unixODBC][Driver Manager]Can't open lib '/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.1.so.1.1' : file not found (0) (SQLDriverConnect)")

相关代码文件 #

Dockerfile #

FROM gcr.io/dataflow-templates-base/python3-template-launcher-base:20220418_RC00

ARG WORKDIR=/dataflow/template
RUN mkdir -p ${WORKDIR}
WORKDIR ${WORKDIR}

COPY transfer.py .
COPY mssql_client.py .

ENV FLEX_TEMPLATE_PYTHON_PY_FILE="${WORKDIR}/transfer.py"

RUN apt-get update -y
RUN apt-get install -y git

RUN pip install -U apache-beam==2.39.0
RUN pip install -U pyodbc==4.0.34

# Install dependency for apt-key
RUN apt-get install -y gnupg2 
RUN curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
# Install Microsoft SQL ODBC (dependency for pyodbc)
RUN curl https://packages.microsoft.com/config/debian/11/prod.list > /etc/apt/sources.list.d/mssql-release.list
RUN apt-get update -y
RUN ACCEPT_EULA=Y apt-get install -y msodbcsql18

mssql_client.py #

# File mssql_client.py
import pyodbc

def connect(host, database, userid, password):
    connection_string = 'DRIVER={/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.1.so.1.1};SERVER='+host+';DATABASE='+database+';UID='+userid+';PWD='+password
    connection = pyodbc.connect(connection_string)
    return connection

setup.py #

# File setup.py 

import setuptools

setuptools.setup (
   name='setup',
   version='1.0.0',
   install_requires=[ 'pyodbc' ],
   packages=setuptools.find_packages(),
)

排查与解决方案 #

1. 确保Dataflow Worker使用自定义镜像 #

核心问题大概率是Dataflow作业未正确关联你构建的自定义镜像，默认使用了官方基础Worker镜像（未安装msodbcsql18）。解决步骤：

• 构建镜像后必须推送到GCP的容器仓库（GCR或Artifact Registry）：

  docker build -t gcr.io/[你的GCP项目ID]/dataflow-mssql-template:latest .
  docker push gcr.io/[你的GCP项目ID]/dataflow-mssql-template:latest
  

• 提交Dataflow作业时，明确指定Worker镜像参数：

  python transfer.py \
    --runner DataflowRunner \
    --project [你的GCP项目ID] \
    --region [你的区域，如us-central1] \
    --worker_harness_container_image gcr.io/[你的GCP项目ID]/dataflow-mssql-template:latest \
    --staging_location gs://[你的存储桶]/staging \
    --temp_location gs://[你的存储桶]/temp \
    ...其他业务参数...
  

• 若使用弹性模板，创建模板时需将镜像信息配置到模板元数据中，确保作业启动时加载自定义镜像。

2. 修正ODBC驱动配置 #

• 避免硬编码驱动绝对路径，改用驱动名称{ODBC Driver 18 for SQL Server}，系统会通过odbcinst.ini自动查找正确路径，修改mssql_client.py：

  def connect(host, database, userid, password):
      connection_string = 'DRIVER={ODBC Driver 18 for SQL Server};SERVER='+host+';DATABASE='+database+';UID='+userid+';PWD='+password
      connection = pyodbc.connect(connection_string)
      return connection
  

• 在Dockerfile中添加unixODBC依赖（msodbcsql18依赖此组件，之前未安装）：

  RUN apt-get install -y unixodbc unixodbc-dev
  

• 构建镜像后，可进入容器验证驱动是否注册成功：

  docker run -it gcr.io/[你的GCP项目ID]/dataflow-mssql-template:latest bash
  odbcinst -j
  

  确认输出中包含ODBC Driver 18 for SQL Server的配置信息。

3. 修复依赖安装问题 #

• 不要在setup.py的install_requires中添加msodbcsql18，这是系统级Debian包，无法通过pip安装，会导致作业无限挂起，保留原install_requires=[ 'pyodbc' ]即可。
• 确保Dockerfile中ACCEPT_EULA=Y参数正确传递，避免msodbcsql18安装中断，可在构建时添加--progress=plain查看安装日志：

  docker build -t gcr.io/[你的GCP项目ID]/dataflow-mssql-template:latest --progress=plain .
  

4. 额外配置建议 #

• 若SQL Server要求SSL连接，需在连接字符串中添加SSL参数：

  connection_string = 'DRIVER={ODBC Driver 18 for SQL Server};SERVER='+host+';DATABASE='+database+';UID='+userid+';PWD='+password+';Encrypt=yes;TrustServerCertificate=yes'
  

• 确保GCP Dataflow Worker所在VPC可访问SQL Server，比如配置VPC peering、防火墙规则开放SQL Server端口（默认1433）。

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