{"id":457,"date":"2009-11-03T22:37:54","date_gmt":"2009-11-04T05:37:54","guid":{"rendered":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/2009\/11\/03\/instant-relief-from-mysql-reporting-queries-incremental-updates\/"},"modified":"2009-11-03T22:37:54","modified_gmt":"2009-11-04T05:37:54","slug":"instant-relief-from-mysql-reporting-queries-incremental-updates","status":"publish","type":"post","link":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/2009\/11\/03\/instant-relief-from-mysql-reporting-queries-incremental-updates\/","title":{"rendered":"Instant Relief from MySQL Reporting Queries: Incremental Updates"},"content":{"rendered":"

Yesterday, I covered how you can do an initial “replication” of data from MySQL to DynamoDB<\/a> and how this can improve performance, and save storage space. The follow on question becomes:<\/p>\n

That’s Great Nick. But how do I do keep my data up to date?<\/strong><\/p>\n

We’ve got data in our Airline Performance dataset through 31-DEC-2007. I loaded 1 year, all of 2007, for the previous example. What happens when the FAA publishes their 2008 January results, and we’ve loaded the new months worth of data into MySQL?<\/p>\n

MySQL:<\/p>\n

select count(*) from otp.ontime; 8061223<\/strong>
\nselect count(*) from ontime where FlightDate > ‘2007-12-31’; 605765<\/strong>
\nselect count(*) from ontime where FlightDate <= ‘2007-12-31’; 7455458<\/strong><\/p><\/blockquote>\n

DynamoDB:<\/p>\n

select count(*) from FASTER.”ontime”; 7455458<\/strong> <\/p><\/blockquote>\n

So, we’ve added approximately 600k new records to our source system that we don’t have in our reporting system. How do we incrementally insert these records and get just the 600k new rows into our DynamoDB reporting instance?<\/p>\n

Easy Easy Easy.<\/p>\n

We’ve already done all the work, all we have to do is simply get records we haven’t processed yet! Should take just a few minutes to get our current table “up to date” with the one over in MySQL.<\/p>\n

DynamoDB:<\/p>\n

select max(“FlightDate”) from FASTER.”ontime”; 2007-12-31<\/strong>
\ninsert into FASTER.”ontime” select * from MYSQL_SOURCE.”ontime” where “FlightDate” > DATE ‘2007-12-31’; 605765<\/strong><\/p><\/blockquote>\n

In other words, let’s select from MySQL any records whose date is beyond what we have currently (2007-12-31).<\/p>\n

select count(*) from FASTER.”ontime”; 8061223
\n<\/strong>select count(*) from FASTER.”ontime” where “FlightDate” > DATE ‘2007-12-31’; 605765<\/strong><\/p><\/blockquote>\n

MySQL:
\nWhile the DynamoDB INSERT<\/strong> statement was running, the following SQL was being run on MySQL.<\/p>\n

show processlist shows a SQL session with the following SQL:
\nSELECT * FROM `ontime` WHERE `FlightDate` > DATE ‘2007-12-31’;<\/p><\/blockquote>\n

A single SQL statement (insert into select * from table where date > last time<\/strong>) has you up to date for reporting! Long term we may look to work with Tungsten to be able to keep our data up to date using replication bin log records but for now, this simple pull based approach.<\/p>\n","protected":false},"excerpt":{"rendered":"

Yesterday, I covered how you can do an initial “replication” of data from MySQL to DynamoDB and how this can improve performance, and save storage space. The follow on question becomes: That’s Great Nick. But how do I do keep my data up to date? We’ve got data in our Airline Performance dataset through 31-DEC-2007. […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[5],"tags":[],"_links":{"self":[{"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/posts\/457"}],"collection":[{"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/comments?post=457"}],"version-history":[{"count":0,"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/posts\/457\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/media?parent=457"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/categories?post=457"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/tags?post=457"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}