通常、データベース内のテーブルデータはデータベースのストレージスペースに保存されますが、外部テーブルのデータは外部ストレージサービスに保存されます。OceanBaseデータベースは、外部テーブル(External Table)機能を導入しており、ユーザーは外部システムに保存されているデータに対するクエリを直接実行したり、外部テーブルを通じて外部データをOceanBaseデータベースに高速インポートしたりできます。外部テーブル機能により、OceanBaseはHDFS、OSS、S3などのファイルシステム内のデータに接続してアクセスできます。CSV、ORC、Parquetなど、さまざまな形式のデータファイルに対応しています。
注意点
外部データファイルのディレクトリに時間や地域などによる明確な階層構造が存在する場合、外部パーティションテーブルとして指定することを優先的に検討してください。これにより、外部テーブルのクエリ時にパーティションプルーニングを行って処理速度を向上させることができます。外部パーティションテーブルの作成構文については、MySQLモードでの外部テーブルパーティションの作成および Oracleモードでの外部テーブルパーティションの作成を参照してください。
外部テーブルを作成する前に、外部データソースとOBServer間のネットワークが接続されていることを確認する必要があります。
適用シナリオ
外部テーブルは、分析型ビジネスでよく使用されるデータベース機能です。代表的な使用シナリオは以下のとおりです：
- ログ分析：企業では毎日、アクセスログやエラーログなど、大量のログ情報が生成されます。通常、これらのログは、まずオブジェクトストレージに保存されます。これらのログファイルを指す外部テーブルを作成することで、特定の期間内のアクセス数の統計やエラー発生頻度が最も高いモジュールの検索など、これらのログに対する複雑なクエリ操作を迅速に実行できます。
- 履歴データ分析：長期保存が必要だが頻繁にアクセスしない履歴データについては、コストの低いオブジェクトストレージやHDFSに保存し、外部テーブルを作成することで、必要な時にこうしたデータのクエリを直接実行できます。これにより、ストレージ容量を節約するとともに、データアクセス効率も確保できます。
- マルチソースデータ統合：現代企業のビジネスプロセスは、複数の異なる情報システムにまたがる場合が多く、各システムがそれぞれ異なる形式のデータを生成する可能性があります。外部テーブルを利用すると、異なるソースからさまざまな形式のデータファイル(CSV、Parquet、ORCなど)をロードし、統一されたプラットフォーム上でシステム間の結合クエリを実行することができ、企業はより包括的なデータインサイトを得ることができます。
- データレイク分析：データレイクアーキテクチャが広く普及するにつれて、ますます多くの企業が各種チャネルからの生データや半構造化データを一元的に管理・分析するために、独自のデータレイクを構築するようになっています。このような場合、データレイク内の特定の場所を指す外部テーブルを作成することで、データレイクのデータ探索アクションに直接参入し、意思決定プロセスを加速できます。
- **レイクハウス加速：**データレイク内のオフラインデータウェアハウスは、非リアルタイムのクエリ分析を提供できますが、一部のシナリオではビジネスのリアルタイム性要件を満たせない場合があります。外部テーブルを使用してデータを内部テーブルに高速インポートすることで、より優れた時効性とパフォーマンスを持つクエリ分析機能を提供できます。
例
以下の例では、外部テーブルを使用して、ローカルマシンのログファイルを読み取って分析するシナリオをシミュレートしています。
ローカルファイルにフォルダが存在し、フォルダ内のファイルは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インポートファイルのパスを設定します。
システム変数
secure_file_privを設定して、ファイルのインポートまたはエクスポート時にアクセス可能なパスを設定します。詳細な設定操作については、secure_file_privを参照してください。注意
セキュリティ上の理由により、システム変数
secure_file_privを設定する際は、ローカルソケット接続を通じてデータベースに接続し、このグローバル変数を変更するSQLステートメントを実行する必要があります。データベースに接続した後、このディレクトリファイルにアクセスするための自動パーティション外部テーブルを作成します。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が返されます。データのクエリを実行します。
外部テーブルデータのクエリを実行します。
パーティションキーの値または範囲を指定してクエリを実行すると、パーティションプルーニングが実行され、外部テーブルはパーティション下のファイルのみを読み取ります。
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';外部テーブルから内部テーブルにデータをインポートします。
構成パラメータ
default_load_modeを変更することで、インポートデータの動作を決定できます。詳細については、default_load_modeを参照してください。内部テーブルを作成します。
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') );外部データを内部テーブルにインポートします。
obclient> insert into t1 select * from ex_t1;