The purpose of this document is to provide options to potentially enable full text search in Synapse Tables. The use case comes from the NF resources portal currently under development where data about NF resources would be stored in Synapse tables along with natural language description of various kind of resources.
Overview of the Project:
User Stories:
Internal Meeting Logs:
Initial Design Wireframes: https://www.figma.com/file/aSr8SUVMX0EETgF3Qxvyrw/NF-Resource-Database?node-id=343%3A18199
Slack Channel: https://app.slack.com/client/T092YT0LV/C01RKMZQBKL
TL;DR: Elasticsearch is the preferred option over CloudSearch. MySQL Full text index a (very) limited option but easier to integrate.
From the project overview we extract the following (note that it is not part of the deliverable nor acceptance criteria):
Implementation of a search index on the data tables that supports search features like relevancy ranking, root word extraction, multiple keyword search;
It highlights the need for a full text search feature that performs some text processing for indexing and provide result sorted by some form of relevance ranking.
From the user stories:
search on a single term that allows for searching / matching across any field within any resource type (Plasmid, Antibody, etc.) without having to specify which type of resource I'm looking for
search on a single term as above, but have accurate results if there are minor misspellings
search using autocomplete
The synapse tables feature already support searching, filtering and faceting results using an SQL like language, a similar result as the above can be achieved using the LIKE (and HAS_LIKE for multi-value columns) operator. The main difference is that synapse tables do not perform any kind of text processing before indexing nor include any form of relevance ranking. For example searching for a phrase using a LIKE expression won’t apply any tokenization nor stemming on the input potentially limiting the relevant results.
For this use case we assume that there will be real and meaningful natural language descriptions in the table data (when tabular data is mostly key/value pairs with common metadata the filtering provided by a relation database is more meaningful than a full text search index).
Available Options
To enable such a feature we have different options: generally a search index is maintained as a separate and eventually consistent component that complements the source of truth, this is the case even for Synapse tables: the data is stored in S3, but they are eventually built in a dedicated relational DB against which we run user queries.
We consider various technologies that are easily available to us and that live within the AWS ecosystem.
CloudSearch: This is a managed service by AWS based on Solr that we use in Synapse to index entity metadata and wiki pages content. Note that while still supported the last meaningful update is from 2014 with dynamic fields and the product receive sporadic updates mainly for supporting new instance types. The product is not included in the AWS pricing calculator (redirects to elasticsearch offering).
Elasticsearch: This the managed offering from AWS of the popular Elasticsearch product from Elastic NV based on Lucene. AWS will maintain an open source fork: https://aws.amazon.com/blogs/opensource/stepping-up-for-a-truly-open-source-elasticsearch (elastic changed the license from apache to their own).
MySQL Full Text Search: Since Synapse tables are built in a MySQL database we also consider the native full text search index capabilities offered by MySQL.
Kendra
: New offering from AWS that supports unstructured data indexing and searching using ML. We exclude this offering because of the type of technology catered toward natural language queries (e.g. what is..?, who is..? where is…? etc) and its costs (min $800/month for a developer edition).
Comparison
We compare some of the relevant features of the various options (excluding Kendra):
Feature | CloudSearch | ElasticSearch | MySQL FTS | Notes |
|---|---|---|---|---|
Schema Type | Fixed, partial support for schema-less with dynamic fields (capture all) | Fixed or Schema-less (Dynamic mapping) | Fixed, index needs to specify all the columns included in the search. The query needs to specify all the columns in the index. | A schema-less approach allows to index data whose structure is unknown, this might not be needed for table as by design we know the structure of the data. |
Stemming | Yes | Yes | No - Require pre-processing from the application side | When indexing the tokens can be usually reduced to a word stem before indexing, this allows more flexibility when matching against similar terms (e.g. search for database might match documents containing databases) |
Fuzzy search | Yes | Yes | No - Can potentially be implemented using soundex in a pre-processing step but it’s very fragile | Fuzzy search can be useful in some cases for minor misspellings |
Field boosting | Yes (at query time) | Yes (both query and schema time) | No - Not sure what a work around would look like. | This is useful when specific columns are more relevant than others (e.g. a match in the title might be more meaningful than a match in a description). |
Multiple indexes | No | Yes | Yes - Each synapse table has its own DB table) | In the synapse tables context it is relevant to have the possibility to create an index per table given that each table might have a different schema. |
Auto-complete | Yes (Suggester API) | Yes (through suggesters, various options) | No | This is a feature that provides suggestions, useful for auto-complete (e.g. while you type) |
Did-you-mean | Partial? - Maybe the suggester can be used or fuzzy search | Yes (through suggesters) | No | This is a feature that provides potential suggestions after the search (e.g. misspellings) |
Highlighting | Yes | Yes | No | |
Facets | Yes | Yes | Partial - This is already supported for Synapse tables as a custom implementation | |
Arrays | Yes | Yes | No - Not natively but could be probably worked around | This might be needed for multi-value columns |
Custom Synonyms | Yes (Index time) | Yes (Index or query time) | No | This feature can be useful to complement stemming or fuzzy search. Expanding the index/query with similar term might yield better results. |
Custom Stop words | Yes (global) | Yes (Index) | Yes (global) | |
Maintenance and scalability | Managed, auto-scale | Managed, tuning suggestions | Managed RDS | |
Synapse Tables Integration Effort | High | High | Medium | |
Additional Costs | Yes, per cluster per instance type/hour. Plus amount of data in batches sent to index. | Yes per instance type/hour. Plus size of data. | No | Elasticsearch might turn out to be cheaper than CloudSearch since the instances are priced lowered and we do not pay for sending batches to index. Setting up the cluster with the right sizing can be complex with Elasticsearch and to ensure availability it can be more expensive (e.g. dedicated master nodes, multiple availability zones and replicas). |
Integration Notes
Clearly the most flexible product is Elasticsearch, mainly because while CloudSearch is a good search index product it does not support multiple indexes per cluster (e.g. one domain per table). We could somehow make use of dynamic fields to catch all the fields (without field boosting) of a given type or try and index a table row as a single document field but field boosting wouldn’t work (does not support nested documents).
Given that AWS elastic search supports multiple indexes per cluster we could maintain one index per table, this would eliminate the use of dynamic mapping since we know the schema before-hand. Additionally while CloudSearch and Elasticsearch are very similar products, with the latter being open source and with many more features and flexibility it does not make much sense to go with the former so I would exclude CloudSearch altogether.
MySQL FTS is a (very) limited product that has the advantage of being potentially integrated with the current query engine. For example faceting might work out of the box without additional work. Since it would be part of the table indexes there is potentially little development overhead in adding it to tables including when changing the schema. There is some performance overhead added due to text processing during the transactions. The main concern is the limited options and flexibility, for example if we have a multiple columns to search we either create a separate index for each column and run an OR and somehow re-compute the ranking or all the columns need to be added to the index and all of them need to be specified in the search query (e.g. WHERE MATCH (title,body) AGAINST ('database' IN NATURAL LANGUAGE MODE), note that both title and body needs to be specified for an FTS index on <title, body>). We might end up exposing the search syntax to the client too.
Using an external search index such as Elasticsearch adds additional complexity in maintaining the index in sync, especially regarding schema changes (where the whole index might need to be rebuilt). We would need infrastructure to either periodically get the changed rows (this would mean adding a timestamp to each row, and potentially a deleted flag) or to send data and changes in a streaming fashion in order (integrations such as kinesis seem to support additions but no deletions). The biggest integration pain would be faceting and filtering and potentially supporting the existing API, while elastic search supports faceting, in order to use it we would have to replicate everything that is already done for the table query engine and make it somehow compatible with the current search results (The input search API could be different and not integrated with the SQL queries).
From the design it seems clear that they would want an integration with faceting and filtering, this might prove to be a huge task (basically it would replace the current query engine). An idea might be to index only relevant columns in elastic search, at query time we first run a search and limit to a small subset of results (e.g. 1000, this makes sense when searching for relevant documents) and apply the normal filters and faceting in the MySQL database on the subset of ids (maintaining the order).
Pros and Cons
Pro | Con | |
|---|---|---|
MySQL Full Text Search |
|
|
Elasticsearch |
|
|
CloudSearch |
|
|