Logstash JDBC query using joins

Elastic Stack Logstash
1

Hello @dadoonet,

I have the ELK stack installed and all three are communicating correctly. I'm trying to get logstash to query a 'mysql' database. My query contains joins from multiple tables. Can we used joins using “statement” or “statement_filepath”?

If I have written my sql join query in one of the .sql file and provides that filepath to “statement_filepath”, will this work?

Thanks & Regards,
Priyanka Yerunkar.

2

Hello,

I've done this for Oracle JDBC connection. I have my query stored in the .sql file with joins and :sql_last_value used within. Everything works well.

3

Hello @saif3r ,

Thanks for replying.

So we have to mention timestamp value as “:sql_last_value “ in .sql file.

Could you please provide sample example?

Thanks & Regards,

Priyanka Yerunkar

4

Sure, there you go.

select
b.transaction_date as transaction_timestamp,
a.*,
b.*
from
dict a
left join transactions b on a.name = b.name
where
b.transaction_date > :sql_last_value

Configuration parts required for it:

statement_filepath => "/queries/oracle_query.sql"
use_column_value => true
tracking_column_type => "timestamp"
tracking_column => "transaction_timestamp"
last_run_metadata_path => "/usr/share/logstash/last_run_metadata/.oracle_query"
5

Hi @saif3r

Thanks for your update!!!!
one question regarding :last_run_metadata_path. which path we have to mention here?

Thanks,
Priyanka Yerunkar

6

This is the place where logstash stores timestamp/numeric value, used in next run of the pipeline as a reference in place of :sql_last_value. So in your case file .oracle_query will store transaction_timestamp from last row of it's previous run.
More info can be found here: https://www.elastic.co/guide/en/logstash/current/plugins-inputs-jdbc.html#_state

7

Hi @saif3r,

Thanks for your quick reply!!!

I have passed value for “:last_run_metadata_path”, but it is giving me error as No such file or directory.

So we have to create “.oracle_query” file manually? Could you please guide more on this?

Thanks,
Priyanka

8

I think the path (/usr/share/logstash/last_run_metadata/) needs to exist, but the file itself (.oracle_query) is created automatically upon first pipieline run.
Make sure that /last_run_metadata/ is owned by logstash user.

9

Hi @saif3r,

Thanks for your help!!!!!

So file path ( /usr/share/logstash/last_run_metadata/) was not there. I have now manually created that and it is not giving me any error.

But now I am getting an error while executing sql query. Kindly look at below query I have created and passed “ :sql_last_value” as mentioned.

So for “:sql_last_value”, we have to specify any number or pass the same value?

select b.*, a.External_link_Name as ext_link from externallinks a, externallinkdetails b where b.externalsysid= a.externalsystemid and a.status = 1 and b.status =1 and rownum < 99999999999999999999999999 > :sql_last_value

Thanks,
Priyanka

10

This part is not valid:

and rownum < 99999999999999999999999999 > :sql_last_value

Not only it has a wrong syntax, as you are using two comparison operators (< and >) within one but also does not make much sense. Rownum depends on current data set, and there's no point using it for :sql_last_value.
But if you want to give it a shot anyways, alter the last line to:

and rownum > :sql_last_value

Initial query should result with rownum > null.

11

Hello @saif3r,

Thanks for your great help!!! Now I am able to fetch data from .sql query.

Now I am able to fetch data from one query. But I want to fetch it from another 6 - 7 queries and combine data in one single output.

Would be possible using logstash? Could you please help me with the same?

Thanks & Regards,

Priyanka Yerunkar

12

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