亚马逊红移：在数据库之间复制数据

Question

我想在Amazon Redshift数据库中复制数据。在此之前，我将数据从Redshift数据库复制到托管在EC2实例上的PostgreSQL以进行分析。我有一个Ruby脚本，使用dblink扩展程序完成此操作。

但现在由于PostgreSQL实例上的数据无法管理，我们决定将数据复制到同一Redshift集群上的单独数据库中。

问题来了！我发现dblink在Postgre 8.0（Redshift版本）中不可用。是否有办法在Redshift上使其正常工作？

我不想使用复制命令并添加S3层或数据管道。你看，它是同一集群，只是不同的数据库。

如果没有其他方法，我应该使用数据管道或将数据复制到S3，然后再复制回Redshift。或者最终本质上是相同的吗？

附言：感谢任何帮助/线索。我会努力工作的，只是需要指点一下。

- Sambhav Sharma

Answer 1

这似乎现在是可能的（自2016年6月3日起）。

请参见：https://blogs.aws.amazon.com/bigdata/post/Tx1GQ6WLEWVJ1OX/JOIN-Amazon-Redshift-AND-Amazon-RDS-PostgreSQL-WITH-dblink。

在上述文章中，他们给出了一个从redshift复制到另一个数据库的示例：

我将使用(postgres cluster)和(redshift cluster)进行注释以增加清晰度。

从Amazon Redshift复制数据到RDS PostgreSQL：

在(postgres cluster)中创建表的SQL：

CREATE TABLE sales_summary (
   saletime timestamp,
   sellerid int,
   sales decimal
);

[...]

下面的查询演示了如何使用dblink函数将数据插入到表中：

从(Redshift群集)复制到(Postgres群集)

INSERT INTO sales_summary
SELECT *
FROM dblink('foreign_server',$REDSHIFT$
    SELECT date_trunc('hours', saletime) AS ts, sellerid, sum(pricepaid) sales
    FROM sales 
    WHERE saletime >= '2008-01-01'
    AND saletime < '2008-02-01'
    GROUP BY ts, sellerid 
    ORDER BY sales 
$REDSHIFT$) AS t1 (saletime timestamp, sellerid int, sales decimal);

编辑：我已经在生产中使用它每天加载数百万行表格，而且我没有遇到任何与此相关的问题。因此，我建议将其作为从Redshift加载到RDS-Postgres的首选方法（相对于COPY或更糟的使用ORM在内存中加载数据）

- Benjamin Crouzier

---

这个问题是关于从一个Redshift数据库复制到同一Redshift群集上的Redshift数据库，但这个答案是关于在Postgres数据库间进行复制的。 - rado

@rado。是的，我没有仔细阅读。在这种情况下，UNLOAD then COPY 似乎是首选方式（尽管 OP 希望有所不同）。 - Benjamin Crouzier

Answer 2

在查询中无法同时从两个不同的数据库访问表。

您应该使用卸载命令将数据从一个数据库卸载到S3，然后使用复制命令将其加载到新的数据库表中。

- Sandesh Deshmane

---

这件事用AWS Data Pipeline怎么样？ - Sambhav Sharma

Answer 3

你可以使用Python/psycopg2/boto脚本化这个流水线。

• Python连接源PostgreSQL并打开数据管道进行读取。
• 数据流被压缩并使用多部分上传方式泵送到S3。
• 使用COPY命令从S3加载数据到Redshift中。

我在PostgreSQL-to-Redshift-Data-Loader中就是这样做的。

- Alex B

Answer 4

如果您的表位于两个不同的集群中，那么最好使用S3作为中间层。优点是，如果数据量很大，从S3复制数据到Redshift比从本地客户端复制数据到Redshift要快得多。这是因为在aws服务之间使用了并行处理。

所以步骤如下：

1）从SQL Workbench/j ->数据库资源管理器 ->表 ->右键单击 ->导出数据 ->类型（文本）和字段分隔符（逗号）下载数据文件（txt）。

2）将文件扩展名更改为CSV，并将其上传到指定文件夹的S3存储桶中。

3）现在登录到您的DB（通过SQL Workbench/j），并运行以下命令。

copy fact_transaction from ‘s3://bucket_name/folder_name’
CREDENTIALS ‘aws_access_key_id=XXXXXXX;aws_secret_access_key=YYYYYYYY’
delimiter ‘,’
csv
region ‘us-east-1’;

- Kanagavelu Sugumar

Answer 5

这是完整的 shell 脚本 - 将导出从 MySQL 改为 PostgreSQL

s3bucket=s3://yours3bucket/yourfolder
aws s3 ls $s3bucket/yourtable.txt.gz
aws s3 cp /tmp/yourtable.txt.gz $s3bucket/yourtable.txt.gz   
aws s3 ls $s3bucket/yourtable.txt.gz
if [ "$?" = "0" ];
 then
   echo "File sucessfully uploaded to S3 bucket."
 else
   echo "Upload to S3 bucket failed." 
fi

在将您的文件从PostgreSQL导出到Amazon S3之后，您只需执行以下操作：

psql -U$postgres_user -h $postgres_host -p $postgres_port $redshift_database -c 
"copy yourdatabase.yourtable from '$s3bucket/yourtable.txt.gz' credentials 
'aws_access_key_id=ajgaslgkshtasj; aws_secret_access_key=shfs/34fiethajthaslthksthask'
delimiter '|' gzip removequotes ESCAPE ACCEPTINVCHARS ACCEPTANYDATE;"

- MontyPython