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 c20fc0c4b7 [#11973] feat(clickhouse): Preserve projection definitions
on table round-trip (#13498)
c20fc0c4b7 is described below
commit c20fc0c4b7f0dc5e5352e1aa1ffed24472b3d49b
Author: StormSpirit <[email protected]>
AuthorDate: Wed Sep 30 17:08:07 2026 +0800
[#11973] feat(clickhouse): Preserve projection definitions on table
round-trip (#13498)
### What changes were proposed in this pull request?
- Read safely reconstructable `Normal` and `Aggregate` projection
definitions from ClickHouse `system.projections` and expose them through
the immutable, ClickHouse-specific `clickhouse.projections` table
property.
- Detect the optional `settings` column through `system.columns`;
preserve safely reconstructable settings when the server exposes them.
ClickHouse 24.9 does not expose this column.
- If a server-provided projection definition or setting fails
validation, log a warning and omit the entire property while preserving
other table metadata; permission and other unexpected SQL errors still
propagate, and user input on `createTable` remains strictly validated.
- Validate and emit equivalent inline projection clauses during
`createTable`, including definitions originally added with native `ALTER
TABLE ... ADD PROJECTION`.
- Limit round-trip support to ClickHouse 24.9+ and the non-replicated
MergeTree-family engines currently supported by this catalog:
`MergeTree`, `ReplacingMergeTree`, `SummingMergeTree`,
`AggregatingMergeTree`, `CollapsingMergeTree`,
`VersionedCollapsingMergeTree`, and `GraphiteMergeTree`. Replicated
MergeTree engines are out of scope.
- Projection-level `WHERE` filters, lightweight `_part_offset`
projections, and compact `PROJECTION ... INDEX` syntax are outside the
round-trip scope.
- Keep projection metadata in a connector-specific property. This change
does not add a shared Projection API, parse table DDL as a fallback,
manage projection ALTER/materialization operations, or copy projection
data.
### Why are the changes needed?
The ClickHouse catalog currently drops projection definitions from
loaded table metadata. Creating a table from that metadata therefore
loses the source table's projections. This change preserves supported
definitions through the `loadTable`/`createTable` round-trip while
keeping the general ClickHouse catalog baseline at 24.8.14.
Fix: #11973
### Does this PR introduce _any_ user-facing change?
Yes. It adds the immutable `clickhouse.projections` table property for
projection metadata round-tripping. Projection round-trip requires a
server that provides `system.projections` (ClickHouse 24.9 or later);
ordinary catalog metadata loading on 24.8 remains supported. Tables
without projections do not receive this property.
If validation rejects a server-provided projection definition or
setting, `loadTable` still returns the other table metadata but omits
the entire property with a warning. Recreating a table from that
metadata does not preserve its source projections.
### How was this patch tested?
- `./gradlew :catalogs-contrib:catalog-jdbc-clickhouse:spotlessCheck`:
passed.
- `./gradlew :catalogs-contrib:catalog-jdbc-clickhouse:test -PskipITs`:
144 tests passed, with no failures, errors, or skips.
- `./gradlew :catalogs-contrib:catalog-jdbc-clickhouse:test --tests
'org.apache.gravitino.catalog.clickhouse.integration.test.CatalogClickHouseProjectionIT'
-PskipDockerTests=false`: 2 tests passed against ClickHouse 24.9.1.3278
and 25.12.2.54; the 25.12 case also verifies that a table with a native
`_part_offset` projection remains loadable without
`clickhouse.projections`.
- `./gradlew :catalogs-contrib:catalog-jdbc-clickhouse:test --tests
'org.apache.gravitino.catalog.clickhouse.integration.test.CatalogClickHouseIT.testCreateAndLoadClickhouseTable'
-PskipDockerTests=false`: 1 test passed against ClickHouse 24.8.14.
- `./gradlew :catalogs-contrib:catalog-jdbc-clickhouse:test --tests
'org.apache.gravitino.catalog.clickhouse.integration.test.CatalogClickHouseClusterIT'
-PskipDockerTests=false`: 20 tests passed.
- `./gradlew rat`: passed.
- `./gradlew :catalogs-contrib:catalog-jdbc-clickhouse:build -x test`:
passed.
These local results were obtained on the final code and test state
pushed as commit 2f012c4.
---------
Signed-off-by: jiangxt2 <[email protected]>
---
.../catalog-jdbc-clickhouse/build.gradle.kts | 1 +
.../ClickHouseTablePropertiesMetadata.java | 15 +
.../operations/ClickHouseTableOperations.java | 117 +++++++
.../operations/ClickHouseTableSqlUtils.java | 298 ++++++++++++++++
.../TestClickHouseTablePropertiesMetadata.java | 19 +
.../test/CatalogClickHouseProjectionIT.java | 315 +++++++++++++++++
.../TestClickHouseTableOperationsUnit.java | 384 +++++++++++++++++++++
docs/jdbc-clickhouse-catalog.md | 33 ++
8 files changed, 1182 insertions(+)
diff --git a/catalogs-contrib/catalog-jdbc-clickhouse/build.gradle.kts
b/catalogs-contrib/catalog-jdbc-clickhouse/build.gradle.kts
index 65ec197473..7739b79574 100644
--- a/catalogs-contrib/catalog-jdbc-clickhouse/build.gradle.kts
+++ b/catalogs-contrib/catalog-jdbc-clickhouse/build.gradle.kts
@@ -37,6 +37,7 @@ dependencies {
implementation(libs.commons.collections4)
implementation(libs.commons.lang3)
implementation(libs.guava)
+ implementation(libs.jackson.databind)
testImplementation(project(":api"))
testImplementation(project(":catalogs:catalog-jdbc-common", "testArtifacts"))
diff --git
a/catalogs-contrib/catalog-jdbc-clickhouse/src/main/java/org/apache/gravitino/catalog/clickhouse/ClickHouseTablePropertiesMetadata.java
b/catalogs-contrib/catalog-jdbc-clickhouse/src/main/java/org/apache/gravitino/catalog/clickhouse/ClickHouseTablePropertiesMetadata.java
index cdcd83b051..31d3ae58d5 100644
---
a/catalogs-contrib/catalog-jdbc-clickhouse/src/main/java/org/apache/gravitino/catalog/clickhouse/ClickHouseTablePropertiesMetadata.java
+++
b/catalogs-contrib/catalog-jdbc-clickhouse/src/main/java/org/apache/gravitino/catalog/clickhouse/ClickHouseTablePropertiesMetadata.java
@@ -37,6 +37,8 @@ import org.apache.gravitino.connector.PropertyEntry;
public class ClickHouseTablePropertiesMetadata extends
JdbcTablePropertiesMetadata {
public static final String GRAVITINO_ENGINE_KEY = TableConstants.ENGINE;
public static final String CLICKHOUSE_ENGINE_KEY =
TableConstants.ENGINE_UPPER;
+ /** Property key for the structured ClickHouse projection definitions. */
+ public static final String CLICKHOUSE_PROJECTIONS_KEY =
"clickhouse.projections";
// The following two properties are mapped to ClickHouse table properties
and can be used by
// tables with different engines.
@@ -126,6 +128,18 @@ public class ClickHouseTablePropertiesMetadata extends
JdbcTablePropertiesMetada
false,
true);
+ /**
+ * Structured ClickHouse projection definitions. This property is immutable
because this connector
+ * supports projection metadata round-tripping during table creation, not
ALTER.
+ */
+ public static final PropertyEntry<String>
CLICKHOUSE_PROJECTIONS_PROPERTY_ENTRY =
+ stringOptionalPropertyEntry(
+ CLICKHOUSE_PROJECTIONS_KEY,
+ "Structured ClickHouse projection definitions for table creation",
+ true,
+ null,
+ false);
+
private static final Map<String, PropertyEntry<?>> PROPERTIES_METADATA =
createPropertiesMetadata();
@@ -154,6 +168,7 @@ public class ClickHouseTablePropertiesMetadata extends
JdbcTablePropertiesMetada
map.put(CLUSTER_SHARDING_KEY_PROPERTY_ENTRY.getName(),
CLUSTER_SHARDING_KEY_PROPERTY_ENTRY);
map.put(ENGINE_PARAMETERS_PROPERTY_ENTRY.getName(),
ENGINE_PARAMETERS_PROPERTY_ENTRY);
map.put(PARTITION_KEY_PROPERTY_ENTRY.getName(),
PARTITION_KEY_PROPERTY_ENTRY);
+ map.put(CLICKHOUSE_PROJECTIONS_PROPERTY_ENTRY.getName(),
CLICKHOUSE_PROJECTIONS_PROPERTY_ENTRY);
return Collections.unmodifiableMap(map);
}
diff --git
a/catalogs-contrib/catalog-jdbc-clickhouse/src/main/java/org/apache/gravitino/catalog/clickhouse/operations/ClickHouseTableOperations.java
b/catalogs-contrib/catalog-jdbc-clickhouse/src/main/java/org/apache/gravitino/catalog/clickhouse/operations/ClickHouseTableOperations.java
index e2236b4003..3af9eac869 100644
---
a/catalogs-contrib/catalog-jdbc-clickhouse/src/main/java/org/apache/gravitino/catalog/clickhouse/operations/ClickHouseTableOperations.java
+++
b/catalogs-contrib/catalog-jdbc-clickhouse/src/main/java/org/apache/gravitino/catalog/clickhouse/operations/ClickHouseTableOperations.java
@@ -30,6 +30,7 @@ import static
org.apache.gravitino.catalog.clickhouse.ClickHouseConstants.IndexC
import static
org.apache.gravitino.catalog.clickhouse.ClickHouseConstants.IndexConstants.RANDOM_SEED;
import static
org.apache.gravitino.catalog.clickhouse.ClickHouseConstants.IndexConstants.SET_MAX_VALUES;
import static
org.apache.gravitino.catalog.clickhouse.ClickHouseTablePropertiesMetadata.CLICKHOUSE_ENGINE_KEY;
+import static
org.apache.gravitino.catalog.clickhouse.ClickHouseTablePropertiesMetadata.CLICKHOUSE_PROJECTIONS_KEY;
import static
org.apache.gravitino.catalog.clickhouse.ClickHouseTablePropertiesMetadata.ENGINE_PROPERTY_ENTRY;
import static
org.apache.gravitino.catalog.clickhouse.ClickHouseTablePropertiesMetadata.GRAVITINO_ENGINE_KEY;
import static
org.apache.gravitino.catalog.clickhouse.operations.ClickHouseClusterUtils.escapeSingleQuotes;
@@ -56,6 +57,7 @@ import java.util.Locale;
import java.util.Map;
import java.util.Objects;
import java.util.Set;
+import java.util.concurrent.atomic.AtomicBoolean;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;
@@ -103,6 +105,8 @@ public class ClickHouseTableOperations extends
JdbcTableOperations {
"Clickhouse does not support nested column names.";
private static final String INVALID_SETTINGS_METADATA_MSG =
"Invalid ClickHouse table SETTINGS metadata";
+ private static final int ERROR_CODE_UNKNOWN_TABLE = 60;
+ private static final Set<String> REQUIRED_PROJECTION_COLUMNS =
Set.of("name", "type", "query");
/** Default GRANULARITY for data skipping indexes, matching ClickHouse's own
default. */
private static final long DEFAULT_INDEX_GRANULARITY = 1;
@@ -153,6 +157,8 @@ public class ClickHouseTableOperations extends
JdbcTableOperations {
"SELECT name, type, expr, granularity FROM system.data_skipping_indices "
+ "WHERE database = ? AND table = ? ORDER BY name";
+ private final AtomicBoolean projectionSourceUnavailableWarningLogged = new
AtomicBoolean();
+
@Override
public void create(
String databaseName,
@@ -352,6 +358,23 @@ public class ClickHouseTableOperations extends
JdbcTableOperations {
// Add Create table clause; capture whether ON CLUSTER is in use
boolean onCluster = appendCreateTableClause(notNullProperties, sqlBuilder,
tableName);
+ List<ClickHouseTableSqlUtils.ProjectionDefinition> projections =
+ ClickHouseTableSqlUtils.parseProjectionDefinitions(
+ notNullProperties.get(CLICKHOUSE_PROJECTIONS_KEY));
+ if (!projections.isEmpty()) {
+ Preconditions.checkArgument(
+ ArrayUtils.isNotEmpty(columns),
+ "ClickHouse projections require an explicit column definition");
+ String configuredEngine = notNullProperties.get(GRAVITINO_ENGINE_KEY);
+ ENGINE projectionEngine =
+ StringUtils.isBlank(configuredEngine)
+ ? ENGINE_PROPERTY_ENTRY.getDefaultValue()
+ : ENGINE.fromString(configuredEngine);
+ Preconditions.checkArgument(
+ projectionEngine.acceptPartition(),
+ "ClickHouse projections are supported only for MergeTree-family
engines");
+ }
+
// We still allow empty columns when the engine is distributed.
if (columns.length > 0) {
buildColumnsDefinition(columns, sqlBuilder);
@@ -359,6 +382,9 @@ public class ClickHouseTableOperations extends
JdbcTableOperations {
// Index definition
appendIndexesSql(indexes, sqlBuilder);
+ // Projection definitions are part of the parenthesized CREATE TABLE
definition.
+
sqlBuilder.append(ClickHouseTableSqlUtils.formatProjectionClauses(projections));
+
sqlBuilder.append("\n)");
}
@@ -900,6 +926,10 @@ public class ClickHouseTableOperations extends
JdbcTableOperations {
// over any settings.* keys that might exist in system.tables (though
getTableProperties()
// currently does not read SETTINGS from system.tables, so no overlap
occurs in practice).
Map<String, String> merged = new HashMap<>(tableProperties);
+ String projectionProperty = getProjectionProperty(connection,
databaseName, tableName);
+ if (StringUtils.isNotEmpty(projectionProperty)) {
+ merged.put(CLICKHOUSE_PROJECTIONS_KEY, projectionProperty);
+ }
if (!systemTableMetadata.settings().isEmpty()) {
merged.putAll(systemTableMetadata.settings());
}
@@ -953,6 +983,93 @@ public class ClickHouseTableOperations extends
JdbcTableOperations {
throw new NoSuchTableException("Table %s does not exist in %s.",
tableName, databaseName);
}
+ @VisibleForTesting
+ @Nullable
+ String getProjectionProperty(Connection connection, String databaseName,
String tableName)
+ throws SQLException {
+ // Probe the stable columns directly so ClickHouse 24.8, which has no
system.projections table,
+ // can continue serving ordinary catalog metadata. Only UNKNOWN_TABLE
means the capability is
+ // unavailable; access and other query errors must remain visible to the
caller.
+ try (PreparedStatement probe =
+ connection.prepareStatement("SELECT database FROM
system.projections LIMIT 0");
+ ResultSet probeResult = probe.executeQuery()) {
+ if (probeResult.next()) {
+ throw new SQLException(
+ "Unexpected row returned by the system.projections capability
probe");
+ }
+ } catch (SQLException e) {
+ if (e.getErrorCode() == ERROR_CODE_UNKNOWN_TABLE) {
+ if (projectionSourceUnavailableWarningLogged.compareAndSet(false,
true)) {
+ LOG.warn(
+ "ClickHouse does not provide system.projections; projection
metadata round-trip "
+ + "requires ClickHouse 24.9 or later");
+ }
+ return null;
+ }
+ throw e;
+ }
+
+ Set<String> projectionColumns = new HashSet<>();
+ try (PreparedStatement statement =
+ connection.prepareStatement(
+ "SELECT name FROM system.columns "
+ + "WHERE database = 'system' AND table = 'projections'");
+ ResultSet resultSet = statement.executeQuery()) {
+ while (resultSet.next()) {
+ projectionColumns.add(resultSet.getString("name"));
+ }
+ }
+ if (!projectionColumns.containsAll(REQUIRED_PROJECTION_COLUMNS)) {
+ Set<String> missingColumns = new HashSet<>(REQUIRED_PROJECTION_COLUMNS);
+ missingColumns.removeAll(projectionColumns);
+ throw new SQLException(
+ "system.projections is missing required columns: "
+ + missingColumns.stream().sorted().collect(Collectors.joining(",
")));
+ }
+
+ boolean hasSettingsColumn = projectionColumns.contains("settings");
+ String query =
+ "SELECT name, type, query"
+ + (hasSettingsColumn ? ", toJSONString(settings) AS settings_json"
: "")
+ + " FROM system.projections WHERE database = ? AND table = ? ORDER
BY name";
+ List<ClickHouseTableSqlUtils.ProjectionDefinition> definitions = new
ArrayList<>();
+ try (PreparedStatement statement = connection.prepareStatement(query)) {
+ statement.setString(1, databaseName);
+ statement.setString(2, tableName);
+ try (ResultSet resultSet = statement.executeQuery()) {
+ while (resultSet.next()) {
+ Map<String, String> settings;
+ try {
+ settings =
+ hasSettingsColumn
+ ? ClickHouseTableSqlUtils.parseProjectionSettings(
+ resultSet.getString("settings_json"))
+ : Collections.emptyMap();
+ } catch (IllegalArgumentException e) {
+ LOG.warn(
+ "Skip projection metadata of {}.{}: {}", databaseName,
tableName, e.getMessage());
+ return null;
+ }
+ definitions.add(
+ new ClickHouseTableSqlUtils.ProjectionDefinition(
+ resultSet.getString("name"),
+ resultSet.getString("type"),
+ resultSet.getString("query"),
+ settings));
+ }
+ }
+ }
+ if (definitions.isEmpty()) {
+ return null;
+ }
+ try {
+ return
ClickHouseTableSqlUtils.serializeProjectionDefinitions(definitions);
+ } catch (IllegalArgumentException e) {
+ LOG.warn("Skip projection metadata of {}.{}: {}", databaseName,
tableName, e.getMessage());
+ return null;
+ }
+ }
+
@Override
protected Transform[] getTablePartitioning(
Connection connection, String databaseName, String tableName) throws
SQLException {
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 c525dfc2a5..905a090fe2 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
@@ -18,12 +18,25 @@
*/
package org.apache.gravitino.catalog.clickhouse.operations;
+import com.fasterxml.jackson.core.JsonParser;
+import com.fasterxml.jackson.core.JsonProcessingException;
+import com.fasterxml.jackson.databind.DeserializationFeature;
+import com.fasterxml.jackson.databind.JsonNode;
+import com.fasterxml.jackson.databind.ObjectMapper;
+import com.fasterxml.jackson.databind.node.ArrayNode;
+import com.fasterxml.jackson.databind.node.ObjectNode;
import com.google.common.base.Preconditions;
import com.google.common.collect.ImmutableList;
import java.util.ArrayList;
import java.util.Collections;
+import java.util.Comparator;
+import java.util.HashSet;
+import java.util.Iterator;
import java.util.List;
import java.util.Locale;
+import java.util.Map;
+import java.util.Set;
+import java.util.TreeMap;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import javax.annotation.Nullable;
@@ -47,9 +60,294 @@ final class ClickHouseTableSqlUtils {
Pattern.compile("toStartOfMonth[(](.+)[)]", Pattern.CASE_INSENSITIVE);
private static final Pattern FUNCTION_WRAPPER_PATTERN =
Pattern.compile("^\\s*([A-Za-z0-9_]+)\\((.*)\\)\\s*$");
+ private static final Pattern PROJECTION_SETTING_NAME_PATTERN =
+ Pattern.compile("^[A-Za-z_][A-Za-z0-9_]*$");
+ private static final Pattern NUMERIC_SETTING_LITERAL_PATTERN =
+ Pattern.compile("^[+-]?(?:\\d+(?:\\.\\d*)?|\\.\\d+)(?:[eE][+-]?\\d+)?$");
+ private static final Pattern IDENTIFIER_SETTING_LITERAL_PATTERN =
+ Pattern.compile("^[A-Za-z_][A-Za-z0-9_]*$");
+ private static final Set<String> PROJECTION_PROPERTY_FIELDS =
+ Set.of("name", "type", "query", "settings");
+ private static final ObjectMapper PROJECTION_JSON_MAPPER =
+ new ObjectMapper()
+ .enable(JsonParser.Feature.STRICT_DUPLICATE_DETECTION)
+ .enable(DeserializationFeature.FAIL_ON_TRAILING_TOKENS);
private ClickHouseTableSqlUtils() {}
+ record ProjectionDefinition(
+ String name, String type, String query, Map<String, String> settings) {}
+
+ static String serializeProjectionDefinitions(List<ProjectionDefinition>
definitions) {
+ ArrayNode root = PROJECTION_JSON_MAPPER.createArrayNode();
+ Set<String> projectionNames = new HashSet<>();
+ definitions.stream()
+ .sorted(Comparator.comparing(ProjectionDefinition::name))
+ .forEach(
+ definition -> {
+ validateProjectionDefinition(definition);
+ Preconditions.checkArgument(
+ projectionNames.add(definition.name()), "Duplicate
ClickHouse projection name");
+ ObjectNode projection = root.addObject();
+ projection.put("name", definition.name());
+ projection.put("type", definition.type());
+ projection.put("query", definition.query().trim());
+ ObjectNode settings = projection.putObject("settings");
+ new TreeMap<>(definition.settings()).forEach(settings::put);
+ });
+ try {
+ return PROJECTION_JSON_MAPPER.writeValueAsString(root);
+ } catch (JsonProcessingException e) {
+ throw new IllegalStateException("Unable to serialize ClickHouse
projection metadata", e);
+ }
+ }
+
+ static List<ProjectionDefinition> parseProjectionDefinitions(@Nullable
String json) {
+ if (StringUtils.isBlank(json)) {
+ return Collections.emptyList();
+ }
+
+ JsonNode root;
+ try {
+ root = PROJECTION_JSON_MAPPER.readTree(json);
+ } catch (JsonProcessingException e) {
+ throw new IllegalArgumentException("Invalid ClickHouse projection
property JSON", e);
+ }
+ Preconditions.checkArgument(
+ root != null && root.isArray(), "Projection property must be a JSON
array");
+
+ List<ProjectionDefinition> definitions = new ArrayList<>();
+ Set<String> projectionNames = new HashSet<>();
+ for (JsonNode projection : root) {
+ Preconditions.checkArgument(
+ projection.isObject(), "Each projection definition must be a JSON
object");
+ Iterator<String> fields = projection.fieldNames();
+ while (fields.hasNext()) {
+ String field = fields.next();
+ Preconditions.checkArgument(
+ PROJECTION_PROPERTY_FIELDS.contains(field),
+ "Unsupported ClickHouse projection property field: %s",
+ field);
+ }
+
+ String name = requiredText(projection, "name");
+ String type = requiredText(projection, "type");
+ String query = requiredText(projection, "query");
+ Map<String, String> settings =
parseProjectionSettings(projection.get("settings"));
+ ProjectionDefinition definition = new ProjectionDefinition(name, type,
query, settings);
+ validateProjectionDefinition(definition);
+ Preconditions.checkArgument(
+ projectionNames.add(name), "Duplicate ClickHouse projection name");
+ definitions.add(definition);
+ }
+ definitions.sort(Comparator.comparing(ProjectionDefinition::name));
+ return Collections.unmodifiableList(definitions);
+ }
+
+ static String formatProjectionClauses(List<ProjectionDefinition>
definitions) {
+ StringBuilder sqlBuilder = new StringBuilder();
+ for (ProjectionDefinition definition : definitions) {
+ validateProjectionDefinition(definition);
+ sqlBuilder
+ .append(",\n PROJECTION ")
+ .append(quoteProjectionIdentifier(definition.name()))
+ .append(" (\n ")
+ .append(definition.query().trim())
+ .append("\n )");
+ if (!definition.settings().isEmpty()) {
+ String settings =
+ new TreeMap<>(definition.settings())
+ .entrySet().stream()
+ .map(entry -> entry.getKey() + " = " + entry.getValue())
+ .collect(java.util.stream.Collectors.joining(", "));
+ sqlBuilder.append(" WITH SETTINGS (").append(settings).append(")");
+ }
+ }
+ return sqlBuilder.toString();
+ }
+
+ private static String requiredText(JsonNode object, String field) {
+ JsonNode value = object.get(field);
+ Preconditions.checkArgument(
+ value != null && value.isTextual() &&
StringUtils.isNotBlank(value.asText()),
+ "ClickHouse projection property must include non-empty text field
'%s'",
+ field);
+ return value.asText();
+ }
+
+ static Map<String, String> parseProjectionSettings(String json) {
+ JsonNode settingsNode;
+ try {
+ settingsNode = PROJECTION_JSON_MAPPER.readTree(json);
+ } catch (JsonProcessingException e) {
+ throw new IllegalArgumentException("Invalid ClickHouse projection
settings JSON", e);
+ }
+ return parseProjectionSettings(settingsNode);
+ }
+
+ private static Map<String, String> parseProjectionSettings(@Nullable
JsonNode settingsNode) {
+ if (settingsNode == null) {
+ return Collections.emptyMap();
+ }
+ Preconditions.checkArgument(
+ settingsNode.isObject(), "Projection settings must be a JSON object");
+
+ Map<String, String> settings = new TreeMap<>();
+ Iterator<Map.Entry<String, JsonNode>> fields = settingsNode.fields();
+ while (fields.hasNext()) {
+ Map.Entry<String, JsonNode> field = fields.next();
+ JsonNode value = field.getValue();
+ Preconditions.checkArgument(
+ PROJECTION_SETTING_NAME_PATTERN.matcher(field.getKey()).matches(),
+ "Invalid ClickHouse projection setting name");
+ Preconditions.checkArgument(
+ value.isTextual() && isSafeSettingLiteral(value.asText()),
+ "Unsupported ClickHouse projection setting value");
+ settings.put(field.getKey(), value.asText().trim());
+ }
+ return Collections.unmodifiableMap(settings);
+ }
+
+ private static void validateProjectionDefinition(ProjectionDefinition
definition) {
+ Preconditions.checkArgument(
+ StringUtils.isNotBlank(definition.name()), "ClickHouse projection name
is required");
+ Preconditions.checkArgument(
+ definition.name().chars().noneMatch(Character::isISOControl),
+ "ClickHouse projection name contains a control character");
+ Preconditions.checkArgument(
+ "Normal".equals(definition.type()) ||
"Aggregate".equals(definition.type()),
+ "Unsupported ClickHouse projection type: %s",
+ definition.type());
+ Preconditions.checkArgument(
+ StringUtils.isNotBlank(definition.query()) &&
isSafeProjectionQuery(definition.query()),
+ "Invalid ClickHouse projection query");
+ Preconditions.checkArgument(
+ definition.settings() != null, "Projection settings cannot be null");
+ definition
+ .settings()
+ .forEach(
+ (name, value) -> {
+ Preconditions.checkArgument(
+ PROJECTION_SETTING_NAME_PATTERN.matcher(name).matches(),
+ "Invalid ClickHouse projection setting name");
+ Preconditions.checkArgument(
+ isSafeSettingLiteral(value), "Unsupported ClickHouse
projection setting value");
+ });
+ }
+
+ private static boolean isSafeSettingLiteral(String value) {
+ String literal = StringUtils.trimToEmpty(value);
+ return NUMERIC_SETTING_LITERAL_PATTERN.matcher(literal).matches()
+ || "true".equalsIgnoreCase(literal)
+ || "false".equalsIgnoreCase(literal)
+ || IDENTIFIER_SETTING_LITERAL_PATTERN.matcher(literal).matches()
+ || isValidQuotedSettingLiteral(literal);
+ }
+
+ private static boolean isValidQuotedSettingLiteral(String literal) {
+ if (literal.length() < 2
+ || literal.charAt(0) != '\''
+ || literal.charAt(literal.length() - 1) != '\'') {
+ return false;
+ }
+ for (int i = 1; i < literal.length() - 1; i++) {
+ char current = literal.charAt(i);
+ if (current == '\\') {
+ if (i + 1 >= literal.length() - 1) {
+ return false;
+ }
+ i++;
+ } else if (current == '\'') {
+ if (i + 1 >= literal.length() - 1 || literal.charAt(i + 1) != '\'') {
+ return false;
+ }
+ i++;
+ }
+ }
+ return true;
+ }
+
+ private static boolean isSafeProjectionQuery(String query) {
+ int depth = 0;
+ char quote = 0;
+ boolean identifierQuote = false;
+ StringBuilder quotedIdentifier = new StringBuilder();
+ boolean hasSelect = false;
+ StringBuilder token = new StringBuilder();
+ for (int i = 0; i < query.length(); i++) {
+ char current = query.charAt(i);
+ if (quote != 0) {
+ if (current == '\\' && i + 1 < query.length()) {
+ char escaped = query.charAt(++i);
+ if (identifierQuote) {
+ quotedIdentifier.append(escaped);
+ }
+ } else if (current == quote) {
+ if (i + 1 < query.length() && query.charAt(i + 1) == quote) {
+ if (identifierQuote) {
+ quotedIdentifier.append(current);
+ }
+ i++;
+ } else {
+ if (identifierQuote &&
"_part_offset".equalsIgnoreCase(quotedIdentifier.toString())) {
+ return false;
+ }
+ quote = 0;
+ identifierQuote = false;
+ quotedIdentifier.setLength(0);
+ }
+ } else if (identifierQuote) {
+ quotedIdentifier.append(current);
+ }
+ continue;
+ }
+
+ if (Character.isLetterOrDigit(current) || current == '_') {
+ token.append(Character.toLowerCase(current));
+ continue;
+ }
+ if (!token.isEmpty()) {
+ String value = token.toString();
+ if ("_part_offset".equals(value) || (depth == 0 &&
"where".equals(value))) {
+ return false;
+ }
+ hasSelect |= "select".equals(value);
+ token.setLength(0);
+ }
+
+ if (current == '\'' || current == '"' || current == '`') {
+ quote = current;
+ identifierQuote = current != '\'';
+ quotedIdentifier.setLength(0);
+ } else if (current == ';' || current == '#') {
+ return false;
+ } else if (current == '-' && i + 1 < query.length() && query.charAt(i +
1) == '-') {
+ return false;
+ } else if (current == '/'
+ && i + 1 < query.length()
+ && (query.charAt(i + 1) == '*' || query.charAt(i + 1) == '/')) {
+ return false;
+ } else if (current == '(') {
+ depth++;
+ } else if (current == ')' && --depth < 0) {
+ return false;
+ }
+ }
+ if (!token.isEmpty()) {
+ String value = token.toString();
+ if ("_part_offset".equals(value) || (depth == 0 &&
"where".equals(value))) {
+ return false;
+ }
+ hasSelect |= "select".equals(value);
+ }
+ return quote == 0 && depth == 0 && hasSelect;
+ }
+
+ private static String quoteProjectionIdentifier(String identifier) {
+ Preconditions.checkArgument(StringUtils.isNotBlank(identifier),
"Projection name is required");
+ return "`" + identifier.replace("\\", "\\\\").replace("`", "\\`") + "`";
+ }
+
static Transform[] parsePartitioning(@Nullable String partitionKey) {
if (StringUtils.isBlank(partitionKey)) {
return Transforms.EMPTY_TRANSFORM;
diff --git
a/catalogs-contrib/catalog-jdbc-clickhouse/src/test/java/org/apache/gravitino/catalog/clickhouse/TestClickHouseTablePropertiesMetadata.java
b/catalogs-contrib/catalog-jdbc-clickhouse/src/test/java/org/apache/gravitino/catalog/clickhouse/TestClickHouseTablePropertiesMetadata.java
index 81b6fbba79..53197ec1b9 100644
---
a/catalogs-contrib/catalog-jdbc-clickhouse/src/test/java/org/apache/gravitino/catalog/clickhouse/TestClickHouseTablePropertiesMetadata.java
+++
b/catalogs-contrib/catalog-jdbc-clickhouse/src/test/java/org/apache/gravitino/catalog/clickhouse/TestClickHouseTablePropertiesMetadata.java
@@ -43,6 +43,10 @@ public class TestClickHouseTablePropertiesMetadata {
properties.put(TableConstants.PARTITION_KEY, "cityHash64(x) % 7");
properties.put(TableConstants.ENGINE, "MergeTree");
properties.put(TableConstants.SETTINGS_PREFIX + "index_granularity",
"8192");
+ properties.put(
+ ClickHouseTablePropertiesMetadata.CLICKHOUSE_PROJECTIONS_KEY,
+ "[{\"name\":\"by_name\",\"type\":\"Normal\","
+ + "\"query\":\"SELECT name ORDER BY name\",\"settings\":{}}]");
Map<String, String> jdbcProperties =
metadata.transformToJdbcProperties(properties);
@@ -55,6 +59,9 @@ public class TestClickHouseTablePropertiesMetadata {
// Other properties pass through unchanged.
Assertions.assertEquals(
"8192", jdbcProperties.get(TableConstants.SETTINGS_PREFIX +
"index_granularity"));
+ Assertions.assertEquals(
+
properties.get(ClickHouseTablePropertiesMetadata.CLICKHOUSE_PROJECTIONS_KEY),
+
jdbcProperties.get(ClickHouseTablePropertiesMetadata.CLICKHOUSE_PROJECTIONS_KEY));
}
@Test
@@ -80,4 +87,16 @@ public class TestClickHouseTablePropertiesMetadata {
Assertions.assertFalse(entry.isHidden());
Assertions.assertEquals("", entry.getDefaultValue());
}
+
+ @Test
+ void testClickHouseProjectionsPropertyEntry() {
+ PropertyEntry<?> entry =
+
ClickHouseTablePropertiesMetadata.CLICKHOUSE_PROJECTIONS_PROPERTY_ENTRY;
+ Assertions.assertEquals(
+ ClickHouseTablePropertiesMetadata.CLICKHOUSE_PROJECTIONS_KEY,
entry.getName());
+ Assertions.assertTrue(entry.isImmutable());
+ Assertions.assertFalse(entry.isReserved());
+ Assertions.assertFalse(entry.isHidden());
+ Assertions.assertNull(entry.getDefaultValue());
+ }
}
diff --git
a/catalogs-contrib/catalog-jdbc-clickhouse/src/test/java/org/apache/gravitino/catalog/clickhouse/integration/test/CatalogClickHouseProjectionIT.java
b/catalogs-contrib/catalog-jdbc-clickhouse/src/test/java/org/apache/gravitino/catalog/clickhouse/integration/test/CatalogClickHouseProjectionIT.java
new file mode 100644
index 0000000000..e6f0e9ce47
--- /dev/null
+++
b/catalogs-contrib/catalog-jdbc-clickhouse/src/test/java/org/apache/gravitino/catalog/clickhouse/integration/test/CatalogClickHouseProjectionIT.java
@@ -0,0 +1,315 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+package org.apache.gravitino.catalog.clickhouse.integration.test;
+
+import static
org.apache.gravitino.catalog.clickhouse.ClickHouseTablePropertiesMetadata.CLICKHOUSE_PROJECTIONS_KEY;
+
+import com.fasterxml.jackson.databind.JsonNode;
+import com.fasterxml.jackson.databind.ObjectMapper;
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.util.ArrayList;
+import java.util.Collections;
+import java.util.HashSet;
+import java.util.List;
+import java.util.Map;
+import java.util.Optional;
+import java.util.Set;
+import org.apache.gravitino.Catalog;
+import org.apache.gravitino.NameIdentifier;
+import
org.apache.gravitino.catalog.clickhouse.ClickHouseConstants.TableConstants;
+import org.apache.gravitino.catalog.jdbc.config.JdbcConfig;
+import org.apache.gravitino.client.GravitinoMetalake;
+import org.apache.gravitino.integration.test.container.ClickHouseContainer;
+import org.apache.gravitino.integration.test.util.BaseIT;
+import org.apache.gravitino.integration.test.util.GravitinoITUtils;
+import org.apache.gravitino.rel.Table;
+import org.apache.gravitino.rel.TableCatalog;
+import org.junit.jupiter.api.AfterAll;
+import org.junit.jupiter.api.Assertions;
+import org.junit.jupiter.api.BeforeAll;
+import org.junit.jupiter.api.Tag;
+import org.junit.jupiter.api.Test;
+import org.junit.jupiter.api.TestInstance;
+import org.junit.jupiter.api.TestInstance.Lifecycle;
+
+/**
+ * Integration tests for projection metadata round-tripping across ClickHouse
system-table schemas.
+ */
+@Tag("gravitino-docker-test")
+@TestInstance(Lifecycle.PER_CLASS)
+public class CatalogClickHouseProjectionIT extends BaseIT {
+
+ private static final String CLICKHOUSE_24_9_IMAGE =
"clickhouse/clickhouse-server:24.9.1.3278";
+ private static final String CLICKHOUSE_25_12_IMAGE =
"clickhouse/clickhouse-server:25.12.2.54";
+ private static final String DATABASE = "gravitino_projection_it";
+ private static final String SOURCE_TABLE = "projection_source";
+ private static final String TARGET_TABLE = "projection_target";
+ private static final String NO_PROJECTION_TABLE = "no_projection_source";
+ private static final String UNSUPPORTED_PROJECTION_TABLE =
"unsupported_projection_source";
+
+ private GravitinoMetalake metalake;
+ private String metalakeName;
+
+ @BeforeAll
+ void createMetalake() {
+ metalakeName =
GravitinoITUtils.genRandomName("clickhouse_projection_it_metalake");
+ client.createMetalake(
+ metalakeName, "projection metadata integration test",
Collections.emptyMap());
+ metalake = client.loadMetalake(metalakeName);
+ }
+
+ @AfterAll
+ void dropMetalake() {
+ if (metalake != null) {
+ client.disableMetalake(metalakeName);
+ client.dropMetalake(metalakeName, true);
+ }
+ }
+
+ @Test
+ void testProjectionRoundTripOnClickHouse24_9WithoutSettingsColumn() throws
Exception {
+ runProjectionRoundTrip(CLICKHOUSE_24_9_IMAGE, false);
+ }
+
+ @Test
+ void testProjectionSettingsRoundTripOnClickHouse25_12() throws Exception {
+ runProjectionRoundTrip(CLICKHOUSE_25_12_IMAGE, true);
+ }
+
+ private void runProjectionRoundTrip(String image, boolean
withProjectionSettings)
+ throws Exception {
+ try (HostMappedClickHouseContainer container = new
HostMappedClickHouseContainer(image)) {
+ container.start();
+ String catalogName =
GravitinoITUtils.genRandomName("clickhouse_projection_it_catalog");
+ Catalog catalog = createCatalog(catalogName, container);
+ try {
+ catalog.asSchemas().createSchema(DATABASE, null,
Collections.emptyMap());
+ createSourceTable(container, withProjectionSettings);
+
+ TableCatalog tableCatalog = catalog.asTableCatalog();
+ NameIdentifier sourceIdentifier = NameIdentifier.of(DATABASE,
SOURCE_TABLE);
+ Table loaded = tableCatalog.loadTable(sourceIdentifier);
+ String projectionProperty =
loaded.properties().get(CLICKHOUSE_PROJECTIONS_KEY);
+ Assertions.assertNotNull(projectionProperty,
loaded.properties().toString());
+ JsonNode definitions = new ObjectMapper().readTree(projectionProperty);
+ Assertions.assertEquals(4, definitions.size());
+ JsonNode orderedProjection = null;
+ JsonNode aggregateProjection = null;
+ Set<String> projectionNames = new HashSet<>();
+ for (JsonNode definition : definitions) {
+ String projectionName = definition.get("name").asText();
+ projectionNames.add(projectionName);
+ if ("by date".equals(projectionName)) {
+ orderedProjection = definition;
+ } else if ("by_region".equals(projectionName)) {
+ aggregateProjection = definition;
+ }
+ }
+ Assertions.assertEquals(
+ Set.of("by date", "by_region", "by expression", "by_customer"),
projectionNames);
+ Assertions.assertNotNull(orderedProjection);
+ Assertions.assertEquals("Normal",
orderedProjection.get("type").asText());
+ Assertions.assertNotNull(aggregateProjection);
+ Assertions.assertEquals("Aggregate",
aggregateProjection.get("type").asText());
+ if (withProjectionSettings) {
+ Assertions.assertEquals(
+ "128",
orderedProjection.get("settings").get("index_granularity").asText());
+ } else {
+ Assertions.assertTrue(orderedProjection.get("settings").isEmpty());
+ }
+
+ NameIdentifier targetIdentifier = NameIdentifier.of(DATABASE,
TARGET_TABLE);
+ Table recreated =
+ tableCatalog.createTable(
+ targetIdentifier,
+ loaded.columns(),
+ loaded.comment(),
+ loaded.properties(),
+ loaded.partitioning(),
+ loaded.distribution(),
+ loaded.sortOrder(),
+ loaded.index());
+ Assertions.assertEquals(TARGET_TABLE, recreated.name());
+
+ Table loadedTarget = tableCatalog.loadTable(targetIdentifier);
+ Assertions.assertEquals(
+ projectionProperty,
loadedTarget.properties().get(CLICKHOUSE_PROJECTIONS_KEY));
+ String sourceCreate = readCreateTable(container, SOURCE_TABLE);
+ String targetCreate = readCreateTable(container, TARGET_TABLE);
+ for (String projectionName : projectionNames) {
+ Assertions.assertTrue(sourceCreate.contains(projectionName),
sourceCreate);
+ Assertions.assertTrue(targetCreate.contains(projectionName),
targetCreate);
+ }
+ Assertions.assertTrue(sourceCreate.contains("PROJECTION"),
sourceCreate);
+ Assertions.assertTrue(targetCreate.contains("PROJECTION"),
targetCreate);
+ if (withProjectionSettings) {
+ Assertions.assertTrue(targetCreate.contains("index_granularity"),
targetCreate);
+ }
+ List<List<String>> sourceRows =
+ readProjectionRows(container, SOURCE_TABLE,
withProjectionSettings);
+ List<List<String>> targetRows =
+ readProjectionRows(container, TARGET_TABLE,
withProjectionSettings);
+ Assertions.assertEquals(4, sourceRows.size());
+ Assertions.assertEquals(sourceRows, targetRows);
+
+ Table withoutProjections =
+ tableCatalog.loadTable(NameIdentifier.of(DATABASE,
NO_PROJECTION_TABLE));
+ Assertions.assertFalse(
+
withoutProjections.properties().containsKey(CLICKHOUSE_PROJECTIONS_KEY));
+ if (withProjectionSettings) {
+ Table withUnsupportedProjection =
+ tableCatalog.loadTable(NameIdentifier.of(DATABASE,
UNSUPPORTED_PROJECTION_TABLE));
+ Assertions.assertEquals(2,
withUnsupportedProjection.columns().length);
+ Assertions.assertEquals(
+ "MergeTree",
withUnsupportedProjection.properties().get(TableConstants.ENGINE));
+ Assertions.assertFalse(
+
withUnsupportedProjection.properties().containsKey(CLICKHOUSE_PROJECTIONS_KEY));
+ }
+ } finally {
+ metalake.disableCatalog(catalogName);
+ metalake.dropCatalog(catalogName, true);
+ }
+ }
+ }
+
+ private Catalog createCatalog(String catalogName,
HostMappedClickHouseContainer container) {
+ Map<String, String> properties =
+ Map.of(
+ JdbcConfig.JDBC_URL.getKey(), container.getJdbcUrl(),
+ JdbcConfig.JDBC_DRIVER.getKey(),
"com.clickhouse.jdbc.ClickHouseDriver",
+ JdbcConfig.USERNAME.getKey(), container.getUsername(),
+ JdbcConfig.PASSWORD.getKey(), container.getPassword());
+ metalake.createCatalog(
+ catalogName, Catalog.Type.RELATIONAL, "jdbc-clickhouse", "projection
catalog", properties);
+ return metalake.loadCatalog(catalogName);
+ }
+
+ private static void createSourceTable(
+ ClickHouseContainer container, boolean withProjectionSettings) throws
SQLException {
+ String customSettings =
+ withProjectionSettings ? " WITH SETTINGS (index_granularity = 128)" :
"";
+ String createTable =
+ "CREATE TABLE "
+ + DATABASE
+ + "."
+ + SOURCE_TABLE
+ + " ("
+ + "id UInt64, event_date Date, region String, customer String, "
+ + "PROJECTION `by date` (SELECT event_date, id ORDER BY
event_date, id)"
+ + customSettings
+ + ", PROJECTION by_region (SELECT region, count() GROUP BY region)"
+ + ", PROJECTION `by expression` (SELECT lower(concat(customer,
'O''Reilly')) "
+ + "ORDER BY lower(concat(customer, 'O''Reilly')))"
+ + ") ENGINE = MergeTree ORDER BY id";
+ String createTableWithoutProjections =
+ "CREATE TABLE "
+ + DATABASE
+ + "."
+ + NO_PROJECTION_TABLE
+ + " (id UInt64) ENGINE = MergeTree ORDER BY id";
+ String addProjection =
+ "ALTER TABLE "
+ + DATABASE
+ + "."
+ + SOURCE_TABLE
+ + " ADD PROJECTION by_customer (SELECT customer ORDER BY
customer)";
+
+ try (Connection connection =
+ DriverManager.getConnection(
+ container.getJdbcUrl(), container.getUsername(),
container.getPassword());
+ Statement statement = connection.createStatement()) {
+ statement.execute(createTable);
+ statement.execute(addProjection);
+ statement.execute(createTableWithoutProjections);
+ if (withProjectionSettings) {
+ statement.execute(
+ "CREATE TABLE "
+ + DATABASE
+ + "."
+ + UNSUPPORTED_PROJECTION_TABLE
+ + " (a UInt64, b String, PROJECTION p (SELECT _part_offset
ORDER BY b))"
+ + " ENGINE = MergeTree ORDER BY a");
+ }
+ }
+ }
+
+ private static List<List<String>> readProjectionRows(
+ ClickHouseContainer container, String table, boolean includeSettings)
throws SQLException {
+ String query =
+ "SELECT name, type, query"
+ + (includeSettings ? ", toJSONString(settings) AS settings_json" :
"")
+ + " FROM system.projections WHERE database = ? AND table = ? ORDER
BY name";
+ List<List<String>> rows = new ArrayList<>();
+ try (Connection connection =
+ DriverManager.getConnection(
+ container.getJdbcUrl(), container.getUsername(),
container.getPassword());
+ PreparedStatement statement = connection.prepareStatement(query)) {
+ statement.setString(1, DATABASE);
+ statement.setString(2, table);
+ try (ResultSet resultSet = statement.executeQuery()) {
+ while (resultSet.next()) {
+ rows.add(
+ List.of(
+ resultSet.getString("name"),
+ resultSet.getString("type"),
+ resultSet.getString("query"),
+ includeSettings ? resultSet.getString("settings_json") :
"{}"));
+ }
+ }
+ }
+ return rows;
+ }
+
+ private static String readCreateTable(ClickHouseContainer container, String
table)
+ throws SQLException {
+ String query = "SHOW CREATE TABLE " + DATABASE + "." + table;
+ try (Connection connection =
+ DriverManager.getConnection(
+ container.getJdbcUrl(), container.getUsername(),
container.getPassword());
+ Statement statement = connection.createStatement();
+ ResultSet resultSet = statement.executeQuery(query)) {
+ Assertions.assertTrue(resultSet.next(), query);
+ return resultSet.getString(1);
+ }
+ }
+
+ private static final class HostMappedClickHouseContainer extends
ClickHouseContainer {
+ private HostMappedClickHouseContainer(String image) {
+ super(
+ image,
+ "gravitino-projection-it",
+ Set.of(CLICKHOUSE_PORT),
+ Map.of(),
+ Map.of(),
+ Map.of("CLICKHOUSE_PASSWORD", PASSWORD),
+ Optional.empty());
+ }
+
+ @Override
+ public String getJdbcUrl() {
+ return "jdbc:clickhouse:http://%s:%d?compress=0";
+ .formatted(getContainer().getHost(), getMappedPort(CLICKHOUSE_PORT));
+ }
+ }
+}
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 a1e10b116a..fb915f4136 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
@@ -18,9 +18,11 @@
*/
package org.apache.gravitino.catalog.clickhouse.operations;
+import static
org.apache.gravitino.catalog.clickhouse.ClickHouseTablePropertiesMetadata.CLICKHOUSE_PROJECTIONS_KEY;
import static
org.apache.gravitino.catalog.clickhouse.ClickHouseUtils.getSortOrders;
import java.sql.Connection;
+import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
@@ -69,6 +71,11 @@ public class TestClickHouseTableOperationsUnit {
return getSystemTableMetadata(connection, databaseName, tableName);
}
+ String callGetProjectionProperty(Connection connection, String
databaseName, String tableName)
+ throws Exception {
+ return getProjectionProperty(connection, databaseName, tableName);
+ }
+
Map<String, String> callGetTableProperties(Connection connection, String
tableName)
throws Exception {
return getTableProperties(connection, tableName);
@@ -486,6 +493,383 @@ public class TestClickHouseTableOperationsUnit {
Assertions.assertTrue(metadata.settings().isEmpty());
}
+ @Test
+ void testProjectionPropertyRoundTripAndDeterministicOrdering() {
+ List<ClickHouseTableSqlUtils.ProjectionDefinition> definitions =
+ List.of(
+ new ClickHouseTableSqlUtils.ProjectionDefinition(
+ "z_aggregate",
+ "Aggregate",
+ "SELECT region, count() GROUP BY region",
+ Map.of("index_granularity", "128")),
+ new ClickHouseTableSqlUtils.ProjectionDefinition(
+ "a projection", "Normal", "SELECT concat(name, ')') ORDER BY
name", Map.of()));
+
+ String property =
ClickHouseTableSqlUtils.serializeProjectionDefinitions(definitions);
+ List<ClickHouseTableSqlUtils.ProjectionDefinition> parsed =
+ ClickHouseTableSqlUtils.parseProjectionDefinitions(property);
+
+ Assertions.assertEquals("a projection", parsed.get(0).name());
+ Assertions.assertEquals("z_aggregate", parsed.get(1).name());
+ Assertions.assertEquals(Map.of("index_granularity", "128"),
parsed.get(1).settings());
+ String clauses = ClickHouseTableSqlUtils.formatProjectionClauses(parsed);
+ Assertions.assertTrue(clauses.contains("PROJECTION `a projection`"),
clauses);
+ Assertions.assertTrue(clauses.contains("PROJECTION `z_aggregate`"),
clauses);
+ Assertions.assertTrue(clauses.contains("WITH SETTINGS (index_granularity =
128)"), clauses);
+ Assertions.assertTrue(
+ clauses.indexOf("a projection") < clauses.indexOf("z_aggregate"),
clauses);
+ }
+
+ @Test
+ void testProjectionPropertyParserRejectsMalformedOrUnsafeValues() {
+ Assertions.assertThrows(
+ IllegalArgumentException.class,
+ () -> ClickHouseTableSqlUtils.parseProjectionDefinitions("not json"));
+ Assertions.assertThrows(
+ IllegalArgumentException.class,
+ () ->
+ ClickHouseTableSqlUtils.parseProjectionDefinitions(
+ "[{\"name\":\"p\",\"type\":\"Normal\","
+ + "\"query\":\"SELECT x\"}] trailing content"));
+ Assertions.assertThrows(
+ IllegalArgumentException.class,
+ () ->
+ ClickHouseTableSqlUtils.parseProjectionDefinitions(
+ "[{\"name\":\"p\",\"type\":\"Normal\",\"query\":\"SELECT
x)\"}]"));
+ Assertions.assertThrows(
+ IllegalArgumentException.class,
+ () ->
+ ClickHouseTableSqlUtils.parseProjectionDefinitions(
+ "[{\"name\":\"p\",\"type\":\"Normal\","
+ + "\"query\":\"SELECT x; DROP TABLE t\"}]"));
+ Assertions.assertThrows(
+ IllegalArgumentException.class,
+ () ->
+ ClickHouseTableSqlUtils.parseProjectionDefinitions(
+ "[{\"name\":\"p\",\"type\":\"Normal\",\"query\":\"SELECT x\","
+ + "\"settings\":{\"index_granularity\":\"1; DROP TABLE
t\"}}]"));
+ Assertions.assertThrows(
+ IllegalArgumentException.class,
+ () ->
+ ClickHouseTableSqlUtils.parseProjectionDefinitions(
+ "[{\"name\":\"p\",\"type\":\"Lightweight\",\"query\":\"SELECT
x\"}]"));
+ Assertions.assertThrows(
+ IllegalArgumentException.class,
+ () ->
+ ClickHouseTableSqlUtils.parseProjectionDefinitions(
+ "[{\"name\":\"p\",\"type\":\"Normal\"," + "\"query\":\"SELECT
x WHERE x > 0\"}]"));
+ Assertions.assertThrows(
+ IllegalArgumentException.class,
+ () ->
+ ClickHouseTableSqlUtils.parseProjectionDefinitions(
+ "[{\"name\":\"p\",\"type\":\"Normal\","
+ + "\"query\":\"SELECT _part_offset ORDER BY
_part_offset\"}]"));
+ Assertions.assertThrows(
+ IllegalArgumentException.class,
+ () ->
+ ClickHouseTableSqlUtils.parseProjectionDefinitions(
+ "[{\"name\":\"p\",\"type\":\"Normal\","
+ + "\"query\":\"SELECT `_part_offset` ORDER BY
tuple()\"}]"));
+ }
+
+ @Test
+ void testGenerateCreateTableSqlAppendsProjectionInsideDefinition() {
+ String property =
+ ClickHouseTableSqlUtils.serializeProjectionDefinitions(
+ List.of(
+ new ClickHouseTableSqlUtils.ProjectionDefinition(
+ "by_name", "Normal", "SELECT name ORDER BY name",
Map.of())));
+
+ String sql =
newOps().callGenerateCreateTableSql(Map.of(CLICKHOUSE_PROJECTIONS_KEY,
property));
+
+ Assertions.assertTrue(
+ sql.contains(",\n PROJECTION `by_name` (\n SELECT name ORDER BY
name\n )"), sql);
+ Assertions.assertTrue(sql.indexOf("PROJECTION") < sql.indexOf("\n)"), sql);
+
Assertions.assertFalse(newOps().callGenerateCreateTableSql(Map.of()).contains("PROJECTION"));
+ Assertions.assertThrows(
+ IllegalArgumentException.class,
+ () ->
+ newOps()
+ .callGenerateCreateTableSql(
+ Map.of(
+ CLICKHOUSE_PROJECTIONS_KEY,
+ property,
+ TableConstants.ENGINE,
+ ENGINE.LOG.getValue())));
+ }
+
+ @Test
+ void testGetProjectionPropertyReads24_9SchemaWithoutSettings() throws
Exception {
+ ExposedClickHouseTableOperations ops = newOps();
+ Connection connection = Mockito.mock(Connection.class);
+ PreparedStatement probe = Mockito.mock(PreparedStatement.class);
+ PreparedStatement columns = Mockito.mock(PreparedStatement.class);
+ PreparedStatement projections = Mockito.mock(PreparedStatement.class);
+ ResultSet probeResult = Mockito.mock(ResultSet.class);
+ ResultSet columnResult = Mockito.mock(ResultSet.class);
+ ResultSet projectionResult = Mockito.mock(ResultSet.class);
+ ArgumentCaptor<String> sqlCaptor = ArgumentCaptor.forClass(String.class);
+
+ Mockito.when(connection.prepareStatement(Mockito.anyString()))
+ .thenReturn(probe)
+ .thenReturn(columns)
+ .thenReturn(projections);
+ Mockito.when(probe.executeQuery()).thenReturn(probeResult);
+ Mockito.when(probeResult.next()).thenReturn(false);
+ Mockito.when(columns.executeQuery()).thenReturn(columnResult);
+ Mockito.when(columnResult.next()).thenReturn(true, true, true, false);
+ Mockito.when(columnResult.getString("name")).thenReturn("name", "type",
"query");
+ Mockito.when(projections.executeQuery()).thenReturn(projectionResult);
+ Mockito.when(projectionResult.next()).thenReturn(true, false);
+ Mockito.when(projectionResult.getString("name")).thenReturn("by_name");
+ Mockito.when(projectionResult.getString("type")).thenReturn("Normal");
+ Mockito.when(projectionResult.getString("query")).thenReturn("SELECT name
ORDER BY name");
+
+ String property = ops.callGetProjectionProperty(connection, "db", "table");
+
+ List<ClickHouseTableSqlUtils.ProjectionDefinition> definitions =
+ ClickHouseTableSqlUtils.parseProjectionDefinitions(property);
+ Assertions.assertEquals(1, definitions.size());
+ Assertions.assertEquals("by_name", definitions.get(0).name());
+ Assertions.assertTrue(definitions.get(0).settings().isEmpty());
+ Mockito.verify(connection,
Mockito.times(3)).prepareStatement(sqlCaptor.capture());
+
Assertions.assertFalse(sqlCaptor.getAllValues().get(2).contains("settings"));
+ Mockito.verify(projections).setString(1, "db");
+ Mockito.verify(projections).setString(2, "table");
+ }
+
+ @Test
+ void testGetProjectionPropertyReadsSettingsWhenSystemTableExposesColumn()
throws Exception {
+ ExposedClickHouseTableOperations ops = newOps();
+ Connection connection = Mockito.mock(Connection.class);
+ PreparedStatement probe = Mockito.mock(PreparedStatement.class);
+ PreparedStatement columns = Mockito.mock(PreparedStatement.class);
+ PreparedStatement projections = Mockito.mock(PreparedStatement.class);
+ ResultSet probeResult = Mockito.mock(ResultSet.class);
+ ResultSet columnResult = Mockito.mock(ResultSet.class);
+ ResultSet projectionResult = Mockito.mock(ResultSet.class);
+ ArgumentCaptor<String> sqlCaptor = ArgumentCaptor.forClass(String.class);
+
+ Mockito.when(connection.prepareStatement(Mockito.anyString()))
+ .thenReturn(probe)
+ .thenReturn(columns)
+ .thenReturn(projections);
+ Mockito.when(probe.executeQuery()).thenReturn(probeResult);
+ Mockito.when(probeResult.next()).thenReturn(false);
+ Mockito.when(columns.executeQuery()).thenReturn(columnResult);
+ Mockito.when(columnResult.next()).thenReturn(true, true, true, true,
false);
+ Mockito.when(columnResult.getString("name")).thenReturn("name", "type",
"query", "settings");
+ Mockito.when(projections.executeQuery()).thenReturn(projectionResult);
+ Mockito.when(projectionResult.next()).thenReturn(true, false);
+ Mockito.when(projectionResult.getString("name")).thenReturn("by_name");
+ Mockito.when(projectionResult.getString("type")).thenReturn("Normal");
+ Mockito.when(projectionResult.getString("query")).thenReturn("SELECT name
ORDER BY name");
+ Mockito.when(projectionResult.getString("settings_json"))
+ .thenReturn("{\"index_granularity\":\"128\"}");
+
+ String property = ops.callGetProjectionProperty(connection, "db", "table");
+
+ List<ClickHouseTableSqlUtils.ProjectionDefinition> definitions =
+ ClickHouseTableSqlUtils.parseProjectionDefinitions(property);
+ Assertions.assertEquals(Map.of("index_granularity", "128"),
definitions.get(0).settings());
+ Mockito.verify(connection,
Mockito.times(3)).prepareStatement(sqlCaptor.capture());
+
Assertions.assertTrue(sqlCaptor.getAllValues().get(2).contains("toJSONString(settings)"));
+ }
+
+ @Test
+ void testGetProjectionPropertySkipsAllDefinitionsForUnsupportedQuery()
throws Exception {
+ ExposedClickHouseTableOperations ops = newOps();
+ Connection connection = Mockito.mock(Connection.class);
+ PreparedStatement probe = Mockito.mock(PreparedStatement.class);
+ PreparedStatement columns = Mockito.mock(PreparedStatement.class);
+ PreparedStatement projections = Mockito.mock(PreparedStatement.class);
+ ResultSet probeResult = Mockito.mock(ResultSet.class);
+ ResultSet columnResult = Mockito.mock(ResultSet.class);
+ ResultSet projectionResult = Mockito.mock(ResultSet.class);
+
+ Mockito.when(connection.prepareStatement(Mockito.anyString()))
+ .thenReturn(probe, columns, projections);
+ Mockito.when(probe.executeQuery()).thenReturn(probeResult);
+ Mockito.when(columns.executeQuery()).thenReturn(columnResult);
+ Mockito.when(columnResult.next()).thenReturn(true, true, true, false);
+ Mockito.when(columnResult.getString("name")).thenReturn("name", "type",
"query");
+ Mockito.when(projections.executeQuery()).thenReturn(projectionResult);
+ Mockito.when(projectionResult.next()).thenReturn(true, true, false);
+ Mockito.when(projectionResult.getString("name")).thenReturn("by_name",
"by_offset");
+ Mockito.when(projectionResult.getString("type")).thenReturn("Normal",
"Normal");
+ Mockito.when(projectionResult.getString("query"))
+ .thenReturn("SELECT name ORDER BY name", "SELECT _part_offset ORDER BY
b");
+
+ Assertions.assertNull(ops.callGetProjectionProperty(connection, "db",
"table"));
+ }
+
+ @Test
+ void testGetProjectionPropertySkipsUnsupportedSettings() throws Exception {
+ ExposedClickHouseTableOperations ops = newOps();
+ Connection connection = Mockito.mock(Connection.class);
+ PreparedStatement probe = Mockito.mock(PreparedStatement.class);
+ PreparedStatement columns = Mockito.mock(PreparedStatement.class);
+ PreparedStatement projections = Mockito.mock(PreparedStatement.class);
+ ResultSet probeResult = Mockito.mock(ResultSet.class);
+ ResultSet columnResult = Mockito.mock(ResultSet.class);
+ ResultSet projectionResult = Mockito.mock(ResultSet.class);
+
+ Mockito.when(connection.prepareStatement(Mockito.anyString()))
+ .thenReturn(probe, columns, projections);
+ Mockito.when(probe.executeQuery()).thenReturn(probeResult);
+ Mockito.when(columns.executeQuery()).thenReturn(columnResult);
+ Mockito.when(columnResult.next()).thenReturn(true, true, true, true,
false);
+ Mockito.when(columnResult.getString("name")).thenReturn("name", "type",
"query", "settings");
+ Mockito.when(projections.executeQuery()).thenReturn(projectionResult);
+ Mockito.when(projectionResult.next()).thenReturn(true, false);
+ Mockito.when(projectionResult.getString("settings_json"))
+ .thenReturn("{\"index_granularity\":\"1 + 1\"}");
+
+ Assertions.assertNull(ops.callGetProjectionProperty(connection, "db",
"table"));
+ }
+
+ @Test
+ void testLoadKeepsOtherPropertiesWhenProjectionIsUnsupported() throws
Exception {
+ DataSource dataSource = Mockito.mock(DataSource.class);
+ Connection connection = Mockito.mock(Connection.class);
+ DatabaseMetaData metadata = Mockito.mock(DatabaseMetaData.class);
+ ResultSet tableResult = Mockito.mock(ResultSet.class);
+ ResultSet columnResult = Mockito.mock(ResultSet.class);
+ PreparedStatement systemTable = Mockito.mock(PreparedStatement.class);
+ PreparedStatement probe = Mockito.mock(PreparedStatement.class);
+ PreparedStatement projectionColumns =
Mockito.mock(PreparedStatement.class);
+ PreparedStatement projections = Mockito.mock(PreparedStatement.class);
+ ResultSet systemTableResult = Mockito.mock(ResultSet.class);
+ ResultSet probeResult = Mockito.mock(ResultSet.class);
+ ResultSet projectionColumnResult = Mockito.mock(ResultSet.class);
+ ResultSet projectionResult = Mockito.mock(ResultSet.class);
+
+ Mockito.when(dataSource.getConnection()).thenReturn(connection);
+ Mockito.when(connection.getCatalog()).thenReturn("db");
+ Mockito.when(connection.getMetaData()).thenReturn(metadata);
+ Mockito.when(metadata.getTables("db", null, "t",
null)).thenReturn(tableResult);
+ Mockito.when(metadata.getColumns("db", "db", "t",
null)).thenReturn(columnResult);
+ Mockito.when(tableResult.next()).thenReturn(true, false);
+ Mockito.when(tableResult.getString("TABLE_NAME")).thenReturn("t");
+ Mockito.when(columnResult.next()).thenReturn(true, false);
+ Mockito.when(columnResult.getString("TABLE_NAME")).thenReturn("t");
+ Mockito.when(columnResult.getString("COLUMN_NAME")).thenReturn("a");
+ Mockito.when(columnResult.getString("TYPE_NAME")).thenReturn("Int32");
+ Mockito.when(columnResult.getString("IS_AUTOINCREMENT")).thenReturn("NO");
+
+ ClickHouseTableOperations ops = Mockito.spy(new
ClickHouseTableOperations());
+ ops.initialize(
+ dataSource,
+ new ClickHouseExceptionConverter(),
+ new ClickHouseTypeConverter(),
+ new ClickHouseColumnDefaultValueConverter(),
+ new HashMap<>());
+ Mockito.doReturn(Map.of()).when(ops).getDefaultKinds(connection, "db",
"t");
+ Mockito.doReturn(List.of()).when(ops).getIndexes(connection, "db", "t");
+ Mockito.doReturn("").when(ops).getPartitionKey(connection, "db", "t");
+ Mockito.doReturn(Map.of(TableConstants.ENGINE, "MergeTree"))
+ .when(ops)
+ .getTableProperties(connection, "t");
+
+ Mockito.when(connection.prepareStatement(Mockito.anyString()))
+ .thenReturn(systemTable, probe, projectionColumns, projections);
+ Mockito.when(systemTable.executeQuery()).thenReturn(systemTableResult);
+ Mockito.when(systemTableResult.next()).thenReturn(true, false);
+ Mockito.when(systemTableResult.getString("sorting_key")).thenReturn("a");
+
Mockito.when(systemTableResult.getString("engine_full")).thenReturn("MergeTree
ORDER BY a");
+ Mockito.when(probe.executeQuery()).thenReturn(probeResult);
+
Mockito.when(projectionColumns.executeQuery()).thenReturn(projectionColumnResult);
+ Mockito.when(projectionColumnResult.next()).thenReturn(true, true, true,
false);
+ Mockito.when(projectionColumnResult.getString("name")).thenReturn("name",
"type", "query");
+ Mockito.when(projections.executeQuery()).thenReturn(projectionResult);
+ Mockito.when(projectionResult.next()).thenReturn(true, false);
+ Mockito.when(projectionResult.getString("name")).thenReturn("p");
+ Mockito.when(projectionResult.getString("type")).thenReturn("Normal");
+ Mockito.when(projectionResult.getString("query")).thenReturn("SELECT
_part_offset ORDER BY a");
+
+ JdbcTable table = ops.load("db", "t");
+
+ Assertions.assertEquals(1, table.columns().length);
+ Assertions.assertEquals("MergeTree",
table.properties().get(TableConstants.ENGINE));
+ Assertions.assertEquals("",
table.properties().get(TableConstants.PARTITION_KEY));
+
Assertions.assertFalse(table.properties().containsKey(CLICKHOUSE_PROJECTIONS_KEY));
+ }
+
+ @Test
+ void testGetProjectionPropertyReturnsNullWhenAvailableTableHasNoRows()
throws Exception {
+ ExposedClickHouseTableOperations ops = newOps();
+ Connection connection = Mockito.mock(Connection.class);
+ PreparedStatement probe = Mockito.mock(PreparedStatement.class);
+ PreparedStatement columns = Mockito.mock(PreparedStatement.class);
+ PreparedStatement projections = Mockito.mock(PreparedStatement.class);
+ ResultSet probeResult = Mockito.mock(ResultSet.class);
+ ResultSet columnResult = Mockito.mock(ResultSet.class);
+ ResultSet projectionResult = Mockito.mock(ResultSet.class);
+
+ Mockito.when(connection.prepareStatement(Mockito.anyString()))
+ .thenReturn(probe)
+ .thenReturn(columns)
+ .thenReturn(projections);
+ Mockito.when(probe.executeQuery()).thenReturn(probeResult);
+ Mockito.when(probeResult.next()).thenReturn(false);
+ Mockito.when(columns.executeQuery()).thenReturn(columnResult);
+ Mockito.when(columnResult.next()).thenReturn(true, true, true, false);
+ Mockito.when(columnResult.getString("name")).thenReturn("name", "type",
"query");
+ Mockito.when(projections.executeQuery()).thenReturn(projectionResult);
+ Mockito.when(projectionResult.next()).thenReturn(false);
+
+ Assertions.assertNull(ops.callGetProjectionProperty(connection, "db",
"table"));
+ }
+
+ @Test
+ void testGetProjectionPropertyRejectsMissingRequiredSystemColumn() throws
Exception {
+ ExposedClickHouseTableOperations ops = newOps();
+ Connection connection = Mockito.mock(Connection.class);
+ PreparedStatement probe = Mockito.mock(PreparedStatement.class);
+ PreparedStatement columns = Mockito.mock(PreparedStatement.class);
+ ResultSet probeResult = Mockito.mock(ResultSet.class);
+ ResultSet columnResult = Mockito.mock(ResultSet.class);
+
+ Mockito.when(connection.prepareStatement(Mockito.anyString()))
+ .thenReturn(probe)
+ .thenReturn(columns);
+ Mockito.when(probe.executeQuery()).thenReturn(probeResult);
+ Mockito.when(probeResult.next()).thenReturn(false);
+ Mockito.when(columns.executeQuery()).thenReturn(columnResult);
+ Mockito.when(columnResult.next()).thenReturn(true, true, false);
+ Mockito.when(columnResult.getString("name")).thenReturn("name", "type");
+
+ SQLException exception =
+ Assertions.assertThrows(
+ SQLException.class, () ->
ops.callGetProjectionProperty(connection, "db", "table"));
+
+ Assertions.assertTrue(exception.getMessage().contains("query"));
+ Mockito.verify(connection,
Mockito.times(2)).prepareStatement(Mockito.anyString());
+ }
+
+ @Test
+ void
testGetProjectionPropertyHandlesUnavailableSystemTableAndPropagatesOtherErrors()
+ throws Exception {
+ ExposedClickHouseTableOperations ops = newOps();
+ Connection missingConnection = Mockito.mock(Connection.class);
+ PreparedStatement missingProbe = Mockito.mock(PreparedStatement.class);
+
Mockito.when(missingConnection.prepareStatement(Mockito.anyString())).thenReturn(missingProbe);
+ Mockito.when(missingProbe.executeQuery())
+ .thenThrow(new SQLException("Unknown table system.projections", "",
60));
+ Assertions.assertNull(ops.callGetProjectionProperty(missingConnection,
"db", "table"));
+
+ Connection deniedConnection = Mockito.mock(Connection.class);
+ PreparedStatement deniedProbe = Mockito.mock(PreparedStatement.class);
+
Mockito.when(deniedConnection.prepareStatement(Mockito.anyString())).thenReturn(deniedProbe);
+ SQLException permissionError = new SQLException("Not enough privileges",
"", 497);
+ Mockito.when(deniedProbe.executeQuery()).thenThrow(permissionError);
+ Assertions.assertSame(
+ permissionError,
+ Assertions.assertThrows(
+ SQLException.class,
+ () -> ops.callGetProjectionProperty(deniedConnection, "db",
"table")));
+ }
+
@Test
void testGetSystemTableMetadataParsesSettingsFromEngineFull() throws
Exception {
ExposedClickHouseTableOperations ops = newOps();
diff --git a/docs/jdbc-clickhouse-catalog.md b/docs/jdbc-clickhouse-catalog.md
index 0cf90512a0..abbf4d6b8b 100644
--- a/docs/jdbc-clickhouse-catalog.md
+++ b/docs/jdbc-clickhouse-catalog.md
@@ -177,6 +177,21 @@ See [Manage Catalogs and
Schemas](./manage-catalogs-and-schemas.md#schema-operat
| Column defaults | Supported.
|
| Unsupported | Engine and connector-owned property changes after
creation; mixing table setting changes with schema changes; auto-increment
columns.
|
+Projection round-trip requires `system.projections` (ClickHouse 24.9 or later)
and supports
+safely reconstructable `Normal` and `Aggregate` definitions on these
non-replicated MergeTree-family
+engines: `MergeTree`, `ReplacingMergeTree`, `SummingMergeTree`,
`AggregatingMergeTree`,
+`CollapsingMergeTree`, `VersionedCollapsingMergeTree`, and
`GraphiteMergeTree`. Projection-level
+`WHERE` filters, lightweight `_part_offset` projections, and compact
`PROJECTION ... INDEX` syntax
+are outside this scope. If projection validation rejects any definition or
setting, `loadTable` logs
+a warning and omits the entire `clickhouse.projections` property while loading
the other table
+metadata. Recreating a table from that metadata omits all source projections,
including otherwise
+supported ones.
+
+ClickHouse 24.9 does not expose the system table's optional `settings` column;
when a newer server
+exposes it, safely reconstructable projection settings are preserved.
ClickHouse 24.8 projection
+round-trip is deferred, while ordinary catalog metadata loading remains
supported. Projection data
+is not copied or materialized, and Gravitino does not manage projection `ALTER
TABLE` operations.
+
### Table Column Types
| Gravitino Type | ClickHouse Type |
@@ -213,6 +228,9 @@ Use `String` for unlimited text or `FixedChar(n)` for
fixed-length values.
contain multiple setting operations of the same form, but cannot mix set and
remove operations
or combine settings with schema, comment, or index changes.
- The `engine` value is immutable after creation.
+- `clickhouse.projections` is an immutable structured property for preserving
projection names,
+ types, queries, and safely reconstructable settings during CREATE TABLE. The
`settings` object is
+ empty on ClickHouse 24.9, whose `system.projections` table has no `settings`
column.
:::
:::warning
@@ -242,6 +260,21 @@ If you need Gravitino to manage an existing cluster
database or table, recreate
| `cluster-sharding-key` | Sharding key for `Distributed` engine
(expression allowed; referenced columns must be non-null integral)
| (none) | No\*\* | No |
No |
| `settings.<name>` | ClickHouse engine setting forwarded as `SETTINGS
<name>=<scalar-literal>`; supports settings-only set or remove requests after
creation | (none) | No | No | No
|
| `partition-key` | ClickHouse's canonical native partition
expression (from `system.tables.partition_key`). Read-only; always present on
load, empty string means unpartitioned. | `""` | No | Yes |
Yes |
+| `clickhouse.projections` | Structured projection definitions read from
`system.projections`; used to recreate definitions during CREATE TABLE.
Read-only after creation. | (none) | No | No
| Yes |
+
+The `clickhouse.projections` value is a JSON array. Projection setting values
are strings containing
+supported ClickHouse scalar literals.
+
+```json
+[
+ {
+ "name": "by_event_date",
+ "type": "Normal",
+ "query": "SELECT event_date, id ORDER BY event_date, id",
+ "settings": {}
+ }
+]
+```
\* Required when `on-cluster=true` or `engine=Distributed`.
\*\* Required when `engine=Distributed`.
