{"id":10984,"date":"2017-12-17T15:58:26","date_gmt":"2017-12-17T14:58:26","guid":{"rendered":"http:\/\/flaven.fr\/?p=10984"},"modified":"2017-12-17T21:01:54","modified_gmt":"2017-12-17T20:01:54","slug":"migrating-json-mysql-migrating-data-from-one-api-to-another","status":"publish","type":"post","link":"https:\/\/flaven.fr\/2017\/12\/migrating-json-mysql-migrating-data-from-one-api-to-another\/","title":{"rendered":"Migrating, JSON, MySQL &#8211; Migrating data from one API to another"},"content":{"rendered":"<p>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.<\/p>\n<p>My issue was the following:<\/p>\n<p>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 &#8220;ToDoList&#8221; where I have written down the following steps :<\/p>\n<ol>\n<li>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.<\/li>\n<li>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.<\/li>\n<li>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.<\/li>\n<\/ol>\n<p><b>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<\/b><\/p>\n<p><b>The script for json-generator.com<\/b><\/p>\n<pre lang=\"json\">\r\n\t\t[\r\n  '{{repeat(100)}}',\r\n  {\r\n    email: '{{firstName().toLowerCase()}}.{{surname().toLowerCase()}}@{{company().toLowerCase()}}.com',\r\n    mojoinc: {\r\n      properties: {\r\n        managedBy: [{\r\n          clientId: '{{guid()}}',\r\n          id: '{{integer(1000000, 1000000000)}}'\r\n        }]\r\n      }\r\n    },\r\n    uuid: '{{guid()}}'\r\n  }\r\n\r\n  ]\r\n\t<\/pre>\n<p>The files are the following: <\/p>\n<ul>\n<li>mojoinc_backup_all.sql: a quick scheme of the database that will receive the output of the JSON files.<\/li>\n<li>parse_and_insert.php: the script that enable the insert into the DB from the JSON files.<\/li>\n<li>lauch_sh_insert.sh: the bash script and the php file that help to generate it: launch_phpcli_parse_and_insert.php<\/li>\n<li>Some JSON with fake data: dump_generated_1.json, dump_generated_2.json, dump_generated_3.json<\/li>\n<\/ul>\n<p><b>The files can be found <a href=\"https:\/\/github.com\/bflaven\/BlogArticlesExamples\/tree\/master\/parse_json_inject_mysql\" target=\"_blank\">@https:\/\/github.com\/bflaven\/BlogArticlesExamples\/tree\/master\/parse_json_inject_mysql<\/a><\/b><\/p>\n<p><b>Create JSON with the JSON Generator<\/b><br \/>\n<img loading=\"lazy\" decoding=\"async\" class=\"aligncenter\" src=\"https:\/\/flaven.fr\/wp-content\/uploads\/2017\/12\/parse_json_inject_mysql_1.jpg\" width=\"640\" height=\"480\" alt=\"Migrating, JSON, MySQL - Migrating data from one API to another\"><\/p>\n<p><b>JSON validated with JSONLint<\/b><br \/>\n<img loading=\"lazy\" decoding=\"async\" class=\"aligncenter\" src=\"https:\/\/flaven.fr\/wp-content\/uploads\/2017\/12\/parse_json_inject_mysql_2.jpg\" width=\"640\" height=\"480\" alt=\"Migrating, JSON, MySQL - Migrating data from one API to another\"><\/p>\n<h2>Read more<\/h2>\n<ul>\n<li>JSON Generator: Create Random, Structured JSON Mock Data with Finesse<br \/><a href=\"https:\/\/blog.runscope.com\/posts\/json-generator\" target=\"_blank\">https:\/\/blog.runscope.com\/posts\/json-generator<\/a><\/li>\n<li>JSONLint &#8211; The JSON Validator<br \/><a href=\"https:\/\/jsonlint.com\/\" target=\"_blank\">https:\/\/jsonlint.com\/<\/a><\/li>\n<li>JSON Generator \u2013 Tool for generating random data<br \/><a href=\"https:\/\/www.json-generator.com\/\" target=\"_blank\">https:\/\/www.json-generator.com\/<\/a><\/li>\n<li>Open a list of URL\u2019s in Google Chrome using Mac Automator<br \/><a href=\"https:\/\/multiplestates.wordpress.com\/2016\/01\/06\/open-a-list-of-urls-in-google-chrome-using-mac-automator\/\" target=\"_blank\">https:\/\/multiplestates.wordpress.com\/2016\/01\/06\/open-a-list-of-urls-in-google-chrome-using-mac-automator\/<\/a><\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>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&hellip; <\/p>\n<p class=\"text-center\"><a href=\"https:\/\/flaven.fr\/2017\/12\/migrating-json-mysql-migrating-data-from-one-api-to-another\/\" class=\"more-link\">Continue reading &rarr; <span class=\"screen-reader-text\">Migrating, JSON, MySQL &#8211; Migrating data from one API to another<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":10988,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"bf_ai_meta_description":"Migrating data from one API to another using JSON and MySQL. Tools and outcomes for budget-friendly data backup and migration.","bf_ai_og_title":"Migrate Data: JSON, MySQL","footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[3439,3437,3454,3444,3447,3448,3449,3435],"tags":[2183,308,2403,192,2401,2402,2404,2211],"class_list":["post-10984","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-apis-integration","category-business-case-studies","category-journalism-writing","category-programming-databases","category-technology-trends","category-tools-productivity","category-tutorials-how-to","category-web-development","tag-api","tag-backup","tag-dump","tag-json","tag-json-generator","tag-jsonlint","tag-migrate","tag-mysql"],"jetpack_publicize_connections":[],"jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p3Vuhl-2Ra","jetpack_featured_media_url":"https:\/\/flaven.fr\/wp-content\/uploads\/2017\/12\/parse_json_inject_mysql_b.jpg","_links":{"self":[{"href":"https:\/\/flaven.fr\/happy-api\/wp\/v2\/posts\/10984","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/flaven.fr\/happy-api\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/flaven.fr\/happy-api\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/flaven.fr\/happy-api\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/flaven.fr\/happy-api\/wp\/v2\/comments?post=10984"}],"version-history":[{"count":3,"href":"https:\/\/flaven.fr\/happy-api\/wp\/v2\/posts\/10984\/revisions"}],"predecessor-version":[{"id":10990,"href":"https:\/\/flaven.fr\/happy-api\/wp\/v2\/posts\/10984\/revisions\/10990"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/flaven.fr\/happy-api\/wp\/v2\/media\/10988"}],"wp:attachment":[{"href":"https:\/\/flaven.fr\/happy-api\/wp\/v2\/media?parent=10984"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/flaven.fr\/happy-api\/wp\/v2\/categories?post=10984"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/flaven.fr\/happy-api\/wp\/v2\/tags?post=10984"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}