Elasticsearch build sql server index and create search query failes

Question

After reading and trying several of articles and getting no result..

I want to create and elasticsearch query that returns data base results

Example:

[Step 1]: my db is [my_db] and my table name is [my_table] to build new index on localhost:9200

POST /my_index/my_type/_meta 
{
    "type":"jdbc",
    "jdbc": 
    {
        "driver":"com.microsoft.sqlserver.jdbc.SQLServerDriver",
        "url":"jdbc:sqlserver://[my_db_ip];databaseName=[my_db]",
        "user":"sa","password":"xxxxxx",
        "sql":"SELECT * FROM [my_table]",
        "poll":"5s",
        "index": "my_index",
        "type": "my_type"     
    }
}

The index creation result:

{
   "_index": "my_index",
   "_type": "my_type",
   "_id": "_meta",
   "_version": 1,
   "created": true
}

[Step 2]: The search query

POST /my_index/_search
{
    "query_string" : {
        "query" : "FreeText"
    }
}

The search result

{
   "error": "SearchPhaseExecutionException[Failed to execute phase [query], all shards failed; shardFailures....
}

what is wrong with my search query??

how can i create a query that returns results from [my_table] rows?

Manolis Manolis · Accepted Answer · 2015-06-02T12:00:48

Try the match_all query (see here for the official documentation). This will bring you all the results of my_type.

Example:

POST /my_index/my_type/_search 
{
   "query": { "match_all": {} } 
}

If you need to search for some specific term then you must pay attention to the mappings of your type and the query type that you'll use.

Update

Mappings:

From the schema of your table I understand that the below mappings for my_type would suit you well.

{
    "my_table" : {
        "properties" : {
            "orderid" : {"type" : "integer", "index" : "not_analyzed"},
            "ordername " : {"type" : "string" }
        }
    }
}

Keep in mind that if the data are already indexed you cannot change the mappings. You must reindex your data after defining the proper mapping.

Generally I'd propose you to follow the below methodology:

Create your index with the index settings that you need
Define the mappings of your type
Index your data

Do not mingle all of these steps in one and avoid leaving things in luck (like default mappings).

You can use the match query in order to search the data of a field on the document.

Example

POST /my_index/my_type/_search 
{
    "query": { 
        "match": {
           "FIELD": "TEXT"
        }
    } 
}

You can use the multi-match query in order to search the data of multiple fields of the document.

Example

POST /my_index/my_type/_search 
{
    "query": { 
        "multi_match": {
           "query":    "TEXT", 
           "fields": [ "field1", "field2" ]
        }
    } 
}

For more querying options check the official documentation on Query DSL.

Elasticsearch build sql server index and create search query failes

1 Answers