You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@drill.apache.org by hm...@apache.org on 2019/01/07 21:47:58 UTC
[drill] 04/05: DRILL-6050: Provide a limit to number of rows
fetched for a query in UI
This is an automated email from the ASF dual-hosted git repository.
hmaduri pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/drill.git
commit feaf5b24e9639669bc43b6673ab36cf403a32525
Author: Kunal Khatua <kk...@maprtech.com>
AuthorDate: Mon Jan 7 11:19:15 2019 -0800
DRILL-6050: Provide a limit to number of rows fetched for a query in UI
Currently, the WebServer side needs to process the entire set of results and stream it back to the WebClient.
Since the WebUI does paginate results, we can load a larger set for pagination on the browser client and relieve pressure off the WebServer to host all the data (most of which will never be streamed to the browser).
e.g. Fetching all rows from a 1Billion records table is impractical and can be capped at (say) 1K. Currently, the user has to explicitly specify LIMIT in the submitted query.
An option is provided in the field to allow for this entry, and can be set to selected by default for the Web UI.
The submitted query indicates that an auto-limiting wrapper was applied.
[Update #1] Updated as per comments
1. Limit Wrapping Unchecked by default
2. Full List configuration of results
[Update #2] Minor update
[Update #3] Followup
closes #1593
---
.../java/org/apache/drill/exec/ExecConstants.java | 4 ++
.../drill/exec/server/rest/QueryResources.java | 60 ++++++++++++++++++----
.../java-exec/src/main/resources/drill-module.conf | 7 +++
.../src/main/resources/rest/query/query.ftl | 10 ++--
.../src/main/resources/rest/query/result.ftl | 2 +-
.../resources/rest/static/js/querySubmission.js | 42 +++++++++++++--
6 files changed, 105 insertions(+), 20 deletions(-)
diff --git a/exec/java-exec/src/main/java/org/apache/drill/exec/ExecConstants.java b/exec/java-exec/src/main/java/org/apache/drill/exec/ExecConstants.java
index ceae237..6285068 100644
--- a/exec/java-exec/src/main/java/org/apache/drill/exec/ExecConstants.java
+++ b/exec/java-exec/src/main/java/org/apache/drill/exec/ExecConstants.java
@@ -224,6 +224,10 @@ public final class ExecConstants {
public static final String HTTP_AUTHENTICATION_MECHANISMS = "drill.exec.http.auth.mechanisms";
public static final String HTTP_SPNEGO_PRINCIPAL = "drill.exec.http.auth.spnego.principal";
public static final String HTTP_SPNEGO_KEYTAB = "drill.exec.http.auth.spnego.keytab";
+ //Control Web UI Resultset
+ public static final String HTTP_WEB_CLIENT_RESULTSET_AUTOLIMIT_CHECKED = "drill.exec.http.web.client.resultset.autolimit.checked";
+ public static final String HTTP_WEB_CLIENT_RESULTSET_AUTOLIMIT_ROWS = "drill.exec.http.web.client.resultset.autolimit.rows";
+ public static final String HTTP_WEB_CLIENT_RESULTSET_ROWS_PER_PAGE_VALUES = "drill.exec.http.web.client.resultset.rowsPerPageValues";
//Customize filters in options
public static final String HTTP_WEB_OPTIONS_FILTERS = "drill.exec.http.web.options.filters";
public static final String SYS_STORE_PROVIDER_CLASS = "drill.exec.sys.store.provider.class";
diff --git a/exec/java-exec/src/main/java/org/apache/drill/exec/server/rest/QueryResources.java b/exec/java-exec/src/main/java/org/apache/drill/exec/server/rest/QueryResources.java
index 5233767..61af7c7 100644
--- a/exec/java-exec/src/main/java/org/apache/drill/exec/server/rest/QueryResources.java
+++ b/exec/java-exec/src/main/java/org/apache/drill/exec/server/rest/QueryResources.java
@@ -18,8 +18,11 @@
package org.apache.drill.exec.server.rest;
import org.apache.drill.shaded.guava.com.google.common.base.CharMatcher;
+import org.apache.drill.shaded.guava.com.google.common.base.Joiner;
import org.apache.drill.shaded.guava.com.google.common.collect.ImmutableList;
import org.apache.drill.shaded.guava.com.google.common.collect.Lists;
+import org.apache.drill.common.config.DrillConfig;
+import org.apache.drill.exec.ExecConstants;
import org.apache.drill.exec.server.rest.DrillRestServer.UserAuthEnabled;
import org.apache.drill.exec.server.rest.auth.DrillUserPrincipal;
import org.apache.drill.exec.server.rest.QueryWrapper.QueryResult;
@@ -36,6 +39,8 @@ import javax.ws.rs.Path;
import javax.ws.rs.Produces;
import javax.ws.rs.core.MediaType;
import javax.ws.rs.core.SecurityContext;
+
+import java.util.Collections;
import java.util.List;
import java.util.Map;
@@ -55,11 +60,8 @@ public class QueryResources {
@Produces(MediaType.TEXT_HTML)
public Viewable getQuery() {
return ViewableWithPermissions.create(
- authEnabled.get(),
- "/rest/query/query.ftl",
- sc,
- // if impersonation is enabled without authentication, will provide mechanism to add user name to request header from Web UI
- WebServer.isOnlyImpersonationEnabled(work.getContext().getConfig()));
+ authEnabled.get(), "/rest/query/query.ftl",
+ sc, new QueryPage(work));
}
@POST
@@ -85,20 +87,56 @@ public class QueryResources {
try {
final String trimmedQueryString = CharMatcher.is(';').trimTrailingFrom(query.trim());
final QueryResult result = submitQueryJSON(new QueryWrapper(trimmedQueryString, queryType));
-
- return ViewableWithPermissions.create(authEnabled.get(), "/rest/query/result.ftl", sc, new TabularResult(result));
+ List<Integer> rowsPerPageValues = work.getContext().getConfig().getIntList(ExecConstants.HTTP_WEB_CLIENT_RESULTSET_ROWS_PER_PAGE_VALUES);
+ Collections.sort(rowsPerPageValues);
+ final String rowsPerPageValuesAsStr = Joiner.on(",").join(rowsPerPageValues);
+ return ViewableWithPermissions.create(authEnabled.get(), "/rest/query/result.ftl", sc, new TabularResult(result, rowsPerPageValuesAsStr));
} catch (Exception | Error e) {
logger.error("Query from Web UI Failed", e);
return ViewableWithPermissions.create(authEnabled.get(), "/rest/query/errorMessage.ftl", sc, e);
}
}
+ /**
+ * Model class for Query page
+ */
+ public static class QueryPage {
+ private final boolean onlyImpersonationEnabled;
+ private final boolean autoLimitEnabled;
+ private final int defaultRowsAutoLimited;
+
+ public QueryPage(WorkManager work) {
+ DrillConfig config = work.getContext().getConfig();
+ //if impersonation is enabled without authentication, will provide mechanism to add user name to request header from Web UI
+ onlyImpersonationEnabled = WebServer.isOnlyImpersonationEnabled(config);
+ autoLimitEnabled = config.getBoolean(ExecConstants.HTTP_WEB_CLIENT_RESULTSET_AUTOLIMIT_CHECKED);
+ defaultRowsAutoLimited = config.getInt(ExecConstants.HTTP_WEB_CLIENT_RESULTSET_AUTOLIMIT_ROWS);
+ }
+
+ public boolean isOnlyImpersonationEnabled() {
+ return onlyImpersonationEnabled;
+ }
+
+ public boolean isAutoLimitEnabled() {
+ return autoLimitEnabled;
+ }
+
+ public int getDefaultRowsAutoLimited() {
+ return defaultRowsAutoLimited;
+ }
+ }
+
+ /**
+ * Model class for Results page
+ */
public static class TabularResult {
private final List<String> columns;
private final List<List<String>> rows;
private final String queryId;
+ private final String rowsPerPageValues;
- public TabularResult(QueryResult result) {
+ public TabularResult(QueryResult result, String rowsPerPageValuesAsStr) {
+ rowsPerPageValues = rowsPerPageValuesAsStr;
queryId = result.getQueryId();
final List<List<String>> rows = Lists.newArrayList();
for (Map<String, String> rowMap:result.rows) {
@@ -128,7 +166,11 @@ public class QueryResources {
public List<List<String>> getRows() {
return rows;
}
- }
+ //Used by results.ftl to render default number of pages per row
+ public String getRowsPerPageValues() {
+ return rowsPerPageValues;
+ }
+ }
}
diff --git a/exec/java-exec/src/main/resources/drill-module.conf b/exec/java-exec/src/main/resources/drill-module.conf
index ac35cd9..4a5f075 100644
--- a/exec/java-exec/src/main/resources/drill-module.conf
+++ b/exec/java-exec/src/main/resources/drill-module.conf
@@ -167,6 +167,13 @@ drill.exec: {
maximum: 9223372036854775807
}
},
+ web.client.resultset: {
+ autolimit {
+ checked: false,
+ rows: 1000
+ },
+ rowsPerPageValues: [10, 25, 50, 75, 100]
+ },
web.options.filters: ["planner", "store", "parquet", "hashagg", "hashjoin"]
},
//setting javax variables for ssl configurations is being deprecated.
diff --git a/exec/java-exec/src/main/resources/rest/query/query.ftl b/exec/java-exec/src/main/resources/rest/query/query.ftl
index 38ed7c9..c4549f7 100644
--- a/exec/java-exec/src/main/resources/rest/query/query.ftl
+++ b/exec/java-exec/src/main/resources/rest/query/query.ftl
@@ -41,7 +41,7 @@
<#include "*/runningQuery.ftl">
- <#if model?? && model>
+ <#if model.isOnlyImpersonationEnabled()>
<div class="form-group">
<label for="userName">User Name</label>
<input type="text" size="30" name="userName" id="userName" placeholder="User Name">
@@ -77,9 +77,10 @@
<input class="form-control" type="hidden" id="query" name="query"/>
</div>
- <button class="btn btn-default" type="button" onclick="<#if model?? && model>doSubmitQueryWithUserName()<#else>submitQuery()</#if>">
+ <button class="btn btn-default" type="button" onclick="<#if model.isOnlyImpersonationEnabled()>doSubmitQueryWithUserName()<#else>wrapAndSubmitQuery()</#if>">
Submit
</button>
+ <input type="checkbox" name="forceLimit" value="limit" <#if model.isAutoLimitEnabled()>checked</#if>> Limit results to <input type="text" id="queryLimit" min="0" value="${model.getDefaultRowsAutoLimited()}" size="6" pattern="[0-9]*"> rows <span class="glyphicon glyphicon-info-sign" onclick="alert('Limits the number of records retrieved in the query')" style="cursor:pointer"></span>
</form>
<script>
@@ -125,11 +126,10 @@
document.getElementById('queryForm')
.addEventListener('keydown', function(e) {
if (!(e.keyCode == 13 && (e.metaKey || e.ctrlKey))) return;
- if (e.target.form)
- <#if model?? && model>doSubmitQueryWithUserName()<#else>submitQuery()</#if>;
+ if (e.target.form) //Submit [Wrapped] Query
+ <#if model.isOnlyImpersonationEnabled()>doSubmitQueryWithUserName()<#else>wrapAndSubmitQuery()</#if>;
});
</script>
-
</#macro>
<@page_html/>
diff --git a/exec/java-exec/src/main/resources/rest/query/result.ftl b/exec/java-exec/src/main/resources/rest/query/result.ftl
index 7a17c64..9f16c1f 100644
--- a/exec/java-exec/src/main/resources/rest/query/result.ftl
+++ b/exec/java-exec/src/main/resources/rest/query/result.ftl
@@ -74,7 +74,7 @@
$('#result').dataTable( {
"aaSorting": [],
"scrollX" : true,
- "lengthMenu": [[10, 25, 50, 100, -1], [10, 25, 50, 100, "All"]],
+ "lengthMenu": [[${model.getRowsPerPageValues()},-1], [${model.getRowsPerPageValues()},"ALL"]],
"lengthChange": true,
"dom": '<"H"lCfr>t<"F"ip>',
"jQueryUI" : true
diff --git a/exec/java-exec/src/main/resources/rest/static/js/querySubmission.js b/exec/java-exec/src/main/resources/rest/static/js/querySubmission.js
index e62cc8f..1183682 100644
--- a/exec/java-exec/src/main/resources/rest/static/js/querySubmission.js
+++ b/exec/java-exec/src/main/resources/rest/static/js/querySubmission.js
@@ -15,6 +15,7 @@ var userName = null;
var elapsedTime = 0;
var delay = 1000; //msec
var timeTracker = null; //Handle for stopping watch
+var userName = null;
//Show cancellation status
function popupAndWait() {
@@ -38,24 +39,33 @@ function closePopup() {
$("#queryLoadingModal").modal("hide");
}
-//Submit query with username
+// Wrap & Submit Query (invoked if impersonation is enabled to check for username)
function doSubmitQueryWithUserName() {
- var userName = document.getElementById("userName").value;
+ userName = document.getElementById("userName").value;
if (!userName.trim()) {
alert("Please fill in User Name field");
return;
}
+ //Wrap and Submit query
+ wrapAndSubmitQuery();
+}
+
+//Wrap & Submit Query (invoked directly if impersonation is not enabled)
+function wrapAndSubmitQuery() {
+ //Wrap if required
+ wrapQuery();
+ //Submit query
submitQuery();
}
-//Submit Query (used if impersonation is not enabled)
+//Submit Query
function submitQuery() {
popupAndWait();
//Submit query
$.ajax({
type: "POST",
beforeSend: function (request) {
- if (typeof userName !== 'undefined' && userName != null && userName.length > 0) {
+ if (typeof userName !== 'undefined' && userName !== null && userName.length > 0) {
request.setRequestHeader("User-Name", userName);
}
},
@@ -72,4 +82,26 @@ function submitQuery() {
alert(errorThrown);
}
});
-}
\ No newline at end of file
+}
+
+//Wraps a query with Limit by directly changing the query in the hidden textbox in the UI (see /query.ftl)
+function wrapQuery() {
+ var origQueryText = $('#query').attr('value');
+ //dBug: console.log("Query Input:" + origQueryText);
+ var mustWrapWithLimit = $('input[name="forceLimit"]:checked').length > 0;
+ if (mustWrapWithLimit) {
+ var semicolonIdx = origQueryText.lastIndexOf(';');
+ //Check and eliminate trailing semicolon
+ if (semicolonIdx == origQueryText.length-1 ) {
+ origQueryText = origQueryText.substring(0, semicolonIdx)
+ }
+ var qLimit = $('#queryLimit').val();
+ var wrappedQuery = "-- [autoLimit: " + qLimit + " rows]\nselect * from (\n" + origQueryText + "\n) limit " + qLimit;
+ //dBug: console.log("Query Output:" + wrappedQuery);
+ //Wrapping Query
+ $('#query').attr('value', wrappedQuery);
+ } else {
+ //Do not change the query
+ //dBug: console.log("Query Output:" + origQueryText);
+ }
+}