Migrating, JSON, MySQL – Migrating data from one API to another

Well as usual, I am just storing a post to keep track of some practices. As I know that maybe in 6 months or 2 years, I may have to do this kind of quick and dirty job again.

My issue was the following:

I have data stored in a third party API and due to budget cuts, I need to backup and migrate this data to a less expansive solution. In order to do so, I have made up a small “ToDoList” where I have written down the following steps :

Making a dump of the data from the API. The dump consists a bunch of JSON files of 100 records. As you can imagine, I have 200 000 users recorded in the API, so basically I have 2 000 JSON files to parse. I just gave 3 sample files with fake data as example.
Studying the JSON output in order to parse it with PHP and grab the data in order to insert into a database. I have chosen MySQL with the help of PHP-Cli to script the all insertion.
Iterate via a command line to repetitively do the injection of each record with the minimum of programming. I will leverage on Bash to do so even though I have tried to work with Automator in the first time.

For the step 1, I had to mock-up the JSON output of the API and generate fake data with the help of json-generator.com. I give also the website that I am using to validate the JSON structure : jsonlint.com

The script for json-generator.com

		[
  '{{repeat(100)}}',
  {
    email: '{{firstName().toLowerCase()}}.{{surname().toLowerCase()}}@{{company().toLowerCase()}}.com',
    mojoinc: {
      properties: {
        managedBy: [{
          clientId: '{{guid()}}',
          id: '{{integer(1000000, 1000000000)}}'
        }]
      }
    },
    uuid: '{{guid()}}'
  }
 
  ]

The files are the following:

mojoinc_backup_all.sql: a quick scheme of the database that will receive the output of the JSON files.
parse_and_insert.php: the script that enable the insert into the DB from the JSON files.
lauch_sh_insert.sh: the bash script and the php file that help to generate it: launch_phpcli_parse_and_insert.php
Some JSON with fake data: dump_generated_1.json, dump_generated_2.json, dump_generated_3.json

The files can be found @https://github.com/bflaven/BlogArticlesExamples/tree/master/parse_json_inject_mysql

Create JSON with the JSON Generator