# Load data from SQL server to Elasticsearch with document\_id on local

**URL:** <https://discuss.elastic.co/t/load-data-from-sql-server-to-elasticsearch-with-document-id-on-local/273373>\
**Category:** Logstash\
**Created:** [May 19, 2021, 8:11am UTC](https://discuss.elastic.co/t/load-data-from-sql-server-to-elasticsearch-with-document-id-on-local/273373 "2021-05-19T08:11:14Z")\
**Posts on this page:** 8\
**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 19, 2021, 8:11am UTC](https://discuss.elastic.co/t/load-data-from-sql-server-to-elasticsearch-with-document-id-on-local/273373/1 "2021-05-19T08:11:14Z")

</div>

Hi every one.  
My local table has 3000 rows. When I config logstash output with  
**document\_id =\> "%{countyId}"**. Just only lastest row is inserted to ElasticSearch.

But when I not use **document\_id =\> "%{countyId}"** so it work fine.

Please any one help me.  
Thank you.

---

<div class="post-metadata">

**Author:** ![ylasri](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ylasri/32/86120_2.png) [@ylasri](https://discuss.elastic.co/u/ylasri)\
**Post date:** [May 19, 2021, 8:39am UTC](https://discuss.elastic.co/t/load-data-from-sql-server-to-elasticsearch-with-document-id-on-local/273373/2 "2021-05-19T08:39:17Z")

</div>

Means that all your SQL rows have the same **countyId**  
Use an other unique ID per row

---

<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:42am UTC](https://discuss.elastic.co/t/load-data-from-sql-server-to-elasticsearch-with-document-id-on-local/273373/3 "2021-05-19T08:42:17Z")

</div>

Hi @ylasri .  
column **countyId** is Primary key in my table. This is my logstash 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:** ![ylasri](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ylasri/32/86120_2.png) [@ylasri](https://discuss.elastic.co/u/ylasri)\
**Post date:** [May 19, 2021, 8:48am UTC](https://discuss.elastic.co/t/load-data-from-sql-server-to-elasticsearch-with-document-id-on-local/273373/4 "2021-05-19T08:48:57Z")

</div>

Try to add this [parameter](https://www.elastic.co/guide/en/logstash/current/plugins-inputs-jdbc.html#plugins-inputs-jdbc-lowercase_column_names) `lowercase_column_names => false` as follow

```auto
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"
		lowercase_column_names => false
	}
}
output {
	elasticsearch {
	hosts => "http://localhost:9200/"
	index => "counties_index"
	document_id => "%{countyId}"
	doc_as_upsert => true
}
	stdout {
	codec => rubydebug
	}
}

```

---

<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, 9:06am UTC](https://discuss.elastic.co/t/load-data-from-sql-server-to-elasticsearch-with-document-id-on-local/273373/5 "2021-05-19T09:06:34Z")

</div>

Hi @ylasri .  
It work fine.  
May be you show me that I can check condition before insert or update to elastic search?  
Thank you so much.

---

<div class="post-metadata">

**Author:** ![ylasri](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ylasri/32/86120_2.png) [@ylasri](https://discuss.elastic.co/u/ylasri)\
**Post date:** [May 19, 2021, 9:49am UTC](https://discuss.elastic.co/t/load-data-from-sql-server-to-elasticsearch-with-document-id-on-local/273373/6 "2021-05-19T09:49:11Z")

</div>

May be this ?

> [@Using different Index names in Output logstash](https://discuss.elastic.co/t/using-different-index-names-in-output-logstash/273364/):
>
> Hello All , I want to re-use the config lines in output logstash as shown below. I am using if condition but is throwing some error. Please help me out. output { elasticsearch { hosts =\> ["https://XXXXXnet:8200"] user =\> "${es\_usr}" password =\> "${es\_pwd}" if "RequestRouter" in [source] and "VAGAPIEMEA" in [InterfaceName] { index =\> "prodsrvrlog-reqrouter-vagapi-%{log\_day}" } else if "RequestRouter" in [source] …

---

<div class="post-metadata">

**Author:** ![Saravana37](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/saravana37/32/85237_2.png) [@Saravana37](https://discuss.elastic.co/u/Saravana37)\
**Post date:** [May 19, 2021, 10:08am UTC](https://discuss.elastic.co/t/load-data-from-sql-server-to-elasticsearch-with-document-id-on-local/273373/7 "2021-05-19T10:08:04Z")

</div>

Hello @ylasri , I have defined it using mutate in the filter section. Now it is working fine. Thank you so much for your help.

---

<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, 10:08am UTC](https://discuss.elastic.co/t/load-data-from-sql-server-to-elasticsearch-with-document-id-on-local/273373/8 "2021-06-16T10:08:55Z")

</div>

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