LOAD DATA

概述

LOAD DATA 语句可以极快地将文本文件中的行读入表中。你可以从服务器主机或 S3 兼容对象存储读取该文件。LOAD DATASELECT … INTO OUTFILE 相反的操作。

  • 将文件读回表中,使用 LOAD DATA
  • 将表中的数据写入文件,使用 SELECT ... INTO OUTFILE
  • FIELDSLINES 子句的语法对于 LOAD DATASELECT ... INTO OUTFILE 这两个语句的使用方式一致,使用 Fields 和 Lines 参数来指定如何处理数据格式。

语法结构

  1. > LOAD DATA
  2. INFILE 'file_name'
  3. INTO TABLE tbl_name
  4. [{FIELDS | COLUMNS}
  5. [TERMINATED BY 'string']
  6. [[OPTIONALLY] ENCLOSED BY 'char']
  7. [ESCAPED BY 'char']
  8. ]
  9. [LINES
  10. [STARTING BY 'string']
  11. [TERMINATED BY 'string']
  12. ]
  13. [IGNORE number {LINES | ROWS}]

参数解释

上述语法结构中的参数解释如下:

INFILE

MatrixOne 目前只支持加载服务器主机上的数据,file_name 必须是 MatrixOne 所在的服务器主机上存放文件的绝对路径名称。

IGNORE LINES

IGNORE number LINES 子句可用于忽略文件开头的行。例如,你可以使用 IGNORE 1 LINES 跳过包含列名的初始标题行:

  1. LOAD DATA INFILE '/tmp/test.txt' INTO TABLE table1 IGNORE 1 LINES;

FIELDS 和 LINES 参数说明

使用 FIELDSLINES 参数来指定如何处理数据格式。

对于 LOAD DATASELECT ... INTO OUTFILE 语句,FIELDSLINES 子句的语法是相同的。这两个子句都是可选的,但如果两者都指定,则 FIELDS 必须在 LINES 之前。

如果指定 FIELDS 子句,那么 FIELDS 的每个子句(TERMINATED BY[OPTIONALLY] ENCLOSED BYESCAPED BY)也是可选的,除非你必须至少指定其中一个。

LOAD DATA 也支持使用十六进制 ASCII 字符表达式或二进制 ASCII 字符表达式作为 FIELDS ENCLOSED BYFIELDS TERMINATED BY 的参数。

如果不指定处理数据的参数,则使用默认值如下:

  1. FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'

意义如下:

  • FIELDS TERMINATED BY ',':以, 作为分隔符
  • ENCLOSED BY '"':使用双引号把各个字段括起来
  • LINES TERMINATED BY '\n':以 ‘\n’ 为行间分隔符

FIELDS TERMINATED BY

FIELDS TERMINATED BY 表示字段与字段之间的分隔符,使用 FIELDS TERMINATED BY 就可以指定每个数据的分隔符号。

FIELDS TERMINATED BY 指定的值可以超过一个字符。

  • 正确示例

例如,读取使用逗号分隔的文件,语法是:

  1. LOAD DATA INFILE 'data.txt' INTO TABLE table1
  2. FIELDS TERMINATED BY ',';
  • 错误示例

如果你使用如下所示的语句读取文件,将会产生报错,因为它表示的是 LOAD DATA 查找字段之间的制表符:

  1. LOAD DATA INFILE 'data.txt' INTO TABLE table1
  2. FIELDS TERMINATED BY '\t';

这样可能会导致结果被解释为每个输入行都是一个字段,你可能会遇到 ERROR 20101 (HY000): internal error: the table column is larger than input data column 错误。

FIELDS ENCLOSED BY

FIELDS TERMINATED BY 指定的值包含输入值的字符。ENCLOSED BY 指定的值必须是单个字符;如果输入值不一定包含在引号中,需要在 ENCLOSED BY 选项之前使用 OPTIONALLY

如下面的例子所示,即表示一部分输入值用可以用引号括起来,另一些可以不用引号括起来:

  1. LOAD DATA INFILE 'data.txt' INTO TABLE table1
  2. FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';

如果 ENCLOSED BY 前不加 OPTIONALLY,比如说,ENCLOSED BY '"' 就表示使用双引号把各个字段都括起来。

FIELDS ESCAPED BY

FIELDS ESCAPED BY 用于控制如何写入或读取特殊字符。FIELDS ESCAPED BY 值必须是单个字符。如果 FIELDS ESCAPED BY 字符不是空字符,则该字符将被去除,同时保留其后一个字符。

一些例外的双字符序列,其中第一个字符作为转义字符。

这些序列如下表所示(使用 \ 作为转义字符)。

字符转义序列
\0反转义成 0(0x00)
\b回车字符
\n换行 (换行) 字符
\r回车字符
\tTab 字符
\ZASCII 26 (Control+Z)
\NNULL

例如,如果某些输入值是特殊字符 “\”,则可以使用 ESCAPE BY

  1. LOAD DATA INFILE 'data.txt' INTO TABLE table1
  2. FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\';

如果 FIELDS ESCAPED BY 字符为空,则不会进行转义序列。

LINES TERMINATED BY

LINES TERMINATED BY 用于指定一行的分隔符。LINES TERMINATED BY 值可以超过一个字符。

例如,如果 csv 文件中的行以回车符/换行符对结束,你在加载它时,可以使用 LINES TERMINATED BY '\r\n'

  1. LOAD DATA INFILE 'data.txt' INTO TABLE table1
  2. FIELDS TERMINATED BY ',' ENCLOSED BY '"'
  3. LINES TERMINATED BY '\r\n';

LINE STARTING BY

如果所有输入行都有一个你想忽略的公共前缀,你可以使用 LINES STARTING BY ‘prefix_string’ 来忽略前缀和前缀之前的任何内容。

如果一行不包含前缀,则跳过整行。如下语句所示:

  1. LOAD DATA INFILE '/tmp/test.txt' INTO TABLE table1
  2. FIELDS TERMINATED BY ',' LINES STARTING BY 'xxx';

如果数据文件是如下样式:

  1. xxx"abc",1
  2. something xxx"def",2
  3. "ghi",3

则输出的结果行是 (“abc”,1) 和 (“def”,2)。文件中的第三行由于没有前缀,则被忽略。

支持的文件格式

在 MatrixOne 当前版本中,LOAD DATA 支持 CSV 格式和 JSONLines 格式文件。

有关导入这两种格式的文档,参见导入. csv 格式数据导入 JSONLines 数据

示例

你可以在 SSB 测试中了解 LOAD DATA 语句的用法,参见完成 SSB 测试

语法示例如下:

  1. > LOAD DATA INFILE '/ssb-dbgen-path/lineorder_flat.tbl ' INTO TABLE lineorder_flat;

上面这行语句表示:将 /ssb-dbgen-path/ 这个目录路径下的 lineorder_flat.tbl 数据集加载到 MatrixOne 的数据表 lineorder_flat 中。

你也可以参考以下语法示例,来快速了解 LOAD DATA

示例 1:LOAD CSV

简单导入示例

本地命名为 char_varchar.csv 文件内数据如下:

  1. a|b|c|d
  2. "a"|"b"|"c"|"d"
  3. 'a'|'b'|'c'|'d'
  4. "'a'"|"'b'"|"'c'"|"'d'"
  5. "aa|aa"|"bb|bb"|"cc|cc"|"dd|dd"
  6. "aa|"|"bb|"|"cc|"|"dd|"
  7. "aa|||aa"|"bb|||bb"|"cc|||cc"|"dd|||dd"
  8. "aa'|'||aa"|"bb'|'||bb"|"cc'|'||cc"|"dd'|'||dd"
  9. aa"aa|bb"bb|cc"cc|dd"dd
  10. "aa"aa"|"bb"bb"|"cc"cc"|"dd"dd"
  11. "aa""aa"|"bb""bb"|"cc""cc"|"dd""dd"
  12. "aa"""aa"|"bb"""bb"|"cc"""cc"|"dd"""dd"
  13. "aa""""aa"|"bb""""bb"|"cc""""cc"|"dd""""dd"
  14. "aa""|aa"|"bb""|bb"|"cc""|cc"|"dd""|dd"
  15. "aa""""|aa"|"bb""""|bb"|"cc""""|cc"|"dd""""|dd"
  16. |||
  17. ||||
  18. ""|""|""|
  19. """"|""""|""""|""""
  20. """"""|""""""|""""""|""""""

在 MatrixOne 中建表:

  1. mysql> drop table if exists t1;
  2. Query OK, 0 rows affected (0.01 sec)
  3. mysql> create table t1(
  4. -> col1 char(225),
  5. -> col2 varchar(225),
  6. -> col3 text,
  7. -> col4 varchar(225)
  8. -> );
  9. Query OK, 0 rows affected (0.02 sec)

将数据文件导入到 MatrixOne 中的表 t1:

  1. load data infile '<your-local-file-path>/char_varchar.csv' into table t1 fields terminated by'|';

查询结果如下:

  1. mysql> select * from t1;
  2. +-----------+-----------+-----------+-----------+
  3. | col1 | col2 | col3 | col4 |
  4. +-----------+-----------+-----------+-----------+
  5. | a | b | c | d |
  6. | a | b | c | d |
  7. | 'a' | 'b' | 'c' | 'd' |
  8. | 'a' | 'b' | 'c' | 'd' |
  9. | aa|aa | bb|bb | cc|cc | dd|dd |
  10. | aa| | bb| | cc| | dd| |
  11. | aa|||aa | bb|||bb | cc|||cc | dd|||dd |
  12. | aa'|'||aa | bb'|'||bb | cc'|'||cc | dd'|'||dd |
  13. | aa"aa | bb"bb | cc"cc | dd"dd |
  14. | aa"aa | bb"bb | cc"cc | dd"dd |
  15. | aa"aa | bb"bb | cc"cc | dd"dd |
  16. | aa""aa | bb""bb | cc""cc | dd""dd |
  17. | aa""aa | bb""bb | cc""cc | dd""dd |
  18. | aa"|aa | bb"|bb | cc"|cc | dd"|dd |
  19. | aa""|aa | bb""|bb | cc""|cc | dd""|dd |
  20. | | | | |
  21. | | | | |
  22. | | | | |
  23. | " | " | " | " |
  24. | "" | "" | "" | "" |
  25. +-----------+-----------+-----------+-----------+
  26. 20 rows in set (0.00 sec)

增加条件导入示例

沿用上面的简单示例,你可以修改一下 LOAD DATA 语句,在末尾增加条件 LINES STARTING BY 'aa' ignore 10 lines;

  1. delete from t1;
  2. load data infile '<your-local-file-path>/char_varchar.csv' into table t1 fields terminated by'|' LINES STARTING BY 'aa' ignore 10 lines;

查询结果如下:

  1. mysql> select * from t1;
  2. +---------+---------+---------+---------+
  3. | col1 | col2 | col3 | col4 |
  4. +---------+---------+---------+---------+
  5. | aa"aa | bb"bb | cc"cc | dd"dd |
  6. | aa""aa | bb""bb | cc""cc | dd""dd |
  7. | aa""aa | bb""bb | cc""cc | dd""dd |
  8. | aa"|aa | bb"|bb | cc"|cc | dd"|dd |
  9. | aa""|aa | bb""|bb | cc""|cc | dd""|dd |
  10. | | | | |
  11. | | | | |
  12. | | | | |
  13. | " | " | " | " |
  14. | "" | "" | "" | "" |
  15. +---------+---------+---------+---------+
  16. 10 rows in set (0.00 sec)

可以看到,查询结果忽略了前 10 行,并且忽略了公共前缀 aa。

有关如何导入 CSV 格式文件的详细步骤,参见导入. csv 格式数据

示例 2:LOAD JSONLines

简单导入示例

本地命名为 jsonline_array.jl 文件内数据如下:

  1. [true,1,"var","2020-09-07","2020-09-07 00:00:00","2020-09-07 00:00:00","18",121.11,["1",2,null,false,true,{"q":1}],"1qaz",null,null]
  2. ["true","1","var","2020-09-07","2020-09-07 00:00:00","2020-09-07 00:00:00","18","121.11",{"c":1,"b":["a","b",{"q":4}]},"1aza",null,null]

在 MatrixOne 中建表:

  1. mysql> drop table if exists t1;
  2. Query OK, 0 rows affected (0.01 sec)
  3. mysql> create table t1(col1 bool,col2 int,col3 varchar(100), col4 date,col5 datetime,col6 timestamp,col7 decimal,col8 float,col9 json,col10 text,col11 json,col12 bool);
  4. Query OK, 0 rows affected (0.03 sec)

将数据文件导入到 MatrixOne 中的表 t1:

  1. load data infile {'filepath'='<your-local-file-path>/jsonline_array.jl','format'='jsonline','jsondata'='array'} into table t1;

查询结果如下:

  1. mysql> select * from t1;
  2. +------+------+------+------------+---------------------+---------------------+------+--------+---------------------------------------+-------+-------+-------+
  3. | col1 | col2 | col3 | col4 | col5 | col6 | col7 | col8 | col9 | col10 | col11 | col12 |
  4. +------+------+------+------------+---------------------+---------------------+------+--------+---------------------------------------+-------+-------+-------+
  5. | true | 1 | var | 2020-09-07 | 2020-09-07 00:00:00 | 2020-09-07 00:00:00 | 18 | 121.11 | ["1", 2, null, false, true, {"q": 1}] | 1qaz | NULL | NULL |
  6. | true | 1 | var | 2020-09-07 | 2020-09-07 00:00:00 | 2020-09-07 00:00:00 | 18 | 121.11 | {"b": ["a", "b", {"q": 4}], "c": 1} | 1aza | NULL | NULL |
  7. +------+------+------+------------+---------------------+---------------------+------+--------+---------------------------------------+-------+-------+-------+
  8. 2 rows in set (0.00 sec)

增加条件导入示例

沿用上面的简单示例,你可以修改一下 LOAD DATA 语句,增加 ignore 1 lines 在语句的末尾,体验一下区别:

  1. delete from t1;
  2. load data infile {'filepath'='<your-local-file-path>/jsonline_array.jl','format'='jsonline','jsondata'='array'} into table t1 ignore 1 lines;

查询结果如下:

  1. mysql> select * from t1;
  2. +------+------+------+------------+---------------------+---------------------+------+--------+-------------------------------------+-------+-------+-------+
  3. | col1 | col2 | col3 | col4 | col5 | col6 | col7 | col8 | col9 | col10 | col11 | col12 |
  4. +------+------+------+------------+---------------------+---------------------+------+--------+-------------------------------------+-------+-------+-------+
  5. | true | 1 | var | 2020-09-07 | 2020-09-07 00:00:00 | 2020-09-07 00:00:00 | 18 | 121.11 | {"b": ["a", "b", {"q": 4}], "c": 1} | 1aza | NULL | NULL |
  6. +------+------+------+------------+---------------------+---------------------+------+--------+-------------------------------------+-------+-------+-------+
  7. 1 row in set (0.00 sec)

可以看到,查询结果忽略掉了第一行。

有关如何导入 JSONLines 格式文件的详细步骤,参见导入 JSONLines 数据

限制

  1. LOAD DATA 暂不支持在客户端主机加载文件,仅支持在服务器主机加载文件。
  2. REPLACEIGNORE 修饰符解决唯一索引的冲突:REPLACE 表示若表中已经存在则用新的数据替换掉旧的数据,而 IGNORE 则表示保留旧的数据,忽略掉新数据。这两个修饰符在 MatrixOne 中尚不支持。
  3. 当前只支持绝对文件路径。
  4. SET 提供不是来源于输入文件的值,MatrixOne 当前部分支持 SET,仅支持 SET columns_name=nullif(expr1,expr2)