This topic describes how to use the LOAD DATA statement to import local data to AnalyticDB for MySQL Data Warehouse Edition (V3.0).
Syntax
LOAD DATA LOCAL
INFILE 'file_name'
[REPLACE | IGNORE]
INTO TABLE table_name
[{FIELDS | COLUMNS}
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
]
[LINES
[TERMINATED BY 'string']
]
[IGNORE number {LINES | ROWS}]
[(column_name_or_user_var
[, column_name_or_user_var] ...)]
Parameters
Parameter | Description |
---|---|
LOAD DATA LOCAL INFILE | Imports data from a local file. |
file_name | The path of the local file. The path consists of the directory and the file name. Note If the path specified by the file_name parameter is a relative path, it is relative to the path of the client. |
table_name | The name of the destination table within AnalyticDB for MySQL. |
REPLACE | Overwrites existing data with the data to be imported when a duplicate primary key is used. |
IGNORE | Ignores rows that failed to be imported when a duplicate primary key is used or a data error occurs. Specific rows may fail to be imported. |
[FIELDS] TERMINATED BY 'string' | Defines the delimiter used to separate columns of data. The default value is \t , which is the same as the column delimiter in MySQL databases. |
[FIELDS] ENCLOSED BY 'char' | Defines the character used to enclose the data of each column. For example, if the data is |
[LINES] TERMINATED BY 'string' | Defines the delimiter used to separate rows of data. The default value is \n . |
IGNORE number LINES | Specifies the number of rows to be ignored from the beginning during data import. For example, |
(column_name_or_user_var,...) | Specifies the columns to be imported. If you do not specify this parameter, data is imported in the order of the columns.
|
Precautions
- The
local-infile
feature must be enabled for a client.For example, you must add the following configuration to the
my.cnf
file to enable thelocal-infile
feature for a MySQL client:cat ~/.my.cnf [mysqld] local-infile [mysql] local-infile
For more information about the
my.cnf
file, see MySQL official documentation. - The atomicity of operations cannot be ensured during data import.
- In
IGNORE
mode, the rows of data that failed to be imported are ignored. - In
REPLACE
mode, if a row of data fails to be imported, theINSERT
operation is terminated and specific rows of data may not be imported.
- In
- You can execute the
SHOW WARNINGS
statement to view the error messages of failed rows.
Examples
Assume that the data within the local file named out.bak
is imported to the table named test
in AnalyticDB for MySQL. The out.bak
file contains 5,000 rows of data. The column delimiter is \t
, and the row delimiter is \n
. The eighth row of data is invalid, as shown in the following data entries:
1 bb
2 bb
3 bb
4 bb
5 bb
6 bb
7 bb
bb 8
9 bb
...
- Connect to the AnalyticDB for MySQL cluster that you want to manage. Execute the CREATE DATABASE and CREATE TABLE statements to create a table named
test
within the database namedadb_demo
to store the data to be imported from the local file.CREATE TABLE test ( a int NOT NULL DEFAULT '0', b varchar NOT NULL, PRIMARY KEY (a) ) DISTRIBUTED by hash(a);
- Execute the LOAD DATA statement on a MySQL client to import data from the local file named
out.bak
to thetest
table within the AnalyticDB for MySQL database.- Execute the LOAD DATA statement in
IGNORE
mode. In this mode, specific rows may fail to be imported. Sample statement:load data local infile '~/out.bak' ignore into table test FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' ;
Execute the following statement to query the import result:select count(1) from test;
+----------+ | count(1) | +----------+ | 4999 | +----------+ 1 row in set (0.14 sec)
- Execute the LOAD DATA statement in
REPLACE
mode. In this mode, if a row fails to be imported, the subsequent import operation is terminated immediately. Sample statement:
The following information is returned:load data local infile '~/out.bak' replace into table test FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' ;
ERROR 1064 (42000): [13000, 2019061210070703000511314203303000266] syntax error :syntax error => IDENTIFIER is not value type pos:34 row: 0 and ceil:0
- Execute the LOAD DATA statement to skip the first 10 rows of data during data import. Sample statement:
The following information is returned:load data local infile '~/out.bak' replace into table test FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' IGNORE 10 LINES;
Query OK, 4990 rows affected (0.37 sec)
- Execute the LOAD DATA statement in