LOAD DATA

Notice

• If the LOG ERRORS clause is not specified, the default behavior is normal import, which means the operation fails upon encountering the first error.
• If the LOG ERRORS clause is specified but the REJECT LIMIT clause is not, it is equivalent to setting LIMIT to 0. The operation fails upon encountering the first error, but the first error is recorded, and the error code is related to diagnostics, i.e., "reject limit reached".

  View error logs

  OceanBase Database supports the following SQL statements to view the error logs of an import operation:

  SELECT * FROM READ_ERROR_LOG('diagnosis_log_path');
  

  In this statement, diagnosis_log_path specifies the path of the error log. When executed, this SQL statement is equivalent to the following URL external table query statement:

  SELECT *
  FROM FILES (
      LOCATION = 'diagnosis_log_path/'
      FORMAT(
          TYPE = 'csv'
          FIELD_DELIMITER = ','
          FIELD_OPTIONALLY_ENCLOSED_BY = '\',
          PARSE_HEADER  = true
          )
      [, PATTERN = 'filename']
  );
  

  Here are some examples:

  1. The specified log path is a file name (not ending with /).

     SELECT * FROM READ_ERROR_LOG('diagnosis/log/path/filename');
     

     The corresponding URL external table query statement is as follows (the file name is used as a pattern to filter files):

     SELECT *
     FROM FILES (
         LOCATION = 'diagnosis/log/path/',
         FORMAT (
             TYPE = 'csv'
             FIELD_DELIMITER = ','
             FIELD_OPTIONALLY_ENCLOSED_BY = '\',
             PARSE_HEADER = true
             ),
         PATTERN = 'filename'
     );
     

  2. The specified log path is a folder (ending with /).

     SELECT * FROM READ_ERROR_LOG('diagnosis/log/path/');
     

     The corresponding URL external table query statement is as follows (no pattern is used):

     SELECT *
     FROM FILES (
         LOCATION = 'diagnosis/log/path/',
         FORMAT (
             TYPE = 'csv'
             FIELD_DELIMITER = ','
             FIELD_OPTIONALLY_ENCLOSED_BY = '\',
             PARSE_HEADER = true
             )
     );
     

  Rules for using wildcards in multi-file direct load

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

  • Server-side wildcard usage

    • Matching rules:

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

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

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

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

      • Only the wildcards supported by the POSIX GLOB function are supported, such as test.6*(6|0).csv and test.6*({0.csv,6.csv}|.csv). Although these wildcards 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:

      Matching 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 '|';

    • 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 the * and ? wildcards are supported for file names. Other wildcards, although allowed, cannot match any results.

  Examples

  Import data from a file on an OBServer node

  Example 1: Import data from a file on an OBServer node.

  1. Set the global secure file path.

     Notice

     Due to security reasons, you can only modify the secure_file_priv system variable by using a local socket connection to execute the SQL statement that modifies the global variable. For more information, see secure_file_priv.

     obclient> SET GLOBAL secure_file_priv = "/";
     

  2. Log out.

     Note

     Because secure_file_priv is a GLOBAL variable, you must execute \q to make the modification take effect.

     obclinet> \q
     

     The return result is as follows:

     Bye
     

  3. After you reconnect to the database, use the LOAD DATA statement to import data.

     • Normal import.

       obclient> LOAD DATA INFILE '/home/admin/test.csv' INTO TABLE t1;
       

     • Enable direct load by using the APPEND hint.

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

  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 of 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 6: 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 an OceanBase Database table.

  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 -usys@oracle001 -p******
     

     The return result is as follows:

     Welcome to the OceanBase.  Commands end with ; or \g.
     Your OceanBase connection id is 3221548006
     Server version: OceanBase 4.2.2.0 (r100000032024010510-75c47d4be18a399e13c5309de1a81da5caf4e7c0) (Built Jan  5 2024 10:17:55)
     
     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 [SYS]>
     

     Notice

     To use the LOAD DATA LOCAL INFILE feature, use OBClient of V2.2.4 or later.

  2. In the client, execute the LOAD DATA LOCAL INFILE statement to load the local data file.

     obclient [SYS]> 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 option.

  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 the PARTITION option.

  • 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 file to be directly loaded is stored in an OSS bucket.

  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 ',';
  

  Import data from a server-side file as a URL external table

  Notice

  The commands in the examples have been desensitized. When verifying, please fill in the actual IP address of your machine.

  The following example shows how to import data from a server-side file as a URL external table. The external file is located on the server (OBServer node) and in the Oracle-compatible mode of OceanBase Database. The steps are as follows:

  1. Create directories on the OBServer node. The /home/admin/test_csv directory stores the external data, the /home/admin/test_into directory stores the error logs, and the /home/admin/test_badfile directory stores the error data files.

     [admin@xxx /home/admin]# mkdir -p /home/admin/{test_csv,test_into,test_badfile}
     

  2. Prepare the external file. In the /home/admin/test_csv directory, create a file named type_cast.csv.

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

     The content of the file is as follows:

     1,2,3
     2,4,af
     3,4,5
     ds,6,32
     4,5,6
     5,2,3
     6,v4,af
     7,4,5
     kj,a6,32
     8,5,6
     

  3. Set the import file path.

     Notice

     For security reasons, when setting the system variable secure_file_priv, you can only modify the global variable by executing an SQL statement through a local Socket connection. For more information, see secure_file_priv.

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

        ssh admin@10.10.10.1
        

     2. Execute the following command to connect to the oracle001 tenant using a local Unix Socket connection.

        obclient -S /home/admin/oceanbase/run/sql.sock -usys@oracle001 -p******
        

     3. Execute the following SQL statement to set the import and export to unlimited.

        SET GLOBAL secure_file_priv = "/";
        

  4. Reconnect to the oracle001 tenant.

     Here is an example:

     obclient -h10.10.10.1 -P2881 -usys@oracle001 -p****** -A
     

  5. Create a table named test_tbl1.

     CREATE TABLE test_tbl1(col1 INT, col2 INT, col3 INT);
     

  6. Set the compression algorithm for diagnostic logs to AUTO.

     ALTER SYSTEM SET load_data_diagnosis_log_compression = 'AUTO';
     

     For more information about setting the compression algorithm for diagnostic logs, see load_data_diagnosis_log_compression.

  7. Set the maximum size of a single diagnostic log file to 1 KB. When the size of the exported log exceeds 1 KB, a second file will be automatically generated and the export will continue.

     ALTER SYSTEM SET load_data_diagnosis_log_max_size = '1K';
     

     For more information about setting the size of a single diagnostic log file, see load_data_diagnosis_log_max_size.

  8. Use the LOAD DATA statement to import data into the test_tbl1 table using the URL external table syntax, and specify error diagnostics. Set the directory for storing error logs to /home/admin/test_into/ and the directory for storing error data files to /home/admin/test_badfile/.

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

     The returned result is as follows:

     Query OK, 6 rows affected, 4 warnings
     Records: 6  Deleted: 0  Skipped: 0  Warnings: 4
     

  9. Use the read_error_log statement to view the error log content.

     SELECT * FROM READ_ERROR_LOG('/home/admin/test_into');
     

     The returned result is as follows:

     +------------+---------------+-------------+-------------------------------------------------------------------------------------------------------+
     | ERROR CODE | FILE NAME     | LINE NUMBER | ERROR MESSAGE                                                                                         |
     +------------+---------------+-------------+-------------------------------------------------------------------------------------------------------+
     | -5114      | type_cast.csv | 4           | fail to scan file type_cast.csv at line 4 for column "SYS"."TEST_TBL1"."COL1", error: Invalid numeric |
     | -5114      | type_cast.csv | 9           | fail to scan file type_cast.csv at line 9 for column "SYS"."TEST_TBL1"."COL1", error: Invalid numeric |
     | -5114      | type_cast.csv | 7           | fail to scan file type_cast.csv at line 7 for column "SYS"."TEST_TBL1"."COL2", error: Invalid numeric |
     | -5114      | type_cast.csv | 2           | fail to scan file type_cast.csv at line 2 for column "SYS"."TEST_TBL1"."COL3", error: Invalid numeric |
     +------------+---------------+-------------+-------------------------------------------------------------------------------------------------------+
     4 rows in set
     

  10. View the data in the test_tbl1 table.

      SELECT * FROM test_tbl1;
      

      The returned result is as follows:

      +------+------+------+
      | COL1 | COL2 | COL3 |
      +------+------+------+
      |    1 |    2 |    3 |
      |    3 |    4 |    5 |
      |    4 |    5 |    6 |
      |    5 |    2 |    3 |
      |    7 |    4 |    5 |
      |    8 |    5 |    6 |
      +------+------+------+
      6 rows in set
      

  11. View the content of the bad file using the URL external table.

      SELECT *
      FROM FILES (
          LOCATION = '/home/admin/test_badfile',
          FORMAT (
              TYPE = 'csv',
              FIELD_DELIMITER = ','),
          PATTERN = 'data.bad');
      

      The returned result is as follows:

      +------+------+------+
      | C1   | C2   | C3   |
      +------+------+------+
      | ds   | 6    | 32   |
      | kj   | a6   | 32   |
      | 6    | v4   | af   |
      | 2    | 4    | af   |
      +------+------+------+
      4 rows in set
      

  References

  • For more information about how to use the LOAD DATA statement, see Use the LOAD DATA statement to import data.
  • For more information about how to use the LOAD DATA statement for bypass import, see Use the LOAD DATA statement for bypass import.