Is it possible to directly connect to Azure Datalake Gen 2 using OPENROWSET from superset?

[surajsrivathsa]

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 ;

When i try i get below errors. I can however read from a view or table correctly

DB engine Error: 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.

Unable to parse SQL: Error parsing near ''https://mydatalake.dfs.core.windows.net/mydata/myevents/partition=1/partition=2/partition=3/partition=4/*/*/*/*/*/*.parquet'' at line 3:124

[dosubot[bot]]

Hey @surajsrivathsa! I'm here to help you with bug fixes, answer your questions, and guide you on becoming a contributor. Let's squash those bugs together! 🤖

The error you're encountering suggests that Superset is unable to confirm that the OPENROWSET query is read-only, which is necessary because the database does not allow DDL/DML operations. Superset uses a parsing mechanism to determine if a query is read-only, and if it cannot confirm this, it will reject the query ^[1].

The error message also indicates a parsing issue with the SQL syntax, particularly around the URL in the OPENROWSET function. This might be due to Superset's SQL parser not fully supporting the syntax used in your query.

To work around this, you might consider creating a view or table in your database that encapsulates the OPENROWSET logic, and then querying that view or table from Superset. This approach has worked for you in the past, as you mentioned being able to read from a view or table correctly.

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 :

1. isnt there an sql alchemy hack to use open rowset?
2. cant we use this hack inside superset?
3. what changes should we make

[dosubot[bot]]

1. SQLAlchemy itself doesn't directly support OPENROWSET as it is a SQL Server-specific function. However, you can execute raw SQL queries using SQLAlchemy, which allows you to use OPENROWSET if your database connection supports it.

2. In Superset, you can execute raw SQL queries through SQL Lab, but the issue you're facing is related to Superset's SQL parsing and read-only query validation. Superset needs to confirm that a query is read-only, and if it can't parse the query to confirm this, it will reject it. This is controlled by the allow_dml attribute of the Database object. If allow_dml is set to False, Superset will not allow queries it cannot confirm as read-only ^[1].

3. To use OPENROWSET in Superset, you might consider the following approaches:

   • Modify the Database Configuration: If you have control over the Superset configuration, you could set allow_dml to True for the specific database connection. This would allow DDL/DML operations, but be aware of the potential security implications.
   • Create a View: As mentioned earlier, creating a view in your database that encapsulates the OPENROWSET logic and querying that view from Superset is a safer approach.
   • Customize SQL Parsing: If you need to extend Superset's SQL parser to support additional syntax, you can modify the sqlparse library used by Superset. This involves configuring the Lexer to extend sqlparse by modifying the SQL_REGEX to include additional patterns ^[2]. However, this requires a deeper understanding of Superset's codebase and should be done with caution.

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}

[luimgmv]

Hi @dosu ,

I've been encountering the same problem, I've enabled the allow_dml option from database settings, the SQL Query works fine and I can create the Dataset but when I try to generate the Chart using that Dataset I get a parsing error.

Any idea on how to fix that?