Discuss and resolve ambiguities with file and table formats

[amotl]

I looked through the current source plumbing [...]

One caveat: format support is currently spread across endpoint parsing, filesystem readers, blob/SFTP allowlists, HTTP’s separate adapter, and the local csv:// source. Before adding many formats, it would probably be worth centralizing the format-to-reader mapping so new readers can be registered once. Feel free to open an issue for that and assign it to me.

Originally posted by @hampsterx in #45

[amotl]

I agree that in an ontology tree of database daemons, object storage addresses, and SaaS services, format support is difficult to squeeze in, because it is indeed an overarching topic, so it might need a kind of special treatment.

I think the most popular way to understand the topic mapped to expectations is that a data loading tool should be able to read from any popular data format from any popular local or remote filesystem / cloud storage provider.

Databricks provides a reasonable set of supported file formats. Because omniload's filesystem source uses fsspec, it can also cover a good amount of 3rd party services already, in theory ;].

In reality, I can only find information about Amazon S3 and Google Cloud Storage, so Azure Blob Storage and OSS are clearly missing, just to enumerate two. Azure should work well with dlt, so this would be a matter of updating the documentation, which will also need corresponding curation (GH-58).

@hampsterx: Please go ahead with any proposals you might have. Either discuss, or just send patches ;]. Thank you again! 🌻

[amotl]

Please also note that single-level URL schemes is not enough to cover all the cases. Let me elaborate on behalf of a good example: In many frameworks using fsspec and an URL notation for addressing data sources or sinks, URL schemes like az://, gs://, or s3:// are used to address cloud storage endpoints, mostly tied to a vendor.

If the framework routes all of them to fsspec, there is little room to plug in different clients. For this reason, we are using URL schemes like abfs+deltalake://, gs+deltalake://, hdfs+deltalake://, or s3+deltalake:// to let the router select the corresponding deltalake client implementation for transferring data.

I hope dlt.destinations.filesystem is a major workhorse here, and that it can do anything we want. It is possible that omniload does not exercise it to its fullest capabilities yet. On the other hand, frameworks like Polars provide plenty of performances and should be given a prominent chance on this journey to support many file formats in a polyglot way. I don't know if fsspec and Polars could be mixed/blended in any sensible way, and I also don't know yet if that would make any sense.

[amotl]

If you will be reorganizing things, please also drop legacy mappings like this.

omniload/omniload/target/csv/__init__.py

Lines 39 to 41 in 9319298

	def dlt_dest(self, uri: str, **kwargs):
	if uri.startswith("csv://"):
	uri = uri.replace("csv://", "file://")

csv:// is certainly not a valid protocol scheme and should not be used as such, otherwise please tell me if I'm wrong. How would you discriminate if it's a CSV file on the local filesystem or somewhere stored remotely? If you agree, and nothing else contradicts it, let's exclusively use file:// for addressing filesystem-based resources going forward?

[amotl]

I'd like to have omniload support those formats where applicable:

File formats: Text, CSV, XLSX, XML, JSON, JSONL, ORC, Avro, Parquet
Table formats: Delta Lake, Iceberg
Compression formats: gzip, bzip2, lzo, brotli, lz4, zstd

All artefacts of those kinds can be stored both on the local filesystem (file://), and on remote filesystems (az://, gs://, s3://, hdfs://, lakefs://). While file-based resources are mostly addressed as such, and often carry their type and compression within their name suffixes, table-based resources are mostly addressed by directory.

[amotl]

In this patch, we also needed to quickly use an excel:// protocol scheme (not applicable!), because a FileTypeRouter does not exist yet.

• Connectors: Add source adapter for the XLSX file format #123

Let's also converge this adapter to use the file:// protocol scheme while it's still baking.

• ValueError: Unsupported source scheme: file #120