如何将CSV数据文件复制到Amazon RedShift? [英] How to copy csv data file to Amazon RedShift?

问题描述

我正在尝试将一些MySQL表迁移到Amazon Redshift，但是遇到了一些问题.

I'm trying to migrating some MySQL tables to Amazon Redshift, but met some problems.

步骤很简单:1.将MySQL表转储到csv文件中2.将csv文件上传到S33.将数据文件复制到RedShift

The steps are simple: 1. Dump the MySQL table to a csv file 2. Upload the csv file to S3 3. Copy the data file to RedShift

在步骤3中发生错误:

SQL命令为:

从's3://ciphor/TABLE_A.csv'复制TABLE_A凭据'aws_access_key_id = xxxx; aws_secret_access_key = xxxx'分隔符'，csv;

copy TABLE_A from 's3://ciphor/TABLE_A.csv' CREDENTIALS 'aws_access_key_id=xxxx;aws_secret_access_key=xxxx' delimiter ',' csv;

错误信息:

执行SQL命令时发生错误:从以下位置复制TABLE_A's3://ciphor/TABLE_A.csv'凭据'aws_access_key_id = xxxx; aws_secret_access_key = xxxx错误:复制CSV为不支持[SQL State = 0A000]执行时间:0.53s 1条语句失败.

An error occurred when executing the SQL command: copy TABLE_A from 's3://ciphor/TABLE_A.csv' CREDENTIALS 'aws_access_key_id=xxxx;aws_secret_access_key=xxxx ERROR: COPY CSV is not supported [SQL State=0A000] Execution time: 0.53s 1 statement(s) failed.

我不知道csv文件的格式是否有任何限制，例如定界符和引号，我无法在文档中找到它.

I don't know if there's any limitations on the format of the csv file, say the delimiters and quotes, I cannot find it in documents.

任何人都可以帮忙吗?

推荐答案

使用以下方法终于解决了问题:

The problem is finally resolved by using:

从's3://ciphor/TABLE_A.csv'复制TABLE_A凭据'aws_access_key_id = xxxx; aws_secret_access_key = xxxx'分隔符'，'删除引号；

copy TABLE_A from 's3://ciphor/TABLE_A.csv' CREDENTIALS 'aws_access_key_id=xxxx;aws_secret_access_key=xxxx' delimiter ',' removequotes;

更多信息，请参见 http://docs.aws.amazon.com/redshift/latest/dg/r_COPY.html

这篇关于如何将CSV数据文件复制到Amazon RedShift?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！