yuqi1129 commented on code in PR #14643:
URL: https://github.com/apache/gravitino/pull/14643#discussion_r4499876734
##########
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:
Fixed in 64d3a4e8b, with follow-ups in 5203e8c79, 0bd290403, and 46c62e900.
`SparkJdbcTable.newScanBuilder` now returns `SparkJdbcScanBuilder`, so pushdown
is decided per expression instead of per table:
- `pushPredicates` keeps only the unsafe predicates in Spark.
- `pushAggregation`, `supportCompletePushDown`, and `pushTopN` reject
expressions that involve string or binary columns (also inside arrays), string
or binary literals or casts, `TIMESTAMP_NTZ` literals the dialect cannot write,
time-zone-dependent casts and date parts, or date parts whose source semantics
differ (such as `second()` or `weekofyear()`).
- LIMIT and OFFSET are unchanged.
- The user's `pushDown*` options are no longer overridden.
`TestSparkJdbcReadCorrectness` now checks the plans on a PostgreSQL 16 table
with string columns. `id = 1`, `LIMIT 2`, `count(*)`, and `ORDER BY id LIMIT 2`
are pushed. The string filter, string Top-N, `min/max(s)`, `ORDER BY tags`, and
`max(tags)` stay in Spark. With a string filter in Spark, LIMIT and `count(*)`
also stay in Spark. Thanks for the detailed reproduction.
##########
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:
Fixed in 64d3a4e8b. As you suggested, the NTZ mapping is now an opt-in
catalog option: `preferTimestampNTZ`, set as a Spark catalog option or as the
Gravitino property `spark.bypass.preferTimestampNTZ`. It is off by default, so
the default path keeps the old `TimestampType` getter and the `infinity` clamp.
The docs list the `long overflow` on `infinity`/`-infinity` as a known
limitation when the option is on. I did not register an extra dialect, because
of the `AggregatedDialect` and JVM-wide registry problems you found. The clamp
belongs in Spark's `convertJavaTimestampToTimestampNTZ` upstream.
##########
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:
Fixed in 64d3a4e8b. `SparkJdbcTable` now uses the reader's element type for
timestamp arrays (`alignTimestampArrays`): `TIMESTAMP` on Spark 3.5 and
`TIMESTAMP_NTZ` on 4.0, while top-level columns keep the converter's type.
`TestSparkJdbcReadCorrectness` now reads a PostgreSQL `timestamp[]` column on
both versions, and
`TestSparkJdbcTableWriteValidation.testTimestampArraysUseTheReaderElementType`
covers the mapping. The docs say that on 3.5, array elements stay `TIMESTAMP`.
##########
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:
Fixed in 64d3a4e8b. NTZ is now opt-in and off by default, so neither
behavior changes for existing jobs. For users who turn it on, the docs now list:
- writes use the Spark session time zone instead of the JVM time zone;
- a timestamp column cannot be `partitionColumn`;
- `infinity` reads fail with `long overflow`;
- on Spark 3.5, timestamp arrays stay `TIMESTAMP`.
The PR description repeats these items for the 1.3.x release notes.
##########
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:
Fixed in 64d3a4e8b, narrowed in 5203e8c79. The NTZ-literal check is now per
expression: only predicates with a `TIMESTAMP_NTZ` literal the dialect cannot
write stay in Spark, so `WHERE id = 1` on an `int` + `timestamptz` table is
pushed again on 3.5.3. Following your suggestion, aggregates stay in Spark on
columns whose reader type differs from the Spark-visible type. 5203e8c79 limits
this rule to timestamp types, because numeric widening, such as MySQL
`SMALLINT` read as `int` on 3.5, keeps correct pushed results.
`testZonedTimestampTableKeepsPushdownAndAggregateTypes` covers a `timestamptz`
table on 3.5 and 4.0: `id = 1` is pushed, and `max(tz)` is correct in three
session time zones.
##########
spark-connector/spark-common/src/main/java/org/apache/gravitino/spark/connector/jdbc/GravitinoJdbcCatalog.java:
##########
@@ -53,6 +53,9 @@ protected TableCatalog createAndInitSparkCatalog(
Map<String, String> all =
getPropertiesConverter().toSparkCatalogProperties(options, properties);
applyJdbcCredential(gravitinoCatalogClient, all);
+ if (useTimestampNtz()) {
+ all.put("preferTimestampNTZ", "true");
Review Comment:
Fixed in 64d3a4e8b. One value now drives both the reader flag and the
converter. `GravitinoJdbcCatalog` resolves `preferTimestampNTZ` from the Spark
catalog option first, then from the Gravitino property
`spark.bypass.preferTimestampNTZ` (5203e8c79 aligned the prefix with
`PropertiesConverter`). It removes every spelling of the key before writing the
resolved value, and 0bd290403 rejects values other than `true`/`false`.
`TestGravitinoJdbcCatalog` covers key spellings, precedence, and invalid
values. `testNtzOptionKeepsOnlyOneSpelling` checks the key cleanup directly, so
it does not depend on the Scala map order.
##########
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
Review Comment:
Fixed in 64d3a4e8b. The check now recurses into list, map, and struct
element types, so a `text[]` column is treated like a `text` column.
`TestSparkJdbcPushDownPolicy` covers the `tags` array column, and
`TestSparkJdbcReadCorrectness` reproduces your case with `text[] COLLATE
"en-US-x-icu"`: `ORDER BY tags LIMIT 1` returns id 2, and `max(tags)` returns
`[a]`, as Spark does.
--
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]