Skip to content

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