Not getting partial reads of large parquet file in AWS S3 opened from pre-signed URL

[dude0001]

What happens?

I am trying to read a large Parquet file from AWS S3 using pre-signed URLs. The expectation is that DuckDB-WASM will be able to do partial reads of the large file. The observed behavior is the entire file is read. I can reproduce this by first registering the file with registerFileURL writing a SELECT against the file alias as well as putting the pre-signed URL inline in a SELECT.

To Reproduce

1. When using await this.db.registerFileURL('data.parquet', 'https://the-bucket.s3.amazonaws.com/data.parquet?X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASI....', DuckDBDataProtocol.HTTP, false);

Request 1: HEAD w/ Range: bytes=0- header results in 403 w/ Access-Control-Expose-Headers: Access-Control-Allow-Origin, Accept-Ranges, Content-Range, ETag header and in the console logs falling back to full HTTP read for: https://the-bucket.s3.amazonaws.com/data.parquet?X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASI...
This is expected since the pre-signed URL only allows GET

Request 2: GET w/ no relevant header results in 200 reading the entire file

2. When using select column_1 from 'https://the-bucket.s3.amazonaws.com/data.parquet?X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASI...' limit 1;

Request 1: HEAD w/ no relevant header results in 403 w/ Access-Control-Expose-Headers: Access-Control-Allow-Origin, Accept-Ranges, Content-Range, ETag header
Again, this is expected since the pre-signed URL only allows GET

Request 2: GET w/ Range: bytes=0-0 header results in 206 w/ Access-Control-Expose-Headers: Access-Control-Allow-Origin, Accept-Ranges, Content-Range, ETag header and a single byte P returned in the Response body.
Seems to prove Range header can be used to fetch a partial file.

But then... instead of starting to partially read the data needed to serve the query, what I observed when using registerFileURL seems to repeat.

Request 3: HEAD w/ Range: bytes=0- header results in 403 w/ Access-Control-Expose-Headers: Access-Control-Allow-Origin, Accept-Ranges, Content-Range, ETag header
This is expected since the pre-signed URL only allows GET. But why is it doing a HEAD here, when Request 2 showed the server can do partial reads using GET w/ Range header?

Request 4: GET w/ no relevant header results in 200 reading the entire file instead of partial reads.

Browser/Environment:

Chome115.0.5790.171 (Official Build) (64-bit)

Device:

Lenovo ThinkPad P16

DuckDB-Wasm Version:

1.27.0

DuckDB-Wasm Deployment:

shell.duckdb.org

Full Name:

mark.lambert@gmail.com

Affiliation:

Episode-VII Solutions

[dude0001]

Cross-posted on Discord in case future readers are looking for more information.

[carlopi]

Connected to #1366, we need to review the detection, thanks a lot for the extended analysis.

For this to be properly approached, a publicly available dataset served from a server with a similar setup would be useful.

[dude0001]

I did see #1366 and this may be related, or have some synergy to refactoring this logic considering these two issues together. I tried testing issue #1367 with v1.24.0 and still have seen the same outcome. The description on #1366 seems to say the issue there does not exist in v1.24.0 and broke in v1.25.0.

I'll see if I can put together a public demo or provide other effort on this.

[carstonhernke]

Hi, jumping in here as I am still seeing this error. I've been able to reliably reproduce it with some test data and the shell.duckdb.org site. It seems to only occur when using an S3 pre-signed URL. The issue does not occur when querying from the same file in a public S3 bucket.

To reproduce error behavior 🔴

Error behavior: DuckDB falls back to full HTTP read when attempting to query from a parquet file on S3 with a pre-signed URL.

1. Navigate to https://shell.duckdb.org/

2. Run the query
   select * from 'https://public-duckdb-range-request-test.s3.us-east-1.amazonaws.com/duckdb_test_data.parquet?response-content-disposition=inline&X-Amz-Security-Token=IQoJb3JpZ2luX2VjENj%2F%2F%2F%2F%2F%2F%2F%2F%2F%2FwEaCXVzLWVhc3QtMSJIMEYCIQCbMAURhqDX9mbDnydx81qoJVEVyqLQbKn1JIfWepk3MQIhAIjuIY4QLLECabojikbz%2B9Kh3sh%2BSiNSwf%2BKTXzDKcDlKugCCBEQBRoMMzM5NTIwNDQwODAyIgwZ%2BxbR%2FmayVaccK%2FgqxQJUBr27wa0E0P8qBJLgBDsidpWZB4zUbWJJ4xtcG%2FGERk7WYNjsEzOR2s5AiP3ieWtcfbu8iWFZ1hgRQcEYbUX0AWuP5N%2BoF3xIxB7UbnCrMccfldGNDiJQzT%2FSOI6WTZym4NKHqA4jHxAPImfoMMs4CAI%2BzkO8TO4XHEdoEeQN4lHREzqeI4JUnmT5vWRJPSCIHZ4q0SYLiGefVhqzoHGkePwy8db46fOElMq7eORzMaEMyF90NI6kRfAdzEQL8zAc3Y3Zp8zl3VASWVSKh%2BWKUE7OnK%2FmAFNIXPrfwvpGkE6gF8yfdf8fbGj%2BFBcXEFLJuWLgXeQraQ9uWjX1NMOmr2WUsI0s8XXkUmolrUgmm3%2B%2FfdBWJBGIGmASZN2Mw%2B7hIElJz11kNs0d8NuAX91dRrmK%2BdyVwqgWM18hKBD5gd0YGBFOMIjj07AGOrICtXpg9aBLkSPYZEx%2FPFckuTlDDqk5XZUH7QLmuzXX5QirVgG%2F3Y8CEDIdXr0DcrHY7hKYwu8sVaZKO39hblAp9rJTl5IIzf0FO1PFTesx7zQMmwC3%2BHifzaYmBGVs8zXi%2BO8cRpclNhnz%2ByKSHA5edhRsChR5TvZN0XsomG547E%2BzQZgy7biNg8mP6IE%2BPn6LnTBehTxY5Ufey0a%2FYaWp2vk%2FZJq3Nnh0Q%2BvLHvprg5cF8PSiaNHGmGcp3ll5Vp2sSUX3j689DxwYWhzBip3uj9SdTMCYPygetdkj9B666%2FvaQxtgm5mEYTvOojfkzvgnbv%2BdDeO%2BOPTuRu9ly%2Fiore61LCJ0j6%2BuQtnjo2fKPOxnP4p4ZSJHGtae78xr40M1%2BIS6eYeA1MqqA9CP%2FrsP7yj5&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Date=20240409T085416Z&X-Amz-SignedHeaders=host&X-Amz-Expires=43200&X-Amz-Credential=ASIAU6DH6ZHRIPRNTAX2%2F20240409%2Fus-east-1%2Fs3%2Faws4_request&X-Amz-Signature=56cf21de8fa0d5d13656331d4d57a81f313d767010b6d682a835dd94a7cb0979' where postcode = 10787 limit 10;

3. Results will return, however, looking at network requests, you will see the 4 different requests that @dude0001 mentioned above. Notably, the 206 partial GET request for range 0-0 succeeds, but DuckDB still falls back to a full HTTP read.

NOTE: the max validity period of S3 pre-signed tokens is 12 hours, so the token above will expire at 20:55 UTC Apr 9. Let me know if you need a new one.

To reproduce intended behavior 🟢

Intended behavior: Range requests are used successfully when the same file is requested with no presigned token.

1. Navigate to https://shell.duckdb.org/
2. Run the query
   select * from 'https://public-duckdb-no-token-test.s3.amazonaws.com/duckdb_test_data.parquet' where postcode = 10787 limit 10;
3. Results will return, and range requests will be used as expected.

Other notes

• Both S3 buckets have been configured for CORS from all origins
• The Parquet files are sorted by postcode and compressed with ZSTD (using DuckDB of course 😄 )
• There is no CDN involved here, requests are going right to s3.

[carstonhernke]

@carlopi Is the relevant chunk of code which performs the detection located here?

duckdb-wasm/packages/duckdb-wasm/src/bindings/runtime_browser.ts

Line 177 in 91335e0

if (contentLength !== null && xhr.status == 206) {

[carstonhernke]

I opened PR #1702 which should resolve this issue :)

[carlopi]

Thanks @carstonhernke for the push on this, should be solved.