You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@superset.apache.org by GitBox <gi...@apache.org> on 2019/01/07 21:40:42 UTC
[GitHub] timifasubaa closed pull request #4834: Load async sql lab results
early for Presto
timifasubaa closed pull request #4834: Load async sql lab results early for Presto
URL: https://github.com/apache/incubator-superset/pull/4834
This is a PR merged from a forked repository.
As GitHub hides the original diff on merge, it is displayed below for
the sake of provenance:
As this is a foreign pull request (from a fork), the diff is supplied
below (as it won't show otherwise due to GitHub magic):
diff --git a/superset/assets/src/SqlLab/actions.js b/superset/assets/src/SqlLab/actions.js
index 644947023b..298c516c40 100644
--- a/superset/assets/src/SqlLab/actions.js
+++ b/superset/assets/src/SqlLab/actions.js
@@ -36,6 +36,7 @@ export const START_QUERY = 'START_QUERY';
export const STOP_QUERY = 'STOP_QUERY';
export const REQUEST_QUERY_RESULTS = 'REQUEST_QUERY_RESULTS';
export const QUERY_SUCCESS = 'QUERY_SUCCESS';
+export const PREFETCH_SUCCESS = 'PREFETCH_SUCCESS';
export const QUERY_FAILED = 'QUERY_FAILED';
export const CLEAR_QUERY_RESULTS = 'CLEAR_QUERY_RESULTS';
export const REMOVE_DATA_PREVIEW = 'REMOVE_DATA_PREVIEW';
@@ -78,6 +79,10 @@ export function querySuccess(query, results) {
return { type: QUERY_SUCCESS, query, results };
}
+export function prefetchSuccess(query, results) {
+ return { type: PREFETCH_SUCCESS, query, results };
+}
+
export function queryFailed(query, msg) {
return { type: QUERY_FAILED, query, msg };
}
@@ -107,7 +112,11 @@ export function fetchQueryResults(query) {
dataType: 'json',
url: sqlJsonUrl,
success(results) {
- dispatch(querySuccess(query, results));
+ if (results.status === 'prefetched') {
+ dispatch(prefetchSuccess(query, results));
+ } else {
+ dispatch(querySuccess(query, results));
+ }
},
error(err) {
let msg = t('Failed at retrieving results from the results backend');
diff --git a/superset/assets/src/SqlLab/components/QueryAutoRefresh.jsx b/superset/assets/src/SqlLab/components/QueryAutoRefresh.jsx
index 55e06cc146..bd61deff26 100644
--- a/superset/assets/src/SqlLab/components/QueryAutoRefresh.jsx
+++ b/superset/assets/src/SqlLab/components/QueryAutoRefresh.jsx
@@ -23,7 +23,8 @@ class QueryAutoRefresh extends React.PureComponent {
const now = new Date().getTime();
return Object.values(queries)
.some(
- q => ['running', 'started', 'pending', 'fetching'].indexOf(q.state) >= 0 &&
+ q => ['running', 'started', 'pending', 'fetching', 'prefetched'].indexOf(
+ q.state) >= 0 &&
now - q.startDttm < MAX_QUERY_AGE_TO_POLL,
);
}
diff --git a/superset/assets/src/SqlLab/components/ResultSet.jsx b/superset/assets/src/SqlLab/components/ResultSet.jsx
index f36a1640c7..b7f74627d1 100644
--- a/superset/assets/src/SqlLab/components/ResultSet.jsx
+++ b/superset/assets/src/SqlLab/components/ResultSet.jsx
@@ -18,6 +18,7 @@ const propTypes = {
visualize: PropTypes.bool,
cache: PropTypes.bool,
height: PropTypes.number.isRequired,
+ has_prefetched: PropTypes.bool,
};
const defaultProps = {
search: true,
@@ -26,6 +27,7 @@ const defaultProps = {
csv: true,
actions: {},
cache: false,
+ has_prefetched: false,
};
const SEARCH_HEIGHT = 46;
@@ -61,7 +63,7 @@ export default class ResultSet extends React.PureComponent {
getControls() {
if (this.props.search || this.props.visualize || this.props.csv) {
let csvButton;
- if (this.props.csv) {
+ if (this.props.csv && this.props.query.state === 'success') {
csvButton = (
<Button bsSize="small" href={'/superset/csv/' + this.props.query.id}>
<i className="fa fa-file-text-o" /> {t('.CSV')}
@@ -214,7 +216,7 @@ export default class ResultSet extends React.PureComponent {
}
let progressBar;
let trackingUrl;
- if (query.progress > 0 && query.state === 'running') {
+ if (query.progress > 0 && (query.state === 'running' || query.state === 'prefetched')) {
progressBar = (
<ProgressBar
striped
@@ -222,6 +224,60 @@ export default class ResultSet extends React.PureComponent {
label={`${query.progress}%`}
/>);
}
+
+ if (query.state === 'prefetched') {
+ const results = query.results;
+ let data;
+ if (this.props.cache && query.cached) {
+ data = this.state.data;
+ } else if (results && results.data) {
+ data = results.data;
+ }
+ if (!this.props.has_prefetched) {
+ if (data && data.length > 0) {
+ return (
+ <div>
+ <div>
+ {progressBar}
+ </div>
+ <VisualizeModal
+ show={this.state.showModal}
+ query={this.props.query}
+ onHide={this.hideModal.bind(this)}
+ />
+ {this.getControls.bind(this)()}
+ {sql}
+ <FilterableTable
+ data={data}
+ orderedColumnKeys={results.columns.map(col => col.name)}
+ height={height}
+ filterText={this.state.searchText}
+ />
+ </div>
+ );
+ } else if (data && data.length === 0) {
+ return <Alert bsStyle="warning">The query returned no data</Alert>;
+ }
+ } else {
+ return (
+ <div>
+ <div>
+ <QueryStateLabel query={query} />
+ {progressBar}
+ </div>
+ <VisualizeModal
+ show={this.state.showModal}
+ query={this.props.query}
+ onHide={this.hideModal.bind(this)}
+ />
+ {this.getControls.bind(this)()}
+ {sql}
+ </div>
+ );
+ }
+ }
+
+
if (query.trackingUrl) {
trackingUrl = (
<Button
diff --git a/superset/assets/src/SqlLab/components/RunQueryActionButton.jsx b/superset/assets/src/SqlLab/components/RunQueryActionButton.jsx
index dd1d8b0250..dca3413b4b 100644
--- a/superset/assets/src/SqlLab/components/RunQueryActionButton.jsx
+++ b/superset/assets/src/SqlLab/components/RunQueryActionButton.jsx
@@ -20,7 +20,7 @@ const defaultProps = {
export default function RunQueryActionButton(props) {
const runBtnText = props.selectedText ? t('Run Selected Query') : t('Run Query');
const btnStyle = props.selectedText ? 'warning' : 'primary';
- const shouldShowStopBtn = ['running', 'pending'].indexOf(props.queryState) > -1;
+ const shouldShowStopBtn = ['running', 'pending', 'prefetched'].indexOf(props.queryState) > -1;
const commonBtnProps = {
bsSize: 'small',
diff --git a/superset/assets/src/SqlLab/reducers.js b/superset/assets/src/SqlLab/reducers.js
index 9c67f3822c..7f3a3c2d47 100644
--- a/superset/assets/src/SqlLab/reducers.js
+++ b/superset/assets/src/SqlLab/reducers.js
@@ -161,6 +161,22 @@ export const sqlLabReducer = function (state, action) {
[actions.REQUEST_QUERY_RESULTS]() {
return alterInObject(state, 'queries', action.query, { state: 'fetching' });
},
+ [actions.PREFETCH_SUCCESS]() {
+ let rows;
+ if (action.results.data) {
+ rows = action.results.data.length;
+ }
+ const alts = {
+ results: action.results,
+ rows,
+ state: 'prefetched',
+ errorMessage: null,
+ cached: false,
+ csv: false,
+ has_prefetched: true,
+ };
+ return alterInObject(state, 'queries', action.query, alts);
+ },
[actions.QUERY_SUCCESS]() {
let rows;
if (action.results.data) {
@@ -174,6 +190,7 @@ export const sqlLabReducer = function (state, action) {
state: action.query.state,
errorMessage: null,
cached: false,
+ csv: true,
};
return alterInObject(state, 'queries', action.query, alts);
},
diff --git a/superset/config.py b/superset/config.py
index 530b126896..48e445c103 100644
--- a/superset/config.py
+++ b/superset/config.py
@@ -250,7 +250,7 @@
MAPBOX_API_KEY = os.environ.get('MAPBOX_API_KEY', '')
# Maximum number of rows returned in the SQL editor
-SQL_MAX_ROW = 1000000
+SQL_MAX_ROW = 10000
DISPLAY_SQL_MAX_ROW = 1000
# Maximum number of tables/views displayed in the dropdown window in SQL Lab.
@@ -294,6 +294,12 @@ class CeleryConfig(object):
# Timeout duration for SQL Lab synchronous queries
SQLLAB_TIMEOUT = 30
+# When set to true, results from asynchronous sql lab are prefetched
+PREFETCH_ASYNC = True
+
+# Howmany rows to prefetch from asyncronous queries
+PREFETCH_ROWS = 100
+
# SQLLAB_DEFAULT_DBID
SQLLAB_DEFAULT_DBID = None
diff --git a/superset/db_engine_specs.py b/superset/db_engine_specs.py
index cbcc570934..a6fa370c1a 100644
--- a/superset/db_engine_specs.py
+++ b/superset/db_engine_specs.py
@@ -20,11 +20,13 @@
from collections import defaultdict, namedtuple
import inspect
+import json
import logging
import os
import re
import textwrap
import time
+import uuid
import boto3
from flask import g
@@ -40,7 +42,7 @@
import unicodecsv
from werkzeug.utils import secure_filename
-from superset import app, cache_util, conf, db, utils
+from superset import app, cache_util, conf, db, results_backend, utils
from superset.exceptions import SupersetTemplateException
from superset.utils import QueryStatus
@@ -72,8 +74,8 @@ class BaseEngineSpec(object):
inner_joins = True
@classmethod
- def fetch_data(cls, cursor, limit):
- if cls.limit_method == LimitMethod.FETCH_MANY:
+ def fetch_data(cls, cursor, limit, prefetch=False):
+ if cls.limit_method == LimitMethod.FETCH_MANY or prefetch:
return cursor.fetchmany(limit)
return cursor.fetchall()
@@ -726,7 +728,29 @@ def extra_table_metadata(cls, database, table_name, schema_name):
}
@classmethod
- def handle_cursor(cls, cursor, query, session):
+ def prefetch_results(cls, cursor, query, cache_timeout, session, limit):
+ data = cursor.fetchmany(limit)
+ column_names = cls.get_normalized_column_names(cursor.description)
+ cdf = utils.convert_results_to_df(column_names, data)
+ payload = dict(query_id=query.id)
+ payload.update({
+ 'status': utils.QueryStatus.PREFETCHED,
+ 'data': cdf.data if cdf.data else [],
+ 'columns': cdf.columns if cdf.columns else [],
+ 'query': query.to_dict(),
+ })
+
+ json_payload = json.dumps(payload, default=utils.json_iso_dttm_ser)
+ key = '{}'.format(uuid.uuid4())
+ prefetch_key = key
+ results_backend.set(
+ prefetch_key, utils.zlib_compress(json_payload), cache_timeout)
+ query.status = utils.QueryStatus.PREFETCHED
+ query.results_key = key
+ session.commit()
+
+ @classmethod
+ def handle_cursor(cls, cursor, query, session, cache_timeout=0):
"""Updates progress information"""
logging.info('Polling the cursor for progress')
polled = cursor.poll()
@@ -737,12 +761,20 @@ def handle_cursor(cls, cursor, query, session):
while polled:
# Update the object and wait for the kill signal.
stats = polled.get('stats', {})
-
query = session.query(type(query)).filter_by(id=query.id).one()
if query.status in [QueryStatus.STOPPED, QueryStatus.TIMED_OUT]:
cursor.cancel()
break
+ if (
+ config.get('PREFETCH_ASYNC') and
+ (not query.has_loaded_early)
+ ):
+ query.has_loaded_early = True
+ limit = config.get('PREFETCH_ROWS')
+ PrestoEngineSpec.prefetch_results(
+ cursor, query, cache_timeout, session, limit)
+
if stats:
completed_splits = float(stats.get('completedSplits'))
total_splits = float(stats.get('totalSplits'))
@@ -1079,6 +1111,8 @@ def handle_cursor(cls, cursor, query, session):
if query.status == QueryStatus.STOPPED:
cursor.cancel()
break
+ if hive.ttypes.TOperationState.RUNNING_STATE == polled.operationState:
+ BaseEngineSpec.fetch_data(cursor, 100, prefetch=True)
log = cursor.fetch_logs() or ''
if log:
diff --git a/superset/sql_lab.py b/superset/sql_lab.py
index c9f07ae906..b51e39e138 100644
--- a/superset/sql_lab.py
+++ b/superset/sql_lab.py
@@ -10,13 +10,11 @@
from celery.exceptions import SoftTimeLimitExceeded
from contextlib2 import contextmanager
-import numpy as np
-import pandas as pd
import sqlalchemy
from sqlalchemy.orm import sessionmaker
from sqlalchemy.pool import NullPool
-from superset import app, dataframe, db, results_backend, security_manager, utils
+from superset import app, db, results_backend, security_manager, utils
from superset.models.sql_lab import Query
from superset.sql_parse import SupersetQuery
from superset.utils import get_celery_app, QueryStatus
@@ -31,27 +29,6 @@ class SqlLabException(Exception):
pass
-def dedup(l, suffix='__'):
- """De-duplicates a list of string by suffixing a counter
-
- Always returns the same number of entries as provided, and always returns
- unique values.
-
- >>> print(','.join(dedup(['foo', 'bar', 'bar', 'bar'])))
- foo,bar,bar__1,bar__2
- """
- new_l = []
- seen = {}
- for s in l:
- if s in seen:
- seen[s] += 1
- s += suffix + str(seen[s])
- else:
- seen[s] = 0
- new_l.append(s)
- return new_l
-
-
def get_query(query_id, session, retry_count=5):
"""attemps to get the query and retry if it cannot"""
query = None
@@ -96,31 +73,12 @@ def session_scope(nullpool):
session.close()
-def convert_results_to_df(column_names, data):
- """Convert raw query results to a DataFrame."""
- column_names = dedup(column_names)
-
- # check whether the result set has any nested dict columns
- if data:
- first_row = data[0]
- has_dict_col = any([isinstance(c, dict) for c in first_row])
- df_data = list(data) if has_dict_col else np.array(data, dtype=object)
- else:
- df_data = []
-
- cdf = dataframe.SupersetDataFrame(
- pd.DataFrame(df_data, columns=column_names))
-
- return cdf
-
-
@celery_app.task(bind=True, soft_time_limit=SQLLAB_TIMEOUT)
def get_sql_results(
ctask, query_id, rendered_query, return_results=True, store_results=False,
user_name=None):
"""Executes the sql query returns the results."""
with session_scope(not ctask.request.called_directly) as session:
-
try:
return execute_sql(
ctask, query_id, rendered_query, return_results, store_results, user_name,
@@ -141,7 +99,6 @@ def execute_sql(
user_name=None, session=None,
):
"""Executes the sql query returns the results."""
-
query = get_query(query_id, session)
payload = dict(query_id=query_id)
@@ -200,10 +157,12 @@ def handle_error(msg):
query.executed_sql = executed_sql
query.status = QueryStatus.RUNNING
query.start_running_time = utils.now_as_float()
+ query.has_loaded_early = False
session.merge(query)
session.commit()
logging.info("Set query to 'running'")
conn = None
+
try:
engine = database.get_sqla_engine(
schema=query.schema,
@@ -241,10 +200,21 @@ def handle_error(msg):
conn.close()
if query.status == utils.QueryStatus.STOPPED:
- return handle_error('The query has been stopped')
-
- cdf = convert_results_to_df(column_names, data)
-
+ return json.dumps(
+ {
+ 'query_id': query.id,
+ 'status': query.status,
+ 'query': query.to_dict(),
+ },
+ default=utils.json_iso_dttm_ser)
+
+ if query.has_loaded_early:
+ preloaded_data = results_backend.get(query.results_key)
+ preloaded_data = json.loads(
+ utils.zlib_decompress_to_string(preloaded_data))['data']
+ preloaded_data = [list(row.values()) for row in preloaded_data]
+ data = preloaded_data + data
+ cdf = utils.convert_results_to_df(column_names, data)
query.rows = cdf.size
query.progress = 100
query.status = QueryStatus.SUCCESS
@@ -263,11 +233,15 @@ def handle_error(msg):
payload.update({
'status': query.status,
'data': cdf.data if cdf.data else [],
+ 'results_key': query.results_key,
'columns': cdf.columns if cdf.columns else [],
'query': query.to_dict(),
})
if store_results:
- key = '{}'.format(uuid.uuid4())
+ if not query.results_key:
+ query.results_key = '{}'.format(uuid.uuid4())
+
+ key = query.results_key
logging.info('Storing results in results backend, key: {}'.format(key))
json_payload = json.dumps(payload, default=utils.json_iso_dttm_ser)
cache_timeout = database.cache_timeout
@@ -275,7 +249,6 @@ def handle_error(msg):
cache_timeout = config.get('CACHE_DEFAULT_TIMEOUT', 0)
results_backend.set(key, utils.zlib_compress(json_payload), cache_timeout)
query.results_key = key
- query.end_result_backend_time = utils.now_as_float()
session.merge(query)
session.commit()
diff --git a/superset/utils.py b/superset/utils.py
index 08ce0d2f38..cf6027dd65 100644
--- a/superset/utils.py
+++ b/superset/utils.py
@@ -68,6 +68,27 @@ def flasher(msg, severity=None):
logging.info(msg)
+def dedup(l, suffix='__'):
+ """De-duplicates a list of string by suffixing a counter
+
+ Always returns the same number of entries as provided, and always returns
+ unique values.
+
+ >>> print(','.join(dedup(['foo', 'bar', 'bar', 'bar'])))
+ foo,bar,bar__1,bar__2
+ """
+ new_l = []
+ seen = {}
+ for s in l:
+ if s in seen:
+ seen[s] += 1
+ s += suffix + str(seen[s])
+ else:
+ seen[s] = 0
+ new_l.append(s)
+ return new_l
+
+
class _memoized(object): # noqa
"""Decorator that caches a function's return value each time it is called
@@ -150,6 +171,29 @@ def string_to_num(s):
return None
+def convert_results_to_df(cursor_description, data):
+ import numpy as np
+ from superset import dataframe
+ import pandas as pd
+ """Convert raw query results to a DataFrame."""
+ column_names = (
+ [col for col in cursor_description] if cursor_description else [])
+ column_names = dedup(column_names)
+
+ # check whether the result set has any nested dict columns
+ if data:
+ first_row = data[0]
+ has_dict_col = any([isinstance(c, dict) for c in first_row])
+ df_data = list(data) if has_dict_col else np.array(data, dtype=object)
+ else:
+ df_data = []
+
+ pdf = pd.DataFrame(df_data, columns=column_names)
+ cdf = dataframe.SupersetDataFrame(
+ pdf)
+ return cdf
+
+
class DimSelector(Having):
def __init__(self, **args):
# Just a hack to prevent any exceptions
@@ -560,6 +604,7 @@ class QueryStatus(object):
"""Enum-type class for query statuses"""
STOPPED = 'stopped'
+ PREFETCHED = 'prefetched'
FAILED = 'failed'
PENDING = 'pending'
RUNNING = 'running'
diff --git a/tests/sqllab_tests.py b/tests/sqllab_tests.py
index 49926f80de..0b51ae8581 100644
--- a/tests/sqllab_tests.py
+++ b/tests/sqllab_tests.py
@@ -13,7 +13,6 @@
from superset import db, security_manager, utils
from superset.models.sql_lab import Query
-from superset.sql_lab import convert_results_to_df
from .base_tests import SupersetTestCase
@@ -205,7 +204,7 @@ def test_alias_duplicate(self):
def test_df_conversion_no_dict(self):
cols = ['string_col', 'int_col', 'float_col']
data = [['a', 4, 4.0]]
- cdf = convert_results_to_df(cols, data)
+ cdf = utils.convert_results_to_df(cols, data)
self.assertEquals(len(data), cdf.size)
self.assertEquals(len(cols), len(cdf.columns))
@@ -213,7 +212,7 @@ def test_df_conversion_no_dict(self):
def test_df_conversion_tuple(self):
cols = ['string_col', 'int_col', 'list_col', 'float_col']
data = [(u'Text', 111, [123], 1.0)]
- cdf = convert_results_to_df(cols, data)
+ cdf = utils.convert_results_to_df(cols, data)
self.assertEquals(len(data), cdf.size)
self.assertEquals(len(cols), len(cdf.columns))
@@ -221,7 +220,7 @@ def test_df_conversion_tuple(self):
def test_df_conversion_dict(self):
cols = ['string_col', 'dict_col', 'int_col']
data = [['a', {'c1': 1, 'c2': 2, 'c3': 3}, 4]]
- cdf = convert_results_to_df(cols, data)
+ cdf = utils.convert_results_to_df(cols, data)
self.assertEquals(len(data), cdf.size)
self.assertEquals(len(cols), len(cdf.columns))
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org