Automatically create a partitioned external table
The syntax for creating a partitioned external table is as follows:
CREATE EXTERNAL TABLE
<table_name>
( <part_col_name> <col_type> AS <part_expr> )
[ , ... ]
[ PARTITION BY ( <part_col_name> [, <part_col_name> ... ] ) ]
..
The relevant parameters are described below:
PARTITION BYis used to specify one or more partitioning columns.
Usage example
Suppose there is a file directory as follows, and you need to manage the files below using a partitioned external table. External tables support three data file formats: CSV, PARQUET, and ORC. This example uses the CSV format.
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
The content of the files is as follows:
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
You can access the files in this directory by creating an automatically partitioned external table:
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)
;
- The
METADATA$FILEURLcolumn records the path and file name information. For local files, it returnsip:port%2023-06-01/server_log2.csv; for object storage (such as S3), it returns2023-06-01/server_log2.csv.
In this example, the partitioning key date_key is used to extract the date fields from the records. The files are partitioned by date. When querying, you can specify the value or range of the partitioning key. In this case, partition pruning occurs, and the external table will only read the files within that partition.
obclient> select * from ex_t1 where date_key = '2023-06-01';
Manually create a partitioned external table
The syntax for manually creating a partitioned external table is as follows:
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
When you need to manually add and delete partitions yourself, rather than letting the external table manage them automatically, you must specify the PARTITION_TYPE = USER_SPECIFIED field.
Usage example
The file directory is the same as in the automatic partitioning example above. You can create a manually partitioned external table accordingly:
Example 1
External tables support three data file formats: CSV, PARQUET, and ORC.
You can execute the following command to create a CSV-type external table partition:
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_colspecifies which column is the partitioning key for the external table, corresponding to the order inpartition by.
Execute the following statement to manually add a partition, incorporating the files under the 2023-06-01 subdirectory into the partition. The value of the partitioning key for this partition is also set to 2023-06-01, which you can specify arbitrarily.
obclient> alter external table ex_t1 add partition
(date_key = '2023-06-01' ) location '2023-06-01';
You can also execute the following statement to drop the added partition.
obclient> alter external table ex_t1 drop partition location '2023-06-01';
Example 2
External tables support three data file formats: CSV, PARQUET, and ORC.
You can create a PARQUET-type external table partition using the following command:
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)
Example 3
External tables support three data file formats: CSV, PARQUET, and ORC.
You can execute the following command to create an ORC-type external table partition:
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)