Error previewing data when schema is backquoted and dialect not supported in sqlglot

[wjsi]

We are developing a plugin to enable superset users to use MaxCompute of Alibaba Cloud, and a parsing issue blocks users from obtaining preview data from tables.

MaxCompute uses a three-layered table location semantic, i.e., <project>/<schema>/<table> to locate a table, and the default name of the schema is default. When previewing the data in SQL Lab in superset, it generates a SQL statement (at client side?) like this:

SELECT * 
FROM `default`.airbnb_listings 
WHERE city = 'newyork'
LIMIT 100

and the name default is backquoted (maybe to avoid conflicts with keywords in SQL?). When this statement is submitted into superset, it raises an error:

superset.exceptions.SupersetErrorsException: [SupersetError(message='This database does not allow for DDL/DML, and the query could not be parsed to confirm it is a read-only query. Please contact your administrator for more assistance.', error_type=<SupersetErrorType.DML_NOT_ALLOWED_ERROR: 'DML_NOT_ALLOWED_ERROR'>, level=<ErrorLevel.ERROR: 'error'>, extra={'issue_codes': [{'code': 1022, 'message': 'Issue 1022 - Database does not allow data manipulation.'}]})]

The cause is that connections in superset disallows DMLs by default. When trying to judge if the statement is a DML, superset calls sqlglot to parse the statement, and sqlglot failed to parse the statement with backquotes in its default dialect (as sqlglot does not support MaxCompute SQL by now):

Traceback (most recent call last):
  File "/Users/mac_user/miniforge3/envs/superset/lib/python3.11/site-packages/superset/sql_lab.py", line 242, in execute_sql_statement
    parsed_statement = SQLStatement(sql_statement, engine=db_engine_spec.engine)
                       ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/mac_user/miniforge3/envs/superset/lib/python3.11/site-packages/superset/sql/parse.py", line 247, in __init__
    super().__init__(statement, engine, ast)
  File "/Users/mac_user/miniforge3/envs/superset/lib/python3.11/site-packages/superset/sql/parse.py", line 158, in __init__
    self._parsed = ast or self._parse_statement(statement, engine)
                          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/mac_user/miniforge3/envs/superset/lib/python3.11/site-packages/superset/sql/parse.py", line 330, in _parse_statement
    statements = cls.split_script(statement, engine)
                 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/mac_user/miniforge3/envs/superset/lib/python3.11/site-packages/superset/sql/parse.py", line 316, in split_script
    ast = cls._parse(remainder, engine)[0]
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/mac_user/miniforge3/envs/superset/lib/python3.11/site-packages/superset/sql/parse.py", line 259, in _parse
    raise SupersetParseError(
superset.exceptions.SupersetParseError: Error parsing near '`' at line 2:14

As BaseEngineSpec has an is_select_query method, is it possible to call this method as a fallback? That is, turn

superset/superset/sql_lab.py

Lines 244 to 250 in c7c3b1b

	try:
	parsed_statement = SQLStatement(sql_statement, engine=db_engine_spec.engine)
	disallowed = parsed_statement.is_mutating()
	except SupersetParseError as ex:
	# if we fail to parse the query, disallow by default
	disallowed = True
	errors.append(ex.error)

into something like

try:
    parsed_statement = SQLStatement(sql_statement, engine=db_engine_spec.engine)
    disallowed = parsed_statement.is_mutating()
except SupersetParseError as ex:
    try:
        # if we fail to parse the query, try fallback to db_engine_spec.is_select_query
        disallowed = not db_engine_spec.is_select_query(parsed_query)
    except:
        # if both methods fails, disallow by default
        disallowed = True
        errors.append(ex.error)

and then we do not need to implement a complete dialect for sqlglot by now.