Amazon Relational Database Service
用户指南 (API Version 2014-10-31)
AWS 服务或AWS文档中描述的功能,可能因地区/位置而异。请点击 Amazon AWS 入门,可查看中国地区的具体差异

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

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

注意

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

向 Aurora 提供访问 Amazon S3 的权限

在从 Amazon S3 存储桶中加载数据之前,您必须向 Aurora 数据库群集提供访问 Amazon S3 的权限。要授予权限,请创建具有必需权限的 AWS Identity and Access Management (IAM) 角色,然后将这些角色与数据库群集关联。此外,您必须将 Aurora 数据库群集配置为允许与 Amazon S3 的出站连接。有关如何允许 Aurora 数据库群集代表您与 Amazon S3 进行通信的详细信息和说明,请参阅授权 Amazon Aurora 代表您访问其他 AWS 服务

注意

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

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

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

必须向发出 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 数字 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 S3mandatory 标志默认为 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 语句都会使用一个条目为每个已加载的文件更新 mysql 架构中的 aurora_s3_load_history 表。

运行 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>
  • 列名位于 <row> 元素的 <field> 元素的 name 属性中。<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 数字 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 分配的右侧使用子查询。如果子查询返回要分配到列的值,则您只能使用标量子查询。此外,您还可以使用子查询来从所加载的表中选择。

相关主题