Merge branch 'develop' into 'master'master

Revert "[Fix][schema] 修正dos event schema field_discovery_metric session计算方式(TSG-21222)"

See merge request galaxy/platform/galaxy-qgw-service!40

5 files changed, 150 insertions, 161 deletions

diff --git a/src/main/resources/dsl-sql-template.sql b/src/main/resources/dsl-sql-template.sql
index 4533a2bc..9906ba88 100644
--- a/src/main/resources/dsl-sql-template.sql
+++ b/src/main/resources/dsl-sql-template.sql
@@ -4,9 +4,6 @@ SELECT SUM(asymmetric_c2s_flows + asymmetric_s2c_flows) AS asymmetric_flows, SUM
 #sql("APPLICATION_AND_PROTOCOL_SUMMARY_TRAFFIC_STAT")
 SELECT SUM(fragmentation_packets) AS fragmentation_packets, SUM(total_bytes) AS total_bytes, SUM(total_packets) AS total_packets, SUM(total_sessions) AS total_sessions, AVG(total_bytes) * 8 / $granularity_seconds AS data_rate FROM ( SELECT SUM(c2s_fragments + s2c_fragments) AS fragmentation_packets, SUM(c2s_bytes + s2c_bytes) AS total_bytes, SUM(c2s_pkts + s2c_pkts) AS total_packets, SUM(sessions) AS total_sessions FROM  $table WHERE $intervals_and_filter AND protocol_stack_id = 'ETHERNET' GROUP BY TIME_FLOOR_WITH_FILL(UNIX_TIMESTAMP(__time), '$granularity')) LIMIT 1
 #end
-#sql("APPLICATION_AND_PROTOCOL_SUMMARY_UNIQUE_CLIENT_IP_STAT")
-SELECT APPROX_COUNT_DISTINCT_DS_HLL(client_ip_sketch) AS uniq_client_ip FROM $table WHERE $intervals_and_filter AND protocol_stack_id = 'ETHERNET' LIMIT 1
-#end
 #sql("APPLICATION_AND_PROTOCOL_SUMMARY_TCP_STAT")
 SELECT SUM(c2s_tcp_retransmitted_pkts + s2c_tcp_retransmitted_pkts) AS tcp_retransmissions_packets, SUM(c2s_pkts + s2c_pkts) AS tcp_total_packets FROM $table WHERE $intervals_and_filter AND RIGHT(protocol_stack_id, 4) = '.TCP' LIMIT 1
 #end
@@ -17,23 +14,38 @@ SELECT SUM(c2s_bytes + s2c_bytes) as unknown_app_bytes FROM $table WHERE $interv
 SELECT protocol_stack_id, SUM(sessions) as sessions,SUM(c2s_bytes) as c2s_bytes, SUM(c2s_pkts) as c2s_pkts, SUM(s2c_bytes) as s2c_bytes, SUM(s2c_pkts) as s2c_pkts FROM $table WHERE $intervals_and_filter GROUP BY protocol_stack_id
 #end
 #sql("APPLICATION_AND_PROTOCOL_TREE_THROUGHPUT")
-SELECT FROM_UNIXTIME(TIME_FLOOR_WITH_FILL(UNIX_TIMESTAMP(__time), '$granularity', 'zero')) as stat_time, protocol_stack_id as type, sum(c2s_bytes + s2c_bytes) as bytes from $table where $intervals_and_filter group by FROM_UNIXTIME(TIME_FLOOR_WITH_FILL(UNIX_TIMESTAMP(__time), '$granularity', 'zero')), protocol_stack_id order by stat_time asc
+SELECT FROM_UNIXTIME(TIME_FLOOR_WITH_FILL(UNIX_TIMESTAMP(__time), '$granularity', 'zero')) as stat_time, protocol_stack_id as type, sum(c2s_bytes + s2c_bytes) as bytes, SUM(c2s_bytes + s2c_bytes) * 8 / $granularity_seconds AS bit_rate from $table where $intervals_and_filter group by FROM_UNIXTIME(TIME_FLOOR_WITH_FILL(UNIX_TIMESTAMP(__time), '$granularity', 'zero')), protocol_stack_id order by stat_time asc
 #end
 #sql("APPLICATION_AND_PROTOCOL_TOP_APPS")
-SELECT app_name as app_name, SUM(bytes) as bytes, SUM(sessions) as sessions, MAX(bytes_rate) as max_rate, AVG(bytes_rate) as avg_rate FROM (SELECT app_name as app_name, SUM(sessions) as sessions, SUM(c2s_bytes + s2c_bytes) as bytes, SUM(c2s_bytes + s2c_bytes) * 8 / $granularity_seconds as bytes_rate FROM $table WHERE $intervals_and_filter AND notEmpty(app_name) GROUP BY app_name ORDER BY bytes DESC ) GROUP BY app_name ORDER BY bytes DESC $limit
+SELECT app_name as app_name, SUM(bytes) as bytes, SUM(sessions) as sessions, MAX(bit_rate) as max_rate, SUM(bytes) * 8/$interval_seconds as avg_rate FROM (SELECT app_name as app_name, SUM(sessions) as sessions, SUM(c2s_bytes + s2c_bytes) as bytes, SUM(c2s_bytes + s2c_bytes) * 8 / $granularity_seconds as bit_rate FROM $table WHERE $intervals_and_filter AND notEmpty(app_name) GROUP BY app_name, TIME_FLOOR_WITH_FILL(UNIX_TIMESTAMP(__time), '$granularity') ORDER BY bytes DESC ) GROUP BY app_name ORDER BY bytes DESC $limit
 #end
 #sql("APPLICATION_AND_PROTOCOL_APP_RELATED_INTERNAL_IPS")
 SELECT if(bitAnd(flags, 8) = 8, client_ip, server_ip) AS ip, SUM(sent_bytes + received_bytes) AS bytes FROM $table WHERE $intervals_and_filter GROUP BY ip ORDER BY bytes DESC $limit
 #end
 #sql("APPLICATION_AND_PROTOCOL_APP_THROUGHPUT")
-SELECT FROM_UNIXTIME(TIME_FLOOR_WITH_FILL(UNIX_TIMESTAMP(__time),'$granularity','zero')) AS stat_time, app_name, SUM(c2s_bytes + s2c_bytes) AS bytes FROM $table WHERE $intervals_and_filter GROUP BY FROM_UNIXTIME(TIME_FLOOR_WITH_FILL(UNIX_TIMESTAMP(__time),'$granularity','zero')), app_name ORDER BY  stat_time ASC
+SELECT FROM_UNIXTIME(TIME_FLOOR_WITH_FILL(UNIX_TIMESTAMP(__time),'$granularity','zero')) AS stat_time, app_name, SUM(c2s_bytes + s2c_bytes) AS bytes, SUM(c2s_bytes + s2c_bytes) * 8 / $granularity_seconds AS bit_rate FROM $table WHERE $intervals_and_filter GROUP BY FROM_UNIXTIME(TIME_FLOOR_WITH_FILL(UNIX_TIMESTAMP(__time),'$granularity','zero')), app_name ORDER BY  stat_time ASC
 #end
 #sql("APPLICATION_AND_PROTOCOL_APP_SUMMARY")
 SELECT app_name, SUM(sessions) AS sessions, SUM(c2s_bytes + s2c_bytes) AS bytes, SUM(s2c_bytes) AS received_bytes, SUM(c2s_bytes) AS sent_bytes, SUM(c2s_pkts + s2c_pkts) AS packets, SUM(c2s_pkts) AS sent_packets, SUM(s2c_pkts) AS received_packets FROM $table WHERE $intervals_and_filter GROUP BY app_name
 #end
-#sql("REAL_TIME_DATA_ANALYTICS_SUBSCRIBER_ID_RELATE_IP")
-SELECT account AS SUBSCRIBER_ID, framed_ip AS IP, first_found_time AS FIRST_FOUND_TIME, last_update_time AS LAST_FOUND_TIME, vsys_id as VSYS_ID FROM $table WHERE $intervals_and_filter AND acct_status_type != 2 $limit
+#sql("DATAPATH_TELEMETRY_RECORD")
+SELECT * FROM $table WHERE $intervals_and_filter ORDER BY timestamp_us ASC
+#end
+#sql("TRAFFIC_SPECTRUM_SUMMARY")
+SELECT direction, SUM(bytes) AS total_bytes, SUM(sessions) AS total_sessions, SUM(pkts) AS total_packets, SUM(IF(app = 'unknown', bytes, 0)) AS unknown_app_bytes, SUM(asymmetric_c2s_flows + asymmetric_s2c_flows) AS asymmetric_flows, SUM(c2s_fragments + s2c_fragments) AS fragmentation_packets FROM $table WHERE $intervals_and_filter GROUP BY direction
+#end
+#sql("TRAFFIC_SPECTRUM_UNIQ_IP")
+SELECT COUNT(DISTINCT(IF(direction = 'Outbound', client_ip, NULL))) AS internal_uniq_client_ip, COUNT(DISTINCT(IF(direction = 'Outbound', server_ip, NULL))) AS external_uniq_server_ip, COUNT(DISTINCT(IF(direction = 'Inbound', server_ip, NULL))) AS internal_uniq_server_ip, COUNT(DISTINCT(IF(direction = 'Inbound', client_ip, NULL))) AS external_uniq_client_ip FROM $table WHERE $intervals_and_filter
+#end
+#sql("TRAFFIC_SPECTRUM_APP_DISTRIBUTION_SERVER_IP")
+SELECT server_ip as value, SUM(bytes) as bytes, SUM(sessions) as sessions, SUM(pkts) as packets FROM $table WHERE $intervals_and_filter GROUP BY server_ip ORDER BY bytes DESC $limit
+#end
+#sql("TRAFFIC_SPECTRUM_APP_DISTRIBUTION_SERVER_DOMAIN")
+SELECT server_domain as value, SUM(bytes) as bytes, SUM(sessions) as sessions, SUM(pkts) as packets FROM $table WHERE $intervals_and_filter AND notEmpty(server_domain) GROUP BY server_domain ORDER BY bytes DESC $limit
+#end
+#sql("TRAFFIC_SPECTRUM_CLIENT_IP_CONNECT_APPLICATION_USAGE")
+SELECT direction, SUM(temp_sessions) AS sessions, SUM(temp_bytes) AS bytes, SUM(temp_packets) AS packets, MAX(temp_rate) AS max_rate, ROUND(SUM(temp_bytes) * 8 /$interval_seconds) AS avg_rate, client_ip AS client_ip, client_country AS client_country, app AS app, app_category AS app_category FROM( SELECT toUnixTimestamp(toDateTime(toStartOfInterval(toDateTime(recv_time), INTERVAL $granularity_seconds SECOND))) AS stat_time, IF(client_ip IN ( SELECT client_ip FROM $table WHERE $intervals_and_filter AND notEmpty(app) GROUP BY client_ip,app ORDER BY SUM(bytes) DESC $limit), client_ip, 'Other') AS client_ip, IF(client_country IN ( SELECT anyLast(client_country) FROM $table WHERE $intervals_and_filter AND notEmpty(app) GROUP BY client_ip,app ORDER BY SUM(bytes) DESC $limit), client_country, 'Other') AS client_country, IF(app IN ( SELECT app FROM $table WHERE $intervals_and_filter AND notEmpty(app) GROUP BY client_ip,app ORDER BY SUM(bytes) DESC $limit), app, 'Other') AS app, IF(app_category IN ( SELECT anyLast(app_category) FROM $table WHERE $intervals_and_filter AND notEmpty(app) GROUP BY client_ip,app ORDER BY SUM(bytes) DESC $limit), app_category, 'Other') AS app_category, direction, SUM(sessions) AS temp_sessions, SUM(bytes) AS temp_bytes, SUM(pkts) AS temp_packets, ROUND(SUM(bytes) * 8 / $granularity_seconds) AS temp_rate FROM $table WHERE $intervals_and_filter GROUP BY stat_time, client_ip, client_country, app, app_category, direction) GROUP BY client_ip, client_country, app, app_category, direction ORDER BY bytes DESC
 #end
-#sql("REAL_TIME_DATA_ANALYTICS_MOBILE_IDENTITY_RELATE_TEID")
-SELECT uplink_teid AS teid, apn, phone_number, imsi, imei, last_update_time,vsys_id FROM $table WHERE $intervals_and_filter AND msg_type = 1 and uplink_teid !=0 order by last_update_time desc $limit
+#sql("TRAFFIC_SPECTRUM_NETWORK_THROUGHPUT_TREND")
+SELECT FROM_UNIXTIME(TIME_FLOOR_WITH_FILL(recv_time, '$granularity', 'zero')) AS stat_time, ROUND(RATE(bytes,$granularity_seconds,1)) * 8 AS avg_bits_per_sec, ROUND(RATE(bytes,$granularity_seconds,1)) AS avg_bytes_per_sec, ROUND(RATE(pkts,$granularity_seconds,1)) AS avg_pkts_per_sec, ROUND(RATE(sessions,$granularity_seconds,1)) AS avg_sessions_per_sec FROM $table WHERE $intervals_and_filter GROUP BY stat_time ORDER BY stat_time ASC $limit
 #end
\ No newline at end of file
diff --git a/src/main/resources/dsl-validation.json b/src/main/resources/dsl-validation.json
index f54eb056..715ac00f 100644
--- a/src/main/resources/dsl-validation.json
+++ b/src/main/resources/dsl-validation.json
@@ -1,12 +1,11 @@
 {
+  "$schema": "http://json-schema.org/draft-07/schema#",
   "title": "服务推荐",
   "description": "查询",
   "type": "object",
   "properties": {
     "clientId": {
       "description": "唯一标识符",
-      "maximum": 2147483647,
-      "minimum": -2147483648,
       "type": [
         "null",
         "integer"
@@ -21,12 +20,10 @@
       "properties": {
         "queryType": {
           "description": "查询类型",
-          "maxLength": 65535,
           "type": "string"
         },
         "dataSource": {
           "description": "查询数据源",
-          "maxLength": 65535,
           "type": "string"
         },
         "parameters": {
@@ -47,19 +44,16 @@
                   "properties": {
                     "type": {
                       "description": "匹配符号",
-                      "maxLength": 65535,
                       "type": "string"
                     },
                     "fieldKey": {
                       "description": "查询字段",
-                      "maxLength": 65535,
                       "type": "string"
                     },
                     "fieldValues": {
                       "description": "查询参数",
                       "type": "array",
                       "items": {
-                        "maxLength": 65535,
                         "type": "string"
                       }
                     }
@@ -76,19 +70,16 @@
                 "properties": {
                   "type": {
                     "description": "匹配符号",
-                    "maxLength": 65535,
                     "type": "string"
                   },
                   "fieldKey": {
                     "description": "查询字段",
-                    "maxLength": 65535,
                     "type": "string"
                   },
                   "fieldValues": {
                     "description": "查询参数",
                     "type": "array",
                     "items": {
-                      "maxLength": 65535,
                       "type": "string"
                     }
                   }
@@ -108,21 +99,16 @@
                 "properties": {
                   "type": {
                     "description": "匹配符号",
-                    "maxLength": 65535,
                     "type": "string"
                   },
                   "fieldKey": {
                     "description": "查询字段",
-                    "maxLength": 65535,
                     "type": "string"
                   },
                   "fieldValues": {
                     "description": "查询参数",
                     "type": "array",
                     "items": {
-                      "maximum": 2147483647,
-                      "minimum": -2147483648,
-                      "maxLength": 65535,
                       "type": [
                         "integer",
                         "string"
@@ -141,21 +127,16 @@
                 "properties": {
                   "type": {
                     "description": "匹配符号",
-                    "maxLength": 65535,
                     "type": "string"
                   },
                   "fieldKey": {
                     "description": "查询字段",
-                    "maxLength": 65535,
                     "type": "string"
                   },
                   "fieldValues": {
                     "description": "查询参数",
                     "type": "array",
                     "items": {
-                      "maximum": 2147483647,
-                      "minimum": -2147483648,
-                      "maxLength": 65535,
                       "type": [
                         "integer",
                         "string"
@@ -169,11 +150,9 @@
               "description": "查询时间",
               "type": "array",
               "items": {
-                "maxLength": 65535,
                 "type": "string"
               },
               "additionalItems": {
-                "maxLength": 65535,
                 "type": "string"
               }
             },
@@ -186,12 +165,10 @@
                 "properties": {
                   "type": {
                     "description": "匹配符号",
-                    "maxLength": 65535,
                     "type": "string"
                   },
                   "fieldKey": {
                     "description": "排序字段",
-                    "maxLength": 65535,
                     "type": "string"
                   }
                 }
@@ -202,12 +179,10 @@
                 "properties": {
                   "type": {
                     "description": "匹配符号",
-                    "maxLength": 65535,
                     "type": "string"
                   },
                   "fieldKey": {
                     "description": "排序字段",
-                    "maxLength": 65535,
                     "type": "string"
                   }
                 }
@@ -215,9 +190,6 @@
             },
             "limit": {
               "description": "查询条数",
-              "maxLength": 65535,
-              "maximum": 2147483647,
-              "minimum": 0,
               "type": [
                 "integer",
                 "string"
diff --git a/src/main/resources/http-sql-template.sql b/src/main/resources/http-sql-template.sql
index c81bfb98..4be71868 100644
--- a/src/main/resources/http-sql-template.sql
+++ b/src/main/resources/http-sql-template.sql
@@ -1,109 +1,9 @@
-#sql("NETWORK_OVERVIEW_ASYMMETRIC_FLOWS_STAT")
-SELECT SUM(asymmetric_c2s_flows + asymmetric_s2c_flows) AS asymmetric_flows, SUM(closed_sessions) AS total_session_used_on_asymmetric_flows FROM traffic_general_stat  WHERE %s  %s LIMIT 1
-#end
-
-#sql("NETWORK_OVERVIEW_STAT")
-SELECT	APPROX_COUNT_DISTINCT_DS_HLL(client_ip_sketch) AS uniq_client_ip, SUM(c2s_fragments + s2c_fragments) AS fragmentation_packets, SUM(c2s_bytes + s2c_bytes) AS total_bytes, SUM(c2s_pkts + s2c_pkts) AS total_packets, SUM(sessions) AS total_sessions, (SUM(c2s_bytes + s2c_bytes) * 8)/(%s -%s) AS data_rate  FROM %s  WHERE %s %s AND protocol_stack_id = '%s' LIMIT 1
-#end
-
-#sql("NETWORK_OVERVIEW_TCP_STAT")
-SELECT SUM(c2s_tcp_retransmitted_pkts + s2c_tcp_retransmitted_pkts) AS tcp_retransmissions_packets, SUM(c2s_pkts + s2c_pkts) AS tcp_total_packets FROM %s  WHERE %s %s AND RIGHT(protocol_stack_id, 4) = '.TCP' LIMIT 1
-#end
-
-#sql("NETWORK_OVERVIEW_APP_STAT")
-SELECT SUM(c2s_bytes + s2c_bytes) as unknown_app_bytes FROM %s WHERE %s %s AND app_name = 'unknown' LIMIT 1
-#end
-
-#sql("PROTOCOL_TREE_SUMMARY")
-SELECT protocol_stack_id, SUM(sessions) as sessions,SUM(c2s_bytes) as c2s_bytes, SUM(c2s_pkts) as c2s_pkts, SUM(s2c_bytes) as s2c_bytes, SUM(s2c_pkts) as s2c_pkts FROM %s WHERE %s %s GROUP BY protocol_stack_id
-#end
-
-#sql("PROTOCOL_DATA_RATE_SUMMARY")
-(SELECT TIME_FORMAT(MILLIS_TO_TIMESTAMP( 1000 * TIME_FLOOR_WITH_FILL(TIMESTAMP_TO_MILLIS(__time)/1000, '%s', 'zero')), 'yyyy-MM-dd HH:mm:ss') as stat_time, protocol_stack_id as type, sum(c2s_bytes + s2c_bytes) as bytes from %s where %s %s and protocol_stack_id = '%s' group by TIME_FORMAT(MILLIS_TO_TIMESTAMP( 1000 * TIME_FLOOR_WITH_FILL(TIMESTAMP_TO_MILLIS(__time)/1000, '%s', 'zero')), 'yyyy-MM-dd HH:mm:ss'), protocol_stack_id order by stat_time asc)
-union all
-(SELECT TIME_FORMAT(MILLIS_TO_TIMESTAMP( 1000 * TIME_FLOOR_WITH_FILL(TIMESTAMP_TO_MILLIS(__time)/1000, '%s', 'zero')), 'yyyy-MM-dd HH:mm:ss') as stat_time, protocol_stack_id as type, sum(c2s_bytes + s2c_bytes) as bytes from %s where %s %s and protocol_stack_id like CONCAT('%s','.%s') and LENGTH(protocol_stack_id) = LENGTH(REPLACE(protocol_stack_id,'.','')) + 1 + %s group by TIME_FORMAT(MILLIS_TO_TIMESTAMP( 1000 * TIME_FLOOR_WITH_FILL(TIMESTAMP_TO_MILLIS(__time)/1000, '%s', 'zero')), 'yyyy-MM-dd HH:mm:ss'), protocol_stack_id order by stat_time asc)
-#end
-
-#sql("APP_DATA_SUMMARY")
-SELECT app_name as app_name, SUM(bytes) as bytes, SUM(sessions) as sessions, MAX(bytes_rate) as max_rate, AVG(bytes_rate) as avg_rate FROM (SELECT app_name as app_name, SUM(sessions) as sessions, SUM(c2s_bytes + s2c_bytes) as bytes, SUM(c2s_bytes + s2c_bytes) * 8 / %s as bytes_rate FROM %s WHERE %s %s AND notEmpty(app_name) GROUP BY app_name ORDER BY bytes DESC ) GROUP BY app_name ORDER BY bytes DESC %s
-#end
-
-#sql("APP_INTERNAL_IP_SUMMARY")
-SELECT if(bitAnd(flags, 8) = 8, client_ip, server_ip) AS ip, SUM(sent_bytes + received_bytes) AS bytes FROM %s WHERE %s %s GROUP BY ip ORDER BY bytes DESC %s
-#end
-
-#sql("APP_DATA_RATE_SUMMARY")
-SELECT TIME_FORMAT(MILLIS_TO_TIMESTAMP( 1000 * TIME_FLOOR_WITH_FILL(TIMESTAMP_TO_MILLIS(__time)/ 1000, '%s', 'zero')), 'yyyy-MM-dd HH:mm:ss') as stat_time, app_name , sum(c2s_bytes + s2c_bytes) as bytes from %s where %s %s group by TIME_FORMAT(MILLIS_TO_TIMESTAMP( 1000 * TIME_FLOOR_WITH_FILL(TIMESTAMP_TO_MILLIS(__time)/ 1000, '%s', 'zero')), 'yyyy-MM-dd HH:mm:ss'), app_name order by stat_time asc
-#end
-
-#sql("APP_TRAFFIC_SUMMARY")
-SELECT app_name, SUM(sessions) as sessions, sum(c2s_bytes + s2c_bytes) as bytes, sum(s2c_bytes) as received_bytes, sum(c2s_bytes) as sent_bytes, sum(c2s_pkts + s2c_pkts) as packets, sum(c2s_pkts) as sent_packets, sum(s2c_pkts) as received_packets from %s where %s %s group by app_name
-#end
-
-#sql("ENTITY_ACTIVE_CLIENT_IP")
-select client_ip as client_ip, vsys_id as vsys_id from %s where %s %s AND notEmpty(client_ip) GROUP BY client_ip, app, vsys_id ORDER BY COUNT(1) DESC LIMIT %s
-#end
-
-#sql("ENTITY_UDP_SESSION")
-select server_ip as server_ip,vsys_id as vsys_id ,COUNT(1) as sessions from %s where %s AND (arrayElement(splitByString('.',protocol_path),length(splitByString('.',protocol_path))) = 'UDP') and server_port in (53 , 443) group by server_ip,vsys_id order by sessions desc limit %s
-#end
-
-#sql("ENTITY_UDP_UNIQ_CLIENT_IPS")
-select server_ip as server_ip, vsys_id as vsys_id  from %s where %s AND (arrayElement(splitByString('.',protocol_path),length(splitByString('.',protocol_path))) = 'UDP') group by server_ip,vsys_id order by COUNT(DISTINCT(client_ip)) desc limit %s
-#end
-
-#sql("ENTITY_TCP_SESSION")
-select server_ip as server_ip,vsys_id as vsys_id ,COUNT(1) as sessions from %s where %s AND (arrayElement(splitByString('.',protocol_path),length(splitByString('.',protocol_path))) = 'TCP') group by server_ip,vsys_id order by sessions desc limit %s
-#end
-
-#sql("ENTITY_TCP_UNIQ_CLIENT_IPS")
-select server_ip as server_ip, vsys_id as vsys_id from %s where %s AND (arrayElement(splitByString('.',protocol_path),length(splitByString('.',protocol_path))) = 'TCP') group by server_ip, vsys_id order by COUNT(DISTINCT(client_ip)) desc limit %s
-#end
-
-#sql("TOP_ENTITY_TCP_UNIQ_CLIENT_IPS")
-select COUNT(DISTINCT(client_ip)) as client_ips from %s where %s AND (arrayElement(splitByString('.',protocol_path),length(splitByString('.',protocol_path))) = 'TCP') AND server_ip in (select server_ip from %s as cc where %s AND (arrayElement(splitByString('.',protocol_path),length(splitByString('.',protocol_path))) = 'TCP') group by server_ip order by COUNT(DISTINCT(client_ip)) desc limit %s)
-#end
-
-#sql("TOP_ENTITY_UDP_UNIQ_CLIENT_IPS")
-select COUNT(DISTINCT(client_ip)) as client_ips from %s where %s AND (arrayElement(splitByString('.',protocol_path),length(splitByString('.',protocol_path))) = 'UDP') AND server_ip in (select server_ip from %s as cc where %s AND (arrayElement(splitByString('.',protocol_path),length(splitByString('.',protocol_path))) = 'UDP') group by server_ip order by COUNT(DISTINCT(client_ip)) desc limit %s)
-#end
-
-#sql("TOTAL_ENTITY_UDP_SESSION")
-select COUNT(1) as sessions from %s where %s AND (arrayElement(splitByString('.',protocol_path),length(splitByString('.',protocol_path))) = 'UDP') and server_port in (53 , 443) limit 1
-#end
-
-#sql("TOTAL_ENTITY_UDP_UNIQ_CLIENT_IPS")
-select COUNT(DISTINCT(client_ip)) as uniq_client_ips from %s where %s AND (arrayElement(splitByString('.',protocol_path),length(splitByString('.',protocol_path))) = 'UDP') limit 1
-#end
-
-#sql("TOTAL_ENTITY_TCP_SESSION")
-select COUNT(1) as sessions from %s where %s AND (arrayElement(splitByString('.',protocol_path),length(splitByString('.',protocol_path))) = 'TCP') limit 1
-#end
-
-#sql("TOTAL_ENTITY_TCP_UNIQ_CLIENT_IPS")
-select COUNT(DISTINCT(client_ip)) as uniq_client_ips  from %s where %s AND (arrayElement(splitByString('.',protocol_path),length(splitByString('.',protocol_path))) = 'TCP') limit 1
-#end
-
-#sql("ENTITY_TOP_SNI")
-select ssl_sni, SUM(sent_bytes + received_bytes) AS bytes, COUNT(1) AS sessions,vsys_id as vsys_id from %s where %s and notEmpty(ssl_sni) and decoded_as = 'SSL' group by ssl_sni,vsys_id order by  COUNT(1) desc limit %s
-#end
-
-#sql("ENTITY_SNI_TOTAL")
-select SUM(sent_bytes + received_bytes) AS bytes, COUNT(1) AS sessions from %s where %s and decoded_as = 'SSL' limit 1
-#end
-
 #sql("SYSTEM_STORAGE_QUOTA")
-SELECT type, SUM(used_size) as used_size, SUM(max_size) * 7 / 10 as max_size, TIME_FORMAT(MILLIS_TO_TIMESTAMP(ANY_VALUE(last_storage) * 1000), 'YYYY-MM-dd') as first_storage FROM
-    (SELECT log_type as type, LATEST(used_size) as used_size, LATEST(max_size) as max_size, LATEST(last_storage) as last_storage FROM sys_storage_log WHERE __time >= CURRENT_TIMESTAMP - INTERVAL '1' HOUR AND data_center != '' GROUP BY data_center, log_type)
-GROUP BY type
+SELECT log_type AS type, SUM(used_size) AS used_size, SUM(total_allocated_size) AS total_allocated_size, MIN(since_time) AS since_time FROM (SELECT log_type, used_size, total_allocated_size, DATE(FROM_UNIXTIME(since_time)) AS since_time, ROW_NUMBER() OVER (PARTITION BY log_type ORDER BY generated_time DESC) AS row_num FROM sys_storage_event WHERE generated_time >= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 WEEK)) ) AS subquery WHERE row_num = 1 GROUP BY log_type
 #end
 
 #sql("SYSTEM_DAILY_TREND_OF_STORAGE")
-select TIME_FORMAT(__time, 'YYYY-MM-dd') as stat_time, log_type as type, sum(aggregate_size) as used_size from sys_storage_log where __time >= '%s' and __time < '%s' group by TIME_FORMAT(__time, 'YYYY-MM-dd'), log_type
-#end
-
-#sql("RELATION_SUBSCRIBER_ID")
-SELECT account AS SUBSCRIBER_ID,framed_ip AS IP,first_found_time AS FIRST_FOUND_TIME,last_update_time AS LAST_FOUND_TIME,vsys_id as VSYS_ID FROM tsg_galaxy.relation_account_framedip WHERE 1=1 %s AND acct_status_type != 2 LIMIT %s
+SELECT DATE(FROM_UNIXTIME(generated_time)) AS stat_time, log_type AS type, sum( bytes ) AS used_size FROM sys_storage_event WHERE generated_time >= UNIX_TIMESTAMP('%s') AND generated_time < UNIX_TIMESTAMP('%s') GROUP BY stat_time, type ORDER BY stat_time ASC
 #end
 
 #sql("SCHEMA_STORAGE_SIZE")
@@ -114,25 +14,10 @@ SELECT name AS field, IFNULL(SUM(data_compressed_bytes), 0) AS bytes FROM column
 SELECT name, engine_full AS table_ttl FROM tables WHERE name ='%s_local' AND  "database" = '%s' AND "engine" ='MergeTree'
 #end
 
-#sql("GTPC_KNOWLEDGE_BASE")
-SELECT uplink_teid AS teid, apn, phone_number, imsi, imei, last_update_time,vsys_id FROM tsg_galaxy.gtpc_knowledge_base WHERE msg_type = 1 and uplink_teid !=0 %s order by last_update_time desc limit %s
-#end
-
 #sql("TABLE_INDEX_KEY")
 SELECT any(splitByString(', ',sorting_key)) AS index_key FROM tables_cluster where name = '%s_local' AND "database" = '%s'
 #end
 
-#sql("UNSTRUCTURED_DATA_PATH")
-SELECT log_id AS log_id,%s AS file_path FROM %s where %s AND notEmpty(%s) ORDER BY recv_time DESC LIMIT %s
-#end
-
-#sql("UNSTRUCTURED_DATA_PATH_OLD")
-SELECT common_log_id AS log_id,%s AS file_path FROM %s where %s AND notEmpty(%s) ORDER BY common_recv_time DESC LIMIT %s
-#end
-
 #sql("SQL_DATASETS")
-SELECT id, identifier_name, category, execute_engine, type, template, description, generated_time, last_update_time FROM dataset %s ORDER BY last_update_time DESC
+SELECT id, identifier_name, category, backend_engine, type, template, description, generated_time, last_update_time FROM dataset %s ORDER BY last_update_time DESC
 #end
-
-#sql("SQL_DATASETS_CATEGORY")
-SELECT category FROM dataset GROUP BY category
diff --git a/src/main/resources/job-sql-template.sql b/src/main/resources/job-sql-template.sql
index 36ef3c40..5be4a538 100644
--- a/src/main/resources/job-sql-template.sql
+++ b/src/main/resources/job-sql-template.sql
@@ -17,13 +17,13 @@ SELECT count(*) AS logCount, %s AS totalMetric FROM %s %s LIMIT 1
 UPSERT INTO "tsg_galaxy"."job_result"( ROWKEY, "detail"."is_done", "detail"."done_progress", "detail"."duration_time") VALUES('%s', %s, %s, %s)
 #end
 #sql("JOB_TOPK_DEFAULT")
-SELECT %s AS value, %s(%s) AS %s FROM %s %s GROUP BY value ORDER BY %s DESC limit 10000
+SELECT %s AS value, %s(%s) AS %s%s FROM %s %s GROUP BY value ORDER BY %s%s DESC limit %s
 #end
 #sql("JOB_TOPK_ARRAY")
-SELECT value, %s(metric) AS %s FROM (SELECT arrayJoin(items) AS value, metric FROM (SELECT %s AS items, %s AS metric FROM %s WHERE notEmpty(%s) %s GROUP BY items )) GROUP BY value ORDER BY %s DESC LIMIT 10000
+SELECT value, %s(metric) AS %s%s FROM (SELECT arrayJoin(items) AS value, metric FROM (SELECT IF(empty(%s), arrayPushBack(%s, NULL), %s) AS items, %s AS metric FROM %s %s GROUP BY items )) GROUP BY value ORDER BY %s%s DESC LIMIT %s
 #end
 #sql("JOB_TOPK_BIT")
-SELECT value, %s(metric) AS %s FROM (SELECT arrayJoin(items) AS value, metric FROM (SELECT bitmaskToArray(%s) AS items, %s AS metric FROM %s %s GROUP BY items )) GROUP BY value ORDER BY %s DESC LIMIT 10000
+SELECT value, %s(metric) AS %s%s FROM (SELECT arrayJoin(items) AS value, metric FROM (SELECT bitmaskToArray(%s) AS items, %s AS metric FROM %s %s GROUP BY items )) GROUP BY value ORDER BY %s%s DESC LIMIT %s
 #end
 #sql("JOB_UPDATE_FIELD_VALUE_SINGLE_QUOTATION")
 UPSERT into "tsg_galaxy"."job_result" (ROWKEY, "%s"."%s") values('%s', '%s')
@@ -50,7 +50,7 @@ SELECT %s FROM %s WHERE %s LIMIT 1
 INSERT INTO saved_query_job (job_id, query_sql, state, done_progress, is_failed, result_message, elapsed, rows_read, bytes_read, result_rows, result_bytes, is_valid, start_time, end_time, last_update_time, generated_time ) VALUES ('%s', '%s', 'PENDING', 0, 0, NULL, NULL, NULL, NULL, NULL, NULL, 1, NULL, NULL, '%s', '%s')
 #end
 #sql("SAVED_QUERY_JOB_STATUS")
-SELECT state, done_progress, is_failed, is_valid, start_time, end_time, rows_read, query_sql, job_id FROM saved_query_job WHERE job_id IN ('%s') LIMIT %s
+SELECT state, done_progress, is_failed, result_message, is_valid, start_time, end_time, rows_read, query_sql, job_id FROM saved_query_job WHERE job_id IN ('%s') LIMIT %s
 #end
 #sql("SAVED_QUERY_JOB_SAME")
 SELECT job_id AS id FROM saved_query_job WHERE is_valid = 1 AND state IN ( 'PENDING', 'RUNNING') AND is_failed = 0 AND query_sql = '%s' LIMIT 1
diff --git a/src/main/resources/schema-syntax-validation.json b/src/main/resources/schema-syntax-validation.json
new file mode 100644
index 00000000..2870eba3
--- /dev/null
+++ b/src/main/resources/schema-syntax-validation.json
@@ -0,0 +1,120 @@
+{
+  "$schema": "http://json-schema.org/draft-07/schema#",
+  "properties": {
+    "type": {
+      "enum": ["record","enum","array","map","union","fixed"]
+    },
+    "doc": {
+      "properties": {
+        "schema_query": {
+          "properties": {
+            "time": {
+              "items": {
+                "enum": [
+                  "$ENUM_VALUES$"
+                ]
+              },
+              "uniqueItems": true
+            },
+            "dimensions": {
+              "items": {
+                "enum": [
+                  "$ENUM_VALUES$"
+                ]
+              },
+              "uniqueItems": true
+            },
+            "metrics": {
+              "items": {
+                "enum": [
+                  "$ENUM_VALUES$"
+                ]
+              },
+              "uniqueItems": true
+            },
+            "filters": {
+              "items": {
+                "enum": [
+                  "$ENUM_VALUES$"
+                ]
+              },
+              "uniqueItems": true
+            },
+            "details": {
+              "patternProperties": {
+                ".*": {
+                  "items": {
+                    "enum": [
+                      "$ENUM_VALUES$"
+                    ]
+                  },
+                  "uniqueItems": true
+                }
+              }
+            }
+          }
+        },
+        "decoded_as": {
+          "patternProperties": {
+            ".*": {
+              "patternProperties": {
+                ".*": {
+                  "items": {
+                    "enum": [
+                      "$ENUM_VALUES$"
+                    ]
+                  },
+                  "uniqueItems": true
+                }
+              }
+            }
+          }
+        }
+      },
+      "message": {
+        "enum" : "{0}: described field not found in $.fields[*].name"
+      }
+    },
+    "fields": {
+      "items": {
+        "properties": {
+          "type": {
+            "oneOf": [
+              {
+                "type": "string",
+                "enum": ["null", "boolean", "int", "long", "float", "double", "bytes", "string"]
+              },
+              {
+                "type": "object",
+                "properties": {
+                  "type": {
+                    "enum": ["array", "null", "boolean", "int", "long", "float", "double", "bytes", "string"]
+                  },
+                  "items": {
+                    "enum": ["null", "boolean", "int", "long", "float", "double", "bytes", "string"]
+                  }
+                },
+                "required": ["type"]
+              }
+            ]
+          },
+          "doc": {
+            "required": [
+              "visibility"
+            ]
+          }
+        },
+        "required": [
+          "name",
+          "type"
+        ]
+      }
+    }
+  },
+  "required": [
+    "type",
+    "name",
+    "namespace",
+    "fields"
+  ]
+}
\ No newline at end of file