feat: Enhance Virtual Dataset Parsing to Support Advanced Query Operations

[mcdogg17]

SUMMARY

This update significantly improves the parsing mechanism for virtual datasets, enabling users to go beyond basic SELECT operations. With this enhancement, data retrieval becomes more flexible and efficient, allowing for complex queries and transformations. These improvements pave the way for advanced analytics, empowering users to derive deeper insights from their datasets.

BEFORE/AFTER SCREENSHOTS OR ANIMATED GIF

TESTING INSTRUCTIONS

ADDITIONAL INFORMATION

• [ ] Has associated issue:
• [ ] Required feature flags:
• [ ] Changes UI
• [ ] Includes DB Migration (follow approval process in SIP-59)
  • [ ] Migration is atomic, supports rollback & is backwards-compatible
  • [ ] Confirm DB migration upgrade and downgrade tested
  • [ ] Runtime estimates and downtime expectations provided
• [ ] Introduces new feature or API
• [ ] Removes existing feature or API

[mcdogg17]

@supersetbot orglabel

[sadpandajoe]

@mcdogg17 can you add more details in the summary? What are the before and after for this pr?

[michael-s-molina]

Thanks @mcdogg17 for the PR. Right now, the restriction for only SELECT type queries when interacting with virtual datasets is by design. What are the use cases you're trying to achieve?

[mcdogg17]

Thanks @mcdogg17 for the PR. Right now, the restriction for only SELECT type queries when interacting with virtual datasets is by design. What are the use cases you're trying to achieve?

Hello, We would like to request the ability to perform multiple queries prior to the SELECT statement when creating a virtual dataset. For example, we would like to create a temporary table as part of the process.

[michael-s-molina]

@mcdogg17 Thanks for linking the SIP to the PR. Now, I understand the context. Given that this SIP is still open for discussion, I'll add a hold:sip! label to this PR which can be removed once we have a voted SIP with an agreed solution.

[rusackas]

Looks like this is in need of a rebase, and the SIP is in need of a steward to carry it through the process. While that happens, I'll convert this to Draft mode. It may be closed in time if there's no activity on either front.

[ETselikov]

Looks like this is in need of a rebase, and the SIP is in need of a steward to carry it through the process. While that happens, I'll convert this to Draft mode. It may be closed in time if there's no activity on either front.

Hey! What can I do to carry this SIP through?

[mcdogg17]

Hi! The very idea of PR is described in this SIP. Here I'll tell you more about the functionality itself. Previously, when saving a virtual dataset, it was impossible to use the CREATE TEMP TABLE and so on constructions. I implemented this function as follows: when saving a dataset, we execute the entire query, at the end of which there must be a SELECT, otherwise a following error will be raised:

"CTAS (create table as select) can only be run with a query where the last statement is a SELECT. Please make sure your query has a SELECT as its last statement. Then, try running your query again."

This is necessary to save a query for further use as a dataset. The last select expression will be used in a generated viz query as expected:

SELECT * FROM (select statement from query virtual dataset) as virtual_dataset

[TechAuditBI]

@supersetbot orglabel

[sadpandajoe]

Looks like this PR might have gotten stale. Going to close it out. Feel free to reopen and rebase.