LOAD DATA

Purpose

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

OceanBase Database supports the following input files for the LOAD DATA statement:

• Files on an OBServer node. You can execute the LOAD DATA INFILE statement to load data from files on an OBServer node into database tables.

• Files in the file system of the local client. You can execute the LOAD DATA LOCAL INFILE statement to load data from files in the file system of the local client into database tables.

  Note

  When you execute the LOAD DATA LOCAL INFILE statement in OceanBase Database, the system automatically adds the IGNORE option to the statement.

• Files in an OSS file system. You can execute the LOAD DATA REMOTE_OSS INFILE statement to load data from files in an OSS file system into database tables.

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.

Syntax

LOAD DATA
    [/*+ PARALLEL(N) [load_batch_size(M)] [APPEND | direct(bool, int, [load_mode])] */]
    [REMOTE_OSS | LOCAL] 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] ...)]

load_mode:
    'full'
    | 'inc_replace'

Parameters

Considerations

Use wildcards for direct load

In the LOAD DATA statement, the direct keyword is used as a hint to specify to use direct load. When you import data in bypass mode, you can specify only one file in OSS as the file source. To import multiple files, you must execute the LOAD DATA statement multiple times. When you import multiple data files from the file system of a cluster or from OSS, you can use wildcards to specify the file names. This method is inapplicable when the file source is a client disk.

Import data from the file system of a cluster through direct load

• Here are some examples:

  • Use wildcards to match file names: load data /*+ parallel(20) direct(true, 0) */ infile '/xxx/test.*.csv' replace into table t1 fields terminated by '|';

  • Use wildcards to match a directory: load data /*+ parallel(20) direct(true, 0) */ infile '/aaa*bb/test.1.csv' replace into table t1 fields terminated by '|';

  • Use wildcards to match a directory and file names: load data /*+ parallel(20) direct(true, 0) */ infile '/aaa*bb/test.*.csv' replace into table t1 fields terminated by '|';

• Take note of the following considerations:

  • At least one file must be matched. Otherwise, the error OB_FILE_NOT_EXIST will be returned.

  • For load data /*+ parallel(20) direct(true, 0) */ infile '/xxx/test.1*.csv,/xxx/test.6*.csv' replace into table t1 fields terminated by '|';, /xxx/test.1*.csv,/xxx/test.6*.csv is matched as a whole. If no file or directory is matched, the error OB_FILE_NOT_EXIST will be returned.

  • Only wildcards compatible with the GLOB function in Portable Operating System Interface (POSIX) are supported. For example, test.6*(6|0).csv and test.6*({0.csv,6.csv}|.csv) can be added to the ls command but are not supported by the GLOB function, and therefore the error OB_FILE_NOT_EXIST will be returned.

Import data from OSS through direct load

• Here is an example:

  • Use wildcards to match file names: load data /*+ parallel(20) direct(true, 0) */ remote_oss infile 'oss://xxx/test.*.csv?host=xxx&access_id=xxx&access_key=xxx' replace into table t1 fields terminated by '|';

• Take note of the following considerations:

  • You cannot use wildcards to match a directory. For example, if you execute the statement load data /*+ parallel(20) direct(true, 0) */ remote_oss infile 'oss://aa*bb/test.*.csv?host=xxx&access_id=xxx&access_key=xxx' replace into table t1 fields terminated by '|';, the error OB_NOT_SUPPORTED will be returned.

  • Only the asterisk (*) and question mark (?) are supported as the wildcards for file names. You can enter other wildcards but these wildcards cannot match any result.

For more information, see Import data through direct load by using the LOAD DATA statement.

Examples

Import data from a file on the server

Example 1: Import data from a file on an OBServer node.

1. Set a global security path.

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

   Note

   Because secure_file_priv is a GLOBAL variable, you need to run \q to exit for the settings to take effect.

2. 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 direct load 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 direct load feature. The file to be imported through direct load 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';

Import data from a local file on the client

Example 4: Import data from a local file to a table in OceanBase Database.

1. Open a terminal or command prompt window and enter the following command to start the client.

   obclient --local-infile -hxxx.xxx.xxx.xxx -P2881 -uroot@mysql001 -p****** -A -Dtest
   

   The return result is as follows:

   Welcome to the OceanBase.  Commands end with ; or \g.
   Your OceanBase connection id is 3221719526
   Server version: OceanBase 4.2.2.0 (r100000022023121309-f536833402c6efe9364d5a4b61830a858ef24d82) (Built Dec 13 2023 09:58:18)
   
   Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.
   
   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
   
   obclient [test]>
   

   Notice

   To use the LOAD DATA LOCAL INFILE feature, use OBClient of V2.2.4 or later. If you do not have OBClient of the required version, you can also use a MySQL client to connect to OceanBase Database.

2. Execute the LOAD DATA LOCAL INFILE statement on the client to load data from a local file.

   obclient [test]> LOAD DATA LOCAL INFILE '/home/admin/test_data/tbl1.csv' INTO TABLE tbl1 FIELDS TERMINATED BY ',';
   

   The return result is as follows:

   Query OK, 3 rows affected
   Records: 3  Deleted: 0  Skipped: 0  Warnings: 0
   

Import data from an OSS file

Use the direct(bool,int) hint to enable direct load. The import file can be an OSS file.

LOAD DATA /*+ direct(true,1024) parallel(16) */ REMOTE_OSS INFILE 'oss://antsys-oceanbasebackup/backup_rd/xiaotao.ht/lineitem2.tbl?host=***.oss-cdn.***&access_id=***&access_key=***' INTO TABLE tbl1 FIELDS TERMINATED BY ',';

References

• For more information about how to connect to OceanBase Database, see Overview.
• For more information about how to use the LOAD DATA statement, see Import data by using the LOAD DATA statement.
• For more direct load examples of the LOAD DATA statement, see Import data through direct load by using the LOAD DATA statement.