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

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

您可以使用 SELECT INTO OUTFILE S3 语句从 Amazon Aurora MySQL 数据库集群中查询数据,并将数据保存到 Amazon S3 存储桶中存储的文本文件。在 Aurora MySQL 中,文件首先存储在本地磁盘上,然后导出到 S3。导出完成后,将删除本地文件。

您可以使用 Amazon S3 管理密钥(SSE-S3)或 AWS KMS key(SSE-KMS:AWS 托管式密钥或客户管理密钥) 对 Amazon S3 桶进行加密。

LOAD DATA FROM S3 语句可以使用 SELECT INTO OUTFILE S3 语句创建的文件将数据加载到 Aurora 数据库集群中。有关更多信息,请参阅 将数据从 Amazon S3 存储桶中的文本文件加载到 Amazon Aurora MySQL 数据库集群

注意

Aurora Serverless v1 数据库集群不支持此功能。Aurora Serverless v2 数据库集群支持此功能。

也可以使用 AWS Management Console、AWS CLI 或 Amazon RDS API 将数据库集群数据和数据库集群快照数据保存到 Amazon S3。有关更多信息,请参阅将数据库集群数据导出到 Amazon S3将数据库集群快照数据导出到 Amazon S3

为 Aurora MySQL 授予 Amazon S3 的访问权限

在将数据保存到 Amazon S3 存储桶之前,您必须先为 Aurora MySQL 数据库集群授予 Amazon S3 的访问权限。

为 Amazon S3 授予 Aurora MySQL 的访问权限
  1. 创建一个 AWS Identity and Access Management(IAM)策略,以提供允许 Aurora MySQL 数据库集群访问 Amazon S3 存储桶和对象的权限。有关说明,请参阅创建 IAM 策略以访问 Amazon S3 资源

    注意

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

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

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

  3. 对于 Aurora MySQL 版本 2,将 aurora_select_into_s3_roleaws_default_s3_role 数据库集群参数设置为新 IAM 角色的 Amazon 资源名称(ARN)。如果没有为 aurora_select_into_s3_role 指定 IAM 角色,则 Aurora 使用在 aws_default_s3_role 中指定的 IAM 角色。

    对于 Aurora MySQL 版本 3,使用 aws_default_s3_role

    如果集群是 Aurora 全局数据库的一部分,则为该全局数据库中的每个 Aurora 集群设置此参数。

    有关数据库集群参数的更多信息,请参阅Amazon Aurora 数据库集群和数据库实例参数

  4. 要允许 Aurora MySQL 数据库集群中的数据库用户访问 Amazon S3,请将您在创建 IAM 角色以允许 Amazon Aurora 访问AWS服务中创建的角色与该数据库集群关联。

    对于 Aurora 全局数据库,将此角色与该全局数据库中的每个 Aurora 集群关联。

    有关将 IAM 角色与数据库集群关联的信息,请参阅将 IAM 角色与 Amazon Aurora MySQL 数据库集群关联

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

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

授予权限以在 Aurora MySQL 中保存数据

发出 SELECT INTO OUTFILE S3 语句的数据库用户必须具有特定角色或权限。在 Aurora MySQL 版本 3 中,您可以授予 AWS_SELECT_S3_ACCESS 角色。在 Aurora MySQL 版本 2 中,您可以授予 SELECT INTO S3 权限。预设情况下,将为数据库集群的管理用户授予适当的角色或权限。您可以使用以下语句之一向另一个用户授予权限。

对 Aurora MySQL 版本 3 使用以下语句:

GRANT AWS_SELECT_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 SELECT INTO S3 ON *.* TO 'user'@'domain-or-ip-address'

AWS_SELECT_S3_ACCESS 角色和 SELECT INTO S3 权限特定于 Amazon Aurora MySQL,而不适用于 MySQL 数据库或 RDS for MySQL 数据库实例。如果您在作为复制源的 Aurora MySQL 数据库集群和作为复制客户端的 MySQL 数据库之间设置了复制,则角色或权限的 GRANT 语句将导致复制停止并出现错误。您可以安全地跳过该错误,继续复制。要跳过 RDS for MySQL 数据库实例上的错误,请使用 mysql_rds_skip_repl_error 过程。要跳过外部 MySQL 数据库上的错误,请使用 slave_skip_errors 系统变量(Aurora MySQL 版本 2)或 replica_skip_errors 系统变量(Aurora MySQL 版本 3)。

指定 Amazon S3 存储桶的路径

用于指定在 Amazon S3 存储桶上存储数据和清单文件的路径的语法与在 LOAD DATA FROM S3 PREFIX 语句中使用的语法类似,如下所示。

s3-region://bucket-name/file-prefix

路径包括以下值:

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

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

  • file-prefix – 指定要保存到 Amazon S3 的文件的 Amazon S3 对象前缀。

SELECT INTO OUTFILE S3 语句创建的数据文件使用以下路径,其中 00000 表示从零开始的 5 位整数。

s3-region://bucket-name/file-prefix.part_00000

例如,假设 SELECT INTO OUTFILE S3 语句指定 s3-us-west-2://bucket/prefix 作为存储数据文件的路径并创建三个数据文件。指定的 Amazon S3 存储桶包含以下数据文件。

  • s3-us-west-2://bucket/prefix.part_00000

  • s3-us-west-2://bucket/prefix.part_00001

  • s3-us-west-2://bucket/prefix.part_00002

创建清单以列出数据文件

您可以使用 SELECT INTO OUTFILE S3 语句和 MANIFEST ON 选项,以 JSON 格式创建列出由语句创建的文本文件的清单文件。LOAD DATA FROM S3 语句可以使用清单文件将数据文件加载回 Aurora MySQL 数据库集群中。有关使用清单将数据文件从 Amazon S3 加载到 Aurora MySQL 数据库集群的更多信息,请参阅使用清单指定要加载的数据文件

清单中包含的由 SELECT INTO OUTFILE S3 语句创建的数据文件按照它们由语句创建的顺序列出。例如,假设 SELECT INTO OUTFILE S3 语句指定 s3-us-west-2://bucket/prefix 作为存储数据文件的路径并创建三个数据文件和一个清单文件。指定的 Amazon S3 存储桶包含一个名为 s3-us-west-2://bucket/prefix.manifest 的清单文件,其中包含以下信息。

{ "entries": [ { "url":"s3-us-west-2://bucket/prefix.part_00000" }, { "url":"s3-us-west-2://bucket/prefix.part_00001" }, { "url":"s3-us-west-2://bucket/prefix.part_00002" } ] }

SELECT INTO OUTFILE S3

您可以使用 SELECT INTO OUTFILE S3 语句从数据库集群中查询数据,并将数据直接保存到 Amazon S3 存储桶中存储的带分隔符的文本文件。

不支持压缩文件。从 Aurora MySQL 版本 2.09.0 开始支持加密文件。

语法

SELECT [ALL | DISTINCT | DISTINCTROW ] [HIGH_PRIORITY] [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT] [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] select_expr [, select_expr ...] [FROM table_references [PARTITION partition_list] [WHERE where_condition] [GROUP BY {col_name | expr | position} [ASC | DESC], ... [WITH ROLLUP]] [HAVING where_condition] [ORDER BY {col_name | expr | position} [ASC | DESC], ...] [LIMIT {[offset,] row_count | row_count OFFSET offset}] INTO OUTFILE S3 's3_uri' [CHARACTER SET charset_name] [export_options] [MANIFEST {ON | OFF}] [OVERWRITE {ON | OFF}] [ENCRYPTION {ON | OFF | SSE_S3 | SSE_KMS ['cmk_id']}] export_options: [FORMAT {CSV|TEXT} [HEADER]] [{FIELDS | COLUMNS} [TERMINATED BY 'string'] [[OPTIONALLY] ENCLOSED BY 'char'] [ESCAPED BY 'char'] ] [LINES [STARTING BY 'string'] [TERMINATED BY 'string'] ]

参数

SELECT INTO OUTFILE S3 语句使用以下特定于 Aurora 的必需和可选参数。

s3-uri

为要使用的 Amazon S3 前缀指定 URI。使用指定 Amazon S3 存储桶的路径中所述的语法。

FORMAT {CSV|TEXT} [HEADER]

(可选)以 CSV 格式保存数据。

TEXT 选项是默认选项,并生成现有的 MySQL 导出格式。

CSV 选项会生成逗号分隔的数据值。CSV 格式遵循 RFC-4180 规范。如果指定可选关键字 HEADER,则输出文件包含一个标题行。标题行中的标签与 SELECT 语句中的列名称相对应。您可以将 CSV 文件用于训练数据模型,以便与 AWS ML 服务一起使用。有关将导出的 Aurora 数据与 AWS ML 服务结合使用的更多信息,请参阅将数据导出到 Amazon S3 以进行 SageMaker 模型训练(高级)

MANIFEST {ON | OFF}

指示是否在 Amazon S3 中创建清单文件。清单文件是 JavaScript 对象表示法 (JSON) 文件,可用于通过 LOAD DATA FROM S3 MANIFEST 语句将数据加载到 Aurora 数据库集群中。有关 LOAD DATA FROM S3 MANIFEST 的更多信息,请参阅将数据从 Amazon S3 存储桶中的文本文件加载到 Amazon Aurora MySQL 数据库集群

如果在查询中指定了 MANIFEST ON,则在创建并上传所有数据文件后在 Amazon S3 中创建清单文件。使用以下路径创建清单文件:

s3-region://bucket-name/file-prefix.manifest

有关清单文件的内容格式的更多信息,请参阅 创建清单以列出数据文件

OVERWRITE {ON | OFF}

指示是否覆盖指定的 Amazon S3 桶中的现有文件。如果指定 OVERWRITE ON,则覆盖与在 s3-uri 中指定的 URI 内的文件前缀匹配的现有文件。否则将出错。

ENCRYPTION {ON | OFF | SSE_S3 | SSE_KMS ['cmk_id']}

指示是使用 Amazon S3 管理密钥(SSE-S3)还是使用 AWS KMS keys(SSE-KMS,包括 AWS 托管式密钥和客户管理密钥)进行服务器端加密。SSE_S3SSE_KMS 设置适用于 Aurora MySQL 版本 3.05 及更高版本。

您也可以使用 aurora_select_into_s3_encryption_default 会话变量代替 ENCRYPTION 子句,如以下示例所示。使用 SQL 子句或会话变量,但不能同时使用这两者。

set session set session aurora_select_into_s3_encryption_default={ON | OFF | SSE_S3 | SSE_KMS};

SSE_S3SSE_KMS 设置适用于 Aurora MySQL 版本 3.05 及更高版本。

aurora_select_into_s3_encryption_default 设置为以下值时:

  • OFF – 遵循 S3 桶的默认加密策略。aurora_select_into_s3_encryption_default 的默认值为 OFF

  • ONSSE_S3 – 使用 Amazon S3 管理密钥(SSE-S3)对 S3 对象进行加密。

  • SSE_KMS – 则使用 AWS KMS key加密 S3 对象。

    在这种情况下,您还需要包含会话变量 aurora_s3_default_cmk_id,例如:

    set session aurora_select_into_s3_encryption_default={SSE_KMS}; set session aurora_s3_default_cmk_id={NULL | 'cmk_id'};
    • 如果aurora_s3_default_cmk_idNULL,则使用加密 S3 对象AWS 托管式密钥。

    • 如果 aurora_s3_default_cmk_id 为非空字符串 cmk_id,则使用客户管理密钥加密 S3 对象。

      cmk_id 值不能为空字符串。

当您使用 SELECT INTO OUTFILE S3 命令时,Aurora 按如下方式决定加密:

  • 如果 ENCRYPTION 子句存在于 SQL 命令中,则 Aurora 仅依赖 ENCRYPTION 的值,而不使用会话变量。

  • 如果 ENCRYPTION 子句不存在,则 Aurora 依赖于会话变量的值。

有关更多信息,请参阅《Amazon Simple Storage Service 用户指南》中的使用 Amazon S3 管理密钥(SSE-S3)进行服务器端加密使用 AWS KMS 密钥(SSE-KMS)进行服务器端加密

在 MySQL 文档的 SELECT 语句LOAD DATA 语句中,可以找到有关其他参数的更多详细信息。

注意事项

写入到 Amazon S3 存储桶的文件数取决于 SELECT INTO OUTFILE S3 语句选择的数据数量以及 Aurora MySQL 的文件大小阈值。默认文件大小阈值为 6GB。如果语句选择的数据小于文件大小阈值,则将创建单个文件;否则将创建多个文件。该语句所创建文件的其他注意事项包括下列内容:

  • Aurora MySQL 确保数据文件中的行不会跨文件边界拆分。对于多个文件,除了最后一个文件之外,每个数据文件的大小通常接近文件大小阈值。不过,有时保持在文件大小阈值之下会导致在两个数据文件之间拆分某一行。在这种情况下,Aurora MySQL 创建一个保留该行而不拆分的数据文件,但可能会大于文件大小阈值。

  • 由于 Aurora MySQL 中的每个 SELECT 语句作为原子事务运行,选择大数据集的 SELECT INTO OUTFILE S3 语句可能需要一段时间来运行。如果语句由于任何原因失败,您可能需要重新启动和发出该语句。不过,如果语句失败,已上传到 Amazon S3 的文件将保留在指定的 Amazon S3 存储桶中。您可以使用其他语句来上传剩余的数据而不用重新启动。

  • 如果要选择的数据量很大(超过 25 GB),我们建议您使用多个 SELECT INTO OUTFILE S3 语句以将数据保存到 Amazon S3 中。每个语句应选择不同部分的数据来保存,并且在保存数据文件时指定使用 file_prefix 参数中的不同 s3-uri。使用多个语句对要选择的数据进行分区可以更轻松地从一个语句中的错误中恢复。如果一条语句发生错误,则只需要重新选择部分数据并将其上传到 Amazon S3 即可。使用多个语句还有助于避免单个长时间运行的事务,这可改进性能。

  • 如果多个 SELECT INTO OUTFILE S3 语句在 file_prefix 参数中使用相同的 s3-uri,并且并行运行这些语句以选择数据并上传到 Amazon S3 中,则行为是不确定的。

  • Aurora MySQL 不会将元数据(例如,表架构或文件元数据)上传到 Amazon S3 中。

  • 有时,您可以重新运行 SELECT INTO OUTFILE S3 查询,以便从故障中恢复。在这些情况下,您必须在 Amazon S3 存储桶中删除具有在 s3-uri 中指定的文件前缀的任何现有数据文件,或者在 OVERWRITE ON 查询中包括 SELECT INTO OUTFILE S3

SELECT INTO OUTFILE S3 语句返回一个典型的 MySQL 错误编号以及有关成功或失败的响应。如果您无权访问 MySQL 错误编号和响应,则确定何时才完成的最简单方法是在语句中指定 MANIFEST ON。清单文件是该语句写入的最后一个文件。换而言之,如果您具有清单文件,则语句已完成。

目前,没有其他方法可以在运行期间直接监视 SELECT INTO OUTFILE S3 语句的进度。不过,假设您使用该语句将大量数据从 Aurora MySQL 写入到 Amazon S3 中,并且您知道语句选择的数据大小。在这种情况下,您可以通过监视 Amazon S3 中数据文件的创建操作来估算进度。

为此,您可以利用这样一个常识,也就是每次当语句选择大约 6GB 数据时,将在指定 Amazon S3 存储桶中创建一个数据文件。将选定数据的大小除以 6GB 可以估计要创建的数据文件数。您可以通过监视在语句运行期间上传到 Amazon S3 的文件数来估算语句的进度。

示例

以下语句选择 employees 表中的所有数据并将数据保存到与 Aurora MySQL 数据库集群位于不同区域的 Amazon S3 存储桶中。该语句创建数据文件,其中每个字段以逗号 (,) 字符终止,每行以换行 (\n) 字符终止。如果在指定的 Amazon S3 存储桶中存在与 sample_employee_data 文件前缀匹配的文件,该语句将返回错误。

SELECT * FROM employees INTO OUTFILE S3 's3-us-west-2://aurora-select-into-s3-pdx/sample_employee_data' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

以下语句选择 employees 表中的所有数据,并将数据保存到与 Aurora MySQL 数据库集群位于相同区域的 Amazon S3 存储桶中。该语句创建数据文件,其中每个字段以逗号 (,) 字符终止,每行以换行 (\n) 字符终止,还创建一个清单文件。如果在指定的 Amazon S3 存储桶中存在与 sample_employee_data 文件前缀匹配的文件,该语句将返回错误。

SELECT * FROM employees INTO OUTFILE S3 's3://aurora-select-into-s3-pdx/sample_employee_data' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' MANIFEST ON;

以下语句选择 employees 表中的所有数据并将数据保存到与 Aurora 数据库集群位于不同区域的 Amazon S3 存储桶中。该语句创建数据文件,其中每个字段以逗号 (,) 字符终止,每行以换行 (\n) 字符终止。该语句在指定的 Amazon S3 存储桶中覆盖与 sample_employee_data 文件前缀匹配的任何现有文件。

SELECT * FROM employees INTO OUTFILE S3 's3-us-west-2://aurora-select-into-s3-pdx/sample_employee_data' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' OVERWRITE ON;

以下语句选择 employees 表中的所有数据,并将数据保存到与 Aurora MySQL 数据库集群位于相同区域的 Amazon S3 存储桶中。该语句创建数据文件,其中每个字段以逗号 (,) 字符终止,每行以换行 (\n) 字符终止,还创建一个清单文件。该语句在指定的 Amazon S3 存储桶中覆盖与 sample_employee_data 文件前缀匹配的任何现有文件。

SELECT * FROM employees INTO OUTFILE S3 's3://aurora-select-into-s3-pdx/sample_employee_data' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' MANIFEST ON OVERWRITE ON;