[Feature Request]: hive partition external table

[iamlinjunhong]

Is there an existing issue for the same feature request?

• I have checked the existing issues.

Is your feature request related to a problem?

Yes. Today MatrixOne can read Parquet from S3 via CREATE EXTERNAL TABLE ... URL s3option{...}, but cannot recognize Hive-style
  partitioned layouts. A typical TPC-DS / data lake setup looks like this:

  s3://nova-nt/user/hive/warehouse/training.db/catalog_returns/data/
    cr_returned_date_sk=2450815/part-00000.parquet
    cr_returned_date_sk=2450816/part-00000.parquet
    ...
    cr_returned_date_sk=2452641/part-00000.parquet    # 1,800+ daily partitions

  Problems this creates for users today:

  - No recursive discovery. filepath='.../catalog_returns/data/' returns nothing — users must hand-write glob patterns like
  .../data/*/*.
  - Partition key is invisible. cr_returned_date_sk exists only in the directory name; Spark/Hive strip it from the Parquet file to save
   space. MatrixOne has no way to expose it as a column.
  - No partition pruning. WHERE cr_returned_date_sk = 2450821 ends up scanning all 1,800+ partitions instead of opening one file.
  - Lakehouse interoperability gap. Trino, Spark, StarRocks, and Impala all read this layout natively. MatrixOne users either maintain a
   separate copy of the data or can't participate in a multi-engine lakehouse.

Describe the feature you'd like

Explicit, opt-in Hive-style partitioned external tables. DDL:

CREATE EXTERNAL TABLE catalog_returns (
cr_item_sk INT NOT NULL,
cr_order_number BIGINT NOT NULL,
cr_net_loss DECIMAL(7,2),
cr_returned_date_sk INT -- partition column
) URL s3option{
"endpoint" = "https://storage.yandexcloud.net",
"bucket" = "nova-nt",
"filepath" = "user/hive/warehouse/training.db/catalog_returns/data/",
"format" = "parquet",
"hive_partitioning" = "true",
"hive_partition_columns" = "cr_returned_date_sk"
};

Runtime behaviour:

• Point filepath at the partition root; MatrixOne discovers key=value/ directories automatically (single-level or multi-level).
• Partition columns behave like regular columns: DESCRIBE, SELECT *, WHERE, GROUP BY, ORDER BY, JOIN all work, even when the Parquet
  files don't physically contain the column.
• WHERE col = v and WHERE col IN (...) are pushed into directory discovery, so List/Get counts on the object store scale with the
  number of matching partitions rather than the total partition count.
• Unsupported predicates (range, OR, cast, LIKE '%...%') fall back to row-level filtering; results are always correct.
• Works identically against local filesystem (INFILE{...}) and S3-compatible object stores (MinIO, AWS S3, Yandex).

Non-goals (explicit, not for this issue):

• Apache Iceberg / Hudi / Delta Lake table formats
• Hive Metastore / AWS Glue / Unity Catalog integration
• Writes (INSERT / LOAD DATA into partitioned external tables)
• Schema evolution
• CSV / JSONLINE hive partitions (Parquet only in P0)

Describe implementation you've considered

A parallel discovery path separate from the existing ReadDir glob flow. Key pieces:

• DiscoverHivePartitions (new): recursive list-and-filter over key=value/ directories. For a declared N-level partitioning, the walk
  descends N times then collects files. S3 request shape is List = matched_partitions + 1 + Get = matched_partitions.
• Conservative three-state matching (MatchTrue / MatchFalse / MatchUnknown). Only MatchFalse prunes. Unknown types (DECIMAL, DATE,
  TIMESTAMP, BOOL, collation-sensitive VARCHAR mismatches) are kept and evaluated by the row filter — never a false-negative pruning
  decision.
• Double filtering for correctness: partition filters live in both the directory pruner and the runtime row filter, so a buggy or
  conservative pruner cannot drop data that SQL semantics requires.
• Partition column types persisted in CreateSql JSON (HivePartitionColTypes snapshot at DDL time), not looked up from catalog at
  compile. Avoids an extra RPC and closes a race window with concurrent DDL.
• Hard limits: warnPartitionCount = 5000 (WARN log), maxPartitionCount = 50000 (error), maxListCalls = 10000 (error) — protects the
  coordinator from pathological layouts.
• Compile integration: getHivePartitionFileList replaces ReadDir when hive_partitioning=true. A runFilePathFilters helper evaluates
  __mo_filepath LIKE ... predicates at compile time; unconsumed filters are appended back to row filters so nothing is ever silently
  dropped.
• Path-value wins when a Parquet file happens to contain a physical column with the same name as a partition column (Hive/Spark
  convention).
• HIVE_DEFAULT_PARTITION maps to SQL NULL on nullable columns; raises constraint violation on NOT NULL.
• URL decoding happens in exactly one place (ParseHivePartitionSegment). Directory names with literal % are currently rejected with an
  explicit error (known P0 limitation due to the FileService path parser; fix deferred to a follow-up).

Explicitly out of scope for the first iteration: distributed per-file execution across CNs (forced param.Parallel = false), partition
metadata caching between queries, range-predicate directory pruning, and concurrent List calls.

Documentation, Adoption, Use Case, Migration Strategy

Additional information

hive_partition_user_guide_en.md

[Ariznawlll]

Feature Verification Report — Hive Partition External Table

Branch: 3.0-dev (commit 5fde73e, PR #24329 + #24332)
Tester: @Ariznawlll
Date: 2026-05-12

---

Test Datasets

Dataset	Rows	Partitions	Schema
sales_large	5.3M	60 (year x month)	BIGINT, INT, SMALLINT, DECIMAL(12,2), TIMESTAMP
events_wide	2M	100 (region)	BIGINT, VARCHAR, DECIMAL(10,2), TIMESTAMP
decimal_stress	1M	10 (category)	DECIMAL(7,2), DECIMAL(18,4), DECIMAL(30,6)

---

1. Functional Correctness (确保使用不报错)

Test	Result	Notes
DDL creation (infile + hive_partitioning)	PASS
Recursive directory discovery	PASS	5.3M rows across 60 partitions
DESCRIBE shows partition columns	PASS	year/month visible
SHOW CREATE TABLE shows hive options	PASS
SELECT * includes partition columns	PASS
WHERE col = v (EQ pruning)	PASS
WHERE col IN (...) (IN pruning)	PASS
WHERE col NOT IN (...)	PASS
WHERE col > v (range, fallback to row filter)	PASS	correct result
WHERE col OR col (cross-partition OR)	PASS	correct result
WHERE CAST(col) (function on partition col)	PASS	falls back correctly
WHERE col + 1 = v (arithmetic)	PASS	falls back correctly
GROUP BY partition column	PASS
ORDER BY partition column	PASS
JOIN using partition column	PASS
UNION ALL across hive tables	PASS
Window functions over hive table	N/A	TIMESTAMP issue blocks this
Column projection (fewer cols than file)	PASS
100-partition table discovery	PASS

Unhappy Path Tests

Test	Result	Notes
Column not in parquet file	PASS	clear error: column xxx not found
Non-existent filepath	PASS	returns empty result
Empty partition directory	PASS	returns 0 rows
Partition col type mismatch (string to INT)	PASS	clear error with col/value/path
Mixed partition levels (inconsistent depth)	PASS	silently skips shallow files
DECIMAL precision overflow	PASS	clear error: exceeds destination precision
LOAD DATA into hive table	PASS	rejected (external table)
Repeated queries (memory stability)	PASS	10x same query, consistent results
DROP and recreate	PASS

Known Issues Found

Issue	Severity	Status
TIMESTAMP(isAdjustedToUTC=false) from PyArrow fails	P1	Filed as #24356, fix on main not yet backported

---

2. Large Data / Stability (大数据量, 不hung, 不OOM)

Test	Time	Result
5.3M rows full COUNT(*)	2.9s	PASS
5.3M rows SUM(DECIMAL) full scan	39.7s	PASS
5.3M rows high-cardinality GROUP BY (100K groups)	19.1s	PASS
5.3M rows COUNT(DISTINCT) (500K distinct)	0.87s	PASS
2M rows across 100 partitions GROUP BY	1.4s	PASS
Cross-table JOIN (5.3M x 2M)	4.5s	PASS
DECIMAL(30,6) SUM over 1M rows	10.4s	PASS
10x repeated queries	stable	PASS, no memory growth
SELECT * LIMIT 100000	less than 5s	PASS

Conclusion: No hung, no OOM, all queries complete successfully.

---

3. Performance (vs ClickHouse 26.4, server mode)

Both MO and ClickHouse run as servers, queries submitted via TCP client. Data on local disk (~287MB parquet).

Query	MO (s)	ClickHouse (s)	MO/CH
5.3M COUNT(*)	2.88	0.19	15x
2M COUNT(*)	0.51	0.14	3.8x
5.3M SUM(decimal)	39.7	0.19	213x
1M AVG(decimal)	18.9	0.16	117x
Single partition + SUM	3.59	0.14	26x
Yearly GROUP BY	35.7	0.19	187x
100-part GROUP BY	1.23	0.20	6x
DECIMAL multiply agg	10.4	0.14	72x
Full scan multi-agg	34.4	0.17	204x
High-card GROUP BY	19.1	0.23	84x
COUNT DISTINCT	0.87	0.20	4.5x
Partition prune IN	0.026	0.14	MO 5x faster
100-part full scan	1.40	0.14	10x

Summary:

• MO vs ClickHouse median: 26x (MO slower)
• MO wins on partition pruning (5x faster) — proves pruning logic is effective
• Main bottleneck: DECIMAL FixedLenByteArray decoding (100-200x), needs vectorized batch decode
• Light queries (COUNT, DISTINCT): 4-15x gap, acceptable for first iteration

---

Test Scripts

All reproducible at /tmp/hive-partition-test/scripts/:

• 01_generate_data.py — PyArrow data generator (5.3M + 2M + 1M rows)
• 04_mo_benchmark.py — MO benchmark via mysql client
• 07_clickhouse_benchmark.py — ClickHouse benchmark via clickhouse-client
• 06_unhappy_path.sql — Error handling tests

---

Overall Verdict

Requirement	Status
1. 确保使用不报错, unhappy path	PASS (1 known issue: #24356 TIMESTAMP)
2. 大数据能跑, 不hung, 不OOM	PASS
3. 性能	Functional but slow on DECIMAL-heavy scans. Partition pruning effective.