Is it possible to overwrite the select statement each time when db is called

Elastic Stack Logstash
1

i have two dates, eg :(from)2018/01/01 to now ..i have to fetch the records between these two dates and after fetching from will be replaced with now timing and now with new timestamp..how can v do these??
any body??any suggestion??

2

Can you not just have two where clauses: one that selects everything newer than the specified cutoff date and one that is based on sql_last_value functionality? The first run there would be no sql_last_value and all rows would be selected. After that only new data based on the sql_last_value could be selected.

3

i dont want new updated data ,,i want to find data between two dates,,
eg:"SELECT * from contacts where date between "2018-01-01 11:11:19.754371" and CURRENT_TIMESTAMP "
after executing these i have to store the current timestamp in log file and execute the query like this
"SELECT * from contacts where date between "old current timestamp" and new CURRENT_TIMESTAMP " alternatively

4

The sql_last_value for each run records the last value processed internally and then uses this for the next query. It should behave like you are describing.

5

let me try...thank you for ur reply

6

is it possible to get the records from last run time to now
eg:"SELECT * from contacts where date between 'last_run_time' and CURRENT_TIMESTAMP "
sql_last_value wont fit for this query ,,think so
Exception when executing JDBC query {:exception=>#<Sequel::DatabaseError: Java::OrgPostgresqlUtil::PSQLException: ERROR: invalid input syntax for type timestamp: "sql_last_value"
Position: 76>}

7

@Christian_Dahlqvist any idea

8

What did your configuration look like when you got that error?

9

file: lastrun_update.config

input {
jdbc {
jdbc_connection_string => "jdbc:postgresql://localhost:5432/postgres"
jdbc_user => "postgres"
jdbc_password =>"***"
jdbc_driver_library => "C:\logstash-6.3.0\postgresql-42.2.2.jar"
jdbc_driver_class => "org.postgresql.Driver"
last_run_metadata_path => 'C:\Users\Gokul.logstash_jdbc_last_run'
record_last_run => true
jdbc_paging_enabled => "true"
jdbc_page_size => "50000"
schedule =>' * * * *'

    statement => "SELECT * from contacts where date between '2018-01-01 11:11:19.754371' and  CURRENT_TIMESTAMP "   
	  }
}

output{
file{
path=>"C:\logstash-6.3.0\map.log"
}
}
i want alternative date to be done eg:"SELECT * from contacts where date between "old current timestamp" and new CURRENT_TIMESTAMP "

10

Why not instead try SELECT * from contacts where date > :sql_last_value ?

The tracking column will be updated for each run so only more recent rows that the last run will be selected.

11

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