如何快速将大量DataFrame数据批量插入Redshift数据库表中？

Question

我尝试了许多方法将数据从数据框插入数据库，但没有一种方法能够快速完成。在尝试插入数据时，我遇到了耗时两天或三天后出现超时错误的情况。

以下是我尝试过的几种方法：

第一种方法是将数据框转换为字典并尝试上传到数据库。以下是一个样本数据示例（请注意，我只展示了20行数据，但实际上我有大量的数据）：

conn = psycopg2.connect(
    host= 'redshift-####-dev.)00000.us-east-1.redshift.amazonaws.com',  
    database= '*****',
    user='****',
    password='*****',
    port= '5439'
)
print("成功连接到RedShift Dev")
cur = conn.cursor ()

df_dic =[{'case_id': 69370, 'column_name': 'subject', 'split_text': 'working', 'split_text_cnt': 1, 'load_ts': '2023-12-15'},
         {'case_id': 69370, 'column_name': 'subject', 'split_text': 'scenes', 'split_text_cnt': 1, 'load_ts': '2023-12-15'}, ...]

sql = "INSERT INTO odey.sfc_ca_sit_di (case_id,column_name,split_text,split_text_cnt,load_ts) VALUES (%(case_id)s,%(column_name)s,%(split_text)s,%(split_text_cnt)s,%(load_ts)s)"
cur.executemany(sql, df_dic)
conn.commit()

第二种方法是将数据框转换为元组，并执行查询语句，其中final_out是数据框：

sql = "INSERT INTO sey.sfdse_sp_di (case_id,column_name,split_text,split_text_cnt,load_ts) VALUES (%s,%s,%s,%s,%s)"

for row in final_out.values.tolist():
    cur.execute(sql, tuple(row))
    #print(tuple(row))
    conn.commit()

这两种方法都需要数天的时间来完成插入操作。

请问是否有人可以提供一种能更快地插入大量数据（大约60万条记录）的方法？

Oli · Answer

参见该问题。

亚马逊Redshift的官方文档明确指出，单行插入数据不是一个好的做法。

引用文档：

如果无法使用COPY命令且必须通过SQL插入语句插入数据，则应尽可能使用多行插入。当你每次仅插入一行或几行数据时，数据压缩效果不佳。

因此，建议将数据分批组织成多行插入的形式。需要注意的是，每个SQL命令所支持的最大大小限制为16MB，所以对于大容量数据插入可能需要拆分成多个批次进行。

尽管如此，最佳实践仍然是通过COPY命令来加载数据。COPY命令利用了Amazon Redshift的并行处理能力，可以从Amazon S3、DynamoDB表或其他来源高效并行地读取和加载大量数据。与逐行插入相比，使用COPY命令能显著提升数据导入速度和效率。

Benjamin Lindley · Answer

首先，这些代码模式对于少量数据是否有效？

根据您的描述，不确定您面临的是速度问题还是功能问题。我将两者都假设在内，并提供一些指导方向。

对于中等大小的数据加载从未完成：

我推测您的代码可能没有正确地与数据库通信。有几个可能性，大多与创建非常大的SQL语句有关。您的处理过程是通过SQL向Redshift插入数据，这种方法慢且生成大尺寸的SQL语句（不建议对超过几行的数据使用此方法）。

您可能具有过大的网络最大传输单元（MTU）。Redshift支持的最大网络MTU为1500，更大的尺寸可能导致“挂起”和错误。请参阅https://docs.aws.amazon.com/redshift/latest/mgmt/connecting-drop-issues.html。
Redshift存在SQL命令大小限制，若发出的命令超过该限制，则可能会导致错误。
各种可能导致连接到Redshift“挂起”的网络问题。

要确定是否存在这些问题，最简单的方法是在运行代码时观察Redshift控制台上的活动情况，查看查询是否正在运行。如果查询未运行而代码处于等待状态，则表明存在与数据库的通信问题。

我怀疑由于600K条记录对于Redshift来说并不算太大，因此可能是查询并未真正运行。如果网络问题导致了这个问题，可能存在相应的网络解决方案，但您最好放弃这种插入方法，转而采用下文所述的方法。如果确实是MTU设置不正确，请务必修复，因为错误的MTU设置可能会引发各种奇怪的错误。

对于中等大小的数据加载能够完成的情况：

通过SQL中的INSERT INTO ... VALUES方式插入数据很可能是将数据导入Redshift最慢的方法。这有多种原因，包括数据值与查询一同编译以及单个数据连接通过领导节点路由。

您需要将数据写入S3作为多个大文件（1GB以上）。为了让Redshift以最快的速度摄取数据，集群的每个切片应当能够与其他所有切片并行摄取文件。另外，S3处理小文件效率较低，因此拥有至少100MB大小（更理想的是大于1GB）的文件将大大提高S3的效率。

同时，您还需要高效地将数据转移到S3。为此，需要了解DBeaver系统的配置情况。当您需要将数据移动到S3时，同样的目标适用——以大约1GB左右的大小并行传输多个文件，以便S3高效处理。相比网络传输速度，本地系统很可能能快速生成本地文件，因此需要解决的是这个网络传输速度问题。一旦本地生成了文件，就通过并行网络连接将其上传至S3。通常情况下，4-6个并行连接可能是最优选择，但这取决于您DBeaver系统的具体条件。