Instructions for MatrixOne Load Data Support¶

The complete syntax of MySQL 8.0 is as follows:

LOAD DATA
    [LOW_PRIORITY | CONCURRENT] [LOCAL]
    INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [PARTITION (partition_name [, partition_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}]
    [(col_name_or_user_var_var
        [, col_name_or_user_var] ...)]
    [SET col_name={expr | DEFAULT}
        [, col_name={expr | DEFAULT}] ...]

Fields not supported by MatrixOne¶

In the above complete syntax structure, MatrixOne does not yet support the following fields:

REPLACE
PARTITION
CHARACTER SET: Currently only UTF8 is supported
FIELDS TERMINATED BY ‘string’: simdcsv does not accept \r,\n,Unicode replacement character (0xFFFD).
FIELDS [OPTIONALLY] ENCLOSED BY ‘char’: simdCSV fixed is ".
FIELDS ESCAPED BY ‘char’: simdcsv is not supported yet.
LINES STARTING BY ‘string’: simdcsv is not supported. Lines with the first character are filtered out.
LINES TERMINATED BY ‘string’: simdcsv does not support modification. Fixed ‘\n’, \r\n.
user_var: Not supported. The data columns corresponding to the variable are filtered out.
SET col_name={expr | DEFAULT}: Not supported.
LOW_PRIORITY: Not supported
CONCURRENT: Not supported
LOCAL: Not supported. local requires data to be transferred from the client side to the server side.

MatrixOne supports syntax and formats¶

grammar¶

Grammar	Description
use T;
load data
infile ‘pathto/file’
ignore	Optional. Add `ignore`, means that when a field is listed in a table, the entire `load` will not be terminated. The number of `warnings` will be recorded, but the content of `warnings` will not be recorded; When `ignore`, the field is listed in a table, the entire `load`
INTO TABLE tableA
FIELDS TERMINATED BY ‘,’	Must represent the field splitter. Usually ‘,’. And ssb’s is ‘
IGNORE number LINES	Optional, indicating that the number line at the beginning of the file is ignored
(col0,col1,…,coln);	Optional, represents the list of column names in the table. For example, `coli` corresponds to the `i` column in the table. For example, the data row `field0 field1 field2 ... fieldN`, corresponding to the table column `(col0, col1, col2 ,...,colN)` If the column coli starts with ‘@’, then field is discarded; (col0,col1,…,coln) may not be the same as the column names in the table. fieldi binds to the corresponding column name. If the data row has more fields than (col0,col1,…,coln) or the number of columns in the table, the excess fields are discarded. If the data row has fewer fields than (col0,col1,…,coln) or the number of columns in the table, the few columns are supplemented with NULL. If there is no (col0,col1,…,coln) in the load data syntax, it is equivalent to all columns loaded into the table.

csv format requirements¶

character	Description
Field splitter	is usually ‘,’, or other characters (not `"`,’\r’,’\n’). For example: the SSB dataset is ‘
Field contains symbol	`"`
Line End Character	`\r\n` or ‘\n’
Field NULL Value	For value \N , it means that the field is NULL, and any other case is considered a string input
Comment symbols	‘#’ lines as the first character will be filtered out