一、COPY 命令作用

COPY 是 Redshift 最常用的批量加载命令，支持从多种数据源将数据并行导入表中：

• Amazon S3

• Amazon DynamoDB

• Amazon EMR/HDFS

• 远程主机（通过 SSH / Amazon EC2）

它比 INSERT 快很多，因为 COPY 会并行导入数据。

---

二、基本语法

COPY table_name FROM 'data_source' CREDENTIALS 'aws_access_key_id=xxx;aws_secret_access_key=yyy' [ FORMAT AS JSON 'jsonpaths_file' | CSV | PARQUET | AVRO | ORC ] [ REGION 'us-east-1' ] [ ... 更多选项 ... ];

---

三、常见场景

1. 从 S3 加载 CSV

COPY my_table FROM 's3://my-bucket/data/' CREDENTIALS 'aws_iam_role=arn:aws:iam::123456789012:role/MyRedshiftRole' CSV IGNOREHEADER 1;

• aws_iam_role：推荐使用 IAM 角色，而不是 Access Key。

• IGNOREHEADER 1：跳过 CSV 第一行标题。

---

2. 从 S3 加载 JSON

COPY my_table FROM 's3://my-bucket/json_data/' CREDENTIALS 'aws_iam_role=arn:aws:iam::123456789012:role/MyRedshiftRole' FORMAT AS JSON 'auto';

• auto：自动解析 JSON。

• 如果结构复杂，可以指定 JSONPaths 文件。

---

3. 从 DynamoDB 加载

COPY my_table FROM 'dynamodb://my-dynamodb-table' CREDENTIALS 'aws_iam_role=arn:aws:iam::123456789012:role/MyRedshiftRole' READRATIO 50;

• READRATIO 控制读取吞吐量比例。

---

四、最佳实践

1.copy feedback.product_feedback
from 'S3URI'
iam_role 'IAMARN'
json 'auto ignorecase'; #自动根据列名匹配 JSON 字段（忽略大小写）

COPY high_diamond_ranked_10min FROM 's3://games-easy-storage-redshift-899772185586-us-east-1/high_diamond_ranked_10min.csv'
iam_role 'arn:aws:iam::899772185586:role/JamRedshiftRole'
CSV
IGNOREHEADER 1 #忽略第一行
DATEFORMAT 'auto'; #自动识别 CSV 文件中的日期格式

2.CREATE TABLE sailors (
s_id bigint,
s_name varchar(25),
s_address varchar(40),
s_phone varchar(15),
s_acctbal numeric(12,2),
s_segment varchar(10),
s_dietrestrictions varchar(20),
s_onboard boolean
);
copy sailors from 's3://redshift-demos/data/gamejam/sailors/'
iam_role 'replace with arn of Redshiftgamesrole from output properties'
delimiter '|'
region 'us-east-1';
CREATE USER cook PASSWORD 'Welcome123';
CREATE USER cuddy PASSWORD 'Welcome123';
CREATE USER cashking PASSWORD 'Welcome123';

CREATE ROLE captain;
CREATE ROLE crew;
CREATE ROLE finance;
GRANT ROLE captain TO cook;
GRANT ROLE crew TO cuddy;
GRANT ROLE finance TO cashking;
select count(*) from sailors
where s_onboard='true'
and s_segment='DIAMOND'
GRANT SELECT ON sailors TO ROLE captain;
GRANT SELECT(s_name,s_segment,s_dietrestrictions) ON sailors TO ROLE crew;
GRANT SELECT(s_name,s_address,s_acctbal) ON sailors TO ROLE finance;
CREATE RLS POLICY show_all_sailors
USING ( true )

CREATE RLS POLICY show_only_current_sailors
WITH ( s_onboard BOOLEAN )
USING ( s_onboard = true )
ATTACH RLS POLICY show_all_sailors
ON sailors
TO ROLE captain;

ATTACH RLS POLICY show_only_current_sailors
ON sailors
TO ROLE crew;

ATTACH RLS POLICY show_only_current_sailors
ON sailors
TO ROLE finance;
ALTER TABLE sailors ROW LEVEL SECURITY on;
SET SESSION AUTHORIZATION 'cuddy';
select count(*) from sailors;

五、在 Lambda 或 Glue 中触发 COPY

如果你要在 Lambda 或 Glue Job 中执行 COPY，可以：

• 使用 psycopg2 (Python) 连接 Redshift 执行 SQL。

• 使用 boto3 redshift-data API 执行 SQL。

示例：boto3 调用 Redshift Data API 执行 COPY

import boto3 client = boto3.client('redshift-data') response = client.execute_statement( ClusterIdentifier='my-redshift-cluster', Database='dev', DbUser='awsuser', Sql=""" COPY my_table FROM 's3://my-bucket/data/' IAM_ROLE 'arn:aws:iam::123456789012:role/MyRedshiftRole' CSV IGNOREHEADER 1; """ ) print(response)