HIVE_UNKNOWN_ERROR: Invalid port in file system location

When using S3 access points with Athena, there are several issues to address:

1. Regarding updating the table location: Yes, you can update the location of an existing table in AWS Glue using the ALTER TABLE statement in Athena. The syntax would be:

ALTER TABLE database_name.table_name 
SET LOCATION 's3://proper-bucket-path/database/table'

However, the core issue is that Athena doesn't support using S3 access point ARNs directly in the LOCATION property. When specifying table locations in Athena, you should avoid using ARNs, HTTP notations, or special patterns.

2. For accessing S3 through VPC privatelink, you have a few options:

• Use S3 access point aliases instead of ARNs. Access points have auto-generated aliases that look like regular S3 bucket names and can be used in place of bucket names in most S3 operations.

• If using the AWS CLI or SDK through your VPC privatelink, you can specify the endpoint URL parameter to route requests through your VPC endpoint while using standard bucket notation.

• For Athena specifically, make sure your table locations use standard S3 paths (s3://bucket-name/path) rather than access point ARNs.

A potential workaround for your situation would be:

1. Have Airbyte write to the access point
2. After data is written, update the table location in Glue to use the standard S3 path format
3. Configure proper IAM permissions to ensure Athena can access the data through the standard path

For Java/Python code accessing AWS APIs through a VPC privatelink, you can configure the AWS client to use a specific endpoint URL that points to your VPC endpoint.
Sources
Specify a table location in Amazon S3 - Amazon Athena
AWS PrivateLink for Amazon S3 - Amazon Simple Storage Service
Athena query failed when trying to access S3 from access point | AWS re:Post