This is an automated email from the ASF dual-hosted git repository.
dockerzhang pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/inlong.git
The following commit(s) were added to refs/heads/master by this push:
new cab63a8eea [INLONG-8197][Manager] Optimize the ClickHouse query for
the Audit interface (#8198)
cab63a8eea is described below
commit cab63a8eea6c0f4bf3d30ce245b7e1beee42504d
Author: Hao <[email protected]>
AuthorDate: Mon Jun 12 17:44:36 2023 +0800
[INLONG-8197][Manager] Optimize the ClickHouse query for the Audit
interface (#8198)
---
.../service/core/impl/AuditServiceImpl.java | 36 +++++++++++++++-------
1 file changed, 25 insertions(+), 11 deletions(-)
diff --git
a/inlong-manager/manager-service/src/main/java/org/apache/inlong/manager/service/core/impl/AuditServiceImpl.java
b/inlong-manager/manager-service/src/main/java/org/apache/inlong/manager/service/core/impl/AuditServiceImpl.java
index 0cddccb59e..80fc900b01 100644
---
a/inlong-manager/manager-service/src/main/java/org/apache/inlong/manager/service/core/impl/AuditServiceImpl.java
+++
b/inlong-manager/manager-service/src/main/java/org/apache/inlong/manager/service/core/impl/AuditServiceImpl.java
@@ -67,8 +67,9 @@ import javax.annotation.PostConstruct;
import java.math.BigDecimal;
import java.sql.Connection;
+import java.sql.PreparedStatement;
import java.sql.ResultSet;
-import java.sql.Statement;
+import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
@@ -239,9 +240,9 @@ public class AuditServiceImpl implements AuditService {
}
} else if (AuditQuerySource.CLICKHOUSE == querySource) {
try (Connection connection =
ClickHouseConfig.getCkConnection();
- Statement statement = connection.createStatement();
- ResultSet resultSet = statement.executeQuery(
- toAuditCkSql(groupId, streamId, auditId,
request.getDt()))) {
+ PreparedStatement statement =
+ getAuditCkStatement(connection, groupId,
streamId, auditId, request.getDt());
+ ResultSet resultSet = statement.executeQuery()) {
List<AuditInfo> auditSet = new ArrayList<>();
while (resultSet.next()) {
AuditInfo vo = new AuditInfo();
@@ -308,28 +309,41 @@ public class AuditServiceImpl implements AuditService {
}
/**
- * Convert to clickhouse search sql
+ * Get clickhouse Statement
*
+ * @param connection The ClickHouse connection
* @param groupId The groupId of inlong
* @param streamId The streamId of inlong
* @param auditId The auditId of request
* @param dt The datetime of request
- * @return clickhouse sql
+ * @return The clickhouse Statement
*/
- private String toAuditCkSql(String groupId, String streamId, String
auditId, String dt) {
+ private PreparedStatement getAuditCkStatement(Connection connection,
String groupId, String streamId,
+ String auditId, String dt) throws SQLException {
DateTimeFormatter formatter = DateTimeFormat.forPattern(DAY_FORMAT);
DateTime date = formatter.parseDateTime(dt);
String startDate = date.toString(SECOND_FORMAT);
String endDate = date.plusDays(1).toString(SECOND_FORMAT);
- return new SQL()
+
+ String sql = new SQL()
.SELECT("log_ts", "sum(count) as total")
.FROM("audit_data")
- .WHERE("inlong_group_id = '" + groupId + "'",
"inlong_stream_id = '" + streamId + "'",
- "audit_id = '" + auditId + "'")
- .WHERE("log_ts >= '" + startDate + "'", "log_ts < '" + endDate
+ "'")
+ .WHERE("inlong_group_id = ?")
+ .WHERE("inlong_stream_id = ?")
+ .WHERE("audit_id = ?")
+ .WHERE("log_ts >= ?")
+ .WHERE("log_ts < ?")
.GROUP_BY("log_ts")
.ORDER_BY("log_ts")
.toString();
+
+ PreparedStatement statement = connection.prepareStatement(sql);
+ statement.setString(1, groupId);
+ statement.setString(2, streamId);
+ statement.setString(3, auditId);
+ statement.setString(4, startDate);
+ statement.setString(5, endDate);
+ return statement;
}
/**
