Skip to content

Instantly share code, notes, and snippets.

Forked from quickbreach/
Last active August 11, 2021 23:11
Show Gist options
  • Save netj/96560d35c6e617fc1b3f21da43f06b2c to your computer and use it in GitHub Desktop.
Save netj/96560d35c6e617fc1b3f21da43f06b2c to your computer and use it in GitHub Desktop.
Convert mysqldump to JSON, optional upload to elastic
import re
import csv
import json
import sys
import requests
from requests.auth import HTTPBasicAuth
import hashlib
Usage: 'python3 <targetFile.sql>
Parses an '.sql' file from mysqldump and converts it to JSON for elastic or other document DBS.
The mysqlFileToIter function parses the sql file into into python objects & arrays, which can be converted into JSON with json.dumps(arrayOfObjects)
Each resulting object represents a row from a table in the original SQL.
mysqlFileToIter adds the fields 'sqltojson_table_name' and 'sqltojson_source_file' to each document. keep or delete them as you wish, ie.
# The brains of the operation
def mysqlFileToIter(sqlFile):
tac = dict()
currentTable = ''
inTableStructure = False
for line in open(sqlFile, "r"):
if'^CREATE TABLE `\S.+`', line) != None:
inTableStructure = True
currentTable ='^CREATE TABLE `\S.+`', line).group(0).replace('CREATE TABLE ', '').replace('`', '')
tac[currentTable] = []
if inTableStructure:
if line.find('PRIMARY KEY') > -1 or line.find(' KEY `') > -1:
# Create a map of each table name and it's associated columns. Each column name is preceeded by two spaces
if'^\ \ `\S.+`', line) != None:
columnName ='^\ \ `\S.+`', line).group(0).strip().replace('`', '')
# This tells us that we are no longer in table description
if'^\)', line) != None:
inTableStructure = False
if'^INSERT INTO `' + currentTable + '` VALUES', line) != None:
line = line[line.find("("):]
entriesArray = line.split("),(")
# Remove the semicolon at the end of the last entry
if (entriesArray[-1][-1] == ';'):
entriesArray[-1] = entriesArray[-1][:-1]
# iterate through the table rows
for entry in entriesArray:
tmp = dict()
# Carve apart the row with the CSV parser
cvp = list(csv.reader([entry], quotechar="'", delimiter=',', escapechar='\\'))[0]
# Create
for x in range(0, len(cvp)):
columnName = tac[currentTable][x]
tmp[columnName] = cvp[x]
tmp['sqltojson_table_name'] = currentTable.replace(");", "")
tmp['sqltojson_source_file'] = sqlFile
yield tmp
except Exception as e:
print("Exception in line-49 loop: " + str(e), file=sys.stderr)
# Takes in the array from sqlFileToArray, then converts each document in it to JSON and posts it to elastic
def sample_postToElastic(document):
indexName = ""
headers = {
'Content-Type': 'application/json'
# Choose your own adventure: you pick what index the data goes into. I'm recreating the speration of tables here
indexName = document['sqltojson_table_name']
# Helps avoid duplicate entries, especially if you run the job multiple times
s = hashlib.sha1()
docId = s.hexdigest()
# Posts the document to an index with the same name as the original table
resp ='' + indexName + '/_doc/' + str(docId), auth = HTTPBasicAuth('username', 'password'), headers = headers, data = json.dumps(document))
if(resp.status_code != 200 and resp.status_code != 201):
# Something went wrong
print("Elastic returned non-successful response")
if len(sys.argv) < 2:
print("Usage: python3 <targetFile.sql>")
# Convert the sql file to an array of JSON documents. Each will have an added 'sqltojson_table_name' field to identify which "table" it belongs to
convertedDb = mysqlFileToIter(sys.argv[1])
# Add each document to elastic. modify the sample_postToElastic to fit your needs
for document in convertedDb:
# Modify the object as you wish here, ie add an '_id' field
s = hashlib.sha1()
document['_id'] = s.hexdigest()
# Prints out the converted document
# Add the document to your db
# sample_postToElastic(document)
# Modify sample_postToElastic for your elastic stack, or use it as guide for other NoSQL document DBs
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment