How to display index name in results using distributed tables?

[terion-name]

Example usage:

{
	"index":"global_table", 
	"profile": true,
	"query": {
			"match_phrase": {"_all": "John Doe"}
	},
	"_source": ["*"],
	"sort": [ "_score" ],
	"limit": 1000
}

Results are returned without the name of index, where tha result is actually coming from.
This is very inconvenient in general and even more when _source is empty and you are trying to find out from where this empty data came from

Is there a way to display underlying index in search results? Did not find anywhere =(

[sanikolaev]

Hi @terion-name

There's no functionality which can do it out of the box, but you can populate each table with its id/name in each row. If you make this special column columnar or stored-only the storage overhead shouldn't be significant since they both should compress it very well provided the value will be the same for all the docs in a table. For example:

Columnar:

➜  ~ mysql -P9306 -h0 -v -e "drop table if exists t1; create table t1(f text, shard_id int engine='columnar'); drop table if exists t2; create table t2 like t1; drop table if exists dist; create table dist type='distributed' local='t1' agent='127.0.0.1:9312:t2'; insert into t1 values(1, 'abc', 1); insert into t2 values(2, 'def', 2); select * from dist;"
--------------
drop table if exists t1
--------------

--------------
create table t1(f text, shard_id int engine='columnar')
--------------

--------------
drop table if exists t2
--------------

--------------
create table t2 like t1
--------------

--------------
drop table if exists dist
--------------

--------------
create table dist type='distributed' local='t1' agent='127.0.0.1:9312:t2'
--------------

--------------
insert into t1 values(1, 'abc', 1)
--------------

--------------
insert into t2 values(2, 'def', 2)
--------------

--------------
select * from dist
--------------

+------+------+----------+
| id   | f    | shard_id |
+------+------+----------+
|    1 | abc  |        1 |
|    2 | def  |        2 |
+------+------+----------+

➜  ~ curl -sX POST http://localhost:9308/search -d '
{
    "index":"dist",
    "profile": true,
    "query": {},
    "_source": ["*"],
    "sort": [ "_score" ],
    "limit": 1000
}
'|jq .
{
  "took": 1,
  "timed_out": false,
  "hits": {
    "total": 2,
    "total_relation": "eq",
    "hits": [
      {
        "_id": "1",
        "_score": 1,
        "_source": {
          "f": "abc",
          "shard_id": 1
        }
      },
      {
        "_id": "2",
        "_score": 1,
        "_source": {
          "f": "def",
          "shard_id": 2
        }
      }
    ]
  },
  "profile": null
}

Stored-only:

mysql> drop table if exists t1; create table t1(f text, shard_id string stored); drop table if exists t2; create table t2 like t1; drop table if exists dist; create table dist type='distributed' local='t1' agent='127.0.0.1:9312:t2'; insert into t1 values(1, 'abc', '1'); insert into t2 values(2, 'def', '2'); select * from dist;
--------------
drop table if exists t1
--------------

Query OK, 0 rows affected (0.01 sec)

--------------
create table t1(f text, shard_id string stored)
--------------

Query OK, 0 rows affected (0.02 sec)

--------------
drop table if exists t2
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
create table t2 like t1
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
drop table if exists dist
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
create table dist type='distributed' local='t1' agent='127.0.0.1:9312:t2'
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
insert into t1 values(1, 'abc', '1')
--------------

Query OK, 1 row affected (0.00 sec)

--------------
insert into t2 values(2, 'def', '2')
--------------

Query OK, 1 row affected (0.00 sec)

--------------
select * from dist
--------------

+------+------+----------+
| id   | f    | shard_id |
+------+------+----------+
|    1 | abc  | 1        |
|    2 | def  | 2        |
+------+------+----------+
2 rows in set (0.01 sec)
--- 2 out of 2 results in 2ms ---

mysql> desc t1;
--------------
desc t1
--------------

+----------+--------+----------------+
| Field    | Type   | Properties     |
+----------+--------+----------------+
| id       | bigint |                |
| f        | text   | indexed stored |
| shard_id | text   | stored         |
+----------+--------+----------------+
3 rows in set (0.00 sec)

➜  ~ curl -sX POST http://localhost:9308/search -d '
{
    "index":"dist",
    "profile": true,
    "query": {},
    "_source": ["*"],
    "sort": [ "_score" ],
    "limit": 1000
}
'|jq .
{
  "took": 2,
  "timed_out": false,
  "hits": {
    "total": 2,
    "total_relation": "eq",
    "hits": [
      {
        "_id": "1",
        "_score": 1,
        "_source": {
          "f": "abc",
          "shard_id": "1"
        }
      },
      {
        "_id": "2",
        "_score": 1,
        "_source": {
          "f": "def",
          "shard_id": "2"
        }
      }
    ]
  },
  "profile": null
}