CREATE EXTERNAL TABLE

説明

このステートメントは、データベース内に外部テーブル（External Table）を作成するために使用します。

外部テーブルのデータは外部ストレージサービス（ローカルファイルシステム、オブジェクトストレージ、HDFS、MaxComputeなど）に保存され、OceanBase側ではテーブル定義とアクセスパスが保持されます。外部テーブルは読み取り専用オブジェクトであり、クエリには使用できますが、DML操作、制約の定義、インデックスの作成はサポートされていません。

権限要件

• CREATE EXTERNAL TABLE を実行するには、現在のユーザーが CREATE 権限を持っている必要があります。詳細については、MySQLモードの権限分類を参照してください。

• Locationオブジェクトを参照して外部テーブルを作成する場合、そのLocationに対する READ 権限が必要です。

  GRANT READ ON LOCATION test_location_local TO user001 WITH GRANT OPTION;
  

バージョン説明

構文

CREATE EXTERNAL TABLE table_name (column_definition [, column_definition ...])
    LOCATION = 'file_name'
    {FORMAT = (format_type_options)
     | PROPERTIES = (properties_type_options)}
    [PARTITION BY (column_name [, column_name ...])]
    [PARTITION_TYPE = USER_SPECIFIED]
    [PATTERN = 'regex_pattern']
    [AUTO_REFRESH = 'xxx'];

column_definition:
    column_name column_type [AS expr]

format_type_options:
    type_csv_option
    | type_parquet_option
    | type_orc_option

type_csv_option:
    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}

type_parquet_option:
    TYPE = 'PARQUET'

type_orc_option:
    TYPE = 'ORC'

properties_type_options:
    type_odps_option

type_odps_option:
    TYPE = 'ODPS'
    ACCESSID = '<string>'
    ACCESSKEY = '<string>'
    ENDPOINT = '<string>',
    PROJECT_NAME = '<string>',
    SCHEMA_NAME = '<string>',
    TABLE_NAME = '<string>',
    QUOTA_NAME = '<string>',
    COMPRESSION_CODE = '<string>',
    API_MODE = {"tunnel_api" | "storage_api"},
    SPLIT = {"byte" | "row"}

使用方法説明

外部テーブルを作成する際には LOCATION を指定し、外部テーブルのタイプに応じて FORMAT または PROPERTIES を選択する必要があります（両者は互斥であり、同時に使用できません）：

その他の一般的な句：

パラメータ説明

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：相対パスまたは絶対パスを指定できます。相対パスを指定する場合、現在のディレクトリはOceanBaseデータベースのインストールディレクトリである必要があります。secure_file_privは、OBServerノードがアクセス権を持つファイルパスを設定するために使用されます。local_file_pathはsecure_file_privパスのサブパスである必要があります。

オブジェクトストレージ（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：

LOCATION = 'hdfs://host:port/path'

Hadoop HA（論理nameservice）：

LOCATION = 'hdfs://nameserviceID/path'

クライアント側のOBServerには、HAクラスタの nameservice 定義とフェイルオーバーポリシーを含める必要があります。

Kerberos認証：

URLの後にクエリパラメータを追加します：?principal=...&keytab=...&krb5conf=...&configs=...

HA + Kerberos環境におけるconfigsのよく使われるパラメータ（複数は#で区切る）：

サンプルフォーマット：

dfs.data.transfer.protection=integrity#dfs.nameservices=mycluster#dfs.ha.namenodes.mycluster=nn1,nn2#...

ファイル形式とプロパティの説明 (format_type_options)

CSV形式パラメータ一覧 (TYPE = 'CSV')

format_type_options

TYPE = 'CSV'

各パラメータのデフォルト値は上表のとおりです。以下は制約事項と注意点です：

• ESCAPE：CSVファイルのエスケープ文字を指定します。1バイトである必要があります。デフォルト値は ESCAPE ='\' です。

• ENCODING：対応する文字セットについては 文字セット を参照してください。デフォルトはUTF8MB4です。

• FIELD_OPTIONALLY_ENCLOSED_BY：ファイルに NULL 値（文字列 "NULL" 以外）が含まれる場合、このパラメータを明示的に設定する必要があり、値は空にできません。

  注意

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

TYPE = 'PARQUET'

外部ファイル形式をParquetと指定します。

TYPE = 'ORC'

外部ファイル形式をORCと指定します。

properties_type_options

TYPE = 'ODPS'

MaxCompute（ODPS）外部テーブルを作成し、APIを通じてリモートテーブル（ファイルパスではない外部テーブル）にアクセスするために使用されます。

API_MODE：ODPSのAPIモードを指定します。取り得る値は以下のとおりです：

• tunnel_api（デフォルト値）：

  • 特別なネットワーク設定は不要です：すべてのデプロイメントシナリオに適用され、OceanBaseデータベースとMaxComputeが同一のVPC（仮想プライベートクラウド）内にある必要はありません。

  • MaxComputeの追加権限は不要です：AccessIDとAccessKeyを提供するだけで認証が完了し、MaxCompute Storage API権限を有効にする必要はありません。

  • 適用環境：

    • OceanBaseデータベースとMaxComputeが同一のVPCにデプロイされていない場合。
    • MaxCompute Storage APIが有効になっていない場合。
    • データ転送の遅延要件が低い場合。

• storage_api：

  • ネットワーク依存性：OceanBaseデータベースとMaxComputeが同一のVPC内にデプロイされている必要があります。これにより、低遅延で高スループットのデータ転送が実現されます。

  • 権限依存性：MaxComputeでStorage API権限を有効にし、アクセスキー（AccessKey）が該当する権限を持っていることを確認する必要があります。

  • 適用環境：

    • OceanBaseデータベースとMaxComputeが同一のVPCネットワークに属している場合。
    • MaxCompute Storage APIが有効になっている場合。
    • データ量が非常に多い、またはリアルタイム性の要件が高い場合。

• SPLIT：storage_api を使用する場合、byte または row でタスクを分割し、各スレッドに割り当てることを指定します。テーブルの各行データのバイト数に大きな差がある場合は、SPLIT の値を byte に設定し、それ以外の場合は row に設定します。

シナリオの実践やパーティションマッピングなどの詳細については、ODPS外部テーブルを参照してください。

使用方法

• 外部テーブルは読み取り専用オブジェクトであり、DML、制約、インデックスはサポートされていません。
• ファイルリストは作成時に LOCATION と PATTERN に基づいて収集されます。ディレクトリ内に新しいファイルが追加された場合は、ALTER EXTERNAL TABLE ... REFRESH を実行して更新する必要があります。詳細については、外部テーブルのファイルリストを更新するを参照してください。
• 外部ファイルの削除、変更、同時アクセス、secure_file_priv、HDFS/ODPS環境などについては、外部テーブルと外部ファイルについておよび外部ファイルの管理を参照してください。
• テーブルオブジェクトを永続化する必要がなく、外部ファイルを一時的にのみ読み取る場合は、FILESテーブル関数を使用できます。

例

例1：ローカルCSV外部テーブル

1. データの準備。まず、secure_file_priv のパスを /home/admin/ に設定し、インポートする外部テーブルデータに対応するCSVファイル extdata.csv を現在のローカル接続OBServerノードの /home/admin/test パスに配置します。

   グローバルセキュリティパスを設定する例は以下のとおりです。

   obclient> SET GLOBAL secure_file_priv = "/home/admin/";
   Query OK, 0 rows affected
   obclinet> \q
   Bye
   

   説明

   secure_file_priv は GLOBAL 変数であるため、\q を実行して終了させる必要があります。

   CSVファイルの内容は以下のとおりです：

   1,'Dave','Smith','dsmith@outlook.com','friend',32
   2,'Xena','Johnson','xjonson@outlook.com','contact',45
   3,'Fred','Jackon','fjackson@outlook.com','co-worker',19
   4,'Alma','Tyler','atyler@outlook.com','friend',53
   

2. ユーザーテナントがデータベースにログインした後、外部テーブル contacts を作成します。

   obclient> CREATE EXTERNAL TABLE contacts (
       id    INT,
       firstname  VARCHAR(100),
       lastname   VARCHAR(100),
       email      VARCHAR(255),
       category   CHAR(30),
       age        NUMBER
      )
      LOCATION = '/home/admin/test'
      FORMAT = (
        TYPE = 'CSV'
        FIELD_DELIMITER = ','
        FIELD_OPTIONALLY_ENCLOSED_BY ='\''
       )
     PATTERN = 'extdata.csv';
   

3. 外部テーブル contacts のデータをクエリします。

   obclient> SELECT * FROM contacts;
   +----+-----------+----------+----------------------+-----------+------+
   | id | firstname | lastname | email                | category  | age  |
   +----+-----------+----------+----------------------+-----------+------+
   |  1 | Dave      | Smith    | dsmith@outlook.com   | friend    |   32 |
   |  2 | Xena      | Johnson  | xjonson@outlook.com  | contact   |   45 |
   |  3 | Fred      | Jackon   | fjackson@outlook.com | co-worker |   19 |
   |  4 | Alma      | Tyler    | atyler@outlook.com   | friend    |   53 |
   +----+-----------+----------+----------------------+-----------+------+
   4 rows in set
   

例2：HDFS CSV外部テーブル（Kerberosなし）

前提条件：Java SDK環境のデプロイが完了していること。（HDFSへのアクセス時に必要です。）

CREATE EXTERNAL TABLE test_tbl1_csv_mysql (
    id INT,
    name VARCHAR(50),
    c_date DATE
)
LOCATION = 'hdfs://${hadoop_namenode_host}:${hadoop_namenode_port}/user'
FORMAT = (
    TYPE = 'CSV'
    FIELD_DELIMITER = ','
    FIELD_OPTIONALLY_ENCLOSED_BY = '\''
)
PATTERN = 'test_tbl1.csv';

SELECT * FROM test_tbl1_csv_mysql;

例3：HDFS HA + Kerberos

前提条件：

• Java SDK環境のデプロイが完了しています。
• OBServerノードごとにkeytab、krb5.confなどのKerberos資格情報ファイルがデプロイされています。
• configs 内のHAパラメータはクラスタの nameservice 設定と一致しています。

CREATE EXTERNAL TABLE test_ha (
    id INT,
    r_name VARCHAR(100),
    age INT
)
LOCATION = 'hdfs://mycluster/hadoop_ha_test?principal=ha/xxx@xxx.com&keytab=/path/to/ha.keytab&krb5conf=/path/to/krb5conf_file&configs=dfs.data.transfer.protection=integrity#dfs.nameservices=mycluster#dfs.ha.namenodes.mycluster=nn1,nn2#dfs.namenode.rpc-address.mycluster.nn1=localhost1:port#dfs.namenode.rpc-address.mycluster.nn2=localhost2:port#dfs.ha.automatic-failover.enabled.mycluster=true#dfs.client.failover.proxy.provider.mycluster=org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider'
FORMAT = (
    TYPE = 'CSV'
    FIELD_DELIMITER = ','
    FIELD_OPTIONALLY_ENCLOSED_BY = '\''
)
PATTERN = 'test_simple.csv';

SELECT * FROM test_ha;

例4：HDFS Kerberos単一ノード

前提は例3と同じです。単一NameNode環境では、LOCATION を次のように簡略化できます：

CREATE EXTERNAL TABLE partsupp (
    PS_PARTKEY    INTEGER,
    PS_SUPPKEY    INTEGER,
    PS_AVAILQTY   INTEGER,
    PS_SUPPLYCOST DECIMAL(15,2),
    PS_COMMENT    VARCHAR(199)
)
LOCATION = 'hdfs://localhost:8020/tpch_csv?principal=principal_str&keytab=/path/to/keytab&krb5conf=/path/to/krb5conf_file&configs=xxx=xxx#xxx=xxx'
FORMAT = (
    TYPE = 'CSV'
    FIELD_DELIMITER = '|'
    FIELD_OPTIONALLY_ENCLOSED_BY = '"'
)
PATTERN = 'partsupp.tbl';

関連ドキュメント

• 外部テーブルと外部ファイルについて
• 外部テーブルの作成とクエリ
• ODPS外部テーブル
• FILESテーブル関数
• 外部ファイルの管理
• 外部テーブルファイルリストの更新
• OceanBase JAVA SDK環境のデプロイ