外部テーブルの作成とクエリ

本記事では、MySQLモードでファイル外部テーブルを作成し、クエリする手順について説明します。完全な構文、LOCATION、FORMAT、PROPERTIESなどのパラメータ説明については、CREATE EXTERNAL TABLEを参照してください。

外部ファイルを一時的に読み取るだけで、テーブルオブジェクトを作成する必要がない場合は、FILESテーブル関数を使用してください。

前提条件

• クラスタがデプロイされ、MySQLモードのテナントが作成されており、データベースへの接続が完了していること。
• データベースの作成が完了していること。
• 現在のユーザーがCREATE権限を持っていること。詳細については、ユーザー権限の確認を参照してください。
• ローカルファイル：データがOBServerからアクセス可能なパスに配置されており、secure_file_privが設定されていること（この変数の変更には通常、ローカルUnixソケット接続が必要です）。
• HDFS / ODPS：OceanBaseデータベースJAVA SDK環境のデプロイと設定が完了していること。

例

以下の例は2種類あります：ローカルファイル外部テーブル（LOCATION + FORMATを使用してOBServer上のCSVにアクセスする）とODPS外部テーブル（PROPERTIESを使用してMaxComputeテーブルにアクセスする、JAVA SDKが必要）。両方の外部テーブルが正常に作成された後、SHOW CREATE TABLEを使用して定義を確認し、SELECTを使用してデータをクエリできます。

ローカルCSVファイル外部テーブルの作成

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

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

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

   接続例：

   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 int, name char(10),score int)
   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` int(11) GENERATED ALWAYS AS (metadata$filecol1),
  `name` char(10) GENERATED ALWAYS AS (metadata$filecol2),
  `score` int(11) GENERATED ALWAYS AS (metadata$filecol3)
)
LOCATION='file:///home/admin/oceanbase/'
PATTERN='data.csv'
FORMAT (
  TYPE = 'CSV',
  FIELD_DELIMITER = ',',
  FIELD_OPTIONALLY_ENCLOSED_BY = '"',
  ENCODING = 'utf8mb4'
)DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

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

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

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

ODPS JAVA SDK外部テーブルの作成

この例では、MaxComputeの公開データセット bigdata_public_dataset のSchema tpch_10g において、TPC-Hテーブル lineitem をマッピングします（テーブル構造とサンプルデータの説明については、OceanBaseによるMaxCompute（ODPS）データのクエリ・読み書き実践チュートリアルを参照してください）。ODPS外部テーブルを作成する前に、すべてのOBServerノードで OceanBaseデータベースJAVA SDK環境のデプロイが完了している必要があり、MaxComputeの読み取り権限を持つ ACCESSID / ACCESSKEY を準備しておく必要があります。

1. CREATE 権限を持つユーザーでMySQLモードのテナントに接続し、対象データベース（例：データベース名 test）に切り替えます。

   USE test;
   

2. ODPS外部テーブル ext_lineitem を作成します。列の定義はMaxComputeのソーステーブルと一致させる必要があります。

   CREATE EXTERNAL TABLE ext_lineitem (
       l_orderkey      BIGINT,
       l_partkey       BIGINT,
       l_suppkey       BIGINT,
       l_linenumber    BIGINT,
       l_quantity      DECIMAL(15,2),
       l_extendedprice DECIMAL(15,2),
       l_discount      DECIMAL(15,2),
       l_tax           DECIMAL(15,2),
       l_returnflag    CHAR(1),
       l_linestatus    CHAR(1),
       l_shipdate      DATE,
       l_commitdate    DATE,
       l_receiptdate   DATE,
       l_shipinstruct  CHAR(25),
       l_shipmode      CHAR(10),
       l_comment       VARCHAR(44)
   )
   PROPERTIES = (
       TYPE = 'ODPS',
       ACCESSID = '***********',
       ACCESSKEY = '***********',
       ENDPOINT = 'https://service.cn-hangzhou.maxcompute.aliyun.com/api',
       PROJECT_NAME = 'bigdata_public_dataset',
       SCHEMA_NAME = 'tpch_10g',
       TABLE_NAME = 'lineitem',
       QUOTA_NAME = '',
       COMPRESSION_CODE = 'zstd'
   );
   

   Storage APIを使用する場合（VPC内で、MaxCompute Storage API権限が有効になっている場合）、PROPERTIES に API_MODE = 'storage_api' と SPLIT = 'byte' を追加し、ENDPOINT を同一リージョンVPCのアドレスに変更します（例：https://service.cn-hangzhou-vpc.maxcompute.aliyun-inc.com/api）。`）。

3. 外部テーブルの定義を確認し、PROPERTIES と列マッピングが有効になっていることを確認します。

   SHOW CREATE TABLE ext_lineitem;
   

4. ODPS外部テーブルをクエリします。システムはJAVA SDKを使用してMaxComputeにアクセスし、結果を外部テーブルの列定義に従って返します。

   SELECT * FROM ext_lineitem LIMIT 5;
   

   クエリ結果の例は以下のとおりです（MaxComputeのソーステーブルの最初の5行と一致します）：

   +------------+-----------+-----------+--------------+------------+------------------+------------+-------+--------------+--------------+------------+------------+-------------+--------------------+------------+----------------------------------+
   | l_orderkey | l_partkey | l_suppkey | l_linenumber | l_quantity | l_extendedprice  | l_discount | l_tax | l_returnflag | l_linestatus | l_shipdate | l_commitdate | l_receiptdate | l_shipinstruct     | l_shipmode | l_comment                        |
   +------------+-----------+-----------+--------------+------------+------------------+------------+-------+--------------+--------------+------------+------------+-------------+--------------------+------------+----------------------------------+
   |          1 |   1551894 |     76910 |            1 |      17.00 |         33078.94 |       0.04 |  0.02 | N            | O            | 1996-03-13 | 1996-02-12 | 1996-03-22  | DELIVER IN PERSON  | TRUCK      | egular courts above the          |
   |          1 |    673091 |     73092 |            2 |      36.00 |         38306.16 |       0.09 |  0.06 | N            | O            | 1996-04-12 | 1996-02-28 | 1996-04-20  | TAKE BACK RETURN   | MAIL       | ly final dependencies: slyly bold|
   |          1 |    636998 |     36999 |            3 |       8.00 |         15479.68 |       0.10 |  0.02 | N            | O            | 1996-01-29 | 1996-03-05 | 1996-01-31  | TAKE BACK RETURN   | REG AIR    | riously. regular, express dep    |
   |          1 |     21315 |     46316 |            4 |      28.00 |         34616.68 |       0.09 |  0.06 | N            | O            | 1996-04-21 | 1996-03-30 | 1996-05-16  | NONE               | AIR        | lites. fluffily even de          |
   |          1 |    240267 |     15274 |            5 |      24.00 |         28974.00 |       0.10 |  0.04 | N            | O            | 1996-03-30 | 1996-03-14 | 1996-04-01  | NONE               | FOB        |  pending foxes. slyly re         |
   +------------+-----------+-----------+--------------+------------+------------------+------------+-------+--------------+--------------+------------+------------+-------------+--------------------+------------+----------------------------------+
   5 rows in set
   

5. ODPS外部テーブルとローカルの通常テーブルを組み合わせてクエリを実行します（前の例での ext_t3 と info のJOINの使い方と同じです）。データベースには、重要な分析対象となる注文キーを記録する通常テーブル orders_summary が既に存在することを前提とします。例は以下のとおりです。

   SELECT o.order_id, l.l_quantity, l.l_extendedprice
   FROM orders_summary o
   JOIN ext_lineitem l ON o.order_id = l.l_orderkey
   WHERE l.l_shipdate >= '1996-03-01'
   LIMIT 10;
   

次のステップ

• 外部ファイルを追加した後：ALTER EXTERNAL TABLE table_name REFRESH; を実行するか、外部ファイルの管理を参照してください。
• ディレクトリ単位でパーティションを管理する：外部テーブルのパーティション作成を参照してください。
• 列型：データ型のマッピングを参照してください。
• 外部テーブルの削除：通常のテーブルと同様に DROP TABLE を使用します。テーブルの削除を参照してください。

関連ドキュメント

• 外部テーブルと外部ファイルについて
• CREATE EXTERNAL TABLE
• FILES テーブル関数
• 外部ファイルの管理