mistercrunch closed pull request #3996: [sql lab] deeper support for templating URL: https://github.com/apache/incubator-superset/pull/3996
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/docs/sqllab.rst b/docs/sqllab.rst index a1da6c7f45..6b87543906 100644 --- a/docs/sqllab.rst +++ b/docs/sqllab.rst @@ -48,17 +48,25 @@ Available macros We expose certain modules from Python's standard library in Superset's Jinja context: + - ``time``: ``time`` - ``datetime``: ``datetime.datetime`` - ``uuid``: ``uuid`` - ``random``: ``random`` - ``relativedelta``: ``dateutil.relativedelta.relativedelta`` -- more to come! `Jinja's builtin filters <http://jinja.pocoo.org/docs/dev/templates/>`_ can be also be applied where needed. - .. autoclass:: superset.jinja_context.PrestoTemplateProcessor :members: .. autofunction:: superset.jinja_context.url_param + +Extending macros +'''''''''''''''' + +As mentioned in the `Installation & Configuration`_ documentation, +it's possible for administrators to expose more more macros in their +environment using the configuration variable ``JINJA_CONTEXT_ADDONS``. +All objects referenced in this dictionary will become available for users +to integrate in their queries in **SQL Lab**. diff --git a/superset/assets/javascripts/SqlLab/actions.js b/superset/assets/javascripts/SqlLab/actions.js index 2541ee5856..d1fbfea46f 100644 --- a/superset/assets/javascripts/SqlLab/actions.js +++ b/superset/assets/javascripts/SqlLab/actions.js @@ -20,6 +20,7 @@ export const QUERY_EDITOR_SET_SCHEMA = 'QUERY_EDITOR_SET_SCHEMA'; export const QUERY_EDITOR_SET_TITLE = 'QUERY_EDITOR_SET_TITLE'; export const QUERY_EDITOR_SET_AUTORUN = 'QUERY_EDITOR_SET_AUTORUN'; export const QUERY_EDITOR_SET_SQL = 'QUERY_EDITOR_SET_SQL'; +export const QUERY_EDITOR_SET_TEMPLATE_PARAMS = 'QUERY_EDITOR_SET_TEMPLATE_PARAMS'; export const QUERY_EDITOR_SET_SELECTED_TEXT = 'QUERY_EDITOR_SET_SELECTED_TEXT'; export const QUERY_EDITOR_PERSIST_HEIGHT = 'QUERY_EDITOR_PERSIST_HEIGHT'; @@ -132,6 +133,7 @@ export function runQuery(query) { tab: query.tab, tmp_table_name: query.tempTableName, select_as_cta: query.ctas, + templateParams: query.templateParams, }; const sqlJsonUrl = '/superset/sql_json/' + location.search; $.ajax({ @@ -248,6 +250,10 @@ export function queryEditorSetSql(queryEditor, sql) { return { type: QUERY_EDITOR_SET_SQL, queryEditor, sql }; } +export function queryEditorSetTemplateParams(queryEditor, templateParams) { + return { type: QUERY_EDITOR_SET_TEMPLATE_PARAMS, queryEditor, templateParams }; +} + export function queryEditorSetSelectedText(queryEditor, sql) { return { type: QUERY_EDITOR_SET_SELECTED_TEXT, queryEditor, sql }; } diff --git a/superset/assets/javascripts/SqlLab/components/SqlEditor.jsx b/superset/assets/javascripts/SqlLab/components/SqlEditor.jsx index 4dcaede726..4b2e8999a7 100644 --- a/superset/assets/javascripts/SqlLab/components/SqlEditor.jsx +++ b/superset/assets/javascripts/SqlLab/components/SqlEditor.jsx @@ -15,6 +15,7 @@ import { import SplitPane from 'react-split-pane'; import Button from '../../components/Button'; +import TemplateParamsEditor from './TemplateParamsEditor'; import SouthPane from './SouthPane'; import SaveQuery from './SaveQuery'; import Timer from '../../components/Timer'; @@ -24,6 +25,7 @@ import { STATE_BSSTYLE_MAP } from '../constants'; import RunQueryActionButton from './RunQueryActionButton'; import { t } from '../../locales'; + const propTypes = { actions: PropTypes.object.isRequired, height: PropTypes.string.isRequired, @@ -95,6 +97,7 @@ class SqlEditor extends React.PureComponent { tab: qe.title, schema: qe.schema, tempTableName: ctas ? this.state.ctas : '', + templateParams: qe.templateParams, runAsync, ctas, }; @@ -189,6 +192,13 @@ class SqlEditor extends React.PureComponent { </Form> </div> <div className="pull-right"> + <TemplateParamsEditor + language="json" + onChange={(params) => { + this.props.actions.queryEditorSetTemplateParams(qe, params); + }} + code={qe.templateParams} + /> {limitWarning} {this.props.latestQuery && <Timer diff --git a/superset/assets/javascripts/SqlLab/components/TemplateParamsEditor.jsx b/superset/assets/javascripts/SqlLab/components/TemplateParamsEditor.jsx new file mode 100644 index 0000000000..d22b9abfae --- /dev/null +++ b/superset/assets/javascripts/SqlLab/components/TemplateParamsEditor.jsx @@ -0,0 +1,129 @@ +import React from 'react'; +import PropTypes from 'prop-types'; +import { Badge } from 'react-bootstrap'; + +import AceEditor from 'react-ace'; +import 'brace/mode/sql'; +import 'brace/mode/json'; +import 'brace/mode/html'; +import 'brace/mode/markdown'; +import 'brace/theme/textmate'; + +import ModalTrigger from '../../components/ModalTrigger'; +import InfoTooltipWithTrigger from '../../components/InfoTooltipWithTrigger'; +import Button from '../../components/Button'; +import { t } from '../../locales'; + +const propTypes = { + onChange: PropTypes.func, + code: PropTypes.string, + language: PropTypes.oneOf(['yaml', 'json']), +}; + +const defaultProps = { + label: null, + description: null, + onChange: () => {}, + code: '{}', +}; + +export default class TemplateParamsEditor extends React.Component { + constructor(props) { + super(props); + const codeText = props.code || '{}'; + this.state = { + codeText, + parsedJSON: null, + isValid: true, + }; + this.onChange = this.onChange.bind(this); + } + componentDidMount() { + this.onChange(this.state.codeText); + } + onChange(value) { + const codeText = value; + let isValid; + let parsedJSON = {}; + try { + parsedJSON = JSON.parse(value); + isValid = true; + } catch (e) { + isValid = false; + } + this.setState({ parsedJSON, isValid, codeText }); + if (isValid) { + this.props.onChange(codeText); + } else { + this.props.onChange('{}'); + } + } + renderDoc() { + return ( + <p> + Assign a set of parameters as <code>JSON</code> below + (example: <code>{'{"my_table": "foo"}'}</code>), + and they become available + in your SQL (example: <code>SELECT * FROM {'{{ my_table }}'} </code>) + by using + <a + href="http://superset.apache.org/sqllab.html#templating-with-jinja"; + target="_blank" + rel="noopener noreferrer" + > + Jinja templating + </a> syntax. + </p> + ); + } + renderModalBody() { + return ( + <div> + {this.renderDoc()} + <AceEditor + mode={this.props.language} + theme="textmate" + style={{ border: '1px solid #CCC' }} + minLines={25} + maxLines={50} + onChange={this.onChange} + width="100%" + editorProps={{ $blockScrolling: true }} + enableLiveAutocompletion + value={this.state.codeText} + /> + </div> + ); + } + render() { + const paramCount = this.state.parsedJSON ? Object.keys(this.state.parsedJSON).length : 0; + return ( + <ModalTrigger + modalTitle={t('Template Parameters')} + triggerNode={ + <Button + className="m-r-5" + tooltip={t('Edit template parameters')} + > + {`${t('parameters')} `} + {paramCount > 0 && + <Badge>{paramCount}</Badge> + } + {!this.state.isValid && + <InfoTooltipWithTrigger + icon="exclamation-triangle" + bsStyle="danger" + tooltip={t('Invalid JSON')} + label="invalid-json" + /> + } + </Button> + } + modalBody={this.renderModalBody(true)} + /> + ); + } +} + +TemplateParamsEditor.propTypes = propTypes; +TemplateParamsEditor.defaultProps = defaultProps; diff --git a/superset/assets/javascripts/SqlLab/reducers.js b/superset/assets/javascripts/SqlLab/reducers.js index 3a49bd1b88..f01f2c3bb7 100644 --- a/superset/assets/javascripts/SqlLab/reducers.js +++ b/superset/assets/javascripts/SqlLab/reducers.js @@ -211,6 +211,9 @@ export const sqlLabReducer = function (state, action) { [actions.QUERY_EDITOR_SET_SQL]() { return alterInArr(state, 'queryEditors', action.queryEditor, { sql: action.sql }); }, + [actions.QUERY_EDITOR_SET_TEMPLATE_PARAMS]() { + return alterInArr(state, 'queryEditors', action.queryEditor, { templateParams: action.templateParams }); + }, [actions.QUERY_EDITOR_SET_SELECTED_TEXT]() { return alterInArr(state, 'queryEditors', action.queryEditor, { selectedText: action.sql }); }, diff --git a/superset/assets/javascripts/components/InfoTooltipWithTrigger.jsx b/superset/assets/javascripts/components/InfoTooltipWithTrigger.jsx index d86d0515e7..caacb914a5 100644 --- a/superset/assets/javascripts/components/InfoTooltipWithTrigger.jsx +++ b/superset/assets/javascripts/components/InfoTooltipWithTrigger.jsx @@ -21,7 +21,7 @@ const tooltipStyle = { wordWrap: 'break-word' }; export default function InfoTooltipWithTrigger({ label, tooltip, icon, className, onClick, placement, bsStyle }) { - const iconClass = `fa fa-${icon} ${className} ${bsStyle ? 'text-' + bsStyle : ''}`; + const iconClass = `fa fa-${icon} ${className} ${bsStyle ? `text-${bsStyle}` : ''}`; const iconEl = ( <i className={iconClass} diff --git a/superset/sql_lab.py b/superset/sql_lab.py index 399faee4de..d7b0878011 100644 --- a/superset/sql_lab.py +++ b/superset/sql_lab.py @@ -87,11 +87,13 @@ def get_session(nullpool): @celery_app.task(bind=True, soft_time_limit=SQLLAB_TIMEOUT) def get_sql_results( - ctask, query_id, return_results=True, store_results=False, user_name=None): + ctask, query_id, return_results=True, store_results=False, + user_name=None, template_params=None): """Executes the sql query returns the results.""" try: return execute_sql( - ctask, query_id, return_results, store_results, user_name) + ctask, query_id, return_results, store_results, user_name, + template_params) except Exception as e: logging.exception(e) stats_logger.incr('error_sqllab_unhandled') @@ -106,6 +108,7 @@ def get_sql_results( def execute_sql( ctask, query_id, return_results=True, store_results=False, user_name=None, + template_params=None, ): """Executes the sql query returns the results.""" session = get_session(not ctask.request.called_directly) @@ -161,7 +164,9 @@ def handle_error(msg): try: template_processor = get_template_processor( database=database, query=query) - executed_sql = template_processor.process_template(executed_sql) + tp = template_params or {} + executed_sql = template_processor.process_template( + executed_sql, **tp) except Exception as e: logging.exception(e) msg = 'Template rendering failed: ' + utils.error_msg_from_exception(e) diff --git a/superset/views/core.py b/superset/views/core.py index ef0cbf5844..529a9f4875 100755 --- a/superset/views/core.py +++ b/superset/views/core.py @@ -2161,6 +2161,8 @@ def sql_json(self): sql = request.form.get('sql') database_id = request.form.get('database_id') schema = request.form.get('schema') or None + template_params = json.loads( + request.form.get('templateParams') or '{}') session = db.session() mydb = session.query(models.Database).filter_by(id=database_id).first() @@ -2212,7 +2214,9 @@ def sql_json(self): try: sql_lab.get_sql_results.delay( query_id=query_id, return_results=False, - store_results=not query.select_as_cta, user_name=g.user.username) + store_results=not query.select_as_cta, + user_name=g.user.username, + template_params=template_params) except Exception as e: logging.exception(e) msg = ( @@ -2241,7 +2245,8 @@ def sql_json(self): error_message=timeout_msg): # pylint: disable=no-value-for-parameter data = sql_lab.get_sql_results( - query_id=query_id, return_results=True) + query_id=query_id, return_results=True, + template_params=template_params) except Exception as e: logging.exception(e) return json_error_response('{}'.format(e)) ---------------------------------------------------------------- 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: [email protected] With regards, Apache Git Services
