Remove the need for registering an ObjectStore for remote files

**Is your feature request related to a problem or challenge? Please describe what you are trying to do.**
Currently, we need to register an ObjectStore to register remote files stored in S3, https, etc. This could be more ergonomic from a DX perspective.

**Describe the solution you'd like**
Automatically detect if an ObjectStore is needed, like in the datafusion-cli; see [here](https://github.com/apache/datafusion/blob/6f8c74ca873fe015b2e7ff4eeb2f76bf21ae9d0e/datafusion-cli/src/object_storage.rs#L395).

**Describe alternatives you've considered**
keep it as it is now

**Additional context**
We should increase the test coverage for ObjectStore