# Importing MySQL in ES

**URL:** <https://discuss.elastic.co/t/importing-mysql-in-es/115126>\
**Category:** Logstash\
**Created:** [January 11, 2018, 3:50pm UTC](https://discuss.elastic.co/t/importing-mysql-in-es/115126 "2018-01-11T15:50:38Z")\
**Posts on this page:** 12\
**Page:** 1

<div class="post-metadata">

**Author:** ![seanziee](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/seanziee/32/45151_2.png) [@seanziee](https://discuss.elastic.co/u/seanziee)\
**Post date:** [January 11, 2018, 3:50pm UTC](https://discuss.elastic.co/t/importing-mysql-in-es/115126/1 "2018-01-11T15:50:38Z")

</div>

Hi, I'm trying to enrich data I have in ES with data I have in a MySQL database. I have a unique id for my ES documents that I can use to reference the data in the SQL db. My ES data has just a uniqueid and time, and the sql database has that same unique id for that event but then about 15 different fields that I'd like to peg onto the ES document.

Currently, I'm pulling the MySql db into my ES instance and then using the elasticsearch filter to peg on those fields. I'm using the jdbc input.

I'd like to know is the jdbc\_streaming filter supposed to be used for this use case? I don't understand the documentation fully but if I wanted to attach 15 fields from the db to my ES document, how would it look? I'm having a hard time understanding/distinguishing the ":code", "parameters" and "target" in the example in the documentation. If I have uniqueID in my ES documents, is that the :code that references the SQL db (and thus I type :uniqueID)? If so, then if my fields in the mysql were location, ip, and size, would that go into the parameters or target or both? How does that look? I'm sorry but I'm new to this!!

```
  jdbc_streaming {
    jdbc_driver_library => "/path/to/mysql-connector-java-5.1.34-bin.jar"
    jdbc_driver_class => "com.mysql.jdbc.Driver"
    jdbc_connection_string => ""jdbc:mysql://localhost:3306/mydatabase"
    jdbc_user => "me"
    jdbc_password => "secret"
    statement => "select * from WORLD.COUNTRY WHERE Code = :code"
    parameters => { "code" => "country_code"}
    target => "country_details"
  }
}

```

Thanks!!

---

<div class="post-metadata">

**Author:** ![seanziee](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/seanziee/32/45151_2.png) [@seanziee](https://discuss.elastic.co/u/seanziee)\
**Post date:** [January 15, 2018, 6:46am UTC](https://discuss.elastic.co/t/importing-mysql-in-es/115126/2 "2018-01-15T06:46:51Z")

</div>

Please help!

---

<div class="post-metadata">

**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\
**Post date:** [January 15, 2018, 7:14am UTC](https://discuss.elastic.co/t/importing-mysql-in-es/115126/3 "2018-01-15T07:14:02Z")

</div>

This;

> [@seanziee](#):
>
> statement =\> "select \* from WORLD.COUNTRY WHERE Code = :code"  
> parameters =\> { "code" =\> "country\_code"}

Basically equates to;

```auto
select * from WORLD.COUNTRY WHERE Code = country_code

```

Because it takes the assigned value and puts it in place of the variable.

I'm not really sure about `target` though sorry.

---

<div class="post-metadata">

**Author:** ![seanziee](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/seanziee/32/45151_2.png) [@seanziee](https://discuss.elastic.co/u/seanziee)\
**Post date:** [January 15, 2018, 7:16am UTC](https://discuss.elastic.co/t/importing-mysql-in-es/115126/4 "2018-01-15T07:16:04Z")

</div>

@warkolm Do you think the use-case for the jdbc\_streaming is what I'm looking to do? Or what is the purpose/general use-case of this filter?

---

<div class="post-metadata">

**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\
**Post date:** [January 15, 2018, 7:21am UTC](https://discuss.elastic.co/t/importing-mysql-in-es/115126/5 "2018-01-15T07:21:44Z")

</div>

Oh now I get target (it took a minute for my brain to work).  
This is a filter plugin, so it expects to be fed data from an input. If there are fields from the database lookup that match the incoming fields, it'll overwrite them by default. Otherwise you can specify to write the lookups values to other fields.

---

<div class="post-metadata">

**Author:** ![seanziee](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/seanziee/32/45151_2.png) [@seanziee](https://discuss.elastic.co/u/seanziee)\
**Post date:** [January 15, 2018, 10:05am UTC](https://discuss.elastic.co/t/importing-mysql-in-es/115126/6 "2018-01-15T10:05:47Z")

</div>

Ah okay, so if I wanted to add 15 fields, I'd probably have to do this filter 15 times (assuming my documents don't have the matching fields). I looked into the git repo and it seems like what I'm asking is coming down the pipeline with a PR 2 days ago. I'll be waiting for that

> <https://github.com/elastic/logstash/issues/6502>

---

<div class="post-metadata">

**Author:** ![seanziee](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/seanziee/32/45151_2.png) [@seanziee](https://discuss.elastic.co/u/seanziee)\
**Post date:** [January 15, 2018, 10:06am UTC](https://discuss.elastic.co/t/importing-mysql-in-es/115126/7 "2018-01-15T10:06:00Z")

</div>

Thanks though for the insight!!

---

<div class="post-metadata">

**Author:** ![guyboertje](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/guyboertje/32/31592_2.png) [@guyboertje](https://discuss.elastic.co/u/guyboertje)\
**Post date:** [January 15, 2018, 11:03am UTC](https://discuss.elastic.co/t/importing-mysql-in-es/115126/8 "2018-01-15T11:03:12Z")

</div>

@seanziee

You can use either enhancement plugin `jdbc_streaming` or `jdbc_static` (available to install as a plugin now and will be bundled with LS 6.2.0).

`jdbc_streaming` is designed for the case where your sql db has new data **inserted often** or where data is being **updated quite frequently** - local (in the filter) caching is short lived. For example a Product Catalog or a Warehouse product location mapping. Also, the db call (and caching) does not happen until an event is actually processed.

`jdbc_static` is designed for the case where you have several Reference data tables that are not updated frequently - local caching can be much longer lived. For example: hardware inventory, application inventory. The loader stage is executed during LS startup and so the local lookup database is "ready" for the first event when it arrives.

_ **Why use the JDBC input alongside the JDBC Streaming or Static filters?** _

Sometimes, putting a JOIN in the JDBC input `statement` is not practical because it either slows down the query significantly or yields multiple events.  
Using JDBC Streaming or Static in this case allows the original select statement to be simpler and run as fast as possible while the local lookup occurs at full speed too (allowing for the initial cache population delay of course).

---

<div class="post-metadata">

**Author:** ![guyboertje](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/guyboertje/32/31592_2.png) [@guyboertje](https://discuss.elastic.co/u/guyboertje)\
**Post date:** [January 15, 2018, 11:39am UTC](https://discuss.elastic.co/t/importing-mysql-in-es/115126/9 "2018-01-15T11:39:01Z")

</div>

@seanziee

Regarding understanding the role that the parameters setting plays, here is the updated `parameters` docs entry from the `jdbc_static` docs.

> A key/value Hash or dictionary. The key (LHS) is the text that is substituted for in the SQL statement SELECT \* FROM sensors WHERE reference = :p1. The value (RHS) is the **field name** in your event. The plugin reads the value from this field out of the event and substitutes that value into the statement, e.g. parameters ⇒ { "p1" ⇒ "[ref]" }. Quoting is automatic - you do not need to put quotes in the statement. Only use the field interpolation syntax on the RHS if you need to add a prefix/suffix or join two event field values together to build the substitution value. For example, imagine an IOT message that has an id and a location and you have a table of sensors that have a column of id-loc\_id, in this case your parameter hash would look like this: parameters ⇒ { "p1" ⇒ "%{[id]}-%{[loc\_id]}" }

I hope this explains it well enough.

---

<div class="post-metadata">

**Author:** ![seanziee](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/seanziee/32/45151_2.png) [@seanziee](https://discuss.elastic.co/u/seanziee)\
**Post date:** [January 15, 2018, 3:23pm UTC](https://discuss.elastic.co/t/importing-mysql-in-es/115126/10 "2018-01-15T15:23:31Z")

</div>

> [@guyboertje](#):
>
> jdbc\_static

This is exactly what I needed to know. Thanks so much for your detailed response 🙂

---

<div class="post-metadata">

**Author:** ![guyboertje](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/guyboertje/32/31592_2.png) [@guyboertje](https://discuss.elastic.co/u/guyboertje)\
**Post date:** [January 15, 2018, 3:32pm UTC](https://discuss.elastic.co/t/importing-mysql-in-es/115126/11 "2018-01-15T15:32:54Z")

</div>

BTW, the parameters setting works the same way in both `jdbc_streaming` and `jdbc_static`.

---

<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:** [February 12, 2018, 3:32pm UTC](https://discuss.elastic.co/t/importing-mysql-in-es/115126/12 "2018-02-12T15:32:57Z")

</div>

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