CloudSQL JDBC Logstash implementation

Question

Question

I need to query CloudSQL from Logstash but can't find any example out there.

Additional Context

I ran the build command for postgres jdbc driver

mvn -P jar-with-dependencies clean package -DskipTests

And provided it as Logstash JDBC driver (tried with dependencies jar too):

input {
    jdbc {
        jdbc_driver_library => "/Users/gustavollermalylarrain/Documents/proyectos/labs/cloud-sql-jdbc-socket-factory/jdbc/postgres/target/postgres-socket-factory-1.6.4-SNAPSHOT.jar"
        jdbc_driver_class => "org.postgresql.Driver"
        jdbc_connection_string => "jdbc:postgresql:///people?cloudSqlInstance=cosmic-keep-148903:us-central1:llermaly&socketFactory=com.google.cloud.sql.postgres.SocketFactory&user=postgres&password=postgres"
        statement => "SELECT * FROM people;"
        jdbc_user => "postgres"
        jdbc_password => "postgres"
    }
}

output {
    stdout {
        codec => rubydebug {
        }
    }
}

I'm having this error:

Error: java.lang.ClassNotFoundException: org.postgresql.Driver. Are you sure you've included the correct jdbc driver in :jdbc_driver_library?

I'm missing something?

Answer 1

The steps to query Cloud SQL from Logstash are:

1. Build the jar driver:

From this repo: https://github.com/GoogleCloudPlatform/cloud-sql-jdbc-socket-factory. Clone and run mvn -P jar-with-dependencies clean package -DskipTests

2. Copy files

Copy the jar files from jdbc/postgres/target/ to logstash-core/lib/jars also download the postgres jdbc driver and copy the jar file to logstash-core/lib/jars as well

3. Configure Logstash

The configuration file will not include the jar's path because will look into the default folder logstash-core/lib/jars where you copied the jar files.

input {
    jdbc {
        jdbc_driver_class => "org.postgresql.Driver"
        jdbc_connection_string => "jdbc:postgresql:///people?cloudSqlInstance=cosmic-keep-148903:us-central1:llermaly&socketFactory=com.google.cloud.sql.postgres.SocketFactory&user=postgres&password=postgres"
        statement => "SELECT * FROM people;"
        jdbc_user => "postgres"
        jdbc_password => "postgres"
    }
}

output {
    stdout {
        codec => rubydebug {
        }
    }
}

jdbc user and password are ignored and the ones you provide in the connection string are used instead. For Postgre Cloud SQL connector you can use both Postgre users or IAM accounts.

Note: You need to run this from a Compute Engine to automatically apply the gcp credentials or manually create the env variables