Make filesystem destination work with gcs in s3 compatibility mode

[sh-rp]

GCS has an s3 interoperability mode which emulates s3. This way users could use the s3 bucket filesystem implementation with gcs. This is useful when loading to a final destination which supports connecting to s3 buckets but not gcs, such as clickhouse. Clickhouse now has a custom implementation to handle this, but it would be nice if our filesystem could be set up to use the s3 implementation with gcs buckets, right now based on the bucket url, gcs will always be selected automatically.

If this is done, we can update clickhouse and remove the specialized implementation.

[rudolfix]

@sh-rp is this the same as #1181 ? we support any compatible s3 storage ie R2 and minio also works. what is different here? it would be good to specify what needs to be done :)

[Pipboyguy]

@sh-rp The gcs is an alias of s3 table function. We don't seem to have an option other than to use the HMAC key authentication mechanism that's currently implemented.

Would #1181 be a solution to this by extending the GcpCredentials class?

[Pipboyguy]

@sh-rp @rudolfix if we remove the GcsCredentials totally from the storage auth flow like so:

        if bucket_scheme in ("s3", "gs", "gcs"):
            if not isinstance(staging_credentials, AwsCredentialsWithoutDefaults):
                raise DestinationTransientException(
                    "Staging data to S3 or Google Cloud Storage requires providing AWS-style HMAC access keys, even if using a "
                    "non-AWS storage provider. Please make sure to configure your destination with an `aws_access_key_id` and `aws_secret_access_key`. "
                    "See https://dlthub.com/docs/general-usage/destination#pass-explicit-parameters-and-a-name-to-a-destination "
                    "for documentation on how to set up your destination with the required access keys."
                )

then the current tests with gcs will fail before we have implemented named destinations in tests so we can separate s3/gcs tests. So I'll leave things as is, and only implement one specialised test for gcs hmac flow:

[destination.clickhouse.credentials]
...
gcp_access_key_id = "..."
gcp_secret_access_key = "..."

[destination.filesystem_s3_gcs_comp.credentials]
aws_access_key_id = "..."
aws_secret_access_key = "..."

Obviously this will leave a duplicate definition of the same keys in config until we get the named destinations support into tests. Please advise if this is good.