通常、データベース内のテーブルデータはデータベース自身のストレージ領域に格納されますが、外部テーブル（External Table）のデータは外部ストレージサービス（ローカルファイルシステム、HDFS、OSS、S3 または S3 プロトコル互換のオブジェクトストレージなど）に保存されます。 外部テーブルを作成する際には、データファイルのパスとファイル形式を明確に定義する必要があります。その後、ユーザーは標準的な SQL クエリステートメントを使用して外部データにアクセスでき、通常のテーブルをクエリするかのように操作できます。
使用上の制限
- 外部テーブルは読み取り専用であり、
SELECT操作はサポートされますが、DML操作（INSERT/UPDATE/DELETEなど）はサポートされません。 - 制約（
DEFAULT、NOT NULL、UNIQUE、CHECK、PRIMARY KEY、FOREIGN KEYなど）の定義はサポートされません。 - インデックスの作成はサポートされません。
- リモートI/Oとパースのオーバーヘッドが発生するため、外部テーブルへのアクセス速度は通常、通常のテーブルよりも遅くなります。
ファイル外部テーブルの作成
構文
CREATE EXTERNAL TABLE <table_name>
(
[ <col_name> <col_type> [AS <expr>] ]
[ , <col_name> <col_type> [AS <expr>] ]
[ , ... ]
)
[PARTITION_TYPE = USER_SPECIFIED]
LOCATION = '<string>'
[AUTO_REFRESH = { IMMEDIATE | OFF | INTERVAL }]
FORMAT = (
TYPE = '<string>',
LINE_DELIMITER = '<string>' | <expr>,
FIELD_DELIMITER = '<string>' | <expr>,
ESCAPE = '<character>' | <expr>,
FIELD_OPTIONALLY_ENCLOSED_BY = '<character>' | <expr>,
ENCODING = 'UTF8MB4|GBK|GB18030|BINARY',
NULL_IF = ('<string>' | <expr>, '<string>' | <expr> ...),
SKIP_HEADER = <int>,
SKIP_BLANK_LINES = { TRUE | FALSE },
TRIM_SPACE = { TRUE | FALSE },
EMPTY_FIELD_AS_NULL = { TRUE | FALSE },
COMPRESSION = {GZIP | ZSTD | DEFLATE | NONE | SNAPPY_BLOCK}
PARALLEL_PARSE_ON_SINGLE_FILE = {TRUE | FALSE}
PARALLEL_PARSE_FILE_SIZE_THRESHOLD = <int>
MAX_ROW_LENGTH = <int>
)
[ PATTERN = '<regex_pattern>' ]
[ PARTITION BY ( <part_col_name> [, <part_col_name> ... ] ) ]
列の定義
外部テーブルの列にはデータ型を定義する必要がありますが、いかなる制約（DEFAULT、NOT NULL、UNIQUE、CHECK、PRIMARY KEY、FOREIGN KEY など）も定義できません。
デフォルトの列マッピング
デフォルトでは、ファイル内のデータ列と外部テーブルで定義された列は、順序に従って自動的に対応付けられます：
- 外部テーブルの1列目 → ファイルの1列目
- 外部テーブルの2列目 → ファイルの2列目
- 以降同様
例：
CREATE EXTERNAL TABLE ext_t1 (
c1 INT,
c2 INT
)
...
CSVファイルでは、c1はファイルの1列目に、c2は2列目に自動的にマッピングされます。
手動での列マッピング
ファイルの列の順序がテーブルの定義と一致しない場合、疑似列 metadata$filecol{N} を使用してマッピング関係を明示的に指定できます（Nは1から始まります）：
CREATE EXTERNAL TABLE ext_t1 (
c1 INT AS (metadata$filecol2), -- 外部テーブル ext_t1 の c1 列はファイルの2列目に対応します
c2 INT AS (metadata$filecol4) -- 外部テーブル ext_t1 の c2 列はファイルの4列目に対応します
)
...
注意
手動マッピングを使用する場合、すべての列についてマッピングを明示的に定義する必要があり、デフォルトの順序によるマッピングは無効になります。
LOCATION
LOCATIONは外部データファイルの保存パスを指定するために使用されます。システムはこのディレクトリおよびそのサブディレクトリ内のすべてのファイルを自動的に再帰的にスキャンします。
ローカルLOCATION形式
LOCATION = '[file://] local_file_path'
local_file_pathは相対パスまたは絶対パスにすることができます。- 相対パスのベースディレクトリはOBServerのインストールディレクトリです。
ディレクトリを指定する必要があり、単一のファイルを直接指定することはできません。単一のファイルを読み取る場合は、その親ディレクトリを指定し、
PATTERNでフィルタリングする必要があります。secure_file_privの制限を受けます：local_file_pathはsecure_file_priv設定のパスのサブディレクトリである必要があります。
リモートLOCATION形式
LOCATION = '{oss|cos|s3}://$ACCESS_ID:$ACCESS_KEY@$HOST/remote_file_path'
$ACCESS_ID、$ACCESS_KEY、$HOSTはクラウドストレージへのアクセスに必要な認証情報です。- 機密情報（AccessKeyなど）はOceanBaseによって暗号化され、システムテーブルに保存されるため、平文で公開する必要はありません。
FORMAT
CSV Format
FORMAT = (
TYPE = 'CSV',
LINE_DELIMITER = '<string>' | <expr>,
FIELD_DELIMITER = '<string>' | <expr>,
ESCAPE = '<character>' | <expr>,
FIELD_OPTIONALLY_ENCLOSED_BY = '<character>' | <expr>,
ENCODING = 'UTF8MB4|GBK|GB18030|BINARY',
NULL_IF = ('<string>' | <expr>, '<string>' | <expr> ...),
SKIP_HEADER = <int>,
SKIP_BLANK_LINES = { TRUE | FALSE },
TRIM_SPACE = { TRUE | FALSE },
EMPTY_FIELD_AS_NULL = { TRUE | FALSE },
COMPRESSION = {GZIP | ZSTD | DEFLATE | NONE | SNAPPY_BLOCK}
PARALLEL_PARSE_ON_SINGLE_FILE = {TRUE | FALSE}
PARALLEL_PARSE_FILE_SIZE_THRESHOLD = <int>
MAX_ROW_LENGTH = <int>
)
CSVタイプの説明：ファイル内のすべての列はSTRINGタイプと見なされ、クエリ時には外部テーブルで定義された列タイプに変換されます。
Parquet Format/ORC Format
-- パラメータ形式はParquet Formatです
FORMAT = ( TYPE = 'PARQUET' )
-- パラメータ形式はORC Formatです
FORMAT = ( TYPE = 'ORC' )
TYPE = 'PARQUET'またはTYPE = 'ORC'のいずれか一方を指定するだけで済みます。- スキーマはParquetファイルのメタデータから自動的に推定されます。
説明
OceanBaseのMySQLモードでは、外部テーブルで ZEROFILL 列属性の使用はサポートされていません。
型のマッピング (Parquet/ORC → OceanBase)
Hive Parquetの特記事項：
- BOOL型はサポートされていません。
- BINARY型はOracleモードではサポートされていません。
詳細については、MySQLモードのデータ型マッピングおよびOracleモードのデータ型マッピングを参照してください。
PATTERN
[ PATTERN = '<regex_pattern>' ]
LOCATIONディレクトリ配下のファイルをフィルタリングするための正規表現を指定します。- 匹合に成功したファイルのみが読み取られ、一致しないファイルはスキップされます。
PATTERNが指定されていない場合、ディレクトリ内のすべてのファイルがデフォルトで読み取られます。
パーティション
自動パーティション
PARTITION BY 句と metadata$fileurl を組み合わせて自動パーティションを実現します。
metadata$fileurl：現在の行が属するファイルの相対パス（LOCATIONをルートとして）を表します。パーティション式には
metadata$fileurlが含まれなければならず、システムはその値に基づいて動的にパーティションを計算します。
例：ログパス内の日付文字列でパーティション分けを行う
CREATE EXTERNAL TABLE t0 (
c0 VARCHAR(500) AS (NVL(METADATA$FILECOL1, '')),
c1 FLOAT(5,4) AS (METADATA$FILECOL2),
date_part VARCHAR(100) AS (SUBSTR(METADATA$fileurl, INSTR(METADATA$fileurl, '2024'), 10)),
path VARCHAR(100) AS (METADATA$fileurl)
)
LOCATION = '~/log'
FORMAT = (TYPE = 'CSV')
PARTITION BY (date_part);
- 新しいファイルが追加されると、クエリ時に
date_part式に従って対応するパーティションに自動的に分類されます。 - 式の計算に失敗した場合（型変換エラーなど）、エラーが返されます。
手動パーティション
PARTITION_TYPE = USER_SPECIFIED で手動パーティションモードを有効にします。この場合：
- 外部テーブルはパーティションを自動的に検出しません。
- ユーザーは
ALTER EXTERNAL TABLE ... ADD/DROP PARTITION LOCATIONを使用して明示的にパーティションを管理する必要があります。
作成例：
CREATE EXTERNAL TABLE table_name (
date_part VARCHAR(100) AS (METADATA$PARTITION_LIST_COL1),
col2 INT AS (METADATA$filecol2)
)
PARTITION_TYPE = USER_SPECIFIED
AUTO_REFRESH = OFF
LOCATION = '~/log'
FORMAT = (TYPE = 'CSV')
PARTITION BY (date_part);
METADATA$PARTITION_LIST_COL1はパーティション列のプレースホルダー偽列です。- 最初の外部テーブルにはファイルメタデータがないため、手動でパーティションを追加する必要があります。
パーティションの追加：
ALTER EXTERNAL TABLE table_name
ADD PARTITION (date_part = '2024-06')
LOCATION '2024/06';
~/log/2024/06フォルダ内のすべてのファイルをdate_part = '2024-06'パーティションに含めます。- 一つのパーティションには一つの
LOCATIONのみをバインドできますが、一つのLOCATIONに複数のパーティションをバインドできます（データ重複を引き起こします）。
パーティションの削除：
ALTER EXTERNAL TABLE table_name DROP PARTITION LOCATION '2022/02';
- そのパス下のすべての関連パーティションおよびそのファイルメタデータを削除します。
偽列
外部テーブルは以下の3種類の偽列をサポートしています：
仮想列 |
説明 |
|---|---|
METADATA$FILECOL{N} |
マッピングファイルのN列目（N ≥ 1） |
METADATA$FILEURL |
現在の行が属するファイルの相対パス（テーブル作成ステートメントのlocationをrootとする相対パス） |
METADATA$PARTITION_LIST_COL{N} |
手動パーティションモードでは、N番目のパーティションキーを示すプレースホルダー |
外部ファイルの管理
外部テーブルは、LOCATION ディレクトリ配下で PATTERN に一致するファイルの一覧をシステムテーブルに格納し、クエリ時にこの一覧に基づいて外部ファイルにアクセスします。ファイルリストは自動または手動で更新できます。
外部テーブルファイルの確認
- MySQLテナント：
SELECT * FROM oceanbase.DBA_OB_EXTERNAL_TABLE_FILES
WHERE table_schema = 'DATABASE4' AND table_name = 'T0';
- Oracleテナント：
SELECT * FROM DBA_OB_EXTERNAL_TABLE_FILES
WHERE owner = 'DATABASE4' AND table_name = 'T0';
- システムテナント：
SELECT * FROM oceanbase.CDB_OB_EXTERNAL_TABLE_FILES;
ファイルリストの手動更新
ALTER EXTERNAL TABLE <table_name> REFRESH;
自動更新ポリシー(AUTO_REFRESH)
ポリシー |
説明 |
|---|---|
IMMEDIATE |
クエリのたびにファイルリストを自動的に更新します。 |
OFF |
自動更新は行わず、手動でのみ更新が可能です。 |
INTERVAL |
DBMS_EXTERNAL_TABLE_AUTO_REFRESH_EXTERNAL_TABLE(x) を使用して定期タスクを設定します (単位：秒)。 |