# Loading incremental data into Elasticsearch from Oracle database **URL:** \ **Category:** Logstash\ **Created:** [September 27, 2017, 4:14pm UTC](https://discuss.elastic.co/t/loading-incremental-data-into-elasticsearch-from-oracle-database/102007 "2017-09-27T16:14:35Z")\ **Posts on this page:** 19\ **Page:** 1
**Author:** ![Newuser](https://avatars.discourse-cdn.com/v4/letter/n/3e96dc/32.png) [@Newuser](https://discuss.elastic.co/u/Newuser)\ **Post date:** [September 27, 2017, 4:14pm UTC](https://discuss.elastic.co/t/loading-incremental-data-into-elasticsearch-from-oracle-database/102007/1 "2017-09-27T16:14:35Z")
Hi all, I need to Load incremental data into Elasticsearch from Oracle database, after the first load ( via Logstash), i need to load only the data updated. Could you advise on this please? Regrads, ---
**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\ **Post date:** [September 27, 2017, 8:05pm UTC](https://discuss.elastic.co/t/loading-incremental-data-into-elasticsearch-from-oracle-database/102007/2 "2017-09-27T20:05:07Z")
Does the data have a last modified column that you can use to select only the rows that have changed since a certain point in time? ---
**Author:** ![Newuser](https://avatars.discourse-cdn.com/v4/letter/n/3e96dc/32.png) [@Newuser](https://discuss.elastic.co/u/Newuser)\ **Post date:** [September 28, 2017, 8:34am UTC](https://discuss.elastic.co/t/loading-incremental-data-into-elasticsearch-from-oracle-database/102007/3 "2017-09-28T08:34:31Z")
Yes we have that column "HistCreationTime". How i can use this column? Thank you! ---
**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\ **Post date:** [September 28, 2017, 10:18am UTC](https://discuss.elastic.co/t/loading-incremental-data-into-elasticsearch-from-oracle-database/102007/4 "2017-09-28T10:18:43Z")
Have you read the State and Predefined parameters section of the jdbc input documentation? ---
**Author:** ![Newuser](https://avatars.discourse-cdn.com/v4/letter/n/3e96dc/32.png) [@Newuser](https://discuss.elastic.co/u/Newuser)\ **Post date:** [September 28, 2017, 11:59am UTC](https://discuss.elastic.co/t/loading-incremental-data-into-elasticsearch-from-oracle-database/102007/5 "2017-09-28T11:59:56Z")
I have read about sql\_last\_value and schedule parameter the schedule works find, but i don't know how to use sql\_last\_value! , i already use jdbc\_validate\_connections, jdbc\_user, password, driver in the input of Logstash. But i can't see last\_run\_metadata\_path, tracking\_column\_type, tracking\_column etc ... Where can i find them? Maybe there are another jdbc plugin i have to install? Thank you, ---
**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\ **Post date:** [September 28, 2017, 12:05pm UTC](https://discuss.elastic.co/t/loading-incremental-data-into-elasticsearch-from-oracle-database/102007/6 "2017-09-28T12:05:26Z")
sql\_last\_value is the name of a query parameter that you can reference in your SQL query. Logstash will populate that parameter with either the time or the value it processed the last time, so you'd typically use it like in this example: [Jdbc input plugin | Logstash Reference [8.11] | Elastic](https://www.elastic.co/guide/en/logstash/current/plugins-inputs-jdbc.html#plugins-inputs-jdbc-tracking_column_type) > But i can't see last\_run\_metadata\_path, tracking\_column\_type, tracking\_column etc ... > Where can i find them? Find what? Their documentation? ---
**Author:** ![Newuser](https://avatars.discourse-cdn.com/v4/letter/n/3e96dc/32.png) [@Newuser](https://discuss.elastic.co/u/Newuser)\ **Post date:** [September 28, 2017, 12:09pm UTC](https://discuss.elastic.co/t/loading-incremental-data-into-elasticsearch-from-oracle-database/102007/7 "2017-09-28T12:09:50Z")
Find those parameters\> For example when i run logstash i have error said "last\_run\_metadata\_path" doesn't exist ---
**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\ **Post date:** [September 28, 2017, 1:09pm UTC](https://discuss.elastic.co/t/loading-incremental-data-into-elasticsearch-from-oracle-database/102007/8 "2017-09-28T13:09:19Z")
What does your configuration look like? Please copy/paste the exact error message. Also, what version of the logstash-input-jdbc plugin do you have (run `logstash-plugin list --version` to find out)? ---
**Author:** ![Newuser](https://avatars.discourse-cdn.com/v4/letter/n/3e96dc/32.png) [@Newuser](https://discuss.elastic.co/u/Newuser)\ **Post date:** [September 29, 2017, 9:51am UTC](https://discuss.elastic.co/t/loading-incremental-data-into-elasticsearch-from-oracle-database/102007/9 "2017-09-29T09:51:57Z")
> [@magnusbaeck](#): > > What does your configuration look like? Please copy/paste the exact error message. Also, what version of the logstash-input-jdbc plugin do you have (run logstash-plugin list --version to find out)? The version of Logstash is 5.2.1 The version of logstash-input-jdbc-4.1.3 Below the link of my logstash-input-jdbc: C:\tmp\logstash-5.2.1\vendor\bundle\jruby\1.9\gems\logstash-input-jdbc-4.1.3\lib\logstash Here is an example of my configuration: input { jdbc { jdbc\_validate\_connection =\> true jdbc\_connection\_string =\> "jdbc:oracle:thin:@10.0.22.56:1521/DBNAME" jdbc\_user =\> "QUOD301PRD" jdbc\_password =\> "password" jdbc\_driver\_library =\> "C:\tmp\logstash-5.2.1\drivers\ojdbc7.jar" jdbc\_driver\_class =\> "Java::oracle.jdbc.driver.OracleDriver" jdbc\_paging\_enabled =\> "true" # jdbc\_page\_size =\> "50000" # schedule =\> "30 10 \* \* \*" schedule =\> "0 6 \* \* \*" statement =\> "SELECT \* FROM ORDR WHERE ParentOrdID like 'AO%' where id \> '13/09/2017 20:50:07'" use\_column\_value =\> true tracking\_column =\> "id" tracking\_column\_type =\> "numeric" # clean\_run =\> true last\_run\_metadata\_path =\> "/path/.logstash\_jdbc\_last\_run" } } # filter { ``` # Set the timestamp to that of the ASH sample, not current time. ``` # mutate { convert =\> ["sample\_time" , "string"]} # date { match =\> ["sample\_time", "ISO8601"]} # } output { # stdout { codec =\> rubydebug } stdout { codec =\> json\_lines } elasticsearch { # hosts =\> ["localhost:9200"] index =\> "ordr" document\_type =\> "ORDR" # document\_id =\> "%{id}" hosts =\> "localhost" } } ---
**Author:** ![Newuser](https://avatars.discourse-cdn.com/v4/letter/n/3e96dc/32.png) [@Newuser](https://discuss.elastic.co/u/Newuser)\ **Post date:** [September 29, 2017, 9:52am UTC](https://discuss.elastic.co/t/loading-incremental-data-into-elasticsearch-from-oracle-database/102007/10 "2017-09-29T09:52:55Z")
I don't put the filter it is on comment. Thank you ---
**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\ **Post date:** [September 29, 2017, 10:02am UTC](https://discuss.elastic.co/t/loading-incremental-data-into-elasticsearch-from-oracle-database/102007/11 "2017-09-29T10:02:53Z")
Please copy/paste the exact error message. ---
**Author:** ![Newuser](https://avatars.discourse-cdn.com/v4/letter/n/3e96dc/32.png) [@Newuser](https://discuss.elastic.co/u/Newuser)\ **Post date:** [September 29, 2017, 10:15am UTC](https://discuss.elastic.co/t/loading-incremental-data-into-elasticsearch-from-oracle-database/102007/12 "2017-09-29T10:15:11Z")
There the error: [2017-09-01T14:34:52,536][ERROR][logstash.pipeline] A plugin had an unrecoverable error. Will restart this plugin. Plugin: \true, jdbc\_connection\_string=\>"jdbc:oracle:thin:@10.0.22.56:1521/QDSHIVA1", jdbc\_user=\>"QUOD301PRD", jdbc\_password=\>, jdbc\_driver\_library=\>"C:\tmp\logstash-5.2.1\drivers\ojdbc7.jar", jdbc\_driver\_class=\>"Java::oracle.jdbc.driver.OracleDriver", jdbc\_paging\_enabled=\>true, statement=\>"SELECT \* FROM ORDR WHERE id \> '13/09/2017 20:50:07'", use\_column\_value=\>true, tracking\_column=\>"id", tracking\_column\_type=\>"numeric", clean\_run=\>false, last\_run\_metadata\_path=\>"/tmp/ph/.logstash\_jdbc\_last\_run", id=\>"4ddd743880bb523c24bcbcd4e0884c0295badf07-1", enable\_metric=\>true, codec=\>\"plain\_c10f01b3-cedb-498d-b723-65a6241cc91a", enable\_metric=\>true, charset=\>"UTF-8"\>, jdbc\_page\_size=\>100000, jdbc\_validation\_timeout=\>3600, jdbc\_pool\_timeout=\>5, sql\_log\_level=\>"info", connection\_retry\_attempts=\>1, connection\_retry\_attempts\_wait\_time=\>0.5, parameters=\>{"sql\_last\_value"=\>0}, record\_last\_run=\>true, lowercase\_column\_names=\>true\> Error: No such file or directory - c:/tmp/ph/.logstash\_jdbc\_last\_run ---
**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\ **Post date:** [September 29, 2017, 11:19am UTC](https://discuss.elastic.co/t/loading-incremental-data-into-elasticsearch-from-oracle-database/102007/13 "2017-09-29T11:19:45Z")
That's a completely different problem than what you described earlier. **Always** show the original error messages. It's clearly having issues opening c:/tmp/ph/.logstash\_jdbc\_last\_run. Do c:/tmp and c:/tmp/ph exist? ---
**Author:** ![Newuser](https://avatars.discourse-cdn.com/v4/letter/n/3e96dc/32.png) [@Newuser](https://discuss.elastic.co/u/Newuser)\ **Post date:** [September 29, 2017, 11:35am UTC](https://discuss.elastic.co/t/loading-incremental-data-into-elasticsearch-from-oracle-database/102007/14 "2017-09-29T11:35:50Z")
> [@magnusbaeck](#): > > Do c:/tmp and c:/tmp/ph exist? The c:\tmp is the repertory contain Logstash, Elasticsearch and kibana. I think my mistake is about c:/tmp/ph, this one doesn't exist. I'll try to correct my input confuguration and let you know. Thank you! ---
**Author:** ![leofer](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/leofer/32/71260_2.png) [@leofer](https://discuss.elastic.co/u/leofer)\ **Post date:** [October 7, 2017, 9:30pm UTC](https://discuss.elastic.co/t/loading-incremental-data-into-elasticsearch-from-oracle-database/102007/15 "2017-10-07T21:30:37Z")
> [@magnusbaeck](#): > > earlier Have the problem being solved? Otherwise, I'd consider using an old fashion Python/Ruby script on crontab to save the debug time of a cutting edge system... Ofer ---
**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\ **Post date:** [October 8, 2017, 9:10am UTC](https://discuss.elastic.co/t/loading-incremental-data-into-elasticsearch-from-oracle-database/102007/16 "2017-10-08T09:10:16Z")
> Have the problem being solved? As the OP said, C:/tmp/ph didn't exist which would explain why Logstash couldn't open c:/tmp/ph/.logstash\_jdbc\_last\_run. ---
**Author:** ![Newuser](https://avatars.discourse-cdn.com/v4/letter/n/3e96dc/32.png) [@Newuser](https://discuss.elastic.co/u/Newuser)\ **Post date:** [October 16, 2017, 11:51am UTC](https://discuss.elastic.co/t/loading-incremental-data-into-elasticsearch-from-oracle-database/102007/17 "2017-10-16T11:51:02Z")
Hi Mag, Sorry for the late answer, not yet my fisrt question still search how to Load incremental data into Elasticsearch from Oracle database, after the first load ( via Logstash), only the data updated. As i said, im my oracle database, all my tables have column called histcreationtiontime, this column mention the last update of each record in the table. I don't know how i can use this column. Thank you! ---
**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\ **Post date:** [October 16, 2017, 11:53am UTC](https://discuss.elastic.co/t/loading-incremental-data-into-elasticsearch-from-oracle-database/102007/18 "2017-10-16T11:53:59Z")
Have you looked at the `sql_last_value` query parameter and read what is said about that parameter in the documentation? ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [November 13, 2017, 11:54am UTC](https://discuss.elastic.co/t/loading-incremental-data-into-elasticsearch-from-oracle-database/102007/19 "2017-11-13T11:54:05Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.