# Load data from SQL server to Elasticsearch on local

**URL:** <https://discuss.elastic.co/t/load-data-from-sql-server-to-elasticsearch-on-local/273196>\
**Category:** Logstash\
**Created:** [May 17, 2021, 3:59pm UTC](https://discuss.elastic.co/t/load-data-from-sql-server-to-elasticsearch-on-local/273196 "2021-05-17T15:59:05Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![Dai\_Thai\_Hoa\_Vo](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dai_thai_hoa_vo/32/78486_2.png) [@Dai\_Thai\_Hoa\_Vo](https://discuss.elastic.co/u/Dai_Thai_Hoa_Vo)\
**Post date:** [May 17, 2021, 3:59pm UTC](https://discuss.elastic.co/t/load-data-from-sql-server-to-elasticsearch-on-local/273196/1 "2021-05-17T15:59:05Z")

</div>

I have local sql server and local ELK . I configed logstash.conf connect to SQL Server. The logstash have read data from SQL but when write data to Elasticsearch only 1 row.  
Please any one explain to me about this.  
input {  
jdbc {  
jdbc\_driver\_library =\> "C:\ELK\elasticsearch-7.12.0-windows-x86\_64\elasticsearch-7.12.0\lib\sqljdbc\_9.2\enu\mssql-jdbc-9.2.1.jre8.jar"  
jdbc\_driver\_class =\> "com.microsoft.sqlserver.jdbc.SQLServerDriver"  
jdbc\_connection\_string =\>"jdbc:sqlserver://BPHHP13:1433;databaseName=WRIPMS;"  
jdbc\_user =\> "test"  
jdbc\_password =\> "TuanTu2017@)!&"  
statement =\> "select countyId, countyName, modifiedDate from Counties"  
}  
}  
output {  
elasticsearch{  
hosts =\> "[http://localhost:9200/](http://localhost:9200/)"  
index =\> "counties\_index"  
document\_id =\> "%{CountyId}"  
}  
stdout{}  
}  
Thank you.

---

<div class="post-metadata">

**Author:** ![stephenb](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stephenb/32/40856_2.png) [@stephenb](https://discuss.elastic.co/u/stephenb)\
**Post date:** [May 17, 2021, 4:55pm UTC](https://discuss.elastic.co/t/load-data-from-sql-server-to-elasticsearch-on-local/273196/2 "2021-05-17T16:55:26Z")

</div>

what is the output of

```
GET _cat/indices/?v

```

Also you are setting the document `_id` to the `CountyId` that may not be the best approach, unless you are specifically trying to overwrite the documents, if so you you might want to use `doc_as_upsert` see [here](https://www.elastic.co/guide/en/logstash/current/plugins-outputs-elasticsearch.html#plugins-outputs-elasticsearch-doc_as_upsert)

> [@Dai\_Thai\_Hoa\_Vo](#):
>
> document\_id =\> "%{CountyId}"

---

<div class="post-metadata">

**Author:** ![Dai\_Thai\_Hoa\_Vo](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dai_thai_hoa_vo/32/78486_2.png) [@Dai\_Thai\_Hoa\_Vo](https://discuss.elastic.co/u/Dai_Thai_Hoa_Vo)\
**Post date:** [May 18, 2021, 5:57am UTC](https://discuss.elastic.co/t/load-data-from-sql-server-to-elasticsearch-on-local/273196/3 "2021-05-18T05:57:57Z")

</div>

Hi @stephenb .  
I need insert the new rows also overwrite data in Elastic Search so I used  
document\_id =\> "%{CountyId}".  
So I will set up 'doc\_as\_upsert' is true?  
Please you support to me about this case.

---

<div class="post-metadata">

**Author:** ![stephenb](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stephenb/32/40856_2.png) [@stephenb](https://discuss.elastic.co/u/stephenb)\
**Post date:** [May 18, 2021, 1:27pm UTC](https://discuss.elastic.co/t/load-data-from-sql-server-to-elasticsearch-on-local/273196/4 "2021-05-18T13:27:38Z")

</div>

This is not a support case we are volunteers. We will help if we can, the more detail you provide the better chance someone can help.

---

<div class="post-metadata">

**Author:** ![Dai\_Thai\_Hoa\_Vo](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dai_thai_hoa_vo/32/78486_2.png) [@Dai\_Thai\_Hoa\_Vo](https://discuss.elastic.co/u/Dai_Thai_Hoa_Vo)\
**Post date:** [May 18, 2021, 4:51pm UTC](https://discuss.elastic.co/t/load-data-from-sql-server-to-elasticsearch-on-local/273196/5 "2021-05-18T16:51:36Z")

</div>

Hi @stephenb .  
I configured doc\_as\_upsert =\> true in logstash.cnf. But it doesn't work. My source tabe has 1900 rows. But just only 1 row has been written to Elastic search.

input {  
jdbc {  
jdbc\_driver\_library =\> "C:\ELK\elasticsearch-7.12.0-windows-x86\_64\elasticsearch-7.12.0\lib\sqljdbc\_9.2\enu\mssql-jdbc-9.2.1.jre8.jar"  
jdbc\_driver\_class =\> "com.microsoft.sqlserver.jdbc.SQLServerDriver"  
jdbc\_connection\_string =\>"jdbc:sqlserver://BPHHP13:1433;databaseName=WRIPMS;"  
jdbc\_user =\> "test"  
jdbc\_password =\> "TuanTu2017@)!&"  
jdbc\_paging\_enabled =\> true  
schedule =\> "\*/5 \* \* \* \* \*"  
statement =\> "select countyId, countyName, modifiedDate from Counties where countyId \> :sql\_last\_value"  
use\_column\_value =\> true  
tracking\_column =\> "countyId"  
}  
}  
output {  
elasticsearch{  
hosts =\> "[http://localhost:9200/](http://localhost:9200/)"  
index =\> "counties\_index"  
document\_id =\> "%{CountyId}"  
doc\_as\_upsert =\> true  
}  
stdout {  
codec =\> json\_lines  
}  
}  
Thank you.

---

<div class="post-metadata">

**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\
**Post date:** [May 18, 2021, 5:08pm UTC](https://discuss.elastic.co/t/load-data-from-sql-server-to-elasticsearch-on-local/273196/6 "2021-05-18T17:08:41Z")

</div>

> [@Dai\_Thai\_Hoa\_Vo](#):
>
> document\_id =\> "%{CountyId}"

Field names are case sensitive. You have a column called [countyId], not [CountyId], so the document\_id will literally be `%{CountyId}` and every document will overwrite it.

---

<div class="post-metadata">

**Author:** ![Dai\_Thai\_Hoa\_Vo](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dai_thai_hoa_vo/32/78486_2.png) [@Dai\_Thai\_Hoa\_Vo](https://discuss.elastic.co/u/Dai_Thai_Hoa_Vo)\
**Post date:** [May 18, 2021, 5:40pm UTC](https://discuss.elastic.co/t/load-data-from-sql-server-to-elasticsearch-on-local/273196/7 "2021-05-18T17:40:25Z")

</div>

Hi @Badger .  
I modified the config file follow your guideline. But it still import 1 row into elastic search.  
Please you help me review it.  
input {  
jdbc {  
jdbc\_driver\_library =\> "C:\ELK\elasticsearch-7.12.0-windows-x86\_64\elasticsearch-7.12.0\lib\sqljdbc\_9.2\enu\mssql-jdbc-9.2.1.jre8.jar"  
jdbc\_driver\_class =\> "com.microsoft.sqlserver.jdbc.SQLServerDriver"  
jdbc\_connection\_string =\>"jdbc:sqlserver://BPHHP13:1433;databaseName=WRIPMS;"  
jdbc\_user =\> "test"  
jdbc\_password =\> "TuanTu2017@)!&"  
jdbc\_paging\_enabled =\> true  
schedule =\> "\*/5 \* \* \* \* \*"  
statement =\> "select countyId, countyName, modifiedDate from Counties where countyId \> :sql\_last\_value"  
use\_column\_value =\> true  
tracking\_column =\> "countyId"  
}  
}  
output {  
elasticsearch{  
hosts =\> "[http://localhost:9200/](http://localhost:9200/)"  
index =\> "counties\_index"  
document\_id =\> "%{countyId}"  
doc\_as\_upsert =\> true  
}  
stdout {  
codec =\> json\_lines  
}  
}  
Thank you.

---

<div class="post-metadata">

**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\
**Post date:** [May 18, 2021, 6:08pm UTC](https://discuss.elastic.co/t/load-data-from-sql-server-to-elasticsearch-on-local/273196/8 "2021-05-18T18:08:07Z")

</div>

You are using a tracking\_column, so only new data will be added. Try running once with `clean_run => true`.

---

<div class="post-metadata">

**Author:** ![Dai\_Thai\_Hoa\_Vo](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dai_thai_hoa_vo/32/78486_2.png) [@Dai\_Thai\_Hoa\_Vo](https://discuss.elastic.co/u/Dai_Thai_Hoa_Vo)\
**Post date:** [May 19, 2021, 8:06am UTC](https://discuss.elastic.co/t/load-data-from-sql-server-to-elasticsearch-on-local/273196/9 "2021-05-19T08:06:38Z")

</div>

Hi @Badger .  
I am try running clean\_run =\> true. It still doesn't work. But when I remove 'document\_id' so data full fill to elastic search. My table has 1987 rows.  
What I need when Insert new record, only just insert row which not exists in Elastic.  
This is my file config.  
input {  
jdbc {  
jdbc\_driver\_library =\> "C:\ELK\elasticsearch-7.12.0-windows-x86\_64\elasticsearch-7.12.0\lib\sqljdbc\_9.2\enu\mssql-jdbc-9.2.1.jre8.jar"  
jdbc\_driver\_class =\> "com.microsoft.sqlserver.jdbc.SQLServerDriver"  
jdbc\_connection\_string =\>"jdbc:sqlserver://BPHHP13:1433;databaseName=WRIPMS;"  
jdbc\_user =\> "test"  
jdbc\_password =\> "TuanTu2017@)!&"  
jdbc\_paging\_enabled =\> true  
clean\_run =\> true  
schedule =\> "\*/5 \* \* \* \* \*"  
statement =\> "select [countyId], [countyName], [modifiedDate] from Counties where [countyId] \> :sql\_last\_value"  
use\_column\_value =\> true  
tracking\_column =\> "countyId"  
}  
}  
output {  
elasticsearch{  
hosts =\> "[http://localhost:9200/](http://localhost:9200/)"  
index =\> "counties\_index"  
document\_id =\> "%{countyId}"  
doc\_as\_upsert =\> true  
}  
stdout {  
codec =\> rubydebug  
}  
}  
Thank you.

---

<div class="post-metadata">

**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\
**Post date:** [May 19, 2021, 4:15pm UTC](https://discuss.elastic.co/t/load-data-from-sql-server-to-elasticsearch-on-local/273196/10 "2021-05-19T16:15:24Z")

</div>

As noted in another question you asked about this, because the lowercase\_column\_names defaults to true, you do not have a field called countId, but instead countyid.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [June 16, 2021, 4:15pm UTC](https://discuss.elastic.co/t/load-data-from-sql-server-to-elasticsearch-on-local/273196/11 "2021-06-16T16:15:31Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.