fast_executemany替代psycopg2

Question

我有一个红移服务器，它是我通过psycopg2启动的(注意，公司服务器上不支持ODBC，所以我不能使用pyodbc)。

目前，它需要花费10分钟以上的时间通过pd.to_sql()编写30-35k行，后者从dataframe写入Redshift DB。因此，作为一种解决方案，我以csv的形式下载DF，将文件推送到S3，然后使用copy写入DB。

按照executemany of pyODBC，fast_executemany解决方案应该是完美的--但是在psycopg2中不支持这一点。我还按照https://github.com/d6t/d6tstack/blob/master/examples-sql.ipynb找到了https://github.com/d6t/d6tstack/blob/master/examples-sql.ipynb，但是pd_to_psql不适用于Redshift，只有Postgresql (不能copy... from stdin)

我的案子有别的选择吗？

这是我的密码：

import sqlalchemy as sa

DATABASE = ""
USER = ""
PASSWORD = ""
HOST = "...us-east-1.redshift.amazonaws.com"
PORT = "5439"
SCHEMA = "public" 

server = "redshift+psycopg2://%s:%s@%s:%s/%s" % (USER,PASSWORD,HOST,str(PORT),DATABASE)
engine = sa.create_engine(server)
conn = engine.raw_connection()

with conn.cursor() as cur:
    cur.execute('truncate table_name')

df.to_sql('table_name', engine, index=False, if_exists='append') 

Answer 1

如果您无法使用 from S3，并且必须依赖DML，则可以尝试将use_batch_mode=True传递给create_engine()。

engine = create_engine('theurl', use_batch_mode=True)

在这台机器上，向Redshift集群插入500行的简单插入显示了合理的改进，启用了批处理模式：

In [31]: df = pd.DataFrame({'batchno': range(500)})

In [32]: %time df.to_sql('batch', engine, index=False, if_exists='append')
CPU times: user 87.8 ms, sys: 57.6 ms, total: 145 ms
Wall time: 1min 6s

In [33]: %time df.to_sql('batch', bm_engine, index=False, if_exists='append')
CPU times: user 10.3 ms, sys: 4.66 ms, total: 15 ms
Wall time: 9.96 s

请注意，如果底层DBMS支持Pandas版本0.23.0和0.24.0及更高版本，则使用批处理模式不会有好处，因为它们使用多值insert而不是executemany。使用多值insert应该在吞吐量方面提供一些类似的改进，因为发出的查询较少。