full-text search support

[etah000]

Do we have plan to add support for full-text search by integrating lucene, sphinx，or some similar stuffs?

[alexey-milovidov]

We have plans to integrate text processing functions like:

• tokenization;
• word normalization and stemming;
• stop words filtering.

[alexey-milovidov]

Direct code reuse from Sphinx and Manticore is not possible due to GPL license.
Although we are going to reuse dictionaries.

[etah000]

How about Lucene by JNI or Solr? Are there any technical problems if we integrate it into Clickhouse in the same way as greenplum DB does？

[r-zenine]

Hello everyone,

I am currently considering adding a support for Lucene or Tantivy. I am early stage in the process and I just started looking into it.

My current approach is to build an external server that would wrap one of the two libraries above and contribute an integration to Clickhouse to communicate with it ( a bit like Kafka, Mysql, PostgresSQL integrations ).

The external server would run locally on the same hardware and ideally, it would be able to communicate in native Clickhouse format to minimise serialisation/deserialisation overhead.

Would you welcome such a contribution ? I can work on it full time for a couple of months.

I am considering this approach because there is not mature full text search library in Cpp that is licence compatible with Clickhouse.

@alexey-milovidov I would really much appreciate your feedback on the approach! If you think it could be a viable option, I can prepare a more thorough design document and we could discuss it further.

Another option, if we chose to integrate with the rust library is to build a C++ wrapper library on top of it. It might be manageable to have that whole process neatly integrated with the current Cmake build. WDYT ?

[r-zenine]

It's seems that the integration with rust was attempted by the community already here

https://github.com/NeowayLabs/ClickHouse/commits/fulltext-21.3

Would you welcome such approach in Clickhouse ?

[etah000]

I will check the fork. think you for the message.

[r-zenine]

@etah000 Any update ?

The company I work for would be delighted to see this capability built in Clickhouse. If you think this is the way to go, my team could help improve that fork until it's production ready :)

[etah000]

@r-zenine it is a workable prototype for full-text search support, but it requires that each row has a unique key. Anyway, it is feasible. You can refine it with creating index statement by defining a materalized view on the index stoarge engine and replacing the subquery with a table function, so users would feel natural to use it.

My intitial intention is to build an inverted index and incorporate it into clickhouse with a function such as ft_search(...)。Each row is identified by its block number and offset which is used by primary key. But as partions are meged time and then, this inverted index have to be updated and merged too. The operation is time and resource consuming. Maybe an offline inverted index building is a better choice when partitions are stable.

Another option is to send text to other full text search engine, such as solr or elasticsearch. The query will be slower, but this solution scales better and can be easily integrated with users' exsting full text system.

Actually, I don't know which is better :(

[r-zenine]

@etah000,

Regarding the second option, we played with that a bit already. The main challenge to overcome are :

• The number of document id's you are going to be able to extract from the external system in a reasonable amount of time is relatively low
• If you use something like : ( doc_id IN url(.......) ) the Set construction is mono-threaded and will be the main bottleneck
• You have to think about aligning the sharding strategies on both systems
• You also have to think about how this will interact with the sampling on clickhouse side.

We are also looking at the second option at the moment.

[godliness]

@r-zenine

I found a C++ library for Lucene: https://github.com/luceneplusplus/LucenePlusPlus

Why you didn't use this to instead of rust library tantivy?

Tantivy performance is better than that? or anything else?

[tobia]

My intitial intention is to build an inverted index and incorporate it into clickhouse with a function such as ft_search(...)

I think this is still the best option, because it is the only one that would allow users to freely mix and match ft_search() and ft_rank() operations with all the other joins, group bys and conditions in a complex ClickHouse query.

For example, suppose one needs to find results by applying some boolean filters, plus a given fulltext search, then group the results by a certain set of columns, somehow aggregating the text search rank and other data for each group, and then sort by a combination of the metrics and limit to N rows. If you need to offload all the partial query results for each block to an external system to perform fulltext matches, maybe even at multiple stages of query processing, performance would probably not be good.

Each row is identified by its block number and offset which is used by primary key. But as partitions are merged time and then, this inverted index have to be updated and merged too. The operation is time and resource consuming. Maybe an offline inverted index building is a better choice when partitions are stable.

I think two stages of indices would solve this problem.

1. Full-text inverted index

This would be an index from token (word, word stem, token id, or token vector...) to the set of records that contain that token (plus additional data such as frequency, but this example is very simplified.) Every record would be identified by a private record_id, that would not be exposed to the user:

token	record_ids
A	1,2,3
B	4,5,5
...	...

2. Record ID two-way index

This would be a two-way index (for example two B-trees, one direct, one inverted) between record_id and block number + offset. The existence of this second index would allow merging operations to quickly move block numbers and offsets around, without having to touch the main full-text inverted index(es), which may contain the same record_id millions of times, in the lists of all the tokens they match.

record_id	block number	offset
1	239	87642
2	3456	26537
3	234	58659
...	...	...

---

@alexey-milovidov

Direct code reuse from Sphinx and Manticore is not possible due to GPL license.

PostgreSQL has excellent, mature fulltext search code, is written in C and is released with a BSD / MIT-style license.