This is an automated email from the ASF dual-hosted git repository. github-merge-queue[bot] pushed a commit to branch gh-readonly-queue/dev/pr-13034-8ee7a0b8524d260464840fb619b646742ba70dce in repository https://gitbox.apache.org/repos/asf/seatunnel.git
commit 25f6fe551181dbb2d2ee7d0eac1e236d97587b17 Author: JacobZheng0927 <[email protected]> AuthorDate: Sat Nov 7 03:05:41 2026 +0000 [Fix][Connector-V2] Avoid target aliases in HANA merge statements (#13034) --- docs/en/connectors/sink/Jdbc.md | 4 + docs/zh/connectors/sink/Jdbc.md | 4 + .../internal/dialect/saphana/SapHanaDialect.java | 7 +- .../dialect/saphana/SapHanaDialectTest.java | 45 +++++++ .../connectors/seatunnel/jdbc/JdbcHanaIT.java | 132 +++++++++++++++++++++ 5 files changed, 189 insertions(+), 3 deletions(-) diff --git a/docs/en/connectors/sink/Jdbc.md b/docs/en/connectors/sink/Jdbc.md index 8159dc8f0bc..a451d04c326 100644 --- a/docs/en/connectors/sink/Jdbc.md +++ b/docs/en/connectors/sink/Jdbc.md @@ -12,6 +12,10 @@ If this is your first JDBC Sink job, start with [Choose a write mode](#choose-a- Generated DB2 upserts update matched rows when an incoming field changes, including NULL-to-value and value-to-NULL transitions. Equal values, including two NULLs, do not trigger an unnecessary matched update. +### SAP HANA version compatibility + +Generated upserts support older SAP HANA revisions such as 2.00.033 through alias-free target references. No extra option is required. Tables containing only key columns still insert unmatched rows and leave matched rows unchanged. + ## MySQL temporal values MySQL `DATETIME` maps to SeaTunnel `TIMESTAMP` (`LocalDateTime`). The MySQL JDBC sink binds its local date/time fields directly, preserving fractional seconds supported by the target column even when the worker JVM and JDBC connection time zones differ. MySQL `TIMESTAMP` maps to `TIMESTAMP_TZ` and retains its instant-based handling. diff --git a/docs/zh/connectors/sink/Jdbc.md b/docs/zh/connectors/sink/Jdbc.md index acc1c7d22e3..6440fb9fcf7 100644 --- a/docs/zh/connectors/sink/Jdbc.md +++ b/docs/zh/connectors/sink/Jdbc.md @@ -12,6 +12,10 @@ JDBC Sink 通过数据库厂商提供的 JDBC 驱动写入数据。它支持批 生成的 DB2 upsert 会在传入字段发生变化时更新匹配行,包括 NULL 变为非 NULL 和非 NULL 变为 NULL。相等值(包括两端均为 NULL)不会触发多余的匹配更新。 +### SAP HANA 版本兼容 + +生成的 upsert 通过不带目标表别名的 SQL 兼容 SAP HANA 2.00.033 等较早版本,无需额外配置。全字段均为键的表仍会插入未匹配行,并保持匹配行不变。 + ## MySQL 时间值 MySQL `DATETIME` 映射为 SeaTunnel `TIMESTAMP`(`LocalDateTime`)。MySQL JDBC Sink 直接绑定本地日期和时间字段,即使 Worker JVM 与 JDBC 连接使用不同的时区,也会保留目标列支持的小数秒精度。MySQL `TIMESTAMP` 映射为 `TIMESTAMP_TZ`,仍按时间点处理。 diff --git a/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connectors/seatunnel/jdbc/internal/dialect/saphana/SapHanaDialect.java b/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connectors/seatunnel/jdbc/internal/dialect/saphana/SapHanaDialect.java index 874b3401918..5ec2bb8ae43 100644 --- a/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connectors/seatunnel/jdbc/internal/dialect/saphana/SapHanaDialect.java +++ b/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connectors/seatunnel/jdbc/internal/dialect/saphana/SapHanaDialect.java @@ -82,7 +82,8 @@ public class SapHanaDialect implements JdbcDialect { .map( fieldName -> String.format( - "TARGET.%s=SOURCE.%s", + "%s.%s=SOURCE.%s", + tableIdentifier(database, tableName), quoteIdentifier(fieldName), quoteIdentifier(fieldName))) .collect(Collectors.joining(" AND ")); @@ -91,7 +92,7 @@ public class SapHanaDialect implements JdbcDialect { .map( fieldName -> String.format( - "TARGET.%s=SOURCE.%s", + "%s=SOURCE.%s", quoteIdentifier(fieldName), quoteIdentifier(fieldName))) .collect(Collectors.joining(", ")); @@ -114,7 +115,7 @@ public class SapHanaDialect implements JdbcDialect { String upsertSQL = String.format( - " MERGE INTO %s AS TARGET" + " MERGE INTO %s" + " USING (%s) AS SOURCE" + " ON (%s) " + "%s" diff --git a/seatunnel-connectors-v2/connector-jdbc/src/test/java/org/apache/seatunnel/connectors/seatunnel/jdbc/internal/dialect/saphana/SapHanaDialectTest.java b/seatunnel-connectors-v2/connector-jdbc/src/test/java/org/apache/seatunnel/connectors/seatunnel/jdbc/internal/dialect/saphana/SapHanaDialectTest.java index 480dacfafbb..be09c8ba45c 100644 --- a/seatunnel-connectors-v2/connector-jdbc/src/test/java/org/apache/seatunnel/connectors/seatunnel/jdbc/internal/dialect/saphana/SapHanaDialectTest.java +++ b/seatunnel-connectors-v2/connector-jdbc/src/test/java/org/apache/seatunnel/connectors/seatunnel/jdbc/internal/dialect/saphana/SapHanaDialectTest.java @@ -60,4 +60,49 @@ public class SapHanaDialectTest { + sql + ")"); } + + @Test + void testSingleKeyOnlyUpsertOmitsMatchedUpdate() { + String sql = + new SapHanaDialect() + .getUpsertStatement("S", "T", new String[] {"ID"}, new String[] {"ID"}) + .get(); + + Assertions.assertEquals( + " MERGE INTO \"S\".\"T\" USING (SELECT :ID AS \"ID\" FROM DUMMY) AS SOURCE" + + " ON (\"S\".\"T\".\"ID\"=SOURCE.\"ID\") " + + " WHEN NOT MATCHED THEN INSERT (\"ID\") VALUES (SOURCE.\"ID\")", + sql); + } + + @Test + void testCompositeKeyOnlyUpsertOmitsMatchedUpdate() { + String sql = + new SapHanaDialect() + .getUpsertStatement( + "S", "T", new String[] {"ID", "PART"}, new String[] {"ID", "PART"}) + .get(); + + Assertions.assertEquals( + " MERGE INTO \"S\".\"T\" USING (SELECT :ID AS \"ID\", :PART AS \"PART\" FROM DUMMY) AS SOURCE" + + " ON (\"S\".\"T\".\"ID\"=SOURCE.\"ID\" AND \"S\".\"T\".\"PART\"=SOURCE.\"PART\") " + + " WHEN NOT MATCHED THEN INSERT (\"ID\", \"PART\") VALUES (SOURCE.\"ID\", SOURCE.\"PART\")", + sql); + } + + @Test + void testUpsertWithPayloadPreservesMatchedUpdate() { + String sql = + new SapHanaDialect() + .getUpsertStatement( + "S", "T", new String[] {"ID", "PAYLOAD"}, new String[] {"ID"}) + .get(); + + Assertions.assertEquals( + " MERGE INTO \"S\".\"T\" USING (SELECT :ID AS \"ID\", :PAYLOAD AS \"PAYLOAD\" FROM DUMMY) AS SOURCE" + + " ON (\"S\".\"T\".\"ID\"=SOURCE.\"ID\") " + + " WHEN MATCHED THEN UPDATE SET \"PAYLOAD\"=SOURCE.\"PAYLOAD\" " + + " WHEN NOT MATCHED THEN INSERT (\"ID\", \"PAYLOAD\") VALUES (SOURCE.\"ID\", SOURCE.\"PAYLOAD\")", + sql); + } } diff --git a/seatunnel-e2e/seatunnel-connector-v2-e2e/connector-jdbc-e2e/connector-jdbc-e2e-part-6/src/test/java/org/apache/seatunnel/connectors/seatunnel/jdbc/JdbcHanaIT.java b/seatunnel-e2e/seatunnel-connector-v2-e2e/connector-jdbc-e2e/connector-jdbc-e2e-part-6/src/test/java/org/apache/seatunnel/connectors/seatunnel/jdbc/JdbcHanaIT.java index 96facbf17af..853da27e7d7 100644 --- a/seatunnel-e2e/seatunnel-connector-v2-e2e/connector-jdbc-e2e/connector-jdbc-e2e-part-6/src/test/java/org/apache/seatunnel/connectors/seatunnel/jdbc/JdbcHanaIT.java +++ b/seatunnel-e2e/seatunnel-connector-v2-e2e/connector-jdbc-e2e/connector-jdbc-e2e-part-6/src/test/java/org/apache/seatunnel/connectors/seatunnel/jdbc/JdbcHanaIT.java @@ -22,12 +22,17 @@ import org.apache.seatunnel.shade.com.google.common.collect.Lists; import org.apache.seatunnel.shade.org.apache.commons.lang3.tuple.Pair; import org.apache.seatunnel.api.table.catalog.CatalogTable; +import org.apache.seatunnel.api.table.catalog.PhysicalColumn; import org.apache.seatunnel.api.table.catalog.TablePath; +import org.apache.seatunnel.api.table.catalog.TableSchema; +import org.apache.seatunnel.api.table.type.BasicType; import org.apache.seatunnel.api.table.type.SeaTunnelRow; import org.apache.seatunnel.common.exception.SeaTunnelRuntimeException; import org.apache.seatunnel.common.utils.ExceptionUtils; import org.apache.seatunnel.connectors.seatunnel.jdbc.catalog.utils.CatalogUtils; +import org.apache.seatunnel.connectors.seatunnel.jdbc.internal.dialect.saphana.SapHanaDialect; import org.apache.seatunnel.connectors.seatunnel.jdbc.internal.dialect.saphana.SapHanaTypeMapper; +import org.apache.seatunnel.connectors.seatunnel.jdbc.internal.executor.FieldNamedPreparedStatement; import org.junit.jupiter.api.Assertions; import org.junit.jupiter.api.Test; @@ -39,6 +44,8 @@ import org.testcontainers.utility.DockerLoggerFactory; import lombok.SneakyThrows; import java.sql.Date; +import java.sql.PreparedStatement; +import java.sql.ResultSet; import java.sql.Statement; import java.time.Duration; import java.time.LocalDate; @@ -301,4 +308,129 @@ public class JdbcHanaIT extends AbstractJdbcIT { 1, catalogTable.getTableSchema().getPrimaryKey().getColumnNames().size()); Assertions.assertEquals(25, catalogTable.getTableSchema().getColumns().size()); } + + @Test + void testAliasFreeMergeWithSchemaAndCompositeKey() throws Exception { + SapHanaDialect dialect = new SapHanaDialect(); + String tableName = "MERGE_ALIAS_PAYLOAD"; + String table = dialect.tableIdentifier(DATABASE, tableName); + TableSchema schema = + TableSchema.builder() + .column( + PhysicalColumn.of( + "PAYLOAD", BasicType.STRING_TYPE, 20L, true, null, null)) + .column( + PhysicalColumn.of( + "PART", BasicType.INT_TYPE, 0L, false, null, null)) + .column(PhysicalColumn.of("ID", BasicType.INT_TYPE, 0L, false, null, null)) + .build(); + try (Statement ddl = connection.createStatement()) { + ddl.execute( + "CREATE TABLE " + + table + + " (ID INT, PART INT, PAYLOAD NVARCHAR(20), " + + "KEEP_VALUE INT DEFAULT 7, PRIMARY KEY (ID, PART))"); + try { + ddl.execute("INSERT INTO " + table + " VALUES (90, 90, 'keep', 9)"); + String merge = + dialect.getUpsertStatement( + DATABASE, + tableName, + schema.getFieldNames(), + new String[] {"ID", "PART"}) + .get(); + try (PreparedStatement statement = + FieldNamedPreparedStatement.prepareStatement( + connection, merge, schema.getFieldNames())) { + for (String payload : new String[] {"old", "new", null, "", ""}) { + dialect.getRowConverter() + .toExternal( + schema, + null, + new SeaTunnelRow(new Object[] {payload, 2, 1}), + statement); + statement.executeUpdate(); + try (ResultSet row = + ddl.executeQuery( + "SELECT PAYLOAD, KEEP_VALUE FROM " + + table + + " WHERE ID = 1 AND PART = 2")) { + Assertions.assertTrue(row.next()); + Assertions.assertEquals(payload, row.getString(1)); + Assertions.assertEquals(7, row.getInt(2)); + Assertions.assertFalse(row.next()); + } + } + } + try (ResultSet row = + ddl.executeQuery( + "SELECT ID, PART, PAYLOAD, KEEP_VALUE FROM " + + table + + " ORDER BY ID")) { + Assertions.assertTrue(row.next()); + Assertions.assertEquals(1, row.getInt(1)); + Assertions.assertTrue(row.next()); + Assertions.assertEquals(90, row.getInt(1)); + Assertions.assertEquals(90, row.getInt(2)); + Assertions.assertEquals("keep", row.getString(3)); + Assertions.assertEquals(9, row.getInt(4)); + Assertions.assertFalse(row.next()); + } + } finally { + ddl.execute("DROP TABLE " + table); + connection.commit(); + } + } + } + + @Test + void testAliasFreeMergeForSingleAndCompositeAllKeyTables() throws Exception { + SapHanaDialect dialect = new SapHanaDialect(); + for (boolean composite : new boolean[] {false, true}) { + String tableName = composite ? "MERGE_ALIAS_COMPOSITE" : "MERGE_ALIAS_SINGLE"; + String table = dialect.tableIdentifier(DATABASE, tableName); + String[] fields = composite ? new String[] {"ID", "PART"} : new String[] {"ID"}; + try (Statement ddl = connection.createStatement()) { + ddl.execute( + "CREATE TABLE " + + table + + (composite + ? " (ID INT, PART INT, PRIMARY KEY (ID, PART))" + : " (ID INT PRIMARY KEY)")); + try { + String merge = + dialect.getUpsertStatement(DATABASE, tableName, fields, fields).get(); + try (PreparedStatement statement = + FieldNamedPreparedStatement.prepareStatement( + connection, merge, fields)) { + for (int value : new int[] {1, 2, 1}) { + statement.setInt(1, composite ? 1 : value); + if (composite) { + statement.setInt(2, value); + } + statement.executeUpdate(); + } + } + try (ResultSet rows = + ddl.executeQuery( + "SELECT * FROM " + + table + + " ORDER BY " + + (composite ? "PART" : "ID"))) { + for (int value : new int[] {1, 2}) { + Assertions.assertTrue(rows.next()); + Assertions.assertEquals(composite ? 1 : value, rows.getInt(1)); + if (composite) { + Assertions.assertEquals(value, rows.getInt(2)); + } + } + Assertions.assertFalse(rows.next()); + } + } finally { + ddl.execute("DROP TABLE " + table); + connection.commit(); + } + } + } + } }
