<h2 dir="auto">[SIP-39] Global Async Query Support</h2>
<h3 dir="auto">Motivation</h3>
<ul dir="auto">
<li>Loading time on dashboards with many charts is excessive due to the number of concurrent connections to the server.</li>
<li>Synchronous connections left open waiting for result data to be returned block other requests to the server.</li>
<li>Browsers limit the number of concurrent connections to the same host to six, causing bottlenecks.</li>
<li>Some of this bottleneck is currently mitigated via the dashboard tabs feature, which defers loading of charts in background tabs. This lazy-loading could also be applied to charts outside of the viewport.</li>
<li><a href="https://github.com/apache/incubator-superset/issues/8655" data-hovercard-type="issue" data-hovercard-url="/apache/superset/issues/8655/hovercard">There is a proposal for SQL Lab to move away from tabs in the UI</a>, altering the current async query polling mechanisms.</li>
<li>Provide a standardized query interface for dashboards, charts and SQL Lab queries.</li>
</ul>
<h3 dir="auto">Proposed Change</h3>
<p dir="auto">Provide a configuration setting to enable async data loading for charts (dashboards, Explore) and SQL Lab. Instead of multiple synchronous requests to the server to load dashboard charts, we issue async requests to the server which enqueue a background job and return a job ID. A single persistent websocket connection to the server is opened to listen for results in a realtime fashion.</p>
<h2 dir="auto">Websockets via a sidecar application</h2>
<p dir="auto"><strong>Pros</strong></p>
<ul dir="auto">
<li>Excellent websocket browser support</li>
<li>Realtime synchronous communication channel</li>
<li>Browser connections limits not an issue (<a href="https://bugs.chromium.org/p/chromium/issues/detail?id=429419" rel="nofollow">255 in Chrome</a>)</li>
<li>Supports bi-directional communication (possible future use cases)</li>
<li>Authentication/authorization via initial HTTP request that is then upgraded to a WSS connection.</li>
</ul>
<p dir="auto"><strong>Cons</strong></p>
<ul dir="auto">
<li>Minor modifications to proxies, load balancers to support websocket connections
<ul dir="auto">
<li>Sticky sessions not required at the load balancer if the reconnection story is sound</li>
</ul>
</li>
<li>Requires persistent connection to the server for <em>each tab</em>
<ul dir="auto">
<li><a href="https://developer.mozilla.org/en-US/docs/Web/API/SharedWorker" rel="nofollow">SharedWorker</a> also evaluated, but support is lacking</li>
</ul>
</li>
<li>AWS "Classic" ELB <a href="https://aws.amazon.com/elasticloadbalancing/features/" rel="nofollow">does not support Websockets</a>. Requires use of NLB (TCP) or ALB in AWS environments.</li>
</ul>
<p dir="auto"><a target="_blank" rel="noopener noreferrer nofollow" href="https://user-images.githubusercontent.com/296227/75080027-9f84f900-54bf-11ea-8494-ebd531e33450.png"><img src="https://user-images.githubusercontent.com/296227/75080027-9f84f900-54bf-11ea-8494-ebd531e33450.png" alt="Superset async query arch - Websockets v2 (2)" style="max-width: 100%;"></a></p>
<h3 dir="auto">Approach</h3>
<p dir="auto">Each open tab of Superset would create a unique "channel" ID to subscribe to. A websocket connection is established with the standalone websocket server as an HTTP request that is then upgraded to <code class="notranslate">wss://</code> if authentication with the main Flask app is successful. Requests for charts or queries in SQL Lab are sent via HTTP to the Flask web app, including the tab's channel ID. The server enqueues a celery job and returns a job ID to the caller. When the job completes, a notification is sent over the WSS connection, which triggers another HTTP request to fetch the cached data and display the results.</p>
<p dir="auto"><strong>Why a separate application?</strong></p>
<p dir="auto">The current Flask application is not well suited for persistent websocket connections. We have evaluated several Python and Flask-based solutions, including <a href="https://flask-socketio.readthedocs.io/en/latest/" rel="nofollow">flask-SocketIO</a> and <a href="https://pgjones.gitlab.io/quart/" rel="nofollow">others</a>, and have found the architectural changes to the Flask web app to be overly invasive. For that reason, we propose that the websocket service be a standalone application, decoupled from the main Flask application, with minimal integration points. Superset's extensive use of Javascript and the mature Node.js <a href="https://www.npmjs.com/package/ws" rel="nofollow">websocket libraries</a> make Node.js and TypeScript (<a href="https://github.com/apache/incubator-superset/issues/9101" data-hovercard-type="issue" data-hovercard-url="/apache/superset/issues/9101/hovercard">SIP-36</a>) an obvious choice for implementing the sidecar application.</p>
<p dir="auto"><strong>Reconnection</strong></p>
<p dir="auto">The nature of persistent connections is that they will, at some point, disconnect. The system should be able to reconnect and "catch up" on any missed events. We evaluated several PubSub solutions (mainly in Redis) that could enable this durable reconnection story, and have determined that <a href="https://redis.io/topics/streams-intro" rel="nofollow">Redis Streams</a> (Redis ≥ 5.0) fits this use case well. By storing a last received event ID, the client can pass that ID when reconnecting to fetch all messages in the channel from that point forward. For security reasons, we should periodically force the client to reconnect to revalidate authentication status.</p>
<p dir="auto"><strong>Why not send result data over the socket connection?</strong></p>
<p dir="auto">While it is possible to send result data over the websocket connection, keeping the scope of the standalone service to event notifications will reduce the security footprint of the sidecar application. Fetching (potentially sensitive) data will still require necessary authentication and authorization checks at load time by routing through the Flask web app. Sending large datasets over the websocket protocol introduces potential unknown performance and consistency issues of its own. Websockets are not streams, and "<a href="https://codeburst.io/polling-vs-sse-vs-websocket-how-to-choose-the-right-one-1859e4e13bd9" rel="nofollow">the client will only be notified about a new message once all of the frames have been received and the original message payload has been reconstructed.</a>"</p>
<p dir="auto"><strong>Query Cancellation</strong></p>
<p dir="auto">Queries may be "cancelled" by calling the <code class="notranslate">/superset/stop_query</code> endpoint in SQL Lab, which simply sets <code class="notranslate">query.status = QueryStatus.STOPPED</code> for the running query. This cancellation logic is currently implemented only for queries running against <a href="https://github.com/apache/incubator-superset/blob/607cfd1f29736590fbba397c4f8a04526be66aff/superset/db_engine_specs/hive.py#L264">hive</a> and <a href="https://github.com/apache/incubator-superset/blob/607cfd1f29736590fbba397c4f8a04526be66aff/superset/db_engine_specs/presto.py#L730">presto</a> databases. Queries that have been enqueued could be cancelled prior to executing the query by adding a check in the celery worker logic. It is also possible to <a href="https://docs.celeryproject.org/en/stable/userguide/workers.html#revoke-revoking-tasks" rel="nofollow">revoke a Celery task</a>, which will skip execution of the task, but it won’t terminate an already executing task. Due to the limited query cancellation support in DB-API drivers, some of which is <a href="https://github.com/apache/incubator-superset/issues/747" data-hovercard-type="issue" data-hovercard-url="/apache/superset/issues/747/hovercard">discussed here</a>, comprehensive query cancellation functionality should be explored in a separate SIP. That said, query cancellation requests may still be issued to the existing (or similar) endpoint when users intentionally navigate away from a dashboard or Explore view with charts in a loading state.</p>
<p dir="auto"><strong>Query deduplication</strong></p>
<p dir="auto">The <code class="notranslate">query</code> table in the Superset metadata database currently includes only queries run via SQL Lab. Adapting this for use with dashboards and charts may have a larger impact than we're willing to accept at this time. Instead, a separate key-value store (e.g. Redis) may be used for tracking and preventing duplicate queries. Using a fast KV store also allows us to check for duplicate queries more efficiently in the web request cycle.</p>
<p dir="auto">Each query issued to the backend can be fingerprinted using a hashing algorithm (SHA-256 or similar) to generate a unique key based on the following:</p>
<ul dir="auto">
<li>Normalized SQL query text</li>
<li>Database ID</li>
<li>Database schema</li>
</ul>
<p dir="auto">Prior to executing a query, a hash (key) is generated and checked against a key-value store. If the key does not exist, it is stored with a configured TTL, containing an object value with the following properties:</p>
<ul dir="auto">
<li>Query state (running, success, error)</li>
<li>Query type (chart, sql_lab)</li>
<li>Result key (cache key)</li>
</ul>
<p dir="auto">Another key is created to track the Channels and Job IDs that should be notified when this query completes (e.g. <code class="notranslate">&lt;hash&gt;_jobs</code>→ <code class="notranslate">List[ChannelId:JobId]</code>). If a duplicate query is issued while currently running, the Job ID is pushed onto the list, and all relevant channels are notified via websocket when the query completes. If a query is issued and a cache key exists with <code class="notranslate">state == "success"</code>, a notification is triggered immediately via websocket to the client. If queries are "force" refreshed, query deduplication is performed only for currently running queries.</p>
<h3 dir="auto">New or Changed Public Interfaces</h3>
<ul dir="auto">
<li>New configuration options</li>
<li>Additional API endpoints (see Migration Plan, below)</li>
</ul>
<h3 dir="auto">New dependencies (optional)</h3>
<ul dir="auto">
<li>Redis ≥ 5.0 (pubsub, Redis Streams) or alternative</li>
<li>Node.js runtime for websocket application</li>
</ul>
<h3 dir="auto">Migration Plan and Compatibility</h3>
<p dir="auto">Asynchronous query operations are currently an optional enhancement to Superset, and should remain that way. Configuring and running Celery workers should not be required for basic Superset operation. As such, this proposed websocket approach to async query operations should be an optional enhancement to Superset, available via a configuration flag.</p>
<p dir="auto">For users who opt to run Superset in full async mode, the following requirements will apply under the current proposal:</p>
<ul dir="auto">
<li>Chart cache backend</li>
<li>SQL Lab results backend</li>
<li>Pubsub backend (current recommendation: Redis Streams)</li>
<li>Running the sidecar Node.js websocket application</li>
</ul>
<p dir="auto"><a href="https://caniuse.com/#feat=websockets" rel="nofollow">Browsers that do not support websockets</a> (very few) should fallback to synchronous operation or short polling.</p>
<p dir="auto"><strong>Migration plan:</strong></p>
<ul dir="auto">
<li>Introduce new configuration options to enable asynchronous operation in Superset. Pass these values to the frontend as feature flags (as appropriate)</li>
<li>Introduce new API endpoints in Superset's Flask web application for async query/data loading for charts and queries</li>
<li>Introduce new API endpoints in Superset's Flask web application required for interaction with the sidecar application</li>
<li>Build and deploy the Node.js websocket server application</li>
<li>Build and deploy async frontend code to consume websocket events, under feature flag</li>
</ul>
<h3 dir="auto">Rejected Alternatives</h3>
<h3 dir="auto">SSE (EventSource) over HTTP/2</h3>
<p dir="auto"><a href="https://developer.mozilla.org/en-US/docs/Web/API/Server-sent_events/Using_server-sent_events" rel="nofollow">Server-Sent Events</a> (SSE) streams data over a multiplexed HTTP/2 connection. SSE is a native HTML5 feature that allows the server to keep the HTTP connection open and push data to the client. Thanks to the multiplexing feature of the HTTP/2 protocol, the number of concurrent requests per domain is not limited to 6-8, but it is virtually unlimited.</p>
<p dir="auto"><strong>Pros</strong></p>
<ul dir="auto">
<li>Single, multiplexed connection to the server (via HTTP/2)
<ul dir="auto">
<li>Connections can be limited via <code class="notranslate">SETTINGS_MAX_CONCURRENT_STREAMS</code>, but this <a href="https://http2.github.io/http2-spec/" rel="nofollow">should be no smaller than 100</a></li>
</ul>
</li>
<li>Events pushed to the client from the server in realtime</li>
<li>Supports streaming data and traditional HTTP authentication methods</li>
<li>Minor adjustments to proxy, load balancers</li>
<li>Can use async <a href="https://medium.com/code-zen/python-generator-and-html-server-sent-events-3cdf14140e56" rel="nofollow">generators</a> with Flask &amp; gevent/eventlet (untested)</li>
</ul>
<p dir="auto"><strong>Cons</strong></p>
<ul dir="auto">
<li>HTTP/2 not supported in <a href="https://caniuse.com/#feat=http2" rel="nofollow">some browsers</a> (IE)</li>
<li>SSE requires <a href="https://github.com/Yaffle/EventSource">polyfill</a> for <a href="https://caniuse.com/#feat=eventsource" rel="nofollow">some browsers</a></li>
<li>Requires persistent connection to the server for <em>each tab</em></li>
<li>HTTP/2 requires TLS (not really a con, per se)</li>
<li>If SSE is used without HTTP/2, persistent SSE connections may saturate browser connection limit with multiple tabs</li>
<li>AWS "Classic" ELB <a href="https://aws.amazon.com/elasticloadbalancing/features/" rel="nofollow">does not support HTTP/2</a>. ALB supports HTTP/2, but apparently converts it to HTTP/1.1 upstream (AWS-specific)</li>
<li>Does not support upstream messaging (from the client to the server)</li>
</ul>
<p dir="auto"><em>NOTE: HTTP/2 multiplexing could still potentially be valuable alongside the websocket features, and should be investigated further.</em></p>
<h3 dir="auto">Long polling (aka Comet)</h3>
<p dir="auto"><strong>Pros</strong></p>
<ul dir="auto">
<li>Excellent browser support (a standard HTTP request)</li>
</ul>
<p dir="auto"><strong>Cons</strong></p>
<ul dir="auto">
<li>Requires long-lived connections</li>
<li>Browser limit on concurrent connections to the same domain (6-8) results in connection blocking (HTTP/1.1)</li>
</ul>
<h3 dir="auto">Short polling</h3>
<p dir="auto"><strong>Pros</strong></p>
<ul dir="auto">
<li>Excellent browser support (a standard HTTP request)</li>
<li>It's the current solution for async queries in SQL Lab</li>
</ul>
<p dir="auto"><strong>Cons</strong></p>
<ul dir="auto">
<li>Browser limit on concurrent connections to the same domain (6-8) results in connection blocking (HTTP/1.1)</li>
<li>Header overhead: every poll request and response contains a full set of HTTP headers</li>
<li>Auth overhead: each polling request must be authenticated and authorized on the server</li>
</ul>
<hr>
<p dir="auto">Thanks to <a class="user-mention notranslate" data-hovercard-type="user" data-hovercard-url="/users/etr2460/hovercard" data-octo-click="hovercard-link-click" data-octo-dimensions="link_type:self" href="https://github.com/etr2460">@etr2460</a> <a class="user-mention notranslate" data-hovercard-type="user" data-hovercard-url="/users/suddjian/hovercard" data-octo-click="hovercard-link-click" data-octo-dimensions="link_type:self" href="https://github.com/suddjian">@suddjian</a> <a class="user-mention notranslate" data-hovercard-type="user" data-hovercard-url="/users/nytai/hovercard" data-octo-click="hovercard-link-click" data-octo-dimensions="link_type:self" href="https://github.com/nytai">@nytai</a> <a class="user-mention notranslate" data-hovercard-type="user" data-hovercard-url="/users/willbarrett/hovercard" data-octo-click="hovercard-link-click" data-octo-dimensions="link_type:self" href="https://github.com/willbarrett">@willbarrett</a> <a class="user-mention notranslate" data-hovercard-type="user" data-hovercard-url="/users/craig-rueda/hovercard" data-octo-click="hovercard-link-click" data-octo-dimensions="link_type:self" href="https://github.com/craig-rueda">@craig-rueda</a> for feedback and review.</p>
<hr>
<p dir="auto"><strong>Update 2020-03-10</strong></p>
<p dir="auto">Per the below discussion, an abstracted interface will be used on the client in order to support transport mechanisms other than Websockets and the proposed sidecar application. The final form of this abstraction will take shape during implementation, but the goal will be to have UI elements interact with the generic interface, while configuration will determine which transport is used underneath.</p>
<p dir="auto"><strong>Is your feature request related to a problem? Please describe.</strong></p>
<p dir="auto">It is easy and user-friendly to add a user via <code class="notranslate">List Users</code> page. I suppose the command <code class="notranslate">superset fab createuser</code> will achieve the same goal with a prompt. But I'm frustrated when I try to create a batch of users. For example, I want to add four different users as public role with default password. Repeatedly filling forms is not a good idea.</p>
<p dir="auto">I guess the user management is related to <code class="notranslate">.superset\superset.db</code>. Connect to the database and insert values should do the job.</p>
<p dir="auto"><strong>Describe the solution you'd like</strong></p>
<ol dir="auto">
<li>
<p dir="auto">An admin user could download a sample csv/xlsx(with header). The admin will fill the table or write some code snippets to generate the correct csv/xlsx(with header).</p>
</li>
<li>
<p dir="auto">The admin upload the csv through a superset page. After the process of the csv, the page is refreshed and lists all the users(or part of it with <code class="notranslate">N users in total are created</code> message printed).</p>
</li>
</ol>
<p dir="auto">e.g.</p>
<table role="table">
<thead>
<tr>
<th align="left">First Name</th>
<th align="center">Last Name</th>
<th align="right">User Name</th>
<th align="left">Active</th>
<th align="center">Email</th>
<th align="right">Role</th>
<th align="right">Password</th>
</tr>
</thead>
<tbody>
<tr>
<td align="left">Frank</td>
<td align="center">Scarlet</td>
<td align="right">Scarlet</td>
<td align="left">1</td>
<td align="center"><a href="mailto:hferfrank@gmail.com">hferfrank@gmail.com</a></td>
<td align="right">Public</td>
<td align="right">P@ssword</td>
</tr>
</tbody>
</table>
<p dir="auto"><strong>Describe alternatives you've considered</strong></p>
<p dir="auto"><code class="notranslate">A clear and concise description of any alternative solutions or features you've considered.</code></p>
<p dir="auto"><strong>Additional context</strong></p>
<p dir="auto"><code class="notranslate">Add any other context or screenshots about the feature request here.</code></p>