外部テーブルのINSERT INTO SELECTによるインポート

通常、データベース内のテーブルデータはデータベースのストレージ領域に格納されますが、外部テーブルのデータは外部ストレージサービスに格納されます。OceanBaseデータベースは外部テーブル機能を導入し、ユーザーは外部システムに保存されたデータを直接クエリすることができるだけでなく、外部テーブルを通じて外部データをOceanBaseデータベースに迅速にインポートすることもできます。外部テーブル機能により、OceanBaseはHDFS、OSS、S3などのファイルシステムに接続してアクセスでき、CSV、ORC、Parquetなど、さまざまな形式のデータファイルをサポートします。

データがローカルパス、オブジェクトストレージ、またはHDFSなどの外部リソースに保存されており、単一のSQLでインポートしたい場合は、FILESテーブル関数を使用して外部ファイルにアクセスできます。詳細な使用方法については、INSERT INTO SELECT FROM FILESおよびLOAD DATA FROM FILESを参照してください。

注意事項

• 外部データファイルのディレクトリに時間、省市などの次元に基づいて階層化された構造がある場合、外部パーティションテーブルとして指定することを優先的に検討してください。これにより、外部テーブルのクエリ時にパーティションプルーニングを行い、クエリの高速化を図ることができます。外部パーティションテーブルの作成構文については、MySQLモードでの外部テーブルパーティションの作成およびOracleモードでの外部テーブルパーティションの作成を参照してください。

• 外部テーブルを作成する前に、外部データソースとOBServer間のネットワークが接続可能であることを確認してください。

適用シナリオ

外部テーブルは分析系業務でよく使われるデータベース機能です。代表的な使用シナリオは以下のとおりです：

• ログ分析：企業では毎日大量のログ情報が生成されます。これにはアクセスログやエラーログなどが含まれます。これらのログは通常、まずオブジェクトストレージに保存されます。これらのログファイルを指す外部テーブルを作成することで、特定の期間のアクセス量の統計や、エラー発生頻度が最も高いモジュールの特定など、複雑なクエリ操作を迅速に実行できます。
• 履歴データ分析：長期保存が必要だが頻繁にアクセスされない履歴データについては、コストの低いオブジェクトストレージやHDFSに保存することができます。外部テーブルを作成することで、必要な際に直接このデータをクエリできるため、ストレージ容量を節約しつつデータアクセスの効率を保証できます。
• マルチソースデータ統合：現代の企業のビジネスプロセスでは、しばしば複数の異なる情報システムが関わってきます。各システムはそれぞれ独自の形式のデータを生成する可能性があります。外部テーブルを利用することで、異なるソースから様々な形式のデータファイル(CSV、Parquet、ORCなど)を読み込み、統一されたプラットフォーム上でシステムを跨ぐ連携クエリを実行することができます。これにより、企業はより包括的なデータインサイトを得ることができます。
• データレイク分析：データレイクアーキテクチャがますます普及する中、ますます多くの企業が、さまざまなチャネルからの生データや半構造化データを一元的に管理・分析するために、自社のデータレイクを構築し始めています。このような場合、データレイク内の特定の位置を指す外部テーブルを作成することで、データレイクのデータ探索活動に直接参加し、意思決定プロセスを加速することができます。
• レイクハウスの高速化：データレイク内のオフラインデータウェアハウスは、非リアルタイムのクエリ分析を提供できますが、一部のシナリオではビジネスのリアルタイム要件を満たせない場合があります。外部テーブルを利用してデータを内部テーブルに迅速にインポートすることで、より時宜を得た、より高性能なクエリ分析能力を提供できます。

例

以下の例は、外部テーブルを利用してローカルのログファイルを読み取り、分析するシナリオをシミュレートしています。

1. ローカルファイルには、CSV形式のファイルが含まれるフォルダが存在します。これらのファイルは、パーティション外部テーブルを使用して管理・整理できます。ファイルの構成は以下のとおりです。

   external_table_mock_log
   ├── 2023-06-01
   │   ├── server_log1.csv
   │   └── server_log2.csv
   ├── 2023-06-02
   │   └── server_log1.csv
   ├── 2023-06-03
   │   ├── server_log1.csv
   │   ├── server_log2.csv
   │   └── server_log3.csv
   └── 2023-07-01
       └── server_log1.csv
   

   ここではserver_log1.csvファイルを例に説明します。ファイルの内容は以下のとおりです：

   2023-06-01 14:42:37.568624, INTERNAL ERROR, -4007, Not supported
   2023-06-01 14:42:38.861356, ITER END, -4008, traverse map failed
   2023-06-01 14:42:39.931161, NEED WAIT, -4076, query and update last id fail
   2023-06-01 14:42:39.931161, SUCCESS, 0, do flush cache success
   

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

   システム変数secure_file_privを設定し、インポートまたはエクスポート時にアクセス可能なパスを構成します。その他の設定操作については、secure_file_privを参照してください。

   注意

   セキュリティ上の理由から、システム変数secure_file_privを設定する際は、ローカルのUnix Socket接続を介してデータベースに接続し、このグローバル変数を変更するSQLステートメントを実行する必要があります。

3. データベースに接続した後、このディレクトリ内のファイルにアクセスするために、自動パーティション外部テーブルを作成します。SQLコマンドは以下のとおりです。

   obclient> create external table ex_t1 (
   time date,
   errstate varchar(30),
   errcode int,
   errcontent varchar(100),
   date_key date as (substr(substr(metadata$fileurl, instr(metadata$fileurl, '%') + 1), 1, 10))
   )
   location='/home/admin/external_table_mock_log'
   FORMAT (
       type = 'csv',
       field_delimiter = ',',
       SKIP_BLANK_LINES = TRUE
   )
   partition by (date_key)
   ;
   

   METADATA$FILEURL 列にはパスとファイル名の情報が記録されています。ここではローカルファイルが返され、ip:port%2023-06-01/server_log2.csv となります。

4. データのクエリ。

   • 外部テーブルデータのクエリ。

     パーティションキーの値または範囲を指定してクエリを実行すると、パーティションプルーニングが行われ、外部テーブルはそのパーティション内のファイルのみを読み取ります。

     obclient> select * from ex_t1 where date_key = '2023-06-01';
     

   • 外部テーブルのファイルリストのクエリ。

     外部テーブル作成後は、ALL_OB_EXTERNAL_TABLE_FILES ビューを使用してファイルリストを確認できます。

     SELECT * FROM oceanbase.ALL_OB_EXTERNAL_TABLE_FILES WHERE TABLE_NAME='ex_t1';
     

5. 外部テーブルから内部テーブルへのデータインポート。

   設定パラメータ default_load_mode を変更することで、データインポートの動作を決定できます。詳細については、default_load_modeを参照してください。

   1. 内部テーブルの作成。

      obclient> create table t1 (
      time date,
      errstate varchar(30),
      errcode int,
      errcontent varchar(100),
      date_key date
      )
      partition by list columns(date_key)
      (partition p20230601 values in ('2023-06-01'),
       partition p20230602 values in ('2023-06-02'),
       partition p20230603 values in ('2023-06-03'),
       partition p20230701 values in ('2023-07-01')
       );
      

   2. 外部データの内部テーブルへのインポート。

      obclient> insert into t1 select * from ex_t1;
      

• URL外部テーブルの詳細については、URL外部テーブルを参照してください。

データレイク章との関連性

ファイル外部テーブル、Catalog、ODPSなどの機能は、データレイクドキュメントと相互に補完し合っています。シナリオに応じて、以下のドキュメントを参照してください：

• 外部テーブルの概要
• ファイル外部テーブル
• ODPS外部テーブル

関連ドキュメント

• MySQLモードでの外部テーブルの作成

• Oracleモードでの外部テーブルの作成

• MySQLモードでの外部テーブルパーティションの作成

• Oracleモードでの外部テーブルパーティションの作成

• MySQLモードでの外部ファイルの管理

• Oracleモードでの外部ファイルの管理