Import local data
This section describes how to import local data files into a MatrixOne Intelligence instance, including using the Load Data Local command and the Source command for data import.
Import using the Load Data Local command
The Load Data Local command is a commonly used data import method. The MatrixOne instance supports the use of the Load Data Local command to import csv files or jsonline files in batches from the local file system where the client is located. If you need to use the local file import command, you need to start the client connection to the MatrixOne Intelligence instance on the server where the data file is located. The example is as follows:
mysql -h <host> -P 6001 -u <user_name> -p --local-infile
Note: If you need to import using local files, you need to add the suffix --local-infile when connecting to the MatrixOne instance
Import csv file
Grammar Structure
LOAD DATA LOCAL
INFILE 'filename'
INTO TABLE tbl_name
[{FIELDS | COLUMNS}
[TERMINATED BY '<string>']
[[OPTIONALLY] ENCLOSED BY '<string>']
]
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']
]
[IGNORE number {LINES | ROWS}]
[PARALLEL {'TRUE' | 'FALSE'}]
Particle description
| Parameters | Values | Required/Optional | Description |
|---|---|---|---|
| infile | string | Must | Address of local data file |
| table | string | Must | Data table imported into local files need to be created in advance |
| fields terminated by | string | Optional | Field separator, default is ',' |
| fields enclosed by | string | Optional | Field enclosed character, default to '"' |
| lines starting by | string | optional | line start position, default to empty string '' |
| lines terminated by | string | optional | line terminator, default to line break '\n' |
| ignore | Number | Optional | Lines to ignore when loading |
| parallel | {'TRUE' | 'FALSE'} | Optional |
Grammar Example
-- Load the lineorder_flat.tbl dataset in the local directory to the data table in the MatrixOne Intelligence instance lineorder_flat
LOAD DATA LOCAL INFILE '/ssb-dbgen-path/lineorder_flat.tbl ' INTO TABLE lineorder_flat;
limit
The JSON format supports the JSON type, but you need to make sure that the JSON does not contain field termination symbols, otherwise JSON needs to be enclosed in double quotes, for example:
- Correct example:
"{"a": 1, "b": 2}", 2 - Error example:
{"a": 1, "b": 2}, 2
Import jsonlines file
About jsonlines format
JSON (JavaScript Object Notation) is a lightweight data exchange format. JSONLines is a format that is more convenient to store structured data, also known as newline-delimited JSON. Each line is an independent, complete and legal JSON value, with '\n' as the separator between lines.
MatrixOne Intelligence has some requirements for the JSONLines format, which only allows JSON objects or JSON arrays that contain the same data type and normal structure. MatrixOne Intelligence does not currently support JSONLines files with nested structures.
Valid JSONLines object example
{"id":1,"father":"Mark","mother":"Charlotte"}
{"id":2,"father":"John","mother":"Ann"}
{"id":3,"father":"Bob","mother":"Monika"}
Invalid JSONLines object example (including nested structure)
{"id":1,"father":"Mark","mother":"Charlotte","children":["Tom"]}
{"id":2,"father":"John","mother":"Ann","children":["Jessika","Antony","Jack"]}
{"id":3,"father":"Bob","mother":"Monika","children":["Jerry","Karol"]}
Valid JSONLines array example (similar to csv format)
["Name", "Session", "Score", "Completed"]
["Gilbert", "2013", 24, true]
["Alexa", "2013", 29, true]
["May", "2012B", 14, false]
["Deloise", "2012A", 19, true]
Invalid JSONLines array example (data type and column number do not match)
["Gilbert", "2013", 24, true, 100]
["Alexa", "2013", "twenty nine", true]
["May", "2012B", 14, "no"]
["Deloise", "2012A", 19, true, 40]
Because the JSON data types are different from the data types of MatrixOne Intelligence, you need to create a data table before importing the JSONlines file to make sure their data types match.
Grammar Structure
LOAD DATA LOCAL
INFILE {"filepath"='<string>', "format"='jsonline', 'jsondata'='<string>', "compression"='<string>'}
INTO TABLE tbl_name
[IGNORE number {LINES | ROWS}]
[PARALLEL {'TRUE' | 'FALSE'}]
Particle description
| Parameters | Values | Required/Optional | Description |
|---|---|---|---|
| file_path | string | Must | Complete object storage file path |
| format | csv/jsonline | Must | Object storage file format, default to csv |
| jsondata | object/array | optional | JSON data format |
| compression | auto/none/bz2/gzip/lz4 | Optional | The compression format of the object storage file, default to 'none' |
| IGNORE | Number | Optional | Lines to ignore when loading |
| PARALLEL | true/false | Optional | Parallel import, the jsonline file defaults to true |
Grammar Example
-- Import the data.jl.gz gzip compressed data file under the /mo_data/ directory path to the data table db1.a in the MatrixOne Intelligence instance and import it from the second row.
LOAD DATA LOCAL INFILE {'filepath'='/mo_data/data.jl.gz', 'compression'='gzip','format'='jsonline','jsondata'='array'} into table db1.a ignore 1 lines;
Import using the Source command
MatrixOne Intelligence supports the use of the SOURCE command to execute SQL statements from an external SQL script file to import the entire database structure (including table structure and data). It should be noted that when processing large amounts of data, the performance may not be as high as the LOAD DATA command, because the SOURCE command requires parsing and executing each SQL statement.
Grammar Structure
SOURCE /<your_path>/sql_script.sql;
If the SQL file is large, you can run the import task in the background using the following command:
sql
nohup mysql -h <moc_host> -P 6001 -u <user_name> -p<your_password> -D<databasename> -e 'source /<your_path>/a.sql;' &
limit
MatrixOne Intelligence already supports MySQL table creation statements, so you can successfully migrate MySQL tables to MatrixOne Intelligence. However, it should be noted that MatrixOne Intelligence is not compatible with some MySQL keywords, such as engine=, etc., which will be automatically ignored in MatrixOne Intelligence and will not affect the migration of table structure. If the table to be migrated contains incompatible data types, triggers, functions, or stored procedures, it needs to be modified manually. For more information about compatibility, see MySQL Compatibility.