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-12828-6191598c2995bf486eb7c8c725978632db288c5a in repository https://gitbox.apache.org/repos/asf/seatunnel.git
commit df59bb4cd1c1fef7f2c38ade6456b8c0c2658da8 Author: Goutam Adwant <[email protected]> AuthorDate: Fri Nov 6 02:58:07 2026 +0000 [Feature][Connector-V2] Add CockroachDB JDBC dialect (#12828) Signed-off-by: Goutam Adwant <[email protected]> --- docs/en/connectors/sink/Jdbc.md | 26 +- docs/en/connectors/source/Jdbc.md | 28 +- docs/zh/connectors/sink/Jdbc.md | 26 +- docs/zh/connectors/source/Jdbc.md | 28 +- .../jdbc/internal/dialect/DatabaseIdentifier.java | 1 + .../jdbc/internal/dialect/JdbcDialect.java | 10 + .../dialect/cockroachdb/CockroachDBDialect.java | 174 ++++++++++ .../cockroachdb/CockroachDBDialectFactory.java | 50 +++ .../cockroachdb/CockroachDBRowConverter.java | 42 +++ .../dialect/psql/PostgresJdbcRowConverter.java | 11 +- .../jdbc/sink/AbstractJdbcSinkWriter.java | 6 + .../connectors/seatunnel/jdbc/sink/JdbcSink.java | 4 + .../seatunnel/jdbc/sink/JdbcSinkFactory.java | 1 + .../internal/dialect/JdbcDialectLoaderTest.java | 12 + .../cockroachdb/CockroachDBDialectTest.java | 147 +++++++++ .../dialect/psql/PostgresJdbcRowConverterTest.java | 35 ++ .../seatunnel/jdbc/sink/JdbcSinkFactoryTest.java | 42 +++ .../sink/JdbcSinkWriterSchemaChangeSafetyTest.java | 115 +++++++ .../seatunnel/jdbc/JdbcCockroachDBDialectIT.java | 367 +++++++++++++++++++++ .../seatunnel/jdbc/JdbcCockroachDBIT.java | 136 ++++++++ .../resources/jdbc_cockroachdb_source_sink.conf | 51 +++ 21 files changed, 1306 insertions(+), 6 deletions(-) diff --git a/docs/en/connectors/sink/Jdbc.md b/docs/en/connectors/sink/Jdbc.md index 7f81690643..8159dc8f0b 100644 --- a/docs/en/connectors/sink/Jdbc.md +++ b/docs/en/connectors/sink/Jdbc.md @@ -45,6 +45,30 @@ Place the JDBC driver in `${SEATUNNEL_HOME}/lib/` on every SeaTunnel node, then See the [driver reference](#driver-reference) for common driver class names and download locations. +## CockroachDB + +Use `driver = "org.postgresql.Driver"`, a PostgreSQL JDBC URL, and explicit `dialect = "CockroachDB"`. Create the target table before starting the job; this dialect does not provide a catalog for automatic table creation or schema save modes. + +```hocon +Jdbc { + url = "jdbc:postgresql://cockroachdb:26257/app?sslmode=verify-full" + driver = "org.postgresql.Driver" + dialect = "CockroachDB" + username = "seatunnel" + password = ${DB_PASSWORD} + database = "app" + table = "public.orders" + generate_sink_sql = true + primary_keys = ["id"] +} +``` + +Configure the driver's trusted root certificate for your cluster. Do not disable TLS in production. + +Generated upserts use CockroachDB's native `INSERT ... ON CONFLICT (keys) DO UPDATE`, retaining the configured `primary_keys` conflict target. This also works with a matching secondary unique constraint. The shorter `UPSERT` statement is intentionally not used: it always selects the physical primary key and could change the meaning of a configured secondary key. Updates modify only incoming non-key columns; omitted columns keep their existing values. Key-only rows use `DO NOTHING`. Repla [...] + +The first scope is row inserts, upserts, updates, and deletes against existing tables. There is no CockroachDB catalog, automatic DDL/schema evolution, XA exactly-once support, or new transaction-retry policy. Schema-change events are rejected before flushing buffered rows or executing DDL. Only `data_save_mode = APPEND_DATA` is supported; `schema_save_mode` may be left at its default or set to `IGNORE`, but neither mode creates or checks the target table. Other save modes are rejected i [...] + ## Choose a write mode JDBC Sink has two mutually exclusive write modes. Choose one before configuring the remaining options. @@ -272,7 +296,7 @@ If one dialect not supported by SeaTunnel, it will use the default dialect `Gene | Vertica | OceanBase | XUGU | | IRIS | Inceptor | Highgo | | DSQL | | | -| YashanDB | | | +| YashanDB | CockroachDB | | ### database [string] The target database or catalog used in generated SQL mode. This option is required when `generate_sink_sql = true` and must not be combined with `query`. diff --git a/docs/en/connectors/source/Jdbc.md b/docs/en/connectors/source/Jdbc.md index f4c20f3150..929aea532d 100644 --- a/docs/en/connectors/source/Jdbc.md +++ b/docs/en/connectors/source/Jdbc.md @@ -302,11 +302,37 @@ If one dialect not supported by SeaTunnel, it will use the default dialect `Gene | SqlServer | Tablestore | Teradata | | Vertica | OceanBase | XUGU | | IRIS | Inceptor | Highgo | -| YashanDB | DSQL | | +| YashanDB | DSQL | CockroachDB | Dameng `NCHAR` source columns are mapped to SeaTunnel `STRING`. Dameng `TIMESTAMP WITH LOCAL TIME ZONE` source columns are mapped to `TIMESTAMP_TZ`, and `INTERVAL` source columns are mapped to `STRING`. +### CockroachDB + +Use the existing PostgreSQL JDBC driver with `dialect = "CockroachDB"`. The dialect must be explicit: a `jdbc:postgresql:` URL without it continues to select PostgreSQL. + +```hocon +Jdbc { + url = "jdbc:postgresql://cockroachdb:26257/app?sslmode=verify-full" + driver = "org.postgresql.Driver" + dialect = "CockroachDB" + username = "seatunnel" + password = ${DB_PASSWORD} + query = "SELECT id, customer, amount FROM public.orders" + partition_column = "customer" + partition_num = 4 + split.string-strategy = "hash" +} +``` + +Configure the driver's trusted root certificate for your cluster. `sslmode=disable` is only appropriate for disposable insecure local test clusters. + +CockroachDB does not implement PostgreSQL's `HASHTEXT` function. This dialect uses `FNV32` for hash partitions, including STRING and UUID keys, and places null keys in one partition so they are not omitted. It uses `COUNT(*)` when the splitter needs a row count because CockroachDB's `pg_class.reltuples` is null; this can require a scan of the input table or query. Set `enable_concurrent_read = false` to avoid split analysis when one reader is sufficient. + +String conversion uses the discovered schema's native type information instead of querying result-set type metadata while a cursor is open. The supported reads do not require the `multiple_active_portals_enabled` preview setting. An explicit schema without native type information treats STRING fields as ordinary strings. + +The initial scope is bounded reads from existing tables using `query` or explicit `table_path`, with the PostgreSQL driver's compatible type mapping. CockroachDB 24.3 tests cover INT8, STRING, DECIMAL, BOOL, BYTES, UUID, and null values. Regex catalog discovery, CDC, and range/auto string splitting are not included. Numeric splitting and hash string splitting use the existing JDBC splitter lifecycle; no new driver or connector plugin is required. + ## Parallel Reader Parallelism determines how many readers can run at the same time; the split configuration determines how many independent splits are available. diff --git a/docs/zh/connectors/sink/Jdbc.md b/docs/zh/connectors/sink/Jdbc.md index 9910178e15..acc1c7d22e 100644 --- a/docs/zh/connectors/sink/Jdbc.md +++ b/docs/zh/connectors/sink/Jdbc.md @@ -45,6 +45,30 @@ sh bin/install-plugin.sh 常用驱动类名和下载地址见[驱动参考](#驱动参考)。 +## CockroachDB + +使用 `driver = "org.postgresql.Driver"`、PostgreSQL JDBC URL,并显式配置 `dialect = "CockroachDB"`。作业启动前必须创建目标表;本方言不提供用于自动建表或 schema save mode 的 Catalog。 + +```hocon +Jdbc { + url = "jdbc:postgresql://cockroachdb:26257/app?sslmode=verify-full" + driver = "org.postgresql.Driver" + dialect = "CockroachDB" + username = "seatunnel" + password = ${DB_PASSWORD} + database = "app" + table = "public.orders" + generate_sink_sql = true + primary_keys = ["id"] +} +``` + +请为驱动配置集群的受信任根证书。生产环境不要禁用 TLS。 + +生成的 upsert 使用 CockroachDB 原生 `INSERT ... ON CONFLICT (keys) DO UPDATE`,保留 `primary_keys` 指定的冲突目标,也支持匹配的非主键唯一约束。不使用简写 `UPSERT`,因为它始终选择数据库实际主键,可能改变非主键唯一键配置的语义。更新仅修改输入中的非键列,未输入的列保留已有值;仅包含键的记录使用 `DO NOTHING`。重复写入相同的非空键值不会新增记录;可为空的唯一键包含 `NULL` 时不会产生冲突,可能新增记录。配置的键必须匹配目标表的主键或唯一约束。 + +首期支持对现有表进行插入、upsert、更新和删除,不包含 CockroachDB Catalog、自动 DDL/模式演变、XA exactly-once 或新的事务重试策略。模式变更事件会在刷新缓冲数据或执行 DDL 前被拒绝。仅支持 `data_save_mode = APPEND_DATA`;`schema_save_mode` 可以保持默认值或设为 `IGNORE`,但这两种配置都不会创建或检查目标表。其他 save mode 会被拒绝,而不会被静默忽略。`table_options` 会被拒绝,避免误接受 PostgreSQL 专有设置。现有 PostgreSQL 作业和 URL 匹配规则不变。 + ## 选择写入模式 JDBC Sink 有两种互斥的写入模式。请先确定模式,再配置其余参数。 @@ -271,7 +295,7 @@ Postgres 9.5及以下版本,请设置为 `postgresLow` 来支持 CDC | Vertica | OceanBase | XUGU | | IRIS | Inceptor | Highgo | | DSQL | | | -| YashanDB | | | +| YashanDB | CockroachDB | | ### database [string] diff --git a/docs/zh/connectors/source/Jdbc.md b/docs/zh/connectors/source/Jdbc.md index 7d06080bb5..7a47206096 100644 --- a/docs/zh/connectors/source/Jdbc.md +++ b/docs/zh/connectors/source/Jdbc.md @@ -298,11 +298,37 @@ numeric_special_value_as_null = true | SqlServer | Tablestore | Teradata | | Vertica | OceanBase | XUGU | | IRIS | Inceptor | Highgo | -| YashanDB | DSQL | | +| YashanDB | DSQL | CockroachDB | 达梦 `NCHAR` 源字段会映射为 SeaTunnel `STRING`。 达梦 `TIMESTAMP WITH LOCAL TIME ZONE` 源字段会映射为 `TIMESTAMP_TZ`,`INTERVAL` 源字段会映射为 `STRING`。 +### CockroachDB + +使用现有 PostgreSQL JDBC 驱动,并显式配置 `dialect = "CockroachDB"`。未配置该选项时,`jdbc:postgresql:` URL 仍然选择 PostgreSQL 方言,不影响现有作业。 + +```hocon +Jdbc { + url = "jdbc:postgresql://cockroachdb:26257/app?sslmode=verify-full" + driver = "org.postgresql.Driver" + dialect = "CockroachDB" + username = "seatunnel" + password = ${DB_PASSWORD} + query = "SELECT id, customer, amount FROM public.orders" + partition_column = "customer" + partition_num = 4 + split.string-strategy = "hash" +} +``` + +请为驱动配置集群的受信任根证书。`sslmode=disable` 仅适用于临时的、不启用安全认证的本地测试集群。 + +CockroachDB 不支持 PostgreSQL 的 `HASHTEXT` 函数。本方言使用 `FNV32` 对 STRING 和 UUID 等分区键进行哈希分片,并将空键值分配到一个分区,避免漏读。由于 CockroachDB 的 `pg_class.reltuples` 为 null,分片器需要行数时使用 `COUNT(*)`,这可能扫描输入表或查询。如果单 Reader 足够,可设置 `enable_concurrent_read = false` 跳过分片分析。 + +字符串转换使用已发现的 schema 中的原生类型信息,而不是在游标打开期间查询结果集类型元数据。支持的读取场景无需启用预览参数 `multiple_active_portals_enabled`。显式 schema 未提供原生类型信息时,STRING 字段按普通字符串处理。 + +首期支持通过 `query` 或明确的 `table_path` 有界读取现有表,复用 PostgreSQL 驱动兼容的类型映射。CockroachDB 24.3 测试覆盖 INT8、STRING、DECIMAL、BOOL、BYTES、UUID 和空值。不包含正则目录发现、CDC 和 range/auto 字符串分片。数值分片和字符串哈希分片复用现有 JDBC 分片生命周期,无需新增驱动或连接器插件。 + ## 并行读取器 任务 `parallelism` 决定最多可以同时运行多少个 Reader;分片配置决定实际有多少个独立 split 可以分配给 Reader。 diff --git a/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connectors/seatunnel/jdbc/internal/dialect/DatabaseIdentifier.java b/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connectors/seatunnel/jdbc/internal/dialect/DatabaseIdentifier.java index 08b1f5f048..2af72e32bf 100644 --- a/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connectors/seatunnel/jdbc/internal/dialect/DatabaseIdentifier.java +++ b/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connectors/seatunnel/jdbc/internal/dialect/DatabaseIdentifier.java @@ -31,6 +31,7 @@ public class DatabaseIdentifier { public static final String YASHANDB = "YashanDB"; public static final String PHOENIX = "Phoenix"; public static final String POSTGRESQL = "Postgres"; + public static final String COCKROACHDB = "CockroachDB"; public static final String REDSHIFT = "Redshift"; public static final String SAP_HANA = "SapHana"; public static final String SNOWFLAKE = "Snowflake"; diff --git a/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connectors/seatunnel/jdbc/internal/dialect/JdbcDialect.java b/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connectors/seatunnel/jdbc/internal/dialect/JdbcDialect.java index 60c7ed949a..b24c8d90a1 100644 --- a/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connectors/seatunnel/jdbc/internal/dialect/JdbcDialect.java +++ b/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connectors/seatunnel/jdbc/internal/dialect/JdbcDialect.java @@ -20,6 +20,8 @@ package org.apache.seatunnel.connectors.seatunnel.jdbc.internal.dialect; import org.apache.seatunnel.shade.org.apache.commons.lang3.StringUtils; import org.apache.seatunnel.api.common.SeaTunnelAPIErrorCode; +import org.apache.seatunnel.api.sink.DataSaveMode; +import org.apache.seatunnel.api.sink.SchemaSaveMode; import org.apache.seatunnel.api.table.catalog.TablePath; import org.apache.seatunnel.api.table.catalog.TableSchema; import org.apache.seatunnel.api.table.converter.BasicTypeDefine; @@ -519,6 +521,14 @@ public interface JdbcDialect extends Serializable { return columnName; } + /** Validates save modes before the sink is created; unrestricted dialects need no check. */ + default void validateSaveModes(DataSaveMode dataSaveMode, SchemaSaveMode schemaSaveMode) {} + + /** Whether this dialect can apply schema-change events to a physical sink table. */ + default boolean supportsSchemaChange() { + return true; + } + /** * Refresh physical table schema by schema change event * diff --git a/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connectors/seatunnel/jdbc/internal/dialect/cockroachdb/CockroachDBDialect.java b/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connectors/seatunnel/jdbc/internal/dialect/cockroachdb/CockroachDBDialect.java new file mode 100644 index 0000000000..629a0f7549 --- /dev/null +++ b/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connectors/seatunnel/jdbc/internal/dialect/cockroachdb/CockroachDBDialect.java @@ -0,0 +1,174 @@ +/* + * 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.seatunnel.connectors.seatunnel.jdbc.internal.dialect.cockroachdb; + +import org.apache.seatunnel.shade.org.apache.commons.lang3.StringUtils; + +import org.apache.seatunnel.api.common.SeaTunnelAPIErrorCode; +import org.apache.seatunnel.api.sink.DataSaveMode; +import org.apache.seatunnel.api.sink.SchemaSaveMode; +import org.apache.seatunnel.api.table.catalog.TablePath; +import org.apache.seatunnel.api.table.schema.event.AlterTableAddColumnEvent; +import org.apache.seatunnel.api.table.schema.event.AlterTableChangeColumnEvent; +import org.apache.seatunnel.api.table.schema.event.AlterTableDropColumnEvent; +import org.apache.seatunnel.api.table.schema.event.AlterTableModifyColumnEvent; +import org.apache.seatunnel.api.table.schema.event.SchemaChangeEvent; +import org.apache.seatunnel.connectors.seatunnel.jdbc.exception.JdbcConnectorException; +import org.apache.seatunnel.connectors.seatunnel.jdbc.internal.converter.JdbcRowConverter; +import org.apache.seatunnel.connectors.seatunnel.jdbc.internal.dialect.DatabaseIdentifier; +import org.apache.seatunnel.connectors.seatunnel.jdbc.internal.dialect.SQLUtils; +import org.apache.seatunnel.connectors.seatunnel.jdbc.internal.dialect.psql.PostgresDialect; +import org.apache.seatunnel.connectors.seatunnel.jdbc.source.JdbcSourceTable; + +import java.sql.Connection; +import java.sql.SQLException; +import java.util.Arrays; +import java.util.Map; +import java.util.stream.Collectors; +import java.util.stream.Stream; + +/** PostgreSQL-wire-compatible reads and writes against pre-created CockroachDB tables. */ +public class CockroachDBDialect extends PostgresDialect { + + public CockroachDBDialect() {} + + public CockroachDBDialect(String fieldIde) { + super(fieldIde); + } + + @Override + public String dialectName() { + return DatabaseIdentifier.COCKROACHDB; + } + + @Override + public JdbcRowConverter getRowConverter() { + return new CockroachDBRowConverter(); + } + + @Override + public String quoteIdentifier(String identifier) { + String[] parts = identifier.split("\\."); + parts[parts.length - 1] = getFieldIde(parts[parts.length - 1], fieldIde); + return Arrays.stream(parts) + .map(this::quoteDatabaseIdentifier) + .collect(Collectors.joining(".")); + } + + @Override + public String quoteDatabaseIdentifier(String identifier) { + return "\"" + identifier.replace("\"", "\"\"") + "\""; + } + + @Override + public String tableIdentifier(TablePath tablePath) { + return Stream.of( + tablePath.getDatabaseName(), + tablePath.getSchemaName(), + tablePath.getTableName()) + .filter(StringUtils::isNotEmpty) + .map(this::quoteDatabaseIdentifier) + .collect(Collectors.joining(".")); + } + + @Override + public String hashModForField(String nativeType, String fieldName, int mod) { + // HASHTEXT is not available in CockroachDB. FNV32 returns a non-negative INT8, and + // casting also supports UUID partition keys exposed as STRING by the type mapper. + // Both fixed and dynamic hash splitters use equality predicates; route NULL to one + // bucket as well, otherwise nullable partition keys would disappear from the source. + return "(COALESCE(FNV32(CAST(" + + quoteIdentifier(fieldName) + + " AS STRING)), 0) % " + + mod + + ")"; + } + + @Override + public Long approximateRowCntStatement(Connection connection, JdbcSourceTable table) + throws SQLException { + // CockroachDB exposes pg_class.reltuples as NULL. Treating it as zero would make + // source splitting decisions from an empty-table estimate even for populated tables. + if (StringUtils.isNotBlank(table.getQuery())) { + return SQLUtils.countForSubquery(connection, table.getQuery()); + } + return SQLUtils.countForTable(connection, tableIdentifier(table.getTablePath())); + } + + @Override + public void validateTableOptions(Map<String, String> tableOptions) { + if (tableOptions != null && !tableOptions.isEmpty()) { + throw new JdbcConnectorException( + SeaTunnelAPIErrorCode.CONFIG_VALIDATION_FAILED, + "CockroachDB requires pre-created tables and does not support table_options"); + } + } + + @Override + public void validateSaveModes(DataSaveMode dataSaveMode, SchemaSaveMode schemaSaveMode) { + if (dataSaveMode != DataSaveMode.APPEND_DATA + || (schemaSaveMode != SchemaSaveMode.CREATE_SCHEMA_WHEN_NOT_EXIST + && schemaSaveMode != SchemaSaveMode.IGNORE)) { + throw new JdbcConnectorException( + SeaTunnelAPIErrorCode.CONFIG_VALIDATION_FAILED, + "CockroachDB JDBC requires pre-created tables and supports only APPEND_DATA " + + "with the default schema_save_mode or IGNORE"); + } + } + + @Override + public boolean supportsSchemaChange() { + return false; + } + + @Override + public void applySchemaChange( + Connection connection, TablePath tablePath, SchemaChangeEvent event) { + throw unsupportedSchemaChange(); + } + + @Override + public void applySchemaChange( + Connection connection, TablePath tablePath, AlterTableAddColumnEvent event) { + throw unsupportedSchemaChange(); + } + + @Override + public void applySchemaChange( + Connection connection, TablePath tablePath, AlterTableChangeColumnEvent event) { + throw unsupportedSchemaChange(); + } + + @Override + public void applySchemaChange( + Connection connection, TablePath tablePath, AlterTableModifyColumnEvent event) { + throw unsupportedSchemaChange(); + } + + @Override + public void applySchemaChange( + Connection connection, TablePath tablePath, AlterTableDropColumnEvent event) { + throw unsupportedSchemaChange(); + } + + private JdbcConnectorException unsupportedSchemaChange() { + return new JdbcConnectorException( + SeaTunnelAPIErrorCode.CONFIG_VALIDATION_FAILED, + "CockroachDB JDBC dialect does not support schema changes; use pre-created tables"); + } +} diff --git a/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connectors/seatunnel/jdbc/internal/dialect/cockroachdb/CockroachDBDialectFactory.java b/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connectors/seatunnel/jdbc/internal/dialect/cockroachdb/CockroachDBDialectFactory.java new file mode 100644 index 0000000000..3ca79d5fa3 --- /dev/null +++ b/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connectors/seatunnel/jdbc/internal/dialect/cockroachdb/CockroachDBDialectFactory.java @@ -0,0 +1,50 @@ +/* + * 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.seatunnel.connectors.seatunnel.jdbc.internal.dialect.cockroachdb; + +import org.apache.seatunnel.connectors.seatunnel.jdbc.internal.dialect.DatabaseIdentifier; +import org.apache.seatunnel.connectors.seatunnel.jdbc.internal.dialect.JdbcDialect; +import org.apache.seatunnel.connectors.seatunnel.jdbc.internal.dialect.JdbcDialectFactory; + +import com.google.auto.service.AutoService; + +/** Explicit dialect selection without changing PostgreSQL URL discovery. */ +@AutoService(JdbcDialectFactory.class) +public class CockroachDBDialectFactory implements JdbcDialectFactory { + @Override + public String dialectFactoryName() { + return DatabaseIdentifier.COCKROACHDB; + } + + @Override + public boolean acceptsURL(String url) { + // CockroachDB uses PostgreSQL JDBC URLs. Require explicit dialect selection so that + // ordinary PostgreSQL connections continue to resolve to a single factory. + return false; + } + + @Override + public JdbcDialect create() { + return new CockroachDBDialect(); + } + + @Override + public JdbcDialect create(String compatibleMode, String fieldIde) { + return new CockroachDBDialect(fieldIde); + } +} diff --git a/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connectors/seatunnel/jdbc/internal/dialect/cockroachdb/CockroachDBRowConverter.java b/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connectors/seatunnel/jdbc/internal/dialect/cockroachdb/CockroachDBRowConverter.java new file mode 100644 index 0000000000..4a6cee636a --- /dev/null +++ b/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connectors/seatunnel/jdbc/internal/dialect/cockroachdb/CockroachDBRowConverter.java @@ -0,0 +1,42 @@ +/* + * 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.seatunnel.connectors.seatunnel.jdbc.internal.dialect.cockroachdb; + +import org.apache.seatunnel.api.table.catalog.TableSchema; +import org.apache.seatunnel.api.table.type.SeaTunnelRowType; +import org.apache.seatunnel.connectors.seatunnel.jdbc.internal.dialect.DatabaseIdentifier; +import org.apache.seatunnel.connectors.seatunnel.jdbc.internal.dialect.psql.PostgresJdbcRowConverter; + +import java.sql.ResultSet; + +/** Reuses PostgreSQL values without issuing metadata queries while a read cursor is open. */ +public class CockroachDBRowConverter extends PostgresJdbcRowConverter { + @Override + public String converterName() { + return DatabaseIdentifier.COCKROACHDB; + } + + @Override + protected String getStringColumnType( + ResultSet rs, TableSchema tableSchema, SeaTunnelRowType typeInfo, int fieldIndex) { + // pgjdbc's getColumnTypeName performs a catalog query to detect serial columns. + // CockroachDB does not allow that second query while a cursor is suspended. Use + // schema discovery instead; an explicit schema without sourceType is a plain STRING. + return tableSchema.getColumn(typeInfo.getFieldName(fieldIndex)).getSourceType(); + } +} diff --git a/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connectors/seatunnel/jdbc/internal/dialect/psql/PostgresJdbcRowConverter.java b/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connectors/seatunnel/jdbc/internal/dialect/psql/PostgresJdbcRowConverter.java index a9894961a8..0252f7507f 100644 --- a/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connectors/seatunnel/jdbc/internal/dialect/psql/PostgresJdbcRowConverter.java +++ b/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connectors/seatunnel/jdbc/internal/dialect/psql/PostgresJdbcRowConverter.java @@ -126,10 +126,10 @@ public class PostgresJdbcRowConverter extends AbstractJdbcRowConverter { for (int fieldIndex = 0; fieldIndex < typeInfo.getTotalFields(); fieldIndex++) { SeaTunnelDataType<?> seaTunnelDataType = typeInfo.getFieldType(fieldIndex); int resultSetIndex = fieldIndex + 1; - String metaDataColumnType = - rs.getMetaData().getColumnTypeName(resultSetIndex).toUpperCase(Locale.ROOT); switch (seaTunnelDataType.getSqlType()) { case STRING: + String metaDataColumnType = + getStringColumnType(rs, tableSchema, typeInfo, fieldIndex); if (PG_GEOMETRY.equalsIgnoreCase(metaDataColumnType) || PG_GEOGRAPHY.equalsIgnoreCase(metaDataColumnType)) { Object geoObj = rs.getObject(resultSetIndex); @@ -258,6 +258,13 @@ public class PostgresJdbcRowConverter extends AbstractJdbcRowConverter { } } + /** Resolves native string types that need PostgreSQL-specific value conversion. */ + protected String getStringColumnType( + ResultSet rs, TableSchema tableSchema, SeaTunnelRowType typeInfo, int fieldIndex) + throws SQLException { + return rs.getMetaData().getColumnTypeName(fieldIndex + 1); + } + @Override public PreparedStatement toExternal( TableSchema tableSchema, diff --git a/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connectors/seatunnel/jdbc/sink/AbstractJdbcSinkWriter.java b/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connectors/seatunnel/jdbc/sink/AbstractJdbcSinkWriter.java index d552fd19b5..8a777e6949 100644 --- a/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connectors/seatunnel/jdbc/sink/AbstractJdbcSinkWriter.java +++ b/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connectors/seatunnel/jdbc/sink/AbstractJdbcSinkWriter.java @@ -17,6 +17,7 @@ package org.apache.seatunnel.connectors.seatunnel.jdbc.sink; +import org.apache.seatunnel.api.common.SeaTunnelAPIErrorCode; import org.apache.seatunnel.api.sink.SinkWriter; import org.apache.seatunnel.api.sink.SupportMultiTableSinkWriter; import org.apache.seatunnel.api.sink.SupportSchemaEvolutionSinkWriter; @@ -70,6 +71,11 @@ public abstract class AbstractJdbcSinkWriter<ResourceT> @Override public void applySchemaChange(SchemaChangeEvent event) throws IOException { + if (!(event instanceof RestoreTableSchemaEvent) && !dialect.supportsSchemaChange()) { + throw new JdbcConnectorException( + SeaTunnelAPIErrorCode.CONFIG_VALIDATION_FAILED, + dialect.dialectName() + " JDBC dialect does not support schema changes"); + } this.tableSchema = tableSchemaChanger.reset(tableSchema).apply(event); reOpenOutputFormat(event); } diff --git a/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connectors/seatunnel/jdbc/sink/JdbcSink.java b/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connectors/seatunnel/jdbc/sink/JdbcSink.java index 3eb0d1471d..3a07df9013 100644 --- a/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connectors/seatunnel/jdbc/sink/JdbcSink.java +++ b/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connectors/seatunnel/jdbc/sink/JdbcSink.java @@ -59,6 +59,7 @@ import lombok.extern.slf4j.Slf4j; import java.io.IOException; import java.util.ArrayList; import java.util.Arrays; +import java.util.Collections; import java.util.List; import java.util.Optional; @@ -334,6 +335,9 @@ public class JdbcSink @Override public List<SchemaChangeType> supports() { + if (!dialect.supportsSchemaChange()) { + return Collections.emptyList(); + } return Arrays.asList( SchemaChangeType.ADD_COLUMN, SchemaChangeType.DROP_COLUMN, diff --git a/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connectors/seatunnel/jdbc/sink/JdbcSinkFactory.java b/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connectors/seatunnel/jdbc/sink/JdbcSinkFactory.java index b77843556e..075f114995 100644 --- a/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connectors/seatunnel/jdbc/sink/JdbcSinkFactory.java +++ b/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connectors/seatunnel/jdbc/sink/JdbcSinkFactory.java @@ -182,6 +182,7 @@ public class JdbcSinkFactory implements TableSinkFactory, SupportSinkDryRunValid CatalogTable finalCatalogTable = catalogTable; DataSaveMode dataSaveMode = config.get(JdbcSinkOptions.DATA_SAVE_MODE); SchemaSaveMode schemaSaveMode = config.get(JdbcSinkOptions.SCHEMA_SAVE_MODE); + dialect.validateSaveModes(dataSaveMode, schemaSaveMode); return () -> new JdbcSink( options, diff --git a/seatunnel-connectors-v2/connector-jdbc/src/test/java/org/apache/seatunnel/connectors/seatunnel/jdbc/internal/dialect/JdbcDialectLoaderTest.java b/seatunnel-connectors-v2/connector-jdbc/src/test/java/org/apache/seatunnel/connectors/seatunnel/jdbc/internal/dialect/JdbcDialectLoaderTest.java index 90e934cd42..89a980b4cc 100644 --- a/seatunnel-connectors-v2/connector-jdbc/src/test/java/org/apache/seatunnel/connectors/seatunnel/jdbc/internal/dialect/JdbcDialectLoaderTest.java +++ b/seatunnel-connectors-v2/connector-jdbc/src/test/java/org/apache/seatunnel/connectors/seatunnel/jdbc/internal/dialect/JdbcDialectLoaderTest.java @@ -18,6 +18,7 @@ package org.apache.seatunnel.connectors.seatunnel.jdbc.internal.dialect; +import org.apache.seatunnel.connectors.seatunnel.jdbc.internal.dialect.cockroachdb.CockroachDBDialect; import org.apache.seatunnel.connectors.seatunnel.jdbc.internal.dialect.mysql.MysqlDialect; import org.apache.seatunnel.connectors.seatunnel.jdbc.internal.dialect.psql.PostgresDialect; @@ -26,6 +27,17 @@ import org.junit.jupiter.api.Test; /** Test for {@link JdbcDialectLoader} */ public class JdbcDialectLoaderTest { + @Test + void shouldSelectCockroachDBOnlyExplicitly() { + String url = "jdbc:postgresql://localhost:26257/seatunnel"; + Assertions.assertInstanceOf( + CockroachDBDialect.class, JdbcDialectLoader.load(url, "CockroachDB", "")); + Assertions.assertEquals( + PostgresDialect.class, JdbcDialectLoader.load(url, null, "").getClass()); + Assertions.assertEquals( + PostgresDialect.class, JdbcDialectLoader.load(url, "Postgres", "").getClass()); + } + @Test public void shouldFindGenericDialect() throws Exception { JdbcDialect jdbcDialect = JdbcDialectLoader.load("jdbc:someting:", null, ""); diff --git a/seatunnel-connectors-v2/connector-jdbc/src/test/java/org/apache/seatunnel/connectors/seatunnel/jdbc/internal/dialect/cockroachdb/CockroachDBDialectTest.java b/seatunnel-connectors-v2/connector-jdbc/src/test/java/org/apache/seatunnel/connectors/seatunnel/jdbc/internal/dialect/cockroachdb/CockroachDBDialectTest.java new file mode 100644 index 0000000000..5714e60933 --- /dev/null +++ b/seatunnel-connectors-v2/connector-jdbc/src/test/java/org/apache/seatunnel/connectors/seatunnel/jdbc/internal/dialect/cockroachdb/CockroachDBDialectTest.java @@ -0,0 +1,147 @@ +/* + * 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.seatunnel.connectors.seatunnel.jdbc.internal.dialect.cockroachdb; + +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.schema.event.AlterTableAddColumnEvent; +import org.apache.seatunnel.api.table.schema.event.AlterTableChangeColumnEvent; +import org.apache.seatunnel.api.table.schema.event.AlterTableDropColumnEvent; +import org.apache.seatunnel.api.table.schema.event.AlterTableModifyColumnEvent; +import org.apache.seatunnel.api.table.schema.event.SchemaChangeEvent; +import org.apache.seatunnel.api.table.type.BasicType; +import org.apache.seatunnel.connectors.seatunnel.jdbc.exception.JdbcConnectorException; +import org.apache.seatunnel.connectors.seatunnel.jdbc.internal.dialect.JdbcDialect; + +import org.junit.jupiter.api.Assertions; +import org.junit.jupiter.api.Test; + +import java.sql.Connection; +import java.sql.ResultSet; +import java.util.Collections; + +import static org.mockito.Mockito.mock; +import static org.mockito.Mockito.never; +import static org.mockito.Mockito.verify; +import static org.mockito.Mockito.verifyNoInteractions; +import static org.mockito.Mockito.when; + +class CockroachDBDialectTest { + private final CockroachDBDialect dialect = new CockroachDBDialect(); + + @Test + void usesCockroachHashForStringAndUuidKeys() { + String expected = "(COALESCE(FNV32(CAST(\"key\" AS STRING)), 0) % 3)"; + Assertions.assertEquals(expected, dialect.hashModForField("key", 3)); + Assertions.assertEquals(expected, dialect.hashModForField("uuid", "key", 3)); + } + + @Test + void preservesConflictTargetAndPartialColumns() { + Assertions.assertEquals( + "INSERT INTO \"db\".\"public\".\"target\" (\"id\", \"email\", \"value\") " + + "VALUES (:id, :email, :value) ON CONFLICT (\"email\") " + + "DO UPDATE SET \"id\"=EXCLUDED.\"id\", \"value\"=EXCLUDED.\"value\"", + dialect.getUpsertStatement( + "db", + "public.target", + new String[] {"id", "email", "value"}, + new String[] {"email"}) + .get()); + Assertions.assertTrue( + dialect.getUpsertStatement("db", "target", new String[] {"id"}, new String[] {"id"}) + .get() + .endsWith("ON CONFLICT (\"id\") DO NOTHING")); + } + + @Test + void quotesIdentifiersAndRetainsConfiguredCase() { + JdbcDialect upper = new CockroachDBDialectFactory().create("", "UPPERCASE"); + Assertions.assertEquals("\"schema\".\"TABLE\"", upper.quoteIdentifier("schema.table")); + Assertions.assertEquals("\"a\"\"b\"", dialect.quoteIdentifier("a\"b")); + Assertions.assertEquals( + "\"db\"\"name\".\"public\".\"table\"\"name\"", + dialect.tableIdentifier(TablePath.of("db\"name", "public", "table\"name"))); + } + + @Test + void convertsStringsWithoutResultSetMetadataQueries() throws Exception { + for (String sourceType : + new String[] {null, "", "STRING", "UUID", "geometry", "GEOGRAPHY"}) { + TableSchema schema = + TableSchema.builder() + .columns( + Collections.singletonList( + PhysicalColumn.builder() + .name("value") + .dataType(BasicType.STRING_TYPE) + .sourceType(sourceType) + .build())) + .build(); + ResultSet rs = mock(ResultSet.class); + when(rs.getObject(1)).thenReturn("value"); + when(rs.getString(1)).thenReturn("value"); + Assertions.assertEquals( + "value", dialect.getRowConverter().toInternal(rs, schema).getField(0)); + when(rs.getObject(1)).thenReturn(null); + Assertions.assertNull(dialect.getRowConverter().toInternal(rs, schema).getField(0)); + verify(rs, never()).getMetaData(); + } + } + + @Test + void rejectsPostgresTableOptionsWithoutACockroachCatalog() { + dialect.validateTableOptions(Collections.emptyMap()); + Assertions.assertThrows( + JdbcConnectorException.class, + () -> dialect.validateTableOptions(Collections.singletonMap("fillfactor", "80"))); + } + + @Test + void rejectsSchemaChangesBeforeIssuingAnySql() { + Connection connection = mock(Connection.class); + TablePath tablePath = TablePath.of("db", "public", "target"); + Assertions.assertThrows( + JdbcConnectorException.class, + () -> + dialect.applySchemaChange( + connection, tablePath, mock(SchemaChangeEvent.class))); + Assertions.assertThrows( + JdbcConnectorException.class, + () -> + dialect.applySchemaChange( + connection, tablePath, mock(AlterTableAddColumnEvent.class))); + Assertions.assertThrows( + JdbcConnectorException.class, + () -> + dialect.applySchemaChange( + connection, tablePath, mock(AlterTableChangeColumnEvent.class))); + Assertions.assertThrows( + JdbcConnectorException.class, + () -> + dialect.applySchemaChange( + connection, tablePath, mock(AlterTableModifyColumnEvent.class))); + Assertions.assertThrows( + JdbcConnectorException.class, + () -> + dialect.applySchemaChange( + connection, tablePath, mock(AlterTableDropColumnEvent.class))); + verifyNoInteractions(connection); + } +} diff --git a/seatunnel-connectors-v2/connector-jdbc/src/test/java/org/apache/seatunnel/connectors/seatunnel/jdbc/internal/dialect/psql/PostgresJdbcRowConverterTest.java b/seatunnel-connectors-v2/connector-jdbc/src/test/java/org/apache/seatunnel/connectors/seatunnel/jdbc/internal/dialect/psql/PostgresJdbcRowConverterTest.java index 578be7c2c5..f76ff61b22 100644 --- a/seatunnel-connectors-v2/connector-jdbc/src/test/java/org/apache/seatunnel/connectors/seatunnel/jdbc/internal/dialect/psql/PostgresJdbcRowConverterTest.java +++ b/seatunnel-connectors-v2/connector-jdbc/src/test/java/org/apache/seatunnel/connectors/seatunnel/jdbc/internal/dialect/psql/PostgresJdbcRowConverterTest.java @@ -47,6 +47,8 @@ import java.util.List; import static org.mockito.Mockito.eq; import static org.mockito.Mockito.mock; +import static org.mockito.Mockito.never; +import static org.mockito.Mockito.spy; import static org.mockito.Mockito.verify; import static org.mockito.Mockito.when; @@ -108,6 +110,39 @@ public class PostgresJdbcRowConverterTest { Assertions.assertEquals(offset, offsetDateTime.getOffset()); } + @Test + public void testStringReadDoesNotLookUpColumnByName() throws SQLException { + ResultSet rs = mock(ResultSet.class); + ResultSetMetaData metadata = mock(ResultSetMetaData.class); + TableSchema schema = spy(createTableSchema("value", BasicType.STRING_TYPE, "VARCHAR")); + when(rs.getMetaData()).thenReturn(metadata); + when(metadata.getColumnTypeName(2)).thenReturn("varchar"); + when(rs.getInt(1)).thenReturn(1); + when(rs.getObject(2)).thenReturn("value"); + when(rs.getString(2)).thenReturn("value"); + + SeaTunnelRow row = converter.toInternal(rs, schema); + + Assertions.assertEquals("value", row.getField(1)); + verify(schema, never()).getColumn("value"); + } + + @Test + public void testNumericReadDoesNotQueryResultSetMetadata() throws SQLException { + ResultSet rs = mock(ResultSet.class); + TableSchema schema = createTableSchema("value", BasicType.LONG_TYPE, "INT8"); + when(rs.getObject(1)).thenReturn(1); + when(rs.getObject(2)).thenReturn(42L); + when(rs.getInt(1)).thenReturn(1); + when(rs.getLong(2)).thenReturn(42L); + + SeaTunnelRow row = converter.toInternal(rs, schema); + + Assertions.assertEquals(1, row.getField(0)); + Assertions.assertEquals(42L, row.getField(1)); + verify(rs, never()).getMetaData(); + } + @Test public void testToInternalWithTimestampTzFromPGobject() throws SQLException { ResultSet rs = mock(ResultSet.class); diff --git a/seatunnel-connectors-v2/connector-jdbc/src/test/java/org/apache/seatunnel/connectors/seatunnel/jdbc/sink/JdbcSinkFactoryTest.java b/seatunnel-connectors-v2/connector-jdbc/src/test/java/org/apache/seatunnel/connectors/seatunnel/jdbc/sink/JdbcSinkFactoryTest.java index bdcd346fa4..75cf15bfad 100644 --- a/seatunnel-connectors-v2/connector-jdbc/src/test/java/org/apache/seatunnel/connectors/seatunnel/jdbc/sink/JdbcSinkFactoryTest.java +++ b/seatunnel-connectors-v2/connector-jdbc/src/test/java/org/apache/seatunnel/connectors/seatunnel/jdbc/sink/JdbcSinkFactoryTest.java @@ -28,7 +28,9 @@ import org.apache.seatunnel.api.table.catalog.TableIdentifier; import org.apache.seatunnel.api.table.catalog.TableSchema; import org.apache.seatunnel.api.table.connector.TableSink; import org.apache.seatunnel.api.table.factory.TableSinkFactoryContext; +import org.apache.seatunnel.api.table.schema.SchemaChangeType; import org.apache.seatunnel.api.table.type.BasicType; +import org.apache.seatunnel.connectors.seatunnel.jdbc.exception.JdbcConnectorException; import org.junit.jupiter.api.Assertions; import org.junit.jupiter.api.Test; @@ -58,6 +60,14 @@ class JdbcSinkFactoryTest { return cfg; } + private Map<String, Object> cockroachConfig() { + Map<String, Object> cfg = baseConfig(); + cfg.put("url", "jdbc:postgresql://localhost:26257/defaultdb"); + cfg.put("driver", "org.postgresql.Driver"); + cfg.put("dialect", "CockroachDB"); + return cfg; + } + private CatalogTable createCatalogTable(boolean withPrimaryKey) { TableSchema.Builder schemaBuilder = TableSchema.builder() @@ -285,4 +295,36 @@ class JdbcSinkFactoryTest { + "CatalogTable has primary keys; the PK + APPEND_VALUES conflict " + "is guarded at runtime by JdbcOutputFormatBuilder.validateOracleInsertMode"); } + + @Test + void cockroachDoesNotAdvertiseSchemaEvolutionWhilePostgresStillDoes() { + Map<String, Object> cfg = cockroachConfig(); + JdbcSink cockroach = (JdbcSink) createSinkViaFactoryContext(cfg, true).createSink(); + Assertions.assertTrue(cockroach.supports().isEmpty()); + + cfg.put("dialect", "Postgres"); + JdbcSink postgres = (JdbcSink) createSinkViaFactoryContext(cfg, true).createSink(); + Assertions.assertTrue(postgres.supports().contains(SchemaChangeType.ADD_COLUMN)); + } + + @Test + void cockroachRejectsSaveModesThatWouldOtherwiseBeSkipped() { + Map<String, Object> cfg = cockroachConfig(); + cfg.put("schema_save_mode", "IGNORE"); + Assertions.assertDoesNotThrow(() -> createSinkViaFactoryContext(cfg, true)); + cfg.put("custom_sql", "DELETE FROM public.target"); + + for (String dataMode : + new String[] {"DROP_DATA", "CUSTOM_PROCESSING", "ERROR_WHEN_DATA_EXISTS"}) { + cfg.put("data_save_mode", dataMode); + Assertions.assertThrows( + JdbcConnectorException.class, () -> createSinkViaFactoryContext(cfg, true)); + } + cfg.put("data_save_mode", "APPEND_DATA"); + for (String schemaMode : new String[] {"RECREATE_SCHEMA", "ERROR_WHEN_SCHEMA_NOT_EXIST"}) { + cfg.put("schema_save_mode", schemaMode); + Assertions.assertThrows( + JdbcConnectorException.class, () -> createSinkViaFactoryContext(cfg, true)); + } + } } diff --git a/seatunnel-connectors-v2/connector-jdbc/src/test/java/org/apache/seatunnel/connectors/seatunnel/jdbc/sink/JdbcSinkWriterSchemaChangeSafetyTest.java b/seatunnel-connectors-v2/connector-jdbc/src/test/java/org/apache/seatunnel/connectors/seatunnel/jdbc/sink/JdbcSinkWriterSchemaChangeSafetyTest.java new file mode 100644 index 0000000000..b326f29b58 --- /dev/null +++ b/seatunnel-connectors-v2/connector-jdbc/src/test/java/org/apache/seatunnel/connectors/seatunnel/jdbc/sink/JdbcSinkWriterSchemaChangeSafetyTest.java @@ -0,0 +1,115 @@ +/* + * 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.seatunnel.connectors.seatunnel.jdbc.sink; + +import org.apache.seatunnel.api.table.catalog.CatalogTable; +import org.apache.seatunnel.api.table.catalog.PhysicalColumn; +import org.apache.seatunnel.api.table.catalog.TableIdentifier; +import org.apache.seatunnel.api.table.catalog.TablePath; +import org.apache.seatunnel.api.table.catalog.TableSchema; +import org.apache.seatunnel.api.table.schema.event.AlterTableAddColumnEvent; +import org.apache.seatunnel.api.table.schema.event.RestoreTableSchemaEvent; +import org.apache.seatunnel.api.table.type.BasicType; +import org.apache.seatunnel.connectors.seatunnel.jdbc.config.JdbcConnectionConfig; +import org.apache.seatunnel.connectors.seatunnel.jdbc.config.JdbcSinkConfig; +import org.apache.seatunnel.connectors.seatunnel.jdbc.exception.JdbcConnectorException; +import org.apache.seatunnel.connectors.seatunnel.jdbc.internal.dialect.cockroachdb.CockroachDBDialect; + +import org.junit.jupiter.api.Assertions; +import org.junit.jupiter.api.Test; + +import java.util.Collections; + +import static org.mockito.ArgumentMatchers.any; +import static org.mockito.Mockito.doNothing; +import static org.mockito.Mockito.never; +import static org.mockito.Mockito.spy; +import static org.mockito.Mockito.verify; + +class JdbcSinkWriterSchemaChangeSafetyTest { + private static final TablePath TABLE_PATH = TablePath.of("defaultdb", "public", "target"); + + @Test + void cockroachRejectsSchemaChangeBeforeMutatingOrCommitting() throws Exception { + TableSchema schema = schema("id"); + JdbcSinkWriter writer = spy(writer(schema)); + Object originalOutputFormat = writer.outputFormat; + AlterTableAddColumnEvent event = + AlterTableAddColumnEvent.add( + TableIdentifier.of("jdbc", TABLE_PATH), + PhysicalColumn.builder() + .name("extra") + .dataType(BasicType.STRING_TYPE) + .build()); + + Assertions.assertThrows( + JdbcConnectorException.class, () -> writer.applySchemaChange(event)); + Assertions.assertSame(schema, writer.tableSchema); + Assertions.assertSame(originalOutputFormat, writer.outputFormat); + verify(writer, never()).prepareCommit(); + verify(writer, never()).reOpenOutputFormat(any()); + } + + @Test + void cockroachStillAcceptsRuntimeSchemaRestore() throws Exception { + JdbcSinkWriter writer = spy(writer(schema("id"))); + TableSchema restoredSchema = schema("id", "extra"); + RestoreTableSchemaEvent event = + new RestoreTableSchemaEvent( + CatalogTable.of( + TableIdentifier.of("jdbc", TABLE_PATH), + restoredSchema, + Collections.emptyMap(), + Collections.emptyList(), + null, + "jdbc")); + doNothing().when(writer).reOpenOutputFormat(event); + + writer.applySchemaChange(event); + + Assertions.assertArrayEquals( + restoredSchema.getFieldNames(), writer.tableSchema.getFieldNames()); + verify(writer).reOpenOutputFormat(event); + } + + private static JdbcSinkWriter writer(TableSchema schema) { + JdbcConnectionConfig connectionConfig = + JdbcConnectionConfig.builder() + .url("jdbc:postgresql://localhost:26257/defaultdb") + .driverName("org.postgresql.Driver") + .properties(Collections.emptyMap()) + .build(); + JdbcSinkConfig sinkConfig = + JdbcSinkConfig.builder() + .jdbcConnectionConfig(connectionConfig) + .database("defaultdb") + .table("public.target") + .build(); + return new JdbcSinkWriter( + TABLE_PATH, new CockroachDBDialect(), sinkConfig, schema, null, null); + } + + private static TableSchema schema(String... names) { + TableSchema.Builder builder = TableSchema.builder(); + for (String name : names) { + builder.column( + PhysicalColumn.builder().name(name).dataType(BasicType.STRING_TYPE).build()); + } + return builder.build(); + } +} diff --git a/seatunnel-e2e/seatunnel-connector-v2-e2e/connector-jdbc-e2e/connector-jdbc-e2e-part-1/src/test/java/org/apache/seatunnel/connectors/seatunnel/jdbc/JdbcCockroachDBDialectIT.java b/seatunnel-e2e/seatunnel-connector-v2-e2e/connector-jdbc-e2e/connector-jdbc-e2e-part-1/src/test/java/org/apache/seatunnel/connectors/seatunnel/jdbc/JdbcCockroachDBDialectIT.java new file mode 100644 index 0000000000..c76913971c --- /dev/null +++ b/seatunnel-e2e/seatunnel-connector-v2-e2e/connector-jdbc-e2e/connector-jdbc-e2e-part-1/src/test/java/org/apache/seatunnel/connectors/seatunnel/jdbc/JdbcCockroachDBDialectIT.java @@ -0,0 +1,367 @@ +/* + * 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.seatunnel.connectors.seatunnel.jdbc; + +import org.apache.seatunnel.api.configuration.ReadonlyConfig; +import org.apache.seatunnel.api.table.catalog.CatalogTable; +import org.apache.seatunnel.api.table.catalog.Column; +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.connectors.seatunnel.jdbc.catalog.utils.CatalogUtils; +import org.apache.seatunnel.connectors.seatunnel.jdbc.config.JdbcSourceConfig; +import org.apache.seatunnel.connectors.seatunnel.jdbc.internal.dialect.JdbcDialect; +import org.apache.seatunnel.connectors.seatunnel.jdbc.internal.dialect.JdbcDialectLoader; +import org.apache.seatunnel.connectors.seatunnel.jdbc.internal.dialect.cockroachdb.CockroachDBDialect; +import org.apache.seatunnel.connectors.seatunnel.jdbc.internal.dialect.psql.PostgresDialect; +import org.apache.seatunnel.connectors.seatunnel.jdbc.internal.executor.FieldNamedPreparedStatement; +import org.apache.seatunnel.connectors.seatunnel.jdbc.source.ChunkSplitter; +import org.apache.seatunnel.connectors.seatunnel.jdbc.source.JdbcSourceSplit; +import org.apache.seatunnel.connectors.seatunnel.jdbc.source.JdbcSourceTable; +import org.apache.seatunnel.connectors.seatunnel.jdbc.utils.JdbcCatalogUtils; + +import org.junit.jupiter.api.AfterAll; +import org.junit.jupiter.api.Assertions; +import org.junit.jupiter.api.BeforeAll; +import org.junit.jupiter.api.Test; +import org.testcontainers.containers.GenericContainer; +import org.testcontainers.containers.wait.strategy.Wait; + +import java.math.BigDecimal; +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.time.Duration; +import java.util.Collection; +import java.util.HashMap; +import java.util.HashSet; +import java.util.Map; +import java.util.Set; +import java.util.stream.Collectors; + +/** Real PostgreSQL-driver and CockroachDB coverage in the JDBC part-1 E2E lane. */ +class JdbcCockroachDBDialectIT { + private static GenericContainer<?> database; + private static String url; + private final JdbcDialect dialect = new CockroachDBDialect(); + + @BeforeAll + static void startDatabase() throws Exception { + database = + new GenericContainer<>("cockroachdb/cockroach:v24.3.0") + .withCreateContainerCmdModifier( + cmd -> cmd.withEntrypoint("/cockroach/cockroach")) + .withCommand( + "start-single-node", + "--insecure", + "--store=type=mem,size=1GiB", + "--cache=64MiB", + "--max-sql-memory=128MiB") + .withExposedPorts(26257) + .waitingFor( + Wait.forLogMessage(".*node startup completed.*", 1) + .withStartupTimeout(Duration.ofMinutes(2))); + database.start(); + url = + "jdbc:postgresql://" + + database.getHost() + + ":" + + database.getMappedPort(26257) + + "/defaultdb?sslmode=disable"; + try (Connection connection = connect(); + Statement statement = connection.createStatement()) { + statement.execute( + "CREATE TABLE source_rows (id INT8 PRIMARY KEY, key STRING, amount DECIMAL(12,2), active BOOL, payload BYTES, uid UUID)"); + statement.execute( + "INSERT INTO source_rows VALUES (1,'alpha',12.34,true,b'abc','00000000-0000-0000-0000-000000000001'), (2,'你好',-9.50,false,b'def','00000000-0000-0000-0000-000000000002'), (3,NULL,NULL,NULL,NULL,NULL), (4,'',0,true,b'','00000000-0000-0000-0000-000000000004')"); + statement.execute( + "CREATE TABLE upsert_rows (id INT8 PRIMARY KEY, email STRING UNIQUE, value STRING, preserved STRING DEFAULT 'default')"); + statement.execute("INSERT INTO upsert_rows VALUES (1,'a','before','keep')"); + } + } + + @AfterAll + static void stopDatabase() { + if (database != null) { + database.stop(); + } + } + + private static Connection connect() throws SQLException { + return DriverManager.getConnection(url, "root", ""); + } + + @Test + void postgresHashFailsButCockroachHashIncludesEveryRowOnce() throws Exception { + try (Connection connection = connect(); + Statement statement = connection.createStatement()) { + SQLException error = + Assertions.assertThrows( + SQLException.class, + () -> + statement.executeQuery( + "SELECT " + + new PostgresDialect() + .hashModForField("key", 3) + + " FROM source_rows")); + Assertions.assertEquals("42883", error.getSQLState()); + } + for (String key : new String[] {"key", "uid"}) { + Map<String, Object> values = new HashMap<>(); + values.put("url", url); + values.put("driver", "org.postgresql.Driver"); + values.put("username", "root"); + values.put("password", ""); + values.put("dialect", "CockroachDB"); + values.put("query", "SELECT * FROM source_rows"); + values.put("partition_column", key); + values.put("partition_num", 3); + values.put("split.string-strategy", "hash"); + JdbcSourceConfig config = JdbcSourceConfig.of(ReadonlyConfig.fromMap(values)); + Map<TablePath, JdbcSourceTable> tables = + JdbcCatalogUtils.getTables( + config.getJdbcConnectionConfig(), config.getTableConfigList(), null); + Assertions.assertEquals(1, tables.size()); + JdbcSourceTable table = tables.values().iterator().next(); + Set<Long> seen = new HashSet<>(); + try (ChunkSplitter splitter = ChunkSplitter.create(config)) { + Collection<JdbcSourceSplit> splits = splitter.generateSplits(table); + Assertions.assertEquals(3, splits.size()); + for (JdbcSourceSplit split : splits) { + try (PreparedStatement query = + splitter.generateSplitStatement( + split, table.getCatalogTable().getTableSchema()); + ResultSet rows = query.executeQuery()) { + while (rows.next()) { + SeaTunnelRow row = + dialect.getRowConverter() + .toInternal( + rows, table.getCatalogTable().getTableSchema()); + Assertions.assertTrue( + seen.add((Long) row.getField(0)), "duplicate source row"); + } + } + } + } + Assertions.assertEquals(Set.of(1L, 2L, 3L, 4L), seen); + } + } + + @Test + void discoversTableMetadataAndReadsNumericDynamicSplits() throws Exception { + Map<String, Object> values = new HashMap<>(); + values.put("url", url); + values.put("driver", "org.postgresql.Driver"); + values.put("username", "root"); + values.put("password", ""); + values.put("dialect", "CockroachDB"); + values.put("table_path", "defaultdb.public.source_rows"); + values.put("partition_column", "id"); + values.put("split.size", 2); + JdbcSourceConfig config = JdbcSourceConfig.of(ReadonlyConfig.fromMap(values)); + JdbcSourceTable table = + JdbcCatalogUtils.getTables( + config.getJdbcConnectionConfig(), config.getTableConfigList(), null) + .values() + .iterator() + .next(); + Assertions.assertEquals( + "id", + table.getCatalogTable().getTableSchema().getPrimaryKey().getColumnNames().get(0)); + Set<Long> seen = new HashSet<>(); + try (ChunkSplitter splitter = ChunkSplitter.create(config)) { + Collection<JdbcSourceSplit> splits = splitter.generateSplits(table); + Assertions.assertTrue(splits.size() > 1); + for (JdbcSourceSplit split : splits) { + try (PreparedStatement query = + splitter.generateSplitStatement( + split, table.getCatalogTable().getTableSchema()); + ResultSet rows = query.executeQuery()) { + while (rows.next()) { + SeaTunnelRow row = + dialect.getRowConverter() + .toInternal(rows, table.getCatalogTable().getTableSchema()); + Assertions.assertTrue( + seen.add((Long) row.getField(0)), "duplicate numeric split row"); + } + } + } + } + Assertions.assertEquals(Set.of(1L, 2L, 3L, 4L), seen); + } + + @Test + void mapsTypesAndCountsTableAndFilteredQuery() throws Exception { + try (Connection connection = connect()) { + CatalogTable catalog = + CatalogUtils.getCatalogTable( + connection, + "SELECT * FROM source_rows", + dialect.getJdbcDialectTypeMapper()); + TableSchema schema = catalog.getTableSchema(); + Assertions.assertEquals(BasicType.LONG_TYPE, schema.getColumn("id").getDataType()); + Assertions.assertEquals(BasicType.STRING_TYPE, schema.getColumn("uid").getDataType()); + JdbcSourceTable table = + JdbcSourceTable.builder() + .tablePath(TablePath.of("defaultdb", "public", "source_rows")) + .catalogTable(catalog) + .build(); + Assertions.assertEquals(4L, dialect.approximateRowCntStatement(connection, table)); + table = + JdbcSourceTable.builder() + .query("SELECT * FROM source_rows WHERE id <= 2") + .catalogTable(catalog) + .build(); + Assertions.assertEquals(2L, dialect.approximateRowCntStatement(connection, table)); + try (PreparedStatement query = + dialect.creatPreparedStatement( + connection, "SELECT * FROM source_rows ORDER BY id", 1); + ResultSet rows = query.executeQuery()) { + Assertions.assertTrue(rows.next()); + SeaTunnelRow row = dialect.getRowConverter().toInternal(rows, schema); + Assertions.assertEquals(new BigDecimal("12.34"), row.getField(2)); + Assertions.assertEquals(true, row.getField(3)); + Assertions.assertArrayEquals(new byte[] {97, 98, 99}, (byte[]) row.getField(4)); + Assertions.assertEquals("00000000-0000-0000-0000-000000000001", row.getField(5)); + } + } + } + + @Test + void readsCursorWithFreshConnectionMetadataCache() throws Exception { + TableSchema schema; + try (Connection discovery = connect()) { + schema = + CatalogUtils.getCatalogTable( + discovery, + "SELECT * FROM source_rows", + dialect.getJdbcDialectTypeMapper()) + .getTableSchema(); + } + assertFreshCursor(schema); + TableSchema explicitSchema = + TableSchema.builder() + .columns( + schema.getColumns().stream() + .map( + column -> + (Column) + PhysicalColumn.builder() + .name(column.getName()) + .dataType( + column + .getDataType()) + .build()) + .collect(Collectors.toList())) + .build(); + assertFreshCursor(explicitSchema); + } + + private void assertFreshCursor(TableSchema schema) throws Exception { + // The source reader uses a different connection from schema discovery. A cached + // PostgreSQL metadata lookup would hide additional queries made while a cursor is open. + try (Connection reader = connect(); + PreparedStatement query = + dialect.creatPreparedStatement( + reader, "SELECT * FROM source_rows ORDER BY id", 1); + ResultSet rows = query.executeQuery()) { + int count = 0; + while (rows.next()) { + SeaTunnelRow row = dialect.getRowConverter().toInternal(rows, schema); + Assertions.assertEquals(++count, ((Long) row.getField(0)).intValue()); + if (count == 1) { + Assertions.assertEquals("alpha", row.getField(1)); + Assertions.assertEquals(new BigDecimal("12.34"), row.getField(2)); + Assertions.assertEquals(true, row.getField(3)); + Assertions.assertArrayEquals(new byte[] {97, 98, 99}, (byte[]) row.getField(4)); + Assertions.assertEquals( + "00000000-0000-0000-0000-000000000001", row.getField(5)); + } else if (count == 3) { + for (int field = 1; field < 6; field++) { + Assertions.assertNull(row.getField(field)); + } + } + } + Assertions.assertEquals(4, count); + } + } + + @Test + void upsertHonorsSecondaryUniqueKeyAndPreservesOmittedColumn() throws Exception { + try (Connection connection = connect()) { + TableSchema schema = + CatalogUtils.getCatalogTable( + connection, + "SELECT id,email,value FROM upsert_rows", + dialect.getJdbcDialectTypeMapper()) + .getTableSchema(); + String sql = + dialect.getUpsertStatement( + "defaultdb", + "public.upsert_rows", + schema.getFieldNames(), + new String[] {"email"}) + .get(); + try (PreparedStatement statement = + FieldNamedPreparedStatement.prepareStatement( + connection, sql, schema.getFieldNames())) { + dialect.getRowConverter() + .toExternal( + schema, + null, + new SeaTunnelRow(new Object[] {2L, "a", "after"}), + statement); + statement.executeUpdate(); + statement.executeUpdate(); + } + try (Statement statement = connection.createStatement(); + ResultSet rows = statement.executeQuery("SELECT * FROM upsert_rows")) { + Assertions.assertTrue(rows.next()); + Assertions.assertEquals(2L, rows.getLong("id")); + Assertions.assertEquals("after", rows.getString("value")); + Assertions.assertEquals("keep", rows.getString("preserved")); + Assertions.assertFalse(rows.next()); + } + String invalidTarget = + dialect.getUpsertStatement( + "defaultdb", + "public.upsert_rows", + schema.getFieldNames(), + new String[] {"value"}) + .get(); + try (PreparedStatement statement = + FieldNamedPreparedStatement.prepareStatement( + connection, invalidTarget, schema.getFieldNames())) { + dialect.getRowConverter() + .toExternal( + schema, + null, + new SeaTunnelRow(new Object[] {3L, "b", "value"}), + statement); + Assertions.assertThrows(SQLException.class, statement::executeUpdate); + } + Assertions.assertEquals( + PostgresDialect.class, JdbcDialectLoader.load(url, null, "").getClass()); + } + } +} diff --git a/seatunnel-e2e/seatunnel-connector-v2-e2e/connector-jdbc-e2e/connector-jdbc-e2e-part-1/src/test/java/org/apache/seatunnel/connectors/seatunnel/jdbc/JdbcCockroachDBIT.java b/seatunnel-e2e/seatunnel-connector-v2-e2e/connector-jdbc-e2e/connector-jdbc-e2e-part-1/src/test/java/org/apache/seatunnel/connectors/seatunnel/jdbc/JdbcCockroachDBIT.java new file mode 100644 index 0000000000..edb27a14f8 --- /dev/null +++ b/seatunnel-e2e/seatunnel-connector-v2-e2e/connector-jdbc-e2e/connector-jdbc-e2e-part-1/src/test/java/org/apache/seatunnel/connectors/seatunnel/jdbc/JdbcCockroachDBIT.java @@ -0,0 +1,136 @@ +/* + * 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.seatunnel.connectors.seatunnel.jdbc; + +import org.apache.seatunnel.e2e.common.TestResource; +import org.apache.seatunnel.e2e.common.TestSuiteBase; +import org.apache.seatunnel.e2e.common.container.ContainerExtendedFactory; +import org.apache.seatunnel.e2e.common.container.TestContainer; +import org.apache.seatunnel.e2e.common.junit.TestContainerExtension; +import org.apache.seatunnel.e2e.common.util.DependencyJar; + +import org.junit.jupiter.api.AfterAll; +import org.junit.jupiter.api.Assertions; +import org.junit.jupiter.api.BeforeAll; +import org.junit.jupiter.api.BeforeEach; +import org.junit.jupiter.api.TestTemplate; +import org.testcontainers.containers.Container; +import org.testcontainers.containers.GenericContainer; +import org.testcontainers.containers.wait.strategy.Wait; + +import java.sql.Connection; +import java.sql.DriverManager; +import java.sql.ResultSet; +import java.sql.Statement; +import java.time.Duration; + +public class JdbcCockroachDBIT extends TestSuiteBase implements TestResource { + private GenericContainer<?> database; + + @TestContainerExtension + private final ContainerExtendedFactory extendedFactory = + container -> + DependencyJar.ofClassName("org.postgresql.Driver") + .copyTo(container, "/tmp/seatunnel/plugins/Jdbc/lib"); + + @BeforeAll + @Override + public void startUp() throws Exception { + database = + new GenericContainer<>("cockroachdb/cockroach:v24.3.0") + .withCreateContainerCmdModifier( + cmd -> cmd.withEntrypoint("/cockroach/cockroach")) + .withCommand( + "start-single-node", + "--insecure", + "--store=type=mem,size=1GiB", + "--cache=64MiB", + "--max-sql-memory=128MiB") + .withNetwork(NETWORK) + .withNetworkAliases("cockroachdb") + .withExposedPorts(26257) + .waitingFor( + Wait.forLogMessage(".*node startup completed.*", 1) + .withStartupTimeout(Duration.ofMinutes(2))); + database.start(); + try (Connection connection = connect(); + Statement statement = connection.createStatement()) { + statement.execute( + "CREATE TABLE source_rows (id INT8 PRIMARY KEY, name STRING, amount DECIMAL(12,2))"); + statement.execute( + "INSERT INTO source_rows VALUES (1,'alpha',12.34),(2,'你好',-9.50),(3,NULL,NULL),(4,'',0)"); + statement.execute( + "CREATE TABLE sink_rows (id INT8 PRIMARY KEY, name STRING, amount DECIMAL(12,2), preserved STRING DEFAULT 'default')"); + } + } + + @BeforeEach + void resetTarget() throws Exception { + try (Connection connection = connect(); + Statement statement = connection.createStatement()) { + statement.execute("DELETE FROM sink_rows"); + statement.execute("INSERT INTO sink_rows VALUES (1,'before',0,'keep')"); + } + } + + @TestTemplate + void readsHashPartitionsAndUpserts(TestContainer container) throws Exception { + Container.ExecResult result = container.executeJob("/jdbc_cockroachdb_source_sink.conf"); + Assertions.assertEquals(0, result.getExitCode(), result.getStderr()); + // Replaying the bounded input must update existing keys rather than insert duplicates. + result = container.executeJob("/jdbc_cockroachdb_source_sink.conf"); + Assertions.assertEquals(0, result.getExitCode(), result.getStderr()); + try (Connection connection = connect(); + Statement statement = connection.createStatement()) { + try (ResultSet rows = + statement.executeQuery( + "SELECT count(*) FROM ((SELECT id,name,amount FROM source_rows EXCEPT SELECT id,name,amount FROM sink_rows) UNION ALL (SELECT id,name,amount FROM sink_rows EXCEPT SELECT id,name,amount FROM source_rows)) AS differences")) { + Assertions.assertTrue(rows.next()); + Assertions.assertEquals(0, rows.getLong(1)); + } + try (ResultSet rows = + statement.executeQuery("SELECT id,preserved FROM sink_rows ORDER BY id")) { + for (int id = 1; id <= 4; id++) { + Assertions.assertTrue(rows.next()); + Assertions.assertEquals(id, rows.getLong(1)); + Assertions.assertEquals(id == 1 ? "keep" : "default", rows.getString(2)); + } + Assertions.assertFalse(rows.next()); + } + } + } + + private Connection connect() throws Exception { + return DriverManager.getConnection( + "jdbc:postgresql://" + + database.getHost() + + ":" + + database.getMappedPort(26257) + + "/defaultdb?sslmode=disable", + "root", + ""); + } + + @AfterAll + @Override + public void tearDown() { + if (database != null) { + database.stop(); + } + } +} diff --git a/seatunnel-e2e/seatunnel-connector-v2-e2e/connector-jdbc-e2e/connector-jdbc-e2e-part-1/src/test/resources/jdbc_cockroachdb_source_sink.conf b/seatunnel-e2e/seatunnel-connector-v2-e2e/connector-jdbc-e2e/connector-jdbc-e2e-part-1/src/test/resources/jdbc_cockroachdb_source_sink.conf new file mode 100644 index 0000000000..87c1c995c0 --- /dev/null +++ b/seatunnel-e2e/seatunnel-connector-v2-e2e/connector-jdbc-e2e/connector-jdbc-e2e-part-1/src/test/resources/jdbc_cockroachdb_source_sink.conf @@ -0,0 +1,51 @@ +# +# 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. +# + +env { + parallelism = 2 + job.mode = "BATCH" +} + +source { + Jdbc { + url = "jdbc:postgresql://cockroachdb:26257/defaultdb?sslmode=disable" + driver = "org.postgresql.Driver" + dialect = "CockroachDB" + username = "root" + password = "" + query = "SELECT id,name,amount FROM source_rows" + partition_column = "name" + partition_num = 3 + split.string-strategy = "hash" + fetch_size = 1 + } +} + +sink { + Jdbc { + url = "jdbc:postgresql://cockroachdb:26257/defaultdb?sslmode=disable" + driver = "org.postgresql.Driver" + dialect = "CockroachDB" + username = "root" + password = "" + database = "defaultdb" + table = "public.sink_rows" + primary_keys = ["id"] + generate_sink_sql = true + batch_size = 2 + } +}
