MWAA环境下googleapiclient连接Google Sheets超时问题求助

问题描述 #

我们在使用googleapiclient连接Google Sheets时出现超时错误。此前代码运行正常，但某次新部署后问题出现，即使回滚部署也无法解决。

我们的Airflow部署在MWAA Airflow 2.6.3环境，依赖通过Python WHL文件构建。部署时曾出现依赖安装超时警告：

WARNING: requirements.txt installation timed out after 9 minutes. Some requirements may not have installed.

导致DAG失效。目前Airflow可正常连接Jira等其他第三方服务，但连接Google Sheet API的DAG始终报错。

代码片段 #

from googleapiclient.discovery import build

service = getattr(build(
    serviceName='sheets',
    version='v4',
    credentials=<credentials>), spreadsheets)()
service.get(spreadsheetId=<spreadsheet_id>).execute()

报错堆栈信息 #

Traceback (most recent call last):
  File "/usr/local/airflow/dags/common/spreadsheet.py", line 199, in get_spreadsheet
    return service.get(spreadsheetId=self._id).execute()
  File "/usr/local/airflow/.local/lib/python3.10/site-packages/googleapiclient/_helpers.py", line 130, in positional_wrapper
    return wrapped(*args, **kwargs)
  File "/usr/local/airflow/.local/lib/python3.10/site-packages/googleapiclient/http.py", line 923, in execute
    resp, content = _retry_request(
  File "/usr/local/airflow/.local/lib/python3.10/site-packages/googleapiclient/http.py", line 222, in _retry_request
    raise exception
  File "/usr/local/airflow/.local/lib/python3.10/site-packages/googleapiclient/http.py", line 191, in _retry_request
    resp, content = http.request(uri, method, *args, **kwargs)
  File "/usr/local/airflow/.local/lib/python3.10/site-packages/google_auth_httplib2.py", line 209, in request
    self.credentials.before_request(self._request, method, uri, request_headers)
  File "/usr/local/airflow/.local/lib/python3.10/site-packages/google/auth/credentials.py", line 151, in before_request
    self.refresh(request)
  File "/usr/local/airflow/.local/lib/python3.10/site-packages/google/oauth2/service_account.py", line 434, in refresh
    access_token, expiry, _ = _client.jwt_grant(
  File "/usr/local/airflow/.local/lib/python3.10/site-packages/google/oauth2/_client.py", line 312, in jwt_grant
    response_data = _token_endpoint_request(
  File "/usr/local/airflow/.local/lib/python3.10/site-packages/google/oauth2/_client.py", line 272, in _token_endpoint_request
    response_status_ok, response_data, retryable_error = _token_endpoint_request_no_throw(
  File "/usr/local/airflow/.local/lib/python3.10/site-packages/google/oauth2/_client.py", line 219, in _token_endpoint_request_no_throw
    request_succeeded, response_data, retryable_error = _perform_request()
  File "/usr/local/airflow/.local/lib/python3.10/site-packages/google/oauth2/_client.py", line 195, in _perform_request
    response = request(
  File "/usr/local/airflow/.local/lib/python3.10/site-packages/google_auth_httplib2.py", line 119, in __call__
    response, data = self.http.request(
  File "/usr/local/airflow/.local/lib/python3.10/site-packages/httplib2/__init__.py", line 1724, in request
    (response, content) = self._request(
  File "/usr/local/airflow/.local/lib/python3.10/site-packages/httplib2/__init__.py", line 1444, in _request
    (response, content) = self._conn_request(conn, request_uri, method, body, headers)
  File "/usr/local/airflow/.local/lib/python3.10/site-packages/httplib2/__init__.py", line 1366, in _conn_request
    conn.connect()
  File "/usr/local/airflow/.local/lib/python3.10/site-packages/httplib2/__init__.py", line 1156, in connect
    sock.connect((self.host, self.port))
TimeoutError: timed out

环境配置信息 #

Configurations:

MWAA: Airflow 2.6.3

Installed Packages (Using plugins.zip):
 - Levenshtein-0.21.1
 - PyGithub-1.59.0
 - adtk-0.6.2
 - apache-airflow-providers-atlassian-jira-2.1.1
 - apache-airflow-providers-github-2.3.1
 - apache-airflow-providers-mysql-5.1.1
 - apache-airflow-providers-snowflake-4.2.0
 - asttokens-2.2.1
 - atlassian-python-api-3.39.0
 - aws-requests-auth-0.4.3
 - backcall-0.2.0
 - cachetools-5.3.1
 - comm-0.2.2
 - cycler-0.12.1
 - debugpy-1.8.1
 - defusedxml-0.7.1
 - executing-1.2.0
 - fonttools-4.50.0
 - google-api-core-2.11.0
 - google-api-python-client-2.92.0
 - google-auth-2.21.0
 - google-auth-httplib2-0.1.0
 - googleapis-common-protos-1.59.1
 - gql-3.3.0
 - graphql-core-3.2.3
 - httplib2-0.22.0
 - iniconfig-2.0.0
 - ipykernel-6.25.1
 - ipython-8.14.0
 - jedi-0.18.2
 - jira-3.5.2
 - joblib-1.3.2
 - jupyter-client-8.3.0
 - jupyter-core-5.3.1
 - kiwisolver-1.4.5
 - matplotlib-3.5.2
 - matplotlib-inline-0.1.6
 - mpld3-0.5.9
 - mysqlclient-2.2.0
 - nest-asyncio-1.6.0
 - numpy-1.24.4
 - oauthlib-3.2.2
 - oscrypto-1.3.0
 - pandas-1.5.3
 - parso-0.8.3
 - patsy-0.5.6
 - pickleshare-0.7.5
 - pillow-10.2.0
 - playwright-1.37.0
 - protobuf-4.23.4
 - pure-eval-0.2.2
 - py-1.11.0
 - pyOpenSSL-23.2.0
 - pyasn1-0.4.8
 - pyasn1-modules-0.2.8
 - pycryptodomex-3.18.0
 - pyee-9.0.4
 - pynacl-1.5.0
 - pypika-0.48.9
 - pytest-7.4.0
 - python-Levenshtein-0.21.1
 - pyzmq-25.1.0
 - requests-oauthlib-1.3.1
 - retry-0.9.2
 - rsa-4.9
 - scikit-learn-1.3.0
 - scipy-1.12.0
 - snowflake-connector-python-3.0.4
 - snowflake-sqlalchemy-1.4.7
 - sortedcontainers-2.4.0
 - sql-formatter-0.6.2
 - stack-data-0.6.2
 - statsmodels-0.14.1
 - thefuzz-0.20.0
 - threadpoolctl-3.4.0
 - traitlets-5.9.0
 - uritemplate-4.1.1

排查方向与解决方案 #

1. 修复依赖安装不完整问题 #

依赖安装超时导致部分包可能未正确安装，尤其是Google相关依赖：

• 将google-api-python-client、google-auth、google-auth-httplib2等包的版本固定在兼容MWAA 2.6.3的范围，避免版本冲突
• 精简requirements.txt，移除不必要的包（如jupyter相关、pytest等开发依赖），减少安装时间
• 使用预编译的WHL包上传至S3，替代从PyPI直接安装，避免网络超时

2. 网络与权限排查 #

从报错堆栈看，超时发生在服务账号获取Token的阶段，需确认：

• MWAA环境的VPC是否允许出站访问Google的Token端点（https://oauth2.googleapis.com/token），检查安全组和NACL规则
• 服务账号密钥是否有效，是否被删除或权限变更，重新生成密钥并更新至Airflow连接
• 确认Google Sheets API已在Google Cloud项目中启用

3. 调整请求超时与重试策略 #

默认的googleapiclient重试策略可能不足以应对网络波动：

• 构建服务时自定义HTTP客户端，设置更长的超时时间：

  from googleapiclient.discovery import build
  import httplib2
  
  http = httplib2.Http(timeout=30)  # 设置30秒超时
  service = build(
      serviceName='sheets',
      version='v4',
      credentials=<credentials>,
      http=http
  ).spreadsheets()
  

• 增加重试次数，使用自定义重试装饰器：

  from googleapiclient.discovery import build
  from retrying import retry
  
  @retry(stop_max_attempt_number=5, wait_exponential_multiplier=1000, wait_exponential_max=10000)
  def get_spreadsheet(service, spreadsheet_id):
      return service.get(spreadsheetId=spreadsheet_id).execute()
  

4. 替换HTTP客户端 #

httplib2在某些环境下可能存在兼容性问题，尝试替换为requests：

• 使用google-auth-requests替代google-auth-httplib2，构建基于requests的HTTP客户端：

  from googleapiclient.discovery import build
  from google.auth.transport.requests import AuthorizedSession
  
  authed_session = AuthorizedSession(<credentials>)
  service = build(
      'sheets',
      'v4',
      requestBuilder=authed_session.request
  ).spreadsheets()
  

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