将数据从 Amazon S3 存储桶中的文本文件加载到 Amazon Aurora MySQL 数据库集群 - Amazon Aurora
Amazon Web Services 文档中描述的 Amazon Web Services 服务或功能可能因区域而异。要查看适用于中国区域的差异,请参阅 中国的 Amazon Web Services 服务入门 (PDF)

将数据从 Amazon S3 存储桶中的文本文件加载到 Amazon Aurora MySQL 数据库集群

您可以使用 LOAD DATA FROM S3LOAD 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 的访问权限
  1. 创建一个 Amazon Identity and Access Management(IAM)策略,以提供允许 Aurora MySQL 数据库集群访问 Amazon S3 存储桶和对象的权限。有关说明,请参阅创建 IAM 策略以访问 Amazon S3 资源

    注意

    在 Aurora MySQL 版本 3.05 及更高版本中,您可以加载使用客户管理的 Amazon KMS keys进行加密的对象。为此,请在您的 IAM policy 中包含 kms:Decrypt 权限。有关更多信息,请参阅 创建 IAM 策略以访问 Amazon KMS 资源

    您不需要此权限即可使用 Amazon 托管式密钥或 Amazon S3 管理密钥(SSE-S3)来加载对象。

  2. 创建一个 IAM 角色,并将您在 创建 IAM 策略以访问 Amazon S3 资源 中创建的 IAM 策略附加到新的 IAM 角色。有关说明,请参阅创建 IAM 角色以允许 Amazon Aurora 访问Amazon服务

  3. 确保数据库集群使用的是自定义数据库集群参数组。

    有关创建自定义数据库集群参数组的更多信息,请参阅创建数据库集群参数组

  4. 对于 Aurora MySQL 版本 2,将 aurora_load_from_s3_roleaws_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 数据库集群和数据库实例参数

  5. 要允许 Aurora MySQL 数据库集群中的数据库用户访问 Amazon S3,请将您在创建 IAM 角色以允许 Amazon Aurora 访问Amazon服务中创建的角色与该数据库集群关联。对于 Aurora 全局数据库,将此角色与该全局数据库中的每个 Aurora 集群关联。有关将 IAM 角色与数据库集群关联的信息,请参阅将 IAM 角色与 Amazon Aurora MySQL 数据库集群关联

  6. 配置 Aurora MySQL 数据库集群以允许建立到 Amazon S3 的出站连接。有关说明,请参阅启用从 Amazon Aurora MySQL 到其他Amazon服务的网络通信

    如果您的 数据库集群不可公开访问且位于 VPC 公有子网中,则它是私有的。您可以创建一个 S3 网关端点来访问您的 S3 桶。有关更多信息,请参阅用于 Amazon S3 的网关端点

    对于 Aurora 全局数据库,为该全局数据库中的每个 Aurora 集群启用出站连接。

授予权限以在 Amazon Aurora MySQL 中加载数据

发出 LOAD DATA FROM S3LOAD 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_nameSET 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 系统变量(Aurora MySQL 版本 2)或 replica_skip_errors 系统变量(Aurora MySQL 版本 3)。

注意

数据库用户必须对正在向其加载数据的数据库具有 INSERT 权限。

指定 Amazon S3 桶的路径(URI)

用于指定 Amazon S3 桶中文件路径(URI)的语法如下所示。

s3-region://DOC-EXAMPLE-BUCKET/file-name-or-prefix

路径包括以下值:

  • region(可选)– 包含作为加载来源的 Amazon S3 存储桶的Amazon区域。该值为可选项。如果您没有指定 region 值,则 Aurora 从与您的数据库集群位于相同区域的 Amazon S3 中加载您的文件。

  • bucket-name – 包含要加载的数据的 Amazon S3 存储桶的名称。支持指定虚拟文件夹路径的对象前缀。

  • file-name-or-prefix – Amazon S3 文本文件或 XML 文件的名称,或指定要加载的一个或多个文本或 XML 文件的前缀。您还可以指定一个清单文件以指定一个或多个要加载的文本文件。有关使用清单文件从 Amazon S3 中加载文本文件的更多信息,请参阅使用清单指定要加载的数据文件

复制 S3 桶中文件的 URI
  1. 登录到Amazon Web Services Management Console,然后通过以下网址打开 Amazon S3 控制台:https://console.aws.amazon.com/s3/

  2. 在导航窗格中,选择,然后选择要复制其 URI 的桶。

  3. 选择要从 S3 加载的前缀或文件。

  4. 选择复制 S3 URI

LOAD DATA FROM S3

您可以使用 LOAD DATA FROM S3 语句从 MySQL LOAD DATA INFILE 语句支持的任意文本文件格式加载数据,例如逗号分隔的文本数据。不支持压缩文件。

注意

确保您的 Aurora MySQL 数据库集群允许与 S3 建立出站连接。有关更多信息,请参阅 启用从 Amazon Aurora MySQL 到其他Amazon服务的网络通信

语法

LOAD DATA [FROM] S3 [FILE | PREFIX | MANIFEST] 'S3-URI' [REPLACE | IGNORE] INTO TABLE tbl_name [PARTITION (partition_name,...)] [CHARACTER SET charset_name] [{FIELDS | COLUMNS} [TERMINATED BY 'string'] [[OPTIONALLY] ENCLOSED BY 'char'] [ESCAPED BY 'char'] ] [LINES [STARTING BY 'string'] [TERMINATED BY 'string'] ] [IGNORE number {LINES | ROWS}] [(col_name_or_user_var,...)] [SET col_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://DOC-EXAMPLE-BUCKET/data.txt' INTO TABLE table1 (column1, @var1) SET table_column2 = @var1/100;
SET

指定以逗号分隔的分配操作列表,这些操作将表中各列的值设置为输入文件中未包含的值。

例如,以下语句将 table1 的前两列设置为输入文件中前两列的值,然后将 column3table1 的值设置为当前时间戳。

LOAD DATA FROM S3 's3://DOC-EXAMPLE-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 表中的字段。

字段 描述

load_prefix

加载语句中指定的 URI。此 URI 可以映射到以下任一项:

  • LOAD DATA FROM S3 FILE 语句的单个数据文件

  • 映射到 LOAD DATA FROM S3 PREFIX 语句的多个数据文件的 Amazon S3 前缀

  • 包含要为 LOAD DATA FROM S3 MANIFEST 语句加载的文件的名称的单个清单文件

file_name

使用 load_prefix 字段中指定的 URI 从 Amazon S3 加载到 Aurora 的文件的名称。

version_number

file_name 字段指定的已加载文件的版本号(如果 Amazon S3 存储桶具有版本号)。

bytes_loaded

已加载文件的大小 (以字节为单位)。

load_timestamp

LOAD DATA FROM S3 语句的完成时间戳。

示例

以下语句从与 Aurora 数据库集群位于相同区域的 Amazon S3 存储桶中加载数据。该语句读取 DOC-EXAMPLE-BUCKET Amazon S3 存储桶中文件 customerdata.txt 的数据(以逗号分隔),然后将数据加载到 store-schema.customer-table 表中。

LOAD DATA FROM S3 's3://DOC-EXAMPLE-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 区域上的 DOC-EXAMPLE-BUCKET Amazon S3 存储桶中,读取与 employee-data 对象前缀匹配的所有文件中的数据(以逗号分隔),然后将数据加载到 employees 表中。

LOAD DATA FROM S3 PREFIX 's3-us-west-2://DOC-EXAMPLE-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://DOC-EXAMPLE-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 TABLE tbl_name [PARTITION (partition_name,...)] [CHARACTER SET charset_name] [ROWS IDENTIFIED BY '<element-name>'] [IGNORE number {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://DOC-EXAMPLE-BUCKET/data.xml' INTO TABLE table1 (column1, @var1) SET table_column2 = @var1/100;
SET

指定以逗号分隔的分配操作列表,这些操作将表中各列的值设置为输入文件中未包含的值。

例如,以下语句将 table1 的前两列设置为输入文件中前两列的值,然后将 column3table1 的值设置为当前时间戳。

LOAD XML FROM S3 's3://DOC-EXAMPLE-BUCKET/data.xml' INTO TABLE table1 (column1, column2) SET column3 = CURRENT_TIMESTAMP;

您可以在 SET 分配的右侧使用子查询。如果子查询返回要分配到列的值,则您只能使用标量子查询。此外,您无法使用子查询来从所加载的表中进行选择。