Unable to connect to duckdb database residing on S3

[fRoDdYy]

Referring to the documentation when I am trying to connect to my duckdb database by attaching it

outputs:
dev:
type: duckdb
path: tmp/dbt.duckdb
extensions:
- httpfs
- parquet
settings:
s3_region: ap-south-1
s3_access_key_id: "{{ env_var('AWS_ACCESS_KEY_ID') }}"
s3_secret_access_key: "{{ env_var('AWS_SECRET_ACCESS_KEY') }}"
s3_session_token: "{{ env_var('AWS_SESSION_TOKEN') }}"
attach:
- path: "s3://bucket-name/file-path/database.duckdb
read_only: true
threads: 1
external_root: s3://bucket-name/output
target: dev

I get the following error:

Runtime Error
Catalog Error: Cannot open database "s3://bucket-name/file-path/database.duckdb" in read-only mode: database does not exist

[jwills]

Huh; the path in the attach statement doesn't match the path in the error and it's not totally obvious to me why-- will take a look.

[Mause]

The s3 attach via httpfs story is a bit of a strange one right now - I think you can only set auth via environment variables or query parameters

[jwills]

ack, thank you @Mause! I swear I've made it work before, let me see if I can sort out how.

[fRoDdYy]

@Mause I tried it with environment variable too but the issue persists.

@jwills Yeah sorry I made that mistake while typing have corrected it though.

[Mause]

Maybe double check the environment variable names? https://duckdb.org/docs/extensions/httpfs#configuration-1

[NatElkins]

I am also interested in this use case. I'm trying to perform a query on a DuckDB database in S3. I'm pretty sure my env vars are correct because I'm able to run a query like:

with t as (
	SELECT *
	FROM iceberg_scan('s3a://path/to/files', allow_moved_paths = true)
)
SELECT *
from t;

without issue.

I've set my env vars like this:

SET s3_region = 'us-east-1';
SET s3_access_key_id = 'access_key_id';
SET s3_secret_access_key = 'secret_access_key';

and I've also executed the following:

INSTALL httpfs;
INSTALL iceberg;

LOAD httpfs;
LOAD iceberg;

(Iceberg not related directly to this problem, just noting it).

I also get the error:

Catalog Error: Cannot open database "s3://path/to/test.duckdb" in read-only mode: database does not exist

I know my repro isn't using dbt-duckdb, but I'm also interested in this use case with dbt-duckdb (was just testing with SQL to verify behavior outside of dbt-duckdb).

[jwills]

Ah appreciate that @NatElkins -- I wonder if it works if you use the fsspec stuff? So as to treat S3 like a filesystem as opposed to trying to use the httpfs route?

[NatElkins]

@jwills It doesn't seem to work, although I may be doing something wrong. I have the creds defined in my ~/.aws/credentials file.

import duckdb
from fsspec import filesystem

duckdb.register_filesystem(filesystem('s3', anon=False))
duckdb.connect("s3://bucket/path/to/test.duckdb'")
r1 = duckdb.sql("select * from test")
print(r1)

The error I get is:

duckdb.connect("s3://bucket/path/to/test.duckdb")
duckdb.duckdb.IOException: IO Error: Cannot open file "/Users/nathanielelkins/Projects/dbt_test/s3://bucket/path/to/test.duckdb": No such file or directory

[jwills]

Yep, right there with you-- I cannot figure out how to get this to work right now. 😞

[AldricVS]

I stumbled across this thread when searching for the same issue and I managed to make this working using the Secret provider system (see the S3 API Support Page).

The issue seems to be to set credentials using statements like SET s3_endpoint = ....

On the 0.10.3 version, when creating a secret like :

INSTALL httpfs; 
LOAD httpfs;
CREATE SECRET secret1 (
        TYPE S3,
        ENDPOINT '***',
        KEY_ID '****',
        SECRET '****',
        REGION '****'
);

then performing the "attach" statement :

ATTACH 's3://path/to/dbfile'
AS db (READ_ONLY)

it worked perfectly

[evan-climate]

Chiming in to say this is still a problem on 1.0.0 python release.

[ldolberg]

Any workaround on this? for dagster with duck the workaround was to use boto3 to set the session manually. Is there any way to make it work?

This is my profiles.yml

analytics:
  target: dev
  outputs:
    dev:
      type: duckdb
      path: '../{{ env_var("DUCKDB_DATABASE", "data/staging/data.duckdb") }}'
      use_credential_provider: aws
      extensions:
        - httpfs
        - parquet
      secrets:
        - type: s3
          provider: credential_chain
      settings:
        s3_region: us-west-2

and this is my source:

- name: external_source meta: external_location: "s3://{{env_var('S3_BUCKET','DEFINE_ME')}}/input/reonomy_{name}_*.json" tables: - name: summaries - name: properties

The error I'm getting is

22:49:00 Runtime Error in model base_reonomy_properties (models/source/base_reonomy_properties.sql) HTTP Error: HTTP GET error on '/?encoding-type=url&list-type=2&prefix=input%2Freonomy_summaries_' (HTTP 403) 22:49:00

[jwills]

Oh interesting— can you point me at the Dagster workaround and I will see if I can implement it here?