LuciferYang commented on code in PR #14643: URL: https://github.com/apache/gravitino/pull/14643#discussion_r4505391419
########## spark-connector/spark-common/src/main/java/org/apache/gravitino/spark/connector/jdbc/SparkJdbcPushDownPolicy.java: ########## @@ -0,0 +1,267 @@ +/* + * 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.spark.connector.jdbc; + +import com.google.common.collect.ImmutableSet; +import java.time.LocalDateTime; +import java.util.HashSet; +import java.util.Locale; +import java.util.Set; +import org.apache.gravitino.rel.Column; +import org.apache.gravitino.rel.types.Type; +import org.apache.gravitino.rel.types.Types; +import org.apache.spark.sql.connector.expressions.Cast; +import org.apache.spark.sql.connector.expressions.Expression; +import org.apache.spark.sql.connector.expressions.Extract; +import org.apache.spark.sql.connector.expressions.Literal; +import org.apache.spark.sql.connector.expressions.NamedReference; +import org.apache.spark.sql.jdbc.JdbcDialect; +import org.apache.spark.sql.types.ArrayType; +import org.apache.spark.sql.types.BinaryType; +import org.apache.spark.sql.types.CharType; +import org.apache.spark.sql.types.DataType; +import org.apache.spark.sql.types.MapType; +import org.apache.spark.sql.types.StringType; +import org.apache.spark.sql.types.StructField; +import org.apache.spark.sql.types.StructType; +import org.apache.spark.sql.types.TimestampNTZType; +import org.apache.spark.sql.types.TimestampType; +import org.apache.spark.sql.types.VarcharType; + +/** + * Decides which expressions a JDBC source may evaluate without changing Spark query results. + * + * <p>An expression stays in Spark when it has a known difference from Spark. These are: + * + * <ul> + * <li>string-like or binary columns, also inside arrays, maps, and structs; + * <li>string or binary literals and casts; + * <li>casts to a timestamp type, and casts or date parts of a Spark {@code TIMESTAMP} column; + * <li>date parts other than year, quarter, month, day, hour, and minute; + * <li>{@code TIMESTAMP_NTZ} literals the dialect cannot write in SQL. + * </ul> + * + * <p>Aggregates also stay in Spark for timestamp columns whose reader type differs from the type + * Spark sees, because the reader types the pushed aggregate result. + */ +class SparkJdbcPushDownPolicy { + + private static final Set<String> PUSHABLE_EXTRACT_FIELDS = + ImmutableSet.of("YEAR", "QUARTER", "MONTH", "DAY", "HOUR", "MINUTE"); + + private final Set<String> sensitiveColumns; + private final Set<String> readerTypeMismatchColumns; + private final Set<String> zonedTimestampColumns; + private final boolean ntzLiteralSupported; + + SparkJdbcPushDownPolicy( + Column[] columns, StructType readerSchema, StructType sparkSchema, JdbcDialect dialect) { + this.sensitiveColumns = new HashSet<>(); + for (Column column : columns) { + if (isSensitive(column.dataType())) { + sensitiveColumns.add(normalize(column.name())); + } + } + this.readerTypeMismatchColumns = new HashSet<>(); + this.zonedTimestampColumns = new HashSet<>(); + for (StructField field : sparkSchema.fields()) { + if (field.dataType() instanceof TimestampType) { + zonedTimestampColumns.add(normalize(field.name())); + } + DataType readerType = fieldType(readerSchema, field.name()); + // Only timestamp differences change pushed aggregate results; Spark widens numeric results + // safely. catalogString ignores nullability, which does not change pushed results. + if (readerType == null + || (!readerType.catalogString().equals(field.dataType().catalogString()) + && (containsTimestamp(readerType) || containsTimestamp(field.dataType())))) { + readerTypeMismatchColumns.add(normalize(field.name())); + } + } + // Spark 3.5 leaves LocalDateTime literals unquoted, while Spark 4 quotes them. Check the + // dialect itself instead of a Spark version or provider name. + this.ntzLiteralSupported = + dialect.compileValue(LocalDateTime.of(2000, 1, 1, 0, 0)) instanceof String; + } + + /** + * Returns whether a predicate, sort, or group-by expression may be evaluated by the source. + * + * @param expression the expression to check + * @return true if pushing it keeps Spark results + */ + boolean canPush(Expression expression) { + if (expression instanceof NamedReference) { + return !sensitiveColumns.contains(columnName((NamedReference) expression)); Review Comment: String `=`, `IN` and `IS NULL` now stay in Spark on every JDBC provider, since `canPush` rejects any reference to a string column. On a 1M-row PostgreSQL table, the scan for `WHERE s = 'value-500'` has no WHERE clause and takes 332-427 ms; base pushes it (73-96 ms). `count(*) WHERE s IS NULL` loses both pushdowns. My earlier suggestion to reject every string expression was too broad. On MySQL, equality can't just be pushed again: base returns a row for `s = 'V500'` because of the case-insensitive collation. I tried pushing a top-level `col = 'lit'` or `col IN (...)` as a pre-filter and also returning it from `pushPredicates` so Spark re-checks it, as Trino does for MySQL. On 3.5.3 and 4.0.3 the filter reached MySQL, the `'V500'`, `'a'` and `IN` results matched Spark, and LIMIT stayed in Spark. Don't push it when negated (`NOT`, `<>`, `NOT IN`): MySQL drops `v500` for `NOT (s = 'V500')`, and Spark can't add it back. Keep literals containing `\` in Spark, since Spark's MySQL dialect only doubles quotes, so `'C:\temp'` matched nothing and `'abc\'` was a syntax error. `IS NULL`/`IS NOT NULL` on the column itself can be pushed as is. ########## spark-connector/spark-common/src/main/java/org/apache/gravitino/spark/connector/jdbc/SparkJdbcTable.java: ########## @@ -58,6 +67,24 @@ public SparkJdbcTable( propertiesConverter, sparkTransformConverter, sparkTypeConverter); + this.sparkSchema = alignTimestampArrays(gravitinoTableInfoHelper.schema(), jdbcTable.schema()); + this.pushDownPolicy = + new SparkJdbcPushDownPolicy( + gravitinoTable.columns(), + jdbcTable.schema(), + sparkSchema, + JdbcDialects.get(jdbcTable.jdbcOptions().url())); + } + + /** + * Creates a scan builder that keeps unsafe expressions in Spark. + * + * @param options the scan options + * @return the JDBC scan builder + */ + @Override + public JDBCScanBuilder newScanBuilder(CaseInsensitiveStringMap options) { + return new SparkJdbcScanBuilder(super.newScanBuilder(options), pushDownPolicy); Review Comment: A per-read `preferTimestampNTZ` still reaches the reader, so the resolved catalog value no longer drives both sides. `JDBCTable.newScanBuilder` merges scan options into the reader options, while the aggregate rule in `SparkJdbcPushDownPolicy` is computed once from the table's reader schema. With the catalog option off, `spark.read.option("preferTimestampNTZ", "true").table(...).agg(max("ts"))` returned `2024-01-20 00:00:00` on 4.0.3 (JVM in Asia/Shanghai, session in UTC), while the same read shows that row as `2024-01-19 16:00:00`. With the option on and a per-read `false`, it is off by 8 hours the other way. On 3.5.3 both directions fail planning with `UnresolvedException`. Base has the same problem, so this isn't a regression, but it breaks the "one value drives both" rule and the doc line saying these aggregates stay in Spark. Passing `super.newScanBuilder` a copy of `options` without any spelling of `preferTimestampNTZ` would close it. It has to be a copy: `asCaseSensitiveMap()` is unmodifiable, and `keySet().removeIf` only changes the lower-cased view, not the map `JDBCTable` reads. ########## spark-connector/spark-common/src/main/java/org/apache/gravitino/spark/connector/jdbc/SparkJdbcTable.java: ########## @@ -31,14 +34,26 @@ import org.apache.spark.sql.connector.catalog.Identifier; import org.apache.spark.sql.connector.write.LogicalWriteInfo; import org.apache.spark.sql.connector.write.WriteBuilder; +import org.apache.spark.sql.execution.datasources.jdbc.JDBCOptions; +import org.apache.spark.sql.execution.datasources.v2.jdbc.JDBCScanBuilder; import org.apache.spark.sql.execution.datasources.v2.jdbc.JDBCTable; import org.apache.spark.sql.execution.datasources.v2.jdbc.JDBCTableCatalog; +import org.apache.spark.sql.jdbc.JdbcDialects; +import org.apache.spark.sql.types.DataTypes; +import org.apache.spark.sql.types.StructField; import org.apache.spark.sql.types.StructType; +import org.apache.spark.sql.util.CaseInsensitiveStringMap; +import scala.collection.JavaConverters; public class SparkJdbcTable extends JDBCTable { + private static final String[] UNSAFE_OPTIONS = { + "pushDownPredicate", "pushDownAggregate", "pushDownLimit", "pushDownOffset" Review Comment: Thanks, confirmed through the production catalog classes on 3.5.3, 4.0.3 and 3.5.3 built with Scala 2.13: on a table with string columns, `id = 1`, `count(*)`, `LIMIT 10` and `ORDER BY id LIMIT 3` are pushed again, and a string filter keeps LIMIT in Spark. String equality now stays in Spark as well, which comes from my earlier suggestion being too broad; I left a separate comment on `SparkJdbcPushDownPolicy` line 111. ########## spark-connector/spark-common/src/main/java/org/apache/gravitino/spark/connector/jdbc/SparkJdbcTypeConverter.java: ########## @@ -44,7 +64,7 @@ public DataType toSparkType(Type gravitinoType) { "Reading type {} as a string, Gravitino cannot represent it", ((Types.ExternalType) gravitinoType).catalogString()); return DataTypes.StringType; - } else if (gravitinoType instanceof Types.TimestampType) { + } else if (gravitinoType instanceof Types.TimestampType && !useTimestampNtz) { Review Comment: Confirmed: with the option off, `infinity` reads as 9999-12-31 on 3.5.3 and 4.0.3, same as base, and the docs cover the opt-in case. ########## spark-connector/spark-common/src/main/java/org/apache/gravitino/spark/connector/jdbc/SparkJdbcTypeConverter.java: ########## @@ -44,7 +64,7 @@ public DataType toSparkType(Type gravitinoType) { "Reading type {} as a string, Gravitino cannot represent it", ((Types.ExternalType) gravitinoType).catalogString()); return DataTypes.StringType; - } else if (gravitinoType instanceof Types.TimestampType) { + } else if (gravitinoType instanceof Types.TimestampType && !useTimestampNtz) { Review Comment: Confirmed: with the option on, `timestamp[]` reads on both versions, and on 4.0.3 the value is the same in UTC and America/Los_Angeles sessions. ########## docs/spark-connector/spark-connector.md: ########## @@ -152,3 +152,24 @@ these columns are rejected before JDBC execution, including writes of NULL value INSERT OVERWRITE. The error identifies the unsupported column. Spark binds these columns as VARCHAR, which does not preserve the PostgreSQL target type. Use a native PostgreSQL writer for these tables. Other PostgreSQL column types retain their existing write behavior. + +### JDBC query consistency + +Some JDBC tables are read without pushdown. For these tables, Spark evaluates predicates, +aggregates, Top-N, limits, and offsets itself. This avoids result differences caused by the +column types below. A table is affected when it has a column of one of these types: + +- String, char, varchar, binary, UUID, or an external type. The source database can compare + strings with a different collation, and Spark cannot always write binary literals in SQL. +- A column that maps to Spark `TIMESTAMP_NTZ` (today, only a PostgreSQL timestamp without time + zone), when the JDBC dialect cannot write `TIMESTAMP_NTZ` literals in SQL. This is the case + in Spark 3.5, not in Spark 4.0. + +The rule applies to the whole table. Filters on numeric columns are not pushed down either, so +Spark can read more data from the source. You cannot turn pushdown back on with the +`pushDownPredicate`, `pushDownAggregate`, `pushDownLimit`, or `pushDownOffset` options. Other +tables keep their normal pushdown settings. + +For PostgreSQL catalogs, a timestamp without time zone maps to Spark `TIMESTAMP_NTZ`. The Review Comment: Confirmed: with the option off, `partitionColumn` reads and the stored write values match base, and the opt-in behavior is documented. ########## spark-connector/spark-common/src/main/java/org/apache/gravitino/spark/connector/jdbc/SparkJdbcTable.java: ########## @@ -99,4 +145,51 @@ public StructType schema() { public Map<String, String> properties() { return gravitinoTableInfoHelper.properties(); } + + private static boolean hasUnsafePushDown(JDBCTable table, Table source) { + for (Column column : source.columns()) { + Type type = column.dataType(); + if (type instanceof Types.StringType + || type instanceof Types.VarCharType + || type instanceof Types.FixedCharType + || type instanceof Types.BinaryType + || type instanceof Types.ExternalType + || type instanceof Types.UUIDType) { + return true; + } + } + for (StructField field : table.schema().fields()) { Review Comment: Confirmed on 3.5.3: with the option on, `WHERE id = 1` on an `int` + `timestamptz` table is pushed, and `max(tz)` stays in Spark with the right value. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
