Unable to escape special character ""

ravi-shanker · May 20, 2021, 6:44am

I am trying to index my data into elasticsearch cluster through logstash. Currently my data is in aws postgres and i have to move data from postgres to elasticsearch.

Below is my logstash config file:

input {
jdbc {
jdbc_connection_string => "jdbc:postgresql://dtacsqldb-ccms-postgresql-dtacqual-1a.cpzfa7z6lrue.us-east-1.rds.amazonaws.com/dtacqual"
jdbc_user => "dtacuser"
jdbc_password => "xyz"
jdbc_driver_library => "/solr-index/Logstash/logstash-7.12.1/logstash-core/lib/jars/postgresql-jdbc.jar"
jdbc_driver_class => "org.postgresql.Driver"
schedule => "* * * * *" # cronjob schedule format (see "Helpful Links")
#jdbc_paging_enabled => "true"
#jdbc_page_size => "300"
statement => "SELECT DISTINCT PYID AS "ID" FROM U90CCMWT.Marketing LIMIT 5"
}
}
output {
# used to output the values in the terminal (DEBUGGING)
# once everything is working, comment out this line
stdout { codec => "json" }
# used to output the values into elasticsearch
elasticsearch {
hosts => ["http://ip_of_node:9200"]
index => "vikings_es"
#document_id => "{101}"
#doc_as_upsert => true # upserts documents (e.g. if the document does not exist, creates a new record)
}
}

i am getting below error while running this logstash file for indexing data

Using bundled JDK: /solr-index/Logstash/logstash-7.12.1/jdk
OpenJDK 64-Bit Server VM warning: Option UseConcMarkSweepGC was deprecated in version 9.0 and will likely be removed in a future release.
Sending Logstash logs to /solr-index/Logstash/logstash-7.12.1/logs which is now configured via log4j2.properties
[2021-05-20T06:27:41,874][INFO ][logstash.runner ] Log4j configuration path used is: /solr-index/Logstash/logstash-7.12.1/config/log4j2.properties
[2021-05-20T06:27:41,886][INFO ][logstash.runner ] Starting Logstash {"logstash.version"=>"7.12.1", "jruby.version"=>"jruby 9.2.13.0 (2.5.7) 2020-08-03 9a89c94bcc OpenJDK 64-Bit Server VM 11.0.10+9 on 11.0.10+9 +indy +jit [linux-x86_64]"}
[2021-05-20T06:27:42,300][WARN ][logstash.config.source.multilocal] Ignoring the 'pipelines.yml' file because modules or command line options are specified
[2021-05-20T06:27:42,913][INFO ][logstash.agent ] Successfully started Logstash API endpoint {:port=>9600}
[2021-05-20T06:27:43,152][ERROR][logstash.agent ] Failed to execute action {:action=>LogStash::PipelineAction::Create/pipeline_id:test, :exception=>"LogStash::ConfigurationError", :message=>"Expected one of [ \t\r\n], "#", "{", "}" at line 11, column 41 (byte 582) after input {\n jdbc {\n jdbc_connection_string => "jdbc:postgresql://dtacsqldb-ccms-postgresql-dtacqual-1a.cpzfa7z6lrue.us-east-1.rds.amazonaws.com/dtacqual"\n jdbc_user => "dtacuser"\n jdbc_password => "xyz"\n jdbc_driver_library => "/solr-index/Logstash/logstash-7.12.1/logstash-core/lib/jars/postgresql-jdbc.jar"\n jdbc_driver_class => "org.postgresql.Driver"\n schedule => "* * * * *" # cronjob schedule format (see "Helpful Links")\n\t#jdbc_paging_enabled => "true"\n\t#jdbc_page_size => "300"\n\tstatement => "SELECT DISTINCT PYID AS "", :backtrace=>["/solr-index/Logstash/logstash-7.12.1/logstash-core/lib/logstash/compiler.rb:32:in compile_imperative'", "org/logstash/execution/AbstractPipelineExt.java:184:in initialize'", "org/logstash/execution/JavaBasePipelineExt.java:69:in initialize'", "/solr-index/Logstash/logstash-7.12.1/logstash-core/lib/logstash/java_pipeline.rb:47:in initialize'", "/solr-index/Logstash/logstash-7.12.1/logstash-core/lib/logstash/pipeline_action/create.rb:52:in execute'", "/solr-index/Logstash/logstash-7.12.1/logstash-core/lib/logstash/agent.rb:389:in block in converge_state'"]}
[2021-05-20T06:27:43,257][INFO ][logstash.runner ] Logstash shut down.
[2021-05-20T06:27:43,268][FATAL][org.logstash.Logstash ] Logstash stopped processing because of an error: (SystemExit) exit
org.jruby.exceptions.SystemExit: (SystemExit) exit
at org.jruby.RubyKernel.exit(org/jruby/RubyKernel.java:747) ~[jruby-complete-9.2.13.0.jar:?]
at org.jruby.RubyKernel.exit(org/jruby/RubyKernel.java:710) ~[jruby-complete-9.2.13.0.jar:?]
at solr_minus_index.Logstash.logstash_minus_7_dot_12_dot_1.lib.bootstrap.environment.(/solr-index/Logstash/logstash-7.12.1/lib/bootstrap/environment.rb:89) ~[?:?]

I tried to escape the special character by trying the below queries but none of them worked

"SELECT DISTINCT PYID AS" +""IDD""+ "FROM U90CCMWT.Marketing LIMIT 5"
"SELECT DISTINCT PYID AS ""IDD"" FROM U90CCMWT.Marketing LIMIT 5"
"SELECT DISTINCT PYID AS \""IDD\"" FROM U90CCMWT.Marketing LIMIT 5"
"SELECT DISTINCT PYID AS \"IDD\" FROM U90CCMWT.Marketing LIMIT 5
"SELECT DISTINCT PYID AS IDD FROM U90CCMWT.Marketing LIMIT 5
"SELECT DISTINCT PYID AS "IDD" FROM U90CCMWT.Marketing LIMIT 5
"SELECT DISTINCT PYID AS \t "IDD" \t FROM U90CCMWT.Marketing LIMIT 5

"SELECT DISTINCT PYID AS "ID#" FROM U90CCMWT.Marketing LIMIT 5"
"SELECT DISTINCT PYID AS "ID" FROM U90CCMWT.Marketing LIMIT 5"
"SELECT DISTINCT PYID AS UPPER(ID)" FROM U90CCMWT.Marketing LIMIT 5"
'SELECT DISTINCT PYID AS "ID" FROM U90CCMWT.Marketing LIMIT 5'
"SELECT DISTINCT PYID AS "ID=>" FROM U90CCMWT.Marketing LIMIT 5"
"SELECT DISTINCT 'PYID' AS ID FROM U90CCMWT.Marketing LIMIT 5"
"SELECT DISTINCT PYID AS ''ID'' FROM U90CCMWT.Marketing LIMIT 5"

Please help me to resolve this issue.Preformatted text

dadoonet · May 20, 2021, 7:49am

Please format your code, logs or configuration files using </> icon as explained in this guide and not the citation button. It will make your post more readable.

Or use markdown style like:

```
CODE
```

This is the icon to use if you are not using markdown format:

There's a live preview panel for exactly this reasons.

Lots of people read these forums, and many of them will simply skip over a post that is difficult to read, because it's just too large an investment of their time to try and follow a wall of badly formatted text.
If your goal is to get an answer to your questions, it's in your interest to make it as easy to read and understand as possible.
Please update your post.

Badger · May 20, 2021, 3:31pm

Use single quotes around a string containing double quotes.

statement => 'SELECT DISTINCT PYID AS "ID" FROM U90CCMWT.Marketing LIMIT 5'

ravi-shanker · May 20, 2021, 4:01pm

Hello Badger,

statement => 'SELECT DISTINCT PYID AS "ID" FROM U90CCMWT.Marketing LIMIT 5'

this query is also throwing the same error.

"LogStash::ConfigurationError", :message=>"Expected one of "\\'", any character, "'" at line 26, column 1 (byte 1082) after input {\n jdbc {\n jdbc_connection_string => "jdbc:postgresql://dtacsqldb-ccms-postgresql-dtacqual-1a.cpzfa7z6lrue.us-east-1.rds.amazonaws.com/dtacqual"\n jdbc_user => "dtacuser"\n jdbc_password => "xyzPreformatted text"\n jdbc_driver_library => "/solr-index/Logstash/logstash-7.12.1/logstash-core/lib/jars/postgresql-jdbc.jar"\n jdbc_driver_class => "org.postgresql.Driver"\n schedule => "* * * * *" # cronjob schedule format (see "Helpful Links")\n\t#jdbc_paging_enabled => "true"\n\t#jdbc_page_size => "300"\n\tstatement => 'SELECT DISTINCT PYID AS "IDD" FROM U90CCMWT.Marketing limit 10\n }\n}\noutput {\n # used to output the values in the terminal (DEBUGGING)\n # once everything is working, comment out this line\n stdout { codec => "json" }\n # used to output the values into elasticsearch\n elasticsearch {\n hosts => ["http://ip:9200"]\n index => "vikings1"\n #document_id => "{101}"\n #doc_as_upsert => true # upserts documents (e.g. if the document does not exist, creates a new record)\n }\n}\n", :backtrace=>["/solr-index/Logstash/logstash-7.12.1/logstash-core/lib/logstash/compiler.rb:32:in compile_imperative'", "org/logstash/execution/AbstractPipelineExt.java:184:in initialize'", "org/logstash/execution/JavaBasePipelineExt.java:69:in initialize'", "/solr-index/Logstash/logstash-7.12.1/logstash-core/lib/logstash/java_pipeline.rb:47:in initialize'", "/solr-index/Logstash/logstash-7.12.1/logstash-core/lib/logstash/pipeline_action/create.rb:52:in execute'", "/solr-index/Logstash/logstash-7.12.1/logstash-core/lib/logstash/agent.rb:389:in block in converge_state'"]}
[2021-05-20T15:56:12,998][INFO ][logstash.runner ] Logstash shut down.
[2021-05-20T15:56:13,006][FATAL][org.logstash.Logstash ] Logstash stopped processing because of an error: (SystemExit) exit
org.jruby.exceptions.SystemExit: (SystemExit) exit
at org.jruby.RubyKernel.exit(org/jruby/RubyKernel.java:747) ~[jruby-complete-9.2.13.0.jar:?]
at org.jruby.RubyKernel.exit(org/jruby/RubyKernel.java:710) ~[jruby-complete-9.2.13.0.jar:?]
at solr_minus_index.Logstash.logstash_minus_7_dot_12_dot_1.lib.bootstrap.environment.(/solr-index/Logstash/logstash-7.12.1/lib/bootstrap/environment.rb:89) ~[?:?]Preformatted text

Badger · May 20, 2021, 5:00pm

No, the error has changed. It is now complaining about what comes after the output section. That would suggest you have an unclosed quote in your configuration.

ravi-shanker · May 21, 2021, 6:02am

input {
    jdbc {
        jdbc_connection_string => "jdbc:postgresql://dtacsqldb-ccms-postgresql-dtacqual-1a.cpzfa7z6lrue.us-east-1.rds.amazonaws.com/dtacqual"
        jdbc_user => "dtacuser"
        jdbc_password => "xyz"
        jdbc_driver_library => "/solr-index/Logstash/logstash-7.12.1/logstash-core/lib/jars/postgresql-jdbc.jar"
        jdbc_driver_class => "org.postgresql.Driver"
        schedule => "* * * * *" # cronjob schedule format (see "Helpful Links")
        #jdbc_paging_enabled => "true"
        #jdbc_page_size => "300"
        statement => 'SELECT DISTINCT PYID AS "IDD"  FROM U90CCMWT.Marketing limit 10
    }
}
output {
    # used to output the values in the terminal (DEBUGGING)
    # once everything is working, comment out this line
    stdout { codec => "json" }
    # used to output the values into elasticsearch
    elasticsearch {
        hosts => ["http://ip:9200"]
        index => "vikings1"
        #document_id => "{101}"
        #doc_as_upsert => true # upserts documents (e.g. if the document does not exist, creates a new record)
    }
}


those are the content of my config file. i don't find any missing quotes in output section. Could you please have a look from your end.

Badger · May 21, 2021, 2:07pm

Indeed, there is no closing ' on the statement option.

ravi-shanker · May 21, 2021, 2:48pm

Yes I corrected that and query got executed but IDD got printed in small letter.....bdw thanks atleast was able to execute the query

Badger · May 21, 2021, 2:51pm

Take a look at the lowercase_column_names option.

ravi-shanker · May 22, 2021, 12:03am

marking "lowercase_column_names " as false worked for me.

Thanks a lot badger for your response on this post.

system · June 19, 2021, 12:04am

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

Topic		Replies	Views
Not able to escape special characters Elasticsearch	2	2447	March 20, 2018
Escaping special characters is not working Elasticsearch	5	2126	July 6, 2017
Logstash escaping characters, want to disable Logstash	3	538	January 10, 2023
Elasticsearch Logstash Error Logstash	2	570	June 13, 2017
How to escape character in logstash Logstash	3	7962	April 4, 2019

Unable to escape special character ""

Related topics