Purpose
You can use this statement to import data from an external file.
In OceanBase Database, you can use the LOAD DATA statement to load files only from a directly connected OBServer node. Therefore, you must copy a file to the current OBServer node before you import it.
Note
- The current version of OceanBase Database does not support the
LOCAL INFILEstatement. - Do not use the
LOAD DATAstatement on tables with triggers.
You can use the LOAD DATA statement to import a CSV text file in the following process:
Parse the file: OceanBase Database reads data from a file based on the file name that you enter and determines whether to perform parallel or serial parsing of data from the input file based on the specified degree of parallelism (DOP).
Distribute the data: OceanBase Database is a distributed database. Data of each partition may be distributed across different OBServer nodes. The
LOAD DATAstatement is used to process the parsed data and determine to which OBServer node the data is sent.Insert the data: After the destination OBServer node receives the data, it executes the
INSERTstatement to insert the data into the corresponding partition.
To import data from an external file, you must have the FILE privilege.
Notice
If the REPLACE or IGNORE clause is used and the DOP is greater than 1, the finally inserted records of conflicting rows may differ from the results of serial execution. If you want to strictly guarantee the insertion of conflicting records, do not specify the DOP for the statement (or set the DOP to 1).
Syntax
LOAD DATA
[/*+ PARALLEL(N) load_batch_size(M) APPEND */]
|[/*+ PARALLEL(N) direct(bool, int) */]
INFILE 'file_name'
[REPLACE | IGNORE]
INTO TABLE table_name
[{FIELDS | COLUMNS}
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char']
]
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']
]
[IGNORE number {LINES | ROWS}]
[(column_name_var
[, column_name_var] ...)]
Parameters
| Parameter | Description |
|---|---|
| parallel(N) | The DOP for loading data. The default value of N is 4. |
| load_batch_size(M) | The batch size of each insertion. The default value of M is 100. Recommended value range: [100, 1000]. |
| APPEND | A hint for enabling the bypass import feature. This feature allows you to allocate space and write data to data files. By default, this hint is equivalent to direct(false, 0) and can collect statistics online like the GATHER_OPTIMIZER_STATISTICS hint does. |
| direct | A hint for enabling the bypass import feature. The bool parameter in direct(bool, int) specifies whether the given CSV file is ordered. The value true indicates that the file is ordered. The int parameter specifies the maximum number of error rows allowed. |
| file_name | The path and name of the input file. Currently, you can load only local files from OBServer nodes. |
| REPLACE | IGNORE | If a unique key conflict occurs, REPLACE indicates that conflicting rows are overwritten, and IGNORE indicates that conflicting rows are ignored. The LOAD DATA statement checks whether a table contains duplicate data based on its primary key. If the table does not have a primary key, the REPLACE and IGNORE options are equivalent. If duplicate data exists, the LOAD DATA statement records the incorrect data to a log file by default. |
| table_name | The name of the table from which data is imported. Partitioned and non-partitioned tables are supported. |
| FIELDS | COLUMNS | The format of the field.
|
| LINES STARTING BY | The start character of the line. |
| LINES TERMINATED BY | The end character of the line. |
| IGNORE number { LINES | ROWS } | Specifies to ignore the first few lines. LINES indicates the first few lines of the file. ROWS indicates the first few rows of data specified by the field delimiter. By default, fields in the input file are mapped to columns in the destination table one by one. If the input file does not contain all the columns, the missing columns are filled based on the following mappings:
|
| column_name_var | The name of the imported column. |
Examples
Example 1: Import data from an external file.
Set a global security path.
obclient> SET GLOBAL secure_file_priv = "" Query OK, 0 rows affected obclient> \q ByeNote
Because
secure_file_privis aGLOBALvariable, you need to run\qto exit for the settings to take effect.After you reconnect to the database, import data from an external file.
obclient> LOAD DATA INFILE 'test.sql' INTO TABLE t1; Query OK, 0 rows affected
Example 2: Use the APPEND hint to enable the bypass import feature.
LOAD DATA /*+ PARALLEL(4) APPEND */
INFILE '/home/admin/a.csv'
INTO TABLE t;
Example 3: Use the direct(bool, int) hint to enable the bypass import feature. The file to be imported in bypass mode can be stored in OSS.
load data /*+ parallel(1) direct(false,0)*/ remote_oss infile 'oss://antsys-oceanbasebackup/backup_rd/xiaotao.ht/lineitem2.tbl?host=***.oss-cdn.***&access_id=***&access_key=***' into table lineitem fields terminated by '|' enclosed by '' lines starting by '' terminated by '\n';