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 を返します。

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

  例

  サーバー側（OBServerノード）のファイルからデータをインポートする

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

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

     注意

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

     obclient> SET GLOBAL secure_file_priv = "/";
     

  2. ログアウトします。

     説明

     secure_file_priv は GLOBAL 変数であるため、\q を実行してログアウトすることで変更が有効になります。

     obclinet> \q
     

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

     Bye
     

  3. データベースに再接続した後、LOAD DATA ステートメントを使用してデータをインポートします。

     • 通常インポート。

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

     • APPEND ヒントを使用してダイレクトロードを有効化します。

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

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

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

     結果は次のとおりです：

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

     注意

     LOAD DATA LOCAL INFILE機能を使用するには、V2.2.4以降のバージョンのOBClientクライアントを使用してください。

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

     obclient [SYS]> 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 /*+ 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 ',';
  

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

  注意

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

  以下では、外部ファイルの場所がサーバー（OBServerノード）上およびOceanBaseデータベースのOracleモードの場合を例に、手順を説明します。

  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ソケット接続方式でテナント oracle001 に接続します。

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

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

        SET GLOBAL secure_file_priv = "/";
        

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

     例：

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

  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                                                                                         |
     +------------+---------------+-------------+-------------------------------------------------------------------------------------------------------+
     | -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. 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
      

  関連ドキュメント

  • LOAD DATA ステートメントの使用例については、LOAD DATAステートメントを使用したデータのインポートを参照してください。
  • LOAD DATA ダイレクトロードの使用例については、LOAD DATAステートメントを使用したデータのダイレクトロードを参照してください。