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!