CREATE EXTERNAL TABLE

説明

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

外部テーブルは、データベース管理システムの重要な機能です。通常、データベース内のテーブルはデータベースのストレージ領域に格納されますが、外部テーブルのデータは外部ストレージサービスに格納されます。

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

構文

CREATE EXTERNAL TABLE table_name (column_definition_list)
    LOCATION = {'file_path' | @location_name['/path']}
    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}
    COMPRESSION = {GZIP | ZSTD | DEFLATE | NONE | SNAPPY_BLOCK}
    PARALLEL_PARSE_ON_SINGLE_FILE = {TRUE | FALSE}
    PARALLEL_PARSE_FILE_SIZE_THRESHOLD = <int>
    MAX_ROW_LENGTH = <int>

type_parquet_option:
    TYPE = 'PARQUET'

type_orc_option:
    TYPE = 'ORC'

パラメータ説明

file_path

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

• ファイルがローカルにある場合、ローカル 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（高可用性）の論理名前サービスに基づくクラスタへのアクセス形式は次のとおりです：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_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：ファイルの行末が空フィールド（行区切り文字の前が列区切り文字）の場合、その空フィールドを無視するかどうかを指定します。デフォルト値は 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 をサポートしています。

• 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
   

4. FORMAT オプションを使用して、COMPRESSION、PARALLEL_PARSE_ON_SINGLE_FILE、PARALLEL_PARSE_FILE_SIZE_THRESHOLD、MAX_ROW_LENGTH パラメータを指定した外部テーブル test_tbl1_csv を作成します。

   obclient> CREATE EXTERNAL TABLE test_tbl1_csv (
       col1 INT,
       col2 VARCHAR(100),
       col3 VARCHAR(100),
       col4 VARCHAR(255),
       col5 CHAR(30),
       col6 NUMBER)
       LOCATION = '/home/admin/test/'
       FORMAT = (
           TYPE = 'CSV'
           FIELD_DELIMITER = ','
           FIELD_OPTIONALLY_ENCLOSED_BY = ''''
           COMPRESSION = SNAPPY_BLOCK
           PARALLEL_PARSE_ON_SINGLE_FILE = TRUE
           PARALLEL_PARSE_FILE_SIZE_THRESHOLD = 1024
           MAX_ROW_LENGTH = 1024)
       PATTERN = 'extdata.csv';
   

例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
   

関連ドキュメント

外部テーブルの作成

外部ファイルの管理

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