This is an automated email from the ASF dual-hosted git repository.
jiafengzheng pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/incubator-doris-manager.git
The following commit(s) were added to refs/heads/master by this push:
new cee3117 Add sql query api for sql editor (#20)
cee3117 is described below
commit cee3117f2b74901f5b31370795e7d9d9b3eee08f
Author: songchuanyuan <[email protected]>
AuthorDate: Fri Mar 25 14:34:18 2022 +0800
Add sql query api for sql editor (#20)
Add sql query api for sql editor
---
.../construct/NativeQueryController.java | 11 ++
.../doris/stack/driver/DorisFieldTypeDef.java | 87 +++++++++++++
.../doris/stack/driver/JdbcSampleClient.java | 49 ++++---
.../apache/doris/stack/driver/SimpleColumn.java | 34 +++++
.../apache/doris/stack/driver/SqlResultData.java | 35 +++++
.../stack/model/request/construct/SqlQueryReq.java | 31 +++++
.../model/response/construct/SqlQueryResp.java | 143 +++++++++++++++++++++
.../service/construct/NativeQueryService.java | 46 ++++++-
8 files changed, 411 insertions(+), 25 deletions(-)
diff --git
a/manager/manager/src/main/java/org/apache/doris/stack/controller/construct/NativeQueryController.java
b/manager/manager/src/main/java/org/apache/doris/stack/controller/construct/NativeQueryController.java
index bb2ea86..b638ad6 100644
---
a/manager/manager/src/main/java/org/apache/doris/stack/controller/construct/NativeQueryController.java
+++
b/manager/manager/src/main/java/org/apache/doris/stack/controller/construct/NativeQueryController.java
@@ -19,6 +19,7 @@ package org.apache.doris.stack.controller.construct;
import org.apache.doris.stack.controller.BaseController;
import org.apache.doris.stack.entity.CoreUserEntity;
+import org.apache.doris.stack.model.request.construct.SqlQueryReq;
import org.apache.doris.stack.rest.ResponseEntityBuilder;
import org.apache.doris.stack.service.user.AuthenticationService;
import org.apache.doris.stack.service.construct.NativeQueryService;
@@ -58,4 +59,14 @@ public class NativeQueryController extends BaseController {
CoreUserEntity user =
authenticationService.checkNewUserAuthWithCookie(request, response);
return ResponseEntityBuilder.ok(nativeQueryService.executeSql(nsId,
dbId, stmtBody, user));
}
+
+ @ApiOperation(value = "Execute SQL query")
+ @PostMapping(value = "sql", produces = MediaType.APPLICATION_JSON_VALUE)
+ public Object query(HttpServletRequest request,
+ HttpServletResponse response,
+ @RequestBody SqlQueryReq queryReq) throws Exception {
+ log.debug("query request.");
+ CoreUserEntity user =
authenticationService.checkNewUserAuthWithCookie(request, response);
+ return ResponseEntityBuilder.ok(nativeQueryService.querySql(user,
queryReq));
+ }
}
diff --git
a/manager/manager/src/main/java/org/apache/doris/stack/driver/DorisFieldTypeDef.java
b/manager/manager/src/main/java/org/apache/doris/stack/driver/DorisFieldTypeDef.java
new file mode 100644
index 0000000..88d4243
--- /dev/null
+++
b/manager/manager/src/main/java/org/apache/doris/stack/driver/DorisFieldTypeDef.java
@@ -0,0 +1,87 @@
+// 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.doris.stack.driver;
+
+import java.util.HashMap;
+import java.util.HashSet;
+import java.util.Map;
+import java.util.Set;
+
+public class DorisFieldTypeDef {
+
+ public static final String TYPE_INTEGER = "type/Integer";
+
+ public static final String TYPE_BIGINTEGER = "type/BigInteger";
+
+ public static final String TYPE_FLOAT = "type/Float";
+
+ public static final String TYPE_DECIMAL = "type/Decimal";
+
+ public static final String TYPE_TEXT = "type/Text";
+
+ public static final String TYPE_DATETIME = "type/DateTime";
+
+ public static final String TYPE_DATE = "type/Date";
+
+ public static final String TYPE_BOOLEAN = "type/Boolean";
+
+ public static final String TYPE_ALL = "type/*";
+
+ public static Map<String, String> dorisFieldTypeMap = new HashMap<>();
+
+ // Doris engine field type definition and mapping
+ public static Set<String> dorisFieldType = new HashSet<>();
+
+ static {
+ // Doris engine field type definition and mapping with structured
field types
+ dorisFieldTypeMap.put("TINYINT", TYPE_ALL);
+ dorisFieldTypeMap.put("DATE", TYPE_DATE);
+ dorisFieldTypeMap.put("INT", TYPE_INTEGER);
+ dorisFieldTypeMap.put("SMALLINT", TYPE_INTEGER);
+ dorisFieldTypeMap.put("VARCHAR", TYPE_TEXT);
+ dorisFieldTypeMap.put("DATETIME", TYPE_DATETIME);
+ dorisFieldTypeMap.put("BOOLEAN", TYPE_BOOLEAN);
+ dorisFieldTypeMap.put("BIT", TYPE_BOOLEAN);
+ dorisFieldTypeMap.put("DOUBLE", TYPE_FLOAT);
+ dorisFieldTypeMap.put("FLOAT", TYPE_FLOAT);
+ dorisFieldTypeMap.put("DECIMAL", TYPE_DECIMAL);
+ dorisFieldTypeMap.put("BIGINT", TYPE_BIGINTEGER);
+ dorisFieldTypeMap.put("LARGEINT", TYPE_TEXT); // TODO：At present,
largeint is set as text type to solve
+ // the problem of
precision
+ dorisFieldTypeMap.put("BITMAP", TYPE_ALL);
+ dorisFieldTypeMap.put("CHAR", TYPE_TEXT);
+ dorisFieldTypeMap.put("HLL", TYPE_ALL); // TODO：Currently set HLL as
text type
+ // Field type of Doris engine
+ dorisFieldType.addAll(dorisFieldTypeMap.keySet());
+ }
+
+ /**
+ * Get Doris mapping data type
+ *
+ * @param dorisType dorisType
+ * @return dorisType
+ */
+ public static String getDorisFieldType(String dorisType) {
+ String type = dorisType.replaceAll("[^a-zA-Z]", "");
+ String result = dorisFieldTypeMap.get(type);
+ if (result == null) {
+ return TYPE_ALL;
+ }
+ return result;
+ }
+}
diff --git
a/manager/manager/src/main/java/org/apache/doris/stack/driver/JdbcSampleClient.java
b/manager/manager/src/main/java/org/apache/doris/stack/driver/JdbcSampleClient.java
index 33a1344..73d8004 100644
---
a/manager/manager/src/main/java/org/apache/doris/stack/driver/JdbcSampleClient.java
+++
b/manager/manager/src/main/java/org/apache/doris/stack/driver/JdbcSampleClient.java
@@ -94,6 +94,26 @@ public class JdbcSampleClient {
}
}
+ public Statement getStatement(String host, int port, String user, String
passwd, String db) throws Exception {
+ StringBuffer buffer = new StringBuffer();
+ buffer.append("jdbc:mysql://");
+ buffer.append(host);
+ buffer.append(":");
+ buffer.append(port);
+ buffer.append("/");
+ buffer.append(db);
+ String url = buffer.toString();
+ try {
+ log.info("Get connection by url:{}", url);
+ Connection myCon = DriverManager.getConnection(url, user, passwd);
+ Statement stmt = myCon.createStatement();
+ return stmt;
+ } catch (Exception e) {
+ log.error("Get doris jdbc connection error {}.", e);
+ throw e;
+ }
+ }
+
public void updateUserPassword(String user, String newPassword, Statement
stmt) throws Exception {
try {
String sql = "SET PASSWORD FOR '" + user
@@ -182,24 +202,17 @@ public class JdbcSampleClient {
}
}
-// public List<String> getBeIps(Statement stmt) throws Exception {
-// try {
-// String sql = "SHOW PROC '/backends';";
-//
-// ResultSet result = stmt.executeQuery(sql);
-// List<String> ips = new ArrayList<>();
-// while (result.next()) {
-// boolean isAlive = result.getBoolean("Alive");
-// if (isAlive) {
-// ips.add(result.getString("IP"));
-// }
-// }
-// return ips;
-// } catch (Exception e) {
-// log.error("get be ip by jdbc error {}.", e);
-// throw e;
-// }
-// }
+ // TODO: At present, only the simplest SQL query is implemented, there is
no cache connection,
+ // and multiple SQL statements are not supported
+ public ResultSet executeSql(Statement stmt, String sql) throws Exception {
+ try {
+ ResultSet result = stmt.executeQuery(sql);
+ return result;
+ } catch (Exception e) {
+ log.error("execute sql error {}.", e);
+ throw e;
+ }
+ }
public void closeStatement(Statement stmt) {
try {
diff --git
a/manager/manager/src/main/java/org/apache/doris/stack/driver/SimpleColumn.java
b/manager/manager/src/main/java/org/apache/doris/stack/driver/SimpleColumn.java
new file mode 100644
index 0000000..3d9ab21
--- /dev/null
+++
b/manager/manager/src/main/java/org/apache/doris/stack/driver/SimpleColumn.java
@@ -0,0 +1,34 @@
+// 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.doris.stack.driver;
+
+import lombok.AllArgsConstructor;
+import lombok.Data;
+import lombok.NoArgsConstructor;
+
+@NoArgsConstructor
+@AllArgsConstructor
+@Data
+public class SimpleColumn {
+ private String displayName;
+
+ private String name;
+
+ // baseType, The unified type of front-end structured display field
+ private String baseType;
+}
diff --git
a/manager/manager/src/main/java/org/apache/doris/stack/driver/SqlResultData.java
b/manager/manager/src/main/java/org/apache/doris/stack/driver/SqlResultData.java
new file mode 100644
index 0000000..31681f5
--- /dev/null
+++
b/manager/manager/src/main/java/org/apache/doris/stack/driver/SqlResultData.java
@@ -0,0 +1,35 @@
+// 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.doris.stack.driver;
+
+import lombok.AllArgsConstructor;
+import lombok.Data;
+import lombok.NoArgsConstructor;
+
+import java.util.List;
+
+@Data
+@AllArgsConstructor
+@NoArgsConstructor
+public class SqlResultData {
+ // Each row of returned result data is a list < Object >, which may be of
different types.
+ // It is determined according to each column defined in cols
+ private List<List<Object>> rows;
+
+ private List<SimpleColumn> cols;
+}
diff --git
a/manager/manager/src/main/java/org/apache/doris/stack/model/request/construct/SqlQueryReq.java
b/manager/manager/src/main/java/org/apache/doris/stack/model/request/construct/SqlQueryReq.java
new file mode 100644
index 0000000..3619fa3
--- /dev/null
+++
b/manager/manager/src/main/java/org/apache/doris/stack/model/request/construct/SqlQueryReq.java
@@ -0,0 +1,31 @@
+// 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.doris.stack.model.request.construct;
+
+import lombok.AllArgsConstructor;
+import lombok.Data;
+import lombok.NoArgsConstructor;
+
+@Data
+@AllArgsConstructor
+@NoArgsConstructor
+public class SqlQueryReq {
+ private int database;
+
+ private String query;
+}
diff --git
a/manager/manager/src/main/java/org/apache/doris/stack/model/response/construct/SqlQueryResp.java
b/manager/manager/src/main/java/org/apache/doris/stack/model/response/construct/SqlQueryResp.java
new file mode 100644
index 0000000..fdb2c97
--- /dev/null
+++
b/manager/manager/src/main/java/org/apache/doris/stack/model/response/construct/SqlQueryResp.java
@@ -0,0 +1,143 @@
+// 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.doris.stack.model.response.construct;
+
+import lombok.AllArgsConstructor;
+import lombok.Data;
+import lombok.NoArgsConstructor;
+import org.apache.doris.stack.driver.DorisFieldTypeDef;
+import org.apache.doris.stack.driver.SimpleColumn;
+import org.apache.doris.stack.driver.SqlResultData;
+import org.apache.doris.stack.model.request.construct.SqlQueryReq;
+
+import java.io.PrintWriter;
+import java.io.StringWriter;
+import java.sql.ResultSet;
+import java.sql.ResultSetMetaData;
+import java.sql.Timestamp;
+import java.util.ArrayList;
+import java.util.HashSet;
+import java.util.List;
+import java.util.Set;
+
+@Data
+@AllArgsConstructor
+@NoArgsConstructor
+public class SqlQueryResp {
+ // query result
+ private SqlResultData data;
+
+ // database_id
+ private int databaseId;
+
+ // started_at
+ private Timestamp startedAt;
+
+ // json_query
+ private SqlQueryReq jsonQuery;
+
+ private Status status;
+
+ // row_count
+ private int rowCount;
+
+ // running_time,Run time, MS
+ private long runningTime;
+
+ // class，Error class name
+ private String className;
+
+ // Detailed error information
+ private String stacktrace;
+
+ // Cause of error
+ private String error;
+
+ public void failedInfoSet(Exception e) {
+ this.setStatus(Status.failed);
+ this.setClassName(e.getClass().getName());
+
+ StringWriter sw = new StringWriter();
+ PrintWriter pw = new PrintWriter(sw);
+ e.printStackTrace(pw);
+ this.setStacktrace(sw.toString());
+
+ if (e.getMessage() == null || e.getMessage().isEmpty()) {
+ this.setError("Query unknown error, please check the error stack
details");
+ } else {
+ this.setError(e.getMessage());
+ }
+ }
+
+ public void resultDataSet(ResultSet resultSet) throws Exception {
+
+ SqlResultData resultData = new SqlResultData();
+
+ // Acquiring Metadata
+ ResultSetMetaData meta = resultSet.getMetaData();
+ int colCount = meta.getColumnCount();
+
+ // Processing metadata information
+ // TODO: The data of bigint type is specially processed. The string
type is returned to the front end
+ Set<Integer> bigintCloum = new HashSet<>();
+ List<SimpleColumn> cols = new ArrayList<>();
+ for (int i = 1; i <= colCount; i++) {
+ SimpleColumn column = new SimpleColumn();
+ String columnName = meta.getColumnName(i);
+ column.setName(columnName);
+ column.setDisplayName(columnName);
+
+ String columnTypeName = meta.getColumnTypeName(i);
+
+ String baseType =
DorisFieldTypeDef.getDorisFieldType(columnTypeName);
+
+ if (DorisFieldTypeDef.TYPE_BIGINTEGER.equals(baseType)) {
+ bigintCloum.add(i);
+ }
+ column.setBaseType(baseType);
+ cols.add(column);
+ }
+ resultData.setCols(cols);
+
+ // Get all result data
+ List<List<Object>> rows = new ArrayList<>();
+ while (resultSet.next()) {
+ List<Object> row = new ArrayList<>();
+ for (int i = 1; i <= colCount; i++) {
+ if (bigintCloum.contains(i)) {
+ row.add(resultSet.getString(i));
+ } else {
+ row.add(resultSet.getObject(i));
+ }
+ }
+ rows.add(row);
+ }
+ resultData.setRows(rows);
+
+ this.setData(resultData);
+ this.setRowCount(rows.size());
+ }
+
+ /**
+ * Status
+ */
+ public enum Status {
+ completed,
+ failed
+ }
+}
diff --git
a/manager/manager/src/main/java/org/apache/doris/stack/service/construct/NativeQueryService.java
b/manager/manager/src/main/java/org/apache/doris/stack/service/construct/NativeQueryService.java
index 61323e2..cda0ec0 100644
---
a/manager/manager/src/main/java/org/apache/doris/stack/service/construct/NativeQueryService.java
+++
b/manager/manager/src/main/java/org/apache/doris/stack/service/construct/NativeQueryService.java
@@ -18,36 +18,44 @@
package org.apache.doris.stack.service.construct;
import org.apache.doris.stack.constant.ConstantDef;
+import org.apache.doris.stack.dao.ManagerDatabaseRepository;
+import org.apache.doris.stack.driver.JdbcSampleClient;
import org.apache.doris.stack.entity.CoreUserEntity;
+import org.apache.doris.stack.model.request.construct.SqlQueryReq;
import org.apache.doris.stack.model.response.construct.NativeQueryResp;
import org.apache.doris.stack.component.ClusterUserComponent;
import org.apache.doris.stack.component.DatabuildComponent;
import org.apache.doris.stack.connector.PaloQueryClient;
import org.apache.doris.stack.entity.ClusterInfoEntity;
import org.apache.doris.stack.entity.ManagerDatabaseEntity;
+import org.apache.doris.stack.model.response.construct.SqlQueryResp;
import org.apache.doris.stack.service.BaseService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
+import java.sql.ResultSet;
+import java.sql.Statement;
+import java.sql.Timestamp;
+
@Service
@Slf4j
public class NativeQueryService extends BaseService {
+ @Autowired
private PaloQueryClient paloQueryClient;
+ @Autowired
private ClusterUserComponent clusterUserComponent;
+ @Autowired
private DatabuildComponent databuildComponent;
@Autowired
- public NativeQueryService(PaloQueryClient paloQueryClient,
- ClusterUserComponent clusterUserComponent,
- DatabuildComponent databuildComponent) {
- this.clusterUserComponent = clusterUserComponent;
- this.paloQueryClient = paloQueryClient;
- this.databuildComponent = databuildComponent;
- }
+ private JdbcSampleClient jdbcSampleClient;
+
+ @Autowired
+ private ManagerDatabaseRepository databaseRepository;
/**
* Implement SQL query through Doris HTTP protocol
@@ -93,4 +101,28 @@ public class NativeQueryService extends BaseService {
ClusterInfoEntity clusterInfo) throws
Exception {
return paloQueryClient.executeSQL(sql, ConstantDef.DORIS_DEFAULT_NS,
dbName, clusterInfo);
}
+
+ public SqlQueryResp querySql(CoreUserEntity user, SqlQueryReq queryReq)
throws Exception {
+ ClusterInfoEntity clusterInfo =
clusterUserComponent.getUserCurrentClusterAndCheckAdmin(user);
+ ManagerDatabaseEntity databaseEntity =
databaseRepository.findById(queryReq.getDatabase()).get();
+
+ SqlQueryResp resp = new SqlQueryResp();
+ long startTime = System.currentTimeMillis();
+ resp.setStartedAt(new Timestamp(startTime));
+ resp.setDatabaseId(queryReq.getDatabase());
+ resp.setJsonQuery(queryReq);
+
+ try {
+ Statement statement =
jdbcSampleClient.getStatement(clusterInfo.getAddress(),
clusterInfo.getQueryPort(),
+ clusterInfo.getUser(), clusterInfo.getPasswd(),
databaseEntity.getName());
+ ResultSet result = jdbcSampleClient.executeSql(statement,
queryReq.getQuery());
+ jdbcSampleClient.closeStatement(statement);
+ resp.resultDataSet(result);
+ } catch (Exception e) {
+ log.error("Query sql error.");
+ resp.failedInfoSet(e);
+ }
+ resp.setRunningTime(System.currentTimeMillis() - startTime);
+ return resp;
+ }
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
