Cannot save queries that query azure data lake with OPENROWSET syntax

[surajsrivathsa]

Hello,

I successfully ran the below query to fetch data from data lake. i needed to enable 'Allow DML/DDL '.
However i cannot save teh data as dataset or chart and i get below exception

SELECT *  FROM OPENROWSET(
                BULK 
'https://mydatalake.dfs.core.windows.net/mydata/myevents/partition=1/partition=2/partition=3/partition=4/*/*/*/*/*/*.parquet',
                    FORMAT = 'PARQUET'
                ) 
                AS records_json ;

exception is as below

ERROR:flask_appbuilder.api:Error parsing near''https://mydatalake.dfs.core.windows.net/mydata/myevents/partition=1/partition=2/partition=3/partition=4/*/*/*/*/*/*.parquet'' at line 3:101
superset_app          | Traceback (most recent call last):
superset_app          |   File "/app/superset/sql/parse.py", line 266, in _parse
superset_app          |     return sqlglot.parse(script, dialect=dialect)

superset.exceptions.SupersetParseError: Error parsing near ''https://mydatalake.dfs.core.windows.net/mydata/myevents/partition=1/partition=2/partition=3/partition=4/*/*/*/*/*/*.parquet'' at line 1:101

sqlglot.errors.ParseError: Expecting ). Line 4, Col: 101.

Is there any way to fix this issue?

What configuration changes should i make in superset?

[dosubot[bot]]

A similar issue was discussed in a thread about connecting to Azure Data Lake Gen 2 using OPENROWSET in Superset ^[1]. Here are some potential solutions:

1. Modify the Database Configuration: You can set the allow_dml attribute to True for the specific database connection in Superset. This allows DDL/DML operations, but be cautious of the security implications.

2. Create a View: Consider creating a view in your database that encapsulates the OPENROWSET logic. You can then query this view from Superset, which is a safer approach.

3. Customize SQL Parsing: If necessary, you can extend Superset's SQL parser to support additional syntax by modifying the sqlparse library. This involves configuring the Lexer to extend sqlparse by modifying the SQL_REGEX to include additional patterns. This requires a deeper understanding of Superset's codebase and should be done with caution.

These approaches might help you resolve the issue with saving queries that use OPENROWSET.

To continue talking to Dosu, mention @dosu.

---

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

[surajsrivathsa]

@dosu I can run the query but not save it. the error occurs in sqlglot library. sqlglot.errors.ParseError: Expecting ). Line 3, Col: 101. v

[dosubot[bot]]

The error you're encountering is related to the sqlglot library's inability to parse the OPENROWSET syntax correctly. This is likely due to the specific SQL syntax not being fully supported by the sqlglot parser. Here are a few steps you can consider to address this issue:

1. Check for Updates: Ensure that you are using the latest version of Superset and sqlglot, as updates may include fixes or improvements for parsing different SQL syntaxes.

2. Modify the SQL Query: If possible, simplify the SQL query or break it down into parts that can be parsed separately. This might involve creating a view or using a different approach to access the data.

3. Extend SQL Parsing: If you have the capability, you might consider extending the sqlglot library to support the OPENROWSET syntax. This would involve modifying the library to recognize and correctly parse this specific syntax.

4. Create a View: As previously suggested, creating a view in your database that encapsulates the OPENROWSET logic can be a workaround. You can then query this view from Superset.

These steps might help you work around the limitations of the sqlglot library with the OPENROWSET syntax.

To continue talking to Dosu, mention @dosu.

---

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

[surajsrivathsa]

@dosu I solved it. you need to sorround the file paths by small brackets. this solves the issue

SELECT * FROM OPENROWSET( BULK ( 'https://mydatalake.dfs.core.windows.net/mydata/myevents/partition=1/partition=2/partition=3/partition=4/*/*/*/*/*/*.parquet'), FORMAT = 'PARQUET' ) AS records_json ;

[dosubot[bot]]

Hey @surajsrivathsa! I'm glad you found a solution to your issue with the OPENROWSET syntax. However, I'm just a bot designed to help with technical questions, bug fixes, and engineering knowledge. If you have any more questions or need further assistance, feel free to ask! If you think I should have been able to help with this request, you can reach out to the Dosu Team.

To continue talking to Dosu, mention @dosu.

---

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