As far as I am aware that is not possible or something we could implement, at least without special support from the application playing the file so that we had some way to stream data to it.
Most video players expect to be given a file which already has the data in it, and ones which can stream tend to do both the downloading and the playback so that they have control over both sides.
I'm not an expert on low-level file read or stream, but I dare say it can't be application-dependent.
Using the likes of software like NetDrive, ExpandDrive and such I'm able to:
. watch video in a few seconds
. listen to music instantaneously, even large flacs
. open large zip/rar/iso files in ~5 sec
I know VLC is able to stream, but I think it has nothing to do with SFTP.
So I guess it can be done, although the programs I mentioned serve just that specific purpose, so I bet it's not trivial.
The ones I cited above are commercial, so no code there for us to read.
This project on GitHub might be interesting: github.com/dokan-dev/dokan-sshfs
It's a way to mount a filesystem other than FAT/NTFS on Windows. It just works with SFTP.
Turning SFTP into a local filesystem that appears like any other drive to all programs: Possible in theory, but not going to work well in reality, and an incredible amount of work. Also not something that would make sense as part of Opus; more as part of a standalone project.
People have tried this with basic FTP and it has never worked very well, in my experience. FTP and SFTP are just too difficult to real filesystems for that route to make sense. It's hard enough making something like Samba work well, and that's when the two ends are a lot more similar than FTP/SFTP vs a real filesystem.
You can make it work well if you only need to provide a subset of the behaviour of a real filesystem, which is exactly what Opus does. But if you do that, you need to know that the software talking to it is only going to expect that subset to work. That is fine when it's Opus talking to Opus, or a video player talking to its own built-in SFTP client, but not going to cut it in a more general case.