外部テーブルのパーティションの自動作成
外部テーブルのパーティションを自動作成する構文は以下のとおりです：
CREATE EXTERNAL TABLE
<table_name>
( <part_col_name> <col_type> AS <part_expr> )
[ , ... ]
[ PARTITION BY ( <part_col_name> [, <part_col_name> ... ] ) ]
..
関連パラメータの説明は以下のとおりです：
PARTITION BYは、1つまたは複数のパーティション列を指定します。
使用例
次のようなファイルディレクトリがあり、パーティション外部テーブルを使用して以下のファイルを管理する必要があるとします。 外部テーブルはCSV、PARQUET、ORCの3種類のデータファイル形式をサポートします。この例ではCSV形式を使用します。
external_table_mock_log
├── 2023-06-01
│ ├── server_log1.csv
│ └── server_log2.csv
├── 2023-06-02
│ └── server_log1.csv
├── 2023-06-03
│ ├── server_log1.csv
│ ├── server_log2.csv
│ └── server_log3.csv
└── 2023-07-01
└── server_log1.csv
ファイルの内容は次のとおりです：
2023-06-01 14:42:37.568624, INTERNAL ERROR, -4007, Not supported
2023-06-01 14:42:38.861356, ITER END, -4008, traverse map failed
2023-06-01 14:42:39.931161, NEED WAIT, -4076, query and update last id fail
2023-06-01 14:42:39.931161, SUCCESS, 0, do flush cache success
パーティション外部テーブルを自動作成することで、このディレクトリ内のファイルにアクセスできます：
obclient> create external table ex_t1 (
time datetime,
errstate varchar(30),
errcode int,
errcontent varchar(100),
date_key datetime as (substr(substr(metadata$fileurl, instr(metadata$fileurl, '%') + 1), 1, 10))
)
location='external_table_mock_log'
FORMAT (
type = 'csv',
field_delimiter = ',',
SKIP_BLANK_LINES = TRUE
)
partition by (date_key)
;
METADATA$FILEURL列には、パスとファイル名の情報が記録されています。ローカルファイルの場合はip:port%2023-06-01/server_log2.csvを返し、OSSストレージの場合は2023-06-01/server_log2.csvを返します。
この例では、パーティションキー date_key を使用して、記録されている日付フィールドを分割します。日付に基づいてファイルをパーティション化します。 クエリ時には、パーティションキーの値または範囲を指定できます。その際、パーティションカットが行われ、外部テーブルはそのパーティション内のファイルのみを読み取ります。
obclient> select * from ex_t1 where date_key = '2023-06-01';
外部テーブルのパーティションを手動で作成する
外部テーブルのパーティションを手動で作成する構文は以下のとおりです：
CREATE EXTERNAL TABLE
<table_name>
( <part_col_name> <col_type> AS <part_expr> )
[ , ... ]
[ PARTITION BY ( <part_col_name> [, <part_col_name> ... ] ) ]
PARTITION_TYPE = USER_SPECIFIED
外部テーブルによる自動的なパーティション管理ではなく、手動でパーティションを追加または削除する必要がある場合は、PARTITION_TYPE = USER_SPECIFIED フィールドを指定する必要があります。
使用例
ファイルディレクトリは上記の自動パーティション分割の例と同じであり、対応する手動パーティション外部テーブルを作成できます。
例1
外部テーブルはCSV、PARQUET、ORCの3種類のデータファイル形式をサポートしています。
以下のコマンドを実行して、CSV形式の外部テーブルパーティションを作成します：
obclient> create external table ex_t1 (
time datetime,
errstate varchar(30),
errcode int,
errcontent varchar(100),
date_key datetime as (metadata$partition_list_col1)
)
location='external_table_mock_log'
PARTITION_TYPE = USER_SPECIFIED
FORMAT (
type = 'csv',
field_delimiter = ',',
SKIP_BLANK_LINES = TRUE
)
partition by (date_key)
;
metadata$partition_list_colは、外部テーブルのパーティションキーの何番目の列であるかを指定し、partition byの順序と対応します。
以下のステートメントを実行して、手動でパーティションを追加し、2023-06-01 サブディレクトリ内のファイルをパーティションに追加します。また、このパーティションのパーティションキー値も 2023-06-01 に設定されており、この値は任意に指定できます。
obclient> alter external table ex_t1 add partition
(date_key = '2023-06-01' ) location '2023-06-01';
また、以下のステートメントを実行して、追加したパーティションを削除することもできます。
obclient> alter external table ex_t1 drop partition location '2023-06-01';
例2
外部テーブルはCSV、PARQUET、ORCの3種類のデータファイル形式をサポートしています。
以下のコマンドを使用して、PARQUET形式の外部テーブルパーティションを作成できます：
obclient> create external table lineitem_part
(
l_orderkey int,
l_partkey int,
l_suppkey int,
l_linenumber int,
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),
l_shipyear varchar(4) as (substr(metadata$fileurl, instr(metadata$fileurl, '%') + 1, 4))
) location 'file:///data/external_data/lineitem_part'
format (
type = 'parquet'
)
partition by(l_shipyear);
Query OK, 0 rows affected (0.126 sec)
例3
外部テーブルはCSV、PARQUET、ORCの3種類のデータファイル形式をサポートしています。
以下のコマンドを実行して、ORC形式の外部テーブルパーティションを作成します：
obclient> create external table lineitem_part
(
l_orderkey int,
l_partkey int,
l_suppkey int,
l_linenumber int,
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),
l_shipyear varchar(4) as (substr(metadata$fileurl, instr(metadata$fileurl, '%') + 1, 4))
) location 'file:///data/external_data/lineitem_part'
format (
type = 'orc'
)
partition by(l_shipyear);
Query OK, 0 rows affected (0.126 sec)