# Database en elasticsearch **URL:** \ **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:** 16\ **Page:** 2
**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, 4:18pm UTC](https://discuss.elastic.co/t/database-en-elasticsearch/66889/21 "2016-11-25T16:18:51Z")
@Diego_Gomez Pasar toda la tabla? O pasar todos los valores nuevos desde el ultimo registro que ya sincronizo? Saludos ---
**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, 4:27pm UTC](https://discuss.elastic.co/t/database-en-elasticsearch/66889/22 "2016-11-25T16:27:15Z")
Pasar los datos actualizados cada minuto, los sensores emiten un dato cada 10 segundos yo quisiera sincronizar cada minuto y copiar los datos esos ---
**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 29, 2016, 3:37pm UTC](https://discuss.elastic.co/t/database-en-elasticsearch/66889/23 "2016-11-29T15:37:45Z")
Hola todavía no encuentro la manera de darle los parámetros para que logstash tome tome los datos de mi base de datos, si alguien me puede indicar como hacerlo se lo agradezco mucho. Saludos ---
**Author:** ![gmoskovicz](https://avatars.discourse-cdn.com/v4/letter/g/ecae2f/32.png) [@gmoskovicz](https://discuss.elastic.co/u/gmoskovicz)\ **Post date:** [November 29, 2016, 3:51pm UTC](https://discuss.elastic.co/t/database-en-elasticsearch/66889/24 "2016-11-29T15:51:43Z")
> [@Diego\_Gomez](#): > > parameters =\> { "favorite\_artist" =\> "Beethoven" } > schedule =\> "\* \* \* \* \*" > statement =\> "SELECT \* from songs where artist = :favorite\_artist" Hola diego, tienes que utilizar ningun parametro, luego un schedule cada 10 segundos, y finalmente en el statement (SQL query) utilizar `sql_last_value` para tomar todos los valores que correspondan que el date\_time o el valor que guarda en forma de fecha sea mayor a `sql_last_value`. Pruebalo y te datas cuenta. Saludos! ---
**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 29, 2016, 4:21pm UTC](https://discuss.elastic.co/t/database-en-elasticsearch/66889/25 "2016-11-29T16:21:20Z")
Hola Grabriel perdona mi ingnoracia pero no me doy cuenta como hacerlo me podrás dar un ejemplo como seria lo que tengo que escribir, perdón de nuevo esto es nuevo para mi. ---
**Author:** ![gmoskovicz](https://avatars.discourse-cdn.com/v4/letter/g/ecae2f/32.png) [@gmoskovicz](https://discuss.elastic.co/u/gmoskovicz)\ **Post date:** [November 29, 2016, 4:35pm UTC](https://discuss.elastic.co/t/database-en-elasticsearch/66889/26 "2016-11-29T16:35:19Z")
``` schedule => "* * * * *" statement => "SELECT * from songs where artist = :favorite_artist" } ``` Algo asi como: ``` schedule => "*/5 * * * *" statement => "SELECT * from where >= :sql_last_value" } ``` `*/5 * * * *` es cada 5 minutos. Statement contiene el comando que necesitas como ejemplo. Saludos! ---
**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 29, 2016, 4:37pm UTC](https://discuss.elastic.co/t/database-en-elasticsearch/66889/27 "2016-11-29T16:37:07Z")
Gracias voy a intentar. ---
**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 29, 2016, 4:56pm UTC](https://discuss.elastic.co/t/database-en-elasticsearch/66889/28 "2016-11-29T16:56:31Z")
Hola me da este error [2016-11-29T13:50:57,538][INFO][logstash.agent] Successfully started Logstash API endpoint {:port=\>9600} [2016-11-29T13:55:00,241][INFO][logstash.inputs.jdbc] (0.036000s) SELECT version() AS `v` LIMIT 1 [2016-11-29T13:55:00,333][ERROR][logstash.inputs.jdbc] Java::ComMysqlJdbcExceptionsJdbc4::MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' where \<2016-11-29 13:47:11\> \>= '1970-01-01 00:00:00'' at line 1: SELECT \* from where \<2016-11-29 13:47:11\> \>= '1970-01-01 00:00:00' [2016-11-29T13:55:00,346][WARN][logstash.inputs.jdbc] Exception when executing JDBC query {:exception=\>#\ \>= '1970-01-01 00:00:00'' at line 1\>} ---
**Author:** ![gmoskovicz](https://avatars.discourse-cdn.com/v4/letter/g/ecae2f/32.png) [@gmoskovicz](https://discuss.elastic.co/u/gmoskovicz)\ **Post date:** [November 29, 2016, 5:09pm UTC](https://discuss.elastic.co/t/database-en-elasticsearch/66889/29 "2016-11-29T17:09:02Z")
Tenes que parsear la fecha tuya o de alguna manera para que sea comparacion de fechas como maneja en: [http://stackoverflow.com/a/33803288](http://stackoverflow.com/a/33803288). Otros ejemplos: [http://stackoverflow.com/questions/29245754/mysql-where-clause-with-date-format](http://stackoverflow.com/questions/29245754/mysql-where-clause-with-date-format) ---
**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 29, 2016, 6:36pm UTC](https://discuss.elastic.co/t/database-en-elasticsearch/66889/30 "2016-11-29T18:36:12Z")
Hola esta es mi configuración ``` input { jdbc { jdbc_driver_library => "/home/rack/mysql-connector-java-5.1.40/mysql-connector-java-5.1.40-bin.jar" jdbc_driver_class => "com.mysql.jdbc.Driver" jdbc_connection_string => "jdbc:mysql://10.10.0.132:3306/testdb" jdbc_user => "user" jdbc_password => "rack" schedule => "*/1 * * * *" statement => "select id, DATE_FORMAT(timestamp, '%Y-%m-%d %T') AS id, id_wasp, id_secret, frame_type, frame_number, sensor, value, timestamp, raw, parser_type, trans_id from testdb where timestamp >= :sql_last_start" } } filter { date { locale => "en" timezone => "ART" match => ["timestamp", "YYYY-MM-dd HH:mm:ss"] target => "@timestamp" } } output { elasticsearch { hosts => ["10.10.0.132:9200"] index => "audit-%{+YYYY.MM.dd}" document_id => "%{id}" } stdout { codec => rubydebug } } ``` Este es lo que me tira [2016-11-29T15:29:30,222][INFO][logstash.outputs.elasticsearch] New Elasticsearch output {:class=\>"LogStash::Outputs::ElasticSearch", :hosts=\>["10.10.0.132:9200"]} [2016-11-29T15:29:30,256][ERROR][logstash.agent] Pipeline aborted due to error {:exception=\>#\, :backtrace=\>["/home/rack/logstash-5.0.0/vendor/bundle/jruby/1.9/gems/logstash-filter-date-3.0.3/lib/logstash/filters/date.rb:297:in `setupMatcher'", "org/jruby/RubyArray.java:1613:in`each'", "/home/rack/logstash-5.0.0/vendor/bundle/jruby/1.9/gems/logstash-filter-date-3.0.3/lib/logstash/filters/date.rb:224:in `setupMatcher'", "/home/rack/logstash-5.0.0/vendor/bundle/jruby/1.9/gems/logstash-filter-date-3.0.3/lib/logstash/filters/date.rb:188:in`register'", "/home/rack/logstash-5.0.0/logstash-core/lib/logstash/pipeline.rb:197:in `start_workers'", "org/jruby/RubyArray.java:1613:in`each'", "/home/rack/logstash-5.0.0/logstash-core/lib/logstash/pipeline.rb:197:in `start_workers'", "/home/rack/logstash-5.0.0/logstash-core/lib/logstash/pipeline.rb:153:in`run'", "/home/rack/logstash-5.0.0/logstash-core/lib/logstash/agent.rb:250:in `start\_pipeline'"]} [2016-11-29T15:29:30,348][INFO][logstash.agent] Successfully started Logstash API endpoint {:port=\>9600} [2016-11-29T15:29:33,278][WARN][logstash.agent] stopping pipeline {:id=\>"main"} ---
**Author:** ![gmoskovicz](https://avatars.discourse-cdn.com/v4/letter/g/ecae2f/32.png) [@gmoskovicz](https://discuss.elastic.co/u/gmoskovicz)\ **Post date:** [November 29, 2016, 7:04pm UTC](https://discuss.elastic.co/t/database-en-elasticsearch/66889/31 "2016-11-29T19:04:02Z")
Hola Diego! Aqui ya es un problema del date filter. Pareceria que no le gusta el locale o el timezone. Creo que el timezone esta incorrecto. No existe un ART timezone, deberia de ser lo siguiente: ```auto date { locale => "en" timezone => "America/Buenos_Aires" match => ["timestamp", "YYYY-MM-dd HH:mm:ss"] target => "@timestamp" } ``` Saludos! --Gabriel ---
**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 29, 2016, 7:30pm UTC](https://discuss.elastic.co/t/database-en-elasticsearch/66889/32 "2016-11-29T19:30:30Z")
> [@gmoskovicz](#): > > date { > locale =\> "en" > timezone =\> "America/Buenos\_Aires" > match =\> ["timestamp", "YYYY-MM-dd HH:mm:ss"] > target =\> "@timestamp" > } Gabriel me esta pidiendo esto Compruebe el manual que corresponde a su versión del servidor MySQL para la sintaxis correcta para usar cerca de ': sql\_last\_start' en la línea 1\>} ---
**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 30, 2016, 3:23pm UTC](https://discuss.elastic.co/t/database-en-elasticsearch/66889/33 "2016-11-30T15:23:51Z")
hola gabriel ahi logre que me diera datos el logstash pero me dio la primera ves y despues me tira esto ``` "@timestamp" => 2016-11-30T14:31:25.789Z, " @version" => "1", "sensor" => "TCA", "id" => "2016-11-29 17:33:23", "value" => "22.26", "timestamp" => 2016-11-29T20:33:23.000Z } { "id_secret" => "408425467", "raw" => "noraw", "parser_type" => 1, "frame_type" => 128, "frame_number" => 204, "id_wasp" => "Waspmote_PRO", "tags" => [ [0] "_dateparsefailure" ], "@timestamp" => 2016-11-30T14:31:25.789Z, "@version" => "1", "sensor" => "TCA", "id" => "2016-11-29 17:33:29", "value" => "22.26", "timestamp" => 2016-11-29T20:33:29.000Z } [2016-11-30T11:32:00,309][INFO]logstash.inputs.jdbc select id, DATE_FORMAT(timestamp, '%Y-%m-%d %T') AS id, id_wasp, id_secret, frame_type, frame_number, sensor, value, timestamp, raw, parser_type, timestamp from testdb.sensorParserwhere timestamp >= '2016-11-30 14:31:26' ``` ---
**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 30, 2016, 3:25pm UTC](https://discuss.elastic.co/t/database-en-elasticsearch/66889/34 "2016-11-30T15:25:19Z")
Ahora solo me muestra esto ``` [2016-11-30T12:23:00,098][INFO][logstash.inputs.jdbc] (0.084000s) select id, DATE_FORMAT(timestamp, '%Y-%m-%d %T') AS id, id_wasp, id_secret, frame_type, frame_number, sensor, value, timestamp, raw, parser_type, timestamp from `testdb`.`sensorParser`where timestamp >= '2016-11-30 15:22:00' [2016-11-30T12:24:00,144][INFO][logstash.inputs.jdbc] (0.072000s) select id, DATE_FORMAT(timestamp, '%Y-%m-%d %T') AS id, id_wasp, id_secret, frame_type, frame_number, sensor, value, timestamp, raw, parser_type, timestamp from `testdb`.`sensorParser`where timestamp >= '2016-11-30 15:23:00' ``` ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [December 28, 2016, 3:25pm UTC](https://discuss.elastic.co/t/database-en-elasticsearch/66889/35 "2016-12-28T15:25:52Z")
Closed on December 28, 2016, 3:25pm UTC This topic was automatically closed 28 days after the last reply. New replies are no longer allowed. ---
**Author:** ![gmoskovicz](https://avatars.discourse-cdn.com/v4/letter/g/ecae2f/32.png) [@gmoskovicz](https://discuss.elastic.co/u/gmoskovicz)\ **Post date:** [April 17, 2017, 4:10pm UTC](https://discuss.elastic.co/t/database-en-elasticsearch/66889/36 "2017-04-17T16:10:37Z")
Hola @Diego_Gomez Alguna noticia de esto? Saludos! [Previous page](https://discuss.elastic.co/t/database-en-elasticsearch/66889.md?page=1)