Export table using COPY TO command to alternate S3 endpoint

CrateDB
1

I have a need to export a Crate DB table to S3, I can do this with COPY TO command by providing s3 endpoint. But I need this to be exported to a different S3 endpoint and not default AWS S3 endpoint. How to provide the alternate s3 endpoint and credentials?

1 Like
2

Hi,

What command are you using, could I see it?

This is the supported syntax to define an end point in the latest release:

s3://[<accesskey>:<secretkey>@]<bucketname>/<path>

See here

Kind regards

3

Hi,
I am wondering the same question.
Is it possible to use COPY TO with other services that are S3-compatible ?

For example, I have a Minio instance on which I successfully ran SNAPSHOT using s3:
CREATE REPOSITORY archive TYPE s3 WITH (bucket='my-bucket', endpoint='minio.myserver.com', protocol='https', access_key='my_access_key', secret_key='my_secret_key');
This works perfectly because I can specify the endpoint.

How can I specify the endpoint for COPY TO ?

Tried this but did not work, gave an S3 error (meaning that it doesn’t use the correct endpoint):
copy doc.my_table to directory 's3://my_access_key:my_secret_key@minio.myserver.com/my-bucket'

SQLActionException[UnhandledServerException: com.amazonaws.services.s3.model.AmazonS3Exception: The AWS Access Key Id you provided does not exist in our records. (Service: Amazon S3; Status Code: 403; Error Code: InvalidAccessKeyId;

4

This seems more like a problem with your credentials/policies.
Are you trying to use the same minio instance?

5

Yes, it’s the same instance.
The problem is the request does not reach my Minio server (checked access logs), it goes directly to AWS.
So the question remains, how to tell COPY TO to use my Minio server instead of AWS - like you can for CREATE REPOSITORY via the endpoint parameter.

6

Have you progressed with this issue? (I have a similar problem trying to copy my data into a min.io instance)
Thanks

7

Unfortunately no, you still cannot set a custom endpoint for COPY.

My guess is that we need a client.setEndpoint() around here.

1 Like
8

Hi @Ignacio_Elicegui & @rps-v

I created a feature request in the GitHub repo: Support setting a different endpoint for COPY TO / FROM for DNS-style S3 bucket adresses · Issue #11654 · crate/crate · GitHub. Feel free to upvote it there.

You are right, that the client currently defaults to AWS S3 servers. Minio added dns-style access only after this feature was initially realised. As mentioned above Minio should work for Snapshot-repositories and is also used internally by Crate.io.

2 Likes
9

Hello rps-v.
Could you tell me how you manage to create your snapshot in your minio instance? I have my own minio framework and I’m trying to replicate your “CREATE REPOSITORY” sentence and I’m always getting:
[cannot create blob store: Unable to execute HTTP request]

I guess this has something to do with the url used by crate that puts the bucket name before de minio endpoint when doing the request:
[bucker-name].minio.myserver.eu

Any idea/configuration that should be done somewhere? I’m using cratedb 4.1.2 and version 2021-04-06T23:11:00Z for minio
Thanks in advance
Regards

10

I think we ran into this issue as well. What we did was to make a DNS CNAME record so that buckets can be accessed via subdomains too.

*.minio.server.com. IN CNAME minio.server.com.

1 Like
11

Thanks a lot for your support. I will try.
Regards

1 Like