外部テーブルの作成

CREATE EXTERNAL TABLE ステートメントを使用して外部テーブルを作成します。外部テーブルを作成する際には、外部ファイルからデータを読み取るために、データファイルのパスと形式を指定する必要があります。

権限要件

外部テーブルを作成するには、現在のユーザーが CREATE TABLE 権限を持っている必要があります。現在のユーザー権限を確認する操作については、ユーザー権限の確認を参照してください。CREATE TABLE 権限がない場合は、管理者に連絡し、権限の追加を依頼してください。ユーザー権限の付与に関する操作については、直接権限付与を参照してください。

外部テーブルの作成

外部テーブルを作成するSQLステートメントは以下のとおりです：

CREATE EXTERNAL TABLE table_name
( col_name col_type [AS (metadata$filecol{N})]
  [ , col_name col_type [AS (metadata$filecol{N})] ]
  [ , ... ] )
LOCATION = '<string>'
FORMAT = (
  TYPE = 'CSV'
  LINE_DELIMITER = '<string>' | <expr>
  FIELD_DELIMITER = '<string>' | <expr>
  ESCAPE = '<character>' | <expr>
  FIELD_OPTIONALLY_ENCLOSED_BY = '<character>' | <expr>
  ENCODING = 'charset'
  NULL_IF = ('<string>' | <expr>, '<string>' | <expr> ...)
  SKIP_HEADER = <int>
  SKIP_BLANK_LINES = { TRUE | FALSE }
  TRIM_SPACE = { TRUE | FALSE }
  EMPTY_FIELD_AS_NULL = { TRUE | FALSE }
  IGNORE_LAST_EMPTY_COLUMN = {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>' ]

関連パラメータの説明は以下のとおりです：

• col_name col_type [AS (metadata$filecol{N})]：列を定義します。ここで、AS (metadata$filecol{N}) は列マッピングを手動で定義するために使用されます。

  外部テーブルがサポートする列タイプは通常のテーブルと同じです。OceanBaseデータベースのOracleモードでサポートされているデータ型および詳細については、データ型の概要を参照してください。

  デフォルトでは、外部ファイル内のデータ列と外部テーブルで定義された列は、順序に従って自動的に対応付けられます。つまり、外部テーブルの1列目は外部ファイルの1列目のデータに対応します。

  例えば、以下の例では、外部テーブル ext_t1 の C1 列は外部ファイルの1列目のデータに自動的にマッピングされ、C2 列は外部ファイルの2列目のデータに自動的にマッピングされます。

  CREATE EXTERNAL TABLE ext_t1 (
    C1 int,
    C2 int
    )
    LOCATION = 'oss://$ACCESS_ID:$ACCESS_KEY@$HOST/tpch_1g_data/lineitem/'
    FORMAT = (
    TYPE = 'CSV'
    FIELD_DELIMITER = '|'
    );
  

  外部ファイル内の列の順序と外部テーブルで定義された列の順序が一致しない場合、metadata$filecol{N} のような擬似列を使用して、外部テーブルの列が外部ファイルのN列目に対応するように指定できます。ここで、ファイル内の列は1から番号が付けられます。

  例えば、以下の例では、C1 int AS (metadata$filecol2) は外部テーブル ext_t2 の C1 列がファイル内の2列目に対応することを意味し、C2 int AS (metadata$filecol4) は外部テーブル ext_t2 の C2 列が外部ファイルの4列目に対応することを意味します。

  CREATE EXTERNAL TABLE ext_t2 (
              C1 int AS (metadata$filecol2),
              C2 int AS (metadata$filecol4)
    )
    LOCATION = 'oss://$ACCESS_ID:$ACCESS_KEY@$HOST/tpch_1g_data/lineitem/'
    FORMAT = (
    TYPE = 'CSV'
    FIELD_DELIMITER = '|'
    );
  

  注意

  列マッピングを手動で定義する必要がある場合、自動列マッピング機能は無効になり、すべての列のマッピングを手動で定義する必要があります。

• LOCATION = '<string>'：外部ファイルの保存先パスを指定します。通常、外部テーブルのデータファイルは個別のディレクトリに保存され、そのフォルダにはサブディレクトリを含めることができます。外部テーブルを作成する際、そのディレクトリ内のすべてのファイルが自動的に収集されます。

  以下の2つの形式をサポートしています：

  • ローカルLocation形式：LOCATION = '[file://] local_file_path'

    • local_file_path：相対パスまたは絶対パスのいずれかです。相対パスを指定する場合、現在のディレクトリはOceanBaseデータベースのインストールディレクトリである必要があります。

      注意

      local_file_pathはファイルではなくディレクトリである必要があります。個別のファイルを指定する必要がある場合は、LOCATIONでそのファイルの上位ディレクトリを指定し、PATTERNプロパティを設定してそのファイルを指定する必要があります。

    • ローカルLocation形式を使用するシナリオでは、システム変数 secure_file_priv を使用してOceanBaseデータベースがアクセス権を持つファイルパスを設定する際、secure_file_priv は local_file_path の上位ディレクトリである必要があります。つまり、local_file_path は secure_file_priv パスのサブパスにすることしかできません。

      テナントレベルのシステム変数 secure_file_priv は、ファイルへのインポートまたはエクスポート時にOceanBaseデータベースがアクセス可能なパスを制御します。secure_file_priv の詳細については、secure_file_privを参照してください。

  • リモートLocation形式：

    注意

    オブジェクトストレージパスを使用する場合、オブジェクトストレージパスの各パラメータは & 記号で区切られます。入力するパラメータ値には、英字の大文字と小文字、数字、/-_$+= およびワイルドカードのみ含まれていることを確認してください。上記以外の文字を入力した場合、設定が失敗する可能性があります。

    • ファイルがOSS/S3上にある場合、形式は次のとおりです：LOCATION = '{oss\|s3}://$ACCESS_ID:$ACCESS_KEY@$HOST：s3_region/remote_file_path'。ここで、$ACCESS_ID、$ACCESS_KEY、$HOST は、Alibaba Cloud OSS、AWS S3、およびS3プロトコル互換のオブジェクトストレージにアクセスするために必要なアクセス情報です。s3_region はS3使用時に選択するリージョン情報であり、これらの機密アクセス情報は暗号化されてデータベースのシステムテーブルに保存されます。

    • ファイルがHDFS上にある場合、形式は以下のとおりです：

      • シングルノードNameNode（NN）アドレスに基づいてクラスタにアクセスする形式：LOCATION = hdfs://localhost:port/PATH。ここで、localhost はHDFSのアドレスを指し、port はHDFSのポート番号を指し、PATH はHDFS内のファイルパスを指します。

        • Kerberos認証を使用する形式：LOCATION = 'hdfs://localhost:port/user?principal=xxx&keytab=xxx&krb5conf=xxx&configs=xxx'。ここで：

          • principal：ログイン認証ユーザーを指します。
          • keytab：ユーザー認証のキータブファイルのパスを指定します。
          • krb5conf：ユーザーが使用するKerberos環境の設定ファイルのパスを指定します。
          • configs：追加のHDFS構成パラメータを指定します。デフォルトは空ですが、Kerberos環境の場合は通常このパラメータに値を設定する必要があります。例：dfs.data.transfer.protection=authentication,privacy は、データ転送の保護レベルを authentication と privacy に設定します。

      • Hadoop HA（高可用性）の論理名サービスに基づいてクラスタにアクセスする形式：LOCATION = hdfs://nameserviceID/PATH。ここで、nameserviceID はHDFSのHadoop HAの論理名サービスIDを指し、PATH はファイルパスを指します。

        説明

        クライアントOBServer側の設定には、HAクラスタの nameservice 定義とフェイルオーバー戦略が含まれていることを確認してください。

        • Kerberos認証を使用する形式：LOCATION = 'hdfs://nameserviceID/PATH?principal=xxx&keytab=xxx&krb5conf=xxx&configs=dfs.data.transfer.protection=${string}#dfs.nameservices=${nameservice id}#dfs.ha.namenodes.${nameservice id}=${namenode1}, ${namenode2}#dfs.namenode.rpc-address.${nameservice id}.${namenode1}=${namenode 1 address}#dfs.namenode.rpc-address.${nameservice id}.${namenode2}=${namenode 2 address}#dfs.ha.automatic-failover.enabled.${nameservice id}=true#dfs.client.failover.proxy.provider.${nameservice id}=org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider'。ここで：

          • principal：ログイン認証ユーザーを指します。非プライマリノードNNの principal に設定します。

          • keytab と krb5conf：シングルノードNNの設定と同じです。

          • configs：追加のHDFS構成パラメータを指定します。複数の構成パラメータを設定する必要があり、HA構成パラメータとセキュリティ構成パラメータにのみ関連します：

            • dfs.data.transfer.protection=${string}：クラスタの dfs.data.transfer.protection 設定に合わせます。
            • dfs.nameservices=${nameservice id}：現在のHAクラスタの namesevice（エイリアス）を指定します。
            • dfs.ha.namenodes.${nameservice id}=${namenode1}, ${namenode2}：HAクラスタのnamenodeバックアップIDリストを指定します。
            • dfs.namenode.rpc-address.${nameservice id}.${namenode1}=${namenode 1 address}：namenode1 が具体的なnamenodeであることを設定し、クライアントのルーティングを容易にします。
            • dfs.namenode.rpc-address.${nameservice id}.${namenode2}=${namenode 2 address}：namenode2 が具体的なnamenodeであることを設定し、クライアントのルーティングを容易にします。
            • dfs.ha.automatic-failover.enabled.${nameservice id}=true：HAクラスタが関連するリクエストを受信すると、利用可能なnamenodeを自動的に取得してサービスに応答するようにします。
            • dfs.client.failover.proxy.provider.${nameservice id}=org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider：HAクラスタがプライマリ/スタンバイ切り替えを行うためのロジックツールクラスを指示します。また、HAクラスタが必要とする独自のロジックをパッケージ化してアップロードすることもできます。

        注意

        一部のHA構成パラメータは namespace と紐づけられています。例えば、以下の例3の mycluster のように、関連する構成パラメータを組み合わせて設定する必要があります。

• FORMAT = ( TYPE = 'CSV'...)：外部ファイルの形式をCSVとして定義します。

  • TYPE：外部ファイルのタイプを指定します。

  • LINE_DELIMITER：ファイルの行区切り文字を指定します。

  • FIELD_DELIMITER：ファイルの列区切り文字を指定します。

  • ESCAPE：ファイルのエスケープ文字を指定します。

  • FIELD_OPTIONALLY_ENCLOSED_BY：ファイル内でフィールド値を囲む記号を指定します。例えば、ESCAPE = '"' は値を二重引用符で囲むことを意味します。指定しない場合、デフォルト値は空です。

    注意

    外部テーブルのデータファイルに NULL 値（文字列の NULL ではなく、"NULL" でもない）が含まれる場合、FIELD_OPTIONALLY_ENCLOSED_BY パラメータを明示的に設定する必要があり、そのパラメータ値は空にすることはできません。

  • ENCODING：ファイルの文字セットエンコーディング形式を指定します。現在のOracleモードでサポートされているすべての文字セットについては、文字セットを参照してください。指定しない場合、デフォルト値はUTF8MB4です。

  • NULL_IF：どの文字列を NULL として処理するかを指定します。指定しない場合、デフォルト値は空です。

  • SKIP_HEADER：ファイルヘッダーをスキップする行数を指定します。指定しない場合、デフォルトではファイルヘッダーはスキップされません。

  • SKIP_BLANK_LINES：空白行をスキップするかどうかを指定します。指定しない場合、デフォルト値は FALSE です。

  • TRIM_SPACE：ファイル内のフィールドの先頭と末尾のスペースを削除するかどうかを指定します。指定しない場合、デフォルト値は FALSE です。

  • EMPTY_FIELD_AS_NULL：空文字列を NULL として処理するかどうかを指定します。指定しない場合、デフォルト値は FALSE です。

  • IGNORE_LAST_EMPTY_COLUMN：ファイルの行末が空フィールド（行区切り文字の前が列区切り文字）の場合、その空フィールドを無視するかどうかを指定します。デフォルト値は TRUE で、最後の空フィールドを無視することを意味します。

  • COMPRESSION：オプションです。ファイルの圧縮形式を指定します。取り得る値は以下のとおりです：

    • GZIP/DEFLATE：GZIP圧縮ファイル。

    • ZSTD：ZSTD圧縮ファイル。

    • NONE：ファイルが圧縮されていないことを意味します（デフォルト値）。

    • SNAPPY_BLOCK：HADOOP-SNAPPY圧縮ファイル。

      説明

      V4.4.2バージョンでは、V4.4.2 BP1バージョンから圧縮形式 SNAPPY_BLOCK がサポートされています。

  • PARALLEL_PARSE_ON_SINGLE_FILE：単一のCSVファイルの並列解析を有効にするかどうかを指定します。指定しない場合、デフォルト値は TRUE です。

  • PARALLEL_PARSE_FILE_SIZE_THRESHOLD：このしきい値を超えるサイズのファイルが並列解析される可能性があるかどうかを指定します。単位はバイトで、デフォルト値は256MBです。

  • MAX_ROW_LENGTH：単一行データの最大長をバイト単位で指定します。デフォルト値は2MBです。単一のCSVファイルを並列解析する定義段階で使用され、データ読み取りの正確性には影響しません。

  説明

  V4.4.2バージョンでは、FORMATオプションはV4.4.2 BP1バージョンからサポートされています：PARALLEL_PARSE_ON_SINGLE_FILE、PARALLEL_PARSE_FILE_SIZE_THRESHOLD、および MAX_ROW_LENGTH。

• FORMAT = ( TYPE = 'PARQUET'...)：外部ファイルの形式をPARQUETとして定義します。

• PATTERN：正規表現パターン文字列を指定し、LOCATION ディレクトリ内のファイルをフィルタリングします。LOCATION ディレクトリ内の各ファイルがこのパターン文字列に一致する場合、外部テーブルはそのファイルにアクセスできます。一致しない場合、外部テーブルはそのファイルをスキップします。このパラメータを指定しない場合、デフォルトでは LOCATION ディレクトリ内のすべてのファイルにアクセスできます。

ローカルマシンの /home/admin/oceanbase/ パスに data.csv ファイルが存在すると仮定します。ファイルの内容は以下のとおりです。

1,"lin",98
2,"hei",90
3,"ali",95

1. OBserverノード上で、テナント管理者がローカルUnixソケットを介してクラスタのOracleテナントに接続します。

   接続の例は以下のとおりです：

   obclient -S /home/admin/oceanbase/run/sql.sock -uroot@sys -p********
   

   ローカルUnixソケットを使用してOceanBaseデータベースに接続する具体的な操作と説明については、secure_file_privを参照してください。

2. データベースがアクセス可能なパス /home/admin/oceanbase/ を設定します。

   SET GLOBAL secure_file_priv = "/home/admin/oceanbase/";
   

   コマンドの実行が成功した後、変更を有効にするには、セッションを再起動する必要があります。

3. データベースに再接続した後、外部テーブル ext_t3 を作成します。

   CREATE EXTERNAL TABLE ext_t3(ID NUMBER(32), NAME VARCHAR2(30),SCORE NUMBER(32))
   LOCATION = '/home/admin/oceanbase/'
   FORMAT = (
   TYPE = 'CSV'
   FIELD_DELIMITER = ','
   FIELD_OPTIONALLY_ENCLOSED_BY ='"'
   )
   PATTERN = 'data.csv';
   

外部テーブルの作成が成功すると、通常のテーブルと同様に SHOW CREATE TABLE ステートメントを使用してテーブルの定義を確認できます。

SHOW CREATE TABLE ext_t3;

クエリ結果は以下のとおりです：

+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TABLE  | CREATE TABLE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXT_T3 | CREATE EXTERNAL TABLE "EXT_T3" (
  "ID" NUMBER(32) GENERATED ALWAYS AS (METADATA$FILECOL1),
  "NAME" VARCHAR2(30) GENERATED ALWAYS AS (METADATA$FILECOL2),
  "SCORE" NUMBER(32) GENERATED ALWAYS AS (METADATA$FILECOL3)
)
LOCATION='file:///home/admin/oceanbase/'
PATTERN='data.csv'
FORMAT (
  TYPE = 'CSV',
  FIELD_DELIMITER = ',',
  FIELD_OPTIONALLY_ENCLOSED_BY = '"',
  ENCODING = 'utf8mb4'
)COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

通常のテーブルと同様にアクセスすることもできます。外部テーブルをクエリする際、システムは外部テーブルのドライバーレイヤーを通じて直接外部ファイルを読み取り、ファイル形式に従って解析し、OceanBaseデータベースの内部データ型に変換してからデータ行を返します。先ほど作成した外部テーブル lineitem をクエリする例を以下に示します。

SELECT * FROM ext_t3;

クエリ結果は以下のとおりです：

+----+------+-------+
| ID | NAME | SCORE |
+----+------+-------+
|  1 | lin  |    98 |
|  2 | hei  |    90 |
|  3 | ali  |    95 |
+----+------+-------+
3 rows in set

さらに、外部テーブルと通常のテーブルを組み合わせてクエリ操作を実行することもできます。現在のデータベースに通常のテーブル info があり、そのデータは以下のとおりです：

+------+--------+------+
| NAME | SEX    | AGE  |
+------+--------+------+
| lin  | male   |    8 |
| hei  | male   |    9 |
| li   | female |    8 |
+------+--------+------+
3 rows in set

外部テーブル ext_t3 と通常のテーブル info を組み合わせてクエリする例を以下に示します。

SELECT info.* FROM info, ext_t3 WHERE info.name = ext_t3.name AND ext_t3.score > 90;

クエリ結果は次のとおりです：

+------+--------+------+
| NAME | SEX    | AGE  |
+------+--------+------+
| lin  | male   |    8 |
| li   | female |    8 |
+------+--------+------+
2 rows in set

その他のクエリ操作については、データの読み取りを参照してください。

外部テーブルの使用上の注意点

• 外部テーブルはクエリ操作のみ実行可能で、DML操作は実行できません。

• 外部テーブルをクエリする際、アクセス対象の外部ファイルが削除されている場合、システムはエラーを報告せず、空行を返します。

• 外部テーブルがアクセスするファイルは外部ストレージシステムによって管理されているため、外部ストレージが利用不可の場合、外部テーブルのクエリはエラーとなります。

次のステップ

外部テーブル作成時、システムはLOCATIONで指定されたパス下でPATTERNに一致するファイルのリストをOceanBaseデータベースのシステムテーブルに保存します。外部テーブルのスキャン時には、このリストに基づいて外部ファイルにアクセスします。外部ディレクトリに新しいファイルが追加された場合は、外部テーブルのファイルリストに新規ファイルを追加するため、外部テーブルファイルの更新操作を実行する必要があります。関連操作については、外部ファイルの管理を参照してください。

外部テーブルは作成後に削除することもできます。外部テーブルを削除するステートメントは通常のテーブルと同じです。DROP TABLEステートメントを使用して外部テーブルを削除します。詳細な操作については、テーブルの削除を参照してください。

関連ドキュメント

• 外部テーブルについて

• 外部ファイルの管理