This is an automated email from the ASF dual-hosted git repository.
yuqi1129 pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/gravitino.git
The following commit(s) were added to refs/heads/main by this push:
new 6dac9e3e38 [#13391] feat(clickhouse): support week/month start
partition transforms (#13427)
6dac9e3e38 is described below
commit 6dac9e3e388cd11e306347e72e1f2dcfe9dd93f1
Author: StormSpirit <[email protected]>
AuthorDate: Wed Sep 23 21:37:36 2026 +0800
[#13391] feat(clickhouse): support week/month start partition transforms
(#13427)
### What changes were proposed in this pull request?
Add structured read and create support for `toStartOfWeek(column)` and
`toStartOfMonth(column)` partition transforms by reusing the existing
function transform. Loading preserves these forms as `ApplyTransform`;
creating a table emits the corresponding ClickHouse function expression.
### Why are the changes needed?
ClickHouse allows these common date partition expressions, but the
catalog does not currently represent them as structured transforms for
loaded tables or create them from Gravitino. Keeping the native
functions intact supports metadata round trips without mapping them to a
different transform.
Fix: #13391
### Does this PR introduce any user-facing change?
Yes. The ClickHouse catalog now supports the one-column forms
`toStartOfWeek(column)` and `toStartOfMonth(column)` for
MergeTree-family partitioning. The one-argument `toStartOfWeek(column)`
form uses ClickHouse's default mode 0 (Sunday start) and the server
timezone. Forms with explicit mode or timezone arguments and nested
functions remain unstructured and are preserved through the read-only
`partition-key` property.
### How was this patch tested?
- `./gradlew :catalogs-contrib:catalog-jdbc-clickhouse:test -PskipITs
--tests '*TestClickHouseTableOperationsPartitioning' --tests
'*TestClickHouseTableOperationsUnit'` — 58 tests passed.
- `./gradlew :catalogs-contrib:catalog-jdbc-clickhouse:test --tests
'org.apache.gravitino.catalog.clickhouse.integration.test.CatalogClickHouseIT.testLoadAndCreateWithStartOfWeekAndMonthPartitionTransforms'
-PskipDockerTests=false` — 1 test passed.
- `./gradlew :catalogs-contrib:catalog-jdbc-clickhouse:test --tests
'org.apache.gravitino.catalog.clickhouse.integration.test.CatalogClickHouseClusterIT'
-PskipDockerTests=false` — 20 tests passed.
- `./gradlew :catalogs-contrib:catalog-jdbc-clickhouse:spotlessCheck` —
passed.
- `./gradlew rat` — passed.
Signed-off-by: jiangxt2 <[email protected]>
---
.../operations/ClickHouseTableSqlUtils.java | 25 +++++++++
.../integration/test/CatalogClickHouseIT.java | 61 ++++++++++++++++++++++
.../TestClickHouseTableOperationsPartitioning.java | 39 ++++++++++++++
.../TestClickHouseTableOperationsUnit.java | 43 +++++++++++++++
docs/jdbc-clickhouse-catalog.md | 7 +--
5 files changed, 172 insertions(+), 3 deletions(-)
diff --git
a/catalogs-contrib/catalog-jdbc-clickhouse/src/main/java/org/apache/gravitino/catalog/clickhouse/operations/ClickHouseTableSqlUtils.java
b/catalogs-contrib/catalog-jdbc-clickhouse/src/main/java/org/apache/gravitino/catalog/clickhouse/operations/ClickHouseTableSqlUtils.java
index bcf1dbeb0b..c525dfc2a5 100644
---
a/catalogs-contrib/catalog-jdbc-clickhouse/src/main/java/org/apache/gravitino/catalog/clickhouse/operations/ClickHouseTableSqlUtils.java
+++
b/catalogs-contrib/catalog-jdbc-clickhouse/src/main/java/org/apache/gravitino/catalog/clickhouse/operations/ClickHouseTableSqlUtils.java
@@ -28,6 +28,7 @@ import java.util.regex.Matcher;
import java.util.regex.Pattern;
import javax.annotation.Nullable;
import org.apache.commons.lang3.StringUtils;
+import org.apache.gravitino.rel.expressions.Expression;
import org.apache.gravitino.rel.expressions.NamedReference;
import org.apache.gravitino.rel.expressions.transforms.Transform;
import org.apache.gravitino.rel.expressions.transforms.Transforms;
@@ -40,6 +41,10 @@ final class ClickHouseTableSqlUtils {
Pattern.compile("toYear\\((.+)\\)", Pattern.CASE_INSENSITIVE);
private static final Pattern TO_MONTH_PATTERN =
Pattern.compile("toYYYYMM\\((.+)\\)", Pattern.CASE_INSENSITIVE);
+ private static final Pattern TO_START_OF_WEEK_PATTERN =
+ Pattern.compile("toStartOfWeek[(](.+)[)]", Pattern.CASE_INSENSITIVE);
+ private static final Pattern TO_START_OF_MONTH_PATTERN =
+ Pattern.compile("toStartOfMonth[(](.+)[)]", Pattern.CASE_INSENSITIVE);
private static final Pattern FUNCTION_WRAPPER_PATTERN =
Pattern.compile("^\\s*([A-Za-z0-9_]+)\\((.*)\\)\\s*$");
@@ -85,6 +90,10 @@ final class ClickHouseTableSqlUtils {
.formatted(quoteIdentifier(partitionFieldName(transform)));
case Transforms.NAME_OF_DAY -> "toDate(%s)"
.formatted(quoteIdentifier(partitionFieldName(transform)));
+ case "tostartofweek" -> "toStartOfWeek(%s)"
+ .formatted(quoteIdentifier(partitionFieldName(transform)));
+ case "tostartofmonth" -> "toStartOfMonth(%s)"
+ .formatted(quoteIdentifier(partitionFieldName(transform)));
default -> throw new IllegalArgumentException(
"Unsupported partition transform: " + transform.name());
};
@@ -207,6 +216,22 @@ final class ClickHouseTableSqlUtils {
return identifier == null ? null : Transforms.day(identifier);
}
+ Matcher toStartOfWeekMatcher =
TO_START_OF_WEEK_PATTERN.matcher(trimmedExpression);
+ if (toStartOfWeekMatcher.matches()) {
+ String identifier =
extractPartitionIdentifier(toStartOfWeekMatcher.group(1));
+ return identifier == null
+ ? null
+ : Transforms.apply("toStartOfWeek", new Expression[]
{NamedReference.field(identifier)});
+ }
+
+ Matcher toStartOfMonthMatcher =
TO_START_OF_MONTH_PATTERN.matcher(trimmedExpression);
+ if (toStartOfMonthMatcher.matches()) {
+ String identifier =
extractPartitionIdentifier(toStartOfMonthMatcher.group(1));
+ return identifier == null
+ ? null
+ : Transforms.apply("toStartOfMonth", new Expression[]
{NamedReference.field(identifier)});
+ }
+
String identifier = extractPartitionIdentifier(trimmedExpression);
return identifier == null ? null : Transforms.identity(identifier);
}
diff --git
a/catalogs-contrib/catalog-jdbc-clickhouse/src/test/java/org/apache/gravitino/catalog/clickhouse/integration/test/CatalogClickHouseIT.java
b/catalogs-contrib/catalog-jdbc-clickhouse/src/test/java/org/apache/gravitino/catalog/clickhouse/integration/test/CatalogClickHouseIT.java
index 9e7dba6e81..09878fb407 100644
---
a/catalogs-contrib/catalog-jdbc-clickhouse/src/test/java/org/apache/gravitino/catalog/clickhouse/integration/test/CatalogClickHouseIT.java
+++
b/catalogs-contrib/catalog-jdbc-clickhouse/src/test/java/org/apache/gravitino/catalog/clickhouse/integration/test/CatalogClickHouseIT.java
@@ -69,6 +69,7 @@ import org.apache.gravitino.rel.Column;
import org.apache.gravitino.rel.Table;
import org.apache.gravitino.rel.TableCatalog;
import org.apache.gravitino.rel.TableChange;
+import org.apache.gravitino.rel.expressions.Expression;
import org.apache.gravitino.rel.expressions.FunctionExpression;
import
org.apache.gravitino.rel.expressions.FunctionExpression.FuncExpressionImpl;
import org.apache.gravitino.rel.expressions.NamedReference;
@@ -637,6 +638,66 @@ public class CatalogClickHouseIT extends BaseIT {
Assertions.assertEquals("",
loaded.properties().get(TableConstants.PARTITION_KEY));
}
+ @Test
+ void testLoadAndCreateWithStartOfWeekAndMonthPartitionTransforms() {
+ TableCatalog tableCatalog = catalog.asTableCatalog();
+ Column[] columns =
+ new Column[] {
+ Column.of(
+ "event_time",
+ Types.TimestampType.withoutTimeZone(),
+ "event time",
+ false,
+ false,
+ DEFAULT_VALUE_NOT_SET)
+ };
+
+ for (String functionName : new String[] {"toStartOfWeek",
"toStartOfMonth"}) {
+ String nativeTableName =
GravitinoITUtils.genRandomName("native_start_partition");
+ String createdTableName =
GravitinoITUtils.genRandomName("created_start_partition");
+ String createNativeSql =
+ String.format(
+ "CREATE TABLE `%s`.`%s` (`event_time` DateTime) "
+ + "ENGINE = MergeTree PARTITION BY %s(event_time) "
+ + "ORDER BY event_time",
+ schemaName, nativeTableName, functionName);
+ clickhouseService.executeQuery(createNativeSql);
+
+ Transform expectedTransform =
+ Transforms.apply(functionName, new Expression[]
{NamedReference.field("event_time")});
+ String expectedPartitionKey = functionName + "(event_time)";
+ Table nativeLoaded =
tableCatalog.loadTable(NameIdentifier.of(schemaName, nativeTableName));
+ Assertions.assertArrayEquals(
+ new Transform[] {expectedTransform}, nativeLoaded.partitioning());
+ Assertions.assertEquals(
+ expectedPartitionKey,
nativeLoaded.properties().get(TableConstants.PARTITION_KEY));
+
+ tableCatalog.createTable(
+ NameIdentifier.of(schemaName, createdTableName),
+ columns,
+ "start function partition roundtrip",
+ createProperties(),
+ new Transform[] {expectedTransform},
+ Distributions.NONE,
+ getSortOrders("event_time"));
+
+ Table createdLoaded =
tableCatalog.loadTable(NameIdentifier.of(schemaName, createdTableName));
+ Assertions.assertArrayEquals(
+ new Transform[] {expectedTransform}, createdLoaded.partitioning());
+ Assertions.assertEquals(
+ expectedPartitionKey,
createdLoaded.properties().get(TableConstants.PARTITION_KEY));
+
+ String showCreateSql =
+ clickhouseService.executeQueryForResult(
+ String.format("SHOW CREATE TABLE `%s`.`%s`", schemaName,
createdTableName));
+ String normalizedShowCreateSql =
StringUtils.deleteWhitespace(showCreateSql).replace("`", "");
+ Assertions.assertTrue(
+ StringUtils.containsIgnoreCase(
+ normalizedShowCreateSql, "PARTITIONBY" + expectedPartitionKey),
+ "CREATE should emit the ClickHouse partition function: " +
showCreateSql);
+ }
+ }
+
@Test
void testCreateAndLoadCompositePrimaryKey() {
String table = GravitinoITUtils.genRandomName("composite_primary_key");
diff --git
a/catalogs-contrib/catalog-jdbc-clickhouse/src/test/java/org/apache/gravitino/catalog/clickhouse/operations/TestClickHouseTableOperationsPartitioning.java
b/catalogs-contrib/catalog-jdbc-clickhouse/src/test/java/org/apache/gravitino/catalog/clickhouse/operations/TestClickHouseTableOperationsPartitioning.java
index 00c0895a08..a44edd8333 100644
---
a/catalogs-contrib/catalog-jdbc-clickhouse/src/test/java/org/apache/gravitino/catalog/clickhouse/operations/TestClickHouseTableOperationsPartitioning.java
+++
b/catalogs-contrib/catalog-jdbc-clickhouse/src/test/java/org/apache/gravitino/catalog/clickhouse/operations/TestClickHouseTableOperationsPartitioning.java
@@ -17,6 +17,7 @@
*/
package org.apache.gravitino.catalog.clickhouse.operations;
+import org.apache.gravitino.rel.expressions.NamedReference;
import org.apache.gravitino.rel.expressions.transforms.Transform;
import org.apache.gravitino.rel.expressions.transforms.Transforms;
import org.junit.jupiter.api.Assertions;
@@ -40,6 +41,14 @@ public class TestClickHouseTableOperationsPartitioning {
Assertions.assertEquals(1, yearPartitions.length);
assertSingleFieldTransform(yearPartitions[0], Transforms.NAME_OF_YEAR,
"event_time");
+ Transform[] weekStartPartitions =
operations.parsePartitioning("toStartOfWeek(event_time)");
+ Assertions.assertEquals(1, weekStartPartitions.length);
+ assertFunctionTransform(weekStartPartitions[0], "toStartOfWeek",
"event_time");
+
+ Transform[] monthStartPartitions =
operations.parsePartitioning("toStartOfMonth(event_time)");
+ Assertions.assertEquals(1, monthStartPartitions.length);
+ assertFunctionTransform(monthStartPartitions[0], "toStartOfMonth",
"event_time");
+
// A native expression that cannot be structured returns an empty
transform array. The raw
// expression is instead exposed through the read-only partition-key
property during load.
Assertions.assertEquals(0,
operations.parsePartitioning("cityHash64(user_id) % 16").length);
@@ -53,6 +62,12 @@ public class TestClickHouseTableOperationsPartitioning {
assertSingleFieldTransform(tuplePartitions[0], Transforms.NAME_OF_MONTH,
"ts");
assertSingleFieldTransform(tuplePartitions[1],
Transforms.NAME_OF_IDENTITY, "tenant_id");
+ Transform[] functionTuplePartitions =
+ operations.parsePartitioning("(toStartOfWeek(ts),
toStartOfMonth(created_at))");
+ Assertions.assertEquals(2, functionTuplePartitions.length);
+ assertFunctionTransform(functionTuplePartitions[0], "toStartOfWeek", "ts");
+ assertFunctionTransform(functionTuplePartitions[1], "toStartOfMonth",
"created_at");
+
Assertions.assertEquals(0, operations.parsePartitioning("tuple()").length);
Assertions.assertEquals(0, operations.parsePartitioning(" ").length);
}
@@ -63,6 +78,8 @@ public class TestClickHouseTableOperationsPartitioning {
// whole partition key is treated as unsupported and returns an empty
transform array rather
// than misrepresenting it as year("f(x)").
Assertions.assertEquals(0,
operations.parsePartitioning("toYear(toString(event_time))").length);
+ Assertions.assertEquals(
+ 0,
operations.parsePartitioning("toStartOfMonth(toDate(event_time))").length);
}
@Test
@@ -73,11 +90,33 @@ public class TestClickHouseTableOperationsPartitioning {
Assertions.assertEquals(1, monthPartitions.length);
assertSingleFieldTransform(monthPartitions[0], Transforms.NAME_OF_MONTH,
"event-time");
+ Transform[] weekStartPartitions =
operations.parsePartitioning("toStartOfWeek(`event-time`)");
+ Assertions.assertEquals(1, weekStartPartitions.length);
+ assertFunctionTransform(weekStartPartitions[0], "toStartOfWeek",
"event-time");
+
+ Transform[] monthStartPartitions =
operations.parsePartitioning("toStartOfMonth(`event-time`)");
+ Assertions.assertEquals(1, monthStartPartitions.length);
+ assertFunctionTransform(monthStartPartitions[0], "toStartOfMonth",
"event-time");
+
Transform[] identityPartitions =
operations.parsePartitioning("`event-time`");
Assertions.assertEquals(1, identityPartitions.length);
assertSingleFieldTransform(identityPartitions[0],
Transforms.NAME_OF_IDENTITY, "event-time");
}
+ @Test
+ public void testWeekModeAndTimezoneFormsRemainUnstructured() {
+ Assertions.assertEquals(0,
operations.parsePartitioning("toStartOfWeek(event_time, 1)").length);
+ Assertions.assertEquals(
+ 0, operations.parsePartitioning("toStartOfWeek(event_time, 1,
'Asia/Shanghai')").length);
+ }
+
+ private void assertFunctionTransform(
+ Transform transform, String expectedName, String expectedColumn) {
+ Assertions.assertEquals(expectedName, transform.name());
+ Assertions.assertEquals(1, transform.arguments().length);
+ Assertions.assertEquals(NamedReference.field(expectedColumn),
transform.arguments()[0]);
+ }
+
private void assertSingleFieldTransform(
Transform transform, String expectedName, String expectedColumn) {
Assertions.assertEquals(expectedName, transform.name());
diff --git
a/catalogs-contrib/catalog-jdbc-clickhouse/src/test/java/org/apache/gravitino/catalog/clickhouse/operations/TestClickHouseTableOperationsUnit.java
b/catalogs-contrib/catalog-jdbc-clickhouse/src/test/java/org/apache/gravitino/catalog/clickhouse/operations/TestClickHouseTableOperationsUnit.java
index 0616db0516..a1e10b116a 100644
---
a/catalogs-contrib/catalog-jdbc-clickhouse/src/test/java/org/apache/gravitino/catalog/clickhouse/operations/TestClickHouseTableOperationsUnit.java
+++
b/catalogs-contrib/catalog-jdbc-clickhouse/src/test/java/org/apache/gravitino/catalog/clickhouse/operations/TestClickHouseTableOperationsUnit.java
@@ -40,6 +40,7 @@ import org.apache.gravitino.catalog.jdbc.JdbcTable;
import org.apache.gravitino.exceptions.GravitinoRuntimeException;
import org.apache.gravitino.exceptions.NoSuchTableException;
import org.apache.gravitino.rel.TableChange;
+import org.apache.gravitino.rel.expressions.Expression;
import org.apache.gravitino.rel.expressions.FunctionExpression;
import org.apache.gravitino.rel.expressions.NamedReference;
import org.apache.gravitino.rel.expressions.distributions.Distributions;
@@ -134,6 +135,48 @@ public class TestClickHouseTableOperationsUnit {
Assertions.assertTrue(exception.getMessage().contains("ClickHouse does not
support varchar"));
}
+ @Test
+ void testToPartitionExpressionSupportsStartFunctions() {
+ Assertions.assertEquals(
+ "toStartOfWeek(`event_time`)",
+ ClickHouseTableSqlUtils.toPartitionExpression(
+ Transforms.apply(
+ "toStartOfWeek", new Expression[]
{NamedReference.field("event_time")})));
+ Assertions.assertEquals(
+ "toStartOfMonth(`event_time`)",
+ ClickHouseTableSqlUtils.toPartitionExpression(
+ Transforms.apply(
+ "toStartOfMonth", new Expression[]
{NamedReference.field("event_time")})));
+ }
+
+ @Test
+ void testToPartitionExpressionRejectsUnsupportedFunctionTransforms() {
+ Assertions.assertThrows(
+ IllegalArgumentException.class,
+ () ->
+ ClickHouseTableSqlUtils.toPartitionExpression(
+ Transforms.apply(
+ "toStartOfQuarter", new Expression[]
{NamedReference.field("event_time")})));
+ Assertions.assertThrows(
+ IllegalArgumentException.class,
+ () ->
+ ClickHouseTableSqlUtils.toPartitionExpression(
+ Transforms.apply(
+ "toStartOfWeek",
+ new Expression[] {
+ NamedReference.field("event_time"),
NamedReference.field("tenant_id")
+ })));
+ Assertions.assertThrows(
+ IllegalArgumentException.class,
+ () ->
+ ClickHouseTableSqlUtils.toPartitionExpression(
+ Transforms.apply(
+ "toStartOfWeek",
+ new Expression[] {
+ FunctionExpression.of("toDate",
NamedReference.field("event_time"))
+ })));
+ }
+
private ExposedClickHouseTableOperations newOps(DataSource dataSource) {
ExposedClickHouseTableOperations ops = new
ExposedClickHouseTableOperations();
ops.initialize(
diff --git a/docs/jdbc-clickhouse-catalog.md b/docs/jdbc-clickhouse-catalog.md
index 56ecf81e6a..0cf90512a0 100644
--- a/docs/jdbc-clickhouse-catalog.md
+++ b/docs/jdbc-clickhouse-catalog.md
@@ -171,7 +171,7 @@ See [Manage Catalogs and
Schemas](./manage-catalogs-and-schemas.md#schema-operat
|---------------------|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| Mapping | Gravitino table maps to a ClickHouse table
|
| Engines | **MergeTree family** (`MergeTree` default,
`ReplacingMergeTree`, `SummingMergeTree`, `AggregatingMergeTree`,
`CollapsingMergeTree`, `VersionedCollapsingMergeTree`, `GraphiteMergeTree`):
fully supported, data persists across restarts. **Log family** (`TinyLog`,
`StripeLog`, `Log`): supported, data and table definition persist across
restarts. **`Null`**: supported, table persists, data is always discarded by
design. **`Set`**: supported, table definition persists. [...]
-| Ordering/Partition | MergeTree-family requires exactly one `ORDER BY`
column; only single-column identity `PARTITION BY` is supported on MergeTree
engines. Other engines reject `ORDER BY`/`PARTITION BY`.
|
+| Ordering/Partition | MergeTree-family requires exactly one `ORDER BY`
column; `PARTITION BY` supports single-column identity and the function
expressions listed below. Other engines reject `ORDER BY`/`PARTITION BY`.
|
| Indexes | Primary key; data-skipping indexes
`DATA_SKIPPING_MINMAX`, `DATA_SKIPPING_BLOOM_FILTER`, `DATA_SKIPPING_SET`,
`DATA_SKIPPING_NGRAMBFV1`, and `DATA_SKIPPING_TOKENBFV1` (configurable
granularity via `Index.properties()`).
[...]
| Distribution | Gravitino enforces `Distributions.NONE`; no custom
distribution strategies.
|
| Column defaults | Supported.
|
@@ -271,10 +271,11 @@ The `engine_parameters` property applies to
`ReplacingMergeTree`, `SummingMergeT
- Accept format: `id`, `(id, name)`, `(func(id), name)`, `func(id)`;
- Reject format: `(id + 1)`, `(func(id) + 1)`, etc.
-- `PARTITION BY`: single-column identity and some functions are supported
only, and only for MergeTree-family engines. For example `PARTITION BY
created_at` or `PARTITION BY toYYYYMM(created_at)` are supported, but
`PARTITION BY (created_at + 1)` are not supported.
+- `PARTITION BY`: single-column identity and some functions are supported
only, and only for MergeTree-family engines. For example `PARTITION BY
created_at`, `PARTITION BY toYYYYMM(created_at)`, `PARTITION BY
toStartOfWeek(created_at)`, and `PARTITION BY toStartOfMonth(created_at)` are
supported, but `PARTITION BY (created_at + 1)` is not supported.
In all, the following partitioning expressions are supported:
- Identity: `PARTITION BY column_name`
- - Functions: `PARTITION BY toDate(column_name)`, `PARTITION BY
toYear(column_name)`, `PARTITION BY toYYYYMM(column_name)`. Other functions are
not supported.
+ - Functions: `PARTITION BY toDate(column_name)`, `PARTITION BY
toYear(column_name)`, `PARTITION BY toYYYYMM(column_name)`, `PARTITION BY
toStartOfWeek(column_name)`, and `PARTITION BY toStartOfMonth(column_name)`.
Other function expressions are not supported as structured transforms.
+ - `toStartOfWeek(column_name)` uses ClickHouse's default mode `0` (Sunday
start) and the server timezone. Calls with an explicit mode or timezone are not
structured.
- Not support: `PARTITION BY (column_name + 1)`, `PARTITION BY
(toYear(column_name) + 1)`, etc. (Note: ClickHouse itself does support
arbitrary partitioning expressions, but Gravitino supports only the above
patterns for partitioning).
The patterns above apply when creating a table. When loading a table,
Gravitino preserves ClickHouse's canonical native partition expression (as
returned by `system.tables.partition_key`) in the read-only `partition-key`
property. An arbitrary native expression is therefore retained on load even
when it cannot be mapped to one of the supported `Transform`s; in that case
`Table.partitioning()` is empty and the full expression is exposed through
`partition-key`.
