Skip to end of banner
Go to start of banner

Sample Data

Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 4 Next »

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

https://www.synapse.org/#!Synapse:syn26050977/tables/

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.

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

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 run using curl, e.g.

curl -XGET -u 'devmarco:Platform?es2021' 'https://vpc-tables-search-test-es7bt4peajix4wokysfxfldqoy.us-east-1.es.amazonaws.com/syn26050977_index_default/_search?q=tumor&pretty=true'

Only the 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. 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 (See https://www.elastic.co/guide/en/elasticsearch/reference/7.x/analysis-lang-analyzer.html#english-analyzer Note that this is the Elastic.co documentation as I could not find it in AWS or opendistro or opensearch docs).

  • No labels