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
- Download the repo:
https://gitlab.com/taeluf/json-to-mysql.git
(or install via composer.taeluf/json-to-mysql v0.1.x-dev
) - Review the code if you like (Can you really trust code you blindly download?)
- Edit
test/run.php
with the correct input file, output directory, desired chunk-size, & table name - Create the output directory
- Execute
php run.php
in your terminal, in thetest
directory - Import
.sql
files into your database. First thecreate
file, then theinsert
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
-
- 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 thatis_
. -
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 a1. Download repo 2. execute script with ARGS
Usage process - NULL is used if value not present... this could be better.