Database: Load file-based databases from remote filesystems

[amotl]

Problem

While omniload can address file-based databases like SQLite or DuckDB using protocol schemes like sqlite:// or duckdb://, that can mostly address files on local filesystems. omniload can't tap into such files on remote filesystems yet, right?

Goal

Enhance component system so users can load .duckdb and .sqlite files from remote filesystems like s3:// and all others.

References

• Data Control Center suraya#36

[hampsterx]

Implemented in #272. A filesystem source URI whose object carries a database extension (.db, .ddb, .duckdb, .sqlite, .sqlite3) routes to the SQL source instead of a format reader, so the URL stays the one the storage service names it with:

omniload ingest \
  --source-uri 's3://analytics/snapshots/events.duckdb?access_key_id=ACCESS&secret_access_key=SECRET' \
  --source-table 'main.events' \
  --dest-uri 'duckdb:///local.duckdb' \
  --dest-table 'raw.events'

--source-table keeps selecting a table inside the database, so SQL reflection, chunking and type mapping stay on the path. The object is staged into a run-scoped temporary directory for the lifetime of the run and removed on both success and failure. The engine comes from the file header rather than the extension, so .db needs no disambiguation and a mislabeled object still loads.

Covered so far: s3://, r2://, gs://, az://, adls://, abfss://, the schemes whose credential parsing the file sources already share. Not yet "all others": the connectors added in GH-254 build their fsspec client inside dlt_source(), so staging cannot get an authorized filesystem for them without lifting that construction into a shared helper, which is the same seam GH-256 is about.

Should that lifting happen here, or does this issue close on the six schemes with the rest riding on GH-256?

[amotl]

Wonderful, so sweet, exactly like I had dreamed it up. Thank you. 💯