将数据从 Amazon S3 存储桶中的文本文件加载到 Amazon Aurora MySQL 数据库集群
您可以使用 LOAD DATA FROM S3
或 LOAD XML FROM S3
语句从 Amazon S3 存储桶上存储的文件中加载数据。在 Aurora MySQL 中,文件首先存储在本地磁盘上,然后导入到数据库。完成向数据库导入后,将删除本地文件。
注意
对于 Aurora Serverless v1,不支持将数据从文本文件加载到表中。Aurora Serverless v2 支持此功能。
目录
为 Aurora 授予 Amazon S3 的访问权限
您必须先为 Aurora MySQL 数据库集群授予 Amazon S3 的访问权限,然后才能从 Amazon S3 存储桶中加载数据。
为 Amazon S3 授予 Aurora MySQL 的访问权限
-
创建一个 AWS Identity and Access Management(IAM)策略,以提供允许 Aurora MySQL 数据库集群访问 Amazon S3 存储桶和对象的权限。有关说明,请参阅创建 IAM 策略以访问 Amazon S3 资源。
注意
在 Aurora MySQL 版本 3.05 及更高版本中,您可以加载使用客户管理的 AWS KMS keys进行加密的对象。为此,请在您的 IAM policy 中包含
kms:Decrypt
权限。有关更多信息,请参阅 创建 IAM 策略以访问 AWS KMS 资源。您不需要此权限即可使用 AWS 托管式密钥或 Amazon S3 管理密钥(SSE-S3)来加载对象。
-
创建一个 IAM 角色,并将您在 创建 IAM 策略以访问 Amazon S3 资源 中创建的 IAM 策略附加到新的 IAM 角色。有关说明,请参阅创建 IAM 角色以允许 Amazon Aurora 访问AWS服务。
-
确保数据库集群使用的是自定义数据库集群参数组。
有关创建自定义数据库集群参数组的更多信息,请参阅在 Amazon Aurora 中创建数据库集群参数组。
-
对于 Aurora MySQL 版本 2,将
aurora_load_from_s3_role
或aws_default_s3_role
数据库集群参数设置为新 IAM 角色的 Amazon 资源名称(ARN)。如果没有为aurora_load_from_s3_role
指定 IAM 角色,则 Aurora 使用在aws_default_s3_role
中指定的 IAM 角色。对于 Aurora MySQL 版本 3,使用
aws_default_s3_role
。如果集群是 Aurora 全局数据库的一部分,则为该全局数据库中的每个 Aurora 集群设置此参数。虽然只有 Aurora 全局数据库中的主集群可以加载数据,但故障转移机制可能会提升另一个集群,使其成为主集群。
有关数据库集群参数的更多信息,请参阅Amazon Aurora 数据库集群和数据库实例参数。
-
要允许 Aurora MySQL 数据库集群中的数据库用户访问 Amazon S3,请将您在创建 IAM 角色以允许 Amazon Aurora 访问AWS服务中创建的角色与该数据库集群关联。对于 Aurora 全局数据库,将此角色与该全局数据库中的每个 Aurora 集群关联。有关将 IAM 角色与数据库集群关联的信息,请参阅将 IAM 角色与 Amazon Aurora MySQL 数据库集群关联。
-
配置 Aurora MySQL 数据库集群以允许建立到 Amazon S3 的出站连接。有关说明,请参阅启用从 Amazon Aurora 到其它 AWS 服务的网络通信。
如果您的 数据库集群不可公开访问且位于 VPC 公有子网中,则它是私有的。您可以创建一个 S3 网关端点来访问您的 S3 桶。有关更多信息,请参阅用于 Amazon S3 的网关端点。
对于 Aurora 全局数据库,为该全局数据库中的每个 Aurora 集群启用出站连接。
授予权限以在 Amazon Aurora MySQL 中加载数据
发出 LOAD DATA FROM S3
或 LOAD XML FROM S3
语句的数据库用户必须具有特定角色或权限才能发出任一语句。在 Aurora MySQL 版本 3 中,您可以授予 AWS_LOAD_S3_ACCESS
角色。在 Aurora MySQL 版本 2 中,您可以授予 LOAD FROM S3
权限。预设情况下,将为数据库集群的管理用户授予适当的角色或权限。您可以使用以下语句之一向另一个用户授予权限。
对 Aurora MySQL 版本 3 使用以下语句:
GRANT AWS_LOAD_S3_ACCESS TO '
user
'@'domain-or-ip-address
'
提示
当您使用 Aurora MySQL 版本 3 中的角色方法时,还可以通过使用 SET ROLE
或 role_name
SET ROLE
ALL
语句来激活角色。如果您不熟悉 MySQL 8.0 角色系统,可以在基于角色的权限模型中了解详情。有关更多详细信息,请参阅《MySQL 参考手册》中的 Using roles
这仅适用于当前的活动会话。当您重新连接时,必须再次运行 SET ROLE
语句来授予权限。有关更多信息,请参阅《MySQL 参考手册》中的 SET ROLE 语句
可以使用 activate_all_roles_on_login
数据库集群参数,在用户连接到数据库实例时自动激活所有角色。设置此参数后,您通常不必显式调用 SET ROLE
语句,即可激活角色。有关更多信息,请参阅《MySQL 参考手册》中的 activate_all_roles_on_login
但是,当存储过程由其他用户调用时,您必须在该存储过程的开头显式调用 SET ROLE ALL
才能激活该角色。
对 Aurora MySQL 版本 2 使用以下语句:
GRANT LOAD FROM S3 ON *.* TO '
user
'@'domain-or-ip-address
'
AWS_LOAD_S3_ACCESS
角色和 LOAD FROM S3
权限特定于 Amazon Aurora,而不适用于外部 MySQL 数据库或 RDS for MySQL 数据库实例。如果您在作为复制源的 Aurora 数据库集群和作为复制客户端的 MySQL 数据库之间设置了复制,则角色或权限的 GRANT
语句将导致复制停止并出现错误。您可以安全地跳过该错误,继续复制。要跳过 RDS for MySQL 实例上的错误,请使用 mysql_rds_skip_repl_error 过程。要跳过外部 MySQL 数据库上的错误,请使用 slave_skip_errors
注意
数据库用户必须对正在向其加载数据的数据库具有 INSERT
权限。
指定 Amazon S3 桶的路径(URI)
用于指定 Amazon S3 桶中文件路径(URI)的语法如下所示。
s3
-region
://amzn-s3-demo-bucket
/file-name-or-prefix
路径包括以下值:
-
region
(可选)– 包含作为加载来源的 Amazon S3 存储桶的AWS区域。该值为可选项。如果您没有指定region
值,则 Aurora 从与您的数据库集群位于相同区域的 Amazon S3 中加载您的文件。 -
bucket-name
– 包含要加载的数据的 Amazon S3 存储桶的名称。支持指定虚拟文件夹路径的对象前缀。 -
file-name-or-prefix
– Amazon S3 文本文件或 XML 文件的名称,或指定要加载的一个或多个文本或 XML 文件的前缀。您还可以指定一个清单文件以指定一个或多个要加载的文本文件。有关使用清单文件从 Amazon S3 中加载文本文件的更多信息,请参阅使用清单指定要加载的数据文件。
复制 S3 桶中文件的 URI
登录到AWS Management Console,然后通过以下网址打开 Amazon S3 控制台:https://console.aws.amazon.com/s3/
。 -
在导航窗格中,选择桶,然后选择要复制其 URI 的桶。
-
选择要从 S3 加载的前缀或文件。
-
选择复制 S3 URI。
LOAD DATA FROM S3
您可以使用 LOAD DATA FROM S3
语句从 MySQL LOAD DATA INFILE
注意
确保您的 Aurora MySQL 数据库集群允许与 S3 建立出站连接。有关更多信息,请参阅 启用从 Amazon Aurora 到其它 AWS 服务的网络通信。
语法
LOAD DATA [FROM] S3 [FILE | PREFIX | MANIFEST] '
S3-URI
' [REPLACE | IGNORE] INTO TABLEtbl_name
[PARTITION (partition_name
,...)] [CHARACTER SETcharset_name
] [{FIELDS | COLUMNS} [TERMINATED BY 'string
'] [[OPTIONALLY] ENCLOSED BY 'char
'] [ESCAPED BY 'char
'] ] [LINES [STARTING BY 'string
'] [TERMINATED BY 'string
'] ] [IGNOREnumber
{LINES | ROWS}] [(col_name_or_user_var
,...)] [SETcol_name
=expr
,...]
注意
在 Aurora MySQL 版本 3.05 及更高版本中,关键字 FROM
是可选的。
参数
LOAD DATA FROM S3
语句使用以下必需和可选参数。在 MySQL 文档的 LOAD DATA 语句
- FILE | PREFIX | MANIFEST
-
指定是从单个文件、与给定前缀匹配的所有文件还是从指定清单上的所有文件中加载数据。默认值为
FILE
。 - S3-URI
-
指定要加载的文本或清单文件的 URI,或指定要使用的 Amazon S3 前缀。使用指定 Amazon S3 桶的路径(URI)中描述的语法指定 URI。
- REPLACE | IGNORE
-
确定在输入行与数据库表中现有行具有相同唯一键值时采取什么操作。
-
如果您希望使用输入行替换表中的现有行,则指定
REPLACE
。 -
如果您希望放弃输入行,则指定
IGNORE
。
-
- INTO TABLE
-
指定将输入行加载到的数据库表的名称。
- PARTITION
-
要求将所有输入行插入到由指定分区名称列表(以逗号分隔)指定的分区中。如果输入行无法插入到指定分区之一,则语句失败并返回错误。
- CHARACTER SET
-
指定输入文件中的数据的字符集。
- FIELDS | COLUMNS
-
指定如何分隔输入文件中的字段或列。默认情况下使用制表符分隔字段。
- LINES
-
指定如何分隔输入文件中的行。默认情况下,行由换行符 (
'\n'
) 分隔。 - IGNORE
number
LINES | ROWS -
指定忽略输入文件开头的特定行数。例如,您可以使用
IGNORE 1 LINES
跳过包含列名的初始标题行,或者使用IGNORE 2 ROWS
跳过输入文件的前两行数据。如果您还使用PREFIX
,则IGNORE
会跳过第一个输入文件开头的特定数量的行。 - col_name_or_user_var, ...
-
指定一个或多个列名的逗号分隔列表,或者指定用户变量以按照名称指定要加载的列。用于此目的的用户变量的名称必须与文本文件中的元素名称匹配,前缀为 @。您可以采用用户变量来存储对应的字段值,以便在后面重复使用。
例如,以下语句将输入文件的第一列加载到
table1
的第一列,并将table_column2
中的table1
列值设置为第二列输入值除以 100。LOAD DATA FROM S3 's3://
amzn-s3-demo-bucket
/data.txt' INTO TABLE table1 (column1, @var1) SET table_column2 = @var1/100; - SET
-
指定以逗号分隔的分配操作列表,这些操作将表中各列的值设置为输入文件中未包含的值。
例如,以下语句将
table1
的前两列设置为输入文件中前两列的值,然后将column3
中table1
的值设置为当前时间戳。LOAD DATA FROM S3 's3://
amzn-s3-demo-bucket
/data.txt' INTO TABLE table1 (column1, column2) SET column3 = CURRENT_TIMESTAMP;您可以在
SET
分配的右侧使用子查询。如果子查询返回要分配到列的值,则您只能使用标量子查询。此外,您还可以使用子查询来从所加载的表中选择。
如果从 Amazon S3 桶中加载数据,则无法使用 LOAD DATA FROM S3
语句的 LOCAL
关键字。
使用清单指定要加载的数据文件
可以将 LOAD DATA FROM S3
语句与 MANIFEST
关键字配合使用来指定 JSON 格式的清单文件,此文件列出了要加载到数据库集群中的表的文本文件。
以下 JSON 架构描述了清单文件的格式和内容。
{ "$schema": "http://json-schema.org/draft-04/schema#", "additionalProperties": false, "definitions": {}, "id": "Aurora_LoadFromS3_Manifest", "properties": { "entries": { "additionalItems": false, "id": "/properties/entries", "items": { "additionalProperties": false, "id": "/properties/entries/items", "properties": { "mandatory": { "default": "false", "id": "/properties/entries/items/properties/mandatory", "type": "boolean" }, "url": { "id": "/properties/entries/items/properties/url", "maxLength": 1024, "minLength": 1, "type": "string" } }, "required": [ "url" ], "type": "object" }, "type": "array", "uniqueItems": true } }, "required": [ "entries" ], "type": "object" }
清单中的每个 url
必须指定带存储桶名称和文件的完整对象路径 (而不仅仅是前缀) 的 URL。您可以使用清单来加载来自不同存储桶或不同区域的文件,或加载未共享相同前缀的文件。如果 URL 中未指定区域,将使用目标 Aurora 数据库集群的区域。以下示例显示一个清单文件,此文件加载来自不同存储桶的四个文件。
{ "entries": [ { "url":"s3://aurora-bucket/2013-10-04-customerdata", "mandatory":true }, { "url":"s3-us-west-2://aurora-bucket-usw2/2013-10-05-customerdata", "mandatory":true }, { "url":"s3://aurora-bucket/2013-10-04-customerdata", "mandatory":false }, { "url":"s3://aurora-bucket/2013-10-05-customerdata" } ] }
可选的 mandatory
标志指定 LOAD DATA FROM S3
是否应在找不到文件时返回错误。mandatory
标志默认为 false
。不管如何设置 mandatory
,如果找不到文件,则 LOAD DATA FROM S3
将终止。
清单文件可具有任何扩展名。以下示例使用前一个示例中名为 LOAD DATA FROM S3
的清单以运行 customer.manifest
语句。
LOAD DATA FROM S3 MANIFEST 's3-us-west-2://aurora-bucket/customer.manifest' INTO TABLE CUSTOMER FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (ID, FIRSTNAME, LASTNAME, EMAIL);
该语句完成后,将为每个成功加载的文件向 aurora_s3_load_history
表中写入一个条目。
使用 aurora_s3_load_history 表验证已加载的文件
每个成功的 LOAD DATA FROM S3
语句都会使用一个条目为每个已加载的文件更新 aurora_s3_load_history
架构中的 mysql
表。
运行 LOAD DATA FROM S3
语句后,您可以通过查询 aurora_s3_load_history
表来确认已加载的文件。要查看通过迭代一次该语句而加载的文件,请使用 WHERE
子句为该语句中使用的清单文件筛选 Amazon S3 URI 上的记录。如果您之前已使用相同的清单文件,请使用 timestamp
字段筛选结果。
select * from mysql.aurora_s3_load_history where load_prefix = '
S3_URI
';
下表介绍了 aurora_s3_load_history
表中的字段。
字段 | 描述 |
---|---|
|
加载语句中指定的 URI。此 URI 可以映射到以下任一项:
|
|
使用 |
|
|
|
已加载文件的大小 (以字节为单位)。 |
|
|
示例
以下语句从与 Aurora 数据库集群位于相同区域的 Amazon S3 存储桶中加载数据。该语句读取 amzn-s3-demo-bucket
Amazon S3 存储桶中文件 customerdata.txt
的数据(以逗号分隔),然后将数据加载到表 store-schema.customer-table
中。
LOAD DATA FROM S3 's3://
amzn-s3-demo-bucket
/customerdata.csv' INTO TABLE store-schema.customer-table FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (ID, FIRSTNAME, LASTNAME, ADDRESS, EMAIL, PHONE);
以下语句从与 Aurora 数据库集群位于不同区域的 Amazon S3 存储桶中加载数据。该语句从 us-west-2
区域上的 amzn-s3-demo-bucket
Amazon S3 存储桶中,读取与 employee-data
对象前缀匹配的所有文件中的数据(以逗号分隔),然后将数据加载到 employees
表中。
LOAD DATA FROM S3 PREFIX 's3-us-west-2://
amzn-s3-demo-bucket
/employee_data' INTO TABLE employees FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (ID, FIRSTNAME, LASTNAME, EMAIL, SALARY);
以下语句将数据从名为 q1_sales.json 的 JSON 清单文件中指定的文件加载到 sales
表中。
LOAD DATA FROM S3 MANIFEST 's3-us-west-2://
amzn-s3-demo-bucket1
/q1_sales.json' INTO TABLE sales FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (MONTH, STORE, GROSS, NET);
LOAD XML FROM S3
您可以使用 LOAD XML FROM S3
语句从 Amazon S3 存储桶上存储的三种不同 XML 格式之一的 XML 文件中加载数据:
-
列名作为
<row>
元素的属性。属性值指定表字段的内容。<row column1="value1" column2="value2" .../>
-
列名作为
<row>
元素的子元素。子元素的值指定表字段的内容。<row> <column1>value1</column1> <column2>value2</column2> </row>
-
列名位于
name
元素的<field>
元素的<row>
属性中。<field>
元素的值指定表字段的内容。<row> <field name='column1'>value1</field> <field name='column2'>value2</field> </row>
语法
LOAD XML FROM S3 '
S3-URI
' [REPLACE | IGNORE] INTO TABLEtbl_name
[PARTITION (partition_name,...)] [CHARACTER SET charset_name] [ROWS IDENTIFIED BY '<element-name>
'] [IGNOREnumber
{LINES | ROWS}] [(field_name_or_user_var,...)] [SET col_name = expr,...]
参数
LOAD XML FROM S3
语句使用以下必需和可选参数。在 MySQL 文档的 LOAD XML 语句
- FILE | PREFIX
-
指定从单个文件还是从与指定前缀匹配的所有文件中加载数据。默认值为
FILE
。 - REPLACE | IGNORE
-
确定在输入行与数据库表中现有行具有相同唯一键值时采取什么操作。
-
如果您希望使用输入行替换表中的现有行,则指定
REPLACE
。 -
如果要放弃输入行,请指定
IGNORE
。默认值为IGNORE
。
-
- INTO TABLE
-
指定将输入行加载到的数据库表的名称。
- PARTITION
-
要求将所有输入行插入到由指定分区名称列表(以逗号分隔)指定的分区中。如果输入行无法插入到指定分区之一,则语句失败并返回错误。
- CHARACTER SET
-
指定输入文件中的数据的字符集。
- ROWS IDENTIFIED BY
-
指定用于标识输入文件中行的元素名称。默认为
<row>
。 - IGNORE
number
LINES | ROWS -
指定忽略输入文件开头的特定行数。例如,您可以使用
IGNORE 1 LINES
跳过文本文件的第一行,或者使用IGNORE 2 ROWS
跳过输入 XML 中的前两行数据。 - field_name_or_user_var, ...
-
指定一个或多个 XML 元素的逗号分隔列表,或者指定用户变量以按照名称指定要加载的元素。用于此目的的用户变量的名称必须与 XML 文件中的元素名称匹配,前缀为 @。您可以采用用户变量来存储对应的字段值,以便在后面重复使用。
例如,以下语句将输入文件的第一列加载到
table1
的第一列,并将table_column2
中的table1
列值设置为第二列输入值除以 100。LOAD XML FROM S3 's3://
amzn-s3-demo-bucket
/data.xml' INTO TABLE table1 (column1, @var1) SET table_column2 = @var1/100; - SET
-
指定以逗号分隔的分配操作列表,这些操作将表中各列的值设置为输入文件中未包含的值。
例如,以下语句将
table1
的前两列设置为输入文件中前两列的值,然后将column3
中table1
的值设置为当前时间戳。LOAD XML FROM S3 's3://
amzn-s3-demo-bucket
/data.xml' INTO TABLE table1 (column1, column2) SET column3 = CURRENT_TIMESTAMP;您可以在
SET
分配的右侧使用子查询。如果子查询返回要分配到列的值,则您只能使用标量子查询。此外,您无法使用子查询来从所加载的表中进行选择。