ファイル外部テーブル

通常、データベース内のテーブルデータはデータベース自身のストレージ領域に格納されますが、外部テーブル（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 = 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://] 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 = (
    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フォーマット/ORCフォーマット

-- フォーマットはParquet Formatです
FORMAT = ( TYPE = 'PARQUET' )

-- フォーマットはORC Formatです
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' パーティションに含めます。
• 1つのパーティションには1つの LOCATION のみをバインドできますが、1つの 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モードのデータ型マッピング