JDBC postgres multiple statement syntax

[henriot-gricad (Oliver Henriot)]

Hi all,

How do you specify multiple statements for JDBC postgres queries in logstash?

As per the doc it's possible : _configuring_multiple_sql_statements but there is no detail as to how to achieve this.

I have one DB with one user with which I'd like to run multiple statements to input into a single elasticsearch index. Running a single postgresql select query does not work, it's impractical. As it seems possible to do this elegantly with elastic I'd like to do it that way. I have not found any further info through search engines though.

I presume I would have to have a configuration file starting with something like this:

input {
  jdbc {
    # DB connexion
    jdbc_connection_string => "jdbc:postgresql://pg.myhost.fr:5432/mydb"
    jdbc_user => "db_ro"
    jdbc_password => "secret"
    # JDBC driver
    jdbc_driver_library => "/nix/store/4m16yjh855l52vc6f1yibgxhmh7mcdmd-postgresql-jdbc-42.2.2/share/java/postgresql-jdbc.jar"
    jdbc_driver_class => "org.postgresql.Driver"
    # The request
    statement => "select count(id) as coll_count from coll"
    # schedule
    schedule => "*/5 * * * *"
    }
  jdbc {
    # DB connexion
    jdbc_connection_string => "jdbc:postgresql://pg.myhost.fr:5432/mydb"
    jdbc_user => "db_ro"
    jdbc_password => "secret"
    # JDBC driver
    jdbc_driver_library => "/nix/store/4m16yjh855l52vc6f1yibgxhmh7mcdmd-postgresql-jdbc-42.2.2/share/java/postgresql-jdbc.jar"
    jdbc_driver_class => "org.postgresql.Driver"
    # The request
    statement => "select count(id) as obj_count from obj"
    # schedule
    schedule => "*/5 * * * *"
    }
  jdbc {
    # DB connexion
    jdbc_connection_string => "jdbc:postgresql://pg.myhost.fr:5432/mydb"
    jdbc_user => "db_ro"
    jdbc_password => "secret"
    # JDBC driver
    jdbc_driver_library => "/nix/store/4m16yjh855l52vc6f1yibgxhmh7mcdmd-postgresql-jdbc-42.2.2/share/java/postgresql-jdbc.jar"
    jdbc_driver_class => "org.postgresql.Driver"
    # The request
    statement => "select count(*) from (select distinct name from coll) as temp"
    # schedule
    schedule => "*/5 * * * *"
    }
  }

but I see no way to differentiate them or identify them in the filter and output sections.

Is there a manpage for this? Thanks.

Cheers,

[Badger]

You are right that you need a separate input for each statement. You could use one of the common options like tags to distinguish them.

[henriot-gricad (Oliver Henriot)]

Hi,

Thanks for your reply Badger.

As a side note, even without using tags, the filter section is clever enough to function just using the unambiguous postgresql result column name as a tag to define a filter.

My functional setup looks like this if it can be of any help to anyone else:

# Logstash pipeline
input {
  jdbc {
    # DB connexion
    jdbc_connection_string => "jdbc:postgresql://myhost.fr:5432/icat"
    jdbc_user => "db_ro"
    jdbc_password => "secret"
    # JDBC driver
    jdbc_driver_library => "/nix/store/4m16yjh855l52vc6f1yibgxhmh7mcdmd-postgresql-jdbc-42.2.2/share/java/postgresql-jdbc.jar"
    jdbc_driver_class => "org.postgresql.Driver"
    # Request
    statement => "select count(id) as users_count from user"
    # Schedule
    schedule => "*/10 * * * *"
  }
  jdbc {
    # DB connexion
    jdbc_connection_string => "jdbc:postgresql://myhost.fr:5432/icat"
    jdbc_user => "db_ro"
    jdbc_password => "secret"
    # JDBC driver
    jdbc_driver_library => "/nix/store/4m16yjh855l52vc6f1yibgxhmh7mcdmd-postgresql-jdbc-42.2.2/share/java/postgresql-jdbc.jar"
    jdbc_driver_class => "org.postgresql.Driver"
    # Request
    statement => "select count(id) as objects_count from data"
    # Schedule
    schedule => "*/10 * * * *"
  }
  jdbc {
    # DB connexion
    jdbc_connection_string => "jdbc:postgresql://myhost.fr:5432/icat"
    jdbc_user => "db_ro"
    jdbc_password => "secret"
    # JDBC driver
    jdbc_driver_library => "/nix/store/4m16yjh855l52vc6f1yibgxhmh7mcdmd-postgresql-jdbc-42.2.2/share/java/postgresql-jdbc.jar"
    jdbc_driver_class => "org.postgresql.Driver"
    # Request
    statement => "select count(*) as owner_count from (select distinct owner_name from coll) as temp"
    # Schedule
    schedule => "*/10 * * * *"
  }
  jdbc {
    # DB connexion
    jdbc_connection_string => "jdbc:postgresql://myhost.fr:5432/icat"
    jdbc_user => "db_ro"
    jdbc_password => "secret"
    # JDBC driver
    jdbc_driver_library => "/nix/store/4m16yjh855l52vc6f1yibgxhmh7mcdmd-postgresql-jdbc-42.2.2/share/java/postgresql-jdbc.jar"
    jdbc_driver_class => "org.postgresql.Driver"
    # Request
    statement => "select sum(size) as used_space from data"
    # Schedule
    schedule => "*/10 * * * *"
  }
}

filter {
  mutate {
    convert => {
      "used_space" => "integer"
    }
  }
}

output {
  elasticsearch {
    index => "cloud_storage"
  }
}

Cheers,