Redshift COPY操作在SQLAlchemy中不起作用 [英] Redshift COPY operation doesn't work in SQLAlchemy

问题描述

我正在尝试在SQLAlchemy中进行Redshift COPY.

I'm trying to do a Redshift COPY in SQLAlchemy.

在我在psql中执行以下SQL时，以下SQL可以将对象正确地从S3存储桶中复制到Redshift表中:

The following SQL correctly copies objects from my S3 bucket into my Redshift table when I execute it in psql:

COPY posts FROM 's3://mybucket/the/key/prefix' 
WITH CREDENTIALS 'aws_access_key_id=myaccesskey;aws_secret_access_key=mysecretaccesskey' 
JSON AS 'auto';

我有几个名为

s3://mybucket/the/key/prefix.001.json
s3://mybucket/the/key/prefix.002.json   
etc.

我可以使用select count(*) from posts验证新行是否已添加到表中.

I can verify that the new rows were added to the table with select count(*) from posts.

但是，当我在SQLAlchemy中执行完全相同的SQL表达式时，执行完成没有错误，但是没有行添加到我的表中.

However, when I execute the exact same SQL expression in SQLAlchemy, execute completes without error, but no rows get added to my table.

session = get_redshift_session()
session.bind.execute("COPY posts FROM 's3://mybucket/the/key/prefix' WITH CREDENTIALS aws_access_key_id=myaccesskey;aws_secret_access_key=mysecretaccesskey'    JSON AS 'auto';")
session.commit()

我是否执行上述操作或

from sqlalchemy.sql import text 
session = get_redshift_session()
session.execute(text("COPY posts FROM 's3://mybucket/the/key/prefix' WITH CREDENTIALS aws_access_key_id=myaccesskey;aws_secret_access_key=mysecretaccesskey'    JSON AS 'auto';"))
session.commit()

推荐答案

我基本上遇到了相同的问题，但就我而言，问题更多:

I basically had the same problem, though in my case it was more:

engine = create_engine('...')
engine.execute(text("COPY posts FROM 's3://mybucket/the/key/prefix' WITH CREDENTIALS aws_access_key_id=myaccesskey;aws_secret_access_key=mysecretaccesskey'    JSON AS 'auto';"))

通过逐步遍历pdb，问题显然是缺少.commit()被调用的问题.我不知道为什么session.commit()在您的情况下不起作用(可能是会话丢失了跟踪"已发送的命令吗?)，因此它实际上可能无法解决您的问题.

By stepping through pdb, the problem was obviously the lack of a .commit() being invoked. I don't know why session.commit() is not working in your case (maybe the session "lost track" of the sent commands?) so it might not actually fix your problem.

无论如何，如在sqlalchemy文档中解释的

鉴于此要求，SQLAlchemy实现了自己的自动提交"功能，该功能在所有后端上都完全一致地工作.这是通过检测表示数据更改操作的语句来实现的，即INSERT，UPDATE，DELETE [...]如果该语句是纯文本语句并且未设置标志，则使用正则表达式来检测INSERT，UPDATE ，DELETE以及用于特定后端的各种其他命令.

Given this requirement, SQLAlchemy implements its own "autocommit" feature which works completely consistently across all backends. This is achieved by detecting statements which represent data-changing operations, i.e. INSERT, UPDATE, DELETE [...] If the statement is a text-only statement and the flag is not set, a regular expression is used to detect INSERT, UPDATE, DELETE, as well as a variety of other commands for a particular backend.

因此，有两种解决方案:

So, there are 2 solutions, either:

• text("COPY posts FROM 's3://mybucket/the/key/prefix' WITH CREDENTIALS aws_access_key_id=myaccesskey;aws_secret_access_key=mysecretaccesskey' JSON AS 'auto';").execution_options(autocommit=True).
• 或者，获取redshift方言的固定版本...我只是打开了PR 关于它

• text("COPY posts FROM 's3://mybucket/the/key/prefix' WITH CREDENTIALS aws_access_key_id=myaccesskey;aws_secret_access_key=mysecretaccesskey' JSON AS 'auto';").execution_options(autocommit=True).
• Or, get a fixed version of the redshift dialect... I just opened a PR about it

这篇关于Redshift COPY操作在SQLAlchemy中不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！