ElasticSearch data modeling with Mysql

Question

I'm studying Elastic search and trying to modeling with mysql tables. I have Mysql tables below for example.

Book Table

id
title
abstract
publisher_id

ex) book table

id | title | abstract | publisher_id

3 | book title A | A book about elastic search. | 12

Authors Table

id
name
country
book_id(id from Book table)

ex) authors table

id | name | country | book_id

1 | Alex | Korea | 3

2 | John | USA | 3

author could be more than one person.

Publisher Table

id
name

ex) publisher table

id | name

12 | Packt pub

In my thoughts, i could convert like below for elastic search index.

Book index

id int
title string
abstract string
authors array (id from Authors index. Authors could be more than one.)
publisher int (id form publisher index)

Authors index

id int
name string
country string

Publisher index

id int
name string

What i need to do is, search for Book title and abstract and get author's id. And then show authors list. For mysql, i would do like this.

Select * from authors where id in (select authors_id from book where match(title,abstract) against('${keyword}' IN BOOLEAN MODE))

How can i do this for elastic search? Is there a better way to modeling? and I also want to know how to query First search authors ids from book index and search with these ids from authors again?? or any other solution???

first You should read some document about nosql database modeling. quick answer is you should put all data in one index. the query will be easier. you can AND in term or match query. — hamid bayat
– hamid bayat, Commented Aug 10, 2020 at 12:54
Hamid is right, get some good reads about document based noSql. When designing an index you need to do an paradigm change. Instead of thinking about the sorage first, think about the queries you need to perform on the data and optimise for the queries and somtimes you need to keep data redundant, what is absolutely fine here. Have fun! — ibexit
– ibexit, Commented Aug 10, 2020 at 18:10
@Sehun, I totally agree with hamid and ibexit and in this case you should just store all the data in same index, added a sample related small example in my answer to make it clear. — Amit
– Amit, Commented Aug 11, 2020 at 10:14
Thank you for all your comments. I thought wrong way.... Now i understand. :) — Sehun Park
– Sehun Park, Commented Aug 11, 2020 at 23:45

Amit · Accepted Answer · 2020-08-11 10:13:35Z

1

This is easy to achieve using a single index in Elasticsearch(ES) as pointed by other ES experts and I may not be able to give the proper ES query but my below example gives you idea, on how to model your data and query it.

Index mapping

{
  "mappings": {
    "properties": {
      "title": {
        "type": "text"
      },
      "abstract":{
        "type" : "text"
      },
      "author" :{
        "type" : "text",
        "fielddata" : true // instead of this, you can use `keyword` field for better perf, this is just for ex
      }
    }
  }
}

Index sample docs

{
  "title" : "hello world",
  "abstract" : "hello world is common in computer programming",
  "author" : ["sehun, stackoverflow"]
}

{
  "title" : "foo bar",
  "abstract" : "foo bar is common in computer programming",
  "author" : ["opster, stackoverflow"]
}

Search query to search on title and abstract and agg on author field

{
  "query": {
    "multi_match": {
      "query": "common",
      "fields": [
        "title",
        "abstract"
      ]
    }
  },
  "aggs": {
    "Cities": {
      "terms": {
        "field": "author"
      }
    }
  }
}

Search results

hits": [
      {
        "_index": "internaledgepre",
        "_type": "_doc",
        "_id": "1",
        "_score": 0.18232156,
        "_source": {
          "title": "foo bar",
          "abstract": "foo bar is common in computer programming",
          "author": [
            "opster, stackoverflow"
          ]
        }
      },
      {
        "_index": "internaledgepre",
        "_type": "_doc",
        "_id": "2",
        "_score": 0.18232156,
        "_source": {
          "title": "hello world",
          "abstract": "hello world is common in computer programming",
          "author": [
            "sehun, stackoverflow"
          ]
        }
      }
    ]
  },
  "aggregations": {
    "Cities": {
      "doc_count_error_upper_bound": 0,
      "sum_other_doc_count": 0,
      "buckets": [
        {
          "key": "stackoverflow",
          "doc_count": 2
        },
        {
          "key": "opster",
          "doc_count": 1
        },
        {
          "key": "sehun",
          "doc_count": 1
        }
      ]
    }
  }

answered Aug 11, 2020 at 10:13

Amit

32.5k7 gold badges68 silver badges101 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sehun Park Over a year ago

Thank you for your perfect answer. However, I think i cannot put all of data from MySQL. So is it okay to use both Elastic Search and MySQL? Finding the book from Elastic Search and find author information from MySQL.

Amit Over a year ago

@SehunPark, you can use mysql as source of truth but why to find author info from mysql and add another hop in the transaction

Suman · Accepted Answer · 2020-08-11 20:45:17Z

1

NoSQL doesn’t support joins like SQL supports. So all data should be indexed in one document.

Do go through nosql datamodelling, architecture, significances and also learn how it’s different from SQL.

answered Aug 11, 2020 at 20:45

Suman

8688 silver badges19 bronze badges

Collectives™ on Stack Overflow

ElasticSearch data modeling with Mysql

id | title | abstract | publisher_id

id | name | country | book_id

id | name

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

id | title | abstract | publisher_id

id | name | country | book_id

id | name

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related