[incubator-pinot] 01/01: [TE] SQL migration - escape the keywords

[jihao]

This is an automated email from the ASF dual-hosted git repository.

jihao pushed a commit to branch sql-migration-escape
in repository https://gitbox.apache.org/repos/asf/incubator-pinot.git

commit 6df4be10cfaba0e927802c0b32c43c41c5e763dc
Author: Jihao Zhang <jihzh...@linkedin.com>
AuthorDate: Tue Feb 2 13:25:25 2021 -0800

    [TE] SQL migration - escape the keywords
---
 .../pinot/thirdeye/datasource/pinot/PqlUtilsTest.java      |  6 +++---
 .../apache/pinot/thirdeye/datasource/pinot/SqlUtils.java   | 14 ++++++++++++--
 2 files changed, 15 insertions(+), 5 deletions(-)

diff --git 
a/thirdeye/thirdeye-dashboard/src/test/java/org/apache/pinot/thirdeye/datasource/pinot/PqlUtilsTest.java
 
b/thirdeye/thirdeye-dashboard/src/test/java/org/apache/pinot/thirdeye/datasource/pinot/PqlUtilsTest.java
index 382bb4c..725291c 100644
--- 
a/thirdeye/thirdeye-dashboard/src/test/java/org/apache/pinot/thirdeye/datasource/pinot/PqlUtilsTest.java
+++ 
b/thirdeye/thirdeye-dashboard/src/test/java/org/apache/pinot/thirdeye/datasource/pinot/PqlUtilsTest.java
@@ -185,7 +185,7 @@ public class PqlUtilsTest {
 
     String pql = SqlUtils.getSql(request, metricFunction, 
ArrayListMultimap.<String, String>create(), timeSpec);
 
-    Assert.assertEquals(pql, "SELECT dimension, AVG(metric) FROM collection 
WHERE  Date >= 1 AND Date < 2 GROUP BY dimension LIMIT 12345");
+    Assert.assertEquals(pql, "SELECT dimension, AVG(metric) FROM collection 
WHERE  \"Date\" >= 1 AND \"Date\" < 2 GROUP BY dimension LIMIT 12345");
   }
 
   @Test
@@ -201,8 +201,8 @@ public class PqlUtilsTest {
         .setGroupBy("dimension")
         .build("ref");
 
-    String pql = SqlUtils.getSql(request, metricFunction, 
ArrayListMultimap.<String, String>create(), timeSpec);
+    String pql = SqlUtils.getSql(request, metricFunction, 
ArrayListMultimap.create(), timeSpec);
 
-    Assert.assertEquals(pql, "SELECT dimension, AVG(metric) FROM collection 
WHERE  Date >= 1 AND Date < 2 GROUP BY dimension LIMIT 100000");
+    Assert.assertEquals(pql, "SELECT dimension, AVG(metric) FROM collection 
WHERE  \"Date\" >= 1 AND \"Date\" < 2 GROUP BY dimension LIMIT 100000");
   }
 }
diff --git 
a/thirdeye/thirdeye-pinot/src/main/java/org/apache/pinot/thirdeye/datasource/pinot/SqlUtils.java
 
b/thirdeye/thirdeye-pinot/src/main/java/org/apache/pinot/thirdeye/datasource/pinot/SqlUtils.java
index a73049a..b418252 100644
--- 
a/thirdeye/thirdeye-pinot/src/main/java/org/apache/pinot/thirdeye/datasource/pinot/SqlUtils.java
+++ 
b/thirdeye/thirdeye-pinot/src/main/java/org/apache/pinot/thirdeye/datasource/pinot/SqlUtils.java
@@ -24,6 +24,7 @@ import com.google.common.base.Predicate;
 import com.google.common.collect.Collections2;
 import com.google.common.collect.Lists;
 import com.google.common.collect.Multimap;
+import java.util.regex.Pattern;
 import org.apache.pinot.spi.data.DateTimeFieldSpec;
 import org.apache.pinot.thirdeye.common.time.TimeGranularity;
 import org.apache.pinot.thirdeye.common.time.TimeSpec;
@@ -78,6 +79,11 @@ public class SqlUtils {
   private static final int DEFAULT_TOP = 100000;
   private static final String PERCENTILE_TDIGEST_PREFIX = "percentileTDigest";
 
+  // for escaping queries that have reserved keywords
+  private static final String RESERVED_KEYWORDS = 
"(DATE|TIME|TIMESTAMP|GROUPS|TABLE)";
+  private static final Pattern RESERVED_KEYWORD_PATTERN = Pattern.compile(
+      "([^a-zA-Z0-9_$\"]|^)" + RESERVED_KEYWORDS + "([^a-zA-Z0-9_$\"]|$)", 
Pattern.CASE_INSENSITIVE);
+  private static final String RESERVED_KEYWORD_REPLACEMENT = "$1\"$2\"$3";
 
   /**
    * Returns sql to calculate the sum of all raw metrics required for 
<tt>request</tt>, grouped by
@@ -123,7 +129,7 @@ public class SqlUtils {
       sb.append(" LIMIT ").append(limit);
     }
 
-    return sb.toString();
+    return escapeSqlReservedKeywords(sb.toString());
   }
 
   private static String getSelectionClause(MetricConfigDTO metricConfig, 
MetricFunction metricFunction,
@@ -189,9 +195,13 @@ public class SqlUtils {
         request.getGroupBy(), request.getGroupByTimeGranularity(), 
dataTimeSpec,
         metricNamesList, metricNamesColumnsList, metricValuesColumn, 
request.getLimit());
 
-    return dimensionAsMetricPql;
+    return escapeSqlReservedKeywords(dimensionAsMetricPql);
   }
 
+  private static String escapeSqlReservedKeywords(String query) {
+    // escape all reserve keywords with double quotes
+    return 
RESERVED_KEYWORD_PATTERN.matcher(query).replaceAll(RESERVED_KEYWORD_REPLACEMENT);
+  }
 
   private static String getDimensionAsMetricSql(MetricFunction metricFunction, 
DateTime startTime,
       DateTime endTimeExclusive, Multimap<String, String> filterSet, 
List<String> groupBy,


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@pinot.apache.org
For additional commands, e-mail: commits-h...@pinot.apache.org