# Jdbc input put data in all indexes

**URL:** <https://discuss.elastic.co/t/jdbc-input-put-data-in-all-indexes/80446>\
**Category:** Logstash\
**Created:** [March 29, 2017, 9:13am UTC](https://discuss.elastic.co/t/jdbc-input-put-data-in-all-indexes/80446 "2017-03-29T09:13:49Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![Amilyso](https://avatars.discourse-cdn.com/v4/letter/a/e36b37/32.png) [@Amilyso](https://discuss.elastic.co/u/Amilyso)\
**Post date:** [March 29, 2017, 9:13am UTC](https://discuss.elastic.co/t/jdbc-input-put-data-in-all-indexes/80446/1 "2017-03-29T09:13:49Z")

</div>

Hi all,

When I create a conf file with jdbc input, the data go to all my indexes.  
For example I did two conf files with jdbc input :

> input {  
> jdbc {  
> jdbc\_connection\_string =\> "jdbc:postgresql://localhost:5432/postgres"  
> jdbc\_user =\> "user"  
> jdbc\_password =\> "password"  
> jdbc\_driver\_library =\> "/path\_to\_postgre\_jar/postgresql-9.2-1004.jdbc3.jar"  
> jdbc\_driver\_class =\> "org.postgresql.Driver"  
> schedule =\> "30 \* \* \* \* \*"  
> statement =\> "select id\_temp\_index, nom, prenom, sexe, age from temp\_index"  
> }  
> }

> filter { }

> output {  
> elasticsearch {  
> index =\> "index\_sql\_1"  
> document\_type =\> "type\_01"  
> hosts =\> ["localhost:9200"]  
> document\_id =\> "%{id\_temp\_index}"  
> }  
> }

and

> input {  
> jdbc {  
> jdbc\_connection\_string =\> "jdbc:postgresql://localhost:5432/postgres"  
> jdbc\_user =\> "user"  
> jdbc\_password =\> "password"  
> jdbc\_driver\_library =\> "/path\_to\_postgre\_jar/postgresql-9.2-1004.jdbc3.jar"  
> jdbc\_driver\_class =\> "org.postgresql.Driver"  
> schedule =\> "30 \* \* \* \* \*"  
> statement =\> "select id\_temp\_index as id\_temp\_index\_2, nom as nom\_2, prenom as prenom\_2, sexe as sexe\_2, age as age\_2 from temp\_index"  
> }  
> }

> filter{ }

> output {  
> elasticsearch {  
> protocol =\> http  
> index =\> "index\_sql\_2"  
> document\_type =\> "type\_2"  
> hosts =\> ["localhost:9200"]  
> document\_id =\> "%{id\_temp\_index\_2}"  
> }  
> }

So I should have two indexes quite identical exept the name of the columns with 5 fields and I got :

 ![](https://us1.discourse-cdn.com/elastic/original/3X/5/5/55c1b7ffa5232d28bf46b3affddc1ffa0b99edfe.PNG)

So, each index got the fields of each conf and one line come from the other.  
Those lines appears in all indexes I will create, even csv input, beats input...

And it can replace some data if the document\_id is somehting like **document\_id =\> "%{id}"** in several conf files.

So, did I miss something in my conf files? Is there a way to prevent that?

---

<div class="post-metadata">

**Author:** ![Greentea](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/greentea/32/15924_2.png) [@Greentea](https://discuss.elastic.co/u/Greentea)\
**Post date:** [March 29, 2017, 9:30am UTC](https://discuss.elastic.co/t/jdbc-input-put-data-in-all-indexes/80446/2 "2017-03-29T09:30:29Z")

</div>

@Amilyso Hi!

If I have not misunderstanding, are you trying to copy index\_sql\_1 document to index\_sql\_2?

---

<div class="post-metadata">

**Author:** ![Amilyso](https://avatars.discourse-cdn.com/v4/letter/a/e36b37/32.png) [@Amilyso](https://discuss.elastic.co/u/Amilyso)\
**Post date:** [March 29, 2017, 9:43am UTC](https://discuss.elastic.co/t/jdbc-input-put-data-in-all-indexes/80446/3 "2017-03-29T09:43:07Z")

</div>

Not at all, it's just I got a lot of error in one of my project due to this "bug". So i simplify the problem to try to undestand it.

I want to get somehting like that :

 ![](https://us1.discourse-cdn.com/elastic/original/3X/3/1/31083a588fa70ba539c592d2380effa87ad89f7b.PNG)

I should have use two tables for my example (like custormer and products instead of using the same table twice), it should be more relevent...

---

<div class="post-metadata">

**Author:** ![Greentea](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/greentea/32/15924_2.png) [@Greentea](https://discuss.elastic.co/u/Greentea)\
**Post date:** [March 29, 2017, 9:52am UTC](https://discuss.elastic.co/t/jdbc-input-put-data-in-all-indexes/80446/4 "2017-03-29T09:52:08Z")

</div>

@Amilyso  
your situation is similar to mine. I have three table in my db. And I have to build three index to save it. But I got a solution of it. but it is not the best way.

---

<div class="post-metadata">

**Author:** ![Greentea](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/greentea/32/15924_2.png) [@Greentea](https://discuss.elastic.co/u/Greentea)\
**Post date:** [March 29, 2017, 9:55am UTC](https://discuss.elastic.co/t/jdbc-input-put-data-in-all-indexes/80446/5 "2017-03-29T09:55:36Z")

</div>

may be you can try my solution

```
input{
	jdbc{
		# mysql jdbc connection string to our backup databse
		jdbc_connection_string => "jdbc:mysql://localhost:3306/price_prod_test"

		# the user we wish to excute our statement as
		jdbc_user => "user"
		jdbc_password => "pass"

		# the path to our downloaded jdbc driver
		jdbc_driver_library => "/usr/share/java/mysql-connector-java.jar"

		# the name of the driver class for mysql
		jdbc_driver_class => "com.mysql.jdbc.Driver"

		# the control statement
		record_last_run=>true
		type => "product"
		statement => "SELECT * FROM product"
	}
	jdbc{
		# mysql jdbc connection string to our backup databse
		jdbc_connection_string => "jdbc:mysql://localhost:3306/price_prod_test"

		# the user we wish to excute our statement as
		jdbc_user => "user"
		jdbc_password => "pass"

		# the path to our downloaded jdbc driver
		jdbc_driver_library => "/usr/share/java/mysql-connector-java.jar"

		# the name of the driver class for mysql
		jdbc_driver_class => "com.mysql.jdbc.Driver"

		# the control statement
		record_last_run=>true
		type => "merchant"
		statement => "SELECT * FROM merchant"
	}
	jdbc{
		# mysql jdbc connection string to our backup databse
		jdbc_connection_string => "jdbc:mysql://localhost:3306/price_prod_test"

		# the user we wish to excute our statement as
		jdbc_user => "user"
		jdbc_password => "pass"

		# the path to our downloaded jdbc driver
		jdbc_driver_library => "/usr/share/java/mysql-connector-java.jar"

		# the name of the driver class for mysql
		jdbc_driver_class => "com.mysql.jdbc.Driver"

		# the control statement
		record_last_run=>true
		type => "news"
		statement => "SELECT * FROM news"
	}
}

filter {
	if [type] == "product"{
		mutate {
			add_field => { "[product_suggest][input]" => "%{display_name}"}
			add_field => { "[product_suggest][weight]" => "%{id}"}
			add_field => { "id_sort" => "%{id}" }
			remove_field => ["attribute_value_en", "attribute_value_sc"]
		}
	}
	if [type] == "merchant"{
		mutate {
			add_field => { "[merchant_suggest][input]" => "%{merchant_name}"}
			add_field => { "[merchant_suggest][weight]" => "%{id}"}
		}
	}
	if [type] == "news"{
		mutate {
			add_field => { "id_sort" => "%{id}" }
		}
	}
}

output {
	stdout{codec => dots}
	if [type] == "product"{
		elasticsearch {
				hosts => "localhost"
				index => "product"
				document_id => "%{id}"
		}
	}
	if [type] == "merchant"{
		elasticsearch {
				hosts => "localhost"
				index => "merchant"
				document_id => "%{id}"
		}
	}
	if [type] == "news"{
		elasticsearch {
				hosts => "localhost"
				index => "news"
				document_id => "%{id}"
		}
	}
}
```

---

<div class="post-metadata">

**Author:** ![Amilyso](https://avatars.discourse-cdn.com/v4/letter/a/e36b37/32.png) [@Amilyso](https://discuss.elastic.co/u/Amilyso)\
**Post date:** [March 29, 2017, 10:01am UTC](https://discuss.elastic.co/t/jdbc-input-put-data-in-all-indexes/80446/6 "2017-03-29T10:01:03Z")

</div>

Thanks!  
I'll try it and will tell you if it works for me.

---

<div class="post-metadata">

**Author:** ![Amilyso](https://avatars.discourse-cdn.com/v4/letter/a/e36b37/32.png) [@Amilyso](https://discuss.elastic.co/u/Amilyso)\
**Post date:** [March 29, 2017, 1:00pm UTC](https://discuss.elastic.co/t/jdbc-input-put-data-in-all-indexes/80446/7 "2017-03-29T13:00:19Z")

</div>

It works fine!

Thanks @Greentea

---

<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:** [April 26, 2017, 1:00pm UTC](https://discuss.elastic.co/t/jdbc-input-put-data-in-all-indexes/80446/8 "2017-04-26T13:00:21Z")

</div>

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