使用SQL处理JSON数据太慢

Question

我有一个案例研究，需要从retail中获取数据，使用聚合函数、联接等对数据进行一些分析，并使用JSON格式的响应数据绘制一些零售图。

目前正在采取的做法如下：

1. 读取JSON中的数据，将这些数据存储在python变量中，并使用insert访问SQL查询。显然，这是一个昂贵的操作，因为每读取一个JSON行，它都会插入到数据库中。对于33k行，它需要超过20分钟，这是低效的。
2. 这可以在弹性搜索中处理，以获得更快的处理速度，但弹性搜索中不存在像联接这样的复杂操作。

如果有人可以建议最好的方法(如python中的预处理或后处理)来处理这样的场景，这将是有帮助的。

SQL脚本

def store_data(AccountNo)

        db=MySQLdb.connect(host=HOST, user=USER, passwd=PASSWD, db=DATABASE, charset="utf8")
        cursor = db.cursor()
        insert_query = "INSERT INTO cstore (AccountNo) VALUES (%s)"
        cursor.execute(insert_query, (AccountNo))
        db.commit()
        cursor.close()
        db.close()
        return

def on_data(file_path):
        #This is the meat of the script...it connects to your mongoDB and stores the tweet
        try:
           # Decode the JSON from Twitter
            testFile = open(file_path)

            datajson = json.load(testFile)
            #print (len(datajson))

            #grab the wanted data from the Tweet
            for i in range(len(datajson)):
                for cosponsor in datajson[i]:
                    AccountNo=cosponsor['AccountNo']
                    store_data( AccountNo)

Answer 1

为了提高数据加载的性能，可以做以下几件事情：

• 将JSON文件转换为CSV，并使用LOAD DATA从文件(示例)加载。这可能是最快的方式去做你想要做的事情。
• 使用.executemany()代替.execute()：datajson = json.load(testFile) insert_query =“”插入cstore (AccountNo)值(%(AccountNo)s)“cursor.executemany(insert_query，datajson)”
• 查看在插入过程中禁用/删除现有索引，然后在插入完成后重新创建它们。
• 确保您没有“通过internet”进行数据加载，并且没有网络延迟和带宽影响，请“靠近”数据库服务器。
• 加载JSON文件可能不是瓶颈，但您也可以查看更快的JSON解析器，如ujson
• 我记得我们在超高速的umysql Python数据库驱动程序中也取得了一些与数据加载性能相关的成功，但是看起来这个包已经有很长一段时间没有维护了。