LOAD DATA

Purpose

This statement is used to import data from an external file.

OceanBase Database supports the following input file types for the LOAD DATA statement:

• Server-side (OBServer node) files: These files are stored on the OBServer node of OceanBase Database. You can use the LOAD DATA INFILE statement to load data from these files into a database table.

• Client-side (local) files: These files are stored on the local file system of the client. You can use the LOAD DATA LOCAL INFILE statement to load data from these files into a database table.

  Note

  Starting from OceanBase Database V4.2.2 in MySQL mode, you can use the LOAD DATA LOCAL INFILE statement to load data from a local file. When you execute the LOAD DATA LOCAL INFILE statement, the system automatically adds the IGNORE option.

• OSS files: These files are stored on the OSS file system. You can use the LOAD DATA REMOTE_OSS INFILE statement to load data from these files into a database table.

The LOAD DATA statement can currently import CSV files. The entire import process can be divided into the following steps:

1. Parse the file: OceanBase Database reads the data from the file based on the file name provided by the user. It then determines whether to parse the data in parallel or sequentially based on the specified parallelism.

2. Distribute the data: Since OceanBase Database is a distributed database, data in different partitions may be stored on different OBServer nodes. The LOAD DATA statement calculates the parsed data and determines which OBServer node the data should be sent to.

3. Insert the data: Once the target 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 */]
   |[/*+ PARALLEL(N) direct(bool, int) */]
    [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] ...)]

Parameters

Considerations

Wildcard rules for direct load

In the LOAD DATA statement, use the Hint direct option to specify whether to perform a direct load. To facilitate multi-file loading, the wildcard feature is introduced, which applies to file sources of the server_disk and OSS types but not to the client_disk type.

How to use direct load for files on the server disk

• server_disk example:

  • Match a file name: load data /*+ parallel(20) direct(true, 0) */ infile '/xxx/test.*.csv' replace into table t1 fields terminated by '|';

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

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

• server_disk considerations:

  • At least one matching file must exist; otherwise, an error OB_FILE_NOT_EXIST is returned.

  • For the input 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 considered a whole match. If no match is found, an error OB_FILE_NOT_EXIST is returned.

  • Only POSIX-compatible wildcard patterns are supported, such as test.6*(6|0).csv and test.6*({0.csv,6.csv}|.csv). Although these patterns can be found using the ls command, the GLOB function cannot match them, resulting in an error OB_FILE_NOT_EXIST.

How to use direct load for files on the OSS

• oss example:

  • Match a file name: 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 '|';

• oss considerations:

  • Directory matching is not supported. For example, 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 '|'; will return OB_NOT_SUPPORTED.

  • Only the * and ? wildcards are supported for file names. Other wildcards, although allowed, cannot match any results.

For more information, see Use the LOAD DATA statement to bypass data/file import.

Examples

Import data from a file on the server

Example 1: Import data from a file on the server.

1. Set the global secure file path.

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

   Note

   Since secure_file_priv is a GLOBAL variable, you need to execute \q to exit and make the setting effective.

2. After reconnecting 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 direct load.

LOAD DATA /*+ PARALLEL(4) APPEND */
   INFILE '/home/admin/a.csv'
   INTO TABLE t;

Example 3: Use the direct(bool, int) hint to enable direct load. The direct load file can be on the 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

Example 4: Use the following statement to import data from a local file to a table in OceanBase Database.

1. Open the 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, you must use OBClient of V2.2.4 or later. If you do not have an OBClient client of a specified version, you can also use MySQL client to connect to the database.

2. In the client, execute the LOAD DATA LOCAL INFILE statement to load the local data 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 direct load file can be on the OSS.

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 of connection methods.
• For more information about how to use the LOAD DATA statement, see Import data by using the LOAD DATA statement.
• For more information about how to bypass import data by using the LOAD DATA statement, see Bypass import data by using the LOAD DATA statement.