跳到主要内容

数据导入

seekdb 支持将外部文件的数据导入到数据库表中，并提供了服务端导入和客户端导入两种方式。本文档介绍这两种导入方式的具体使用方法。

概述​

根据数据文件的读取位置不同，seekdb 支持以下两种主要的数据导入方式：

  1. 服务端导入（推荐）：数据文件位于服务器可访问的位置（如本地文件系统、对象存储等），由数据库服务器直接读取文件进行导入，适用于生产环境和大规模导入。服务端导入基于 FILES() 函数，提供两种使用方式：LOAD DATA FROM FILES() 和 INSERT INTO ... SELECT FROM FILES()。

  2. 客户端导入：数据文件位于客户端本地，通过客户端工具读取文件并传输到服务器。这种方式适用于服务器和文件所在位置网络不通的场景。

说明

对于 seekdb，推荐优先使用服务端导入方式。服务端导入性能更优，且不需要依赖客户端配置。客户端导入方式适用于服务器无法直接访问文件所在位置的场景。

本文仅以简单的示例来说明如何使用 LOAD DATA 语句进行数据导入，更多关于 LOAD DATA 相关语法的详细说明，请参见 LOAD DATA 文档。

使用限制​

带有触发器（Trigger）的表禁止使用 LOAD DATA 语句。

注意事项​

seekdb 通过并行处理技术优化数据导入速率。该操作将数据分成多个子任务并行执行，每个子任务都视为独立的事务，执行顺序是不固定的。因此：

  • 不能保证数据导入过程的全局原子性。
  • 对于没有主键的表，数据写入顺序可能与原文件中的顺序不同。

获取导入权限​

在执行数据导入操作之前，您需要先获得相应的权限。以下是授予执行权限的操作步骤：

  1. 对用户进行授权 FILE 权限。

    GRANT FILE ON *.* TO user_name;

    其中，user_name 是需要执行导入命令的用户。

  2. 授予表的 INSERT 权限。

    GRANT INSERT ON database_name.tbl_name TO user_name;

    其中，database_name 是数据库名称，tbl_name 是表名，user_name 是需要执行导入命令的用户。

  3. 设置 secure_file_priv 系统变量控制导入或导出到文件时可以访问的路径（仅服务端导入方式需要）。

    注意

    由于安全原因，设置系统变量 secure_file_priv 时，只能通过本地 Socket 连接数据库执行修改该全局变量的 SQL 语句。

    示例：

    设置为文件所在目录为 /，表示没有限制，任意路径均可访问。更多信息，请参见 secure_file_priv。

    SET GLOBAL SECURE_FILE_PRIV = "/";

服务端导入（推荐）​

服务端导入是 seekdb 推荐的数据导入方式，支持从服务器可访问的文件位置（本地文件系统、对象存储等）直接导入数据。

服务端导入基于 FILES() 函数，seekdb 提供了两种使用 FILES() 函数进行数据导入的方式：

  1. LOAD DATA FROM FILES()：专门用于数据导入的语句，适合直接导入场景。
  2. INSERT INTO ... SELECT FROM FILES()：使用标准的 INSERT SELECT 语法，结合 FILES() 函数，提供了更灵活的数据处理能力。

本文将以简单示例介绍如何使用以上两种方式进行数据导入。

方式一：使用 LOAD DATA FROM FILES()​

  1. 准备数据文件，将数据文件放置在服务器可访问的位置。

    1. 在服务器上创建数据文件目录并准备测试数据：

      mkdir -p /home/admin/test_data

    2. 使用编辑器创建文件：

      vi /home/admin/test_data/student.csv

    3. 在文件中添加以下内容：

      1,lin,98
      2,hei,90
      3,ali,95

  2. 创建目标表。

    CREATE TABLE student (id INT, name VARCHAR(50), score INT);

  3. 使用 LOAD DATA FROM FILES() 语句导入数据。

    LOAD DATA FROM FILES(
        LOCATION = '/home/admin/test_data/',
        FORMAT = (
            TYPE = 'csv',
            FIELD_DELIMITER = ','),
        PATTERN = 'student.csv')
        INTO TABLE student;

    参数说明：

    • LOCATION：指定文件所在的目录路径。
    • FORMAT：指定文件格式，TYPE = 'csv' 表示 CSV 格式，FIELD_DELIMITER 指定列分隔符为逗号。
    • PATTERN：指定文件匹配模式，'student.csv' 表示只导入名为 student.csv 的文件。

  4. 查看导入结果。

    SELECT * FROM student;

    返回结果如下：

    +------+------+-------+
    | id   | name | score |
    +------+------+-------+
    |    1 | lin  |    98 |
    |    2 | hei  |    90 |
    |    3 | ali  |    95 |
    +------+------+-------+
    3 rows in set

异常处理​

LOAD DATA FROM FILES() 语句支持错误诊断模式，允许将失败的行记录下来，而不是让整个操作因第一个错误而终止。您可以在正常的导入语句后添加 LOG ERRORS 子句来启用错误诊断功能。

示例：

LOAD DATA FROM FILES(
    LOCATION = '/home/admin/test_data/',
    FORMAT = (
        TYPE = 'csv',
        FIELD_DELIMITER = ','),
    PATTERN = 'student.csv')
    INTO TABLE student
    LOG ERRORS
        INTO '/home/admin/error_logs/'
        REJECT LIMIT 10
        BADFILE '/home/admin/bad_files/';

说明：

  • LOG ERRORS：开启错误诊断模式。
  • INTO：指定错误日志存放目录。
  • REJECT LIMIT：允许的最大错误行数，10 表示最多允许 10 行数据导入失败，超过限制后整个导入操作才会终止。
  • BADFILE：指定失败数据行保存的目录。

您可以通过 READ_ERROR_LOG() 函数查看错误日志：

SELECT * FROM READ_ERROR_LOG('/home/admin/error_logs/');

方式二：使用 INSERT INTO ... SELECT FROM FILES()​

  1. 准备数据文件，将数据文件放置在服务器可访问的位置。

    1. 在服务器上创建数据文件目录并准备测试数据（如果之前已创建可跳过此步）：

      mkdir -p /home/admin/test_data

    2. 创建 student.csv 文件（如果文件已存在可跳过此步）：

      vi /home/admin/test_data/student.csv

    3. 在文件中添加以下内容：

      1,lin,98
      2,hei,90
      3,ali,95

  2. 创建目标表。

    CREATE TABLE student (id INT, name VARCHAR(50), score INT);

  3. 使用 INSERT INTO ... SELECT FROM FILES() 语句导入数据。

    INSERT INTO student
    SELECT * FROM FILES(
        LOCATION = '/home/admin/test_data/',
        FORMAT = (
            TYPE = 'csv',
            FIELD_DELIMITER = ','),
        PATTERN = 'student.csv');

  4. 查看导入结果。

    SELECT * FROM student;

    返回结果如下：

    +------+------+-------+
    | id   | name | score |
    +------+------+-------+
    |    1 | lin  |    98 |
    |    2 | hei  |    90 |
    |    3 | ali  |    95 |
    +------+------+-------+
    3 rows in set

说明

INSERT INTO ... SELECT FROM FILES() 使用相同的 FILES() 函数语法，但提供了更灵活的数据处理能力。您可以在 SELECT 语句中使用 WHERE、JOIN、聚合函数等 SQL 功能对数据进行处理后再插入。

客户端导入​

客户端导入方式适用于服务器无法直接访问文件所在位置的场景。数据文件位于客户端本地，通过客户端工具读取文件并传输到服务器。

操作步骤：

  1. 在本地创建测试数据文件。

    1. 在本地 /home/admin/test_data 目录下创建一个名为 test_tbl1.csv 的 CSV 文件。

      vi /home/admin/test_data/test_tbl1.csv

    2. 在文件中添加以下内容：

      1,11
      2,22
      3,33

  2. 启动客户端并启用本地文件导入功能。

    执行以下语句，使用客户端工具连接到 seekdb 数据库。必须通过添加 --local-infile 参数启用从本地文件加载数据的功能。

    mysql --local-infile -hxxx.xxx.xxx.xxx -P2881 -uroot -p****** -Dtest

    注意

    使用 LOAD DATA LOCAL INFILE 功能时，必须在客户端连接时添加 --local-infile 参数。这是 MySQL 客户端的行为，无法通过其他方式修改。如果未添加此参数，执行 LOAD DATA LOCAL INFILE 语句将会报错。

    为了使用 LOAD DATA LOCAL INFILE 功能，请使用支持该功能的客户端工具。

  3. 创建测试表。

    CREATE TABLE test_tbl1(col1 INT, col2 INT);

  4. 在客户端中，执行 LOAD DATA LOCAL INFILE 语句来加载本地数据文件。

    LOAD DATA LOCAL INFILE '/home/admin/test_data/test_tbl1.csv'
    INTO TABLE test_tbl1
    FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\n';

  5. 查看表信息。

    SELECT * FROM test_tbl1;

    返回结果如下：

    +------+------+
    | col1 | col2 |
    +------+------+
    |    1 |   11 |
    |    2 |   22 |
    |    3 |   33 |
    +------+------+
    3 rows in set

相关文档​

  • 更多有关 LOAD DATA 语法的详细信息，请参见 LOAD DATA。
  • 更多有关 secure_file_priv 的详细信息，请参见 secure_file_priv。

Contents

编辑此页