betodealmeida opened a new pull request #12188: URL: https://github.com/apache/incubator-superset/pull/12188
### SUMMARY <!--- Describe the change below, including rationale and design decisions --> The current behavior for running CTAS (create table as select) in SQL Lab is to check for **each statement** if it's a `SELECT`, and if so prepend the query with `CREATE TABLE $table_name AS $query`. For example, if we have this query: ```sql SELECT 1 AS col; ``` And we run CTAS by passing the table name `my_table` we get this query: ```sql CREATE TABLE my_table AS SELECT 1 AS col; ``` The problem is that for a query with multiple statements we run CTAS **for each one**: ```sql SELECT 1 AS col; SELECT 2 AS col; ``` When run with CTAS and `my_table` will produce these 2 queries: ```sql CREATE TABLE my_table AS SELECT 1 AS col; CREATE TABLE my_table AS SELECT 2 AS col; ``` The first query runs successfully, but the second fails because the table already exists. This PR fixes the CTAS behavior, making it more consistent with how multiple statements work in SQL Lab. It changes SQL Lab so that: 1. A CTAS query can have multiple statements, as long as the last one is a `SELECT`. This allows user to pre-process the data before loading it into the table. A simple example in MySQL would be: ```sql SET @value = 42; SELECT @value AS foo; ``` When run in a CTAS, this will create a table with the column `foo` with a row with the value 42, as expected. In Hive, a common pattern is to write queries like this: ```sql INSERT OVERWRITE staging_table ...; SELECT * FROM staging_table WHERE ...; ``` Which should also work. 2. A CVAS (create view as select) query, on the other hand, can have a single statement and it MUST be a `SELECT`. Before, there was no differentiation between CTAS and CVAS (other than the query generated). ### BEFORE/AFTER SCREENSHOTS OR ANIMATED GIF <!--- Skip this if not applicable --> N/A ### TEST PLAN <!--- What steps should be taken to verify the changes --> Tested manually, will add unit tests. ### ADDITIONAL INFORMATION <!--- Check any relevant boxes with "x" --> <!--- HINT: Include "Fixes #nnn" if you are fixing an existing issue --> - [ ] Has associated issue: - [ ] Changes UI - [ ] Requires DB Migration. - [ ] Confirm DB Migration upgrade and downgrade tested. - [ ] Introduces new feature or API - [ ] Removes existing feature or API ---------------------------------------------------------------- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
