DRILL-6050: Provide a limit to number of rows fetched for a query in UI · HanumathRao/drill@feaf5b2 · GitHub
Skip to content

Commit feaf5b2

Browse files
Kunal KhatuaHanumath Maduri
Kunal Khatua
authored and
Hanumath Maduri
committed
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 apache#1] Updated as per comments 1. Limit Wrapping Unchecked by default 2. Full List configuration of results [Update apache#2] Minor update [Update apache#3] Followup closes apache#1593
1 parent 5c90032 commit feaf5b2

6 files changed

Lines changed: 105 additions & 20 deletions

File tree

‎exec/java-exec/src/main/java/org/apache/drill/exec/ExecConstants.java‎

Lines changed: 4 additions & 0 deletions

‎exec/java-exec/src/main/java/org/apache/drill/exec/server/rest/QueryResources.java‎

Lines changed: 51 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -18,8 +18,11 @@
1818
package org.apache.drill.exec.server.rest;
1919

2020
import org.apache.drill.shaded.guava.com.google.common.base.CharMatcher;
21+
import org.apache.drill.shaded.guava.com.google.common.base.Joiner;
2122
import org.apache.drill.shaded.guava.com.google.common.collect.ImmutableList;
2223
import org.apache.drill.shaded.guava.com.google.common.collect.Lists;
24+
import org.apache.drill.common.config.DrillConfig;
25+
import org.apache.drill.exec.ExecConstants;
2326
import org.apache.drill.exec.server.rest.DrillRestServer.UserAuthEnabled;
2427
import org.apache.drill.exec.server.rest.auth.DrillUserPrincipal;
2528
import org.apache.drill.exec.server.rest.QueryWrapper.QueryResult;
@@ -36,6 +39,8 @@
3639
import javax.ws.rs.Produces;
3740
import javax.ws.rs.core.MediaType;
3841
import javax.ws.rs.core.SecurityContext;
42+
43+
import java.util.Collections;
3944
import java.util.List;
4045
import java.util.Map;
4146

@@ -55,11 +60,8 @@ public class QueryResources {
5560
@Produces(MediaType.TEXT_HTML)
5661
public Viewable getQuery() {
5762
return ViewableWithPermissions.create(
58-
authEnabled.get(),
59-
"/rest/query/query.ftl",
60-
sc,
61-
// if impersonation is enabled without authentication, will provide mechanism to add user name to request header from Web UI
62-
WebServer.isOnlyImpersonationEnabled(work.getContext().getConfig()));
63+
authEnabled.get(), "/rest/query/query.ftl",
64+
sc, new QueryPage(work));
6365
}
6466

6567
@POST
@@ -85,20 +87,56 @@ public Viewable submitQuery(@FormParam("query") String query,
8587
try {
8688
final String trimmedQueryString = CharMatcher.is(';').trimTrailingFrom(query.trim());
8789
final QueryResult result = submitQueryJSON(new QueryWrapper(trimmedQueryString, queryType));
88-
89-
return ViewableWithPermissions.create(authEnabled.get(), "/rest/query/result.ftl", sc, new TabularResult(result));
90+
List<Integer> rowsPerPageValues = work.getContext().getConfig().getIntList(ExecConstants.HTTP_WEB_CLIENT_RESULTSET_ROWS_PER_PAGE_VALUES);
91+
Collections.sort(rowsPerPageValues);
92+
final String rowsPerPageValuesAsStr = Joiner.on(",").join(rowsPerPageValues);
93+
return ViewableWithPermissions.create(authEnabled.get(), "/rest/query/result.ftl", sc, new TabularResult(result, rowsPerPageValuesAsStr));
9094
} catch (Exception | Error e) {
9195
logger.error("Query from Web UI Failed", e);
9296
return ViewableWithPermissions.create(authEnabled.get(), "/rest/query/errorMessage.ftl", sc, e);
9397
}
9498
}
9599

100+
/**
101+
* Model class for Query page
102+
*/
103+
public static class QueryPage {
104+
private final boolean onlyImpersonationEnabled;
105+
private final boolean autoLimitEnabled;
106+
private final int defaultRowsAutoLimited;
107+
108+
public QueryPage(WorkManager work) {
109+
DrillConfig config = work.getContext().getConfig();
110+
//if impersonation is enabled without authentication, will provide mechanism to add user name to request header from Web UI
111+
onlyImpersonationEnabled = WebServer.isOnlyImpersonationEnabled(config);
112+
autoLimitEnabled = config.getBoolean(ExecConstants.HTTP_WEB_CLIENT_RESULTSET_AUTOLIMIT_CHECKED);
113+
defaultRowsAutoLimited = config.getInt(ExecConstants.HTTP_WEB_CLIENT_RESULTSET_AUTOLIMIT_ROWS);
114+
}
115+
116+
public boolean isOnlyImpersonationEnabled() {
117+
return onlyImpersonationEnabled;
118+
}
119+
120+
public boolean isAutoLimitEnabled() {
121+
return autoLimitEnabled;
122+
}
123+
124+
public int getDefaultRowsAutoLimited() {
125+
return defaultRowsAutoLimited;
126+
}
127+
}
128+
129+
/**
130+
* Model class for Results page
131+
*/
96132
public static class TabularResult {
97133
private final List<String> columns;
98134
private final List<List<String>> rows;
99135
private final String queryId;
136+
private final String rowsPerPageValues;
100137

101-
public TabularResult(QueryResult result) {
138+
public TabularResult(QueryResult result, String rowsPerPageValuesAsStr) {
139+
rowsPerPageValues = rowsPerPageValuesAsStr;
102140
queryId = result.getQueryId();
103141
final List<List<String>> rows = Lists.newArrayList();
104142
for (Map<String, String> rowMap:result.rows) {
@@ -128,7 +166,11 @@ public List<String> getColumns() {
128166
public List<List<String>> getRows() {
129167
return rows;
130168
}
131-
}
132169

170+
//Used by results.ftl to render default number of pages per row
171+
public String getRowsPerPageValues() {
172+
return rowsPerPageValues;
173+
}
174+
}
133175

134176
}

‎exec/java-exec/src/main/resources/drill-module.conf‎

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -167,6 +167,13 @@ drill.exec: {
167167
maximum: 9223372036854775807
168168
}
169169
},
170+
web.client.resultset: {
171+
autolimit {
172+
checked: false,
173+
rows: 1000
174+
},
175+
rowsPerPageValues: [10, 25, 50, 75, 100]
176+
},
170177
web.options.filters: ["planner", "store", "parquet", "hashagg", "hashjoin"]
171178
},
172179
//setting javax variables for ssl configurations is being deprecated.

‎exec/java-exec/src/main/resources/rest/query/query.ftl‎

Lines changed: 5 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -41,7 +41,7 @@
4141

4242
<#include "*/runningQuery.ftl">
4343

44-
<#if model?? && model>
44+
<#if model.isOnlyImpersonationEnabled()>
4545
<div class="form-group">
4646
<label for="userName">User Name</label>
4747
<input type="text" size="30" name="userName" id="userName" placeholder="User Name">
@@ -77,9 +77,10 @@
7777
<input class="form-control" type="hidden" id="query" name="query"/>
7878
</div>
7979

80-
<button class="btn btn-default" type="button" onclick="<#if model?? && model>doSubmitQueryWithUserName()<#else>submitQuery()</#if>">
80+
<button class="btn btn-default" type="button" onclick="<#if model.isOnlyImpersonationEnabled()>doSubmitQueryWithUserName()<#else>wrapAndSubmitQuery()</#if>">
8181
Submit
8282
</button>
83+
<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>
8384
</form>
8485

8586
<script>
@@ -125,11 +126,10 @@
125126
document.getElementById('queryForm')
126127
.addEventListener('keydown', function(e) {
127128
if (!(e.keyCode == 13 && (e.metaKey || e.ctrlKey))) return;
128-
if (e.target.form)
129-
<#if model?? && model>doSubmitQueryWithUserName()<#else>submitQuery()</#if>;
129+
if (e.target.form) //Submit [Wrapped] Query
130+
<#if model.isOnlyImpersonationEnabled()>doSubmitQueryWithUserName()<#else>wrapAndSubmitQuery()</#if>;
130131
});
131132
</script>
132-
133133
</#macro>
134134

135135
<@page_html/>

‎exec/java-exec/src/main/resources/rest/query/result.ftl‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -74,7 +74,7 @@
7474
$('#result').dataTable( {
7575
"aaSorting": [],
7676
"scrollX" : true,
77-
"lengthMenu": [[10, 25, 50, 100, -1], [10, 25, 50, 100, "All"]],
77+
"lengthMenu": [[${model.getRowsPerPageValues()},-1], [${model.getRowsPerPageValues()},"ALL"]],
7878
"lengthChange": true,
7979
"dom": '<"H"lCfr>t<"F"ip>',
8080
"jQueryUI" : true

‎exec/java-exec/src/main/resources/rest/static/js/querySubmission.js‎

Lines changed: 37 additions & 5 deletions

0 commit comments

Comments
 (0)