Sample Data

We setup a testing environment with some sample data, the source data is stored in the following Synapse table:

We setup both a MySQL RDS instance and an AWS Elasticsearch cluster where we imported the data to perform some testing around indexing and searching. The number of records is very small (51) so we cannot gauge performance metrics.

Some example queries are taken from: https://sagebionetworks.jira.com/browse/PLFM-6876

The code used can be found in the following repository: https://github.com/marcomarasca/synapse-table-search

MySQL Setup

An RDS MySQL instance (version 8.0.16) was used (with a db.t3.small instance) and a dedicated table was created importing the data from the Synapse table: only STRING, STRING_LIST and LARGETEXT columns were imported. This totaled to 46 columns. The table structure is very similar to a real Synapse table (same datatypes were used).

MySQL limits the number of secondary indexes to 64, and the total number of columns in one index to 16. We added a special column that contains the concatenated values of all the columns and created a FULL TEXT index on that particular column. FULL TEXT indexes for each column were also added (so that ORed queries can be run against each column) but for each column a separate score will be computed and the optimizer might choose not to use the indexes (not enough data to see how this would work).

The RDS instance with the data can be reached (through the VPN) at:

host: dev-marco-db.cdusmwdhqvso.us-east-1.rds.amazonaws.com
db: devmarco
user: devmarcouser
password: platform
table: SEARCH_TEST

Once connected full text queries queries can be executed such as:

SELECT MATCH(CONTENT_TEXT) AGAINST('tumor') as SCORE, S.* FROM SEARCH_TEST S WHERE MATCH(CONTENT_TEXT) AGAINST('tumor')
SELECT MATCH(CONTENT_TEXT) AGAINST('peripher* tumor' IN BOOLEAN MODE) as SCORE, S.* FROM SEARCH_TEST S WHERE MATCH(CONTENT_TEXT) AGAINST('peripher* tumor' IN BOOLEAN MODE)

See MySQL :: MySQL 8.0 Reference Manual :: 14.9 Full-Text Search Functions for documentation on the syntax.

Elasticsearch Setup

We setup an AWS elasticsearch cluster with a single data node (t3.small.elasticsearch instance) and no dedicated master node in a VPC. The setup was initially done using fine grained access with a IAM user to perform the import. Later the authentication was switched to the internal user management with a dedicated user so that queries can be run from the command line for testing.

The instance can be reached (through the VPN) at:

Endpoint: https://vpc-tables-search-test-es7bt4peajix4wokysfxfldqoy.us-east-1.es.amazonaws.com
Kibana Console: https://vpc-tables-search-test-es7bt4peajix4wokysfxfldqoy.us-east-1.es.amazonaws.com/_plugin/kibana/
user: devmarco
password: Platform?es2021
indexes: syn26050977_index_default, syn26050977_index_eng

Queries can be executed using curl, e.g.

See Full-Text Queries for documentation on the syntax.

Only STRING, STRING_LIST and LARGETEXT columns were imported in the indexes, no static mapping was performed beforehand and we let elasticsearch dynamically map the fields (all the fields that were not null were automatically added as TEXT with a KEYWORD field as well). Multi values column were set in the document as arrays.

The syn26050977_index_default index is “as-is” from just submitting the documents, the syn26050977_index_eng was instead configured to use as default analyzer the pre-configured English analyzer that includes an English stemmer (See Language analyzers | Elasticsearch Guide [7.16] | Elastic: this is the Elastic.co documentation as I could not find it in AWS or opendistro or opensearch docs).

Search Results

In the following some search results with the following configurations: