# Combining three different indices into one available to be queried by ODBC with Tableau **URL:** \ **Category:** Elasticsearch\ **Created:** [May 29, 2020, 6:28am UTC](https://discuss.elastic.co/t/combining-three-different-indices-into-one-available-to-be-queried-by-odbc-with-tableau/234864 "2020-05-29T06:28:29Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![saif3r](https://avatars.discourse-cdn.com/v4/letter/s/49beb7/32.png) [@saif3r](https://discuss.elastic.co/u/saif3r)\ **Post date:** [May 29, 2020, 6:28am UTC](https://discuss.elastic.co/t/combining-three-different-indices-into-one-available-to-be-queried-by-odbc-with-tableau/234864/1 "2020-05-29T06:28:29Z")
Hello, I have three different indices called: **first, second, third**. **first** is the main index, **second** references to **main** and **third** references to **second** index using id fields. The sample data looks like this: **first** - 2 sample documents (two different id's) ``` {"first.id": 1, "first.desc": "first_1"} {"first.id": 2, "first.desc": "first_2"} ``` **second** - 2 sample documents (referening to two different first.id) ``` {"second.id": 1, "second.desc": "second_1", "first.id": 1} {"second.id": 2, "second.desc": "second_2", "first.id": 2} ``` **third** - 2 sample documents (refering to same second.id document) ``` {"third.id": 1, "second.desc": "third_1", "second.id": 1} {"third.id": 2, "second.desc": "third_2", "second.id": 1} ``` I would like to automaticaly create an index that would contain documents from all three indices and is automatically updated whenever new document is indexed in any of the indices. Sample of such index would contain below records: **combined index** - would contain 3 documents in total. ``` {"first.id": 1, "first.desc": "first_1", "second.id": 1, "second.desc": "second_1", "third.id": 1, "second.desc": "third_1"} {"first.id": 1, "first.desc": "first_1", "second.id": 1, "second.desc": "second_1", "third.id": 2, "second.desc": "third_2"} {"first.id": 2, "first.desc": "first_2", "second.id": 2, "second.desc": "second_2"} ``` This is to make sure that our data is denormalized so that it can be pulled by Tableau using ODBC. Is that possible using existing mechanism? ---
**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\ **Post date:** [May 29, 2020, 6:39am UTC](https://discuss.elastic.co/t/combining-three-different-indices-into-one-available-to-be-queried-by-odbc-with-tableau/234864/2 "2020-05-29T06:39:13Z")
You might be able to do that with [https://www.elastic.co/guide/en/elasticsearch/reference/current/transform-apis.html](https://www.elastic.co/guide/en/elasticsearch/reference/current/transform-apis.html) ---
**Author:** ![saif3r](https://avatars.discourse-cdn.com/v4/letter/s/49beb7/32.png) [@saif3r](https://discuss.elastic.co/u/saif3r)\ **Post date:** [May 29, 2020, 1:44pm UTC](https://discuss.elastic.co/t/combining-three-different-indices-into-one-available-to-be-queried-by-odbc-with-tableau/234864/3 "2020-05-29T13:44:03Z")
Thank you @warkolm, I've looked at the link you've sent and came up with simple transformation using this data: ``` PUT first {"mappings":{"properties":{"first_id":{"type":"short"},"first_desc":{"type":"keyword"}}}} PUT second {"mappings":{"properties":{"second_id":{"type":"short"},"second_desc":{"type":"keyword"},"first_id":{"type":"short"}}}} PUT third {"mappings":{"properties":{"third_id":{"type":"short"},"third_desc":{"type":"keyword"},"second_id":{"type":"short"}}}} PUT combined_index {"mappings":{"properties":{"first_id":{"type":"short"},"first_desc":{"type":"keyword"},"second_id":{"type":"short"},"second_desc":{"type":"keyword"},"third_id":{"type":"short"},"third_desc":{"type":"keyword"}}}} ``` Sample data: ``` POST _bulk {"index": {"_index": "first", "_id": "1"}} {"first_id": 1, "first_desc": "first_1"} {"index": {"_index": "first", "_id": "2"}} {"first_id": 2, "first_desc": "first_2"} {"index": {"_index": "second", "_id": "1"}} {"second_id": 1, "second_desc": "second_1", "first_id": 1} {"index": {"_index": "second", "_id": "2"}} {"second_id": 2, "second_desc": "second_2", "first_id": 2} {"index": {"_index": "third", "_id": "1"}} {"third_id": 1, "third_desc": "third_1", "second_id": 1} {"index": {"_index": "third", "_id": "2"}} {"third_id": 2, "third_desc": "third_2", "second_id": 1} ``` I created this basic transformation: ``` PUT _transform/test_combined { "source": { "index": ["first", "second", "third"] }, "pivot": { "group_by": { "test": { "terms": { "field": "first_id" } } }, "aggregations": { "min_id": { "min": { "field": "first_id" } } } }, "description": "Transform test", "dest": { "index": "combined_index" } } ``` _(there is an error in this transform related to my fields, but ill fix that by expanding the index with additional fields)_ Here is the result of `GET combined_index/_search` ``` { "took" : 0, "timed_out" : false, "_shards" : { "total" : 1, "successful" : 1, "skipped" : 0, "failed" : 0 }, "hits" : { "total" : { "value" : 2, "relation" : "eq" }, "max_score" : 1.0, "hits" : [ { "_index" : "combined_index", "_type" : "_doc", "_id" : "AGvuZWuqqz7c5ytICzX5Z74AAAAAAAAA", "_score" : 1.0, "_source" : { "min_id" : 1.0, "test" : 1 } }, { "_index" : "combined_index", "_type" : "_doc", "_id" : "AA3tqz9zEwuio1D73_EArycAAAAAAAAA", "_score" : 1.0, "_source" : { "min_id" : 2.0, "test" : 2 } } ] } } ``` Now I'm trying to figure out how exacty match values from other indices based on id field. I haven't found anything so far, except for this this code from ML webinar: [https://gist.github.com/stevedodson/c80d245a5dc6ae8cc93e9f9d25897aef](https://gist.github.com/stevedodson/c80d245a5dc6ae8cc93e9f9d25897aef) But understanding how **Transform raw data into entity-centric index** works exactly, is difficult to grasp without some baseline. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [June 26, 2020, 1:44pm UTC](https://discuss.elastic.co/t/combining-three-different-indices-into-one-available-to-be-queried-by-odbc-with-tableau/234864/4 "2020-06-26T13:44:13Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.