duckdb / pg_duckdb

DuckDB-powered Postgres for high performance apps & analytics.
MIT License
1.54k stars 54 forks source link

Cannot access locally deployed minio using pg_duckdb #207

Open transparent1998 opened 1 month ago

transparent1998 commented 1 month ago

pg version:16.4


postgres=# select * from duckdb.secrets ;
type |          id          |                  secret                  | region | session_token |  endpoint  | r2_account_id | use_ssl 
------+----------------------+------------------------------------------+--------+---------------+------------+---------------+---------
S3   | xxxxxxxxxxxxxxxxxxxxx| xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx|        |               | minio:9000 |               | f
(1 row)

postgres=# select duckdb.install_extension('httpfs'); install_extension

t (1 row)

postgres=# select from read_csv('s3://jyt/test.csv') as t (a int,b int); WARNING: (PGDuckDB/CreatePlan) Prepared query returned an error: 'HTTP Error: HTTP GET error on 'https://jyt.s3.amazonaws.com/test.csv' (HTTP 403) WARNING: (PGDuckDB/CreatePlan) Prepared query returned an error: 'Conversion Error: Type VARCHAR with value '{}' can't be cast to the destination type LIST LINE 1: ...est.csv'::text, false, true, true, '{}'::text[], 'auto'::character varying, ''... ^ ERROR: Function read_csv(TEXT) only works with Duckdb execution. CONTEXT: PL/pgSQL function read_csv(text,boolean,boolean,boolean,text[],character varying,character varying,character varying,character varying,character varying,boolean,text[],boolean,boolean,boolean,bigint,text[],character varying,boolean,boolean,text[],boolean,character varying,bigint,character varying,bigint,character varying,text[],boolean) line 3 at RAISE postgres=# select from read_csv('/tmp/a.csv') as t (a text); a

1 (1 row)

The key and endpoint are set, but the corresponding address is not modified when accessing, and there is no place to set s3_url_style

```sql
D load httpfs;
D set s3_use_ssl='false';
D set s3_url_style='path';
D set s3_endpoint='minio:9000';
D set s3_access_key_id ='xxxxxxxxxxxxxxxxxx';
D set s3_secret_access_key ='xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx';
D select * from read_csv('s3://jyt/test.csv');
┌───────┬───────┐
│   a   │   b   │
│ int64 │ int64 │
├───────┼───────┤
│     1 │     2 │
└───────┴───────┘
D 

It can be accessed normally in duckdb

wuputah commented 1 month ago

Correct, s3_url_style is not currently settable.

As discussed a bit in #197, we might want to move towards a more flexible way of supporting all the various options. For instance it would also be nice to support the scope option… and there's many others.

JelteF commented 1 month ago

I guess this will be fixed by #151, once we implement it.