# Database en elasticsearch

**URL:** <https://discuss.elastic.co/t/database-en-elasticsearch/66889>\
**Category:** Elastic en Español\
**Created:** [November 22, 2016, 4:21pm UTC](https://discuss.elastic.co/t/database-en-elasticsearch/66889 "2016-11-22T16:21:50Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![Diego\_Gomez](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/diego_gomez/32/12893_2.png) [@Diego\_Gomez](https://discuss.elastic.co/u/Diego_Gomez)\
**Post date:** [November 22, 2016, 4:21pm UTC](https://discuss.elastic.co/t/database-en-elasticsearch/66889/1 "2016-11-22T16:21:50Z")

</div>

Buenos Tardes, queria consultarles necesito desde un gateway que recibe informacion y este crea una base de datos la quiero ingresar a elastisearch. Desde el Gateway puedo configurar que grabe en una base de datos externa, por eso quisiera grabar los datos en elasticsearch. ¿Que psobilidades hay para que pueda hacer esto? ¿Como seria? Gracias

---

<div class="post-metadata">

**Author:** ![gmoskovicz](https://avatars.discourse-cdn.com/v4/letter/g/ecae2f/32.png) [@gmoskovicz](https://discuss.elastic.co/u/gmoskovicz)\
**Post date:** [November 22, 2016, 4:46pm UTC](https://discuss.elastic.co/t/database-en-elasticsearch/66889/2 "2016-11-22T16:46:30Z")

</div>

Hola @Diego_Gomez!

Que tal? Me puedes comentar un poco mas sobre este Gateway? Que producto es? A que le llamas Gateway?

Tal vez uno de los inputs the Logstash the ayuden a enviar la info a Logstash, y luego desde Logstash enviarla a Elasticsearch? Source: [https://www.elastic.co/guide/en/logstash/current/input-plugins.html](https://www.elastic.co/guide/en/logstash/current/input-plugins.html)

Saludos!

--Gabriel

---

<div class="post-metadata">

**Author:** ![Diego\_Gomez](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/diego_gomez/32/12893_2.png) [@Diego\_Gomez](https://discuss.elastic.co/u/Diego_Gomez)\
**Post date:** [November 22, 2016, 5:33pm UTC](https://discuss.elastic.co/t/database-en-elasticsearch/66889/3 "2016-11-22T17:33:09Z")

</div>

hola Gabriel, el producto se llama Meshlium es de la empresa libelium este dispositivo lo usamos para conectar equipos con sensores, el meshlium hace que estos se puedan conectar a diferentes opciones, la que yo necesito es por medio de una base de datos que el crea, tiene una opción donde te permite que le digas en que base de datos externa grabe los datos, me pide IP, PORT, USER Y PASS, quisiera saber si puedo meterlo directamente al elasticsearch o debería crear una base de datos sql y después ingresarla a logstash o elasticsearch? la idea es una ves que los indexe poder verlos en el kibana. Gracias por tu respuesta

---

<div class="post-metadata">

**Author:** ![Diego\_Gomez](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/diego_gomez/32/12893_2.png) [@Diego\_Gomez](https://discuss.elastic.co/u/Diego_Gomez)\
**Post date:** [November 22, 2016, 5:45pm UTC](https://discuss.elastic.co/t/database-en-elasticsearch/66889/4 "2016-11-22T17:45:25Z")

</div>

Provee de esta manera y me tira esto

```
input {
  tcp {
   host => "190.104.xxx.xxx"
   port => 80
   mode => "client"
}
}
 output{
     elasticsearch {
            index => ["log-sensores-%{+yyyy}"]
            hosts => ["10.10.0.132:9200"]
            }
     stdout { codec => rubydebug }
}
 rack@rack-Not-Specified:~/logstash-5.0.0$ sudo bin/logstash -f /home/rack/tcp.conf
  [sudo] password for rack:
  Sending Logstash logs to /home/rack/logstash-5.0.0/logs which is now configured via log4j2.properties.
[2016-11-22T14:41:04,098][INFO][logstash.outputs.elasticsearch] Elasticsearch pool URLs updated {:changes=>{:removed=>[], :added=> ["http://10.10.0.132:9200"]}}
  [2016-11-22T14:41:04,138][INFO][logstash.outputs.elasticsearch] Using mapping template from {:path=>nil}
 [2016-11-22T14:41:04,514][INFO][logstash.outputs.elasticsearch] Attempting to install template {:manage_template=>{"template"=>"logstash-*", "settings"=>{"index.refresh_interval"=>"5s"}, "mappings"=>{"_default_"=>{"_all"=>{"enabled"=>true, "omit_norms"=>true}, "dynamic_templates"=>[{"message_field"=>{"path_match"=>"message", "match_mapping_type"=>"string", "mapping"=>{"type"=>"string", "index"=>"analyzed", "omit_norms"=>true, "fielddata"=>{"format"=>"disabled"}}}}, {"string_fields"=>{"match"=>"*", "match_mapping_type"=>"string", "mapping"=>{"type"=>"string", "index"=>"analyzed", "omit_norms"=>true, "fielddata"=>{"format"=>"disabled"}, "fields"=>{"raw"=>{"type"=>"string", "index"=>"not_analyzed", "doc_values"=>true, "ignore_above"=>256}}}}}, {"float_fields"=>{"match"=>"*", "match_mapping_type"=>"float", "mapping"=>{"type"=>"float", "doc_values"=>true}}}, {"double_fields"=>{"match"=>"*", "match_mapping_type"=>"double", "mapping"=>{"type"=>"double", "doc_values"=>true}}}, {"byte_fields"=>{"match"=>"*", "match_mapping_type"=>"byte", "mapping"=>{"type"=>"byte", "doc_values"=>true}}}, {"short_fields"=>{"match"=>"*", "match_mapping_type"=>"short", "mapping"=>{"type"=>"short", "doc_values"=>true}}}, {"integer_fields"=>{"match"=>"*", "match_mapping_type"=>"integer", "mapping"=>{"type"=>"integer", "doc_values"=>true}}}, {"long_fields"=>{"match"=>"*", "match_mapping_type"=>"long", "mapping"=>{"type"=>"long", "doc_values"=>true}}}, {"date_fields"=>{"match"=>"*", "match_mapping_type"=>"date", "mapping"=>{"type"=>"date", "doc_values"=>true}}}, {"geo_point_fields"=>{"match"=>"*", "match_mapping_type"=>"geo_point", "mapping"=>{"type"=>"geo_point", "doc_values"=>true}}}], "properties"=>{"@timestamp"=>{"type"=>"date", "doc_values"=>true}, "@version"=>{"type"=>"string", "index"=>"not_analyzed", "doc_values"=>true}, "geoip"=>{"type"=>"object", "dynamic"=>true, "properties"=>{"ip"=>{"type"=>"ip", "doc_values"=>true}, "location"=>{"type"=>"geo_point", "doc_values"=>true}, "latitude"=>{"type"=>"float", "doc_values"=>true}, "longitude"=>{"type"=>"float", "doc_values"=>true}}}}}}}}
[2016-11-22T14:41:04,543][INFO][logstash.outputs.elasticsearch] New Elasticsearch output {:class=>"LogStash::Outputs::ElasticSearch", :hosts=> ["10.10.0.132:9200"]}
 [2016-11-22T14:41:04,551][INFO][logstash.pipeline] Starting pipeline {"id"=>"main", "pipeline.workers"=>2, "pipeline.batch.size"=>125, "pipeline.batch.delay"=>5, "pipeline.max_inflight"=>250}
[2016-11-22T14:41:04,561][INFO][logstash.pipeline] Pipeline main started
[2016-11-22T14:41:04,798][INFO][logstash.agent] Successfully started Logstash API endpoint {:port=>9600}
```

---

<div class="post-metadata">

**Author:** ![gmoskovicz](https://avatars.discourse-cdn.com/v4/letter/g/ecae2f/32.png) [@gmoskovicz](https://discuss.elastic.co/u/gmoskovicz)\
**Post date:** [November 22, 2016, 5:48pm UTC](https://discuss.elastic.co/t/database-en-elasticsearch/66889/5 "2016-11-22T17:48:50Z")

</div>

No estoy seguro que se pueda escribir directo a Elasticsearch desde Meshlium, pero seguramente se pueda hacer dos differentes casos:

1. Escribir en la base de datos, y con LS RDBMS input tomar los ultimos datos cada x minutos.
2. Escribir a un LOG, y desde Filebeat (beat que lee archivos) enviar la informacion de el log a Logstash, y luego desde Logstash enviar a Elasticsearch.
3. Desde su parte fijarse si alguno de los inputs, con el link que les pase, puede leer desde alguna otra opcion que no estoy considerando dado que no estamos tan afines con Meshlium como producto.

Espero que esto te sea de ayuda!

Saludos!

---

<div class="post-metadata">

**Author:** ![gmoskovicz](https://avatars.discourse-cdn.com/v4/letter/g/ecae2f/32.png) [@gmoskovicz](https://discuss.elastic.co/u/gmoskovicz)\
**Post date:** [November 22, 2016, 5:53pm UTC](https://discuss.elastic.co/t/database-en-elasticsearch/66889/6 "2016-11-22T17:53:23Z")

</div>

Pareceria que tiene una base de datos MySql no? De ser asi, el punto [1] podria funcionarte dependiendo de la cantidad de datos que el producto escribe. Sino yo iria por la segunda solucion.

Saludos!

---

<div class="post-metadata">

**Author:** ![Diego\_Gomez](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/diego_gomez/32/12893_2.png) [@Diego\_Gomez](https://discuss.elastic.co/u/Diego_Gomez)\
**Post date:** [November 22, 2016, 5:55pm UTC](https://discuss.elastic.co/t/database-en-elasticsearch/66889/7 "2016-11-22T17:55:24Z")

</div>

Hola Gabriel perfecto voy a intentar sino te molesta si me trabo te puedo consultar? gracias por tu ayuda.

---

<div class="post-metadata">

**Author:** ![gmoskovicz](https://avatars.discourse-cdn.com/v4/letter/g/ecae2f/32.png) [@gmoskovicz](https://discuss.elastic.co/u/gmoskovicz)\
**Post date:** [November 22, 2016, 6:06pm UTC](https://discuss.elastic.co/t/database-en-elasticsearch/66889/8 "2016-11-22T18:06:05Z")

</div>

Por supuesto, sin problemas Diego.

Saludos.

---

<div class="post-metadata">

**Author:** ![Diego\_Gomez](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/diego_gomez/32/12893_2.png) [@Diego\_Gomez](https://discuss.elastic.co/u/Diego_Gomez)\
**Post date:** [November 22, 2016, 6:06pm UTC](https://discuss.elastic.co/t/database-en-elasticsearch/66889/9 "2016-11-22T18:06:51Z")

</div>

> [@gmoskovicz](#):
>
> LS RDBMS inpu

Hola Gabriel, te consulto esta es la idea para lo que me decis?

> **[INSERT INTO LOGSTASH SELECT DATA FROM DATABASE](https://www.elastic.co/blog/logstash-jdbc-input-plugin)**

---

<div class="post-metadata">

**Author:** ![gmoskovicz](https://avatars.discourse-cdn.com/v4/letter/g/ecae2f/32.png) [@gmoskovicz](https://discuss.elastic.co/u/gmoskovicz)\
**Post date:** [November 22, 2016, 6:09pm UTC](https://discuss.elastic.co/t/database-en-elasticsearch/66889/10 "2016-11-22T18:09:41Z")

</div>

Exactamente. Ese es el blog post, pero aqui esta la doc oficial, para LS 5: [https://www.elastic.co/guide/en/logstash/current/plugins-inputs-jdbc.html](https://www.elastic.co/guide/en/logstash/current/plugins-inputs-jdbc.html)

---

<div class="post-metadata">

**Author:** ![Diego\_Gomez](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/diego_gomez/32/12893_2.png) [@Diego\_Gomez](https://discuss.elastic.co/u/Diego_Gomez)\
**Post date:** [November 22, 2016, 6:18pm UTC](https://discuss.elastic.co/t/database-en-elasticsearch/66889/11 "2016-11-22T18:18:46Z")

</div>

El meshlium me dice que cree esta base de datos,

```
  CREATE database MeshliumDB;
  
Just copy paste:
CREATE TABLE IF NOT EXISTS `sensorParser` (
`id` int(11) NOT NULL auto_increment,
`id_wasp` text character set utf8 collate utf8_unicode_ci,
`id_secret` text character set utf8 collate utf8_unicode_ci,
`frame_type` int(11) default NULL,
`frame_number` int(11) default NULL,
` sensor` text character set utf8 collate utf8_unicode_ci,
`value` text character set utf8 collate utf8_unicode_ci,
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
`raw` text character set utf8 collate utf8_unicode_ci,
`parser_type` tinyint(3) NOT NULL default '0',
 PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

  

GRANT ALL PRIVILEGES ON *.* TO root@'%' IDENTIFIED BY 'passw';
```

---

<div class="post-metadata">

**Author:** ![gmoskovicz](https://avatars.discourse-cdn.com/v4/letter/g/ecae2f/32.png) [@gmoskovicz](https://discuss.elastic.co/u/gmoskovicz)\
**Post date:** [November 22, 2016, 6:30pm UTC](https://discuss.elastic.co/t/database-en-elasticsearch/66889/12 "2016-11-22T18:30:21Z")

</div>

Yo crearia la DB, y luego probaria que funcione sin Logstash.

Luego probaria Logstash y tomar datos desde la DB que estas creando. Y luego verificar que esta funcionado bien.

---

<div class="post-metadata">

**Author:** ![Diego\_Gomez](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/diego_gomez/32/12893_2.png) [@Diego\_Gomez](https://discuss.elastic.co/u/Diego_Gomez)\
**Post date:** [November 24, 2016, 4:19pm UTC](https://discuss.elastic.co/t/database-en-elasticsearch/66889/13 "2016-11-24T16:19:37Z")

</div>

Hola gabriel ya tengo el equipo conectado a una base de datos en el mismo servidor que logstash, estoy un poco perdido como configurar esto.

```
input {
  jdbc {
  jdbc_driver_library => "mysql-connector-java-5.1.36-bin.jar"
  jdbc_driver_class => "com.mysql.jdbc.Driver"
  jdbc_connection_string => "jdbc:mysql://localhost:3306/mydb"
  jdbc_user => "mysql"
  parameters => { "favorite_artist" => "Beethoven" }
  schedule => "* * * * *"
  statement => "SELECT * from songs where artist = :favorite_artist"
  }
}

```

Yo quiero recibir todo los datos.

---

<div class="post-metadata">

**Author:** ![gmoskovicz](https://avatars.discourse-cdn.com/v4/letter/g/ecae2f/32.png) [@gmoskovicz](https://discuss.elastic.co/u/gmoskovicz)\
**Post date:** [November 24, 2016, 4:39pm UTC](https://discuss.elastic.co/t/database-en-elasticsearch/66889/14 "2016-11-24T16:39:14Z")

</div>

Hola @Diego_Gomez

Te recomiendo que comiences por leer el link: [https://www.elastic.co/guide/en/logstash/current/plugins-inputs-jdbc.html](https://www.elastic.co/guide/en/logstash/current/plugins-inputs-jdbc.html)

Aqui explica como utilizarlo y cada una de las opciones en detalle. Luego recomiendo que pruebes esto un poco en un ambiente de testing para entenderlo mejor!

Saludos!

---

<div class="post-metadata">

**Author:** ![Diego\_Gomez](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/diego_gomez/32/12893_2.png) [@Diego\_Gomez](https://discuss.elastic.co/u/Diego_Gomez)\
**Post date:** [November 25, 2016, 2:34pm UTC](https://discuss.elastic.co/t/database-en-elasticsearch/66889/15 "2016-11-25T14:34:37Z")

</div>

Hola gabriel estoy teniendo problemas con logtash que no encuentra los archivos, esto es lo que tengo y que es lo que me tira de error.  
En parametrers no se que poner quiero que venga toda lña informacón  
Gracias por la ayuda.

```
input {
 jdbc {
jdbc_driver_library => "mysql-connector-java-5.1.36-bin.jar"
jdbc_driver_class => "com.mysql.jdbc.Driver"
jdbc_connection_string => "jdbc:mysql://localhost:3306/mydb"
jdbc_user => "mysql"
#parameters => { "favorite_artist" => "Beethoven" }
schedule => "* * * * *"
statement => "SELECT * from songs where artist = :favorite_artist"
    }
  }

 rack@rack-Not-Specified:~/logstash-5.0.0$ sudo bin/logstash -f /home/rack/tcp.co nf
 [sudo] password for rack:
 Sending Logstash logs to /home/rack/logstash-5.0.0/logs which is now configured via log4j2.properties.
 LoadError: no such file to load -- /home/rack/mysql-connector-java- 5.1.40
             require at org/jruby/RubyKernel.java:1040
              require at /home/rack/logstash- 5.0.0/vendor/bundle/jruby/1.9/gems/polyglot-0.3.5/lib/polyglot.rb:65
            load_drivers at /home/rack/logstash- 5.0.0/vendor/bundle/jruby/1.9/gems/logstash-input-jdbc- 4.1.3/lib/logstash/plugin_mixins/jdbc.rb:134
                 each at org/jruby/RubyArray.java:1613
             load_drivers at /home/rack/logstash- 5.0.0/vendor/bundle/jruby/1.9/gems/logstash-input-jdbc- 4.1.3/lib/logstash/plugin_mixins/jdbc.rb:132
            prepare_jdbc_connection at /home/rack/logstash- 5.0.0/vendor/bundle/jruby/1.9/gems/logstash-input-jdbc- 4.1.3/lib/logstash/plugin_mixins/jdbc.rb:146
                  register at /home/rack/logstash- 5.0.0/vendor/bundle/jruby/1.9/gems/logstash-input-jdbc- 4.1.3/lib/logstash/inputs/jdbc.rb:191
            start_inputs at /home/rack/logstash-5.0.0/logstash- core/lib/logstash/pipeline.rb:319
                   each at org/jruby/RubyArray.java:1613
            start_inputs at /home/rack/logstash-5.0.0/logstash- core/lib/logstash/pipeline.rb:318
             start_workers at /home/rack/logstash-5.0.0/logstash- core/lib/logstash/pipeline.rb:195
                    run at /home/rack/logstash-5.0.0/logstash-core/lib/logstash/pipeline.rb:153
             start_pipeline at /home/rack/logstash-5.0.0/logstash- core/lib/logstash/agent.rb:250
```

---

<div class="post-metadata">

**Author:** ![gmoskovicz](https://avatars.discourse-cdn.com/v4/letter/g/ecae2f/32.png) [@gmoskovicz](https://discuss.elastic.co/u/gmoskovicz)\
**Post date:** [November 25, 2016, 3:03pm UTC](https://discuss.elastic.co/t/database-en-elasticsearch/66889/16 "2016-11-25T15:03:52Z")

</div>

> [@Diego\_Gomez](#):
>
> no such file to load -- /home/rack/mysql-connector-java- 5.1.40

Este pareceria ser el error ^.

Tienes bien puesto el file? Tal vez solamente arreglando el jdc driver esto deberia funcionar.

Saludos!

---

<div class="post-metadata">

**Author:** ![Diego\_Gomez](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/diego_gomez/32/12893_2.png) [@Diego\_Gomez](https://discuss.elastic.co/u/Diego_Gomez)\
**Post date:** [November 25, 2016, 3:06pm UTC](https://discuss.elastic.co/t/database-en-elasticsearch/66889/17 "2016-11-25T15:06:39Z")

</div>

Hola gabriel si ahi ya lo arregle y me toma bien, pero me dice que no tiene permiso para entrar con user ´´@10.10.0.132 pero yo cree un usuario para esto, hay que decirle en el conector de alguna manera que usuario debe tomar?

---

<div class="post-metadata">

**Author:** ![gmoskovicz](https://avatars.discourse-cdn.com/v4/letter/g/ecae2f/32.png) [@gmoskovicz](https://discuss.elastic.co/u/gmoskovicz)\
**Post date:** [November 25, 2016, 3:18pm UTC](https://discuss.elastic.co/t/database-en-elasticsearch/66889/18 "2016-11-25T15:18:23Z")

</div>

Exacto!

User: [https://www.elastic.co/guide/en/logstash/current/plugins-inputs-jdbc.html#plugins-inputs-jdbc-jdbc\_user](https://www.elastic.co/guide/en/logstash/current/plugins-inputs-jdbc.html#plugins-inputs-jdbc-jdbc_user)  
Password (si es necesario): [https://www.elastic.co/guide/en/logstash/current/plugins-inputs-jdbc.html#plugins-inputs-jdbc-jdbc\_password](https://www.elastic.co/guide/en/logstash/current/plugins-inputs-jdbc.html#plugins-inputs-jdbc-jdbc_password)

---

<div class="post-metadata">

**Author:** ![Diego\_Gomez](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/diego_gomez/32/12893_2.png) [@Diego\_Gomez](https://discuss.elastic.co/u/Diego_Gomez)\
**Post date:** [November 25, 2016, 3:38pm UTC](https://discuss.elastic.co/t/database-en-elasticsearch/66889/19 "2016-11-25T15:38:36Z")

</div>

> [@Diego\_Gomez](#):
>
> log-sensores-

Ahi logre conectarme pero no se que poner parametrer porque eso me da error

---

<div class="post-metadata">

**Author:** ![Diego\_Gomez](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/diego_gomez/32/12893_2.png) [@Diego\_Gomez](https://discuss.elastic.co/u/Diego_Gomez)\
**Post date:** [November 25, 2016, 3:52pm UTC](https://discuss.elastic.co/t/database-en-elasticsearch/66889/20 "2016-11-25T15:52:44Z")

</div>

> [@Diego\_Gomez](#):
>
> parameters =\> { "favorite\_artist" =\> "Beethoven" }  
> schedule =\> "\* \* \* \* \*"  
> statement =\> "SELECT \* from songs where artist = :favorite\_artist"

No se bien como porner estos datos me podras ayudar? necesito que se sincrinice cada minuto y que pase toda la tabla.

```
         parameters => { "favorite_artist" => "Beethoven" }
         schedule => "* * * * *"
         statement => "SELECT * from songs where artist = :favorite_artist"

```

[Next page](https://discuss.elastic.co/t/database-en-elasticsearch/66889.md?page=2)