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-side (OBServer node) files: files stored on the OBServer nodes of OceanBase Database. You can use the LOAD DATA INFILE statement to load data from server-side files to database tables.

• Client-side (local) files: files stored in the local file system of the client. You can use the LOAD DATA LOCAL INFILE statement to load data from client-side 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 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 in parallel or in series based on the specified parallelism.

2. Distribute data: As a distributed database, OceanBase Database distributes data in partitions across OBServer nodes. The LOAD DATA statement determines which OBServer node the parsed data should be sent to for further processing.

3. Insert data: When the data is received by the target OBServer node, the node executes an INSERT statement to insert the data into the corresponding partition on the node.

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

The Hint direct keyword is specified in the LOAD DATA statement to indicate whether to perform direct load. When performing a direct load from files stored in the cloud object storage service (OSS), you can specify only one file. To import multiple files, you must use multiple load data statements. To facilitate importing multiple files in the cluster file system (server_disk), the file wildcard feature is introduced for server_disk and OSS file sources, but not for client_disk.

Perform direct load in the server_disk file system

• server_disk examples:

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

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

  • Match both directories and filenames: 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, the error OB_FILE_NOT_EXIST is 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 considered as a whole for matching. If no file matches the pattern, the error OB_FILE_NOT_EXIST is returned.

  • Only POSIX GLOB functions supported wildcards are supported. For example, test.6*(6|0).csv and test.6*({0.csv,6.csv}|.csv) can be matched by the GLOB function because they can be found by using the ls command. However, if you specify such a pattern that cannot be matched, the error OB_FILE_NOT_EXIST is returned.

Perform direct load from cloud object storage service (OSS)

• oss examples:

  • Match 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 '|';

• oss considerations:

  • Directory matching is not supported. For example, 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 * and ? are supported as wildcard characters in 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 security 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 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 V2.2.4 or later, 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 imported 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 that use direct load, see Import data by using the LOAD DATA statement.