MySQL Shell 9.0  /  MySQL Shell 实用程序  /  并行表导入实用程序

11.4 并行表导入实用程序

MySQL Shell 的并行表导入实用程序 util.importTable() 为大型数据文件提供快速数据导入到 MySQL 关系表的功能。该实用程序分析输入数据文件,将其分成多个块,并使用并行连接将这些块上传到目标 MySQL 服务器。与使用 LOAD DATA 语句的标准单线程上传相比,该实用程序能够以快许多倍的速度完成大型数据导入。

关于实用程序

MySQL Shell 的并行表导入实用程序支持 MySQL Shell 的表导出实用程序的输出,该实用程序可以压缩其生成的输出数据文件,并可以将其导出到本地文件夹或对象存储桶。并行表导入实用程序的默认方言是表导出实用程序生成的输出文件的默认方言。并行表导入实用程序也可用于从其他源上传文件。

要导入的一个或多个数据文件可以位于以下任何位置

  • 客户端主机可以作为本地磁盘访问的位置。

  • 客户端主机可以通过 HTTP 或 HTTPS 访问的远程位置,使用 URL 指定。以这种方式访问的文件不支持模式匹配。

  • Oracle 云基础设施对象存储桶。

数据导入到活动 MySQL 会话连接到的 MySQL 服务器中的单个关系表。

运行并行表导入实用程序时,需要指定数据文件中的字段与 MySQL 表中的列之间的映射关系。可以像使用 LOAD DATA 语句一样设置字段和行处理选项,以处理任意格式的数据文件。对于多个文件,所有文件必须采用相同的格式。该实用程序的默认方言映射到使用 SELECT...INTO OUTFILE 语句创建的文件,并使用该语句的默认设置。该实用程序还具有预设的方言,这些方言映射到 CSV 文件(在 DOS 或 UNIX 系统上创建)、TSV 文件和 JSON 的标准数据格式,并且可以根据需要使用字段和行处理选项自定义这些方言。请注意,JSON 数据必须采用每行一个文档的格式。

自并行表导入实用程序推出以来,已经添加了许多函数,因此请使用最新版本的 MySQL Shell 以获得该实用程序的完整功能。

输入预处理

并行表导入实用程序可以捕获数据文件中的列以进行输入预处理,就像使用 LOAD DATA 语句一样。可以选择丢弃选定的数据,也可以转换数据并将其分配给目标表中的列。

Oracle 云基础设施对象存储导入

数据必须从客户端主机可以作为本地磁盘访问的位置导入,或者可以从 Oracle 云基础设施对象存储桶导入数据,由 osBucketName 选项指定。

多个数据文件导入

并行表导入实用程序可以将单个输入数据文件导入到单个关系表,并且还能够导入指定的多个文件列表,并且支持通配符模式匹配以包含来自某个位置的所有相关文件。由实用程序的一次运行上传的多个文件将放入单个关系表中,例如,可以将从多个主机导出的数据合并到单个表中以用于分析。

压缩文件处理

并行表导入实用程序可以接受未压缩的输入数据文件。该实用程序分析数据文件,将其分成多个块,并将这些块上传到目标 MySQL 服务器中的关系表,在并行连接之间划分这些块。该实用程序还可以接受以 gzip (.gz) 和 zstd (.zst) 格式压缩的数据文件,并根据文件扩展名自动检测格式。该实用程序以上传的压缩格式从存储中上传压缩文件,从而节省了该部分传输的带宽。压缩文件不能分成多个块,因此该实用程序使用其并行连接同时解压缩多个文件并将其上传到目标服务器。如果只有一个输入数据文件,则压缩文件的上传只能使用一个连接。

MySQL Shell 的转储加载实用程序 util.loadDump() 旨在导入 MySQL Shell 的实例转储实用程序 util.dumpInstance()、架构转储实用程序 util.dumpSchemas() 和表转储实用程序 util.dumpTables() 生成的分块输出文件和元数据的组合。如果要在将分块输出文件中的任何数据上传到目标服务器之前对其进行修改,则可以将并行表导入实用程序与转储加载实用程序结合使用。为此,首先使用转储加载实用程序仅加载所选表的 DDL,以在目标服务器上创建表。然后使用并行表导入实用程序捕获和转换来自表的输出文件的数据,并将其导入到目标表。对要修改数据的任何其他表,根据需要重复该过程。最后,使用转储加载实用程序加载不想修改的任何剩余表的 DDL 和数据,但不包括已修改的表。有关该过程的说明,请参阅 修改转储数据

要求和限制

并行表导入实用程序使用 LOAD DATA LOCAL INFILE 语句上传数据,因此 local_infile 系统变量在目标服务器上必须设置为 ON。可以通过在运行并行表导入实用程序之前在 SQL 模式下发出以下语句来执行此操作

SET GLOBAL local_infile = 1;

为了避免使用 LOAD DATA LOCAL 时出现已知的潜在安全问题,当 MySQL 服务器使用文件传输请求回复并行表导入实用程序的 LOAD DATA 请求时,该实用程序仅发送预定的数据块,并忽略服务器尝试的任何特定请求。有关更多信息,请参阅 LOAD DATA LOCAL 的安全注意事项

运行实用程序

并行表导入实用程序需要到目标 MySQL 服务器的现有经典 MySQL 协议连接。每个线程都打开自己的会话以将数据块发送到 MySQL 服务器,或者在压缩文件的情况下,以并行发送多个文件。可以调整线程数、每个块中发送的字节数以及每个线程的最大数据传输速率,以平衡网络负载和数据传输速度。该实用程序不能通过 X 协议连接运行,因为该协议不支持 LOAD DATA 语句。

在 MySQL Shell API 中,并行表导入实用程序是 util 全局对象的一个函数,并具有以下签名

importTable ({file_name | file_list}, options)

options 是一个导入选项字典,如果为空,则可以省略。这些选项列在本主题的最后一部分。

file_name 是一个字符串,用于指定包含要导入的数据的单个文件的名称和路径。或者,file_list 是一个文件路径数组,用于指定多个数据文件。在 Windows 上,必须对文件路径中的反斜杠进行转义,或者可以使用正斜杠代替。

  • 对于客户端主机可以在本地磁盘上访问的文件,可以使用 file:// 模式作为目录路径的前缀,或者允许其默认为该模式。对于以这种方式访问的文件,文件路径可以包含通配符 *(多个字符)和 ?(单个字符)以进行模式匹配。请注意,如果这些通配符存在于文件路径中,则该实用程序会将它们视为通配符,因此可能会尝试使用错误的文件传输策略。

  • 对于客户端主机可以通过 HTTP 或 HTTPS 访问的文件,请提供 URL 或 URL 列表,并根据需要以 http://https:// 模式作为前缀,格式为 http[s]://host.domain[:port]/path。对于以这种方式访问的文件,模式匹配不可用。HTTP 服务器必须支持 Range 请求标头,并且必须将 Content-Range 响应标头发回给客户端。

  • 对于 Oracle 云基础设施对象存储桶中的文件,请指定存储桶中文件的路径,并使用 osBucketName 选项指定存储桶名称。

该函数返回 void,如果出现错误则返回异常。如果导入在中途被用户使用 Ctrl+C 或由于错误而停止,则该实用程序将停止发送数据。当服务器完成对其接收到的数据的处理后,将返回消息,显示每个线程在当时正在导入的块、完成百分比以及目标表中已更新的记录数。

以下示例(第一个在 MySQL Shell 的 JavaScript 模式下,第二个在 MySQL Shell 的 Python 模式下)将单个 CSV 文件 /tmp/productrange.csv 中的数据导入 mydb 数据库中的 products 表,并跳过文件中的标题行

mysql-js> util.importTable("/tmp/productrange.csv", {schema: "mydb", table: "products", dialect: "csv-unix", skipRows: 1, showProgress: true})
mysql-py> util.import_table("/tmp/productrange.csv", {"schema": "mydb", "table": "products", "dialect": "csv-unix", "skipRows": 1, "showProgress": True})

以下 MySQL Shell Python 模式示例仅指定了 CSV 文件的方言。 mydb 是 MySQL Shell 会话的活动架构。 因此,该实用程序会将文件 /tmp/productrange.csv 中的数据导入 mydb 数据库中的 productrange

mysql-py> \use mydb
mysql-py> util.import_table("/tmp/productrange.csv", {"dialect": "csv-unix"})

以下 MySQL Shell Python 模式示例从多个文件导入数据,包括单独命名的文件、使用通配符模式匹配指定的文件范围以及压缩文件

mysql-py> util.import_table(
    [
        "data_a.csv",
        "data_b*",
        "data_c*",
        "data_d.tsv.zst",
        "data_e.tsv.zst",
        "data_f.tsv.gz",
        "/backup/replica3/2021_01_12/data_g.tsv",
        "/backup/replica3/2021_01_13/*.tsv",
    ],
    {"schema": "mydb", "table": "productrange"}
)

并行表导入实用程序也可以使用 mysqlsh 命令行界面从命令行调用。 使用此界面,您可以像以下示例一样调用该实用程序

mysqlsh mysql://root:@127.0.0.1:3366 --ssl-mode=DISABLED -- util import-table /r/mytable.dump --schema=mydb --table=regions --bytes-per-chunk=10M --linesTerminatedBy=$'\r\n'

导入多个数据文件时,如果使用通配符模式匹配指定的文件范围用引号引起来(如下例所示),则 MySQL Shell 的 glob 模式匹配逻辑会对其进行扩展。 否则,它们将由您输入 mysqlsh 命令的用户 shell 的模式匹配逻辑进行扩展。

mysqlsh mysql://root:@127.0.0.1:3366 -- util import-table data_a.csv "data_b*" data_d.tsv.zst --schema=mydb --table=productrange --osBucketName=mybucket

请注意,如上例所示,在支持此功能的 shell(例如 bashkshmkshzsh)中,必须使用 ANSI-C 引号传递换行符。 有关 mysqlsh 命令行集成的信息,请参阅 第 5.8 节“API 命令行集成”

用于导入表的选项

并行表导入实用程序提供以下导入选项,用于指定如何导入数据

schema:“db_name

连接的 MySQL 服务器上的目标数据库的名称。 如果省略此选项,该实用程序将尝试识别并使用当前 MySQL Shell 会话中使用的架构名称,如连接 URI 字符串、\use 命令或 MySQL Shell 选项中指定的那样。 如果未指定架构名称并且无法从会话中识别,则会返回错误。

table:“table_name

目标关系表的名称。 如果省略此选项,该实用程序会假定表名称是数据文件的名称(不带扩展名)。 目标表必须存在于目标数据库中。

columns:列名数组

一个字符串数组,其中包含来自一个或多个导入文件的列名,顺序与它们映射到目标关系表中列的顺序相同。 如果导入的数据不包含目标表的所有列,或者导入数据中字段的顺序与表中列的顺序不同,请使用此选项。 如果省略此选项,则输入行应包含与目标表中每个列匹配的字段。

您可以使用此选项从一个或多个导入文件中捕获列以进行输入预处理,方法与使用 LOAD DATA 语句相同。 当您在数组中使用整数值代替列名时,导入文件中的该列将被捕获为用户变量 @int,例如 @1。 可以丢弃选定的数据,也可以使用 decodeColumns 选项转换数据并将其分配给目标表中的列。

在以下 MySQL Shell JavaScript 模式示例中,导入文件中的第二列和第四列分别分配给了用户变量 @1@2,并且不存在将它们分配给目标表中任何列的 decodeColumns 选项,因此它们被丢弃。

mysql-js> util.importTable('file.txt', {
            table: 't1',
            columns: ['column1', 1, 'column2', 2, 'column3']
          });
decodeColumns:字典

一个键值对字典,它将 columns 选项捕获为用户变量的导入文件列分配给目标表中的列,并以与 LOAD DATA 语句的 SET 子句相同的方式为它们指定预处理转换。

在以下 MySQL Shell JavaScript 模式示例中,数据文件中的第一个输入列用作目标表中的第一个列。 第二个输入列(已由 columns 选项分配给变量 @1)在用作目标表中第二列的值之前要进行除法运算。

mysql-js> util.importTable('file.txt', {
            columns: ['column1', 1],
            decodeColumns: {'column2': '@1 / 100'}
          });

在以下 MySQL Shell JavaScript 模式示例中,数据文件中的输入列都被分配给了变量,然后以各种方式转换并用于填充目标表的列

mysql-js> util.importTable('file.txt', {
            table: 't1',
            columns: [1, 2],
            decodeColumns: {
              'a': '@1',
              'b': '@2',
              'sum': '@1 + @2',
              'multiple': '@1 * @2',
              'power': 'POW(@1, @2)'
            }
          });
skipRows:数字

跳过导入文件开头或多个导入文件的情况下每个包含在文件列表中的文件开头的此行数。 您可以使用此选项省略包含要上传到表的列名的初始标题行。 默认情况下,不会跳过任何行。

replaceDuplicates:[true|false]

主键或唯一索引值与现有行相同的新输入行是应该被替换 (true) 还是应该被跳过 (false)。 默认值为 false

dialect:[default|csv|csv-unix|tsv|json]

使用一组适用于指定文件格式的字段和行处理选项。 您可以通过指定一个或多个 linesTerminatedByfieldsTerminatedByfieldsEnclosedByfieldsOptionallyEnclosedfieldsEscapedBy 选项来更改设置,从而将所选方言用作进一步自定义的基础。 默认方言映射到使用 SELECT...INTO OUTFILE 语句创建的文件,并使用该语句的默认设置。 这是 MySQL Shell 的表导出实用程序生成的输出文件的默认值。 其他方言可用于适应 CSV 文件(在 DOS 或 UNIX 系统上创建)、TSV 文件和 JSON 数据。 为每个方言应用的设置如下:

表 11.2 并行表导入实用程序的方言设置

dialect

linesTerminatedBy

fieldsTerminatedBy

fieldsEnclosedBy

fieldsOptionallyEnclosed

fieldsEscapedBy

default

[LF]

[TAB]

[空]

false

\

csv

[CR][LF]

,

''

true

\

csv-unix

[LF]

,

''

false

\

tsv

[CR][LF]

[TAB]

''

true

\

json

[LF]

[LF]

[空]

false

[空]


注意
  1. 方言的回车符和换行符值与操作系统无关。

  2. 如果使用 linesTerminatedByfieldsTerminatedByfieldsEnclosedByfieldsOptionallyEnclosedfieldsEscapedBy 选项,则根据命令解释器的转义约定,如果在选项值中使用反斜杠字符 (\),则可能需要将其加倍。

  3. 与带有 LOAD DATA 语句的 MySQL 服务器一样,MySQL Shell 不会验证您指定的字段和行处理选项。 为这些选项选择不准确会导致数据部分地和/或不正确地导入到错误的字段中。 请务必在开始导入之前验证您的设置,并在之后验证结果。

linesTerminatedBy:“字符

一个或多个字符(或空字符串),用于终止一个或多个输入数据文件中的每一行。 默认值与指定方言相同,如果省略方言选项,则为换行符 (\n)。 此选项等效于 LOAD DATA 语句的 LINES TERMINATED BY 选项。 请注意,该实用程序没有提供 LOAD DATA 语句的 LINES STARTING BY 选项的等效项,该选项设置为字符串。

fieldsTerminatedBy:“字符

一个或多个字符(或空字符串),用于终止一个或多个输入数据文件中的每个字段。 默认值与指定方言相同,如果省略方言选项,则为制表符 (\t)。 此选项等效于 LOAD DATA 语句的 FIELDS TERMINATED BY 选项。

fieldsEnclosedBy:“字符

一个字符(或空字符串),用于将一个或多个输入数据文件中的每个字段括起来。 默认值与指定方言相同,如果省略方言选项,则为空字符串。 此选项等效于 LOAD DATA 语句的 FIELDS ENCLOSED BY 选项。

fieldsOptionallyEnclosed:[true|false]

fieldsEnclosedBy 指定的字符是将一个或多个输入数据文件中的所有字段括起来 (false),还是仅在某些情况下将字段括起来 (true)。 默认值与指定方言相同,如果省略方言选项,则为 false。 此选项使 fieldsEnclosedBy 选项等效于 LOAD DATA 语句的 FIELDS OPTIONALLY ENCLOSED BY 选项。

fieldsEscapedBy:“字符

在输入数据文件中启动转义序列的字符。 如果未提供,则不会进行转义序列解释。 默认值与指定方言相同,如果省略方言选项,则为反斜杠 (\)。 此选项等效于 LOAD DATA 语句的 FIELDS ESCAPED BY 选项。

characterSet:“字符集

此选项指定在导入期间解释输入数据所用的字符集编码。 将选项设置为 binary 表示在导入期间不进行任何转换。 如果省略此选项,则导入将使用 character_set_database 系统变量指定的字符集来解释输入数据。

bytesPerChunk:“大小

对于多个输入数据文件的列表,此选项不可用。 对于单个输入数据文件,此选项指定线程为对目标服务器的每次 LOAD DATA 调用发送的字节数(加上到达行尾所需的任何其他字节)。 该实用程序将数据分配为此大小的块,供线程提取并发送到目标服务器。 块大小可以指定为字节数,也可以使用后缀 k(千字节)、M(兆字节)、G(吉字节)。 例如,bytesPerChunk="2k" 使线程发送大约 2 千字节的块。 最小块大小为 131072 字节,默认块大小为 50M。

threads:数字

用于将输入文件中数据发送到目标服务器的最大并行线程数。 如果您没有指定线程数,则默认最大值为 8。对于多个输入数据文件的列表,该实用程序会创建指定或最大数量的线程。 对于单个输入数据文件,该实用程序会使用以下公式计算要创建的适当线程数(最多不超过此最大值):

min{max{1, threads}, chunks}}

其中,threads 是最大线程数,chunks 是数据将被分割成的块数,其计算方法是用文件大小除以 bytesPerChunk 大小,然后加 1。此计算可确保如果最大线程数超过实际发送的块数,则该实用程序不会创建不必要的线程。

压缩文件不能分割成块,因此该实用程序使用其并行连接来一次上传多个文件。如果只有一个输入数据文件,则压缩文件的上传只能使用一个连接。

maxRate: "rate"

每个线程每秒数据吞吐量的最大限制(以字节为单位)。如果您需要避免网络饱和或客户端主机或目标服务器的 I/O 或 CPU 饱和,请使用此选项。最大速率可以指定为字节数,或使用后缀 k(千字节)、M(兆字节)、G(千兆字节)。例如,maxRate="5M" 将每个线程限制为每秒 5MB 的数据,对于 8 个线程,传输速率为 40MB/秒。默认值为 0,表示没有限制。

showProgress: [ true | false ]

显示 (true) 或隐藏 (false) 导入的进度信息。如果标准输出是终端 (tty),则默认值为 true,否则为 false

sessionInitSql: 字符串列表

要在用于将数据加载到目标 MySQL 实例的每个客户端会话开始时运行的 SQL 语句列表。您可以使用此选项更改会话变量。例如,以下语句在导入过程中跳过目标 MySQL 实例上用于该实用程序的会话的二进制日志记录,并增加可用于索引创建的线程数

sessionInitSQL: ["SET SESSION sql_log_bin=0;", "SET SESSION innodb_ddl_threads=8,"]

如果在运行 SQL 语句时发生错误,则导入将停止并返回错误消息。

适用于 Oracle Cloud Infrastructure 的选项

MySQL Shell 支持导入存储在 Oracle Cloud Infrastructure 对象存储桶中的输入数据文件。

osBucketName: "字符串"

输入数据文件所在的 Oracle Cloud Infrastructure 对象存储桶的名称。默认情况下,使用位于 ~/.oci/config 的 Oracle Cloud Infrastructure CLI 配置文件中的 [DEFAULT] 配置文件来建立与存储桶的连接。您可以使用 ociConfigFileociProfile 选项替换用于连接的备用配置文件。有关设置 CLI 配置文件的说明,请参阅SDK 和 CLI 配置文件

osNamespace: "字符串"

osBucketName 命名的对象存储桶所在的 Oracle Cloud Infrastructure 命名空间。对象存储桶的命名空间显示在 Oracle Cloud Infrastructure 控制台中存储桶详细信息页面的“存储桶信息”选项卡中,也可以使用 Oracle Cloud Infrastructure 命令行界面获取。

ociConfigFile: "字符串"

包含用于连接的配置文件的 Oracle Cloud Infrastructure CLI 配置文件,而不是默认位置 ~/.oci/config 中的配置文件。

ociProfile: "字符串"

用于连接的 Oracle Cloud Infrastructure 配置文件的配置文件名称,而不是用于连接的 Oracle Cloud Infrastructure CLI 配置文件中的 [DEFAULT] 配置文件。

ociAuth: "字符串"

连接到 Oracle Cloud Infrastructure 时使用的身份验证方法。此选项需要使用有效值配置 osBucketName

可以使用以下选项

适用于 S3 兼容服务的选项

MySQL Shell 支持导入存储在 S3 兼容存储桶(例如 Amazon Web Services (AWS) S3)中的输入数据文件。

注意

MySQL Shell 支持在命令行选项、环境变量和配置文件中配置 AWS S3。命令行选项会覆盖环境变量、配置文件和默认选项。

有关配置要求的信息,请参阅第 4.7 节“云服务配置”

s3BucketName: "字符串"

转储文件所在的 S3 存储桶的名称。默认情况下,位于 ~/.aws/ 的 Amazon Web Services (AWS) CLI configcredentials 文件中的 default 配置文件用于建立与 S3 存储桶的连接。您可以使用 s3ConfigFiles3CredentialsFile 选项替换用于连接的备用配置和凭据。有关安装和配置 AWS CLI 的说明,请参阅AWS CLI 入门

s3CredentialsFile: "字符串"

包含用于连接的用户凭据的凭据文件,而不是默认位置 ~/.aws/credentials 中的文件。通常,凭据文件包含用于连接的 aws_access_key_idaws_secret_access_key

s3ConfigFile: "字符串"

包含用于连接的配置文件的 AWS CLI 配置文件,而不是默认位置 ~/.aws/config 中的文件。通常,配置文件包含用于连接的区域和输出类型。

s3Profile: "字符串"

用于连接的 s3 CLI 配置文件的配置文件名称,而不是用于连接的 AWS CLI 配置文件中的 default 配置文件。

s3Region: "字符串"

用于连接的区域的名称。

s3EndpointOverride: "字符串"

要使用的端点的 URL,而不是默认端点。

连接到 Oracle Cloud Infrastructure S3 兼容性 API 时,端点的格式如下:https://namespace.compat.objectstorage.region.oraclecloud.com。将 namespace 替换为对象存储命名空间,将 region 替换为您的区域标识符。例如,美国东部(阿什本)地区的区域标识符为 us-ashburn-1

对于美国东部(阿什本)地区名为 axaxnpcrorw5 的命名空间

https://axaxnpcrorw5.compat.objectstorage.us-ashburn-1.oraclecloud.com.

适用于 Microsoft Azure Blob 存储的选项

MySQL Shell 支持从 Microsoft Azure Blob 存储导入。

注意

MySQL Shell 支持在命令行选项、环境变量和配置文件中配置 Microsoft Azure Blob 存储。命令行选项会覆盖环境变量和配置文件。

有关配置要求和配置类型优先级的顺序的信息,请参阅第 4.7 节“云服务配置”

azureContainerName: "字符串"

必需。要从中导入表的 Azure 容器的名称。该容器必须存在。

azureConfigFile: "字符串"

可选。包含存储连接参数的配置文件,而不是默认位置(例如 ~/.azure/config)中的文件。如果未定义,则使用默认配置文件。

必须定义 azureContainerName,并且不能为空。

azureStorageAccount: "字符串"

可选。要用于操作的 Azure 存储帐户的名称。

azureStorageSasToken: "字符串"

可选。要用于操作身份验证的 Azure 共享访问签名 (SAS) 令牌,而不是密钥。