Fast full-text search or LIKE query on Parquet file on S3

[collimarco]

I am trying to filter some logs that are stored in Parquet format on S3.

I use a query like this:

SELECT status, message FROM sample WHERE status = 200 AND message LIKE '%foo%'

Is this the right approach to search some keywords in a text field? Or is there a better/faster function to use instead of SQL LIKE?

In particular I would like to optimize the speed of the query and reduce the data downloaded (e.g. leveraging the condition pushdown).

[tustvold]

You could try enabling filter pushdown (late materialization) for parquet - https://docs.rs/datafusion/latest/datafusion/config/struct.ParquetOptions.html

[collimarco]

The row group only stores the min and max values for each column.

I know that it wouldn't be easy to improve... but what about keeping also a Bloom filter for each term in the column (and not just min/max)?

I see that Parquet already supports Bloom filters.

For my understanding, it currently uses them only on the entire value.

Fo example, if I have a column "MovieTitle":

• "The title of my movie"
• "Another movie title"
• "The best movie title"
• ...

Then the current Bloom filters can be used to find only the column chunks/pages that match an exact title. For example you can use the bloom filter to search for "The best movie title".

It would be interesting to have a bloom filter on the specific words, instead of using the entire value: in this way you can search the word "best" in the "MovieTitle" column and find the titles that contain that specific word in an efficient way.

It would enable a sort of full-text search of keywords inside text columns. It would also allow predicate pushdown for searches based on keywords.

Would make sense to have such an addition? Where can you make a proposal?

[collimarco]

Has the parquet file been written with a page index?

I don't know. Can you explain?

I created it with the official Arrow library for Ruby:

arrow_table = Arrow::Table.new(table)
arrow_table.save('sample.parquet')

[tustvold]

I suspect supporting native indexing for accelerated full text search is likely out of scope for the parquet project, especially since it would be tricky to get consensus on an approach and then drive ecosystem adoption, but you could try proposing the idea on the parquet mailing list. I suspect the suggestion would be to maintain some form of secondary-index like https://github.com/valeriansaliou/sonic or Lucene on top of your parquet files.

Taking a step back though, the bottleneck in your queries appears to be IO to R2, something that additional indexing is unlikely to help dramatically with. I'm not very familiar with R2, but typically object stores have very high first byte latencies after which throughput is very high - consequently the relationship between bytes scanned and latency is often extremely sub-linear.

[collimarco]

what about keeping also a Bloom filter for each term in the column (and not just min/max)?

Maybe I could convert the text column in an array of words. Example: "The best movie title" becomes ["The", "best", "movie", "title"]

If I make this transformation and I search for a specific term, can Parquet / Datafusion use a condition pushdown? Do array columns have some advantages for term search? Or they need to be scanned entirely like normal text?

[alamb]

If I make this transformation and I search for a specific term, can Parquet / Datafusion use a condition pushdown? Do array columns have some advantages for term search? Or they need to be scanned entirely like normal text?

I don't think DataFusion would be able to help here (as it only has min/max values per column, so even if you split a column into arrays the index would still only have two (min/max) array values on each row group and data page.

As @tustvold mentions above, supporting fast full text search will likely need a specialized index built on top of your parquet files (I think the academic term is "inverted index") but parquet doesn't support such indexes natively