Question: Does filtering on the Hive metadata "$path" column work? #24440

draganrelu · 2024-12-10T13:54:23Z

draganrelu
Dec 10, 2024

Hi,

Suppose we have the following parquet files in S3:

s3://history/results/dt=2024-12-01/results_alice_0.parquet
s3://history/results/dt=2024-12-01/results_alice_1.parquet
s3://history/results/dt=2024-12-01/results_alice_2.parquet
s3://history/results/dt=2024-12-01/results_bob_0.parquet
s3://history/results/dt=2024-12-01/results_bob_1.parquet
s3://history/results/dt=2024-12-02/results_alice_0.parquet
s3://history/results/dt=2024-12-02/results_bob_0.parquet
...

And we create a Trino table results with the storage location at s3://history/results/, partitioned by dt.

If we do a query that filters both on the partitioning column dt but also on the Hive metadata column $path :

select * from results
where dt = date('2024-12-01')
and "$path" like "%alice%";

This query will return the elements from

s3://history/results/dt=2024-12-01/results_alice_0.parquet
s3://history/results/dt=2024-12-01/results_alice_1.parquet
s3://history/results/dt=2024-12-01/results_alice_2.parquet

But would it also read and parse the content of the bob files in that day?

s3://history/results/dt=2024-12-01/results_bob_0.parquet
s3://history/results/dt=2024-12-01/results_bob_1.parquet

?

In other words, does query filtering on the $path column do "pre-filtering" (skips reading the content of certain files) or "post-filtering" (reads the contents of all files and then filters the results)?

Thanks,
Relu

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Question: Does filtering on the Hive metadata "$path" column work? #24440

{{title}}

Replies: 0 comments

Select a reply

Question: Does filtering on the Hive metadata "$path" column work? #24440

draganrelu Dec 10, 2024

Replies: 0 comments

draganrelu
Dec 10, 2024