LOAD DATA

説明

インポートプロセス中に CSVファイル解析エラー が発生した場合、OBServerのインストールパス配下の log サブディレクトリに obloaddata.log ログファイルが生成される可能性があります。型変換失敗、主キー競合 などの場合はこのログファイルには書き込まれません。FROM FILES を使用し、LOG ERRORS 句を指定した場合、失敗行の診断情報の記録方法は、その句および SHOW WARNINGS に準じます。詳細は以下の log_errors を参照してください。

注意

• 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モードにある場合の外部テーブル作成手順を例示します。手順は以下の通りです：

  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を参照してください。

  3. 以下のコマンドを実行し、OBServerノードが存在するマシンにログインします。

    ```shell
    ssh admin@10.10.10.1
    ```
  

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

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

  3. 以下のSQLコマンドを実行し、インポートパスを /home/admin/test_csv に設定します。

    ```sql
    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ステートメントを使用したデータのダイレクトロードを参照してください。