Not able to use postgres query with double quotes in jdbc_streaming plugin

Elastic Stack Logstash
#1

Hi

I am using jdbc_streaming plugin. I have a complex query and also my postgres required double quotes (") with each table/column name.

I am also using Case When in my query, which requires apostrophe(').

Plugin does not support statement file path and if I use escape character() it give me error that query exception.

jdbc_streaming {
        jdbc_driver_library => "${LOGSTASH_JDBC_DRIVER_JAR_LOCATION}"
        jdbc_driver_class => "org.postgresql.Driver"
        jdbc_connection_string => "${LOGSTASH_JDBC_URL}"
        jdbc_user => "${LOGSTASH_JDBC_USERNAME}"
        jdbc_password => "${LOGSTASH_JDBC_PASSWORD}"
        statement => '  select
                        "ClosedDealId" as "closedDealId"
                       , CASE WHEN "IsCpo" = true then \'Certified Pre-owned\'
				when ("IsCpo" = false or "IsCpo" = null) and "IsNew" = true then \'New\'
				ELSE\ 'N/A\' END AS "vehicleType"
                        from dbo."ReplacementDeal"'
        target => "replacementDeal"
    }

Can anyone help me?

#2

You are not escaping that single quote in there (note the space):

ELSE\ 'N/A\' END AS "vehicleType"

it should be:

ELSE \'N/A\' END AS "vehicleType"
#3

Oh sorry, this is just here while doing formatting it happened but issue still exist.

jdbc_streaming {
        jdbc_driver_library => "${LOGSTASH_JDBC_DRIVER_JAR_LOCATION}"
        jdbc_driver_class => "org.postgresql.Driver"
        jdbc_connection_string => "${LOGSTASH_JDBC_URL}"
        jdbc_user => "${LOGSTASH_JDBC_USERNAME}"
        jdbc_password => "${LOGSTASH_JDBC_PASSWORD}"
        statement => '  select
                        "ClosedDealId" as "closedDealId"
                       , CASE WHEN "IsCpo" = true then \'Certified Pre-owned\'
				when ("IsCpo" = false or "IsCpo" = null) and "IsNew" = true then \'New\'
				ELSE \'N/A\' END AS "vehicleType"
                        from dbo."ReplacementDeal"'
        target => "replacementDeal"
    }
#4

Can you share what error message you are seeing in Logstash?

#5

I figured this out. Had to use below configuration in pipeline

config.support_escapes: true
1 Like