优化Python处理CSV表格为父表和EAV子表

Question

对于用python将大型csv文件处理成多个postgresql表，在线上也有一些类似的问题。然而，似乎没有一个能解决关于优化数据库读/写和系统内存/处理的几个问题。你知道吗

假设我有一行产品数据，如下所示：

名称、sku、日期时间、十进制、十进制、十进制、十进制、十进制、十进制

如果名称和sku存储在一个表（父表）中，则每个十进制字段都存储在一个子EAV表中，该子EAV表基本上包含十进制、父表\ id和日期时间。你知道吗

假设我在一个csv文件中有20000行这样的行，所以我最终将它们分块。现在，我从2000行中取出一大块，一行一行地循环。每次迭代都检查产品是否存在，如果不存在，则创建产品，检索父表的\u id。然后，我有一个为子表生成的带有十进制值的insert语句的大列表。如果用户选择仅覆盖未修改的十进制值，则在添加到插入列表之前，还将检查每个十进制值是否已修改。你知道吗

在这个例子中，如果我遇到最坏的情况，我最终会执行160000次数据库读取和10-20010次写入。我还将在内存中的一个列表中为每个块存储多达12000条insert语句（但是，这将只有一个列表，所以这部分没有那么糟糕）。你知道吗

我的主要问题是：

• 如何优化它以更快、使用更少的数据库操作（因为这也会影响网络流量）以及使用更少的处理和内存？如果可以节省其他两种优化，我也希望处理速度慢一些，因为在AWS之类的软件中，这些优化转换为服务器/数据库处理定价时会花费更多的钱。你知道吗

一些子问题是：

• 有没有一种方法我可以合并所有产品读/写操作，并在执行小数之前在文件中替换它们？你知道吗
• 我应该做一个较小的块大小，以帮助记忆？你知道吗
• 我应该使用线程还是保持线性？你知道吗
• 我能让它构建一个更高效的sql查询吗？如果不存在，产品会创建这个sql查询并引用内联，从而将一些处理移到sql而不是python中？你知道吗
• 我是否可以优化子insert语句，使其比数千条insert-INTO语句做得更好？你知道吗

Answer 1

一个有趣的问题，但很难准确回答，因为有很多 定义可能适用或不适用的最佳解决方案的变量。你知道吗

以下是一种基于以下假设的方法-

• 您不需要数据库代码是可移植的。你知道吗
• csv是用一个头来构造的，或者至少是属性名 已知和固定。你知道吗
• 产品表中的sku（或名称/sku组合）具有唯一的约束。你知道吗
• 同样，EAV表在product_id上有一个唯一的约束，并且 attr_name
  • 推论-您没有指定，但我还假设EAV表有一个字段 属性名。你知道吗

这个过程可以归结为-

• 以最快的路径将数据加载到数据库中
• 在加载过程中或加载之后，将csv从表格结构取消到EAV结构
• “向上插入”结果记录-如果存在则更新，否则插入。你知道吗

接近-

所有这些背景，考虑到一个类似的问题，这里是我将采取的方法。你知道吗

• 创建镜像最终目标的临时表，但不带pks、类型或约束
  • 数据库会话结束时，临时表将被删除
• 一次将.csv直接加载到temp表中；每行执行两次SQL
  • 一个用于产品
  • 一个用于EAV，使用'multi-value'insert-insert into tmp_eav (sku, attr_name, attr_value) values (%s, %s), (%s, %s)...。你知道吗
  • psycopg2有一个自定义方法来为您执行此操作：http://initd.org/psycopg/docs/extras.html#psycopg2.extras.execute_values
• 使用类似insert into product (name, sku) select name, sku from tmp_product on conflict (sku) do nothing的语句，从tmp表中选择以向上插入最终表
  • 这需要PostgreSQL 9.5+。你知道吗
  • 对于用户可选择的基于csv更新字段的要求，可以将do nothing更改为do update set col = excluded.col。excluded是冲突的输入行

替代方法-

• 根据csv的结构创建temp表（假设 有足够的元数据在每次运行csv结构的或时执行此操作 固定的并且可以一致地转换为表）
• 使用COPY命令（psycopg2支持）将csv加载到数据库中 通过cursor.copy_from方法，将csv作为文件对象传入）。 这将比用Python编写的任何东西都要快
  • 警告：如果csv是非常可靠的（相同数量的col在 每一行）和temp表是非常松散的w/nulls，所有字符串w/no 类型强制。你知道吗
• 您可以使用结合了 为每列选择行转置。你例子中的6位小数 应该是可控的。你知道吗

例如：

select sku, 'foo' as attr_name, foo as attr_value from tmp_csv union all
select sku, 'bar' as attr_name, bar as attr_value from tmp_csv union all
...
order by sku;

此解决方案涉及您感兴趣的几个方面：

• Python应用程序内存保持平坦
• 网络I/O仅限于将.csv输入数据库并发出 正确的后续sql语句

一个关于结束的小建议-

• 最佳和“足够好”几乎不是一回事
• 只有在非常特殊的情况下才需要优化
• 所以，要以“足够好”为目标，但要明确“足够好”的含义- i、 例如，选择一个或两个措施
• 迭代，一次求解一个变量。根据我的经验，第一个障碍（比如说，“端到端处理时间少于 X秒）通常就足够了。你知道吗