MySQL通用批量写入工具（Python）

背景   平台目前的分析任务主要以Hive为主，分析后的结果存储在HDFS，用户通过REST API或者Rsync的方式获取分析结果，这样的方式带来以下几个问题：   （1）任务执行结束时间未知，用户必须自行编写代码不断地通过REST API请求分析结果，直至获取到分析结果为止，其中还需要处理分析结果过大，转而通过Rsync方式获取；   （2）受限于Hive SQL的表达能力，用户的计算逻辑无法完全表述，获取分析结果后需要再计算，然后入库；   （3）基于（1）、（2）的原因，用户编写大量复杂且冗余的代码处理上述逻辑。   为了改善上述情况，平台设计的解决方案如下：   （1）使用Spark替换Hive（MapReduce）分析任务，结合Hive SQL和Spark API两种方式，使用户的计算逻辑可以得到很好的表述；   （2）用户无需使用“Pull”的方式获取结果，转而使用“Post”的方式将分析结果存入自己的MySQL数据库中；   （3）提供基于Python的MySQL通用批量写入工具方便用户使用。   方案   MySQL批量写入工具需要解决以下几个问题：   （1）通用：不受限于具体数据格式的束缚；        用户需要将不同格式的数据写入不同的数据库/表，意味着数据库的主机名、端口、用户名、密码、数据库实例可以通过参数定制，不同的数据格式使用SQL语句表达，如“insert into students (c_number, c_name) values (%s, %s)”。   （2）多线程：使用多线程的方式，提高写入的吞吐量；        典型的“生产者——消费者”问题，用户需要将数据首先写入一个线程安全的共享队列中；“消费者”线程不断的从这个共享队列中获取数据并缓存，待缓存数据达到一定数目时，将这批数据一次性写出；然后继续上述过程。        除此之外，“消费者”线程需要能够“正常”结束。   （3）数据库连接控制：需要避免连接过多或者频繁连接带来的性能开销；        “消费者”线程需要能够重复利用一定数目的数据库连接，数据库连接由专门的连接池提供，工作流程如下：        a. 从连接池中获取数据库连接；      b. 通过a中的连接将数据批量写出并commit；      c. 将数据库连接归还给连接池。   （4）API：简单易用   我们将批量写入工具定义为一个“存储引擎”，考虑到MySQL的吞吐量可能在大数据量的写入下会成为一个瓶颈，后期会考虑扩展其它工具。因此定义一个基类表示抽象的“存储引擎”，并扩展出本文具体讨论的MySQL“存储引擎”：MySQLStorageEngine。       MySQLStorageEngine的初始化过程涉及以下六个方面：   （1）begin、end用于统计一次批量写入的耗时；   （2）接收用户定制的参数；      host：数据库主机名；      user：数据库登录用户名；      passwd：数据库登录密码；      db：数据库实例      port：数据库端口      charset：数据库字符编码      sql：写入数据时使用的SQL语句，如“insert into students (c_number, c_name) values (%s, %s)”      threads：写入线程数目；      bufferSize：写入线程内部的缓存区大小，亦即每一次“batch”的大小；      mincached：数据库连接池内部最小缓存连接数；      maxcached：数据库连接池内部最大缓存连接数；      maxconnections：数据库连接池所允许同时建立的最大连接数：目前与写入线程数目相同。   （3）构建共享队列queue；   （4）saveNum、storeNum用于统计用户写入总数及实际（成功）写入总数，考虑到多线程使用环境，分别构建相应的锁对象saveLock、storeLock；   （5）构建数据库连接池，这里使用的是DBUtils PooledDB；   （6）构建写入线程（多个）并启动。   至此，MySQLStorageEngine实例创建完毕，并且启动内部多个写入线程用于消费队列queue中数据。用户可通过实例方法save写入数据：     可见，save支持两种类型的数据，一种是Tuple，另一种是Tuple数组，它们都被保存至队列queue中，由写入线程负责处理。   用户写入完成之后，可以通过方法close关闭“存储引擎”，     需要注意的是，用户写入的数据实际是保存在队列queue中的，“用户写入完成”并不代表队列queue中的数据已全部被写入线程消费且完成入库，因此必须首先通知写入线程用户数据已全部写入完毕（requestStop），然后等待写入线程运行完毕（join），最后关闭数据库连接池。   写入线程由MySQLSaver实现，它的初始化过程特别简单：   （1）接收“存储引擎”实例engine； （2）定义实例变量stop，初始值为False，用于表示用户尚有数据写入；True表示用户写入结束。     MySQLSaver的工作流程如下：     （1）初始化缓存区buffer，用于保存从队列queue消费而来的数据；   （2）循环从队列queue获取数据，如果没有获取到数据，则执行（3）；如果获取到数据，则执行（4）；   （3）如果用户写入结束（stop值为True）而且队列中已经没有剩余数据，将缓冲区buffer中的数据一次性写入（__save），结束线程（break）；   （4）将（2）中获取到的数据存入缓存区，如果缓存区的大小达到数目限制，则将缓冲区buffer中的数据一次性写入（__save），继续（2）。   __save的工作流程如下：     （1）从连接池pool中获取数据库连接db并构建实例cursor； （2）写入buffer中的数据（executemany）并提交（commit）； （3）清空buffer、关闭实例cursor、将数据库连接db“归还”给连接池（close）。   使用示例如下：  

MySQL通用批量写入工具（Python）

标签：