Jdbc_streaming with nested elements

Elastic Stack Logstash
1

Hi,

I have 3 tables that has the described connections:

A->B->C

Basically i have FK from A to B and B to C.
I'm trying to put all the data into a unique document with a list of B inside A and a list of C inside B.
This is possible?
I'm using jdbc_streaming to put the B elements into the A, but how do i put the C elements inside the B??

here is an example what i doing:

    input {
      jdbc {
        jdbc_driver_library => "driver"
        jdbc_driver_class => "com.ibm.db2.jcc.DB2Driver"
        jdbc_connection_string => "connectString"
        jdbc_user => "user"
    	jdbc_password => "pwd"
        schedule => "* * * * *"
        statement => "
    		SELECT FILE_ID, STATUS_CODE
    		FROM TABLE_A 
    		WHERE DATE BETWEEN '2019-01-04-16.04.56' AND '2019-01-04-16.04.57'"
    	}
    }
        filter {

        		jdbc_streaming {
        		jdbc_driver_library => "driver"
        		jdbc_driver_class => "com.ibm.db2.jcc.DB2Driver"
        		jdbc_connection_string => "connectString"
        		jdbc_user => "user"
        		jdbc_password => "pwd"
        			
        		parameters => { "idfile" => "file_id" }
        		statement => "
        			SELECT FILE_PROCESS_ID, LINE_ID
        			FROM TABLE_B
        			WHERE FILE_ID = :idfile"
                
        		target => "B"
        	}
        }
        output {
        	file {
        		codec => "json_lines"
        		path => "fileintegration.json"  
        	}
        }

Any ideas?

2

How static is table B and C?

jdbc_static allows you to pull B and C into a local DB (Apache Derby) then you can do a local join of B -> C type lookup.

3

Hi,

Table B and C are not static at all. Both tables are very intensive with new data, specially C (errors).
The use case is processing files (ETL) and register the file, its processes (lines) and errors ( encountered in line).

Cheers,

4

No one have any idea about this?

5

Before we look at the possibilities, is there likely to be a timing problem? Are the child records written to B and C before the parent write to A, or maybe all are in a transaction?

6

No, there is no timing problem.
The data in C exists only if there is some data in B.
The data in B exists only if there is some data in A.

7

Great.

In this case I think you need two jdbc_streaming filters and the jdbc input.

The jdbc input collects new records from A, then the first jdbc_streaming filter collects a matching record from B and the second jdbc_streaming filter collects a matching record from C.

8

I did that, but how to i put the records from C inside the data of B? That's what i'm asking?
Currently i have B and C data inside the A record, not C inside B that is inside A.

9

Let me check.

10

On the second jdbc_filter use the target setting. Specify a nested field name using square bracket syntax.
e.g. "[B][C]"