Variables in SQL statements, running only after first statement

[Alex_Korn]

Hello all
I'm new to logstash, and at the moment I'm playing around with my sql-requests, the almost all of them are using temp variable. It
I'm running sql-request like select my_id from table1 where date > $start and date < $end
$start and $end are timestamps which i'm setting before running this requests.

After executing a statement, I got a list with my id's and then I'm running new bunch of sql-requests like select Max(message) from table2 where id_ba_fa = '$my_id'. ($my_id is variable which will be replaced with real my_id that I've got from first request.

So first I'm getting list of id's from time range, then I need exact data from every id I've become.

It approach works fine in my old tool, but since I want to use logstash, I don't understand how to config the same statements. Any suggestion, help?

[ptamba]

1. for the variables, you can store them in environment variables then reference them in your jdbc config file. see here for documentation about environment variables

2. for the second scenario , you will need two jdbc filter. say your first jdbc filter contains this statement

@Alex_Korn

select my_id from table1 where date > $start and date < $en d

that filter will produce a field called my_id

you can then call it with second jdbc filter

if [my_id] 
{
  jdbc { 
     #some jdbc settings 
      statement => “select Max(message) from table2 where id_ba_fa = %{my_id}”
  }
} 

use %{field_name} to access the value of field_name

[Alex_Korn]

@ptamba

for the variables, you can store them in environment variables then reference them in your jdbc config file. see here for documentation about environment variables

Even for sql statements? Hm.

I'll take a look at filter and if-statement, thanks.
Should I copy all jdbc settings, when I'm running any statement?

[ptamba]

yes, you can use env var anywhere in the config file. and yes, you need all required jdbc settings anytime you initiate a jdbc filter

[Alex_Korn]

Nah, can't figured out how the settings should be. I tried the filter you shared, with "if", but failed.
When I'm putting %{my_id} into sql statement, I'm getting error SQLSyntaxErrorException, invalid character.

My settings in logstash are, and I'm totally sure it's wrong. Do I didn't add %{my_id}to global environment variable, btw? The idea was to use the my_id value after running first statement, because if have no id, other statements are useless.

input {
	jdbc {
		some jdbc_settings
		statement => "select my_id from table1 where date >= (1588291200)"
	}

if [my_id] 
{	
	jdbc {
		some jdbc_settings
		statement => "select sum(message) from table2 where date  >= (1588291200) and param1= 'value1' and param2 =%{request_id}"
	}	
}
} 
output {
	elasticsearch {
		hosts => "localhost:9200"
		index => "my_test"
	}
	stdout {
		codec => rubydebug
	}
}

I thought statement "if" should equals to something like if [my_id] is not null or similar

[Badger]

You cannot reference a field of an event, like [my_id], in the input section since there are no events when the inputs are being configured. Note that ptamba was suggesting the use of a filter, not a second input. If you want to use the value of my_id in a query that would look something like

input {
	jdbc {
		some jdbc_settings
		statement => "select my_id from table1 where date >= (1588291200)"
	}
}
filter {
    if [my_id] {	
	    jdbc_streaming {
    		some jdbc_settings
            statement => "select sum(message) from table2 where date  >= (1588291200) and param1= 'value1' and param2 = :request_id"
            parameters => { request_id => my_id }
    	}	
    }
}