LOAD DATA

Notice

• If the LOG ERRORS clause is not specified, the default behavior is to immediately report an error upon encountering the first error.
• If the LOG ERRORS clause is specified but the REJECT LIMIT clause is not, it is equivalent to specifying a diagnostic limit of 0. In this case, the operation will fail upon encountering the first error, but the first error will be recorded, and the error code will be related to the diagnostic error, such as "reject limit reached".

  Rules for using wildcards in multi-file direct load

  To facilitate multi-file import, the wildcard feature is introduced for server-side and OSS file imports, but not for client-side file imports.

  • Server-side wildcard usage

    • Matching rules:

      • Match a file: 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: 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, an 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 '|';, the pattern /xxx/test.1*.csv,/xxx/test.6*.csv is considered as a whole. If no files match, an error code 4027 is returned.

      • Only POSIX-compatible wildcards supported by the GLOB function 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 code 4027.

  • Wildcard usage in Cloud Object Storage Service (OSS)

    • Matching rules:

      Match a file: 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 input 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 filenames. Other wildcards, although allowed in input, cannot match any results.

  Examples

  Note

  When you use the LOAD DATA statement, you can use \N to represent NULL.

  Import data from a server-side file

  Example 1: Import data from a server-side file.

  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 must execute \q to make it effective.

  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: Enable direct load by using the APPEND hint.

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

  Example 3: Import a CSV file.

  • Import all columns of the test1.csv file.

    load data  /*+ direct(true,0) parallel(2)*/
    from files(
      location = "data/csv",
      format = (
        type = 'csv',
        field_delimiter = ',',
        parse_header = true,
        skip_blank_lines = true
      ),
      pattern = 'test1.csv')
    into table t1;
    

  • Read the c1 and c2 columns from the test1.csv file in the data/csv directory and import them to the col1 and col2 columns of the t1 table.

    load data  /*+ direct(true,0) parallel(2)*/
    from (
      select c1, c2 from files(
          location = 'data/csv'
            format = (
            type = 'csv',
            field_delimiter = ',',
            parse_header = true,
            skip_blank_lines = true
          ),
          pattern = 'test1.csv'))
    into table t1 (col1, col2);
    

  Example 4: Import a PARQUET file.

  load data  /*+ direct(true,0) parallel(2)*/
  from files(
    location = "data/parquet",
    format = ( type = 'PARQUET'),
    pattern = 'test1.parquet')
  into table t1;
  

  Example 5: Import an ORC file.

  load data  /*+ direct(true,0) parallel(2)*/
  from files(
    location = "data/orc",
    format = ( type = 'ORC'),
    pattern = 'test1.orc')
  into table t1;
  

  Example 5: Import an ODPS file.

  load data  /*+ direct(true,0) parallel(2)*/
  from source (
    type = 'ODPS',
    accessid = '$ODPS_ACCESSID',
    accesskey = '******',
    endpoint= '$ODPS_ENDPOINT',
    project_name = 'example_project',
    schema_name = '',
    table_name = 'example_table',
    quota_name = '',
    compression_code = '')
  into table t1;
  

  Import data from a local file

  Example 1: 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, you must use OBClient V2.2.4 or later. If you do not have OBClient of a specific 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
     

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

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

  Example 3: Specify a partition for direct load by using PARTITION.

  • Specify a partition for direct load

  load data /*+ direct(true,0) parallel(2) load_batch_size(100) */
  infile "$FILE_PATH"
  into table t1 partition(p0, p1)
  fields terminated by '|' enclosed by '' lines starting by '' terminated by '\n';
  

  • Specify a subpartition for direct load

  load data /*+ direct(true,0) parallel(2) load_batch_size(100) */
  infile "$FILE_PATH"
  into table t1 partition(p0sp0, p1sp1)
  fields terminated by '|' enclosed by '' lines starting by '' terminated by '\n';
  

  Import data from an OSS file

  Example 1: Enable direct load by using the direct(bool, int) hint. The direct load file is 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 server-side file as a URL external table

  Notice

  The commands involving IP addresses in the examples have been desensitized. When verifying, replace them with the actual IP address of your machine.

  The following example describes how to create an external table in the MySQL mode of OceanBase Database when the external file is stored locally or in the database. The steps are as follows:

  1. Prepare an external file.

     Run the following command to create a file named column_conv.csv in the /home/admin/test_csv directory on the machine where the OBServer node is located.

     [admin@xxx /home/admin/test_csv]# vi column_conv.csv
     

     The content of the file is as follows:

     1,short,short
     2,long_text,long_text
     3,long_text,long_text
     

  2. Set the import file path.

     Notice

     For security reasons, you can only modify the secure_file_priv system variable by executing an SQL statement through a local Unix socket. For more information, see secure_file_priv.

     1. Run the following command to log in to the machine where the OBServer node is located.

        ssh admin@10.10.10.1
        

     2. Run the following command to connect to the mysql001 tenant through a local Unix socket.

        obclient -S /home/admin/oceanbase/run/sql.sock -uroot@mysql001 -p******
        

     3. Run the following SQL statement to set the import path to /home/admin/test_csv.

        SET GLOBAL secure_file_priv = "/home/admin/test_csv";
        

  3. Reconnect to the mysql001 tenant.

     Here is an example:

     obclient -h10.10.10.1 -P2881 -uroot@mysql001 -p****** -A -Ddb_test
     

  4. Create a table named test_tbl1.

     CREATE TABLE test_tbl1(col1 VARCHAR(5), col2 VARCHAR(5), col3 VARCHAR(5));
     

  5. Use the LOAD DATA statement to import data into the test_tbl1 table as a URL external table and specify error diagnostics.

     LOAD DATA FROM FILES(
         LOCATION = '/home/admin/test_csv',
         FORMAT = (
             TYPE = 'csv',
             FIELD_DELIMITER = ','),
         PATTERN = 'column_conv.csv')
         INTO TABLE test_tbl1
         LOG ERRORS REJECT LIMIT UNLIMITED;
     

     The return result is as follows:

     Query OK, 1 row affected, 2 warnings
     Records: 1  Deleted: 0  Skipped: 0  Warnings: 2
     

  6. View the Warnings.

     SHOW warnings;
     

     The return result is as follows:

     +---------+------+----------------------------------------------------------------------------------------------------------------------+
     | Level   | Code | Message                                                                                                              |
     +---------+------+----------------------------------------------------------------------------------------------------------------------+
     | Warning | 1406 | fail to scan file column_conv.csv at line 2 for column "db_test"."test_tbl1"."col2", error: Data too long for column |
     | Warning | 1406 | fail to scan file column_conv.csv at line 3 for column "db_test"."test_tbl1"."col2", error: Data too long for column |
     +---------+------+----------------------------------------------------------------------------------------------------------------------+
     2 rows in set
     

  7. View the data in the test_tbl1 table.

     SELECT * FROM test_tbl1;
     

     The return result is as follows:

     +------+-------+-------+
     | col1 | col2  | col3  |
     +------+-------+-------+
     | 1    | short | short |
     +------+-------+-------+
     1 row in set
     

  References

  • For more information about connecting to OceanBase Database, see Overview of connection methods.
  • For more information about using the LOAD DATA statement, see Import data by using the LOAD DATA statement
  • For more information about using the LOAD DATA statement for direct load, see Import data by using the LOAD DATA statement for direct load