Can you join indexes with different fields using an index pattern?

Elastic Stack Elasticsearch
1

Problem:

I have two indexes, stage-ath and stage-athena. I defined my index pattern as stage-ath* to cover both index fields.

stage-ath has customer_name + customer_id
stage-athena has only customer_id and other fields which are not important in this example.

When creating a visualization table on stage-ath* index pattern, I cannot see any value for customer_name, it says "No Result Found".

Questions:

  1. Is there any way to use index patterns to kind of join the information from both tables with one another?
  2. Is there any change I need to make to my mapping for my two indexes?

Mappings:

Stage-athena map:

PUT /stage-athena/
PUT /stage-athena/_mapping
{
  "properties" : {
    "@timestamp" : {
      "type" : "date"
    },
    "@version" : {
      "type" : "text",
      "fields" : {
        "keyword" : {
          "type" : "keyword",
          "ignore_above" : 256
        }
      }
    },
    "capture_time" : {
      "type" : "date"
    },
    "color" : {
      "type" : "text",
      "fields" : {
        "keyword" : {
          "type" : "keyword",
          "ignore_above" : 256
        }
      }
    },
    "customer_id" : {
      "type" : "text",
      "fields" : {
        "keyword" : {
          "type" : "keyword",
          "ignore_above" : 256
        }
      }
    },
    "direction" : {
      "type" : "text",
      "fields" : {
        "keyword" : {
          "type" : "keyword",
          "ignore_above" : 256
        }
      }
    },
    "duration_hour" : {
      "type" : "long"
    },
    "farm_id" : {
      "type" : "text",
      "fields" : {
        "keyword" : {
          "type" : "keyword",
          "ignore_above" : 256
        }
      }
    },
    "firmware_version" : {
      "type" : "text",
      "fields" : {
        "keyword" : {
          "type" : "keyword",
          "ignore_above" : 256
        }
      }
    },
    "hardware_version" : {
      "type" : "text",
      "fields" : {
        "keyword" : {
          "type" : "keyword",
          "ignore_above" : 256
        }
      }
    },
    "machine_id" : {
      "type" : "text",
      "fields" : {
        "keyword" : {
          "type" : "keyword",
          "ignore_above" : 256
        }
      }
    },
    "phase_id" : {
      "type" : "text",
      "fields" : {
        "keyword" : {
          "type" : "keyword",
          "ignore_above" : 256
        }
      }
    },
    "record_type" : {
      "type" : "text",
      "fields" : {
        "keyword" : {
          "type" : "keyword",
          "ignore_above" : 256
        }
      }
    },
    "row_captured" : {
      "type" : "long"
    },
    "row_number" : {
      "type" : "text",
      "fields" : {
        "keyword" : {
          "type" : "keyword",
          "ignore_above" : 256
        }
      }
    },
    "row_session_id" : {
      "type" : "text",
      "fields" : {
        "keyword" : {
          "type" : "keyword",
          "ignore_above" : 256
        }
      }
    },
    "tomato_count" : {
      "type" : "long"
    },
    "type" : {
      "type" : "text",
      "fields" : {
        "keyword" : {
          "type" : "keyword",
          "ignore_above" : 256
        }
      }
    },
    "upload_time" : {
      "type" : "date"
    },
    "uuid" : {
      "type" : "text",
      "fields" : {
        "keyword" : {
          "type" : "keyword",
          "ignore_above" : 256
        }
      }
    },
    "version" : {
      "type" : "text",
      "fields" : {
        "keyword" : {
          "type" : "keyword",
          "ignore_above" : 256
        }
      }
    }
  }

Stage-ath mapping:

{
    "properties":{ 
        "customer_id":{
            "type" : "text",
            "fields" : {
                "keyword" : {
                    "type" : "keyword",
                    "ignore_above" : 256
                }
            }
        }, 
        "customer_name":{
            "type" : "text",
            "fields" : {
                "keyword" : {
                    "type" : "keyword",
                    "ignore_above" : 256
                }
            }
        }
    }
}

Index pattern:

image
image.png1067×548 45.6 KB

Kibana Visualization Result:

image
image.png1795×687 49.1 KB

Note: stage-ath* index pattern includes fields from both stage-ath and stage-athena indexes in the above Kibana configurations.

2

Bump

3

Elasticsearch does not support joins so I am not sure you can do what you want. I suspect you may show aggregations across fields from different types of documents, but if you filter on a field that only exists in one index that will not necessarily have the effect you want.

1 Like
4

Thanks for your suggestion. The problem was I have two indexes, and I kept pushing Elasticsearch to act like a relational database to join/merge these two indexes using an index pattern that could cover both indexes. Well, Elasticsearch is a search engine, and to do what I wanted to do with it doesn't make sense.

Solution:

My data sources were coming from AWS Athena and a Backend API containing customer data. I wrote a Python script (which used data frames from pandas library) which would receive the missing information from both data sources and merge them on the same columns existing in both tables. After doing this, I ended up having a table cotaining the merged information from both Athena and my API, I then sent data to Elasticsearch for indexing and built my visualizations in Kibana with no problem.

Hope this helps others out.

1 Like
5

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.