# Jdbc\_static and combining tables

**URL:** <https://discuss.elastic.co/t/jdbc-static-and-combining-tables/185155>\
**Category:** Logstash\
**Created:** [June 11, 2019, 10:05am UTC](https://discuss.elastic.co/t/jdbc-static-and-combining-tables/185155 "2019-06-11T10:05:10Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![ErikWarming](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/erikwarming/32/39525_2.png) [@ErikWarming](https://discuss.elastic.co/u/ErikWarming)\
**Post date:** [June 11, 2019, 10:05am UTC](https://discuss.elastic.co/t/jdbc-static-and-combining-tables/185155/1 "2019-06-11T10:05:11Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![ErikWarming](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/erikwarming/32/39525_2.png) [@ErikWarming](https://discuss.elastic.co/u/ErikWarming)\
**Post date:** [June 12, 2019, 12:18pm UTC](https://discuss.elastic.co/t/jdbc-static-and-combining-tables/185155/2 "2019-06-12T12:18:03Z")

</div>

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_

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [July 10, 2019, 12:18pm UTC](https://discuss.elastic.co/t/jdbc-static-and-combining-tables/185155/3 "2019-07-10T12:18:09Z")

</div>

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