Merge remote-tracking branch 'origin/develop' into develop

19 files changed, 378 insertions, 94 deletions

diff --git a/.gitignore b/.gitignore
new file mode 100644
index 0000000..ccdcd76
--- /dev/null
+++ b/.gitignore
@@ -0,0 +1,31 @@
+*/target/
+!.mvn/wrapper/maven-wrapper.jar
+
+.DS_Store
+
+### STS ###
+.apt_generated
+.classpath
+.factorypath
+.project
+.settings
+.springBeans
+.sts4-cache
+
+### IntelliJ IDEA ###
+.idea
+*.iws
+*.iml
+*.ipr
+/logs/
+
+### NetBeans ###
+/nbproject/private/
+/build/
+/nbbuild/
+/dist/
+/nbdist/
+/.nb-gradle/
+
+### Jrebel ###
+rebel*.xml
diff --git a/galaxy-auth-center/config/application-dev.properties b/galaxy-auth-center/config/application-dev.properties
index fe7fcfc..7596835 100644
--- a/galaxy-auth-center/config/application-dev.properties
+++ b/galaxy-auth-center/config/application-dev.properties
@@ -22,4 +22,6 @@ arango.username=query
 #Arango密码
 arango.password=ceiec2018
 #ArangoJWT授权地址
-arango.authurl=http://192.168.44.12:8529/_db/ip-learning-test/_open/auth
\ No newline at end of file
+arango.authurl=http://192.168.44.12:8529/_db/tsg_galaxy_v3/_open/auth
+#Arango数据查询地址
+arango.query.url=http://192.168.44.12:8529/_db/tsg_galaxy_v3/_api/cursor
\ No newline at end of file
diff --git a/galaxy-auth-center/config/application-prod.properties b/galaxy-auth-center/config/application-prod.properties
index fe7fcfc..7596835 100644
--- a/galaxy-auth-center/config/application-prod.properties
+++ b/galaxy-auth-center/config/application-prod.properties
@@ -22,4 +22,6 @@ arango.username=query
 #Arango密码
 arango.password=ceiec2018
 #ArangoJWT授权地址
-arango.authurl=http://192.168.44.12:8529/_db/ip-learning-test/_open/auth
\ No newline at end of file
+arango.authurl=http://192.168.44.12:8529/_db/tsg_galaxy_v3/_open/auth
+#Arango数据查询地址
+arango.query.url=http://192.168.44.12:8529/_db/tsg_galaxy_v3/_api/cursor
\ No newline at end of file
diff --git a/galaxy-auth-center/config/application-test.properties b/galaxy-auth-center/config/application-test.properties
index fe7fcfc..76c6e61 100644
--- a/galaxy-auth-center/config/application-test.properties
+++ b/galaxy-auth-center/config/application-test.properties
@@ -7,7 +7,7 @@ eureka.instance.lease-renewal-interval-in-seconds=10
 #健康检查页面的URL
 eureka.instance.health-check-url-path=/actuator/health
 #与Eureka注册服务中心的通信zone和url地址
-eureka.client.serviceUrl.defaultZone=http://admin:admin@localhost:8501/eureka/
+eureka.client.serviceUrl.defaultZone=http://admin:[email protected]:8501/eureka/,http://admin:[email protected]:8501/eureka/
 #该实例相较于hostname是否优先使用IP
 eureka.instance.prefer-ip-address=true
 #该实例注册到服务中心的唯一ID
@@ -22,4 +22,6 @@ arango.username=query
 #Arango密码
 arango.password=ceiec2018
 #ArangoJWT授权地址
-arango.authurl=http://192.168.44.12:8529/_db/ip-learning-test/_open/auth
\ No newline at end of file
+arango.authurl=http://192.168.44.12:8529/_db/tsg_galaxy_v3/_open/auth
+#Arango数据查询地址
+arango.query.url=http://192.168.44.12:8529/_db/tsg_galaxy_v3/_api/cursor
diff --git a/galaxy-auth-center/src/main/java/com/mesalab/auth/component/config/ArangoConfig.java b/galaxy-auth-center/src/main/java/com/mesalab/auth/component/config/ArangoConfig.java
new file mode 100644
index 0000000..717b755
--- /dev/null
+++ b/galaxy-auth-center/src/main/java/com/mesalab/auth/component/config/ArangoConfig.java
@@ -0,0 +1,28 @@
+package com.mesalab.auth.component.config;
+
+import lombok.Data;
+import org.springframework.beans.factory.annotation.Value;
+import org.springframework.stereotype.Component;
+
+/**
+ * @description: for http
+ * @author: zhq
+ * @create: 2020-07-07
+ **/
+@Data
+@Component
+public class ArangoConfig {
+
+    @Value("${arango.query.url}")
+    private String queryurl;
+
+    @Value("${arango.username}")
+    private String userName;
+
+    @Value("${arango.password}")
+    private String passWord;
+
+    @Value("${arango.authurl}")
+    private String authUrl;
+
+}
diff --git a/galaxy-auth-center/src/main/java/com/mesalab/auth/service/AuthorizeService.java b/galaxy-auth-center/src/main/java/com/mesalab/auth/service/AuthorizeService.java
index 69e710f..e04a974 100644
--- a/galaxy-auth-center/src/main/java/com/mesalab/auth/service/AuthorizeService.java
+++ b/galaxy-auth-center/src/main/java/com/mesalab/auth/service/AuthorizeService.java
@@ -17,4 +17,13 @@ public interface AuthorizeService {
      * @return: java.lang.String
      **/
     String arangoJwtLogin() throws BusinessException;
+
+    /**
+     * @Description: 验证ArangoJwtToken是否有效
+     * @Author: liuyongqiang
+     * @Date: 2020/8/27 18:07
+     * @param token:
+     * @return: boolean
+     **/
+    boolean tokenValidate(String token)throws BusinessException;
 }
diff --git a/galaxy-auth-center/src/main/java/com/mesalab/auth/service/impl/AuthorizeServiceImpl.java b/galaxy-auth-center/src/main/java/com/mesalab/auth/service/impl/AuthorizeServiceImpl.java
index 8d305b2..7c768fe 100644
--- a/galaxy-auth-center/src/main/java/com/mesalab/auth/service/impl/AuthorizeServiceImpl.java
+++ b/galaxy-auth-center/src/main/java/com/mesalab/auth/service/impl/AuthorizeServiceImpl.java
@@ -1,6 +1,8 @@
 package com.mesalab.auth.service.impl;
 
+import com.google.common.collect.ImmutableMap;
 import com.google.gson.Gson;
+import com.mesalab.auth.component.config.ArangoConfig;
 import com.mesalab.auth.service.AuthorizeService;
 import com.mesalab.common.annotation.SysLog;
 import com.mesalab.common.enums.CacheKeyEnum;
@@ -8,8 +10,12 @@ import com.mesalab.common.exception.BusinessException;
 import com.mesalab.common.util.HttpClientUtil;
 import com.mesalab.common.util.MemoryCache;
 import com.mesalab.common.util.SysConstant;
+import com.zdjizhi.utils.JsonMapper;
 import lombok.extern.slf4j.Slf4j;
-import org.springframework.beans.factory.annotation.Value;
+import org.apache.http.Header;
+import org.apache.http.message.BasicHeader;
+import org.springframework.beans.factory.annotation.Autowired;
+import org.springframework.http.HttpHeaders;
 import org.springframework.stereotype.Service;
 
 import java.util.HashMap;
@@ -27,12 +33,8 @@ import java.util.Objects;
 @Service("jwtAuthorizeService")
 public class AuthorizeServiceImpl implements AuthorizeService {
 
-    @Value("${arango.username}")
-    String arangoUserName;
-    @Value("${arango.password}")
-    String arangoPassWord;
-    @Value("${arango.authurl}")
-    String arangoAuthUrl;
+    @Autowired
+    ArangoConfig arangoConfig;
 
     /**
      * @Description: ArangoDBJwt登录
@@ -46,27 +48,58 @@ public class AuthorizeServiceImpl implements AuthorizeService {
 
         String cacheKey = CacheKeyEnum.ARONGO_JWT_KEY.getKey();
         Object cacheVal = MemoryCache.getInstance().get(cacheKey);
-        log.info("从缓存中获取的arangoJwt字符串为:{}",cacheVal);
-        if(Objects.nonNull(cacheVal))
-            return String.valueOf(cacheVal);
+        log.info("从缓存中获取的Token为:{}", cacheVal);
+        if (Objects.nonNull(cacheVal)) {
+            if (tokenValidate(String.valueOf(cacheVal))) {
+                log.info("从缓存中获取的Token未失效!");
+                return String.valueOf(cacheVal);
+            } else {
+                log.info("从缓存中获取的Token已失效!");
+            }
+        }
 
         Map<String, String> params = new HashMap<>();
-        params.put("username", arangoUserName);
-        params.put("password", arangoPassWord);
-        String jwtStr = null;
+        params.put("username", arangoConfig.getUserName());
+        params.put("password", arangoConfig.getPassWord());
+        String jwtStr;
 
         try {
-            String jsonRes = HttpClientUtil.httpPost(arangoAuthUrl,new Gson().toJson(params));
-            params = new Gson().fromJson(jsonRes,Map.class);
+            String jsonRes = HttpClientUtil.httpPost(arangoConfig.getAuthUrl(), new Gson().toJson(params));
+            params = new Gson().fromJson(jsonRes, Map.class);
             jwtStr = params.get(SysConstant.ARANGO_JWT_STR);
-            log.info("请求arango授权接口获取的jwt:{}",jwtStr);
+            log.info("请求arango授权接口获取的jwt:{}", jwtStr);
             //将jwt字符串做缓存处理,过期时间为7天
-            MemoryCache.getInstance().put(cacheKey,jwtStr,604800);
+            MemoryCache.getInstance().put(cacheKey, jwtStr, 604800);
         } catch (BusinessException e) {
-            log.info("请求arango授权接口发生异常,异常信息:{}",e.getMessage());
+            log.info("请求arango授权接口发生异常,异常信息:{}", e.getMessage());
             throw new BusinessException(e.getMessage());
         }
 
         return jwtStr;
     }
+
+    /**
+     * @param token:
+     * @Description: 验证ArangoJwtToken是否有效
+     * @Author: liuyongqiang
+     * @Date: 2020/8/27 18:07
+     * @return: boolean
+     **/
+    @Override
+    public boolean tokenValidate(String token) throws BusinessException {
+        Map queryMap = ImmutableMap.of("query", "");
+        String jwtStr = SysConstant.ARANGO_JWT_PRE.concat(token);
+        Header header = new BasicHeader(HttpHeaders.AUTHORIZATION, jwtStr);
+        try {
+            HttpClientUtil.httpPost(arangoConfig.getQueryurl(), JsonMapper.toJsonString(queryMap), header);
+        } catch (BusinessException e) {
+            if (e.getErrorMessage() != null && e.getErrorMessage().contains("not authorized to execute this request")) {
+                return false;
+            } else {
+                return true;
+            }
+        }
+        return true;
+    }
+
 }
diff --git a/galaxy-common/src/main/java/com/mesalab/common/dto/results/ArangoCursorResult.java b/galaxy-common/src/main/java/com/mesalab/common/dto/results/ArangoCursorResult.java
index 0536710..b0d55cf 100644
--- a/galaxy-common/src/main/java/com/mesalab/common/dto/results/ArangoCursorResult.java
+++ b/galaxy-common/src/main/java/com/mesalab/common/dto/results/ArangoCursorResult.java
@@ -23,6 +23,7 @@ public class ArangoCursorResult {
     private ExtraBean extra;
     private boolean error;
     private int code;
+    private int errorNum;
     private List<Map<String,Object>> result;
 
     @Data
diff --git a/galaxy-data-engine/config/application-dev.properties b/galaxy-data-engine/config/application-dev.properties
index a50514b..d244b1e 100644
--- a/galaxy-data-engine/config/application-dev.properties
+++ b/galaxy-data-engine/config/application-dev.properties
@@ -27,8 +27,8 @@ xxl.job.admin.url=http://192.168.44.12:8181/xxl-job-admin
 xxl.job.admin.username=admin
 xxl.job.admin.password=123456
 #最大的数据查询条数
-engine.maxCacheNum=1048576
-engine.defaultResultNum=200
+engine.maxCacheNum=500000
+engine.defaultResultNum=300000
 #Hbase数据库相关配置
 hbase.url=192.168.44.12:8084
 hbase.dbname=tsg
@@ -42,8 +42,8 @@ druid.sqlTimeZone=Asia/Shanghai
 #arango数据库相关配置
 arango.maxrows=10000
 arango.login=http://galaxy-auth-center/authorize/arangoJwtLogin
-arango.query.url=http://192.168.40.182:8529/_db/ip-learning-test/_api/cursor
-arango.collection=http://192.168.40.182:8529/_db/ip-learning-test/_api/collection
+arango.query.url=http://192.168.44.12:8529/_db/tsg_galaxy_v3/_api/cursor
+arango.collection=http://192.168.44.12:8529/_db/tsg_galaxy_v3/_api/collection
 #clickhouse数据库相关配置
 clickhouse.query.url=http://192.168.44.12:8123
 clickhouse.dbname=tsg_galaxy_v3
diff --git a/galaxy-data-engine/config/application-prod.properties b/galaxy-data-engine/config/application-prod.properties
index a50514b..d244b1e 100644
--- a/galaxy-data-engine/config/application-prod.properties
+++ b/galaxy-data-engine/config/application-prod.properties
@@ -27,8 +27,8 @@ xxl.job.admin.url=http://192.168.44.12:8181/xxl-job-admin
 xxl.job.admin.username=admin
 xxl.job.admin.password=123456
 #最大的数据查询条数
-engine.maxCacheNum=1048576
-engine.defaultResultNum=200
+engine.maxCacheNum=500000
+engine.defaultResultNum=300000
 #Hbase数据库相关配置
 hbase.url=192.168.44.12:8084
 hbase.dbname=tsg
@@ -42,8 +42,8 @@ druid.sqlTimeZone=Asia/Shanghai
 #arango数据库相关配置
 arango.maxrows=10000
 arango.login=http://galaxy-auth-center/authorize/arangoJwtLogin
-arango.query.url=http://192.168.40.182:8529/_db/ip-learning-test/_api/cursor
-arango.collection=http://192.168.40.182:8529/_db/ip-learning-test/_api/collection
+arango.query.url=http://192.168.44.12:8529/_db/tsg_galaxy_v3/_api/cursor
+arango.collection=http://192.168.44.12:8529/_db/tsg_galaxy_v3/_api/collection
 #clickhouse数据库相关配置
 clickhouse.query.url=http://192.168.44.12:8123
 clickhouse.dbname=tsg_galaxy_v3
diff --git a/galaxy-data-engine/config/application-test.properties b/galaxy-data-engine/config/application-test.properties
index a50514b..d0634e5 100644
--- a/galaxy-data-engine/config/application-test.properties
+++ b/galaxy-data-engine/config/application-test.properties
@@ -27,8 +27,8 @@ xxl.job.admin.url=http://192.168.44.12:8181/xxl-job-admin
 xxl.job.admin.username=admin
 xxl.job.admin.password=123456
 #最大的数据查询条数
-engine.maxCacheNum=1048576
-engine.defaultResultNum=200
+engine.maxCacheNum=500000
+engine.defaultResultNum=300000
 #Hbase数据库相关配置
 hbase.url=192.168.44.12:8084
 hbase.dbname=tsg
diff --git a/galaxy-data-engine/schema/druid/security_event_hits_log.avsc b/galaxy-data-engine/schema/druid/security_event_hits_log.avsc
index 906921e..740c124 100644
--- a/galaxy-data-engine/schema/druid/security_event_hits_log.avsc
+++ b/galaxy-data-engine/schema/druid/security_event_hits_log.avsc
@@ -29,18 +29,10 @@
       "type": "long"
     },
     {
-      "name": "intercept_state",
-      "type": "long"
-    },
-    {
       "name": "isp",
       "type": "string"
     },
     {
-      "name": "pinningst",
-      "type": "long"
-    },
-    {
       "name": "policy_id",
       "type": "long"
     }
diff --git a/galaxy-data-engine/schema/druid/sys_storage_log.avsc b/galaxy-data-engine/schema/druid/sys_storage_log.avsc
index 61e0efe..1ab7b1c 100644
--- a/galaxy-data-engine/schema/druid/sys_storage_log.avsc
+++ b/galaxy-data-engine/schema/druid/sys_storage_log.avsc
@@ -17,10 +17,6 @@
       "type": "long"
     },
     {
-      "name": "stored_days",
-      "type": "long"
-    },
-    {
       "name": "used_size",
       "type": "long"
     },
@@ -29,7 +25,7 @@
       "type": "long"
     },
     {
-      "name": "first_storage",
+      "name": "last_storage",
       "type": "long"
     }
   ]
diff --git a/galaxy-data-engine/schema/druid/traffic_summary_log.avsc b/galaxy-data-engine/schema/druid/traffic_summary_log.avsc
index 7536304..c50c5fe 100644
--- a/galaxy-data-engine/schema/druid/traffic_summary_log.avsc
+++ b/galaxy-data-engine/schema/druid/traffic_summary_log.avsc
@@ -21,24 +21,12 @@
       "type": "string"
     },
     {
-      "name": "sessions",
-      "type": "long"
-    },
-    {
       "name": "one_sided_connections",
       "type": "long"
     },
     {
-      "name": "bytes",
-      "type": "long"
-    },
-    {
       "name": "uncategorized_bytes",
       "type": "long"
-    },
-    {
-      "name": "packets",
-      "type": "long"
     }
   ]
 }
\ No newline at end of file
diff --git a/galaxy-data-engine/src/main/java/com/mesalab/engine/component/config/XxlJobConfig.java b/galaxy-data-engine/src/main/java/com/mesalab/engine/component/config/XxlJobConfig.java
index f5b9bf8..5688d5f 100644
--- a/galaxy-data-engine/src/main/java/com/mesalab/engine/component/config/XxlJobConfig.java
+++ b/galaxy-data-engine/src/main/java/com/mesalab/engine/component/config/XxlJobConfig.java
@@ -14,12 +14,12 @@ import org.springframework.stereotype.Component;
 @Component
 public class XxlJobConfig {
 
-    @Value("$xxl.job.admin.url")
+    @Value("${xxl.job.admin.url}")
     private String url;
 
-    @Value("$xxl.job.admin.username")
+    @Value("${xxl.job.admin.username}")
     private String userName;
 
-    @Value("$xxl.job.admin.password")
+    @Value("${xxl.job.admin.password}")
     private String password;
 }
diff --git a/galaxy-data-engine/src/main/java/com/mesalab/engine/component/dialect/ClickHouseDialect.java b/galaxy-data-engine/src/main/java/com/mesalab/engine/component/dialect/ClickHouseDialect.java
index f7ea9ba..d4e3506 100644
--- a/galaxy-data-engine/src/main/java/com/mesalab/engine/component/dialect/ClickHouseDialect.java
+++ b/galaxy-data-engine/src/main/java/com/mesalab/engine/component/dialect/ClickHouseDialect.java
@@ -24,6 +24,7 @@ import net.sf.jsqlparser.JSQLParserException;
 import net.sf.jsqlparser.expression.Alias;
 import net.sf.jsqlparser.expression.Expression;
 import net.sf.jsqlparser.expression.Function;
+import net.sf.jsqlparser.expression.Parenthesis;
 import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
 import net.sf.jsqlparser.expression.operators.conditional.OrExpression;
 import net.sf.jsqlparser.expression.operators.relational.*;
@@ -202,24 +203,28 @@ public class ClickHouseDialect extends AbstractDataSourceDialect {
          * @param where
          */
         private void setInSubqueryAlias(Expression where) {
-            if (where instanceof InExpression) {//where in 查询处理
+            if (where instanceof InExpression) {
                 InExpression whereIn = (InExpression) where;
                 ItemsList rightItemsList = whereIn.getRightItemsList();
                 if (rightItemsList instanceof SubSelect) {
                     SubSelect sub = (SubSelect) rightItemsList;
                     SelectBody selectBody = sub.getSelectBody();
                     PlainSelect plainSelect = (PlainSelect) selectBody;
-                    Table table = (Table) plainSelect.getFromItem();
-                    if (StringUtil.isEmpty(table.getAlias())) {
-                        Alias alias = new Alias(table.getName());
-                        plainSelect.getFromItem().setAlias(alias);
+                    FromItem fromItem = plainSelect.getFromItem();
+                    if (fromItem instanceof Table) {
+                        Table table = (Table) fromItem;
+                        if (StringUtil.isEmpty(table.getAlias())) {
+                            Alias alias = new Alias(table.getName());
+                            plainSelect.getFromItem().setAlias(alias);
+                        }
+                    } else if (fromItem instanceof SubSelect) {
+                        SubSelect subSelect = (SubSelect) fromItem;
+                        generateSubqueryAlias(subSelect.getSelectBody().toString());
                     }
                     if (StringUtil.isNotEmpty(plainSelect.getWhere())) {
                         setInSubqueryAlias(plainSelect.getWhere());
                     }
-
                 }
-
             } else if (where instanceof AndExpression) {
                 AndExpression whereAnd = (AndExpression) where;
                 Expression leftExpression = whereAnd.getLeftExpression();
@@ -234,8 +239,6 @@ public class ClickHouseDialect extends AbstractDataSourceDialect {
                 setInSubqueryAlias(rightExpression);
             }
         }
-
-
     }
 
 
@@ -489,7 +492,8 @@ public class ClickHouseDialect extends AbstractDataSourceDialect {
                 log.warn("Get Partition Key is Null.Focus in tableName {} schema", param.getTableName());
                 return sql;
             } else {
-                StringBuffer subQuery = getSubquerySql(partitionKey, param.getTableName());
+                StringBuffer subQuery = getSubquerySql(partitionKey,
+                        metadataService.getDBNameByTableName(param.getTableName()) + "." + param.getTableName());
                 Matcher m = pWhere.matcher(sql);
                 StringBuffer sb = new StringBuffer();
                 while (m.find()) {
@@ -843,9 +847,17 @@ public class ClickHouseDialect extends AbstractDataSourceDialect {
 
     @Override
     public Dialect executeSyntaxCheck() {
-        String sql = getSyntaxCheckSql();
-        generateBaseResult(sql);
+        String sql = convertQuery(getSyntaxCheckSql());
         log.info("option=syntax-check:  original sql: {}, after transformation(return message) info: {}", param.getQuery(), sql);
+
+        Map<String, String> results = executeHttpGet(sql);
+        if (results.get("status").equals(String.valueOf(HttpStatus.SC_OK))) {
+            baseResult = BaseResultUtil.generate(Integer.valueOf(results.get("status")), ResultCodeEnum.EXECUTE_SUCCESS.getCode(), Encodes.urlDecode(sql),
+                    JsonMapper.fromJsonString(results.get("result"), Map.class), null, null, QueryFormatEnum.JSON.getValue());
+        } else {
+            baseResult = BaseResultUtil.generate(Integer.valueOf(results.get("status")), ResultCodeEnum.SQL_EXECUTION_ERROR.getCode(), results.get("message"),
+                    results.get("result"), null, null, param.getFormat());
+        }
         return this;
     }
 
@@ -853,7 +865,7 @@ public class ClickHouseDialect extends AbstractDataSourceDialect {
 
         Map<String, String> results = executeHttpGet(sql);
         if (results.get("status").equals(String.valueOf(HttpStatus.SC_OK))) {
-            baseResult = BaseResultUtil.generate(Integer.valueOf(results.get("status")), ResultCodeEnum.EXECUTE_SUCCESS.getCode(), Encodes.urlDecode(sql),
+            baseResult = BaseResultUtil.generate(Integer.valueOf(results.get("status")), ResultCodeEnum.EXECUTE_SUCCESS.getCode(), "ok",
                     JsonMapper.fromJsonString(results.get("result"), Map.class), null, null, QueryFormatEnum.JSON.getValue());
         } else {
             baseResult = BaseResultUtil.generate(Integer.valueOf(results.get("status")), ResultCodeEnum.SQL_EXECUTION_ERROR.getCode(), results.get("message"),
@@ -898,8 +910,123 @@ public class ClickHouseDialect extends AbstractDataSourceDialect {
      * @return
      */
     private String getSyntaxCheckSql() {
-        String query = convertQuery(findBestQuery());
-        return query;
+        String sql = param.getDbQuerySource().getSqlBody();
+        try {
+            sql = String.valueOf(parserAndUpdateSql(sql));
+        } catch (Exception e) {
+            log.error("syntax-check sql error {}, execute original sql: {}, error is: {} ",
+                    sql, sql = param.getDbQuerySource().getSqlBody(), e.getMessage() == null ? e.getCause() : e.getMessage());
+            e.printStackTrace();
+        }
+        return sql;
+    }
+
+    /**
+     *
+     * 做数据集限制: 将最内层sql的表名, 替换成(select * from tableName limit XXX) as alias
+     *   涉及from 和 where中存在子查询
+     *
+     * @param sql
+     * @return
+     * @throws JSQLParserException
+     */
+    private SelectBody parserAndUpdateSql(String sql) throws JSQLParserException {
+        Statement parse = CCJSqlParserUtil.parse(sql);
+        if (parse instanceof Select) {
+            Select select = (Select) parse;
+            SelectBody selectBody = select.getSelectBody();
+            if (selectBody instanceof PlainSelect) {
+                PlainSelect plainSelect = (PlainSelect) select.getSelectBody();
+                FromItem fromItem = plainSelect.getFromItem();
+                if (fromItem instanceof SubSelect) {
+                    SubSelect subSelect = (SubSelect) plainSelect.getFromItem();
+                    subSelect.setSelectBody(parserAndUpdateSql(subSelect.getSelectBody().toString()));
+                } else if (fromItem instanceof Table) {
+                    Table table = (Table) fromItem;
+                    plainSelect.setFromItem(generateDataSetSql(table.getName(), table.getAlias() != null ? table.getAlias().getName() : table.getName()));
+                }
+                parseWhereAndUpdate(plainSelect.getWhere());
+                return plainSelect;
+            } else if (selectBody instanceof SetOperationList) {
+                SetOperationList setOperationList = (SetOperationList) selectBody;
+                List<SelectBody> selects = setOperationList.getSelects();
+                for (int i = 0; i < selects.size(); i++) {
+                    selects.set(i, parserAndUpdateSql(selects.get(i).toString()));
+                }
+                return setOperationList;
+            } else {
+                throw new BusinessException("Only support selectBody operation: PlainSelect or SetOperationList");
+            }
+        }
+        throw new BusinessException("Only support statement operation as select");
+    }
+
+    /**
+     * 将where中子查询语句表名更新为数据集形式,类同from中表的替换
+     *
+     * @param whereExpression
+     * @throws JSQLParserException
+     */
+    private void parseWhereAndUpdate(Expression whereExpression) throws JSQLParserException {
+        if (whereExpression instanceof ComparisonOperator) {
+            ComparisonOperator whereOperator = (ComparisonOperator) whereExpression;
+            Expression left = whereOperator.getLeftExpression();
+            setSelectBody(left);
+            Expression right = whereOperator.getRightExpression();
+            setSelectBody(right);
+        } else if (whereExpression instanceof InExpression) {
+            InExpression inExpression = (InExpression) whereExpression;
+            Expression leftExpression = inExpression.getLeftExpression();
+            setSelectBody(leftExpression);
+            ItemsList rightItemsList = inExpression.getRightItemsList();
+            if (rightItemsList instanceof SubSelect) {
+                SubSelect subSelect = (SubSelect) rightItemsList;
+                setSelectBody(subSelect);
+            }
+        } else if (whereExpression instanceof Between) {
+            Between whereBetween = (Between) whereExpression;
+            Expression betweenExpressionStart = whereBetween.getBetweenExpressionStart();
+            setSelectBody(betweenExpressionStart);
+            Expression betweenExpressionEnd = whereBetween.getBetweenExpressionEnd();
+            setSelectBody(betweenExpressionEnd);
+        } else if (whereExpression instanceof AndExpression) {
+            AndExpression whereAnd = (AndExpression) whereExpression;
+            parseWhereAndUpdate(whereAnd.getLeftExpression());
+            parseWhereAndUpdate(whereAnd.getRightExpression());
+        } else if (whereExpression instanceof OrExpression) {
+            OrExpression whereOr = (OrExpression) whereExpression;
+            parseWhereAndUpdate(whereOr.getLeftExpression());
+            parseWhereAndUpdate(whereOr.getRightExpression());
+        } else if (whereExpression instanceof Parenthesis) {
+            Parenthesis whereExpressionParenthesis = (Parenthesis) whereExpression;
+            Expression expression = whereExpressionParenthesis.getExpression();
+            parseWhereAndUpdate(expression);
+        }
+    }
+
+    private void setSelectBody(Expression expression) throws JSQLParserException {
+        if (expression instanceof SubSelect) {
+            SubSelect subSelect = (SubSelect) expression;
+            SelectBody selectBody = subSelect.getSelectBody();
+            subSelect.setSelectBody(parserAndUpdateSql(selectBody.toString()));
+        }
+    }
+
+    /**
+     * 生成数据集: 以FromItem对象形式的数据集表 sql,用来替换table
+     *
+     * @param tableName
+     * @param alias
+     * @return
+     * @throws JSQLParserException
+     */
+    private FromItem generateDataSetSql(String tableName, String alias) throws JSQLParserException {
+        String sql = String.format("SELECT 1 FROM (SELECT * FROM %s  LIMIT %s ) as %s ",
+                tableName, 10, alias);
+        Statement parse = CCJSqlParserUtil.parse(sql);
+        Select select = (Select) parse;
+        PlainSelect plainSelect = (PlainSelect) select.getSelectBody();
+        return plainSelect.getFromItem();
     }
 
 
diff --git a/galaxy-data-engine/src/main/java/com/mesalab/engine/component/dialect/DruidDialect.java b/galaxy-data-engine/src/main/java/com/mesalab/engine/component/dialect/DruidDialect.java
index 4618e01..fce510b 100644
--- a/galaxy-data-engine/src/main/java/com/mesalab/engine/component/dialect/DruidDialect.java
+++ b/galaxy-data-engine/src/main/java/com/mesalab/engine/component/dialect/DruidDialect.java
@@ -2,6 +2,7 @@ package com.mesalab.engine.component.dialect;
 
 import com.google.common.base.Stopwatch;
 import com.google.common.collect.Maps;
+import com.mesalab.common.exception.BusinessException;
 import com.mesalab.common.util.DataTypeUtil;
 import com.mesalab.engine.component.bean.SqlQueryBean;
 import com.mesalab.engine.component.bean.ApiQueryBean;
@@ -16,10 +17,17 @@ import com.mesalab.engine.component.SqlFunction;
 import com.mesalab.engine.component.config.DruidConfig;
 import com.mesalab.engine.component.config.EngineConfig;
 import com.mesalab.engine.service.HttpClientService;
+import com.zdjizhi.utils.Encodes;
 import com.zdjizhi.utils.JsonMapper;
 import com.zdjizhi.utils.StringUtil;
 import lombok.Data;
 import lombok.extern.slf4j.Slf4j;
+import net.sf.jsqlparser.JSQLParserException;
+import net.sf.jsqlparser.expression.Expression;
+import net.sf.jsqlparser.parser.CCJSqlParserUtil;
+import net.sf.jsqlparser.schema.Table;
+import net.sf.jsqlparser.statement.Statement;
+import net.sf.jsqlparser.statement.select.*;
 import org.apache.http.HttpStatus;
 
 import java.util.ArrayList;
@@ -109,12 +117,16 @@ public class DruidDialect extends AbstractDataSourceDialect {
 
     @Override
     public Dialect executeSyntaxCheck() {
-        String query = convertQuery(param.getDbQuerySource().getSqlBody());
-        Map<String, String> results = executeHttpPost("select 1");
+        String query = convertQuery(getSyntaxCheckSql());
+        Map<String, String> results = executeHttpPost(query);
 
-        baseResult = BaseResultUtil.generate(Integer.valueOf(results.get("status")), null, query,
-                (List<Object>) JsonMapper.fromJsonString(results.get("result"), Object.class), null, null, param.getFormat());
-        log.info("option=syntax-check:  original sql: {}, after transformation(return message) info: {}", param.getQuery(), query);
+        if (results.get("status").equals(String.valueOf(HttpStatus.SC_OK))) {
+            baseResult = BaseResultUtil.generate(Integer.valueOf(results.get("status")),ResultCodeEnum.EXECUTE_SUCCESS.getCode(), query,
+                    (List<Object>) JsonMapper.fromJsonString(results.get("result"), Object.class), null, null, param.getFormat());
+        } else {
+            baseResult = BaseResultUtil.generate(Integer.valueOf(results.get("status")), ResultCodeEnum.SQL_EXECUTION_ERROR.getCode(), results.get("message"),
+                    results.get("result"), null, null, param.getFormat());
+        }
         return this;
     }
 
@@ -155,6 +167,78 @@ public class DruidDialect extends AbstractDataSourceDialect {
         return baseResult;
     }
 
+
+    /**
+     * 获取语法检测sql
+     *
+     * @return
+     */
+    private String getSyntaxCheckSql() {
+        String sql = param.getDbQuerySource().getSqlBody();
+        try {
+            sql = String.valueOf(parserAndUpdateSql(sql));
+        } catch (Exception e) {
+            log.error("syntax-check sql error {}, execute original sql: {}, error is: {} ",
+                    sql, sql = param.getDbQuerySource().getSqlBody(), e.getMessage() == null ? e.getCause() : e.getMessage());
+            e.printStackTrace();
+        }
+        return sql;
+    }
+
+    /**
+     * 做时间范围条件过滤: 去除原有最内层条件, 重新添加时间范围
+     *
+     * @param sql
+     * @return
+     * @throws JSQLParserException
+     */
+    private SelectBody parserAndUpdateSql(String sql) throws JSQLParserException {
+        Statement parse = CCJSqlParserUtil.parse(sql);
+        if (parse instanceof Select) {
+            Select select = (Select) parse;
+            SelectBody selectBody = select.getSelectBody();
+            if (selectBody instanceof PlainSelect) {
+                PlainSelect plainSelect = (PlainSelect) select.getSelectBody();
+                FromItem fromItem = plainSelect.getFromItem();
+                if (fromItem instanceof SubSelect) {
+                    SubSelect subSelect = (SubSelect) plainSelect.getFromItem();
+                    subSelect.setSelectBody(parserAndUpdateSql(subSelect.getSelectBody().toString()));
+                } else if (fromItem instanceof Table) {
+                    plainSelect.setWhere(generateDataSetSql());
+                    return plainSelect;
+                }
+                return plainSelect;
+            } else if (selectBody instanceof SetOperationList) {
+                SetOperationList setOperationList = (SetOperationList) selectBody;
+                List<SelectBody> selects = setOperationList.getSelects();
+                for (int i = 0; i < selects.size(); i++) {
+                    selects.set(i, parserAndUpdateSql(selects.get(i).toString()));
+                }
+                return setOperationList;
+            } else {
+                throw new BusinessException("Only support selectBody operation: PlainSelect or SetOperationList");
+            }
+        }
+        throw new BusinessException("Only support statement operation as select");
+    }
+
+
+    /**
+     * 生成where条件: 以Expression对象形式
+     *
+     * @return
+     * @throws JSQLParserException
+     */
+    private Expression generateDataSetSql() throws JSQLParserException {
+        String sql = String.format("SELECT * FROM tableName WHERE %s >= CURRENT_TIMESTAMP - INTERVAL '%s' HOUR ", param.getDbQuerySource().getPartitionKey(), 1);
+        Statement parse = CCJSqlParserUtil.parse(sql);
+        Select select = (Select) parse;
+        PlainSelect plainSelect = (PlainSelect) select.getSelectBody();
+        return plainSelect.getWhere();
+    }
+
+
+
     private List getMeta(List<Object> results) {
         List meta = new ArrayList<Map<String, String>>();
         if (results.size() > 0) {
diff --git a/galaxy-data-engine/src/main/java/com/mesalab/engine/service/impl/SqlEngineServiceImpl.java b/galaxy-data-engine/src/main/java/com/mesalab/engine/service/impl/SqlEngineServiceImpl.java
index c098142..b0abed9 100644
--- a/galaxy-data-engine/src/main/java/com/mesalab/engine/service/impl/SqlEngineServiceImpl.java
+++ b/galaxy-data-engine/src/main/java/com/mesalab/engine/service/impl/SqlEngineServiceImpl.java
@@ -104,9 +104,7 @@ public class SqlEngineServiceImpl implements SqlEngineService {
             } else {
                 log.warn("Long-Term Results not exist, execute real-time query: resultID-{}, sql-{}",
                         param.getResultId(), StringUtil.strip(param.getQuery()));
-
             }
-
         }
 
         Dialect dbDialect = parserSQL(param);
@@ -114,8 +112,8 @@ public class SqlEngineServiceImpl implements SqlEngineService {
             baseResult = dbDialect.executeSyntaxCheck().build();
         } else {
             baseResult = dbDialect.executeQuery().build();
-            baseResult = new FederationDialect(param, baseResult).executeQuery().build();
         }
+        baseResult = new FederationDialect(param, baseResult).executeQuery().build();
 
         if (!baseResult.isSuccess()) {
             throw new BusinessException(baseResult.getStatus(), baseResult.getCode(), baseResult.getMessage(), null);
diff --git a/galaxy-data-engine/src/main/java/com/mesalab/engine/service/impl/SystemServiceImpl.java b/galaxy-data-engine/src/main/java/com/mesalab/engine/service/impl/SystemServiceImpl.java
index 592fdfc..f1accfe 100644
--- a/galaxy-data-engine/src/main/java/com/mesalab/engine/service/impl/SystemServiceImpl.java
+++ b/galaxy-data-engine/src/main/java/com/mesalab/engine/service/impl/SystemServiceImpl.java
@@ -113,10 +113,8 @@ public class SystemServiceImpl implements SystemService {
             StorageDeletion reportInfo = getStorageDeletionInfoByHandler(LogType.REPORT_AND_METRICS.getValue(), JobHandlerEnum.DELETE_REPORT_AND_METRICS_DATA_JOB_HANDLER.getValue());
             list.add(reportInfo);
 
-            StorageDeletion storageDeletionInfo = new StorageDeletion();
-            storageDeletionInfo.setLogType(LogType.FILES.getValue());//伪代码
-            storageDeletionInfo.setMaxDays(365);
-            list.add(storageDeletionInfo);
+            StorageDeletion fileInfo = getStorageDeletionInfoByHandler(LogType.FILES.getValue(), JobHandlerEnum.DELETE_FILES_JOB_HANDLER.getValue());
+            list.add(fileInfo);
         } else if (LogType.TRAFFIC_LOGS.getValue().equalsIgnoreCase(logType)) {
             StorageDeletion trafficInfo = getStorageDeletionInfoByHandler(logType, JobHandlerEnum.DELETE_TRAFFIC_DATA_JOB_HANDLER.getValue());
             list.add(trafficInfo);
@@ -185,9 +183,6 @@ public class SystemServiceImpl implements SystemService {
         setCookie();
         for (StorageDeletion info : list) {
 
-            if (LogType.FILES.getValue().equalsIgnoreCase(info.getLogType())) { //伪代码
-                continue;
-            }
             String handler = getDeletePartHandlerByLogType(info.getLogType());
             if (jobIsBusyByHandler(handler)) {
                 return true;
@@ -221,10 +216,6 @@ public class SystemServiceImpl implements SystemService {
      */
     private BaseResult executeDeleteStorageJob(StorageDeletion info) {
 
-        if (LogType.FILES.getValue().equalsIgnoreCase(info.getLogType())) { //伪代码
-            return BaseResultUtil.failure(HttpStatus.SC_OK, "ok");
-        }
-
         BaseResult baseResult;
         String jobHandler;
         String logType = info.getLogType();