本文介绍如何通过LOAD DATA将本地数据导入AnalyticDB MySQL数仓版(3.0)

语法

LOAD DATA LOCAL 
    INFILE 'file_name' 
    [REPLACE | IGNORE] 
    INTO TABLE table_name 
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string'] 
        [[OPTIONALLY] ENCLOSED BY 'char'] 
    ]
    [LINES
        [TERMINATED BY 'string'] 
    ]
    [IGNORE number {LINES | ROWS}] 
    [(column_name_or_user_var
    [, column_name_or_user_var] ...)]    

参数

参数说明
LOAD DATA LOCAL INFILE进行本地文件导入操作。
file_name本地文件的路径,包含文件地址和文件名。
说明 如果file_name使用相对路径,则是相对于客户端程序启动时的路径。
table_nameAnalyticDB MySQL中目标表名。
REPLACE导入数据时,遇到主键重复则强制用当前数据覆盖已有数据。
IGNORE导入数据时,遇到主键重复或者数据问题时则自动忽略失败行,部分行导入失败但不影响其他行导入。
[FIELDS] TERMINATED BY 'string'定义列分隔符,默认为\t,与MySQL一致。
[FIELDS] ENCLOSED BY 'char'定义每列数据的enclosed符号。

例如,某一列数据为"a",定义enclosed by '"'后,导入数据时先将"a"前后的"移除,然后导入数据。

[LINES] TERMINATED BY 'string'定义行分隔符,默认为\n
IGNORE number LINES设置导入数据时忽略开始的某几行。

例如IGNORE 1 LINES,导入数据时忽略第一行数据,即第一行数据不会导AnalyticDB MySQL

(column_name_or_user_var,...)设置导入的列,如果不设置,默认按照列的顺序来导入数据。
  • 如果导入列数 > 目标表的列数,则系统自动忽略多余的列。
  • 如果导入列数 < 目标表的列数,则后续缺少的列将自动填充默认值。

注意事项

  • 客户端开启local-infile

    以mysql-client为例,您需要在my.cnf文件中增加以下配置开启local-infile功能。

    cat ~/.my.cnf
    [mysqld]
    local-infile
    [mysql]
    local-infile                    

    更多my.cnf文件信息,请参见MySQL官方文档

  • 导入数据时无法保证操作的原子性。
    • IGNORE模式下,忽略导入失败的数据行。
    • REPLACE模式下,一旦有数据行导入失败,系统将中止后续INSERT操作,因此可能存在部分行数据导入,部分行数据未导入的情况。
  • 支持通过SHOW WARNINGS命令,查看失败行的错误信息。

示例

将本地文件out.bak中的数据导入AnalyticDB MySQLtest表中。out.bak文件中共有5000行数据,列分隔符为\t,行分隔符\n,其中第8行数据存在问题,如下所示。

1 bb
2 bb
3 bb
4 bb
5 bb
6 bb
7 bb
bb   8
9 bb
...          
  1. 连接AnalyticDB for MySQL集群,通过CREATE DATABASECREATE TABLE,在adb_demo数据库下创建表test表,从本地文件导入的数据将存储在test表中。
    CREATE TABLE test (
    a int NOT NULL DEFAULT '0', 
    b varchar NOT NULL, 
    PRIMARY KEY (a) 
    ) DISTRIBUTED by hash(a);                
  2. 在mysql-client中执行LOAD DATA命令将本地文件out.bak中的数据导入AnalyticDB MySQLtest表中。
    • IGNORE模式下执行LOAD DATA命令,部分行导入失败但不影响其他行导入。示例如下:
      load data local infile '~/out.bak' ignore into table test FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' ;
      查询导入结果:
      select count(1) from test;
      +----------+
      | count(1) |
      +----------+
      |     4999 |
      +----------+
      1 row in set (0.14 sec)
    • REPLACE模式下执行LOAD DATA命令,部分行导入失败后立即终止后续导入操作。示例如下:
      load data local infile '~/out.bak' replace into table test FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' ;
      执行结果如下:
      ERROR 1064 (42000): [13000, 2019061210070703000511314203303000266] syntax error :syntax error => IDENTIFIER is not value type pos:34 row: 0 and ceil:0
    • 执行LOAD DATA命令,导入数据时跳过前10条数据。示例如下:
      load data local infile '~/out.bak' replace into table test FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' IGNORE 10 LINES;
      返回结果如下:
      Query OK, 4990 rows affected (0.37 sec)