minioduckdb

Proper way to create a duckdb table from a minio s3 bucket?

I ran minio as a docker container using the latest image from https://hub.docker.com/r/minio/minio:

docker run 
-d 
-p 9000:9000 
-p 9001:9001 
-v C:\\Users\\drew\\Downloads\\NY:/data 
minio/minio server /data 
--console-address ":9001"

The container spins up successfully with no error messages in the logs. When I go to 127.0.0.1:9000 I get redirected to the webUI and I'm able to login and view my object storage with no issues.

When trying to create a duckdb table with the following query:

CREATE SECRET secret1 (
    TYPE S3,
    KEY_ID 'minioadmin',
    SECRET 'minioadmin',
    REGION 'us-east-1',
    ENDPOINT '127.0.0.1:9000',
    USE_SSL false
);

CREATE TABLE mydata AS
SELECT 
    *
FROM read_parquet('s3://plswork/mydata.parquet', hive_partitioning = true);

where my bucketname is plswork. I keep getting a:

Error: IO Error: Connection error for HTTP HEAD to 'http://plswork.127.0.0.1:9000/mydata.parquet'

however when I go to http://127.0.0.1:9000/plswork/mydata.parquet in the browser I just get back xml response

<Error>
<Code>AccessDenied</Code>
<Message>Access Denied.</Message>
<Key>mydata.parquet</Key>
<BucketName>plswork</BucketName>
<Resource>/plswork/mydata.parquet</Resource>
...
</Error>

What am I missing? Am I setting up the url or the secret credentials wrong with duckdb?

Solution

  • If anyone finds this from google I figured it out by doing the following thanks to this article:

    In the web ui for minio you can enable public access on a bucket. I enabled this as its default is set to private

    I changed the code to the following:

    INSTALL 'httpfs';
LOAD 'httpfs';

SET s3_region = 'us-east-1';
SET s3_url_style = 'path';
SET s3_endpoint = '127.0.0.1:9000';
SET s3_access_key_id = 'minioadmin';
SET s3_secret_access_key = 'minioadmin';
SET s3_use_ssl = false;

CREATE TABLE mydata AS
SELECT * 
FROM read_parquet('s3://plswork/mydata.parquet/*.parquet', hive_partitioning = true);

    According to the duckdb documentation the preferred way to set s3 credentials is using secrets and that this old way of using variables is deprecated but it seems to still work