LOAD DATA

Description

The statement is used to import data from an external source.

The LOAD DATA statement in OceanBase Database allows you to load data from the following types of input files:

• Server (OBServer) node files: files that are stored on the OBServer nodes of OceanBase Database. You can use the LOAD DATA INFILE statement to load data from server node files to database tables.

• Client (local) files: files that are stored in the local file system of the client. You can use the LOAD DATA LOCAL INFILE statement to load data from client local files to database tables.

  Note

  When OceanBase Database executes the LOAD DATA LOCAL INFILE statement, the system automatically adds the IGNORE option.

• OSS files: files that are stored in the OSS file system. You can use the LOAD DATA REMOTE_OSS INFILE statement to load data from OSS files to database tables.

The LOAD DATA statement currently supports importing data from CSV files. The entire import process consists of the following steps:

1. Parse the file: OceanBase Database reads data from the file based on the specified file name, and then parses the data in the file based on the specified degree of parallelism.

2. Distribute data: As OceanBase Database is a distributed database, the data in different partitions may be distributed on different OBServer nodes. The LOAD DATA statement calculates where the parsed data should be sent to based on the OBServer nodes.

3. Insert data: After the data is received by the target OBServer node, an INSERT statement is executed locally 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'

Parameter description

Considerations

Wildcard rules for direct load

The Hint direct keyword is used in the LOAD DATA statement to specify whether to enable direct load. When direct load is enabled, only one file can be specified that is stored in Object Storage Service (OSS). To import multiple files, you must execute multiple load data statements. To facilitate importing multiple files in the cluster file system (server_disk), the file wildcard feature is introduced. This feature applies to file sources of the server_disk and OSS types, but not to client disks (client_disk).

Enable direct load for server_disk

• Examples:

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

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

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

• Considerations:

  • At least one matching file must exist. Otherwise, the 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 as a whole for matching. If no file matches the pattern, the error OB_FILE_NOT_EXIST is returned.

  • Only wildcard characters supported by the POSIX GLOB function can be used. For example, test.6*(6|0).csv and test.6*({0.csv,6.csv}|.csv) can be used although they can be listed by using the ls command. However, the GLOB function cannot match them, and the error OB_FILE_NOT_EXIST is returned.

Enable direct load for oss

• Examples:

  • Matching files: 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 '|';

• 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 '|'; returns the error OB_NOT_SUPPORTED.

  • Only the wildcard characters * and ? are supported for matching filenames. Other wildcard characters, although allowed, cannot match any files.

For more information, see Import data or files by using the LOAD DATA statement

Examples

Import data from a server file

Example 1: Import data from a server file.

1. Set the global secure 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 make the setting take effect.

2. Reconnect to the database and 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 stored in Alibaba Cloud 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 client 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 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 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, and the direct load file can be stored in Alibaba Cloud 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 examples that use the LOAD DATA statement, see Import data by using the LOAD DATA statement.
• For more information about examples of direct load using the LOAD DATA statement, see Import data by using the LOAD DATA statement.