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 <[email protected]>
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: [email protected]
For additional commands, e-mail: [email protected]
