[incubator-pinot] branch master updated: [TE] SQL migration - escape the keywords (#6528)

[ji...@apache.org]

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

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


The following commit(s) were added to refs/heads/master by this push:
     new 84ae4f1  [TE] SQL migration - escape the keywords (#6528)
84ae4f1 is described below

commit 84ae4f111926cfc8758698402b56702a96fa0f28
Author: Jihao Zhang <ji...@linkedin.com>
AuthorDate: Tue Feb 2 15:01:17 2021 -0800

    [TE] SQL migration - escape the keywords (#6528)
    
    This PR adds the escaping for the Pinot SQL queries 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-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org