Jdbc_static and combining tables

Elastic Stack Logstash
1

I am currently building experience with JDBC in logstash and I have this testing setup. I'm connecting to my own server where I get the backbone table from a SQL server ( [dbo].[Numbers] ) and the table that I want to join it ([dw].[iv_employees]).
The reason for this setup is that my final data source has very limited computation power, so I have to pull all my tables to the logstash computer and to the modeling here, before sending the result to ES.

So far I have problems with just joining the two tables together. I'm getting this error and running out of ideas

[2019-06-11T11:39:01,158][WARN ][logstash.filters.jdbc.lookup] Exception when executing Jdbc query {:lookup_id=>"local-user", :exception=>"Java::JavaSql::SQLSyntaxErrorException: Syntax error: Encountered "user" at line 2, column 30.", :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)"]}

and the output looks like this (with numbers from 1-10)

{
"number" => "8",
"tags" => [
[0] "_jdbcstaticfailure"
]
}

My config file:

input {
 jdbc {
    jdbc_driver_library => "C:/Elastic/logstash-6.4.2/plugin/sqljdbc_7.2/enu/mssql-jdbc-7.2.2.jre8.jar"
    jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
    jdbc_connection_string =>"XXXXXXXXXX"
    jdbc_user => "YYYYYYY"
    jdbc_password => "ZZZZZ"
    # parameters => { }
    schedule => "* * * * *"
    statement => "SELECT cast(number as varchar(5)) as number FROM [dbo].[Numbers] where number < 10"
  }
}



filter {
  json {source => "message"}

  jdbc_static {
    loaders => [ 
      {
        id => "remote-user"
        query => "SELECT  
                cast([emp_id] as varchar(5)) as emp_id
                ,[initialer]
                ,[full_name]
                FROM [dw].[iv_employees]"
        local_table => "user"
      }
    ]
    local_db_objects => [ 

      {
        name => "user"
        index_columns => ["emp_id"]
        columns => [
          ["emp_id", "varchar(5)"],
          ["initialer", "varchar(3)"],
          ["full_name", "varchar(20)"]
        ]
      }
    ]
    local_lookups => [ 
      {
        id => "local-user"
        query =>    "select initialer 
                    from user 
                    where emp_id = :number"  
        parameters => {number => "number"}
        target => "user_initialer"
      }

    ]
    
    add_field => { user_firstname => "%{[user_initialer][0][initialer]}" } 
    
    staging_directory => "C:/Elastic/logstash-6.4.2/bin/tmp/import_data"
    loader_schedule => "*/30 * * * *"
    
    jdbc_driver_library => "C:/Elastic/logstash-6.4.2/plugin/sqljdbc_7.2/enu/mssql-jdbc-7.2.2.jre8.jar"
    jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
		jdbc_connection_string =>"XXXXXXXXXX"
		jdbc_user => "YYYYYYY"
		jdbc_password => "ZZZZZ"
    }

mutate { remove_field => ["@timestamp"]    }
mutate { remove_field => ["@version"]    }
}


#Output to console
output {    stdout { codec => rubydebug }    }

The table [dbo].[Numbers] just contain one column with the numbers from 1...10000, total 10000 rows
The table [dw].[iv_employees] contain tree columns with emp_id as numbers 1...8, initialer as inititials as 3 letters, and full_name as the full name in text. Total 8 rows

2

Found one error in my config file,

name => "user"

User is apparently a reserved word that has special meaning for other parts of logstash. When I used a different word, the logstash was a bit more accommodating.

But I still can't get parameters to work with emp_id and number

3

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