JDBC input multiple tables with table name as a parameter

Elastic Stack Logstash
1

Hello everyone!

I am working currently with a Postgres database and trying to convert it to a json file for Elasticsearch.
I found out that Logstash is perfect for doing this with the JDBC input plugin.
I have several tables in my DB and I wanted to know how to process them all at the same time.

To be more specfic, I have my .conf file like this:

input {
    jdbc {
        # Postgres jdbc connection string to our database, db
        jdbc_connection_string => "jdbc:postgresql://localhost:5432/db"
        # The user we wish to execute our statement as
        jdbc_user => "postgres"
        #password
        jdbc_password => "postgres"
        # The path to our downloaded jdbc driver
        jdbc_driver_library => "blabla.jar"
        # The name of the driver class for Postgresql
        jdbc_driver_class => "org.postgresql.Driver"
        # our query
        statement => "SELECT * FROM some_table LIMIT 10;"
    }
}

but the thing is that I have 40 tables. Obviously, I am not going to have 40 conf files for every table of my database.
What would be a smart way to do this? Could I pass the table name as a parameter in the statement?

I was thinking of using this statement:

SELECT TABLE_NAME
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
  AND table_schema NOT IN ('pg_catalog','information_schema')

to get all the table names of my database and use them to create other statements of the form

SELECT * FROM some_table LIMIT 10

where 'some_table' would be a parameter.

Any idea on how I could achieve this with logstash?

Thank you in advance for your attention!

2

Why not use a script to generate the necessary configuration blocks?

3

Before you transfer table by table into Elasticsearch, think about how you want to be able to query the data. As Elasticsearch does not support joins, it may make sense to denormalise or restructure some of the data before sending it to Elasticsearch.

4

Yes, that could be an idea, but I was wondering if I could use some features if the plugin to do this.

5

That depends on your data and data model, so I don't think there is any way to automate that.

6

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