LOAD DATAステートメントを使用したデータのインポート

OceanBaseデータベースは、LOAD DATA コマンドを使用して外部ファイルのデータをデータベーステーブルにロードすることをサポートしています。

制限

トリガー(Trigger)を持つテーブルでは、LOAD DATA ステートメントの使用は禁止されています。

注意事項

データインポート速度を向上させるため、OceanBaseデータベースはLOAD DATA操作において並列設計を採用しています。このプロセスでは、インポートするデータが複数のサブタスクに分割され、並行して実行されます。各サブタスクは独立したトランザクションとして処理され、実行順序はランダムです。そのため、以下の点にご注意ください：

• 全体のデータインポートの原子性は保証されません。
• 主キーのないテーブルについては、データ書き込みの順序がファイル内のデータ順序と一致しない場合があります。

使用シナリオ

LOAD DATAは現在、CSV形式のテキストファイルをインポートできます。インポートプロセス全体は以下のとおりです：

1. ファイルを解析します。

   OceanBaseは、ユーザーが入力したファイル名に基づいてファイル内のデータを読み取り、ユーザーが入力した並列度に基づいて、入力ファイル内のデータを並列または直列で解析するかを決定します。

2. データを分散します。

   OceanBaseは分散型データベースシステムであるため、各パーティションのデータは異なるOBServerノードに分散されている可能性があります。そのため、LOAD DATAは解析されたデータに対して計算を行い、データを送信するOBServerノードを決定します。

3. データを挿入します。

   ターゲットのOBServerノードが送信されたデータを受信すると、ローカルでINSERT操作を実行し、データを対応するパーティションに挿入します。

LOAD DATA構文

LOAD DATA 構文の詳細については、LOAD DATA(MySQLモード)およびLOAD DATA(Oracleモード)を参照してください。

LOAD DATA実行権限の取得

LOAD DATA ステートメントを実行する前に、対応する権限を取得する必要があります。以下は、実行権限を付与する手順です：

1. ユーザーに FILE 権限を付与します。

   例：

   ユーザーに FILE 権限を付与するには、次のコマンドを使用できます：

   GRANT FILE ON *.* TO user_name;
   

   ここで、user_name は LOAD DATA コマンドを実行するユーザーです。

2. その他の必要な権限を付与します。

   • MySQLモードでは、対応するテーブルに対する INSERT 権限が必要です。

     例：

     ユーザーに INSERT 権限を付与するには、次のコマンド形式を使用できます：

     GRANT INSERT ON database_name.tbl_name TO user_name;
     

     ここで、database_name はデータベース名、tbl_name はテーブル名、user_name は LOAD DATA コマンドを実行するユーザーです。

   • Oracleモードでは、CREATE SESSION 権限が必要です。

     例：

     ユーザーに CREATE SESSION 権限を付与するには、次のコマンド形式を使用できます：

     GRANT CREATE SESSION TO user_name;
     

     ここで、user_name は権限を付与するユーザー名です。

例

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

1. OBServerノードが存在するマシンにログインします。

   例：

   ssh admin@10.10.10.1
   

2. /home/admin/test_data ディレクトリにテストデータを作成します。

   例：

   以下のコマンドを実行して、student.sql という名前のスクリプトを作成します。

   vi student.sql
   

3. 編集モードに入り、テストデータを追加します。

   *例：

   i キーまたは Insert キーを押して vi エディタの挿入モードに入り、挿入モードで以下の内容を追加します。

   1,"lin",98
   2,"hei",90
   3,"ali",95
   

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

   注意

   セキュリティ上の理由により、システム変数 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. ファイルの保存ディレクトリを / に設定します。これは制限なく、任意のパスからアクセス可能であることを意味します。

      SET GLOBAL SECURE_FILE_PRIV = "/";
      

5. データベースに再接続します。

   例：

   obclient -h127.0.0.1 -P2881 -utest_user001@mysql001 -p****** -A
   

6. テストテーブルを作成します。

   例：

   以下のSQL文を実行して、テストテーブル student を作成します。

   obclient [test]> CREATE TABLE student (id INT, name VARCHAR(50), score INT);
   

7. LOAD DATA ステートメントを使用してデータをインポートします。

   例：

   以下の LOAD DATA ステートメントを使用して、ファイルからデータベーステーブルにデータをロードします。ここで：

   • ロードするファイルのパスとファイル名を /home/admin/test_data/student.sql と指定します。
   • ロードするデータのターゲットテーブル名を student と指定します。
   • データファイル内のフィールド区切り文字をカンマと指定します。
   • データファイル内のフィールド（文字型）をダブルクォーテーションマークで囲むことを指定します。
   • データファイル内の行を終了文字として改行を使用することを指定します。
   • ロードするデータファイル内の列とターゲットテーブル内の列のマッピング関係を指定します。データファイル内の最初の列はターゲットテーブルの id 列に、2 番目の列は name 列に、3 番目の列は score 列にマッピングされます。

   obclient [test]> LOAD DATA INFILE '/home/admin/test_data/student.sql'
   INTO TABLE student
   FIELDS TERMINATED BY ','
   ENCLOSED BY '"'
   LINES TERMINATED BY '\n'
   (id,name,score);
   

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

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

8. テーブル情報を確認します。

   例：

   obclient [test]> SELECT * FROM student;
   

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

   +------+------+-------+
   | id   | name | score |
   +------+------+-------+
   |    1 | lin  |    98 |
   |    2 | hei  |    90 |
   |    3 | ali  |    95 |
   +------+------+-------+
   3 rows in set
   

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

以下のステートメントを使用して、ローカルファイルからOceanBaseデータベースのテーブルにデータをインポートします。

1. ローカルの /home/admin/test_data ディレクトリにテストデータを作成します。

   例：

   以下のコマンドを実行して、test_tbl1.csv という名前のスクリプトを作成します。

   vi test_tbl1.csv
   

2. 編集モードに入り、テストデータを追加します。

   *例：

   i キーまたは Insert キーを押して vi エディタの挿入モードに入り、挿入モードで以下の内容を追加します。

   1,11
   2,22
   3,33
   

3. クライアントを起動します。

   例：

   以下のステートメントを実行して、OBClientコマンドラインツールを使用してOceanBaseデータベースに接続します。--local-infile パラメータを追加して、ローカルファイルからデータを読み込む機能を有効にします。

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

   注意

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

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

   例：

   CREATE TABLE test_tbl1(col1 INT,col2 INT);
   

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

   例：

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

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

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

6. テーブル情報を確認します。

   例：

   obclient [test]> SELECT * FROM test_tbl1;
   

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

   +------+------+
   | col1 | col2 |
   +------+------+
   |    1 |   11 |
   |    2 |   22 |
   |    3 |   33 |
   +------+------+
   3 rows in set
   

例外処理

ログファイル

インポート中にエラーが発生した場合、エラーを引き起こした INSERT ステートメントはロールバックされ、LOAD DATA ステートメントによってobserverプロセスのインストールパスの log サブディレクトリに obloaddata.log.<XXXXXX> という名前のログファイルが生成されます。以下はログファイルの内容例です。ログには LOAD DATA によって生成されたタスクの基本情報が含まれ、テナント名、入力ファイル名、ターゲットテーブル名、並列度、使用される LOAD DATA コマンドが記録され、各行単位で具体的なエラー情報が示されます。

Tenant name:    mysql
File name:  /home/admin/a.csv
Into table: `test`.`t`
Parallel:   1
Batch size: 1000
SQL trace:  YD7A20BA65670-0005AADAAA3C****
Start time: 2020-07-29 21:08:13.073741
Load query:
load data infile '/home/admin/test.csv' into table t fields terminated by ',' lines terminated by '\n'
Row ErrCode ErrMsg
1   1062    Duplicated primary key
2   1062    Duplicated primary key

関連ドキュメント

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