LOAD DATA

Note

If a CSV file parsing error occurs during import, a obloaddata.log log file may be generated in the log subdirectory under the OBServer installation path. Type conversion failures and primary key conflicts are not recorded in this log file. When using the FROM FILES clause with the LOG ERRORS clause specified, the diagnostic information for failed rows is recorded according to that clause and the SHOW WARNINGS statement. For details, see log_errors below.

Notice

• If the LOG ERRORS clause is not specified, the behavior is the normal import behavior, that is, an error is reported 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 recorded, and the error code will also be a diagnostic error, namely "reject limit reached".

  Rules for wildcards during multi-file direct load

  To facilitate multi-file imports, 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:

      • At least one matching file must exist; 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 global match. If no match is found, error 4027 is reported.

      • Only POSIX GLOB functions support 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

  Note

  LOAD DATA supports using \N to represent NULL when loading data.

  Import data from a server-side file

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

  1. Set the global security path.

     obclient> SET GLOBAL secure_file_priv = "/"
     Query OK, 0 rows affected
     obclinet> \q
     Bye
     

     Note

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

  2. After reconnecting 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: 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 in the data/csv directory 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 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 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 -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, use an OBClient client of version V2.2.4 or later. If you do not require a specific version of the OBClient client, you can also use a MySQL client to connect to the database.

  2. On 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: 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 a 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 /*+ 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 IP addresses in the example commands have been masked. During verification, you must replace them with your actual machine IP address.

  The following example shows how to create an external table when the external file is located locally or in MySQL-compatible mode of OceanBase Database. The procedure is as follows:

  1. Prepare external files.

     Run the following command to create a file named column_conv.csv in the /home/admin/test_csv directory on the server where you want to log in to the OBServer node.

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

     The file content is as follows:

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

  2. Set the path of the file to import.

     Notice

     For security reasons, when setting the system variable secure_file_priv, you must connect to the database via a local Unix 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 mysql001 tenant using a local Unix socket connection.

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

     3. Execute 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.

     Example:

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

  4. Create the table 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 table test_tbl1 from the external URL table, specifying 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 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 how to connect to OceanBase Database, see Overview of connection methods.
  • For more examples of using the LOAD DATA statement, see Import data by using the LOAD DATA statement.
  • For more examples of using direct load with LOAD DATA, see Import data by using direct load with LOAD DATA.