1

posted this before but never received a clear answer.

I'm working on an ETL project and am basically feeding JSON records of "tickets" into a MySQL database. (actually come to think of it, may be migrating to SQL server, but I'm not sure that will be a huge difference).

These JSON records are the "current data" for each ticket.

So it will say, for example:

Ticket_Id: 1 Ticket_Subject: Hi Ticket_Issue: Greeting Ticket_Status: Open

In the future I might get a record like:

Ticket_Id: 1 Ticket_Subject: Hi Ticket_Issue: Greeting Ticket_Status: Closed

These update records are stored in the order in which they are made ... so it's pretty easy to sweep through each day, parse the JSON, and perform an Insert/ Update into a SQL database/ table with Ticket records.

Here's the issue. There's currently 100k tickets in the database. In 6 months there may be 500k tickets.

The ETL process is already fairly slow, and I need to work on optimizing each step. For now, I want to focus on the SQL Insert/ Update.

Currently it's lookup against 100k rows ... soon it will be looking up against 500k ... eventually 2 million ... you get the idea. This is NOT scalable currently.

Luckily, I know that these tickets become "archived"/ uneditable after 30 days of inactivity. They become "closed" permanently and will never be changed or updated again.

Thus, there may be a logical solution that somehow transfers, locks, moves, partitions, ignores .... I simply don't know ... these "closed/ archived" tickets ... so I only have to perform daily lookups against 30k active tickets any given day. Not a million.

This seems like an extremely common issue (virtually ANY large insert/ update conundrum) .... but I have not found many simple ideas online.

I'm wondering what is a simple, maintainable solution to this problem. Even if the "simplest" is sadly two different tables or what have you. I have no problem doing a "union" select statements --- in actuality the 'select' query times are already super fast and not a problem. The problem is the insert/ update/ lookup/ write times.

Any ideas?

  • Hi Marek -- it's a data integration project, and the BI tool we are using requires a SQL architecture. Unfortunately, one of our application data sources is a massive 3rd-party tool that uses JSON. I know bashing JSON into a SQL DB kinda sucks ... but it can be done a few ways. The fact that the incoming data was originally JSON though is not the main issue. ... On your second point, how do you specify that an insert/ update will only target certain rows without AGAIN scanning every row? It's already checking every ID . your solution is check every is_active? Nothing gained there, right? – user45867 Apr 27 '15 at 22:40
  • Just to clarify, the JSON-to-SQL architecture is not the issue/ main point of difficulty. The main point of difficulty is that Insert/ Update .... even between two SQL sources, whatever .... is not inherently scalable if you're looking up more and more records each time ... it takes linearly more time every day you add more records. – user45867 Apr 27 '15 at 22:41
1

Parse the JSON when the record in inserted, not later. (OK, maybe you need to collect the JSON in a "staging" table between arrival and processing.)

When you parse the JSON, extract only the fields that you need to manipulate/search/etc in MySQL. Be conservative. Don't extract many fields. Leave the JSON as is in case you need the details later. It would even be good to compress it and store it into a BLOB -- to save space, hence decrease I/O.

What queries will you be doing on the data? Studying them is critical to the design of a Data Warehouse.

Properly indexed (etc), a table that grows by a factor of 10 may be only 10% slower. Don't panic, but do look at the queries. I have seen billion-row tables that hum along nicely.

It sounds like you are inserting only about 2 rows per minute; is that correct? 100 per second is when it starts to get challenging.

Let's worry about the "archiving" after we have covered some of the issues I mentioned.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.