ファイル外部テーブル

本記事では、LOCATION + FORMAT を使用して外部のファイル（CSV、Parquet、ORC）にアクセスする外部テーブルについて説明します。ODPS APIを使用する外部テーブル（ODPS外部テーブル）や、External Catalog経由でのアクセス方法は含まれません。

通常、データベース内のテーブルデータはデータベース自身のストレージ領域に格納されますが、ファイル外部テーブルのデータは外部ストレージサービス（ローカルファイルシステム、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 = AUTO | GZIP | ZSTD | DEFLATE | NONE
)
[ 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はファイルの最初の列に、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:// | sfile://] local_file_path'

• file://：このプロトコルヘッダーは、複数のOBServerノードで同じ論理パスのファイルに遭遇した場合、それらが異なるファイルとして扱われ、個別に処理されることを示します。各ノードはローカルパス下のファイルを独立して読み取ります。

• sfile://：このプロトコルヘッダーは、複数のOBServerノードで同じ論理パスのファイルに遭遇した場合、システムが論理パスに基づいて重複排除処理を行い、データを1部のみ読み取ることを保証します（マルチサーバーロードバランシングをサポート）。

  注意

  • sfile:// を使用する場合、システムはファイルサイズの一貫性検証を行います。同一の論理パス下のファイルが異なるOBServerノードでサイズが一致しない場合、システムはデータの不整合による結果の誤りを防ぐために、OB_INVALID_EXTERNAL_FILE エラーを返します。
  • V4.4.2バージョンでは、V4.4.2 BP2バージョンから CREATE EXTERNAL TABLE 構文のローカルLocation形式に sfile:// プロトコルヘッダーのサポートが追加されました。

• local_file_pathは相対パスまたは絶対パスを指定できます。

  • 相対パスのベースディレクトリはOBServerのインストールディレクトリです。

• ディレクトリを指定する必要があり、単一のファイルを直接指定することはできません。単一のファイルを読み取る必要がある場合は、その親ディレクトリを指定し、PATTERNでフィルタリングします。

• secure_file_privの制限を受けます：local_file_pathはsecure_file_priv設定パスのサブディレクトリである必要があります。

リモートLOCATION形式

LOCATION = '{oss|s3}://$ACCESS_ID:$ACCESS_KEY@$HOST/remote_file_path'

• $ACCESS_ID、$ACCESS_KEY、$HOSTはクラウドストレージへのアクセスに必要な認証情報です。
• 機密情報（AccessKeyなど）はOceanBaseによって暗号化され、システムテーブルに保存されるため、平文で公開する必要はありません。

FORMAT

完全なFORMATパラメータ（CSV COMPRESSIONなどを含む）はCREATE EXTERNAL TABLE SQLリファレンスに準じます。以下は一般的な例です。

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 = AUTO | GZIP | ZSTD | DEFLATE | NONE
)

CSVタイプの説明：ファイル内のすべての列はSTRINGタイプと見なされ、クエリ時には外部テーブルで定義された列タイプに変換されます。

Parquet Format/ORC Format

-- パーチェット形式
FORMAT = ( TYPE = 'PARQUET' )

-- ORC形式
FORMAT = ( TYPE = 'ORC' )

• TYPE = 'PARQUET'またはTYPE = 'ORC'のいずれか一方を指定するだけで済みます。
• スキーマは、Parquetファイルのメタデータから自動的に推定されます。

型のマッピング (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種類の偽列をサポートしています：

外部ファイルの管理

外部テーブルは、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)

関連ドキュメント

• MySQLモードのデータ型マッピング
• Oracleモードのデータ型マッピング