1

My problem is that I need to perform sorting from data coming from two different datasource, a MySQL database which contains information about some products and a PostgreSQL that contains some metrics linked to these products.

Because the data resides in two different datasources I cannot out of the box come up with a single performant query that would make the ordering (pagination) at database level. I need to make two different queries and then manually merge the data and perform sorting and pagination code side. I would like to avoid as much as possible having to create a custom pagination system and a manual data merging and as much as possible delegate this job to the underlying database.

This is where I thought a system such as ElasticSearch (or Solr, but ES seems to be easier to use) could help.

1) Does ES provide tools or mechanism to merge 2 datasource into 1 document ? Or this job needs to be done by a 3rd party tool that will peridocally pull the data from both datasource and create / update the documents?

2) I'm correct to assume that having 2 indices (or 2 different doc type) is pointless in my case since ES cannot perform join queries ?

3) Apart from creating one single document what other solution do I have that ES can help with? Is it possible 'somehow' that with having datasource1 data in an index1 and datasource2 data in an index2 I can perform multiple search queries using both the index at the same time (since join is a no go).

Marco
  • 561
  • 4
  • 14

1 Answers1

0

Does ES provide tools or mechanism to merge 2 datasource into 1 document ? Or this job needs to be done by a 3rd party tool that will peridocally pull the data from both datasource and create / update the documents?

There are two approaches to accomplish this :

  1. An ETL process (Extract, Transform, Load) to load data from both sources into one single document. In the Elastic world you can use logstash to accomplish this
  2. Data Virtualization is supposed to do this without the need to copy the data

3) Apart from creating one single document what other solution do I have that ES can help with? Is it possible 'somehow' that with having datasource1 data in an index1 and datasource2 data in an index2 I can perform multiple search queries using both the index at the same time (since join is a no go).

It's very easy to perform a single query through multiple indices. Answers here

nihilman
  • 25
  • 6