LOAD DATA

Description

The LOAD DATA statement reads rows from a text file into a table at a very high speed. The file can be read from the server host or a S3 compatible object storage. LOAD DATA is the complement of SELECT … INTO OUTFILE. To write data from a table to a file, use SELECT ... INTO OUTFILE. To read the file back into a table, use LOAD DATA. The syntax of the FIELDS and LINES clauses is the same for both statements.

Syntax

  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}]

Input File Location

Currently MatrixOne only supports to load data on server host, and the file_name must be an absolute path name for MatrixOne to locate it.

IGNORE LINES

The IGNORE number LINES clause can be used to ignore lines at the start of the file. For example, you can use IGNORE 1 LINES to skip an initial header line containing column names:

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

Field and Line Handling

For both the LOAD DATA and SELECT ... INTO OUTFILE statements, the syntax of the FIELDS and LINES clauses is the same. Both clauses are optional, but FIELDS must precede LINES if both are specified.

If you specify a FIELDS clause, each of its subclauses (TERMINATED BY, [OPTIONALLY] ENCLOSED BY, and ESCAPED BY) is also optional, except that you must specify at least one of them. Arguments to these clauses are permitted to contain only ASCII characters.

If you specify no FIELDS or LINES clause, the defaults are the same as if you had written this:

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

FIELDS TERMINATED BY

FIELDS TERMINATED BY specifies the delimiter for a field. The FIELDS TERMINATED BY values can be more than one character.

For example, to read the comma-delimited file, the correct statement is:

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

If instead you tried to read the file with the statement shown following, it would not work because it instructs LOAD DATA to look for tabs between fields:

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

The likely result is that each input line would be interpreted as a single field. You may encounter an error of "ERROR 20101 (HY000): internal error: the table column is larger than input data column".

FIELDS ENCLOSED BY

FIELDS TERMINATED BY option specifies the character enclose the input values. ENCLOSED BY value must be a single character. If the input values are not necessarily enclosed within quotation marks, use OPTIONALLY before the ENCLOSED BY option.

For example, if some input values are enclosed within quotation marks, some are not:

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

FIELDS ESCAPED BY

ESCAPE BY option controls how to read or write special characters. FIELDS ESCAPED BY values must be a single character.

For input, if the FIELDS ESCAPED BY character is not empty, occurrences of that character are stripped and the following character is taken literally as part of a field value. Some two-character sequences that are exceptions, where the first character is the escape character. These sequences are shown in the following table (using \ for the escape character). The rules for NULL handling are described later in this section. If the FIELDS ESCAPED BY character is empty, escape-sequence interpretation does not occur.

CharacterEscape Sequence
\0An ASCII NUL (X’00’) character
\bA backspace character
\nA newline (linefeed) character
\rA carriage return character
\tA tab character.
\ZASCII 26 (Control+Z)
\NNULL

For example, if some input values are special character \, you can use escape by as:

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

LINES TERMINATED BY

LINES TERMINATED BY specifies the delimiter for the a line. The LINES TERMINATED BY values can be more than one character.

For example, if the lines in a csv file are terminated by carriage return/newline pairs, you can load it with 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

If all the input lines have a common prefix that you want to ignore, you can use LINES STARTING BY ‘prefix_string’ to skip the prefix and anything before it. If a line does not include the prefix, the entire line is skipped. Suppose that you issue the following statement:

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

If the data file looks like this:

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

The resulting rows are (“abc”,1) and (“def”,2). The third row in the file is skipped because it does not contain the prefix.

Supported file formats

In MatrixOne’s current release, LOAD DATA supports CSV(comma-separated values) format and JSONLines format file. See full tutorials for loading csv and jsonline.

Examples

The SSB Test is an example of LOAD DATA syntax. Complete a SSB Test with MatrixOne

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

The above statement means: load the lineorder_flat.tbl data set under the directory path /ssb-dbgen-path/ into the MatrixOne data table lineorder_flat.

You can also refer to the following syntax examples to quickly understand LOAD DATA:

Example 1: LOAD CSV

Simple example

The data in the file locally named char_varchar.csv is as follows:

  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. """"""|""""""|""""""|""""""

Create a table named t1 in MatrixOne:

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

Load the data file into table t1:

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

The query result is as follows:

  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)

Add conditional Example

Following the example above, you can modify the LOAD DATA statement and add LINES STARTING BY 'aa' ignore 10 lines; at the end of the statement to experience the difference:

  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;

The query result is as follows:

  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)

As you can see, the query result ignores the first line and and ignores the common prefix aa.

For more information on loding csv, see Import the .csv data.

Example 2: LOAD JSONLines

Simple example

The data in the file locally named jsonline_array.jl is as follows:

  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]

Create a table named t1 in MatrixOne:

  1. mysql> drop table if exists t1;
  2. Query OK, 0 rows affected (0.01 sec)
  4. 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);
  5. Query OK, 0 rows affected (0.03 sec)

Load the data file into table t1:

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

The query result is as follows:

  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)

Add conditional Example

Following the example above, you can modify the LOAD DATA statement and add ignore 1 lines at the end of the statement to experience the difference:

  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;

The query result is as follows:

  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)

As you can see, the query result ignores the first line.

For more information on loding JSONLines, see Import the JSONLines data.

Constraints

  1. LOAD DATA doesn’t support to load files on the client host yet.
  2. The REPLACE and IGNORE modifiers control handling of new (input) rows that duplicate existing table rows on unique key values (PRIMARY KEY or UNIQUE index values) are not supported in MatrixOne yet.
  3. Only absolute file path is supported.
  4. Input pre-pressing with SET is supported very limitedly. Only SET columns_name=nullif(expr1,expr2) is supported.