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 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 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 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 a distributed database, OceanBase Database distributes data in partitions across OBServer nodes. The LOAD DATA statement calculates which OBServer node the parsed data should be sent to.

3. Insert data: After the data is received by the target OBServer node, the node executes an 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
    [COMPRESSION [=] {AUTO|NONE|GZIP|DEFLATE|ZSTD}]
    [{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

Wildcard rules for importing multiple files

To facilitate importing multiple files, the wildcard feature is introduced for server-side and OSS file imports. This feature is not applicable to client-side file imports.

• Use of wildcards on the server

  • Matching rules:

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

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

    • Matching both directories and file names: 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, error code 4027 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 will be considered as a whole for matching. If no matching is found, error code 4027 will be returned.

    • Only wildcards 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, but they cannot be matched by the GLOB function. In this case, error code 4027 will be returned, even though these wildcards can be matched by the ls command.

• Use of wildcards in Cloud Object Storage Service (OSS)

  • Matching rules:

    File name matching: 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, 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 * and ? are supported for file name wildcards. Other wildcards, although allowed, cannot match any results.

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

   After you set the secure_file_priv variable, you must exit MySQL by using the \q command for the setting to 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;

Import data from a local client file

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

1. Open the terminal or command prompt 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]>
   

   Note

   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
   

Example 2: Directly import a compressed file by setting the COMPRESSION parameter.

LOAD DATA LOCAL INFILE '/your/file/lineitem.tbl.gz' 
    INTO TABLE lineitem 
    COMPRESSION GZIP 
    FIELDS TERMINATED BY '|';

Import data from an OSS file

Example 1: Use the direct(bool, int) hint to enable direct load, where the source file is 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';

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.