You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@seatunnel.apache.org by ga...@apache.org on 2024/01/23 02:35:15 UTC
(seatunnel) branch dev updated: [Improve][JDBC-sink] Improve query Approximate Total Row Count of a Table (#5972)
This is an automated email from the ASF dual-hosted git repository.
gaojun2048 pushed a commit to branch dev
in repository https://gitbox.apache.org/repos/asf/seatunnel.git
The following commit(s) were added to refs/heads/dev by this push:
new 8156036a2f [Improve][JDBC-sink] Improve query Approximate Total Row Count of a Table (#5972)
8156036a2f is described below
commit 8156036a2fe50b1352703ced3bdca31fda779bfc
Author: ic4y <83...@users.noreply.github.com>
AuthorDate: Tue Jan 23 10:35:08 2024 +0800
[Improve][JDBC-sink] Improve query Approximate Total Row Count of a Table (#5972)
---
.../connectors/seatunnel/jdbc/config/JdbcSourceConfig.java | 3 +--
.../seatunnel/jdbc/internal/dialect/mysql/MysqlDialect.java | 13 ++++++++++++-
.../jdbc/internal/dialect/oracle/OracleDialect.java | 13 ++++++++++++-
.../jdbc/internal/dialect/psql/PostgresDialect.java | 13 ++++++++++++-
.../jdbc/internal/dialect/sqlserver/SqlServerDialect.java | 13 ++++++++++++-
5 files changed, 49 insertions(+), 6 deletions(-)
diff --git a/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connectors/seatunnel/jdbc/config/JdbcSourceConfig.java b/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connectors/seatunnel/jdbc/config/JdbcSourceConfig.java
index 1f4bd29fc1..74c7801318 100644
--- a/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connectors/seatunnel/jdbc/config/JdbcSourceConfig.java
+++ b/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connectors/seatunnel/jdbc/config/JdbcSourceConfig.java
@@ -52,8 +52,7 @@ public class JdbcSourceConfig implements Serializable {
boolean isOldVersion =
config.getOptional(JdbcOptions.QUERY).isPresent()
- && config.getOptional(JdbcOptions.PARTITION_COLUMN).isPresent()
- && config.getOptional(JdbcOptions.PARTITION_NUM).isPresent();
+ && config.getOptional(JdbcOptions.PARTITION_COLUMN).isPresent();
builder.useDynamicSplitter(isOldVersion ? false : true);
builder.splitSize(config.get(JdbcSourceOptions.SPLIT_SIZE));
diff --git a/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connectors/seatunnel/jdbc/internal/dialect/mysql/MysqlDialect.java b/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connectors/seatunnel/jdbc/internal/dialect/mysql/MysqlDialect.java
index ec53dd0d6c..86531594e7 100644
--- a/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connectors/seatunnel/jdbc/internal/dialect/mysql/MysqlDialect.java
+++ b/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connectors/seatunnel/jdbc/internal/dialect/mysql/MysqlDialect.java
@@ -165,7 +165,18 @@ public class MysqlDialect implements JdbcDialect {
@Override
public Long approximateRowCntStatement(Connection connection, JdbcSourceTable table)
throws SQLException {
- if (StringUtils.isBlank(table.getQuery())) {
+
+ // 1. If no query is configured, use TABLE STATUS.
+ // 2. If a query is configured but does not contain a WHERE clause and tablePath is
+ // configured , use TABLE STATUS.
+ // 3. If a query is configured with a WHERE clause, or a query statement is configured but
+ // tablePath is not, use COUNT(*).
+
+ boolean useTableStats =
+ StringUtils.isBlank(table.getQuery())
+ || (!table.getQuery().toLowerCase().contains("where")
+ && table.getTablePath() != null);
+ if (useTableStats) {
// The statement used to get approximate row count which is less
// accurate than COUNT(*), but is more efficient for large table.
TablePath tablePath = table.getTablePath();
diff --git a/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connectors/seatunnel/jdbc/internal/dialect/oracle/OracleDialect.java b/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connectors/seatunnel/jdbc/internal/dialect/oracle/OracleDialect.java
index 9167fe07f4..1cfeb8d705 100644
--- a/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connectors/seatunnel/jdbc/internal/dialect/oracle/OracleDialect.java
+++ b/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connectors/seatunnel/jdbc/internal/dialect/oracle/OracleDialect.java
@@ -179,7 +179,18 @@ public class OracleDialect implements JdbcDialect {
@Override
public Long approximateRowCntStatement(Connection connection, JdbcSourceTable table)
throws SQLException {
- if (StringUtils.isBlank(table.getQuery())) {
+
+ // 1. If no query is configured, use TABLE STATUS.
+ // 2. If a query is configured but does not contain a WHERE clause and tablePath is
+ // configured, use TABLE STATUS.
+ // 3. If a query is configured with a WHERE clause, or a query statement is configured but
+ // tablePath is not, use COUNT(*).
+
+ boolean useTableStats =
+ StringUtils.isBlank(table.getQuery())
+ || (!table.getQuery().toLowerCase().contains("where")
+ && table.getTablePath() != null);
+ if (useTableStats) {
TablePath tablePath = table.getTablePath();
String analyzeTable =
String.format(
diff --git a/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connectors/seatunnel/jdbc/internal/dialect/psql/PostgresDialect.java b/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connectors/seatunnel/jdbc/internal/dialect/psql/PostgresDialect.java
index 57a803df91..d1bf6257ec 100644
--- a/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connectors/seatunnel/jdbc/internal/dialect/psql/PostgresDialect.java
+++ b/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connectors/seatunnel/jdbc/internal/dialect/psql/PostgresDialect.java
@@ -150,7 +150,18 @@ public class PostgresDialect implements JdbcDialect {
@Override
public Long approximateRowCntStatement(Connection connection, JdbcSourceTable table)
throws SQLException {
- if (StringUtils.isBlank(table.getQuery())) {
+
+ // 1. If no query is configured, use TABLE STATUS.
+ // 2. If a query is configured but does not contain a WHERE clause and tablePath is
+ // configured, use TABLE STATUS.
+ // 3. If a query is configured with a WHERE clause, or a query statement is configured but
+ // tablePath is not, use COUNT(*).
+
+ boolean useTableStats =
+ StringUtils.isBlank(table.getQuery())
+ || (!table.getQuery().toLowerCase().contains("where")
+ && table.getTablePath() != null);
+ if (useTableStats) {
String rowCountQuery =
String.format(
"SELECT reltuples FROM pg_class r WHERE relkind = 'r' AND relname = '%s';",
diff --git a/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connectors/seatunnel/jdbc/internal/dialect/sqlserver/SqlServerDialect.java b/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connectors/seatunnel/jdbc/internal/dialect/sqlserver/SqlServerDialect.java
index 729ba17949..8826e1fdc9 100644
--- a/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connectors/seatunnel/jdbc/internal/dialect/sqlserver/SqlServerDialect.java
+++ b/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connectors/seatunnel/jdbc/internal/dialect/sqlserver/SqlServerDialect.java
@@ -160,7 +160,18 @@ public class SqlServerDialect implements JdbcDialect {
@Override
public Long approximateRowCntStatement(Connection connection, JdbcSourceTable table)
throws SQLException {
- if (StringUtils.isBlank(table.getQuery())) {
+
+ // 1. If no query is configured, use TABLE STATUS.
+ // 2. If a query is configured but does not contain a WHERE clause and tablePath is
+ // configured, use TABLE STATUS.
+ // 3. If a query is configured with a WHERE clause, or a query statement is configured but
+ // tablePath is not, use COUNT(*).
+
+ boolean useTableStats =
+ StringUtils.isBlank(table.getQuery())
+ || (!table.getQuery().toLowerCase().contains("where")
+ && table.getTablePath() != null);
+ if (useTableStats) {
TablePath tablePath = table.getTablePath();
try (Statement stmt = connection.createStatement()) {
if (StringUtils.isNotBlank(tablePath.getDatabaseName())) {