[R] open_dataset() on csv files lacks support for compressed files

Using open_dataset() on S3 buckets of csv files is a game-changing magic, particularly with all the additional support for database / dplyr operations over the remote connection, and the widespread adoption of S3 buckets even by old-school big data providers like NOAA.

It's not uncommon to encounter buckets with \*.csv.gz formats.  I know technically this should be unnecessary, as compression can be done "in flight" by the server, but usually this is not an issue for R users since R's `connection` class automatically detects and gunzips compressed files (over either POSIX or HTTP connections).  It would be really great if arrow could handle this case too. 

**Reporter**: [Carl Boettiger](https://issues.apache.org/jira/browse/ARROW-15060) / @cboettig
#### Related issues:
- [[C++][Dataset] Add option to write compressed CSV](https://github.com/apache/arrow/issues/30654) (relates to)

_{**Note**: *This issue was originally created as [ARROW-15060](https://issues.apache.org/jira/browse/ARROW-15060). Please see the [migration documentation](https://github.com/apache/arrow/issues/14542) for further details.*}

Start from the R open_dataset() entry point and trace how remote CSV inputs from S3 are handled, paying attention to .csv.gz files and the existing connection behavior described in the issue. Verify the result with a compressed CSV dataset and confirm that reading it supports the stated database/dplyr operations.

Written by the indexing model from the issue text.