AWS EMR Serverless v6.8.0连接SQL Server超时故障求助

问题描述 #

使用AWS EMR Serverless Spark v6.8.0连接SQL Server，代码在本地机器及EC2上测试正常，但Serverless集群运行时出现连接超时错误。已开启VPC安全组所有流量端口。

作业提交配置 #

applicationId=app_id,
executionRoleArn="my-role",
jobDriver={
    "sparkSubmit": {
        "entryPoint": "s3://emr-studio-rts/scripts/ms-sql-fetch.py",
        "entryPointArguments": ["s3://emr-studio-rts/output"],
        "sparkSubmitParameters": "--jars https://emr-studio-rts.s3.us-east-2.amazonaws.com/jars/sqljdbc42.jar --conf spark.executor.cores=1 --conf spark.executor.memory=4g --conf spark.driver.cores=1 --conf spark.driver.memory=4g --conf spark.executor.instances=1",
    }
},
configurationOverrides={
    "monitoringConfiguration": {
        "s3MonitoringConfiguration": {"logUri": "s3://emr-studio-rts/logs"}
    }
},
)

报错代码片段 #

spark = SparkSession\
        .builder\
        .appName('test-db') \
        .config('spark.driver.extraClassPath', 'https://emr-studio-rts.s3.us-east-2.amazonaws.com/jars/sqljdbc42.jar') \
        .config('spark.executor.extraClassPath', 'https://emr-studio-rts.s3.us-east-2.amazonaws.com/jars/sqljdbc42.jar') \
        .config("spark.executor.cores", "1") \
        .getOrCreate()

# read table data into a spark dataframe
df1 = spark.read.format("jdbc") \
    .option("url", f"jdbc:sqlserver://{my_host}:1433;databaseName={my_database};") \
    .option("dbtable", table_name) \
    .option("user", my_user) \
    .option("password", my_password) \
    .option("driver", "com.microsoft.sqlserver.jdbc.SQLServerDriver") \
    .load()

错误详情 #

Status Details: Job failed, please check complete logs in configured logging destination. ExitCode: 1. Last few exceptions: : com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host 3.12.0.70, port 1433 has failed. Error: "Connection timed out: no further information. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.". py4j.protocol.Py4JJavaError: An error occurred while calling o93.load.

排查与解决方案 #

• 配置EMR Serverless的VPC网络：EMR Serverless默认运行在AWS公有网络中，若SQL Server部署在VPC内，需在创建EMR Serverless应用时指定目标VPC的子网、安全组，让Serverless执行环境接入该VPC。确保指定的安全组与SQL Server所在安全组互通，允许1433端口的TCP流量。
• 移除重复的ClassPath配置：作业提交参数sparkSubmitParameters已通过--jars指定驱动包，无需在SparkSession构建时重复配置spark.driver.extraClassPath和spark.executor.extraClassPath，避免配置冲突。修改后的SparkSession代码：

  spark = SparkSession\
          .builder\
          .appName('test-db') \
          .config("spark.executor.cores", "1") \
          .getOrCreate()
  

• 修正驱动包路径：将代码中驱动包的https://路径改为s3://格式（如s3://emr-studio-rts/jars/sqljdbc42.jar），确保EMR Serverless能正确加载S3中的驱动文件。
• 验证SQL Server的网络访问规则：若SQL Server使用公网IP，检查其所在实例的网络ACL是否允许EMR Serverless所在区域的IP段访问1433端口，同时确认SQL Server的系统防火墙（Windows防火墙或iptables）已开放该端口。
• 检查执行角色权限：确保executionRoleArn对应的IAM角色拥有访问S3驱动包路径的s3:GetObject权限，以及VPC相关的ec2:DescribeSubnets、ec2:DescribeSecurityGroups等权限，保证Serverless能正常接入目标VPC。

内容的提问来源于stack exchange，提问作者Muhammad Ashir Ali