LOAD DATA

注意

• LOG ERRORS句を指定しない場合、通常のインポート動作となり、最初のエラーに遭遇すると直ちにエラーが報告されます。
• LOG ERRORS句を指定したもののREJECT LIMIT句を指定しなかった場合、LIMITを0に設定した診断と同等になり、最初のエラーに遭遇すると操作は失敗します。ただし、最初に遭遇したエラーは記録され、エラーコードも診断関連のエラー、すなわち「reject limit reached」となります。

  エラーログの確認

  OceanBaseデータベースは、エクスポートされたエラーログ情報を確認するための以下のSQLステートメントをサポートしています：

  SELECT * FROM READ_ERROR_LOG('diagnosis_log_path');
  

  ここで、diagnosis_log_path はエラーログのパスを表します。このSQLステートメントは実行時に、以下のURL外部テーブルステートメントと同等です：

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

  例：

  1. 指定されたログパスがファイル名の場合（/ で終わらない）。

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

     このSQLステートメントに対応するURL外部テーブルステートメントは次のとおりです（ファイル名はpatternとして使用され、ファイルがフィルタリングされます）：

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

  2. 指定されたログパスがフォルダの場合（/ で終わる）。

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

     このSQLステートメントに対応するURL外部テーブルステートメントは次のとおりです（patternは使用されません）：

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

  複数ファイルダイレクトロード時のワイルドカードのルール

  複数ファイルのインポートを容易にするため、ファイルワイルドカード機能が導入されました。これはサーバーサイドおよびOSSファイルのインポートに適用されますが、クライアント側からのファイルインポートには適用されません。

  • サーバーサイドでのワイルドカードの使用

    • マッチングルール：

      • ファイル名のマッチング：load data /*+ parallel(20) direct(true, 0) */ infile '/xxx/test.*.csv' replace into table t1 fields terminated by '|';

      • ディレクトリのマッチング：load data /*+ parallel(20) direct(true, 0) */ infile '/aaa*bb/test.1.csv' replace into table t1 fields terminated by '|';

      • ディレクトリとファイル名の同時マッチング：load data /*+ parallel(20) direct(true, 0) */ infile '/aaa*bb/test.*.csv' replace into table t1 fields terminated by '|';

    • 注意事項：

      • マッチするファイルが少なくとも1つ存在しなければなりません。存在しない場合、エラーコード4027が返されます。

      • 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 は全体一致と見なされます。マッチしない場合、エラー4027が報告されます。

      • POSIXのGLOB関数がサポートするワイルドカードのみがサポートされます。例えば、test.6*(6|0).csv や test.6*({0.csv,6.csv}|.csv) は ls コマンドでは検索できますが、GLOB関数ではマッチせず、エラー4027が報告されます。

  • クラウドオブジェクトストレージサービス（OSS）でのワイルドカードの使用

    • マッチングルール：

      ファイル名のマッチング：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 '|';

    • 注意事項：

      • ディレクトリのマッチングはサポートされていません。例えば、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 '|'; は OB_NOT_SUPPORTED を返します。

      • ファイル名のワイルドカードは * と ? のみをサポートします。他のワイルドカードは入力は可能ですが、結果とマッチしません。

  例

  説明

  LOAD DATA でデータをロードする際、\N を使用して NULL を表現できます。

  サーバー側ファイルからのデータインポート

  例1：サーバー側ファイルからデータをインポートします。

  1. グローバルセキュリティパスを設定します。

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

     説明

     secure_file_privはGLOBAL変数であるため、\qを実行して終了し、変更を有効にする必要があります。

  2. データベースに再接続した後、外部ファイルからデータをインポートします。

     obclient> LOAD DATA INFILE 'test.sql' INTO TABLE t1;
     Query OK, 0 rows affected
     

  例2：APPENDヒントを使用してダイレクトロード機能を有効にします。

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

  例3：CSV形式ファイルをインポートします。

  • test1.csvファイルのすべての列をインポートします。

    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;
    

  • data/csv パス下の test1.csv ファイルの c1、c2 列を読み取り、それらをテーブル t1 の col1、col2 列にインポートします。

    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);
    

  例4：PARQUET形式ファイルをインポートします。

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

  例5：ORC形式ファイルをインポートします。

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

  例5：ODPS形式ファイルをインポートします。

  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;
  

  クライアント（ローカル）ファイルからデータをインポートする

  例1：ローカルファイルからOceanBaseデータベースのテーブルにデータをインポートします。

  1. ターミナルまたはコマンドプロンプトウィンドウを開き、以下のコマンドを入力してクライアントを起動します。

     obclient --local-infile -hxxx.xxx.xxx.xxx -P2881 -uroot@mysql001 -p****** -A -Dtest
     

     結果は次のとおりです：

     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]>
     

     注意

     LOAD DATA LOCAL INFILE機能を使用するには、V2.2.4以降のバージョンのOBClientクライアントを使用してください。指定バージョンのOBClientクライアントがない場合は、MySQLクライアントを使用してデータベースに接続することもできます。

  2. クライアントでLOAD DATA LOCAL INFILEステートメントを実行し、ローカルのデータファイルを読み込みます。

     obclient [test]> LOAD DATA LOCAL INFILE '/home/admin/test_data/tbl1.csv' INTO TABLE tbl1 FIELDS TERMINATED BY ',';
     

     結果は次のとおりです：

     Query OK, 3 rows affected
     Records: 3  Deleted: 0  Skipped: 0  Warnings: 0
     

  例2：COMPRESSIONを設定して圧縮ファイルを直接インポートします。

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

  例3：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';
  

  • サブパーティションダイレクトロードの指定

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

  OSSファイルからデータをインポートする

  例1：direct(bool, int) ヒントを使用してダイレクトロード機能を有効にします。ダイレクトロードファイルは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';
  

  サーバー側ファイルからURL外部テーブル方式でデータをインポートする

  注意

  例に含まれるIPアドレスに関するコマンドはマスキングされています。検証時には、ご自身のマシンの実際のIPアドレスを記入してください。

  以下では、外部ファイルの場所がサーバー（OBServerノード）上およびOceanBaseデータベースのMySQLモード上にある場合の外部テーブル作成例を示します。手順は以下のとおりです：

  1. OBServerノードにディレクトリを作成します。/home/admin/test_csv ディレクトリに外部データを、/home/admin/test_into ディレクトリにエラー情報を、/home/admin/test_badfile ディレクトリに誤ったデータファイルを格納します。

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

  2. 外部ファイルを準備します。/home/admin/test_csv ディレクトリ内にファイル type_cast.csv を作成します。

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

     ファイルの内容は以下のとおりです：

     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. インポートファイルのパスを設定します。

     注意

     セキュリティ上の理由により、システム変数 secure_file_priv を設定する際は、ローカルソケット接続を介してデータベースに接続し、このグローバル変数を変更するSQLステートメントを実行する必要があります。詳細については、secure_file_privを参照してください。

     1. 以下のコマンドを実行して、OBServerノードが配置されているマシンにログインします。

        ssh admin@10.10.10.1
        

     2. 以下のコマンドを実行して、ローカルUnixソケット接続を介してテナント mysql001 に接続します。

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

     3. 以下のSQLコマンドを実行して、インポート・エクスポートを無制限に設定します。

        SET GLOBAL secure_file_priv = "/";
        

  4. テナント mysql001 に再接続します。

     例：

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

  5. テーブル test_tbl1 を作成します。

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

  6. 診断ログで使用する圧縮アルゴリズムを AUTO に設定します。

     ALTER SYSTEM SET load_data_diagnosis_log_compression = 'AUTO';
     

     診断ログで使用する圧縮アルゴリズムの設定方法の詳細については、load_data_diagnosis_log_compressionを参照してください。

  7. 最大ログファイルサイズを1KBに設定します。エクスポートするログのサイズが1KBを超えると、自動的に2番目のファイルが生成され、エクスポートが継続されます。

     ALTER SYSTEM SET load_data_diagnosis_log_max_size = '1K';
     

     単一診断ログファイルのサイズ設定方法の詳細については、load_data_diagnosis_log_max_sizeを参照してください。

  8. LOAD DATA ステートメントのURL外部テーブルインポート構文を使用して、テーブル test_tbl1 にデータをインポートします。エラー診断を指定し、エラーログを格納するディレクトリを /home/admin/test_into/、誤ったデータファイルを格納するディレクトリを /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/';
     

     実行結果は次のとおりです：

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

  9. read_error_log ステートメントを使用して、エラーログの内容を確認します。

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

     実行結果は次のとおりです：

     +------------+---------------+-------------+-------------------------------------------------------------------------------------------------------------------+
     | ERROR CODE | FILE NAME     | LINE NUMBER | ERROR MESSAGE                                                                                                     |
     +------------+---------------+-------------+-------------------------------------------------------------------------------------------------------------------+
     | -4226      | type_cast.csv | 4           | fail to scan file type_cast.csv at line 4 for column "db_test"."test_tbl1"."col1", error: Incorrect integer value |
     | -4226      | type_cast.csv | 9           | fail to scan file type_cast.csv at line 9 for column "db_test"."test_tbl1"."col1", error: Incorrect integer value |
     | -4226      | type_cast.csv | 7           | fail to scan file type_cast.csv at line 7 for column "db_test"."test_tbl1"."col2", error: Incorrect integer value |
     | -4226      | type_cast.csv | 2           | fail to scan file type_cast.csv at line 2 for column "db_test"."test_tbl1"."col3", error: Incorrect integer value |
     +------------+---------------+-------------+-------------------------------------------------------------------------------------------------------------------+
     4 rows in set
     

  10. test_tbl1 のデータを確認します。

      SELECT * FROM test_tbl1;
      

      実行結果は次のとおりです：

      +------+------+------+
      | 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. URL外部テーブルを使用して、badファイルの内容を確認します。

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

      実行結果は次のとおりです：

      +------+------+------+
      | c1   | c2   | c3   |
      +------+------+------+
      | ds   | 6    | 32   |
      | kj   | a6   | 32   |
      | 6    | v4   | af   |
      | 2    | 4    | af   |
      +------+------+------+
      4 rows in set
      

  関連ドキュメント

  • OceanBaseデータベースへの接続方法の詳細については、接続方法の概要を参照してください。
  • LOAD DATAステートメントの使用例については、LOAD DATAステートメントを使用したデータのインポートを参照してください。
  • LOAD DATAダイレクトロードの使用例については、LOAD DATAステートメントを使用したデータのダイレクトロードを参照してください。