# JDBC\_Static Filter **URL:** \ **Category:** Logstash\ **Created:** [March 3, 2021, 9:24pm UTC](https://discuss.elastic.co/t/jdbc-static-filter/266162 "2021-03-03T21:24:54Z")\ **Posts on this page:** 13\ **Page:** 1
**Author:** ![wwalker](https://avatars.discourse-cdn.com/v4/letter/w/43a26b/32.png) [@wwalker](https://discuss.elastic.co/u/wwalker)\ **Post date:** [March 3, 2021, 9:24pm UTC](https://discuss.elastic.co/t/jdbc-static-filter/266162/1 "2021-03-03T21:24:54Z")
I am pulling data from a database that has fields populated with numeric values. I need to perform subsequent lookups to additional tables to convert the numeric value to a named value. Since I'll be doing this frequently, I wanted to minimize the number of SQL calls which it looks like the JDBC static filter would allow me to do? I've configured the loaders fine but I have a couple questions regarding the local\_db\_objects and local lookups. 1. I don't understand the purpose of the `index_columns` setting, is this like the table's primary key? 2. Do local queries have to be specified in the same call of the filter or can they be specified later. For instance, does this work? ```auto filter { jdbc_static { loaders => [ { query => "SELECT id,value FROM Contact" local_table => "contact_type" } ] } local_db_objects => [ { name => "contact_type" index_columns => [id] columns => [ ["id", "SMALLINT"], ["value", "varchar(20)"] ] } ] } if [contact] { jdbc_static { local_lookups => [ { query => "select name from contact_type WHERE value = :contact" target => "contact" } ] } } } ``` ---
**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\ **Post date:** [March 3, 2021, 11:21pm UTC](https://discuss.elastic.co/t/jdbc-static-filter/266162/2 "2021-03-03T23:21:42Z")
1. When a local\_db\_object is loaded, an index is [built](https://github.com/logstash-plugins/logstash-filter-jdbc_static/blob/e1707ec2ab084ad1f370ea03993ccb689d23db49/lib/logstash/filters/jdbc/db_object.rb#L19) on each of the columns in the index\_columns array. You do not have to have a primary (unique) key -- the lookup can return an array of hashes. 1. I think all the data loaded by the filter has instance scope, so data loaded in one jdbc\_static\_filter would not be visible in another. ---
**Author:** ![wwalker](https://avatars.discourse-cdn.com/v4/letter/w/43a26b/32.png) [@wwalker](https://discuss.elastic.co/u/wwalker)\ **Post date:** [March 4, 2021, 2:38am UTC](https://discuss.elastic.co/t/jdbc-static-filter/266162/3 "2021-03-04T02:38:32Z")
1. I'm not sure what.....most of what you said on this point means, lol. Can you dumb it to say....the level of a moderately smart chimp? 2. Actually, I was thinking about implementation wrong, so it's fine really to just have a single scope. Though the documentation says something about being multipipeline aware or something, I haven't full read into that though. ---
**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\ **Post date:** [March 4, 2021, 3:23am UTC](https://discuss.elastic.co/t/jdbc-static-filter/266162/4 "2021-03-04T03:23:38Z")
> [@wwalker](#): > > I'm not sure what.....most of what you said on this point means, lol. Can you dumb it to say....the level of a moderately smart chimp? When you use the local\_db\_object the filter executes the query defined by the loader option (either as a one-off at startup or possibly on a schedule). The result set from the query is loaded into an in-memory [Apache Derby](https://db.apache.org/derby/) database. That avoids having to contact the source database for every event. If the source DB was in another data centre that could result in a delay of tens of milliseconds for each event, which would be a big performance problem. The index\_columns is used to tell the Derby database which columns should be indexed. Having an index on a column greatly speeds up lookups on that column (but not WHERE clauses that use any other columns) If you take a look at the [description](https://www.elastic.co/guide/en/logstash/current/plugins-filters-jdbc_static.html#_description_133) of these options then there is an example of each of the options. There are two loaders. One loads ip and descr from a table to create the "servers" table in Derby. The other loads firstname, lastname, and userid into a Derby table called "users". I suspect the "order by" clauses on these queries are micro-optimizations and you should not worry about them until you have the basic functionality working. The local\_db\_objects says "servers" should be indexed by "ip", and "users" should be indexed by "userid". Note! The local\_lookups option configures two queries against the local\_db\_objects. The local-servers query does a lookup against the Derby "servers" table, queried by ip, which is the field that was indexed in Derby. Likewise, the local-users query does a lookup against the Derby "users" table, queried by userid, which again, is the field that was indexed. For your local\_lookups, you are trying to select a column called "name" from the Derby contact\_type DB, but that only has columns called "id" and "value". If you are trying to lookup a field called contact and overwrite it with the contents of the "value" column I think that would be ``` local_lookups => [ { query => "select value from contact_type WHERE id = :contact" target => "contact" } ] ``` "id" matching the indexed field. I am not sure if you need ``` parameters => {contact => "[contact]"} ``` or the filter matches names by default. Did you want `target => "name"`? I am also not sure how the filter handles overwriting a field. It is not impossible that it will turn it into an array containing both values. You would need to experiment. ---
**Author:** ![wwalker](https://avatars.discourse-cdn.com/v4/letter/w/43a26b/32.png) [@wwalker](https://discuss.elastic.co/u/wwalker)\ **Post date:** [March 4, 2021, 3:34am UTC](https://discuss.elastic.co/t/jdbc-static-filter/266162/5 "2021-03-04T03:34:21Z")
I saw once I started the pipeline that specifying an index column would improve performance. Unfortunately, I've run into another issue that I'm trying to sort. I'm using the exact same settings and statement I used in the jdbc input that works fine. ```auto loaders => [ { query => "use DB select id,value from table" local_table => "contact_type" } ``` Error generated: ```auto Exception occurred when executing loader Jdbc query count Exception occurred when executing loader Jdbc query count {:exception=>"Java::ComMicrosoftSqlserverJdbc::SQLServerException: Incorrect syntax near the keyword 'use'." ``` If I remove `use DB`, I get a different error ```auto Exception occurred when executing loader Jdbc query count {:exception=>"Java::ComMicrosoftSqlserverJdbc::SQLServerException: Invalid object name 'table'." ``` ---
**Author:** ![wwalker](https://avatars.discourse-cdn.com/v4/letter/w/43a26b/32.png) [@wwalker](https://discuss.elastic.co/u/wwalker)\ **Post date:** [March 4, 2021, 3:39am UTC](https://discuss.elastic.co/t/jdbc-static-filter/266162/6 "2021-03-04T03:39:00Z")
For the sake of completeness, here's the full jdbc\_static configuration ```auto jdbc_static { jdbc_driver_library => "d:/Logstash/data/sqljdbc_9.2/mssql-jdbc-9.2.0.jre11.jar" jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver" jdbc_connection_string => "jdbc:sqlserver://SERVER_FQDN:1433" jdbc_user => "user" jdbc_password => "password" loader_schedule => "0 */1 * * *" loaders => [ { query => "use DB select id,value from table" local_table => "contact_type" } ] local_db_objects => [ { name => "contact_type" columns => [ ["id", "SMALLINT"], ["value", "varchar(20)"] ] } local_lookups => [ { query => "SELECT value FROM contact_type WHERE id = :id" parameters => { "id" => "[contacttype]"} target => "contacttype" } } ] } ``` ---
**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\ **Post date:** [March 4, 2021, 4:59pm UTC](https://discuss.elastic.co/t/jdbc-static-filter/266162/7 "2021-03-04T16:59:10Z")
I think you have to specify the database name in the connection string. ---
**Author:** ![wwalker](https://avatars.discourse-cdn.com/v4/letter/w/43a26b/32.png) [@wwalker](https://discuss.elastic.co/u/wwalker)\ **Post date:** [March 8, 2021, 3:31pm UTC](https://discuss.elastic.co/t/jdbc-static-filter/266162/8 "2021-03-08T15:31:06Z")
Unfortunately, it doesn't like that either. ```auto LogStash::Filters::Jdbc::ConnectionJdbcException: Java::ComMicrosoftSqlserverJdbc::SQLServerException: The port number 1433/DB is not valid. ``` ---
**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\ **Post date:** [March 8, 2021, 5:09pm UTC](https://discuss.elastic.co/t/jdbc-static-filter/266162/9 "2021-03-08T17:09:23Z")
> [@wwalker](#): > > `The port number 1433/DB is not valid.` Shouldn't that be a semicolon? ``` jdbc:sqlserver://localhost:1433;databaseName=AdventureWorks ``` ---
**Author:** ![wwalker](https://avatars.discourse-cdn.com/v4/letter/w/43a26b/32.png) [@wwalker](https://discuss.elastic.co/u/wwalker)\ **Post date:** [March 8, 2021, 7:04pm UTC](https://discuss.elastic.co/t/jdbc-static-filter/266162/10 "2021-03-08T19:04:51Z")
woot! That was the issue, it's now working. One last issue with JDBC that I see. The returned value from the JDBC local query is an object, I was expecting a value. So an event with the field `contacttype` comes into Logstash with a value of `3`. The JDBC filter is putting the following in the elasticsearch output. ```auto { value=phone } ``` Again, here's my config: ```auto loaders => [ { query => "select id,value from Table" local_table => "contact_type" } ] local_db_objects => [ { name => "contact_type" index_columns => ["id"] columns => [ ["id", "int"], ["value", "varchar(20)"] ] } ] local_lookups => [ { query => "SELECT value FROM contact_type WHERE id = :id" parameters => { "id" => "[contacttype]"} target => "contacttype" default_hash => { "contacttype" => "null" } } ] } ``` ---
**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\ **Post date:** [March 8, 2021, 7:49pm UTC](https://discuss.elastic.co/t/jdbc-static-filter/266162/11 "2021-03-08T19:49:42Z")
> [@wwalker](#): > > The returned value from the JDBC local query is an object, I was expecting a value. I think that is working as expected. In the [description](https://www.elastic.co/guide/en/logstash/current/plugins-filters-jdbc_static.html#_description_133) of lookups it says "Any rows are converted to Hash objects and are stored in a target field that is an Array". If you read through the full example in that section the lookup ``` local_lookups => [ { query => "select descr as description from servers WHERE ip = :ip" parameters => {ip => "[from_ip]"} target => "server" } ] ``` results in an array of hashes ``` "server" => [ [0] { "description" => "Payroll Server" } ], ``` It pretty much has to do that since you can SELECT multiple columns and/or multiple rows. I expect nearly everyone is selecting a single row of a single column so ``` "server" => "Payroll Server" ``` would work better for them, but if the [server] field were a string on some events, an array on others, and a hash on others then elasticsearch would drop documents with a mapping exception. ---
**Author:** ![wwalker](https://avatars.discourse-cdn.com/v4/letter/w/43a26b/32.png) [@wwalker](https://discuss.elastic.co/u/wwalker)\ **Post date:** [March 8, 2021, 9:32pm UTC](https://discuss.elastic.co/t/jdbc-static-filter/266162/12 "2021-03-08T21:32:17Z")
> [@Badger](#): > > ```auto > target => "server" > > ``` Ya I saw that and it makes sense. I'm using this more like you'd use a translate filter. I can't use the translate in this case because some of the fields I am doing this lookup on may have additional values added at any time. Thanks for all the help @Badger ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [April 5, 2021, 9:33pm UTC](https://discuss.elastic.co/t/jdbc-static-filter/266162/13 "2021-04-05T21:33:06Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.