Reddit DevOps

How can I speed up this data scrubber?

Alright,

I'm a jr devops engineer and I have been asked to write a data scrubber to remove sensitive information from every instance of the db besides prod. There is a Kubernetes job that compresses the contents of the prod db into a single .tar file using mydumper. The engineers/dev & sandbox environments pull this file and decompress it to update their db's. Naturally, I decided to build scrubbing logic in this job.

The job is written in bash so I stuck with a bash/sql (specifically MySQL) combo to accomplish the scrubbing. I got a working model, however, the database is large enough that it will take 20+ hours to run across all the tables. This is partially due to the fact that I have to process the data in batches to prevent consuming absurd amounts of memory in the cloud. Here are the basics of how it works.





1. The scrubbing starts after the database has been dumped using the mydumper tool. The output is anywhere between 1 .sql file to 200 .sql files, depending on table size.
2. Spin up a local mysql instance in a separate k8s container and start processing the data table by table, file by file.
3. Create the schema for the target table using the provided schema file.
4. Write the target file's contents to the table and begin the scrubbing logic. I am using MD5 to hash the value, replacing all the characters to be integers or letters to match the column type and slicing the hashed value to meet the length constraints. Dates are being offset by a random number of days determined by the first 4 integers of the hashed date. (see code below)
5. Use the mydumper tool to dupm the scrubbed table and overwrite the original .sql file with the scrubbed file.
6. Compress the directory with the scrubbed files/files that don't need to be scrubbed and push the .tar file to a bucket.

Here is the code but the field and table names are changed to something generic:

echo 'scrubbing table'
mysql -D prod -u root -S /mysql-socket/mysql.sock < proddump/prod.table-schema.sql
for filename in proddumpnohj/*table*.sql; do
  if [[ $filename != *schema*.sql ]]; then
    echo "writing $filename to database"
    mysql -D prod -u root -S /mysql-socket/mysql.sock < $filename
    echo "scrubbing table $filename"
    mysql -D prod -u root -S /mysql-socket/mysql.sock << "END"
      SET FOREIGN_KEY_CHECKS=0;
      UPDATE table SET string_value = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING(MD5(string_value), 1, 20), 0, 'g'), 1, 'h'), 2, 'i'), 3, 'j'), 4, 'k'), 5, 'l'), 6, 'm'), 7, 'n'), 8, 'o'), 9, 'p'),
      int_value = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING(MD5(int_value), 1, 10), 'a', '1'), 'b', '2'), 'c', '3'), 'd', '4'), 'e', '5'), 'f', '6'),
      date_value = DATE_ADD(appointment_date, INTERVAL REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING(MD5(IFNULL(IFNULL(patient_id, date_value), 'NULL NULL')), 1, 4), 'a', '1'), 'b', '2'), 'c', '3'), 'd', '4'), 'e', '5'), 'f', '6') DAY_HOUR ),
      email_and_phone_values = IF (
        sent_to LIKE '%@%',
        CONCAT(SUBSTRING(MD5(email_and_phone_values), 1, 20), '@scrubbed.com'),
        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING(MD5(email_and_phone_values), 1, 10), 'a', '1'), 'b', '2'), 'c', '3'), 'd', '4'), 'e', '5'), 'f', '6')
      );
END
    echo "dumping table"
    `mydumper` -u root -r 250000 -S /mysql-socket/mysql.sock -B prod -t 8 -o /mnt/ssd_storage/scrubbedproddump -l 7200 -T 'table'
    echo "deleting from table"
    mysql -D prod -u root -S /mysql-socket/mysql.sock << "END"
    TRUNCATE table;
END
    echo "copying file to output"
    ls scrubbedproddump
    if test -f 'prod.table.sql'
    then
      cp /mnt/ssd_storage/scrubbedproddump/prod.table.sql  /mnt/ssd_storage/$filename
    else
      cp /mnt/ssd_storage/scrubbedproddump/prod.table.00000.sql /mnt/ssd_storage/$filename
    fi
    rm -r /mnt/ssd_storage/scrubbedproddump
  fi
done

I know this is a lot so thanks if you made it this far. I'm just digging

4 views03:34