使用python和psycopg2将数据从S3复制到AWS Redshift [英] Copying data from S3 to AWS redshift using python and psycopg2

问题描述

执行复制命令以将数据从S3加载到python的Amazon Redshift时遇到问题.
我有以下复制命令:

I'm having issues executing the copy command to load data from S3 to Amazon's Redshift from python.
I have the following copy command:

copy moves from 's3://<my_bucket_name>/moves_data/2013-03-24/18/moves'
credentials 'aws_access_key_id=<key_id>;aws_secret_access_key=<key_secret>'
removequotes
delimiter ',';

当我使用SQL Workbench/j执行此命令时，一切正常，但是，当我尝试使用python和psycopg2执行此命令时，该命令通过了OK，但未加载任何数据且未引发任何错误.
尝试了以下两个选项(假设psycopg2连接正常，因为它可以):

When I execute this command using SQL Workbench/j everything works as expected, however when I try to execute this with python and psycopg2 the command pass OK but no data is loaded and no error is thrown.
tried the following two options (assume psycopg2 connection is OK because it is):

cursor.execute(copy_command)  
cursor.copy_expert(copy_command, sys.stdout)

两次通过均未发出警告，但尚未加载数据

both pass with no warning yet data isn't loaded

想法?

谢谢

推荐答案

我已经成功使用了此精确设置(psycopg2 + redshift + COPY).之后您承诺了吗? SQL Workbench默认情况下自动提交，而psycopg2默认情况下打开事务，因此直到在连接上调用commit()，数据才可见.

I have used this exact setup (psycopg2 + redshift + COPY) successfully. Did you commit afterwards? SQL Workbench defaults to auto-commit while psycopg2 defaults to opening a transaction, so the data won't be visible until you call commit() on your connection.

完整的工作流程是:

conn = psycopg2.connect(...)
cur = conn.cursor()
cur.execute("COPY...")
conn.commit()

我不认为copy_expert()或任何cursor.copy_ *命令可与Redshift一起使用.

I don't believe that copy_expert() or any of the cursor.copy_* commands work with Redshift.

这篇关于使用python和psycopg2将数据从S3复制到AWS Redshift的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！