The "with as select" syntax cannot be used when querying the odps database

[yujun2021]

Bug description

The "with as select" syntax cannot be used when querying the odps database

I'm curious what caused it.

Screenshots/recordings

ODPS 异常
odps error: InstanceTunnel cannot be opened at a non-select SQL Task.

这可能由以下因素触发：
Issue 1002 - 数据库返回意外错误。

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]

The error message "ODPS error: InstanceTunnel cannot be opened at a non-select SQL Task" suggests that the ODPS database does not support executing non-select SQL tasks using the "WITH AS SELECT" syntax. This could be due to limitations in the ODPS SQL dialect or the way Superset handles query execution contexts for ODPS databases. Unfortunately, specific details about how Superset manages ODPS connections or handles such queries are not available in the repository ^[1][2][3][4].

To address this issue, you might consider checking the ODPS documentation for any restrictions on SQL syntax or exploring alternative query structures that are compatible with ODPS. Additionally, reviewing Superset's configuration settings related to ODPS might provide insights into any adjustments needed for successful query execution.

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}

[rusackas]

I don't know anything about ODPS... can you tell us a bit more about what DB driver / engine spec you're using here? Without steps we can reproduce, this isn't likely to get solved. CC @betodealmeida in case they have some insight here and I'm mistaken :)

[yujun2021]

I don't know anything about ODPS... can you tell us a bit more about what DB driver / engine spec you're using here? Without steps we can reproduce, this isn't likely to get solved. CC @betodealmeida in case they have some insight here and I'm mistaken :)

ODPS, also known as MaxCompute, is a distributed, big data processing engine developed by Alibaba Cloud. It is designed for large-scale data warehousing and analytics, offering high performance, reliability, and scalability for processing petabyte-level datasets.

Key Features of ODPS:
Massively Parallel Processing (MPP) – Distributes computation across clusters for fast query execution.

Serverless Architecture – No infrastructure management required; users pay for storage and computation resources used.

SQL Support – Compatible with standard SQL syntax, along with extensions for big data scenarios.

Batch & Interactive Processing – Handles both long-running batch jobs and interactive queries efficiently.

Security & Compliance – Provides fine-grained access control, encryption, and compliance with enterprise security standards.

Cost-Effective Storage – Uses columnar storage and compression to optimize costs.

Integration with Ecosystem – Works seamlessly with Alibaba Cloud services like DataWorks (for workflow management) and PAI (AI platform).

[yujun2021]

I don't know anything about ODPS... can you tell us a bit more about what DB driver / engine spec you're using here? Without steps we can reproduce, this isn't likely to get solved. CC @betodealmeida in case they have some insight here and I'm mistaken :)

I am currently using the pyodps driver to connect to superset for data analysis. However, it does not support the "with as select" syntax.

https://github.com/aliyun/aliyun-odps-python-sdk?tab=readme-ov-file

[yujun2021]

@rusackas @bgreenlee