CREATE EXTERNAL TABLE

概要

このステートメントは、データベースに新しい外部テーブルを作成するために使用されます。

外部テーブルは、データベースマネジメントシステムにおける重要な機能の一つです。通常、データベース内のテーブルはデータベースのストレージスペースに配置されますが、外部テーブルのデータは外部ストレージサービスに保存されます。

外部テーブルを作成する際には、データのファイルパスとファイル形式を定義する必要があります。その後、ユーザーは外部テーブルを使用して外部ストレージサービスからファイルのデータを読み取ることができます。外部テーブルは読み取り専用であり、クエリ文で使用することは可能ですが、DML操作を実行することはできません。また、外部テーブルには制約の定義やインデックスの作成はサポートされていません。

構文

CREATE EXTERNAL TABLE table_name (column_definition_list)
    LOCATION = 'file_name'
    FORMAT = (format_type_options)
    [PARTITION BY (column_name [, column_name ...])]
    [PARTITION_TYPE = USER_SPECIFIED]
    [PATTERN = '<regex_pattern>']
    [AUTO_REFRESH = 'xxx'];

column_definition_list:
    column_definition [, column_definition ...]

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'

パラメータの説明

file_name

外部ファイルの保存パスには以下の形式があります：

• ファイルがローカルにある場合、ローカルの LOCATION の形式は：LOCATION = '[file://] local_file_path' です。ここで、local_file_path は相対パスでも絶対パスでも構いません。相対パスを指定する場合は、現在のディレクトリがOceanBaseデータベースのインストールディレクトリである必要があります。secure_file_priv は、OBServerノードがアクセスできるファイルパスを設定します。local_file_path は 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（High Availability）に基づく論理ネームサービスによるクラスタアクセスの場合の形式は：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の principals を設定します。

        • 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_options

• TYPE = 'CSV'：外部ファイルの形式をCSVタイプと指定します。以下のフィールドも含まれます：

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

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

  • ESCAPE：CSVファイルのエスケープ文字を指定します。これは1バイトのみ許可されます。

  • FIELD_OPTIONALLY_ENCLOSED_BY：CSVファイルでフィールド値を囲む記号を指定します。デフォルトは空です。

    注意

    外部テーブルデータファイルに 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 であり、空文字列を NULL として処理しないことを意味します。

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

    説明

    V4.3.5バージョンでは、V4.3.5 BP2バージョンから IGNORE_LAST_EMPTY_COLUMN がサポートされています。

• TYPE = 'PARQUET'：外部ファイルの形式を PARQUET タイプと指定します。

• TYPE = 'ORC'：外部ファイルの形式を ORC タイプと指定します。

使用方法

• 外部ファイルが削除された場合、外部テーブルアクセスファイルリストに記録されているファイルは存在しなくなり、その際には外部テーブルは存在しないファイルを無視します。
• 外部ファイルが変更された場合、外部テーブルは外部ファイルの最新データにアクセスします。外部ファイルの変更と外部テーブルのクエリが同時に行われる場合、予期しない結果が発生する可能性があるため、外部テーブルのクエリ中に外部ファイルを変更することは避ける必要があります。
• 外部ディレクトリに新しいファイルが追加された場合、外部テーブルはファイルリスト内のファイルのみにアクセスします。新しいファイルを外部テーブルのファイルリストに追加する必要がある場合は、外部テーブルのファイルリストを更新する操作を実行する必要があります。

例

例1

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

1. HDFS上のあるファイルパスに、CSVファイルが存在する場合、そのファイルの内容は以下のとおりです：

   $hdfs dfs -cat /user/test_tbl1.csv
   1,'Emma','2021-09-01'
   2,'William','2021-09-02'
   3,'Olivia','2021-09-03'
   

2. HDFS外部テーブルを作成します。

   ターゲットHDFS環境でKerberos認証が有効になっていない場合

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

   注意

   hadoop_namenode_hostname および hadoop_namenode_port はHDFSノードのホスト名とポート番号を指し、実際の値に置き換える必要があります。

   ターゲットHDFS環境でKerberos認証が有効になっている場合

   注意

   Kerberos認証設定がある場合は、対応するOBServerノードにkeytab（ユーザー認証キーファイル）とkrb5confファイルをデプロイする必要があります。

   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';
   

3. 外部テーブルデータを照会します。

   select * from test_tbl1_csv_oracle;
   

   実行結果は次のとおりです：

   +----+----------+------------+
   | id | name     | c_date     |
   +----+----------+------------+
   |  1 | Emma     | 2021-09-01 |
   |  2 | William  | 2021-09-02 |
   |  3 | Olivia   | 2021-09-03 |
   +----+----------+------------+
   3 rows in set
   

例3

1. ORC形式のデータファイルdata.orcが/home/admin/orc_test/ディレクトリに配置されていると仮定します。

2. ORC形式の外部テーブルを作成します。

   obclient> CREATE EXTERNAL TABLE ext_table_orc (
       id   NUMBER,
       name VARCHAR2(50)
   )
   LOCATION = '/home/admin/orc_test/'
   FORMAT = (
     TYPE = 'ORC'
   )
   PATTERN = 'data.orc';
   

   説明

   FORMAT = ( TYPE = 'ORC' )を指定することで、外部テーブルはORC形式のデータファイルを直接読み取ることができます。外部データファイルの実際の形式が宣言したものと一致していることを確認してください。そうでない場合、解析が失敗する可能性があります。

例4

1. HDFS上のあるファイルパスに、以下の内容のCSVファイルが存在する場合を想定します：

   $hdfs dfs -cat /hadoop_ha_test/test_simple.csv
   1,lili,19
   2,alic,20
   3,solvi,21
   

2. Observer関連のJNI構成パラメータを有効化します。

   JNIの詳細な構成については、OceanBaseデータベースJAVA SDK環境のデプロイを参照してください。

3. HDFS外部テーブルを作成します。

   obclient> 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';
   

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

   obclient> SELECT * FROM test_ha;
   

   実行結果は次のとおりです：

   +----+--------+-------+
   | ID | R_NAME | AGE   |
   +----+--------+-------+
   |  1 | lili   |    19 |
   |  2 | alic   |    20 |
   |  3 | solvi  |    21 |
   +----+--------+-------+
   3 rows in set
   

関連ドキュメント

外部テーブルの作成

外部ファイル管理

外部テーブルファイルリストの更新