Fix streaming for servers not supporting HTTP range requests
Does it mean that huge files might end up being downloaded? It would go against the purpose of streaming, I think. At least, this fallback should be an option that could be disabled Yes, it is against the purpose of streaming, but streaming is not possible if the server does not allow HTTP range requests.

We have two options: either we download the file or we throw an error. I think we simply cannot fallback to downloading the file if streaming fails without the user being aware of it. Some options: 
- make the fallback optional (using an env var? or a function param)
- use the fallback only if the dataset size is under some threshold (provided we have the data in the DatasetInfo) -> it's the option I use in `datasets-preview-backend` ([here](https://github.com/huggingface/datasets-preview-backend/blob/48ac19e49c19809763e8d640986bf2c3d792faed/src/datasets_preview_backend/models/typed_row.py#L40) and [here](https://github.com/huggingface/datasets-preview-backend/blob/aa86c5493b275c9e2dbae7dab7bd469da5773a41/src/datasets_preview_backend/models/split.py#L31-L37))
- throw an exception and let the user decide what to do
 IMO in general we should throw an exception and ask the user to not use streaming mode in that case.

Your second point is also interesting but I feel like it could be confusing for users sometimes: it doesn't feel natural that the streaming-ability should depend on the size of the file. Sure, I think we should just throw an exception
 Current behavior is already throwing an Exception:
```
ValueError: Cannot seek streaming HTTP file
```

We could customize the exception class and/or the exception message. I'm not sure we really need to change anything. I opened the issue https://github.com/huggingface/datasets/issues/3677  because discovery was streamable and is not anymore (according to my test suite in https://github.com/huggingface/datasets-preview-backend): I was not sure if it was due to some regression in the library, or to some change in the dataset itself. I'm wondering why it worked before and it is no longer working... > We could customize the exception class and/or the exception message.

Yup a message that says that the host doesn't support streaming because it doesn't support HTTP Range requests would be useful ! DONE, @lhoestq. 
Some servers do not support HTTP range requests, whereas this is required to stream some file formats (like ZIP).

~~This PR implements a workaround for those cases, by download the files locally in a temporary directory (cleaned up by the OS once the process is finished).~~

This PR raises custom error explaining that streaming is not possible because data host server does not support HTTP range requests.

Fix #3677.