LOAD DATA

Purpose

You can use this statement to import data from an external file.

In OceanBase Database, the LOAD DATA statement can load only local input files on OBServer nodes. Therefore, you must copy a file to an OBServer node before you import it.

You can use the LOAD DATA statement to import a CSV text file in the following process:

1. 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).

2. Distribute the data: OceanBase Database is a distributed database. Data of each partition may be distributed across different OBServer nodes. The LOAD DATA statement is used to process the parsed data and determine to which OBServer node the data is sent.

3. Insert the data: After the destination OBServer node receives the data, it executes the INSERT statement to insert the data into the corresponding partition.

To import data from an external file, you must have the FILE privilege.

Syntax

LOAD DATA
    [/*+ PARALLEL(N) load_batch_size(M) APPEND */]
   |[/*+ PARALLEL(N) direct(bool, int) */]
    INFILE 'file_name'
    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

Examples

Example 1: Import data from an external file.

1. Set a global security path.

   obclient> SET GLOBAL secure_file_priv = ""
   Query OK, 0 rows affected
   obclient> \q
   Bye
   

2. 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';