LOAD DATA

注意

• LOG ERRORS 句を指定しない場合、通常のインポート動作になります。つまり、最初のエラーが発生した時点で直ちにエラーが報告されます。
• LOG ERRORS 句を指定したが、REJECT LIMIT 句を指定していない場合、LIMIT を0に指定した場合と同じ挙動になります。最初のエラーが発生すると操作は失敗しますが、最初のエラーのみが記録され、エラーコードも診断関連のエラーである「reject limit reached」となります。

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

  複数ファイルのロードを容易にするために、ファイルワイルドカード機能が導入されました。この機能はサーバーサイドおよび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アドレスを記入してください。

  以下の手順では、外部ファイルがローカルに存在する場合と、OceanBaseデータベースのMySQLモード内に存在する場合の2つの例を挙げます。

  1. 外部ファイルを準備します。

     以下のコマンドを実行し、OBServerノードが存在するマシンの /home/admin/test_csv ディレクトリ内に column_conv.csv ファイルを作成します。

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

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

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

  2. インポートするファイルパスを設定します。

     注意

     セキュリティ上の理由により、システム変数 secure_file_priv を設定する際は、ローカルのUnixソケット経由でのみデータベースに接続し、このグローバル変数を変更する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コマンドを実行して、インポートパスを /home/admin/test_csv に設定します。

        SET GLOBAL secure_file_priv = "/home/admin/test_csv";
        

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

     例：

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

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

     CREATE TABLE test_tbl1(col1 VARCHAR(5), col2 VARCHAR(5), col3 VARCHAR(5));
     

  5. LOAD DATA ステートメントを使用して、URL外部テーブル構文でテーブル test_tbl1 にデータをインポートし、エラーデバッグを指定します。

     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;
     

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

     Query OK, 1 row affected, 2 warnings
     Records: 1  Deleted: 0  Skipped: 0  Warnings: 2
     

  6. Warningsを確認します。

     SHOW warnings;
     

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

     +---------+------+----------------------------------------------------------------------------------------------------------------------+
     | 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. test_tbl1 のデータを確認します。

     SELECT * FROM test_tbl1;
     

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

     +------+-------+-------+
     | col1 | col2  | col3  |
     +------+-------+-------+
     | 1    | short | short |
     +------+-------+-------+
     1 row in set
     

  参照ドキュメント

  • OceanBaseデータベースに接続する方法の詳細については、接続方式の概要を参照してください。
  • LOAD DATAステートメントを使用したデータインポートに関するサンプル情報については、LOAD DATAステートメントを使用したデータインポートを参照してください。
  • LOAD DATAステートメントによるダイレクトロードに関するサンプル情報については、LOAD DATAステートメントによるダイレクトロードを参照してください。