LOAD DATA

The LOAD DATA statement reads rows from a text file into a table at a very high speed.

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

> LOAD DATA [LOCAL]
    INFILE '<file_name>|stage://stage_name/filepath'
    INTO TABLE tbl_name
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number {LINES | ROWS}]
    [SET column_name_1=nullif(column_name_1, expr1), column_name_2=nullif(column_name_2, expr2)...]
    [PARALLEL {'TRUE' | 'FALSE'}]
    [STRICT {'TRUE' | 'FALSE'}]

> LOAD DATA URL S3OPTION {'endpoint'='hdfs://host:port', 'filepath'='/path/file'}
    INTO TABLE tbl_name

Input File Location

  • LOAD DATA INFILE 'file_name': Indicates that the data file to be loaded is on the same machine as the MatrixOne host server. file_name can be the relative path name of the storage location of the file, or it can be the absolute path name.

  • LOAD DATA LOCAL INFILE 'file_name': indicates that the data file to be loaded is not on the same machine as the MatrixOne host server; that is, the data file is on the client server. file_name can be the relative path name of the storage location of the file, or it can be the absolute path name.

Wildcard File Patterns

file_name (and the filepath option) can contain the shell wildcard characters *, ?, and [. When the path contains a wildcard, MatrixOne resolves it to every matching file and loads each file into the target table.

  • When PARALLEL 'TRUE' is specified, each matching file is distributed as a whole-file shard, one reader per file. Because whole files are never split by byte offset, this is also how multiple compressed files are loaded in parallel.

  • IGNORE n LINES is applied to every file, not just the first.

  • A pattern that matches exactly one file behaves the same as naming that file directly.

  • A pattern that matches no files fails with the error the file does not exist in load flow.

Wildcards are resolved against server-side storage (the local file system, S3, or a stage). LOAD DATA LOCAL INFILE reads a single client-side file and does not expand wildcards.

A server-side path in file_name (a local file path, a stage reference, or a path resolved against an object-storage service) can only be opened by the sys account with the moadmin role active in the session. Table-level privileges such as SELECT or INSERT on the target table never grant access to files on the MatrixOne host server, and no permissive fallback mode exists. LOAD DATA LOCAL INFILE and INLINE loads read the file on the client side and are unaffected. For the full scope of this boundary, see SQL File Access.

CHARACTER SET

The grammar accepts a CHARACTER SET name, for example:

LOAD DATA INFILE '/tmp/test.txt' INTO TABLE table1 CHARACTER SET utf8;

The loader does not use this clause to transcode the input file. Prepare text files in the encoding expected by the target columns before loading them.

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:

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

Field and Line Handling

Use the FIELDS and LINES parameters to specify how to handle data formats.

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

If the FIELDS clause is specified, then each of the clauses of FIELDS (TERMINATED BY, [OPTIONALLY] ENCLOSED BY) is also optional, unless you must specify at least one of them.

FIELDS ENCLOSED BY and FIELDS TERMINATED BY take string literals. Hexadecimal and bit literals are not accepted by this grammar.

If you do not specify a parameter for processing data, the following default values are used:

FIELDS TERMINATED BY '\t' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n'

Note

  • FIELDS TERMINATED BY '\t': with and only \t as delimiters.

  • ENCLOSED BY '"': with and only " as the included character.

  • ESCAPED BY '\\':use \ as an escape character, and only as an escape character.

  • LINES TERMINATED BY '\n': Use and only use \n or \r\n as the line separator.

FIELDS TERMINATED BY

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

Examples

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

FIELDS ENCLOSED BY

FIELDS ENCLOSED BY option specifies the character used to 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.

Examples

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

FIELDS ESCAPED BY

FIELDS ESCAPED BY allows you to specify an escape character, with a default value of \\, which means that \ is an escape symbol that is deleted if the FIELDS ESCAPED BY character is not null and subsequent characters will be used literally as part of the field value.

However, some two-character sequences have special meanings, as shown in the table below:

escape sequence

Sequentially represented characters

\0

A NUL byte (0x00)

\b

A backspace character

\n

A newline (linefeed) character

\r

A carriage return character

\t

A tab character

\Z

ASCII 26 (Control+Z)

Examples

  • Examples 1

The contents of data.txt are as follows:

(base) admin@admindeMacBook-Pro case % cat data.txt 
1	a\\b

Connect mo than execute the following statement to import the data.txt contents to t1:

create table t1(n1 int,n2 varchar(255));
load data infile '/Users/admin/test/case/data.txt' into table t1;

mysql> select * from t1;
+------+------+
| n1   | n2   |
+------+------+
|    1 | a\b  |
+------+------+
1 row in set (0.00 sec)

The result of n2 is a\b because the first \ was an escape character and was deleted.

  • Examples 2

The contents of data.txt are as follows:

(base) admin@admindeMacBook-Pro case % cat data.txt 
1	a\\b

Connect mo than execute the following statement to import the data.txt contents to t2:

create table t2(n1 int,n2 varchar(255));
load data infile '/Users/admin/test/case/data.txt' into table t2 fields escaped by 'a';

mysql> select * from t2;
+------+------+
| n1   | n2   |
+------+------+
|    1 | \\b  |
+------+------+
1 row in set (0.00 sec)

The result of n2 is \\b, because here we specified the escape character as a, so a is removed.

  • Examples 3

The contents of data.txt are as follows:

(base) admin@admindeMacBook-Pro case % cat data.txt 
1	a\\b

Connect mo than execute the following statement to import the data.txt contents to t3:

create table t3(n1 int,n2 varchar(255));
load data infile '/Users/admin/test/case/data.txt' into table t3 fields escaped by '';

mysql> SELECT * FROM t3;
+------+------+
| n1   | n2   |
+------+------+
|    1 | a\\b |
+------+------+
1 row in set (0.01 sec)

The result of n2 is a\\b, and when ESCAPED BY is empty, it is read as is without escaping the characters.

  • Examples 4

The contents of data.txt are as follows:

(base) admin@admindeMacBook-Pro case % cat data.txt 
1	a\0b
2	c\bd
3	a\nb
4	a\rb
5	a\tb
6	a\Zb

Connect mo than execute the following statement to import the data.txt contents to t4:

create table t4(n1 int,n2 varchar(255));
load data infile '/Users/admin/test/case/data.txt' into table t4;

mysql> SELECT n1, HEX(n2) AS n2_hex FROM t4 ORDER BY n1;
+------+--------+
| n1   | n2_hex |
+------+--------+
|    1 | 610062 |
|    2 | 630864 |
|    3 | 610A62 |
|    4 | 610D62 |
|    5 | 610962 |
|    6 | 611A62 |
+------+--------+
6 rows in set (0.01 sec)

When n1=1, \0 inserts a NUL byte between a and b;

When n1=2, \b inserts a backspace byte between c and d; it does not delete c;

When n1=3, the result of n2 is a plus a newline b because \n is a newline;

When n1=4, the result of n2 is a plus b after a carriage return, because \r is a carriage return;

When n1=5, \t inserts a tab between a and b;

When n1=6, uppercase \Z inserts ASCII 26 between a and b.

LINES TERMINATED BY

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

Examples

For example, to read a file separated by comma, the syntax is:

LOAD DATA INFILE 'data.txt' INTO TABLE table1
  FIELDS TERMINATED BY ',' ENCLOSED BY '"'
  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:

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

If the data file looks like this:

xxx"abc",1
something xxx"def",2
"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.

SET

MatrixOne only supports SET column_name=nullif(column_name,expr). That is, when column_name = expr, it returns NULL; otherwise, it returns the original value of column_name. For example, SET a=nullif(a,1), if a=1, returns NULL; otherwise, it returns the original value of column a.

By setting the parameter, you can use SET column_name=nullif(column_name,"null") to return the NULL value in the column when loading the file.

Example

The following example loads three rows whose id column contains the literal string null for the third row, and uses SET id=nullif(id,'null') to convert that string into SQL NULL:

```sql
DROP DATABASE IF EXISTS aaa;
CREATE DATABASE aaa;
USE aaa;
CREATE TABLE `user` (`id` varchar(20), `user_name` varchar(255), `sex` varchar(255));
LOAD DATA INLINE FORMAT='csv', DATA='1,weder,man\n2,tom,man\nnull,wederTom,man' INTO TABLE `user` FIELDS TERMINATED BY ',' SET id=nullif(id,'null');
SELECT * FROM `user`;
```

The query returns three rows: (1,weder,man), (2,tom,man), and (NULL,wederTom,man). The third row’s id is NULL because nullif(id,'null') returns NULL when id equals the string null.

For a file-based load, replace the INLINE clause with INFILE '/tmp/test.txt': LOAD DATA INFILE '/tmp/test.txt' INTO TABLE user SET id=nullif(id,'null');.

PARALLEL

For a sizeable well-formed file, such as a JSOLLines file or a CSV file with no line breaks in a line of data, you can use PARALLEL to load the file in parallel to speed up the loading.

For a sufficiently large uncompressed file, parallel loading splits the file at record boundaries so multiple readers can process different ranges. The number and size of those ranges depend on the planner and file contents.

Enable/Disable Parallel Loading Command Line Example:

--  Enable Parallel Loading
load data infile 'file_name' into table tbl_name FIELDS TERMINATED BY '|' ENCLOSED BY '\"' LINES TERMINATED BY '\n' IGNORE 1 LINES PARALLEL 'TRUE';

--  Disable Parallel Loading
load data infile 'file_name' into table tbl_name FIELDS TERMINATED BY '|' ENCLOSED BY '\"' LINES TERMINATED BY '\n' IGNORE 1 LINES PARALLEL 'FALSE';

--  Parallel loading is disabled by default
load data infile 'file_name' into table tbl_name FIELDS TERMINATED BY '|' ENCLOSED BY '\"' LINES TERMINATED BY '\n' IGNORE 1 LINES;

Note

[PARALLEL {'TRUE' | 'FALSE'}] currently only support TRUE or FALSE and are not case-sensitive.

Note: If PARALLEL is omitted, parallel loading is disabled by default for both CSV and JSONLines. When loading a single file in parallel, keep the configured line terminator out of field values so that file splitting does not cut a record.

STRICT

MatrixOne supports the use of the STRICT parameter to specify the way to cut the file in parallel, which is only valid when PARALLEL is TRUE. The default value of STRICT is TRUE, which indicates that the cut is done using read-ahead detection, which not only relies on the line breaks for the segmentation, but also performs a read-ahead to verify that it matches the column definitions of the table, and will process it as a valid segmentation point only if the data meets the column definitions. Only if the data matches the column definition will it be processed as a valid split point. When the parameter is FALSE, it will use the newline character (default is ‘\n’) to cut in the parallel import of the cut file, and in the case of data with newline character, there may be an error in the cut.

Example:

-- Enable pre-reading mode
LOAD DATA INFILE 'file_name' INTO TABLE tbl_name PARALLEL 'TRUE' STRICT 'TRUE';

-- Turn off pre-reading mode
LOAD DATA INFILE 'file_name' INTO TABLE tbl_name PARALLEL 'TRUE' STRICT 'FALSE';

Supported file formats

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

LOAD DATA from Parquet files correctly converts all three Parquet binary decimal encodings (INT32, INT64, FixedLenByteArray) to the native DECIMAL type.

STRING columns in Parquet files can be loaded into nullable VECF32 and VECF64 vector columns. The string values in the Parquet file are automatically converted to the target vector type during import.

DROP DATABASE IF EXISTS str_to_vec_demo;
CREATE DATABASE str_to_vec_demo;
USE str_to_vec_demo;

CREATE TABLE vec_table (
    id INT NOT NULL,
    c_vecf32 VECF32(3),
    c_vecf64 VECF64(3)
);

LOAD DATA INFILE {'filepath'='resources/load_data/string_to_vector_optional.parq', 'format'='parquet'}
INTO TABLE vec_table PARALLEL 'true';

SELECT id, c_vecf32, c_vecf64 FROM vec_table ORDER BY id;

DROP DATABASE str_to_vec_demo;

Note

LOAD DATA supports importing lz4, gz, bz2, zlib, flate, zstd, zip, tar.gz, and tar.bz2. Bare .tar files and other .tar.xx variants (for example .tar.xz) are not supported.

CSV format standard description

For comma-separated files, specify FIELDS TERMINATED BY ','. The following examples illustrate common CSV records; the default field delimiter is a tab.

  1. Each record is on a separate line, separated by a newline character (CRLF):

    aaa,bbb,ccc CRLF
    zzz,yyy,xxx CRLF
    

    Imported into the table as follows:

    +———+———+———+ | col1 | col2 | col3 | +———+———+———+ | aaa | bbb | ccc | | zzz | yyy | xxx | +———+———+———+

  2. The last record in the file can have a terminating newline or no terminating newline (CRLF):

    aaa,bbb,ccc CRLF
    zzz,yyy,xxx
    

    Imported into the table as follows:

    +———+———+———+ | col1 | col2 | col3 | +———+———+———+ | aaa | bbb | ccc | | zzz | yyy | xxx | +———+———+———+

  3. A file can have a header line in the same format as a data record. Use IGNORE 1 LINES to skip it; otherwise, the loader treats it as data. For example:

    field_name,field_name,field_name CRLF
    aaa,bbb,ccc CRLF
    zzz,yyy,xxx CRLF
    

    With IGNORE 1 LINES, the data rows are:

    +————+————+————+ | col1 | col2 | col3 | +————+————+————+ | aaa | bbb | ccc | | zzz | yyy | xxx | +————+————+————+

  4. In the header and each record, there may be one or more fields separated by commas. Whitespace within a field is part of the field. A trailing comma represents an empty last field. For example:

    aaa,bbb,ccc
    

    Or:

    a aa, bbb,cc c
    

    Both examples are correct.

    Imported into the table as follows:

    +———+———+———+ | col1 | col2 | col3 | +———+———+———+ | aaa | bbb | ccc | +———+———+———+

    Or:

    +———+———+———+ | col1 | col2 | col3 | +———+———+———+ | a aa | bbb | cc c | +———+———+———+

  5. Each field can be enclosed in double quotes or not. The parser can also read an unquoted field containing a double quote. For example:

    "aaa","bbb","ccc" CRLF
    zzz,yyy,xxx
    

    Or:

    "aaa","bbb",ccc CRLF
    zzz,yyy,xxx
    

    Both examples are correct.

    Imported into the table as follows:

    +———+———+———+ | col1 | col2 | col3 | +———+———+———+ | aaa | bbb | ccc | | zzz | yyy | xxx | +———+———+———+

  6. Fields containing line breaks (CRLF), double quotes, and commas should be enclosed in double-quotes. For example:

    "aaa","b CRLF
    bb","ccc" CRLF
    zzz,yyy,xxx
    

    Imported into the table as follows:

    +———+———+———+ | col1 | col2 | col3 | +———+———+———+ | aaa | bbb | ccc | | zzz | yyy | xxx | +———+———+———+

  7. Inside a quoted field, two consecutive double quotes represent one literal double quote. For example:

    "aaa","b""bb","ccc"
    

    The quoted field "b""bb" parses as b"bb; to load b""bb, write:

    "aaa","b""""bb","ccc"
    

    Or:

    "aaa",b""bb,"ccc"
    

    Imported into the table as follows:

    +———+———+———+ | col1 | col2 | col3 | +———+———+———+ | aaa | b””bb | ccc | +———+———+———+

Examples

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

> 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:

a|b|c|d
"a"|"b"|"c"|"d"
'a'|'b'|'c'|'d'
"'a'"|"'b'"|"'c'"|"'d'"
"aa|aa"|"bb|bb"|"cc|cc"|"dd|dd"
"aa|"|"bb|"|"cc|"|"dd|"
"aa|||aa"|"bb|||bb"|"cc|||cc"|"dd|||dd"
"aa'|'||aa"|"bb'|'||bb"|"cc'|'||cc"|"dd'|'||dd"
aa"aa|bb"bb|cc"cc|dd"dd
"aa"aa"|"bb"bb"|"cc"cc"|"dd"dd"
"aa""aa"|"bb""bb"|"cc""cc"|"dd""dd"
"aa"""aa"|"bb"""bb"|"cc"""cc"|"dd"""dd"
"aa""""aa"|"bb""""bb"|"cc""""cc"|"dd""""dd"
"aa""|aa"|"bb""|bb"|"cc""|cc"|"dd""|dd"
"aa""""|aa"|"bb""""|bb"|"cc""""|cc"|"dd""""|dd"
|||
||||
""|""|""|
""""|""""|""""|""""
""""""|""""""|""""""|""""""

Create a table named t1 in MatrixOne:

mysql> drop table if exists t1;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t1(
    -> col1 char(225),
    -> col2 varchar(225),
    -> col3 text,
    -> col4 varchar(225)
    -> );
Query OK, 0 rows affected (0.02 sec)

Load the data file into table t1:

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

The query result is as follows:

mysql> select * from t1;
+-----------+-----------+-----------+-----------+
| col1      | col2      | col3      | col4      |
+-----------+-----------+-----------+-----------+
| a         | b         | c         | d         |
| a         | b         | c         | d         |
| 'a'       | 'b'       | 'c'       | 'd'       |
| 'a'       | 'b'       | 'c'       | 'd'       |
| aa|aa     | bb|bb     | cc|cc     | dd|dd     |
| aa|       | bb|       | cc|       | dd|       |
| aa|||aa   | bb|||bb   | cc|||cc   | dd|||dd   |
| aa'|'||aa | bb'|'||bb | cc'|'||cc | dd'|'||dd |
| aa"aa     | bb"bb     | cc"cc     | dd"dd     |
| aa"aa     | bb"bb     | cc"cc     | dd"dd     |
| aa"aa     | bb"bb     | cc"cc     | dd"dd     |
| aa""aa    | bb""bb    | cc""cc    | dd""dd    |
| aa""aa    | bb""bb    | cc""cc    | dd""dd    |
| aa"|aa    | bb"|bb    | cc"|cc    | dd"|dd    |
| aa""|aa   | bb""|bb   | cc""|cc   | dd""|dd   |
|           |           |           |           |
|           |           |           |           |
|           |           |           |           |
| "         | "         | "         | "         |
| ""        | ""        | ""        | ""        |
+-----------+-----------+-----------+-----------+
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:

delete from t1;
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:

mysql> select * from t1;
+---------+---------+---------+---------+
| col1    | col2    | col3    | col4    |
+---------+---------+---------+---------+
| aa"aa   | bb"bb   | cc"cc   | dd"dd   |
| aa""aa  | bb""bb  | cc""cc  | dd""dd  |
| aa""aa  | bb""bb  | cc""cc  | dd""dd  |
| aa"|aa  | bb"|bb  | cc"|cc  | dd"|dd  |
| aa""|aa | bb""|bb | cc""|cc | dd""|dd |
|         |         |         |         |
|         |         |         |         |
|         |         |         |         |
| "       | "       | "       | "       |
| ""      | ""      | ""      | ""      |
+---------+---------+---------+---------+
10 rows in set (0.00 sec)

The query skips the first ten records, then keeps only records containing the aa prefix and removes that prefix.

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:

[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]
["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:

mysql> drop table if exists t1;
Query OK, 0 rows affected (0.01 sec)

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);
Query OK, 0 rows affected (0.03 sec)

Load the data file into table t1:

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

The query result is as follows:

mysql> select * from t1;
+------+------+------+------------+---------------------+---------------------+------+--------+---------------------------------------+-------+-------+-------+
| col1 | col2 | col3 | col4       | col5                | col6                | col7 | col8   | col9                                  | col10 | col11 | col12 |
+------+------+------+------------+---------------------+---------------------+------+--------+---------------------------------------+-------+-------+-------+
| 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  |
| 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  |
+------+------+------+------------+---------------------+---------------------+------+--------+---------------------------------------+-------+-------+-------+
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:

delete from t1;
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:

mysql> select * from t1;
+------+------+------+------------+---------------------+---------------------+------+--------+-------------------------------------+-------+-------+-------+
| col1 | col2 | col3 | col4       | col5                | col6                | col7 | col8   | col9                                | col10 | col11 | col12 |
+------+------+------+------------+---------------------+---------------------+------+--------+-------------------------------------+-------+-------+-------+
| 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  |
+------+------+------+------------+---------------------+---------------------+------+--------+-------------------------------------+-------+-------+-------+
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.

Example 3: LOAD Stage

Simple import example

There is a file t1.csv in the /Users/admin/test directory:

(base) admin@192 test % cat t1.csv 
1	a
2	b
3	c
create table t1(n1 int,n2 varchar(10));
create stage stage_fs url = 'file:///Users/admin/test';
load data infile 'stage://stage_fs/t1.csv' into table t1;

mysql> select * from t1;
+------+------+
| n1   | n2   |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
+------+------+
3 rows in set (0.01 sec)

Add conditional import example

You can add IGNORE 1 LINES at the end of the LOAD DATA statement to skip the first line of the data file.

There is a file t1.csv in the /Users/admin/test directory:

(base) admin@192 test % cat t1.csv 
1	a
2	b
3	c
create table t2(n1 int,n2 varchar(10));
create stage stage_fs1 url = 'file:///Users/admin/test';
load data infile 'stage://stage_fs1/t1.csv' into table t2 ignore 1 lines;

mysql> select * from t2;
+------+------+
| n1   | n2   |
+------+------+
|    2 | b    |
|    3 | c    |
+------+------+
2 rows in set (0.00 sec)

Example 4: LOAD HDFS

Simple Import Example

There is a file t1.csv under /User/ in HDFS:

(base) admin@admindeMBP test % hdfs dfs -cat /user/t1.csv
1	a
2	b
3	c
mysql> create table t1(n1 int,n2 text);
Query OK, 0 rows affected (0.03 sec)

mysql> load data url s3option {'endpoint'='hdfs://127.0.0.1:9000','filepath'='/user/t1.csv'} into table t1;
Query OK, 3 rows affected (0.15 sec)

mysql> select * from t1;
+------+------+
| n1   | n2   |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
+------+------+
3 rows in set (0.01 sec)

Conditional Import Example

You can append IGNORE 1 LINES at the end of the LOAD DATA statement to skip the first line of the data file.

There is a file t1.csv under /User/ in HDFS:

(base) admin@admindeMBP test % hdfs dfs -cat /user/t1.csv
1	a
2	b
3	c
mysql> create table t2(n1 int,n2 varchar(10));
Query OK, 0 rows affected (0.02 sec)

mysql> load data url s3option {'endpoint'='hdfs://127.0.0.1:9000','filepath'='/user/t1.csv'} into table t2 ignore 1 lines;
Query OK, 2 rows affected (0.08 sec)

mysql> select * from t2;
+------+------+
| n1   | n2   |
+------+------+
|    2 | b    |
|    3 | c    |
+------+------+
2 rows in set (0.01 sec)

CSV Comment Support

When creating an external table for CSV files, you can use the 'comment' option in the infile parameters to specify a comment marker. Lines whose raw prefix (before unquoting) matches the comment marker are treated as comments and skipped during reading. This is useful for CSV files that contain metadata or header lines prefixed with a specific character like # or REM.

The comment match is performed on the raw line prefix (the beginning of each line as stored in the file) before any unquoting. A value that is enclosed in quotes (e.g., "#quoted") has a literal " as its raw prefix, not #, so it is treated as data under a # comment marker.

Example:

DROP DATABASE IF EXISTS csvcmt;
CREATE DATABASE csvcmt;
USE csvcmt;

-- Default: no comment marker, every line is data
CREATE EXTERNAL TABLE t_default(a VARCHAR(20), b VARCHAR(20))
INFILE{'filepath'='$resources/external_table_file/csv_comment.csv', 'format'='csv'}
FIELDS TERMINATED BY ',';
SELECT a, b FROM t_default ORDER BY a;

-- comment = '#': lines starting with '#' are skipped
CREATE EXTERNAL TABLE t_hash(a VARCHAR(20), b VARCHAR(20))
INFILE{'filepath'='$resources/external_table_file/csv_comment.csv', 'format'='csv', 'comment'='#'}
FIELDS TERMINATED BY ',';
SELECT a, b FROM t_hash ORDER BY a;

DROP DATABASE csvcmt;

YEAR Type in LOAD DATA

LOAD DATA supports loading into YEAR type columns. YEAR values in the input file are parsed according to the standard MySQL YEAR rules (values 0-69 map to 2000-2069, values 70-99 map to 1970-1999, and four-digit years are used as-is).

Example:

DROP TABLE IF EXISTS load_data_year_t;
CREATE TABLE load_data_year_t (id INT PRIMARY KEY, y YEAR);
LOAD DATA INFILE '$resources/load_data/year.csv'
INTO TABLE load_data_year_t
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES;
SELECT id, CAST(y AS CHAR) AS y FROM load_data_year_t ORDER BY id;
DROP TABLE load_data_year_t;

String Width Strict Enforcement

In strict SQL mode (the default), LOAD DATA rejects values that exceed the declared width of CHAR or VARCHAR columns. This applies to both serial and parallel (PARALLEL 'true') loading modes. Values that fit within the column width are loaded successfully.

Example:

DROP DATABASE IF EXISTS load_string_width;
CREATE DATABASE load_string_width;
USE load_string_width;

CREATE TABLE t_width(c1 VARCHAR(3), c2 CHAR(3));

-- Over-width value rejected (serial load)
LOAD DATA INFILE '$resources/load_data/test_string_width_over.csv' INTO TABLE t_width FIELDS TERMINATED BY ',';
SELECT COUNT(*) FROM t_width;

-- Fitting values load successfully
LOAD DATA INFILE '$resources/load_data/test_string_width_fit.csv' INTO TABLE t_width FIELDS TERMINATED BY ',';
SELECT * FROM t_width;

DROP DATABASE load_string_width;

Parquet String to Vector Conversion

When loading Parquet files, STRING columns can be loaded into nullable VECF32 and VECF64 vector columns. The string values are automatically parsed and converted to the target vector type during import.

Example:

DROP DATABASE IF EXISTS test_string_to_vector;
CREATE DATABASE test_string_to_vector;
USE test_string_to_vector;

CREATE TABLE test_string_to_vector_optional(
    id INT NOT NULL,
    c_vecf32 VECF32(3),
    c_vecf64 VECF64(3)
);

LOAD DATA INFILE {'filepath'='$resources/load_data/string_to_vector_optional.parq', 'format'='parquet'}
INTO TABLE test_string_to_vector_optional PARALLEL 'true';

SELECT id, c_vecf32, c_vecf64 FROM test_string_to_vector_optional ORDER BY id;

DROP DATABASE test_string_to_vector;

Parquet Cross-Type Conversions

When loading Parquet files via LOAD DATA INFILE, MatrixOne supports the following cross-type conversions between Parquet source types and MatrixOne target columns:

Numeric type conversions:

  • Parquet FLOAT / DOUBLE can load into any MatrixOne integer type (TINYINT, SMALLINT, INT, BIGINT, and their unsigned variants) with standard rounding (half away from zero). Out-of-range values are rejected with an overflow error.

  • Parquet FLOAT / DOUBLE can load into BOOL (non-zero → true), JSON, and DECIMAL columns.

  • Parquet BOOLEAN can load into FLOAT / DOUBLE (true → 1.0, false → 0.0), JSON, and DECIMAL.

  • Parquet INT32 / INT64 can load into BOOL (non-zero → true), JSON, and ENUM columns.

  • Parquet DECIMAL logical type can load into integer columns with standard rounding based on the decimal scale.

Date/time type conversions:

  • Parquet DATE can load into TIMESTAMP columns (interpreted as midnight in the session time zone).

  • Parquet TIMESTAMP can load into DATE and TIME columns (the time component is extracted for TIME).

  • Parquet TIME logical type can load into DATETIME columns.

  • TIMESTAMP with isAdjustedToUTC=false now correctly uses the session time zone for wall-clock interpretation.

String-based conversions:

  • Parquet BYTE_ARRAY strings can load into BIT columns (parsed as decimal) and BOOL columns ("true" → true, "0" → false).

Nested type conversions:

  • Parquet FLOAT lists can load into VECF64 columns.

  • Parquet DOUBLE lists can load into VECF32 columns. Values exceeding FLOAT range are rejected.

Additional conversions:

  • Parquet INT32 / INT64 / FLOAT / DOUBLE / BOOLEAN and date/time columns can load into JSON columns with proper JSON representation.

  • Non-DECIMAL integer types (INT32, INT64) can load into DECIMAL columns, subject to the target precision and scale.

  • DECIMAL logical type encoding (INT32, INT64, FixedLenByteArray, ByteArray) is now correctly bypassed for DECIMAL256 target columns.

Note

If a source value cannot be converted to the target type or exceeds its range, the load fails with an error. Parquet STRING values containing valid decimal text can load into DECIMAL columns.

Constraints

  1. The REPLACE and IGNORE modifiers, which control the handling of new (input) rows that duplicate existing table rows on unique key values (PRIMARY KEY or UNIQUE index values), are not yet supported in MatrixOne.

  2. Input pre-processing with SET is very limited. Only SET columns_name=nullif(expr1,expr2) is supported.

  3. When enabling parallel loading, ensure that each row of data in the file does not contain the specified line terminator (e.g., \n); otherwise, it may cause data errors during file loading.

  4. Parallel loading of a single file requires an uncompressed format, because a single file is parallelized by splitting it at byte offsets. When the file path is a wildcard pattern that matches multiple files, each file is distributed as a whole-file shard, so multiple compressed files can be loaded in parallel.

  5. For LOAD DATA LOCAL INFILE, enable the client’s local-file capability. With the MySQL command-line client, for example, connect with mysql -h <mo-host-ip> -P 6001 -u <user> -p --local-infile=1.