将 CSV 文件加载到 SQL 数据库¶
当需要将大于内存的 CSV 文件从 Python 中加载到 SQL 数据库时，许多人会首先想到使用 pandas。其工作流程大致如下：
>>> import sqlalchemy as sa
>>> import pandas as pd
>>> con = sa.create_engine('postgresql:///db')
>>> chunks = pd.read_csv('filename.csv', chunksize=100000)
>>> for chunk in chunks:
... chunk.to_sql(name='table', if_exist='append', con=con)
这里进行了不必要且开销很大的数据转换。首先，我们将 CSV 转换为 DataFrame 的迭代器，然后这些 DataFrame 被转换为与 SQLAlchemy 兼容的 Python 数据结构。接着，这些 Python 对象需要以与目标数据库兼容的方式进行序列化。不知不觉中，在转换数据和序列化 Python 数据结构上花费的时间比从磁盘读取数据的时间还要多。
使用已很好解决您问题的技术¶
将 CSV 文件加载到数据库是一个已解决的问题。而且它已经解决得很好。与其每次需要执行此操作时都自行编写加载器并浪费计算资源，我们应该使用我们所选数据库中的原生加载器。Odo 允许您通过一行代码完成此操作。
odo 如何实现原生数据库加载速度？¶
Odo 利用其支持的数据库的原生 CSV 加载能力。这些加载器速度极快。在加载大型数据集时，Odo 将胜过任何其他纯 Python 方法。以下是使用 odo 将整个纽约市出租车行程和票价合并数据集（约 33GB 文本）加载到 PostgreSQL、MySQL 和 SQLite3 的性能比较。我们的比较基准是 pandas。
注意： 我很高兴听到我可能没有利用到的其他优化措施。
耗时¶
CSV → PostgreSQL (22分 64秒)¶
- 读取速度：~50 MB/秒
- 写入速度：~50 MB/秒
postgresql 内置的 COPY 命令相当快。Odo 使用自定义的 SQLAlchemy 表达式为 COPY 命令生成代码。
In [1]: %time t = odo('all.csv', 'postgresql://::nyc')
CPU times: user 1.43 s, sys: 330 ms, total: 1.76 s
Wall time: 22min 46s
PostgreSQL → CSV (21分 32秒)¶
将数据从数据库中导出大约与加载数据所需的时间相同。
pg_bulkload 命令行工具 (13分 17秒)¶
- 读取速度：~50 MB/秒
- 写入速度：~50 MB/秒
一个名为 pg_bulkload 的特殊命令行工具专门用于将文件加载到 postgresql 表中。它通过禁用 WAL（预写式日志）和缓冲来实现加速。Odo 尚未（目前）使用它，因为安装需要多个步骤。关闭 WAL 还会对数据完整性产生影响。
$ time ./pg_bulkload nyc2.ctl < all.csv
NOTICE: BULK LOAD START
NOTICE: BULK LOAD END
1 Rows skipped.
173179759 Rows successfully loaded.
0 Rows not loaded due to parse errors.
0 Rows not loaded due to duplicate errors.
0 Rows replaced with new rows.
./pg_bulkload nyc2.ctl < all.csv 26.14s user 33.31s system 7% cpu 13:17.31 total
CSV → MySQL (20分 49秒)¶
In [1]: %time t = odo('all.csv', 'mysql+pymysql:///test::nyc')
CPU times: user 1.32 s, sys: 304 ms, total: 1.63 s
Wall time: 20min 49s
- 读取速度：~30 MB/秒
- 写入速度：~150 MB/秒
MySQL → CSV (17分 47秒)¶
In [1]: %time csv = odo('mysql+pymysql:///test::nyc', 'nyc.csv')
CPU times: user 1.03 s, sys: 259 ms, total: 1.29 s
Wall time: 17min 47s
- 读取速度：~30 MB/秒
- 写入速度：~30 MB/秒
与 PostgreSQL 类似，MySQL 将 CSV 文件写入与加载到表中所需的时间大致相同。
CSV → SQLite3 (57分 31秒*)¶
In [1]: dshape = discover(resource('all.csv'))
In [2]: %time t = odo('all.no.header.csv', 'sqlite:///db.db::nyc',
...: dshape=dshape)
CPU times: user 3.09 s, sys: 819 ms, total: 3.91 s
Wall time: 57min 31s
* 在这里，我们对一个数据集版本调用 discover，该版本的第一行包含标题，并且在 sqlite3 的 .import 命令中我们使用一个*不带*标题行的数据集版本。这有点像是作弊，但我只是想看看 sqlite3 的导入命令在没有创建不带标题行的新文件的额外开销下的加载时间。
SQLite3 → CSV (46分 43秒)¶
- 读取速度：~15 MB/秒
- 写入速度：~13 MB/秒
In [1]: %time t = odo('sqlite:///db.db::nyc', 'nyc.csv')
CPU times: user 2.7 s, sys: 841 ms, total: 3.55 s
Wall time: 46min 43s
Pandas¶
- 读取速度：~60 MB/秒
- 写入速度：~3-5 MB/秒
我实际上并没有完成这个计时，因为插入 1,000,000 行的单次迭代大约需要 4 分钟，而总共将有 174 次这样的迭代，使得总加载时间达到
.. code-block:: python
>>> 175 * 4 / 60.0
11.66...
11.66 **小时**！
将 1.75 亿行数据插入 postgresql 数据库需要将近 *12* 小时。下一个最慢的数据库 (SQLite) 仍然比将 CSV 文件读取到 pandas 中然后使用 to_pandas 方法将 DataFrame 发送到 PostgreSQL 快 **11 倍**。