# Using sql server queries in elasticsearch 5.6.3

**URL:** <https://discuss.elastic.co/t/using-sql-server-queries-in-elasticsearch-5-6-3/104027>\
**Category:** Logstash\
**Created:** [October 15, 2017, 3:23pm UTC](https://discuss.elastic.co/t/using-sql-server-queries-in-elasticsearch-5-6-3/104027 "2017-10-15T15:23:30Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![parsa](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/parsa/32/23227_2.png) [@parsa](https://discuss.elastic.co/u/parsa)\
**Post date:** [October 15, 2017, 3:23pm UTC](https://discuss.elastic.co/t/using-sql-server-queries-in-elasticsearch-5-6-3/104027/1 "2017-10-15T15:23:30Z")

</div>

How can I use sql server queries in elastic search 5.6.3?  
I don't know anything about this process.  
Just I could install elastic search on windows successfully.  
please help for more details.

---

<div class="post-metadata">

**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\
**Post date:** [October 15, 2017, 3:27pm UTC](https://discuss.elastic.co/t/using-sql-server-queries-in-elasticsearch-5-6-3/104027/2 "2017-10-15T15:27:18Z")

</div>

Elasticsearch is not a relational database and does not have a SQL interface, so you can not use SQL queries. You query Elasticsearch through its [query DSL](https://www.elastic.co/guide/en/elasticsearch/reference/5.6/query-dsl.html).

---

<div class="post-metadata">

**Author:** ![parsa](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/parsa/32/23227_2.png) [@parsa](https://discuss.elastic.co/u/parsa)\
**Post date:** [October 15, 2017, 4:40pm UTC](https://discuss.elastic.co/t/using-sql-server-queries-in-elasticsearch-5-6-3/104027/3 "2017-10-15T16:40:52Z")

</div>

I mean how can I connect elasticsearch to sql server?  
AS this link : [http://hintdesk.com/how-to-connect-elasticsearch-to-ms-sql-server/](http://hintdesk.com/how-to-connect-elasticsearch-to-ms-sql-server/)  
But jdbc importer not work for elasticsearch 5

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [October 15, 2017, 5:00pm UTC](https://discuss.elastic.co/t/using-sql-server-queries-in-elasticsearch-5-6-3/104027/4 "2017-10-15T17:00:15Z")

</div>

You can use logstash.

Note that you don’t really connect elasticsearch to the database but you use an ETL to extract data from the SQL DB transform to Json and load in elasticsearch.

I shared my thoughts here: [http://david.pilato.fr/blog/2015/05/09/advanced-search-for-your-legacy-application/](http://david.pilato.fr/blog/2015/05/09/advanced-search-for-your-legacy-application/)

---

<div class="post-metadata">

**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\
**Post date:** [October 15, 2017, 5:00pm UTC](https://discuss.elastic.co/t/using-sql-server-queries-in-elasticsearch-5-6-3/104027/5 "2017-10-15T17:00:25Z")

</div>

Then you can use Logstash and its [jdbc input plugin](https://www.elastic.co/guide/en/logstash/current/plugins-inputs-jdbc.html).

---

<div class="post-metadata">

**Author:** ![parsa](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/parsa/32/23227_2.png) [@parsa](https://discuss.elastic.co/u/parsa)\
**Post date:** [October 15, 2017, 7:09pm UTC](https://discuss.elastic.co/t/using-sql-server-queries-in-elasticsearch-5-6-3/104027/6 "2017-10-15T19:09:15Z")

</div>

Thank you @Christian_Dahlqvist @dadoonet for guides.  
When I try to install by this way:  
bin/logsatsh-plugin install logstash-input-jdbc  
I get this below error:

> The system cannot find the path specified.  
> "could not find jruby in C:\Program Files\logstash-5.6.3\vendor\jruby"

But this path exists.  
What is wrong

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [October 15, 2017, 7:22pm UTC](https://discuss.elastic.co/t/using-sql-server-queries-in-elasticsearch-5-6-3/104027/7 "2017-10-15T19:22:13Z")

</div>

I don’t know but for sure on windows I’d avoid dir with space in their name.

Try by moving to another dir

---

<div class="post-metadata">

**Author:** ![parsa](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/parsa/32/23227_2.png) [@parsa](https://discuss.elastic.co/u/parsa)\
**Post date:** [October 15, 2017, 7:39pm UTC](https://discuss.elastic.co/t/using-sql-server-queries-in-elasticsearch-5-6-3/104027/8 "2017-10-15T19:39:38Z")

</div>

Thank you so much. I moved "logstash-5.6.3" folder to d:\ and then I ran this command:

`bin/logsatsh-plugin install logstash-input-jdbc`

and it installed.Thank you.

---

<div class="post-metadata">

**Author:** ![parsa](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/parsa/32/23227_2.png) [@parsa](https://discuss.elastic.co/u/parsa)\
**Post date:** [October 18, 2017, 6:42pm UTC](https://discuss.elastic.co/t/using-sql-server-queries-in-elasticsearch-5-6-3/104027/9 "2017-10-18T18:42:12Z")

</div>

I put _jdbc42.jar_ file as `jdbc_driver_library` in this path:

> D:\logstash-5.6.3\tools\ingest-converter\build\libs\sqljdbc42.jar

As the [jdbc input plugin](https://www.elastic.co/guide/en/logstash/current/plugins-inputs-jdbc.html) of logstash I create below file in bin path of logstash :

# file: simple-out.conf

input {  
jdbc {  
jdbc\_connection\_string =\> "jdbc:sqlserver://localhost:1433/user=Elasticsearch;password=parsaparsa"  
jdbc\_user =\> "Elasticsearch"  
jdbc\_validate\_connection =\> true  
jdbc\_driver\_library =\> "/tools/ingest-converter/build/libs/sqljdbc42.jar"  
jdbc\_driver\_class =\> "com.microsoft.sqlserver.jdbc.SQLServerDriver"  
statement =\> "SELECT \* from Video"  
}  
}  
output {  
elasticsearch {  
protocol =\> http  
index =\> "Video"  
document\_type =\> "video"  
document\_id =\> "%{uid}"  
host =\> "localhost:9200"  
}  
}

And I run this command at cmd :

` bin/logstash -f simple-out.conf Logstash startup`

first : I get this error

> ERROR: Unknown command 'Logstash'

second : is my conf file OK or what is wrong in my work?

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [October 18, 2017, 6:56pm UTC](https://discuss.elastic.co/t/using-sql-server-queries-in-elasticsearch-5-6-3/104027/10 "2017-10-18T18:56:26Z")

</div>

The command you need to run is

```
bin/logstash -f simple-out.conf
```

---

<div class="post-metadata">

**Author:** ![parsa](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/parsa/32/23227_2.png) [@parsa](https://discuss.elastic.co/u/parsa)\
**Post date:** [October 18, 2017, 7:12pm UTC](https://discuss.elastic.co/t/using-sql-server-queries-in-elasticsearch-5-6-3/104027/11 "2017-10-18T19:12:20Z")

</div>

Thank you. after run it the output is:

```
Sending Logstash's logs to D:/logstash-5.6.3/logs which is now configured via log4j2.properties
    [2017-10-18T22:38:53,550][INFO][logstash.modules.scaffold] Initializing module {:module_name=>"fb_apache", :directory=>"D:/logstash-5.6.3/modules/fb_apache/configuration"}
    [2017-10-18T22:38:53,753][INFO][logstash.modules.scaffold] Initializing module {:module_name=>"netflow", :directory=>"D:/logstash-5.6.3/modules/netflow/configuration"}
    [2017-10-18T22:38:53,769][INFO][logstash.setting.writabledirectory] Creating directory {:setting=>"path.queue", :path=>"D:/logstash-5.6.3/data/queue"}
    [2017-10-18T22:38:53,800][INFO][logstash.setting.writabledirectory] Creating directory {:setting=>"path.dead_letter_queue", :path=>"D:/logstash-5.6.3/data/dead_letter_queue"}
    [2017-10-18T22:38:53,862][INFO][logstash.agent] No persistent UUID file found. Generating new UUID {:uuid=>"ba9e9713-a8e0-4b31-aa55-5a1d955027b7", :path=>"D:/logstash-5.6.3/data/uuid"}
    [2017-10-18T22:38:55,816][ERROR][logstash.outputs.elasticsearch] Unknown setting 'protocol' for elasticsearch
    [2017-10-18T22:38:55,816][ERROR][logstash.outputs.elasticsearch] Unknown setting 'host' for elasticsearch
    [2017-10-18T22:38:55,972][ERROR][logstash.agent] Cannot create pipeline {:reason=>"Something is wrong with your configuration."}
```

---

<div class="post-metadata">

**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\
**Post date:** [October 18, 2017, 7:54pm UTC](https://discuss.elastic.co/t/using-sql-server-queries-in-elasticsearch-5-6-3/104027/12 "2017-10-18T19:54:49Z")

</div>

You've been following outdated documentation. Please consult the documentation and examples for your version of Logstash to see what options are valid for the elasticsearch output.

---

<div class="post-metadata">

**Author:** ![parsa](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/parsa/32/23227_2.png) [@parsa](https://discuss.elastic.co/u/parsa)\
**Post date:** [October 19, 2017, 12:21pm UTC](https://discuss.elastic.co/t/using-sql-server-queries-in-elasticsearch-5-6-3/104027/13 "2017-10-19T12:21:19Z")

</div>

Thank you,as [jdbc-sqlserver-to-elastic-search](https://dba.stackexchange.com/questions/184897/jdbc-sqlserver-to-elastic-search-one-input-to-different-output) I found my problems and I fix my problems, and it installed successfully.

---

<div class="post-metadata">

**Author:** ![parsa](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/parsa/32/23227_2.png) [@parsa](https://discuss.elastic.co/u/parsa)\
**Post date:** [October 19, 2017, 12:24pm UTC](https://discuss.elastic.co/t/using-sql-server-queries-in-elasticsearch-5-6-3/104027/14 "2017-10-19T12:24:03Z")

</div>

I change my .conf file as below:

# file: simple-out.conf

input {  
jdbc {  
jdbc\_connection\_string =\> "jdbc:sqlserver://localhost:1433;databaseName=myDB;user=Elasticsearch;password=parsaparsa"  
jdbc\_user =\> "Elasticsearch"  
jdbc\_validate\_connection =\> true  
jdbc\_driver\_library =\> "D:\logstash-5.6.3\tools\ingest-converter\build\libs\sqljdbc42.jar"  
jdbc\_driver\_class =\> "com.microsoft.sqlserver.jdbc.SQLServerDriver"  
statement =\> "SELECT \* from Video"  
}  
}  
output {  
elasticsearch {  
index =\> "%{Video}"  
document\_type =\> "%{vid}"  
document\_id =\> "%{Subject}"  
hosts =\> "localhost:9200"  
}  
}

then I called this url :

> [http://localhost:9200/Video/vid/Hello](http://localhost:9200/Video/vid/Hello)

but I get this output:

`{"error":{"root_cause":[{"type":"index_not_found_exception","reason":"no such index","resource.type":"index_expression","resource.id":"Video","index_uuid":"_na_","index":"Video"}],"type":"index_not_found_exception","reason":"no such index","resource.type":"index_expression","resource.id":"Video","index_uuid":"_na_","index":"Video"},"status":404}`

---

<div class="post-metadata">

**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\
**Post date:** [October 19, 2017, 1:02pm UTC](https://discuss.elastic.co/t/using-sql-server-queries-in-elasticsearch-5-6-3/104027/15 "2017-10-19T13:02:06Z")

</div>

And what does the query "SELECT \* from Video" return?

---

<div class="post-metadata">

**Author:** ![parsa](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/parsa/32/23227_2.png) [@parsa](https://discuss.elastic.co/u/parsa)\
**Post date:** [October 19, 2017, 1:07pm UTC](https://discuss.elastic.co/t/using-sql-server-queries-in-elasticsearch-5-6-3/104027/16 "2017-10-19T13:07:43Z")

</div>

It contain Id,Subject,Description,,UploadDate,CategoryId,.... another fields.  
and here exists the result of running this command:

> logstash -f simple-out.conf

is:

```
Sending Logstash's logs to D:/logstash-5.6.3/logs which is now configured via log4j2.properties
[2017-10-19T16:27:26,976][INFO][logstash.modules.scaffold] Initializing module {:module_name=>"fb_apache", :directory=>"D:/logstash-5.6.3/modules/fb_apache/configuration"}
[2017-10-19T16:27:26,976][INFO][logstash.modules.scaffold] Initializing module {:module_name=>"netflow", :directory=>"D:/logstash-5.6.3/modules/netflow/configuration"}
[2017-10-19T16:27:28,236][INFO][logstash.outputs.elasticsearch] Elasticsearch pool URLs updated {:changes=>{:removed=>[], :added=>[http://localhost:9200/]}}
[2017-10-19T16:27:28,236][INFO][logstash.outputs.elasticsearch] Running health check to see if an Elasticsearch connection is working {:healthcheck_url=>http://localhost:9200/, :path=>"/"}
[2017-10-19T16:27:28,392][WARN][logstash.outputs.elasticsearch] Restored connection to ES instance {:url=>"http://localhost:9200/"}
[2017-10-19T16:27:28,439][INFO][logstash.outputs.elasticsearch] Using mapping template from {:path=>nil}
[2017-10-19T16:27:28,439][INFO][logstash.outputs.elasticsearch] Attempting to install template {:manage_template=>{"template"=>"logstash-*", "version"=>50001, "settings"=>{"index.refresh_interval"=>"5s"}, "mappings"=>{"_default_"=>{"_all"=>{"enabled"=>true, "norms"=>false}, "dynamic_templates"=>[{"message_field"=>{"path_match"=>"message", "match_mapping_type"=>"string", "mapping"=>{"type"=>"text", "norms"=>false}}}, {"string_fields"=>{"match"=>"*", "match_mapping_type"=>"string", "mapping"=>{"type"=>"text", "norms"=>false, "fields"=>{"keyword"=>{"type"=>"keyword", "ignore_above"=>256}}}}}], "properties"=>{"@timestamp"=>{"type"=>"date", "include_in_all"=>false}, "@version"=>{"type"=>"keyword", "include_in_all"=>false}, "geoip"=>{"dynamic"=>true, "properties"=>{"ip"=>{"type"=>"ip"}, "location"=>{"type"=>"geo_point"}, "latitude"=>{"type"=>"half_float"}, "longitude"=>{"type"=>"half_float"}}}}}}}}
[2017-10-19T16:27:28,455][INFO][logstash.outputs.elasticsearch] New Elasticsearch output {:class=>"LogStash::Outputs::ElasticSearch", :hosts=>["//localhost:9200"]}
[2017-10-19T16:27:28,455][INFO][logstash.pipeline] Starting pipeline {"id"=>"main", "pipeline.workers"=>4, "pipeline.batch.size"=>125, "pipeline.batch.delay"=>5, "pipeline.max_inflight"=>500}
[2017-10-19T16:27:29,142][INFO][logstash.pipeline] Pipeline main started
[2017-10-19T16:27:29,408][INFO][logstash.agent] Successfully started Logstash API endpoint {:port=>9600}
[2017-10-19T16:27:30,392][INFO][logstash.inputs.jdbc] (0.000000s) SELECT * from Video
[2017-10-19T16:27:32,158][WARN][logstash.agent] stopping pipeline {:id=>"main"}

```

---

<div class="post-metadata">

**Author:** ![parsa](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/parsa/32/23227_2.png) [@parsa](https://discuss.elastic.co/u/parsa)\
**Post date:** [October 19, 2017, 1:39pm UTC](https://discuss.elastic.co/t/using-sql-server-queries-in-elasticsearch-5-6-3/104027/17 "2017-10-19T13:39:35Z")

</div>

What I must be do?  
please help me.

---

<div class="post-metadata">

**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\
**Post date:** [October 19, 2017, 1:54pm UTC](https://discuss.elastic.co/t/using-sql-server-queries-in-elasticsearch-5-6-3/104027/18 "2017-10-19T13:54:47Z")

</div>

> It contain Id,Subject,Description,,UploadDate,CategoryId,.... another fields.

Okay, but does it contain columns named "Video" and "vid", since you with

```auto
index => "%{Video}"
document_type => "%{vid}"

```

have configured Logstash to pick the index and type name from the fields named "Video" and "vid".

---

<div class="post-metadata">

**Author:** ![parsa](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/parsa/32/23227_2.png) [@parsa](https://discuss.elastic.co/u/parsa)\
**Post date:** [October 19, 2017, 2:18pm UTC](https://discuss.elastic.co/t/using-sql-server-queries-in-elasticsearch-5-6-3/104027/19 "2017-10-19T14:18:06Z")

</div>

it not contained columns named "Video" and "vid".  
I suppose I want to search from column with "subject" name I mus be do:

> ```
> index => "%{subject}"
> document_type => "%{subject}"
> document_id => "%{subject}"
> 
> ```

is it true?  
and how can call with url?

---

<div class="post-metadata">

**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\
**Post date:** [October 19, 2017, 3:39pm UTC](https://discuss.elastic.co/t/using-sql-server-queries-in-elasticsearch-5-6-3/104027/20 "2017-10-19T15:39:41Z")

</div>

> it not contained columns named "Video" and "vid".

Then your configuration doesn't make sense. As I said in my previous post, your current configuration stores events in an index named after the contents of the `Video` field. If you want it to be stored in an index called Video just say `Video`, not `%{Video}`.

> **[Accessing event data and fields | Logstash Reference \[8.11\] | Elastic](https://www.elastic.co/guide/en/logstash/current/event-dependent-configuration.html#logstash-config-field-references)**

[Next page](https://discuss.elastic.co/t/using-sql-server-queries-in-elasticsearch-5-6-3/104027.md?page=2)