Jdbc_streaming with nested elements


(Nuno Ferreira) #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?


(Guy Boertje) #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.


(Nuno Ferreira) #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,


(Nuno Ferreira) #4

No one have any idea about this?