Taeluf.com

Json to MySql

Convert JSON data into a MySql statement

Features

  • Read small or large JSON source file and output mysql create statement & insert statements
  • Chunk insert statements, limiting max length of output files
  • Output column data for all rows of json file
  • Convert nested arrays into JSON strings for INSERT
  • TRIES to use correct column type (text, bool, varchar, int, float)
  • Build table from generated schema
  • Edit generated schema file to CREATE TABLE with names & types you want
  • Very small RAM footprint - Reads input file line-by-line

Requirements

  • Source Json MUST:
    • be an array of objects with one object per line
    • Have array opener/closer on their own lines
    • Ex: [ \n{...}, \n{...}, \n{...}\n]

Usage

  1. Download the repo: https://gitlab.com/taeluf/json-to-mysql.git (or install via composer. taeluf/json-to-mysql v0.1.x-dev)
  2. Review the code if you like (Can you really trust code you blindly download?)
  3. Edit test/run.php with the correct input file, output directory, desired chunk-size, & table name
  4. Create the output directory
  5. Execute php run.php in your terminal, in the test directory
  6. Import .sql files into your database. First the create file, then the insert files Need to verify, but something like one of these:
    • cat the-file.sql | mysql -u USER -p db_name
    • mysql -u USER -p db_name; ENTER PASSWORD; source /abs/path/to/file.sql
  7. Optionally add an auto_increment column: (I'm not sure if this is quite right)
    • ALTER TABLE table_name ADD COLUMN id PRIMARY KEY AUTO_INCREMENT

Allowed

  • Source Json MAY:
    • Have white-space surrounding json on each line
    • commas at end of each object declaration (but not before)

Warnings

  • Set your length to at least 1024*1024 (roughly one megabyte), or you might end up with several thousand files
  • Setting a length SMALLER than the length of an insert statement... is probably really bad

Column Data

  • Each is_ entry counts the number of rows that matched that is_.
  • sampleValue is the longest found value for that column
"columnName": {
	"maxlen": 4,
	"minlen": 4,
	"count": 22039,
	"is_string": 22039, 
	"is_int": 0,
	"is_float": 0,
	"is_bool": 0,
	"is_array": 0,
	"is_json": 0,
    "is_numeric": 0,
	"sampleValue": "card"
}

Niceties that aren't available / TODO (maybe)

  • Choose format of output (single-line insert VALUES , , , vs. multi-line)
  • Output PHP code to import the mysql data
  • Output a bash script to import the mysql data
  • Have a pre-made script that runs from a config file? Nah nah...
  • Parse multi-line JSON

TODO (maybe)

  • Refactor the code so it's easier to follow & maintain
  • Write a bash script to compress, upload, & uncompress the sql files
  • Write a bash script that will import all the sql files into the database
    • Probably write this WHILE writing the SQL files
  • Modify run.php to take CLI input arguments for output directory & whatnot, so it's just a 1. Download repo 2. execute script with ARGS Usage process
  • NULL is used if value not present... this could be better.