Amazon Aurora
Aurora 用户指南 (API 版本 2014-10-31)
AWS 文档中描述的 AWS 服务或功能可能因区域而异。要查看适用于中国区域的差异,请参阅中国的 AWS 服务入门

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

您可以使用 LOAD DATA FROM S3LOAD XML FROM S3 语句从 Amazon S3 存储桶上存储的文件中加载数据。

注意

在 Amazon Aurora MySQL 1.8 和更高版本中,支持将数据从 Amazon S3 存储桶中的文本文件加载到表中。有关 Aurora MySQL 版本的更多信息,请参阅Amazon Aurora MySQL 的数据库引擎更新

为 Aurora 授予 Amazon S3 的访问权限

您必须先为 Aurora MySQL 数据库集群授予 Amazon S3 的访问权限,然后才能从 Amazon S3 存储桶中加载数据。

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

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

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

  3. aurora_load_from_s3_roleaws_default_s3_role 数据库集群参数设置为新 IAM 角色的 Amazon 资源名称 (ARN)。如果没有为 aurora_load_from_s3_role 指定 IAM 角色,则 Aurora 使用在 aws_default_s3_role 中指定的 IAM 角色。

    如果集群是 Aurora 全局数据库的一部分,则为该全局数据库中的每个 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 MySQL 到其他 AWS 服务的网络通信

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

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

必须向发出 LOAD DATA FROM S3LOAD XML FROM S3 语句的数据库用户授予 LOAD FROM S3 权限以便发出任一语句。默认情况下,将为数据库集群的主用户名授予 LOAD FROM S3 权限。您可以使用以下语句向另一个用户授予权限。

GRANT LOAD FROM S3 ON *.* TO 'user'@'domain-or-ip-address'

LOAD FROM S3 权限是 Amazon Aurora 特定的,而不适用于 MySQL 数据库或 RDS MySQL 数据库实例。如果您在作为复制主实例的 Aurora 数据库集群和作为复制客户端的 MySQL 数据库之间设置了复制,GRANT LOAD FROM S3 语句将导致复制停止并出现错误。您可以安全地跳过该错误,继续复制。要跳过 RDS MySQL 数据库实例的错误,请使用 mysql_rds_skip_repl_error 过程。要跳过外部 MySQL 数据库的错误,请使用 SET GLOBAL sql_slave_skip_counter 语句。

指定 Amazon S3 存储桶的路径

用于指定存储在 Amazon S3 存储桶中的文件的路径的语法如下所示。

s3-region://bucket-name/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 中加载文本文件的更多信息,请参阅使用清单指定要加载的数据文件

LOAD DATA FROM S3

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

语法

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,...]

参数

随后,您可以找到 LOAD DATA FROM S3 语句使用的必需和可选参数列表。在 MySQL 文档的 LOAD DATA INFILE 语法中可以找到有关这些参数的详细信息。

  • FILE | PREFIX | MANIFEST – 指定是从单个文件、与给定前缀匹配的所有文件还是指定清单上的所有文件中加载数据。默认值为 FILE

  • S3-URI – 指定要加载的文本或清单文件的 URI,或指定要使用的 Amazon S3 前缀。使用指定 Amazon S3 存储桶的路径中描述的语法指定 URI。

  • REPLACE | IGNORE – 确定在输入行与数据库表中的现有行具有相同唯一键值时采取什么操作。

    • 如果您希望使用输入行替换表中的现有行,则指定 REPLACE

    • 如果要放弃输入行,请指定 IGNORE。默认值为 IGNORE

  • INTO TABLE – 指定将输入行加载到的数据库表的名称。

  • PARTITION – 要求将所有输入行插入到由指定分区名称列表(以逗号分隔)指定的分区中。如果输入行无法插入到指定分区之一,则语句失败并返回错误。

  • CHARACTER SET – 指定输入文件中的数据的字符集。

  • FIELDS | COLUMNS – 指定如何分隔输入文件中的字段或列。默认情况下使用制表符分隔字段。

  • LINES – 指定如何分隔输入文件中的行。默认情况下使用回车分隔行。

  • IGNORE number LINES | ROWS – 指定忽略输入文件开头的特定行数。例如,您可以使用 IGNORE 1 LINES 跳过包含列名的初始标题行,或者使用 IGNORE 2 ROWS 跳过输入文件的前两行数据。

  • col_name_or_user_var, ... – 指定一个或多个列名的逗号分隔列表,或者指定用户变量以按照名称指定要加载的列。用于此目的的用户变量的名称必须与文本文件中的元素名称匹配,前缀为 @。您可以采用用户变量来存储对应的字段值,以便在后面重复使用。

    例如,以下语句将输入文件的第一列加载到 table1 的第一列,并将 table1 中的 table_column2 列值设置为第二列输入值除以 100。

    LOAD DATA FROM S3 's3://mybucket/data.txt' INTO TABLE table1 (column1, @var1) SET table_column2 = @var1/100;
  • SET – 指定以逗号分隔的分配操作列表,这些操作将表中列的值设置为在输入文件中未包含的值。

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

    LOAD DATA FROM S3 's3://mybucket/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 格式的清单文件,此文件列出了要加载到数据库集群中的表的文本文件。您必须使用 Aurora 1.11 或更高版本以将 MANIFEST 关键字与 LOAD DATA FROM S3 语句配合使用。

以下 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 将终止。

清单文件可具有任何扩展名。以下示例使用前一个示例中名为 customer.manifest 的清单以运行 LOAD DATA FROM S3 语句。

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 存储桶中加载数据。该语句读取 dbbucket Amazon S3 存储桶中的 customerdata.txt 文件中的数据(以逗号分隔),然后将数据加载到 store-schema.customer-table 表中。

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

LOAD DATA FROM S3 PREFIX 's3-us-west-2://my-data/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://aurora-bucket/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 DATA 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 的第一列,并将 table1 中的 table_column2 列值设置为第二列输入值除以 100。

    LOAD XML FROM S3 's3://mybucket/data.xml' INTO TABLE table1 (column1, @var1) SET table_column2 = @var1/100;
  • SET – 指定以逗号分隔的分配操作列表,这些操作将表中列的值设置为在输入文件中未包含的值。

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

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

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

相关主题