# Adding more details - ElasticSearch query to identify relationships between Hive table fields using search for keywords **URL:** \ **Category:** Elasticsearch\ **Tags:** es-hadoop\ **Created:** [February 3, 2017, 4:46pm UTC](https://discuss.elastic.co/t/adding-more-details-elasticsearch-query-to-identify-relationships-between-hive-table-fields-using-search-for-keywords/73885 "2017-02-03T16:46:16Z")\ **Posts on this page:** 3\ **Page:** 1
**Author:** ![annanyan](https://avatars.discourse-cdn.com/v4/letter/a/a9a28c/32.png) [@annanyan](https://discuss.elastic.co/u/annanyan)\ **Post date:** [February 3, 2017, 4:46pm UTC](https://discuss.elastic.co/t/adding-more-details-elasticsearch-query-to-identify-relationships-between-hive-table-fields-using-search-for-keywords/73885/1 "2017-02-03T16:46:16Z")
Due to restrictions in edit, adding a separate topic for providing additional details. Sorry for inconvenience. Original post link: > [@ElasticSearch query to identify relationships between Hive table fields using search for keywords](https://discuss.elastic.co/t/elasticsearch-query-to-identify-relationships-between-hive-table-fields-using-search-for-keywords/73528): > > We are new to Elasticsearch and Kibana. We are using Elasticsearch 5.0.0 to identify relationships between Hive table fields in a database by searching across all columns for specific keywords. We are open to use queries or Elasticsearch APIs or any other solutions to meet the requirement. We have uploaded details into a single index in Elasticsearch by installing Elasticsearch-hadoop-5.1.1 library and creating external Hive tables in Elasticsearch. We are using \_type column for storing Hive t… We have used the \_type field to store the Hive Table Names for the example index movies. movies/movie\_intrnl movies/movie\_shows Data used for Hive Tables stored in Elasticsearch Index movies: movie\_intrnl: Title1,1,Action1,2003,Action Title2,2,dire2,2007,Crime Title3,3,dire3,2004,CrimeThriller Title4,4,Drama1,2003,Drama Title5,5,Action2,2005,Action Title6,6,Drama4,2007,BiographyDrama movie\_shows: Action1,Title1,1,2017-04-03 00:00:00,Action Theatre2,Title2,2,2016-05-07 00:00:00,Crime Theatre3,Title2,3,2015-06-04 00:00:00,CrimeThriller Drama4,Title4,4,2014-08-03 00:00:00,Drama Action5,Title1,5,2019-09-05 00:00:00,Action Theatre6,Title6,6,2017-10-07 00:00:00,BiographyDrama Elasticsearch Query to get the distinct table names (\_type field in Elasticsearch): ``` GET /movies/_search?pretty { "size": 0, "_source": false, "query": { "query_string": { "analyze_wildcard": true, "query": "*Drama*" } }, "aggs": { "distinct_tables": { "terms": { "field": "_type" } } } } ``` We got the response given below for getting the distinct tables using the following tags: aggregations -\> buckets -\> key movie\_intrnl movie\_shows Response for the ES Query to get distinct table names: ``` { "took": 2, "timed_out": false, "_shards": { "total": 5, "successful": 5, "failed": 0 }, "hits": { "total": 4, "max_score": 0, "hits": [] }, "aggregations": { "distinct_tables": { "doc_count_error_upper_bound": 0, "sum_other_doc_count": 0, "buckets": [ { "key": "movie_intrnl", "doc_count": 2 }, { "key": "movie_shows", "doc_count": 2 } ] } } } ``` We are using the highlight feature to identify matching field names for the search query. ES Query to get matching table names and column names for a search pattern: ``` GET /movies/_search?pretty { "_source": false, "query": { "query_string": { "analyze_wildcard": true, "query": "*Drama*" } }, "highlight": { "fields": { "*": {} }, "require_field_match": false, "fragment_size": 2147483647 } } ``` We got the response below for getting the matching table names and column names. Response - 1st matching document: "\_type": "movie\_shows", "highlight": { "theatre": [ "_Drama4_" ], "genres": [ "_Drama_" ] Response - 2nd matching document: "\_type": "movie\_shows", "highlight": { "genres": [ "_BiographyDrama_" ] } Response - 3rd matching document "\_type": "movie\_intrnl", "highlight": { "director": [ "_Drama1_" ], "genres": [ "_Drama_" ] Response - 4th matching document "\_type": "movie\_intrnl", "highlight": { "director": [ "_Drama4_" ], "genres": [ "_BiographyDrama_" ] But this approach does not give the distinct table names and column names across all the matched documents. Expected response as per the requirement is given below. "\_type": "movie\_shows" "theatre" "genres" "\_type": "movie\_intrnl" "director" "genres" Response for search query to get matching column names and table names: ``` { "took": 3, "timed_out": false, "_shards": { "total": 5, "successful": 5, "failed": 0 }, "hits": { "total": 4, "max_score": 1, "hits": [ { "_index": "movies", "_type": "movie_shows", "_id": "AVoEcEMrxAEXKBamIeYk", "_score": 1, "highlight": { "theatre": [ "Drama4" ], "genres": [ "Drama" ] } }, { "_index": "movies", "_type": "movie_shows", "_id": "AVoEcEMrxAEXKBamIeYm", "_score": 1, "highlight": { "genres": [ "BiographyDrama" ] } }, { "_index": "movies", "_type": "movie_intrnl", "_id": "AVoEbkPFxAEXKBamIeYe", "_score": 1, "highlight": { "director": [ "Drama1" ], "genres": [ "Drama" ] } }, { "_index": "movies", "_type": "movie_intrnl", "_id": "AVoEbkPFxAEXKBamIeYg", "_score": 1, "highlight": { "director": [ "Drama4" ], "genres": [ "BiographyDrama" ] } } ] } } ``` In the query given below, we have tried aggregation on \_type column to get distinct table names and with sub-aggregation by using a sample static field “genres”. However, since columns from the search result is dynamic, we are looking for a mechanism to use sub-aggregation on top of highlight field results to get the distinct column names within each identified distinct table name. ES Query tried to get distinct table names and column names: ``` GET /movies/_search?pretty { "size": 0, "_source": false, "query": { "query_string": { "analyze_wildcard": true, "query": "*Drama*" } }, "aggs": { "distinct_tables": { "terms": { "field": "_type" }, "aggs" : { "unique_set_2": { "terms": { "field": "genres.keyword" } } } } } } ``` ---
**Author:** ![james.baiera](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/james.baiera/32/10209_2.png) [@james.baiera](https://discuss.elastic.co/u/james.baiera)\ **Post date:** [February 3, 2017, 7:05pm UTC](https://discuss.elastic.co/t/adding-more-details-elasticsearch-query-to-identify-relationships-between-hive-table-fields-using-search-for-keywords/73885/2 "2017-02-03T19:05:54Z")
Please post this as a reply on the original thread. ---
**Author:** ![james.baiera](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/james.baiera/32/10209_2.png) [@james.baiera](https://discuss.elastic.co/u/james.baiera)\ **Post date:** [February 3, 2017, 7:06pm UTC](https://discuss.elastic.co/t/adding-more-details-elasticsearch-query-to-identify-relationships-between-hive-table-fields-using-search-for-keywords/73885/3 "2017-02-03T19:06:02Z")