Ability to launch SQL Lab with database, schema and SQL query pre-populated using URL params or JSON payload

[apankha]

Is your feature request related to a problem? Please describe.

We are using a Data Cataloging tool which allows our users to view available datasets in our data lake. We would like to integrate this tool with Superset. The functionality would allow the user to launch Superset inside SQL Lab view and query the table that they were viewing within the Data Cataloging tool.

Describe the solution you'd like

We would like to be able to pass parameters to
https://ourhost:ourport/superset/sqllab

eg:

https://ourhost:ourport/superset/sqllab?sqlstring=blah

Describe alternatives you've considered

We have had a look at https://github.com/apache/incubator-superset/blob/0.36.0/superset/views/sql_lab.py to see if we can define a separate to overwrite the query editor from the params. We've also looked at the static JS generated for the UI but couldn't see a clear way to do this.

[issue-label-bot[bot]]

Issue-Label Bot is automatically applying the label #enhancement to this issue, with a confidence of 0.95. Please mark this comment with 👍 or 👎 to give our bot feedback!

Links: app homepage, dashboard and code for this bot.

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions. For admin, please label this issue .pinned to prevent stale bot from closing the issue.

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions. For admin, please label this issue .pinned to prevent stale bot from closing the issue.

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions. For admin, please label this issue .pinned to prevent stale bot from closing the issue.

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions. For admin, please label this issue .pinned to prevent stale bot from closing the issue.

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions. For admin, please label this issue .pinned to prevent stale bot from closing the issue.

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions. For admin, please label this issue .pinned to prevent stale bot from closing the issue.

[tooptoop4]

cc: @yousoph @junlincc @Steejay

[zhaoyongjie]

Hi @apankha,

Now Superset does not seem to generate SQLlab query through URL parameters. But you can use work around to do that.

SavedQueryRestApi.post                  POST       /api/v1/saved_query/

1. Create a saved query from the above API, then saved post response id to the Data Catalog app
2. Query this Saved query from http://<hostname>:<port>/superset/sqllab?savedQueryId=<saved id>

Yongjie

[Danikokonn]

Try this:
http://<hostname>:<port>/superset/sqllab?dbname=<DBNAME>&schema=<schema>&sql=<query>&title=<title>
Options are also available: autorun={false|true}, queryLimit=<integer>

[marcusze]

This looks great - however I get the error: Unable to add a new tab to the backend. Please contact your administrator.
Any thoughts what we could explore in the config to enable url links to new queries?

[marcusze]

I can see the backend endpoint /tabstateview is throwing 500 and the following error message:

    "errors": [
        {
            "message": "(psycopg2.ProgrammingError) can't adapt type 'LazyString'\n[SQL: INSERT INTO tab_state (created_on, changed_on, extra_json, user_id, label, active, database_id, schema, sql, query_limit, latest_query_id, autorun, template_params, hide_left_bar, saved_query_id, created_by_fk, changed_by_fk) VALUES (%(created_on)s, %(changed_on)s, %(extra_json)s, %(user_id)s, %(label)s, %(active)s, %(database_id)s, %(schema)s, %(sql)s, %(query_limit)s, %(latest_query_id)s, %(autorun)s, %(template_params)s, %(hide_left_bar)s, %(saved_query_id)s, %(created_by_fk)s, %(changed_by_fk)s) RETURNING tab_state.id]\n[parameters: {'created_on': datetime.datetime(2023, 12, 12, 8, 38, 3, 860901), 'changed_on': datetime.datetime(2023, 12, 12, 8, 38, 3, 860910), 'extra_json': '{}', 'user_id': 3, 'label': l'Untitled Query', 'active': True, 'database_id': 1, 'schema': 'public', 'sql': 'SELECT', 'query_limit': None, 'latest_query_id': None, 'autorun': False, 'template_params': None, 'hide_left_bar': False, 'saved_query_id': None, 'created_by_fk': 3, 'changed_by_fk': 3}]\n(Background on this error at: https://sqlalche.me/e/14/f405)",
            "error_type": "GENERIC_BACKEND_ERROR",
            "level": "error",
            "extra": {
                "issue_codes": [
                    {
                        "code": 1011,
                        "message": "Issue 1011 - Superset encountered an unexpected error."
                    }
                ]
            }
        }
    ]
}```

[marcusze]

Problem solved.
There is no query paramtitle, instead it should be name.
http://<hostname>:<port>/superset/sqllab?dbname=<DBNAME>&schema=<schema>&sql=<query>&name=<name>

[Danikokonn]

Yes, as far as i remember, starting from version 2.1.0, title param was replaced by name.
What i wrote is correct for version 2.0.1

[yujinchoi-94]

Hi, is there any documentation on query parameters?
I'm trying to use autorun, but it doesn't seem to be working. (My version is 3.1.0)