What is the best way to index data on elasticsearch?

Question

I have 4 tables:

country
state
city
address

These tables are related by ids where country is the top parent:

state.countryId
city.stateId
address.cityId

I want to integrate elastic search on my application and want to know what is the best way to index these table?

Should i create 1 index for each tables so that i have 1 index for each of country, state, city and address?

Or should i denormalize the tables and create only 1 index and store all the data with redundancy?

Val · Accepted Answer · 2016-05-11 11:20:16Z

2

ES is not afraid of redundancy in your data, so I would clearly denormalize so that each document represents one address like this:

{
    "country_id": 1,
    "country_name": "United Stated of America",
    "state_id": 1,
    "state_name": "California"
    "state_code": "CA",
    "city_id": 1,
    "city_name": "San Mateo"
    "zip_code": 94402,
    "address": "400 N El Camino Real"
}

You can then aggregate your data on whatever city, state, country field you wish.

Your mileage may vary as it ultimately depends on how you want to query/aggregate your data, but it's much easier to query address data like this in a single index instead of hitting several indices.

answered May 11, 2016 at 11:20

Val

218k14 gold badges377 silver badges384 bronze badges

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

2 Comments

user2707590 Over a year ago

Right, i was going for something like this too, but was a bit hesitant to decide... i have one question though... what if you need the individual country or state or city records? do you store it like this?

{     "country_id": 1,     "country_name": "United States of America",     "state_id": ,     "state_name":      "state_code": ,     "city_id": ,     "city_name":      "zip_code": ,     "address":  }

Val Over a year ago

You could decide to only retrieve the country_* fields for instance.

NikoNyrh · Accepted Answer · 2016-05-11 20:51:45Z

0

I like Val's answer, it is the most straight forward option. But if you really want to reduce duplication (for example to minimize size on disk) you could use parent-child mapping. It will make indexing and querying a bit more verbose though. I still sugges to go with "flat" mapping.

You asked "what if you need the individual country or state or city records?", I'd recommend to add an additional field (not_analyzed or integer) which would indicate which level of hierarchy this document represents. It is fine not to have fields which correspond to lower levels of hierarchy. This way you could easily have a filter on just searching states or countries.

answered May 11, 2016 at 20:51

NikoNyrh

4,2084 gold badges21 silver badges37 bronze badges

Comments

Eyal.Dahari · Accepted Answer · 2016-05-13 14:35:20Z

0

Here is a very useful article by @adrien-grand which elaborates on the subject of the trade-offs between creating many indexes, or less indexes and many types.

Hope it helps!

answered May 13, 2016 at 14:35

Eyal.Dahari

7706 silver badges14 bronze badges

Collectives™ on Stack Overflow

What is the best way to index data on elasticsearch?

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related