Select from s3 files directly

[arunbala]

Hi all,
I am new to Datafusion and playing around with it. Is there a way to query remote parquet files directly without adding it as external table? I couldn't find it in the docs and I tried a couple of things and they didn't work:
select * from location 's3://bucket/file.parquet';
select * from 's3://bucket/file.parquet';

[alamb]

Hi @arunbala -- I don't think we have taught datafusion-cli how to do this quite yet. I think you need to register the datasource like this https://arrow.apache.org/datafusion/user-guide/cli.html#registering-s3-data-sources

I would like to file a ticket describing your usecase. Can you tell me:

1. How would you like to provide credentials (e.g. environment variables?)
2. Is this a public s3 bucket? If so, can you provide an example for us

Maybe @r4ntix has some thoughts

[alamb]

I tried this:

AWS_DEFAULT_REGION=us-west-1 datafusion-cli
DataFusion CLI v32.0.0
❯ create external table test stored as csv location 's3://daylight-map-distribution/release/v1.32/preferred-localization-v1.32.tsv';
Object Store error: Generic S3 error: response error "No Body", after 0 retries: HTTP status client error (403 Forbidden) for url (https://s3.us-west-1.amazonaws.com/daylight-map-distribution/release/v1.32/preferred-localization-v1.32.tsv)

But it didn't seem to work 🤔

This is from https://registry.opendata.aws/daylight-osm/

$ aws s3 ls --no-sign-request s3://daylight-map-distribution/release/v1.32/
2023-10-11 00:30:35 1979121841 admin-v1.32.osc.gz
2023-10-11 00:30:44 1663984746 coastlines-v1.32.tgz
2023-10-11 00:30:51 5145738435 fb-ml-roads-v1.32.osc.gz
2023-10-11 00:35:57    1118957 important-features-v1.32.json
2023-10-11 00:31:12 71590373272 ml-buildings-v1.32.osm.pbf
2023-10-11 00:35:58 79052472394 planet-v1.32.osm.pbf
2023-10-11 00:35:57    7328880 preferred-localization-v1.32.tsv

[r4ntix]

Currently, object_store needs a credential provider to access S3.

In my local env, it got an error:

$ AWS_DEFAULT_REGION=us-west-1 ./target/debug/datafusion-cli
DataFusion CLI v32.0.0
❯ create external table test stored as csv location 's3://daylight-map-distribution/release/v1.32/preferred-localization-v1.32.tsv';
Object Store error: Generic S3 error: the credential provider was not enabled
❯

Maybe we can provide a way for object_store access to public files in S3 without credentials? 🤔️
likes --no-sign-request options in aws-cli. cc @tustvold

[tustvold]

https://github.com/apache/arrow-rs/issues/4927

[r4ntix]

Hi @arunbala

Currently, datafusion-cli needs to register an external table to access non-local datasource. I think the way datafusion-cli registers the external table is simple and clear.

Could you tell us about your use cases? Maybe, we could have a discussion to find ways to improve it. 😊

[arunbala]

Thanks for the responses @r4ntix @alamb. Our use case is this: we have a set of parquet files in a bucket for each of our customers. When a customer queries for their data through an API, we would like to dynamically pull the data from the parquet files in the right bucket. Each customer's data that can be queried in an API call can fit in memory. We can have 10s of thousands of customers and registering an external table for each customer can get unwieldy. I am just using the CLI to try out DataFusion. If it works out, we will use the Rust library in the service.

[alamb]

Thanks @arunbala -- I think you can do what you describe from datafusion (and from datafusion-cli but you will have to supply real credentials via ENVIRONMENT variables)

@tustvold 's ticket / PR in object store https://github.com/apache/arrow-rs/issues/4927 will allow the unsigned (public) usecase, but I assume your customer data is not in public buckets, so it will not be directly applicable

[arunbala]

Got it, thanks. Will try that out