LOAD DATA

Notice

• If the LOG ERRORS clause is not specified, the behavior is the normal import behavior, which is to report an error immediately upon encountering the first error.
• If the LOG ERRORS clause is specified but the REJECT LIMIT clause is not specified, it is equivalent to specifying a diagnostic LIMIT of 0. The operation will fail upon encountering the first error, but the first encountered error will be logged, and the error code will also be a diagnostic error, namely "reject limit reached".

  View error logs

  OceanBase Database supports the following SQL statement to view the exported error log information:

  SELECT * FROM READ_ERROR_LOG('diagnosis_log_path');
  

  Here, diagnosis_log_path indicates the path of the error log. When executed, this SQL statement is equivalent to the following read URL external table statement:

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

  Example:

  1. When the specified log path results in a file name that does not end with /.

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

     The URL external table statement corresponding to this SQL statement is as follows (the file name will be 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 results in a folder (ending with /).

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

     The corresponding URL external table statement for this SQL statement is as follows (pattern is not used):

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

  Rules for wildcards during multi-file direct load

  To facilitate multi-file import, file wildcard functionality has been introduced. It applies to server-side and OSS file imports, but not to client-side file imports.

  • Server-side wildcard usage

    • Matching rules:

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

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

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

    • Considerations:

      • There must be at least one matching file; otherwise, return error code 4027.

      • For the input of 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 a whole match. If no match is found, error 4027 will be reported.

      • Only the POSIX GLOB function supports the following wildcards: test.6*(6|0).csv and test.6*({0.csv,6.csv}|.csv). Although these files can be found using the ls command, the GLOB function cannot match them and returns error 4027.

  • Use wildcard characters in Cloud Object Storage Service (OSS)

    • Matching rules:

      Match 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, 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 '|'; will return OB_NOT_SUPPORTED.

      • File name wildcards only support * and ?. Other wildcards, although allowed, will not match anything.

  Examples

  Import data from a server-side (OBServer node) file

  Example 1: Import data from a file on the server side.

  1. Set the global security path.

     Notice

     For security reasons, when setting the system variable secure_file_priv, you must connect to the database via a local socket to execute the SQL statement that modifies this global variable. For more information, see secure_file_priv.

     obclient> SET GLOBAL secure_file_priv = "/";
     

  2. Log off.

     Note

     secure_file_priv is a GLOBAL variable. You must execute \q to exit and make it effective.

     obclinet> \q
     

     The return result is as follows:

     Bye
     

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

     • Normal import.

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

     • Use the APPEND hint to enable direct load.

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

  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: Import a CSV file.

  • Import all columns from 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 columns c1 and c2 from the test1.csv file located at the data/csv path, and import them into columns col1 and col2 of table t1.

    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 client (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 -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, please use the OBClient client of version 2.2.4 or later.

  2. On 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: Import a compressed file directly by setting COMPRESSION.

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

  Example 3: Direct load by partition using the PARTITION clause.

  • Specify direct load for a partition

  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 direct load for subpartition

  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: Use the direct(bool, int) hint to enable direct load. The direct load file can be stored on 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 ',';
  

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

  Notice

  The IP addresses in the example have been masked. When verifying, you must enter your own real IP address.

  The following example shows how to create an external table on a server (OBServer node) and in Oracle-compatible mode of OceanBase Database based on the location of the external file. The procedure is as follows:

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

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

  2. Prepare an external file. Create a file named type_cast.csv in the /home/admin/test_csv directory.

     [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 must connect to the database via a local socket to execute the SQL statement that modifies this global variable. For more information, see secure_file_priv.

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

        ssh admin@10.10.10.1
        

     2. Run 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 import and export limits to unlimited.

        SET GLOBAL secure_file_priv = "/";
        

  4. Reconnect to the oracle001 tenant.

     Example:

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

  5. Create the table 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 used by diagnostic logs, see load_data_diagnosis_log_compression.

  7. Set the maximum log file size to 1 KB. When the size of the exported log exceeds 1 KB, a second file is automatically generated and the export continues.

     ALTER SYSTEM SET load_data_diagnosis_log_max_size = '1K';
     

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

  8. Use the LOAD DATA statement to import data into table test_tbl1 from an external URL table, specifying error diagnostics, the directory for storing error logs as /home/admin/test_into/, and the directory for storing error data files as /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 return 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 contents of the error log.

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

     The return 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 return 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 through the URL external table.

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

      The return 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 examples of using the LOAD DATA statement, see Import data by using the LOAD DATA statement.