Reading an iceberg table in a vpc fails with connection time out on endpoint URL: "https://sts.amazonaws.com/"

[AdrianoNicolucci]

Describe the bug

Description of problem

I have a lambda function within a VPC that calls the method df = wr.athena.read_sql_query. I get the error message Connect timeout on endpoint URL: "https://sts.amazonaws.com/". I have configured vpc endpoints for my subnet that the lambda function runs in for athena, s3, glue and even sts. When I run the same function outside the VPC there is no problems.

I have even tried set the sts endpoint in my code with wr.config.sts_endpoint_url = 'https://sts.us-east-2.amazonaws.com' "

Error message

Traceback (most recent call last):
    df = wr.athena.read_sql_query(sql=sql_query,
  File "/opt/python/awswrangler/_config.py", line 735, in wrapper
    return function(**args)
  File "/opt/python/awswrangler/_utils.py", line 178, in inner
    return func(*args, **kwargs)
  File "/opt/python/awswrangler/athena/_read.py", line 1080, in read_sql_query
    return _resolve_query_without_cache(
  File "/opt/python/awswrangler/athena/_read.py", line 557, in _resolve_query_without_cache
    return _resolve_query_without_cache_regular(
  File "/opt/python/awswrangler/athena/_read.py", line 432, in _resolve_query_without_cache_regular
    s3_output = _get_s3_output(s3_output=s3_output, wg_config=wg_config, boto3_session=boto3_session)
  File "/opt/python/awswrangler/athena/_utils.py", line 76, in _get_s3_output
    return create_athena_bucket(boto3_session=boto3_session)
  File "/opt/python/awswrangler/athena/_utils.py", line 452, in create_athena_bucket
    account_id: str = sts.get_account_id(boto3_session=boto3_session)
  File "/opt/python/awswrangler/sts.py", line 33, in get_account_id
    return _utils.client(service_name="sts", session=boto3_session).get_caller_identity()["Account"]
  File "/var/runtime/botocore/client.py", line 553, in _api_call
    return self._make_api_call(operation_name, kwargs)
  File "/var/runtime/botocore/client.py", line 989, in _make_api_call
    http, parsed_response = self._make_request(
  File "/var/runtime/botocore/client.py", line 1015, in _make_request
    return self._endpoint.make_request(operation_model, request_dict)
  File "/var/runtime/botocore/endpoint.py", line 119, in make_request
    return self._send_request(request_dict, operation_model)
  File "/var/runtime/botocore/endpoint.py", line 202, in _send_request
    while self._needs_retry(
  File "/var/runtime/botocore/endpoint.py", line 354, in _needs_retry
    responses = self._event_emitter.emit(
  File "/var/runtime/botocore/hooks.py", line 412, in emit
    return self._emitter.emit(aliased_event_name, **kwargs)
  File "/var/runtime/botocore/hooks.py", line 256, in emit
    return self._emit(event_name, kwargs)
  File "/var/runtime/botocore/hooks.py", line 239, in _emit
    response = handler(**kwargs)
  File "/var/runtime/botocore/retryhandler.py", line 207, in __call__
    if self._checker(**checker_kwargs):
  File "/var/runtime/botocore/retryhandler.py", line 284, in __call__
    should_retry = self._should_retry(
  File "/var/runtime/botocore/retryhandler.py", line 320, in _should_retry
    return self._checker(attempt_number, response, caught_exception)
  File "/var/runtime/botocore/retryhandler.py", line 363, in __call__
    checker_response = checker(
  File "/var/runtime/botocore/retryhandler.py", line 247, in __call__
    return self._check_caught_exception(
  File "/var/runtime/botocore/retryhandler.py", line 416, in _check_caught_exception
    raise caught_exception
  File "/var/runtime/botocore/endpoint.py", line 281, in _do_get_response
    http_response = self._send(request)
  File "/var/runtime/botocore/endpoint.py", line 377, in _send
    return self.http_session.send(request)
  File "/var/runtime/botocore/httpsession.py", line 499, in send
    raise ConnectTimeoutError(endpoint_url=request.url, error=e)```
 


### How to Reproduce

1. Create a iceberg table in s3
2. Attempt to read from an iceberg table that is deployed in a vpc in a lambda function.

### Expected behavior

I can read from my dataset succesfully when using the `wr.athena.read_sql_query` method when using a lambda function deployed in a vpc. The issue would apply to a glue job as well.

### Your project

_No response_

### Screenshots

_No response_

### OS

Linux

### Python version

3.10

### AWS SDK for pandas version

3.7.2

### Additional context

_No response_

[kukushking]

Hi @AdrianoNicolucci , does it succeed with a vanilla boto3 call and not with aws sdk for pandas? is athena endpoint also set?

[AdrianoNicolucci]

Hi @AdrianoNicolucci , does it succeed with a vanilla boto3 call and not with aws sdk for pandas? is athena endpoint also set?

@kukushking After testing it with a table that is just a csv table instead, I'm still getting the same issue of the lambda function timing out since it can't connect to athena. So perhaps it has nothing to do with the iceberg table specifically. However, When I attempt to query directly with boto3, I can see the query reach the athena service which tells me there is an issue somewhere with this method.

[AdrianoNicolucci]

After reviewing the logs and doing some further debugging, it looks like the read_sql_query method is trying to call the sts service to get the account_id, using the global instead of the regional endpoint. I'm confused why it's trying to use the global sts endpoint instead of the regional one.

error message

 File "/opt/python/awswrangler/sts.py", line 33, in get_account_id
    return _utils.client(service_name="sts", session=boto3_session).get_caller_identity()["Account"]

Here are the global endpoint configurations I have tried to set to force it to use the region endpoints but with no luck:

os.environ["AWS_STS_REGIONAL_ENDPOINTS"] = "regional"
wr.config.glue_endpoint_url = "https://glue.us-east-2.amazonaws.com"
wr.config.athena_endpoint_url = "https://athena.us-east-2.api.aws.com"
wr.config.sts_endpoint_url = "https://sts.us-east-2.amazonaws.com"