Realtime syntax validation of queries in SQL lab

[amalakar]

Real time query validation in sql lab I think could be a very useful and powerful feature, it can bring in equivalent productivity modern IDEs bring to programming. I also noticed that query engine's like presto does have a very easy way to validate queries, prepending explain (TYPE VALIDATE) to a query validates the syntax of the query. Other query engine's may support similar functionalities as well. Here is some test of query validation for presto engine

Valid query

presto:default> explain (TYPE VALIDATE) select * from hive.default.test_table where ds='2019-01-01' limit 10;
 Valid
-------
 true
(1 row)

Invalid table name:

Query 20190116_163346_02068_b85df failed: line 1:39: Table hive.default.test_table_x does not exist
explain (TYPE VALIDATE) select * from hive.default.test_table_x where ds='2019-01-01' and audit_source='presto' limit 10 

Invalid column

presto:default> explain (TYPE VALIDATE) select * from hive.default.test_table_x where ds='2019-01-01' and col1='abc' limit 10;
Query 20190116_163535_02086_b85df failed: line 1:99: Column 'col1' cannot be resolved
explain (TYPE VALIDATE) select * from hive.default.test_table_x where ds='2019-01-01' and col1='abc' limit 10

More: https://prestodb.io/docs/current/sql/explain.html

Examples of such feature in other sql editors:

[mistercrunch]

EXPLAIN is very standard but somewhat costly (guessing TYPE VALIDATE is probably cheaper), triggering it onChange might be rough on the coordinator. We could wait 2-5 seconds after the last change to actually trigger the validation, depending on how much the query planner can take.

I know BigQuery's UI has that feature and it was requested internally at Lyft before.

From a Superset standpoint it should be fairly easy, we'd add a new validate_sql(sql) method in superset.db_engine_specs that would return something like boolean and an array of know errors, maybe line number + message. Then we need a new REST endpoint to serve this and maybe a database configuration setting sql_validation_interval_ms or something that is off if == 0, otherwise defines the interval at which we validate.

[stale]

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.

[prashantkommireddi]

very interested in this feature. does this lie on Superset's immediate roadmap?