Ndtyjky

recently our 5-year old MySQL data warehouse (used mostly for business reporting) has gotten quite full and we need to come up with a way to archive old data which is not frequently accessed to clear up space.

I created a process which dumps old data from the DW into .parquet files in Amazon S3, which are then mapped onto an Athena table. This works quite well.

however we sometimes add/rename/delete columns in existing tables. I'd like the changes to be reflected in the old, archived data as well, but I just can't come up with a good way to do it without reprocessing the entire dataset.

is there a 'canon' way to mantain structural compatibility between a live data warehouse and its file-based archived data? I've googled relevant literature and come up with nothing.

should I just accept the fact that if I need to actively maintain schemas then the data is not really archived?

There are tons of materials in internet if you search the term "Schema evolution" in big data space.

The Athena documentation has a chapter on schema updates case by case example here.

If you are re-processing the whole archived dataset to handle schema change, probably you are doing a bit too much.

Since you have parquet files and by default Athena parquet resolves the column by column name rather than by index, you are safe in almost all cases i.e. add new columns, drop columns etc except column rename. TO handle renamed columns (and to handle addition/dropping of columns), the fastest way is to use view. In the view definition you can alias the renamed column. Also, if column rename is mostly the case of your schema evolution and if you are doing it a lot, you can also consider AVRO to gracefully handle that.

Plan A:

It's too late to do this, but PARTITIONing is an excellent tool for getting the data out of the table.

I say "too late" because adding partitioning would require enough space for making a copy of the already-big table. And you don't have that much disk space?

If the table were partitioned by Year or Quarter or Month, you could

• Every period, "Export tablespace" to remove the oldest from the partition scheme.


• That tablespace will the be a separate table; you could copy/dump/whatever, then drop it.

At about the same time, you would build a new partition to receive new data.

(I would keep the two processes separate so that you could stretch beyond 5 years or shrink below 5 with minimal extra effort.)

A benefit of the method is that there is virtually zero impact on the big table during the processing.

An extra benefit of partitioning: You can actually return space to the OS (assuming you have innodb_file_per_table=ON).

Plan B:

Look at what you do with the oooold data. Only a few things? Possibly involving summarization? So...

• Don't archive the old data.


• Summarize the data to-be-removed into new tables. Since they will be perhaps one-tenth the size, you can keep them online 'forever'.