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

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 ceae23778..628506825 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 52337672d..61af7c7ac 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 ac35cd90d..4a5f07549 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 38ed7c9c2..c4549f7a5 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 7a17c648b..9f16c1f31 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 e62cc8f33..1183682a7 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);
+    }
+}