sqlalchemy-kusto - create dataset - could not load columns

[MatMor]

Bug description

I have installed a new superset (version 4.1.1) with sqlalchemy-kusto==2.0.1. I use kusto sql. I can connect to the Azure Data Explorer. When I want to create a dataset, I got an error:

An Error Occurred Unable to load columns for the selected table. Please select a different table.

I tried this with different tables. Allways the same.
All Databases and all tables of the database were correct listed in the screen. So the connection seems to work.

To Reproduce
Steps to reproduce the behavior:

In settings create a database connection.

• Use "kustosql+https://xxx.westeurope.kusto.windows.net/..."
• In Datasets create a new Dataset
• Select database and table.
• An error is displayed: "An Error Occurred Unable to load columns for the selected table. Please select a different table."

Normaly the columns of the table should be displayed.

We made following adaptions to the values.yaml:

bootstrapScript:
...
  pip install psycopg2-binary==2.9.6
  pip install sqlalchemy-kusto==2.0.1

In the logs are 2 error messages:

2024-12-30 10:02:41,496:WARNING:superset.views.error_handling:SupersetErrorException
Traceback (most recent call last):
  File "/app/superset/sql/parse.py", line 256, in _parse
    return sqlglot.parse(script, dialect=dialect)
  File "/usr/local/lib/python3.10/site-packages/sqlglot/__init__.py", line 102, in parse
    return Dialect.get_or_raise(read or dialect).parse(sql, **opts)
  File "/usr/local/lib/python3.10/site-packages/sqlglot/dialects/dialect.py", line 919, in parse
    return self.parser(**opts).parse(self.tokenize(sql), sql)
  File "/usr/local/lib/python3.10/site-packages/sqlglot/parser.py", line 1395, in parse
    return self._parse(
  File "/usr/local/lib/python3.10/site-packages/sqlglot/parser.py", line 1467, in _parse
    self.raise_error("Invalid expression / Unexpected token")
  File "/usr/local/lib/python3.10/site-packages/sqlglot/parser.py", line 1508, in raise_error
    raise error
sqlglot.errors.ParseError: Invalid expression / Unexpected token. Line 1, Col: 14.
  SELECT TOP 100 *
FROM "SmartSignage_Dev"."AssetDimensions"

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/local/lib/python3.10/site-packages/flask/app.py", line 1484, in full_dispatch_request
    rv = self.dispatch_request()
  File "/usr/local/lib/python3.10/site-packages/flask/app.py", line 1469, in dispatch_request
    return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)
  File "/usr/local/lib/python3.10/site-packages/flask_appbuilder/security/decorators.py", line 95, in wraps
    return f(self, *args, **kwargs)
  File "/app/superset/views/base_api.py", line 119, in wraps
    duration, response = time_function(f, self, *args, **kwargs)
  File "/app/superset/utils/core.py", line 1364, in time_function
    response = func(*args, **kwargs)
  File "/app/superset/utils/log.py", line 303, in wrapper
    value = f(*args, **kwargs)
  File "/app/superset/databases/api.py", line 1024, in table_metadata
    payload = database.db_engine_spec.get_table_metadata(database, table)
  File "/app/superset/db_engine_specs/base.py", line 1079, in get_table_metadata
    return get_table_metadata(database, table)
  File "/app/superset/databases/utils.py", line 100, in get_table_metadata
    "selectStar": database.select_star(
  File "/app/superset/models/core.py", line 719, in select_star
    return self.db_engine_spec.select_star(
  File "/app/superset/db_engine_specs/base.py", line 1689, in select_star
    sql = SQLScript(sql, engine=cls.engine).format()
  File "/app/superset/sql/parse.py", line 611, in __init__
    self.statements = statement_class.split_script(script, engine)
  File "/app/superset/sql/parse.py", line 316, in split_script
    ast = cls._parse(remainder, engine)[0]
  File "/app/superset/sql/parse.py", line 259, in _parse
    raise SupersetParseError(
superset.exceptions.SupersetParseError: Error parsing near '100' at line 1:14

How to solve this?

Screenshots/recordings

No response

Superset version

master / latest-dev

Python version

3.9

Node version

16

Browser

Chrome

Additional context

No response

Checklist

• I have searched Superset docs and Slack and didn't find a solution to my problem.
• I have searched the GitHub issue tracker and didn't find a similar bug report.
• I have checked Superset's logs for errors and if I found a relevant Python stacktrace, I included it here as text in the "additional context" section.

[dosubot]

Hey @MatMor! I'm here to help you with any bugs, questions, or contributions. Let's tackle this together!

The error you're encountering seems to be related to the SQL parsing process in Superset, specifically when using the sqlalchemy-kusto library. The SupersetParseError indicates that there's an issue with parsing the SQL query, particularly around the SELECT TOP 100 * syntax, which might not be supported by the SQL dialect being used.

Here are a few steps you can take to troubleshoot and potentially resolve the issue:

1. Check SQL Dialect Compatibility: Ensure that the SQL dialect used by sqlglot is compatible with Kusto SQL. The error suggests that the parser is having trouble with the SELECT TOP 100 * syntax. You might need to adjust the SQL query to match the expected syntax for Kusto SQL.

2. Custom SQL Parsing: If the default SQL parsing does not support Kusto SQL, you might need to implement a custom SQL parsing logic or adjust the dialect settings in Superset to better handle Kusto SQL syntax.

3. Review sqlalchemy-kusto Configuration: Double-check the configuration and ensure that sqlalchemy-kusto is correctly set up to handle the specific SQL syntax used by Azure Data Explorer.

4. Update or Patch: Check if there are any updates or patches available for sqlalchemy-kusto or Superset that address this specific issue. Sometimes, newer versions include fixes for known parsing issues.

5. Error Handling: Consider adding error handling or logging to capture more detailed information about the parsing process, which might help in identifying the root cause of the issue.

If these steps do not resolve the issue, you may need to delve deeper into the Superset codebase or the sqlalchemy-kusto library to identify any specific limitations or bugs related to SQL parsing with Kusto SQL.

To continue talking to Dosu, mention @dosu.

---

^{Help Dosu learn! Give it feedback: Great Response | Irrelevant Answer | Incorrect Sources | Too Verbose | Hallucination | Other}

[MatMor]

@dosu Thanks for reply. I check the documentation of sqlalchemy-kusto. There are no additional configuration settings and I´m using the latest version.

[dosubot]

The issue might be related to the fact that the sqlglot library, which Superset uses for SQL parsing, does not support Kusto SQL syntax. This is explicitly mentioned in the Superset codebase, where it states that Kusto KQL is a SQL-like language but not supported by sqlglot ^[1].

Given this limitation, you might need to handle Kusto SQL parsing separately. Superset uses a custom function split_kql to manage KQL scripts, which suggests that you may need to implement or adjust custom parsing logic to accommodate Kusto SQL syntax.

If you are unable to modify the parsing logic directly, consider checking if there are any community-contributed solutions or workarounds for integrating Kusto SQL with Superset. Alternatively, you might need to explore other ways to transform or adapt your queries to fit within the supported SQL dialects.

To continue talking to Dosu, mention @dosu.

---

^{Help Dosu learn! Give it feedback: Great Response | Irrelevant Answer | Incorrect Sources | Too Verbose | Hallucination | Other}