WIXLIB

ObjectRocket: MySQL to ElasticsearchSetting up the Elasticsearch OutputNow that we know the input side of our ilter is working correctly, we need to conigure the Elasticsearch end. See the coniguration ilefor the full setup below.Everything here is, once again, pretty straightforward:input {jdbc {jdbc driver library �jdbc driver class “com.mysql.jdbc.Driver”jdbc connection string ��jdbc user “mymysqluser”jdbc password “notreallyapassword”statement “SELECT * FROM employees LIMIT 10”lowercase column names true}}# The ilter part of this ile is commented out to indicate that it is# optional.# ilter {## }output {elasticsearch {id “esoutput”document id “%{emp no}”hosts [“some-es-host”, “another-es-host”]user “myelasticsearchuser”password “myelasticsearchpassword”ssl “true”index “mysqlemployees”}} Host(s): This is a host or list of elasticsearch hosts. If you’re using ObjectRocket for Elasticsearch, you can just cut and paste thisblock from the connection snippets section of the Instance Details screen. User/password: In the JDBC block, these are your source database credentials. In the Elasticsearch block, these are yourElasticsearch username and password. Index: If you don’t want to use the default of logstash-%{ YYYY.MM.dd}, you can specify an index name here. Document id: In order to make employees updateable (and some other actions we’ll describe later), use theemp no (employee number) ield as the Elasticsearch document ID.7 2018 Rackspace, US Inc.All trademarks, service marks, images, products and brands remain the sole property of their respective holders.

ObjectRocket: MySQL to ElasticsearchNow it’s time to rerun Logstash and see what shows up inConiguration Wrap-upElasticsearch. If everything worked, you should see your 10At this point, you should have a repeatable setup for grabbingdocs from the test above replicated in Elasticsearch. From thedata from your JDBC connection to your RDMS (i.e. MySQL,query below, you can see that we have 10 documents in theindex and a sample document.PostgreSQL, Oracle) and syncing it with Elasticsearch. Now youcan start playing with your SQL query and narrowing it downto the data you actually want. However, there still may be aGET /mysqlemployees/ search?size 1number of questions. How do you represent multiple relationaldatabase tables in Elasticsearch? How do you keep track ofupdates?If it didn’t work correctly, check your logstash logs andlogstash.conf. Also: Conirm you can curl the Elasticsearch hosts you speciiedin the conig and get a response with the user andpassword provided. Make sure the user has the right permissions to indexdocuments and create new indexes.8 2018 Rackspace, US Inc.All trademarks, service marks, images, products and brands remain the sole property of their respective holders.

ObjectRocket: MySQL to ElasticsearchModeling the DataThere are a number of diferent ways to model your relational data in Elasticsearch. We’ll give you a few examples below so you candecide what is right for your application.The Sample Data SetLet’s dig into our sample data set to set the stage for how we’ll model the data. We used the Employees sample database providedin the MySQL docs, which provides employee records, with title, salary, and department information in additional tables. See theschema below.As you can see, the employees table contains the main employee records, while the dept emp, dept manager, and titles tables addadditional information about where each employee worked and on which dates.The big question is how to represent those relations within Elasticsearch. Since Elasticsearch can’t really join at query time, how can wemake sure we’re able to grab all relevant data about an employee with a simple query? There are a few options.Methods for Modeling the DataTo keep the queries from getting too excessive, we won’t worry about salaries and managers for now. For the purposes of this example,we’ll just focus on employees, the roles/titles they’ve had, and the departments they’ve been in. This requires joining four diferent tableson the relational side.9 2018 Rackspace, US Inc.All trademarks, service marks, images, products and brands remain the sole property of their respective holders.

ObjectRocket: MySQL to ElasticsearchDenormalizing Your DataAn easy solution is to just join everything on the SQL side and replicate. You can essentially create an Elasticsearch document for everycombination of employee, title, and department they were in.Here’s what the logstash conig looks like in this case:input {jdbc {jdbc driver library �jdbc driver class “com.mysql.jdbc.Driver”jdbc connection string “jdbc:mysql://mySQLhostname:3306/database”jdbc paging enabled truejdbc user “mysqluser”jdbc password “mysqlpassword”statement “SELECT e.emp no as ‘employee number’, birth date, irst name, last name, gender, hire date, t.title AS‘title.name’, t.from date AS ‘title.from date’, t.to date AS ‘title. to date’, d.dept no AS ‘department.number’,ds.dept name AS ‘department.name’, d.from date AS ‘department.from date’, d.to date AS ‘department.to date’ FROMemployees e LEFT JOIN (titles t, dept emp d, departments ds) ON (e.emp no t.emp no AND e.emp no d.emp no ANDd.dept no ds.dept no AND t.from date d.to date AND t.to date d.from date)”lowercase column names true}}output {elasticsearch {id “esoutput”document id “%{employee number} %{department.number} %{title.name} %{title.from date}”hosts [“eshostone”, “eshosttwo”]user “esuser”password “espassword”ssl “true”index “mysqlempdenorm”}}The two key changes are the SQL query, which is now a large set of joins, and the document id on the Elasticsearch side. The SQL queryis pretty standard, but the real key is that we’re looking for every title an employee had in the company and then making sure we’rejoining only the department(s) they were in while holding that title.In the case of the document id, now there can be multiple documents for each employee number since each employee could potentiallyhave had multiple titles/roles within the company. Therefore, we created an ID that takes into account department, title, and start date.The start date was a later add, just to be extra sure that if an employee left a role, then later went back to that same role in that same10 2018 Rackspace, US Inc.All trademarks, service marks, images, products and brands remain the sole property of their respective holders.

ObjectRocket: MySQL to Elasticsearchdepartment, we won’t miss it. None of this is required, though. You can simply let Elasticsearch pick an ID for you. We did it this way so thatif we ever wanted to update or overwrite employees, we could base the ID on this identifying information.One other note is that we’ve enabled paging with the jdbc paging enabled setting. As the dataset starts to get larger, you may need tobreak up the output from the relational database to manage the load. However, this could cause some issues because overlap/reloadingparts of the data is possible. What we did with the ID above makes sure we don’t create duplicates.Here’s a resulting Elasticsearch doc:Pros It’s pretty easy to identify a{“ index”: “mysqlempdenorm”,“ type”: “doc”,Cons Potentially larger spacespeciic employee and their roleusage, since you have everyat any point in time.combination of every table. This You get great Kibana support,could be a problem for largerdata sets.“ id”: “10007 d008 Staff 1989-02-10T00:00:00.000Z”,since you’re not using some of“ score”: 1.0,the Elasticsearch joins (nested It’s a pretty expensive query ontypes, parent/child) that aren’tthe SQL side and could causewell supported in Kibana.some performance issues.“ source”:{ Queries that rely on distinctemployee counts can be trickier“department.from date”: “1989-02-10T00:00:00.000Z”,(see below).“birth date”: “1957-05-23T00:00:00.000Z”,“@timestamp”: “2018-04-10T19:31:38.773Z”,“gender”: “F”,Create Arrays or Nested Objects forDepartments and Titles“hire date”: “1989-02-10T00:00:00.000Z”,Another option is to just aggregate the titles and departments“department.number”: “d008”,into arrays/nested objects within the employee docs. This“department.name”: “Research”,still requires some joining on the SQL side, but also uses the“title.name”: “Staff”,“department.to date”: “9999-01-01T00:00:00.000Z”,“title.to date”: “1996-02-11T00:00:00.000Z”,“aggregate” ilter within Logstash to combine things like thetitles and departments. What we’ll end up with is a documentper employee with arrays for the roles and departments“employee number”: 10007,they’ve been in. Let’s look at how this looks in our Logstash“irst name”: “Tzvetan”,conig (example only shows the ields that have changed).“last name”: “Zielinski”,}}“title.from date”: “1989-02-10T00:00:00.000Z”,Note 1: The Logstash docs call it out, but know that when“@version”: “1”using the ”aggregate” ilter the way we do in the example, it’simportant to set the number of pipeline workers in Logstashto 1. The ilter below requires that all docs with the sameemployee number are next to each other, which is notguaranteed if you use multiple worker threads. You should alsoturn of jdbc paging enabled, which can also cause issues withthis ilter.Note 2: You can also do some of this aggregating on therelational side. MySQL, for example, has some functions in5.7.22 and later that will allow you to combine multiple rowsinto a JSON array.11 2018 Rackspace, US Inc.All trademarks, service marks, images, products and brands remain the sole property of their respective holders.

ObjectRocket: MySQL to Elasticsearchinput {jdbc {statement “SELECT e.emp no as ‘employee number’, birth date, irst name, last name, gender, hire date, t.titleAS ‘title.name’, t.from date AS ‘title.from date’, t.to date AS ‘title.to date’, d.dept no AS ‘department.number’, ds.dept name AS ‘department.name’, d.from date AS ‘department.from date’, d.to date AS ‘department.to date’ FROM employees e LEFT JOIN (titles t, dept emp d, departments ds) ON (e.emp no t.emp no AND e.emp no d.emp no AND d.dept no ds.dept no AND t.from date d.to date AND t.to date d.from date) ORDER BY e.emp noASC”}}ilter {aggregate {task id “%{employee number}”code “map[‘employee number’] event.get(‘employee number’)map[‘birth date’] event.get(‘birth date’)map[‘irst name’] event.get(‘irst name’)map[‘last name’] event.get(‘last name’)map[‘gender’] event.get(‘gender’)map[‘hire date’] event.get(‘hire date’)map[‘roles’] []map[‘roles’] {‘title.name’ event.get(‘title.name’),’title.from date’ event.get(‘title.fromdate’),’title.to date’ event.get(‘title.to date’),’department.number’ ame’ m date’ event.get(‘department.from date’),’department.to date’ event.get(‘department.to date’)}event.cancel()“push previous map as event truetimeout 30}}output {elasticsearch {document id “%{employee number}”index “mysqlempnested”}}12 2018 Rackspace, US Inc.All trademarks, service marks, images, products and brands remain the sole property of their respective holders.

ObjectRocket: MySQL to ElasticsearchThe SQL query is almost exactly the same as before, but the big diference is that we’re now speciically ordering by employee number.This is extremely important for the aggregate ilter mentioned below because it depends on documents with the same employeenumber being next to each other.The aggregate ilter that we use will create a temporary map that appends each diferent title/department that comes through to anarray. When it sees a document with a diferent task id/employee number, it will push the map and its array of roles as a new event(push previous map as event true). This is very similar to an example in the Logstash documentation.Outside the Logstash conig, you also have a choice of whether you want to put the roles into a nested ield or not. If you go the nestedroute, queries about titles and departments should be more accurate, but the downside is that queries will need to change a bit, andKibana support for nested ields is not great.To make these items a nested ield, you will need to specify that in the template or mapping before you load the data:PUT /mysqlempnested{“mappings”: {“doc”: {“properties”: {“roles”: {“type”: “nested”}}}}}13 2018 Rackspace, US Inc.All trademarks, service marks, images, products and brands remain the sole property of their respective holders.

ObjectRocket: MySQL to ElasticsearchHere’s a resulting Elasticsearch doc:{“ index”: “mysqlempnested”,“ type”: “doc”,“ id”: “10007”,“ score”: 1.0,“ source”:{“last name”: “Zielinski”,“employee number”: 10007,“irst name”: “Tzvetan”,“hire date”: “1989-02-10T00:00:00.000Z”,“@timestamp”: “2018-04-12T17:34:53.300Z”,“gender”: “F”,“@version”: “1”,“birth date”: “1957-05-23T00:00:00.000Z”,“roles”: [{“title.from date”: “1996-02-11T00:00:00.000Z”,“department.to date”: “9999-01-01T00:00:00.000Z”,“title.name”: “Senior Staff”,“department.from date”: “1989-02-10T00:00:00.000Z”,“title.to date”: ��: “Research”,“department.number”: “d008”},{“title.from date”: “1989-02-10T00:00:00.000Z”,“department.to date”: “9999-01-01T00:00:00.000Z”,“title.name”: “Staff”,“department.from date”: “1989-02-10T00:00:00.000Z”,“title.to date”: ��: “Research”,“department.number”: “d008”}]}}14 2018 Rackspace, US Inc.All trademarks, service marks, images, products and brands remain the sole property of their respective holders.

ObjectRocket: MySQL to ElasticsearchProsConsUse Parent-Child RelationshipsYet another option is to use the parent/child facilities It’s still a pretty expensive queryin Elasticsearch. This requires a little more complicatedeasier to manage and may workon the SQL side and could causebetter with some aggregations.some performance issues.Logstash coniguration, since you’ll need one query for A single doc per employee is If you go the nested route, theremay be some diiculties inthe parents and one for the children. Logstash 6.x makesthis easy, because it includes the ability to create multipleKibana, since nested ields arepipelines, so you can just create a coniguration ile for eachnot supported.type. This is still possible in earlier versions of Logstash,but you’ll have to use multiple input plugins with diferentqueries and then use a conditional on the output plugin todetermine whether you’re loading a parent or child doc.Elasticsearch parent-child index mapping:The example will show the latter, since it will work inmost versions of Logstash.PUT /mysqlempparentchild{One other note is that parent/child has changed signiicantlyin Elasticsearch 6.x because there are no longer multiplemapping types per index. It also seems like the support forparent/child in Logstash 6.x is not quite straightforward“mappings”: {when connecting to an Elasticsearch 6.x cluster. The pipeline“doc”: {“properties”: {“doctype”: {“type”: “join”,“relations”: {“employee”: “role”}}}}on page 16 gets it working.The irst order of business is to set up the mapping with thenew “join” ield type to facilitate the parent/child mapping.Due to the changes in Elasticsearch 6.x, you’ll need to createa ield of a new “join” type (named “doctype” on the left) andspecify the relationships between the various values for thatield (in the “relations” sub-ield). In our case, our parents willset the “doctype” to “employee” and the children will setit to “role.”}}15 2018 Rackspace, US Inc.All trademarks, service marks, images, products and brands remain the sole property of their respective holders.

ObjectRocket: MySQL to ElasticsearchLogstash coniginput {jdbc {statement “SELECT emp no as ‘employee number’, birth date, irst name, last name, gender, hire date FROM employeese ORDER BY employee number ASC”add ield { “doctype” “employee” }}jdbc {statement “SELECT t.emp no as ‘employee number’, t.title AS ‘title.name’, t.from date AS ‘title.from date’,t.to date AS ‘title.to date’, d.dept no AS ‘department.number’, ds.dept name AS ‘department.name’, d.from date AS‘department.from date’, d.to date AS ‘department.to date’ FROM titles t LEFT JOIN (dept emp d, departments ds) ON(t.emp no d.emp no AND d.dept no ds.dept no AND ( t.from date BETWEEN d.from date AND d.to date OR d.from dateBETWEEN t.from date AND t.to date)) ORDER BY employee number ASC”}}ilter {if [doctype] ! “employee” {mutate {add ield {“[doctype][name]” “role”“[doctype][parent]” “%{employee number}”}}}}output {if [doctype] “employee” {elasticsearch {id “esparentoutput”document id “%{employee number}”index “mysqlempparentchild”}} else {elasticsearch {id “eschildoutput”document id “%{employee number} %{department.number} %{title.name} %{title.from date}”index “mysqlempparentchild”routing “%{employee number}”}}}16 2018 Rackspace, US Inc.All trademarks, service marks, images, products and brands remain the sole property of their respective holders.

ObjectRocket: MySQL to ElasticsearchA summary of the key changes from the previous page: You now have two input blocks—one that queries the parentPros A single doc per employee isCons Parent-child requires you to usedocs and one that queries the child docs. The one for theeasier to manage and may workspecial/speciic queries, and theparent docs sets the doctype, so we can identify them asbetter with some aggregations.support in Kibana is not there.parents downstream and so we can avoid the need for anothermutate statement la

In our example, we’re running Elasticsearch on the ObjectRocket service. So, we’ll create an admin user and whitelist the IP for the Logstash server in the ObjectRocket UI. On the Elasticsearch side, it’s as simple as making sure that you have appropriate