I have loded lookup table in postgresql and used jdbc_static filter to enrich the data. while executing i am getting the below error. In staging directory the data is not loaded.
Can some one please help what is the issue.
Exception when executing Jdbc query {:lookup_id=>"local-servers", :exception=>"Java::JavaSql::SQLSyntaxErrorException: Syntax error: Encountered ":" at line 1, column 57.", :backtrace=>["org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(org/apache/derby/impl/jdbc/SQLExceptionFactory)", "org.apache.derby.impl.jdbc.Util.generateCsSQLException(org/apache/derby/impl/jdbc/Util)", "org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(org/apache/derby/impl/jdbc/TransactionResourceImpl)", "org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(org/apache/derby/impl/jdbc/TransactionResourceImpl)", "org.apache.derby.impl.jdbc.EmbedConnection.handleException(org/apache/derby/impl/jdbc/EmbedConnection)", "org.apache.derby.impl.jdbc.ConnectionChild.handleException(org/apache/derby/impl/jdbc/ConnectionChild)", "org.apache.derby.impl.jdbc.EmbedStatement.execute(org/apache/derby/impl/jdbc/EmbedStatement)", "org.apache.derby.impl.jdbc.EmbedStatement.executeQuery(org/apache/derby/impl/jdbc/EmbedStatement)"]}
Mu logstash config file is
input {
file {
path => "C:/Users/605824803/Documents/data/invcs.csv"
start_position => "beginning"
sincedb_path => "C:/Users/605824803/Documents/data/null26"
}
}
filter {
csv{
separator => ","
columns => ["host_ip","destin"]
skip_empty_columns => false
}
jdbc_static {
loaders => [
{
id => "remote-users"
query => "select host_ip, host_name from host_mapping"
local_table => "users"
}
]
local_db_objects => [
{
name => "users"
index_columns => ["host_ip"]
columns => [
["host_ip", "varchar(50)"],
["host_name", "varchar(50)"]
]
}
]
local_lookups => [
{
id => "local-servers"
query => "SELECT host_name as HostName FROM users WHERE host_ip = :host_ip"
target => "user"
}
]
staging_directory => "C:/Users/605824803/Documents/data/postgresdata"
jdbc_user => "postgres"
jdbc_password => "anji"
jdbc_driver_class => "org.postgresql.Driver"
jdbc_driver_library => "C:/Users/605824803/Downloads/postgresql-42.2.12.jar"
jdbc_connection_string => "jdbc:postgresql://localhost:5432/Monitoring"
}
}
output {
elasticsearch {
hosts => "http://localhost:9200"
manage_template => false
index => "test-host6"
}
#stdout {codec => "rubydebug"}
}