Filter rows with par¶
Experimental
par works, but its commands, options and output may change between releases.
You want the rows matching a condition, such as the countries of a continent or the features inside a box, without writing code. --filter takes a SQL WHERE expression and applies it inside the read: par skips the parts of the file unable to match, and tests the remaining rows one by one. par explain shows what was skipped.
Filter a read¶
cat, head, row-count, explain and cp take --filter. With countries.parquet from Getting started:
$ par cat countries.parquet --columns name,pop_est --filter "pop_est > 50000000"
{"pop_est":5.8005463E7,"name":"Tanzania"}
{"pop_est":3.28239523E8,"name":"United States of America"}
$ par row-count countries.parquet --filter "pop_est > 50000000"
2
The columns of the filter need not be among the columns printed:
$ par cat countries.parquet --columns name,iso_a3 \
--filter "continent = 'Africa' OR pop_est BETWEEN 1000000 AND 40000000"
{"name":"Tanzania","iso_a3":"TZA"}
{"name":"W. Sahara","iso_a3":"ESH"}
{"name":"Canada","iso_a3":"CAN"}
The expressions¶
--filter-help prints the supported expressions. In short:
| Kind | Expressions |
|---|---|
| Comparisons | = != <> < <= > >= |
| Logic | AND, OR, NOT, parentheses |
| Sets and ranges | IN (...), NOT IN (...), BETWEEN x AND y, IS NULL, IS NOT NULL |
| Spatial relations | ST_Intersects, ST_Touches, ST_Crosses, ST_Overlaps, ST_Disjoint, ST_Equals, ST_Contains, ST_Within, ST_Covers, ST_CoveredBy, ST_DWithin |
| Query geometries | ST_GeomFromText('<WKT>'), ST_MakeEnvelope(minx, miny, maxx, maxy) |
| Bounding boxes | ST_Extent(<geometry column>), ST_Envelope(<geometry column>) |
The rules:
- The left side of a comparison is a column. The right side is a literal: a number, a
'string',trueorfalse. - A null value matches no comparison, and no negation of a comparison:
x <> 1,NOT (x = 1)andx NOT IN (1)skip it.IS NULLselects it. - A column named after a SQL keyword, or with a space in its name, goes in double quotes:
"from" > 1,"my column" = 2. A double quote inside the name is written twice:"a""b" = 4. - A filter on a nested column names its path, such as
"outer".code = 3.
An expression outside this list fails with exit code 5:
$ par cat countries.parquet --filter "upper(name) = 'FIJI'"
par: unsupported in --filter: upper (run with --filter-help to list the supported predicates)
Filter by location¶
The spatial functions test the exact geometries of the rows against a query geometry:
$ par cat countries.parquet --columns name \
--filter "ST_Intersects(geometry, ST_MakeEnvelope(-100, 30, -90, 40))"
{"name":"United States of America"}
$ par cat countries.parquet --columns name \
--filter "ST_DWithin(geometry, ST_GeomFromText('POINT(178 -17)'), 2)"
{"name":"Fiji"}
- The tests run in the coordinate reference system of the file.
pardoes not reproject the query geometry: write its coordinates in that system. ST_ContainsandST_Within, andST_CoversandST_CoveredBy, depend on the order of their arguments.ST_Within(geometry, <query>)keeps the rows inside the query geometry.ST_DWithintakes a distance as its third argument, in the units of the coordinates.
ST_Extent(geometry) stands for the bounding box of the geometry. With it, the test compares two rectangles and skips the exact geometry test. Edges count as inside, and Z is ignored:
$ par cat countries.parquet --columns name \
--filter "ST_Intersects(ST_Extent(geometry), ST_MakeEnvelope(-100, 30, -90, 40))"
{"name":"United States of America"}
The rectangle form accepts ST_Intersects, ST_Covers, ST_CoveredBy, ST_Equals and ST_Disjoint, and a query written as ST_MakeEnvelope, ST_Extent or ST_Envelope. The other relations reject it and say what to use instead:
$ par cat countries.parquet \
--filter "ST_Contains(ST_Extent(geometry), ST_MakeEnvelope(-20, 20, -10, 30))"
par: ST_Contains has no bounding-box form: box relations include their edges, and a geometry on the query edge answers differently under ST_Contains; use ST_Covers for the box relation, or remove ST_Extent(geometry) to test the exact geometry (run with --filter-help to list the supported predicates)
ST_Envelope is a synonym of ST_Extent. It is the spelling understood by PostGIS for the box of one geometry, where ST_Extent aggregates over rows.
Filter by location explains the same tests in the Java API.
See what a filter skips¶
par explain prints the decisions taken on the metadata of the file, without reading the rows. A Parquet file keeps its rows in row groups, blocks of rows described in the footer at the end of the file. The sample file has a single row group. This copy splits it into row groups of two rows (Copy files):
The copy also has bbox columns, holding the bounding boxes of the geometries. A spatial filter uses their statistics to skip row groups:
$ par explain countries-rg.parquet \
--filter "ST_Intersects(geometry, ST_MakeEnvelope(-100, 30, -90, 40))"
predicate: GeometryFilterPredicate[filter=io.tileverse.parquetry.geo.JtsGeometryFilter@76a36b71]
1 kept, 0 skipped (0 rows pruned without opening a file)
location outcome rows reason
countries-rg.parquet KEEP 5 kept
countries-rg.parquet:
Tiers: STATS, SPATIAL, COLUMN_INDEX, BLOOM_FILTER, RECORD_LEVEL
RG Rows Stats Spatial ColIdx Bloom Record Outcome
0 2 ELIM - - - - skip
1 2 n/a n/a passed n/a - all
2 1 n/a n/a passed n/a - all
The first line shows the filter as Parquetry received it. The file table comes next: for a directory or an Iceberg table, it lists the files, kept or skipped with the reason. The last table has one line per row group of a kept file, and one column per tier, in the order of the read:
- Stats: the minimum and maximum of the column chunks, from the footer. Row group 0 holds Fiji and Tanzania, far from the query box, and its
bboxstatistics rule it out. - Spatial: the bounding box of the geometries of the row group, recorded in the footer of files with native geometry types, such as GeoParquet 2.0 files.
- ColIdx: the page index. A column chunk is cut into pages, and the page index records the minimum and maximum of the pages. It skips pages inside a row group.
- Bloom: the bloom filters, compact sets answering whether a value may be present in a column chunk.
- Record: the test of the rows one by one.
explainreads no rows, and prints-.
A cell holds ELIM when the tier ruled the row group out, NARROW n/m when it kept n rows out of m, kept or passed when the row group went on, and n/a when the tier had no data or did not apply. A - follows an ELIM: the later tiers did not run. The Outcome column gives the result: skip, all for rows still to test, matched when the statistics proved that all rows match, or a number of rows left.
--analyze runs the read as well, and adds per row group the rows matched, the bytes read and the time spent, then the totals and the requests sent to storage. -o json prints the plan as JSON.
Lay out files for selective queries shows how the order of the rows and the page size make these tiers skip more.
Under the hood¶
- The read path describes the tiers.
- Spatial filtering explains how the
bboxcolumns and the bounds of GeoParquet 2.0 files skip row groups and pages.