Index database field value as column in elastic search

[phani546]

Hi All,

I have a scenario like following.

i have field in database table called "tempprop1" it contains value like "frontwheels : 50" etc .. like following. table name: parts (Here tempprop1 and tempprop2 are two columns in parts table and the below are the corresponding values)

tempprop1 tempprop2

frontwheels : 50 Backwheels: 20 Headgear : 2 gear type : 2

             When i index the data using river straight forward query it is indexing as follows.

tempprop1 : frontwheels : 50 , tempprop2 : backwheels: 20.

                but for our index search convenience we want to index the value (frontwheels : 50)  for tempprop1 field like following.means instead of tempprop1 field we need frontwheels :50 in index.

frontwheels : 50
backwheels : 20 

        it needs to be index with value. now my question can we achieve this scenarios using river query (like bracket notation) to elastic search index .please suggest me how to handle this index creation using above requirements.

please help me in this.

thanks

phani.

[pib]

I'm almost afraid to ask, but why not just name the columns "frontwheels" and "backwheels" to begin with?

[jprante]

It is not clear how the tables look like. It is very uncommon to store key plus value in a column and call the column "tempprop".

If you can give table definition, the column names, and a sample row, it would be clearer to me.

[phani546]

Hi jprante,

Thank you for the quick reply. please find the attached screen shot in that i mentioned whole scenario with the table structure and sample rows.I need to index records using mysql river.

Thanks and Regards phani

[jprante]

Have you checkd the MySQL commands for string operations, like SUBSTRING, SUBSTRING_INDEX and the like?

[phani546]

Hi Jprante,

I tried the SUBSTRING_INDEX function but i didn't get exact approach how to index PROPERTY1 and PROPERTY2 values , once we extract string using the function. would you please help me how to design the schema that fits exactly to my requirement and how can i frame river query to index the data to elastic search.

query :

 SELECT  `SUBSTRING_INDEX(PROPERTY1,':',2)`,`SUBSTRING_INDEX(PROPERTY2,":",2)` from temp limit 10.

but getting following exception.

error : {"error":"MapperParsingException[failed to parse]; nested: JsonParseException[Unexpected character (':' (code 58)): was expecting comma to separate OBJECT entries\n at [S ource: [B@423a346f; line: 1, column: 299]]; ","status":400}

 i am using following command to execute river script.

curl -XPUT "http://localhost:9200/_river/temp/_meta" -d @config.json

Thanks & Regards, phani

[jprante]

What are the column names? You should use sensitive names for the JSON objects.

[phani546]

Here 'PROPERTY1' and 'PROPERTY2' are the columns i applied SUBSTRING_INDEX function. please suggest me if i did wrong in the above query.

phani.

[jprante]

As said, it is very uncommon to use columns as a container for key/value attributes in a DB. It is up to you to find a strategy how to decode the data.

The SQL command must contain pseudo column names in order to define the JSON field names, as described in the README of JDBC plugin. The error you got is a hint that you did not defined correct pseudo column names for JSON field names.