MYSQL-LOAD
MYSQL-LOAD
Name
MYSQL LOAD
Description
mysql-load: Import local data using the MySql client
LOAD DATA
[LOCAL]
INFILE 'file_name'
INTO TABLE tbl_name
[PARTITION (partition_name [, partition_name] ...)]
[COLUMNS TERMINATED BY 'string']
[LINES TERMINATED BY 'string']
[IGNORE number {LINES | ROWS}]
[(col_name_or_user_var [, col_name_or_user_var] ...)]
[SET (col_name={expr | DEFAULT} [, col_name={expr | DEFAULT}] ...)]
[PROPERTIES (key1 = value1 [, key2=value2]) ]
This statement is used to import data to the specified table. Unlike normal Load, this import method is a synchronous import.
This import method can still guarantee the atomicity of a batch of import tasks, either all data imports are successful or all fail.
- MySQL Load starts with the syntax
LOAD DATA
, without specifyingLABEL
- Specify
LOCAL
to read client side files. Not specified to read FE server side local files. Server side load was disabled by default. It can be enabled by setting a secure path in FE configurationmysql_load_server_secure_path
- The local fill path will be filled after
INFILE
, which can be a relative path or an absolute path. Currently only a single file is supported, and multiple files are not supported - The table name after
INTO TABLE
can specify the database name, as shown in the case. It can also be omitted, and the database where the current user is located will be used. PARTITION
syntax supports specified partition to importCOLUMNS TERMINATED BY
specifies the column separatorLINES TERMINATED BY
specifies the line separatorIGNORE num LINES
The user skips the header of the CSV and can skip any number of lines. This syntax can also be replaced by'IGNORE num ROWS '- Column mapping syntax, please refer to the column mapping chapter of Imported Data Transformation
PROPERTIES
parameter configuration, see below for details
PROPERTIES
max_filter_ratio: The maximum tolerable data ratio that can be filtered (for reasons such as data irregularity). Zero tolerance by default. Data irregularities do not include rows filtered out by where conditions.
timeout: Specify the import timeout. in seconds. The default is 600 seconds. The setting range is from 1 second to 259200 seconds.
strict_mode: The user specifies whether to enable strict mode for this import. The default is off.
timezone: Specify the time zone used for this import. The default is Dongba District. This parameter affects the results of all time zone-related functions involved in the import.
exec_mem_limit: Load memory limit. Default is 2GB. The unit is bytes.
trim_double_quotes: Boolean type, The default value is false. True means that the outermost double quotes of each field in the load file are trimmed.
enclose: When the csv data field contains row delimiters or column delimiters, to prevent accidental truncation, single-byte characters can be specified as brackets for protection. For example, the column separator is ",", the bracket is "'", and the data is "a,'b,c'", then "b,c" will be parsed as a field. Note: when the bracket is
"
, trim_double_quotes must be set to true.escape: Used to escape characters that appear in a csv field identical to the enclosing characters. For example, if the data is "a,'b,'c'", enclose is "'", and you want "b,'c to be parsed as a field, you need to specify a single-byte escape character, such as "\", and then modify the data to "a,' b,\'c'".
Example
Import the data from the client side local file
testData
into the tabletestTbl
in the databasetestDb
. Specify a timeout of 100 secondsLOAD DATA LOCAL
INFILE 'testData'
INTO TABLE testDb.testTbl
PROPERTIES ("timeout"="100")Import the data from the server side local file
/root/testData
(set FE configmysql_load_server_secure_path
to beroot
already) into the tabletestTbl
in the databasetestDb
. Specify a timeout of 100 secondsLOAD DATA
INFILE '/root/testData'
INTO TABLE testDb.testTbl
PROPERTIES ("timeout"="100")Import data from client side local file
testData
into tabletestTbl
in databasetestDb
, allowing 20% error rateLOAD DATA LOCAL
INFILE 'testData'
INTO TABLE testDb.testTbl
PROPERTIES ("max_filter_ratio"="0.2")Import the data from the client side local file
testData
into the tabletestTbl
in the databasetestDb
, allowing a 20% error rate and specifying the column names of the fileLOAD DATA LOCAL
INFILE 'testData'
INTO TABLE testDb.testTbl
(k2, k1, v1)
PROPERTIES ("max_filter_ratio"="0.2")Import the data in the local file
testData
into the p1, p2 partitions in the table oftestTbl
in the databasetestDb
, allowing a 20% error rate.LOAD DATA LOCAL
INFILE 'testData'
INTO TABLE testDb.testTbl
PARTITION (p1, p2)
PROPERTIES ("max_filter_ratio"="0.2")Import the data in the CSV file
testData
with a local row delimiter of0102
and a column delimiter of0304
into the tabletestTbl
in the databasetestDb
.LOAD DATA LOCAL
INFILE 'testData'
INTO TABLE testDb.testTbl
COLUMNS TERMINATED BY '0304'
LINES TERMINATED BY '0102'Import the data from the local file
testData
into the p1, p2 partitions in the table oftestTbl
in the databasetestDb
and skip the first 3 lines.LOAD DATA LOCAL
INFILE 'testData'
INTO TABLE testDb.testTbl
PARTITION (p1, p2)
IGNORE 1 LINESImport data for strict schema filtering and set the time zone to Africa/Abidjan
LOAD DATA LOCAL
INFILE 'testData'
INTO TABLE testDb.testTbl
PROPERTIES ("strict_mode"="true", "timezone"="Africa/Abidjan")Import data is limited to 10GB of load memory and timed out in 10 minutes
LOAD DATA LOCAL
INFILE 'testData'
INTO TABLE testDb.testTbl
PROPERTIES ("exec_mem_limit"="10737418240", "timeout"="600")
Keywords
MYSQL, LOAD