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

问题描述

我在执行复制命令以将数据从 S3 加载到 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 执行此命令时，命令通过，但没有加载数据，也没有抛出错误.
尝试了以下两个选项(假设 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屋！