Hi David, The CTAS feature is already part of Flink (proposed in FLIP-218 [1]). The new FLIP-463 is just to extend the CTAS syntax to allow for adding new columns to the created table that are not part of the generated schema. I think FLIP-218 [1] was discussed in the mail list somewhere, but I couldn't find the discussion thread. I was hoping it could contain the answers for your questions as that's where CTAS was implemented. There's a user doc [2] for it that may help too.
But, in a nutshell, CTAS is similar to running CREATE and then INSERT/SELECT statements. The CTAS will execute a background job that executes the AS SELECT query and then inserts the data into the newly created table. So the table will be kept up to date as the source data changes as you said. Generated columns are from the AS SELECT schema, right? Yes, that's already allowed. FLIP-463 will also allow modifying the schema of the sink table in case you need to add more columns. I notice amazon [3] talks of the difference between view and CTAS; that > CTAS persists the content. Is this the approach we are taking? If so where > are we persisting? This is already supported by FLIP-218 [1]. But yes, the CTAS persists the content in the new table you specify in the CTAS statement. > is the table read-only, or can we insert/ delete / update into it. If it > is read only how will the inserts , deletes updates fail. Are there any restrictions on the select ? Can the select be a join, > aggregate, windowed? I notice Azure [2] supports ISNULL. Is there a thought to change the > nullability for the CTAS? Amazon [4] ignores ordered by clauses ? is that the same for this proposal? I suppose all of this is already supported by FLIP-218. I don't see any restrictions on the query. You can learn more about it in the FLIP-218 [1] - Sergio [1] https://cwiki.apache.org/confluence/pages/viewpage.action?pageId=199541185 [2] https://nightlies.apache.org/flink/flink-docs-master/docs/dev/table/sql/create/#as-select_statement On Mon, Jun 10, 2024 at 3:10 AM David Radley <david_rad...@uk.ibm.com> wrote: > Hi Sergio, > Sounds good . I am relatively new to this area and had some basic > questions: > > I notice in [1] it talks of materialized views. And CREATE view can > already take the AS keyword. It would be useful to me to understand when we > would use each of these. > > - I assume the table will be kept up to date as the source data changes > like a view. > - Are there any restrictions on the select ? Can the select be a join, > aggregate, windowed? > - I assume generated columns are allowed? > - is the table read-only, or can we insert/ delete / update into it. If it > is read only how will the inserts , deletes updates fail. > - I notice Azure [2] supports ISNULL. Is there a thought to change the > nullability for the CTAS? > - I notice amazon [3] talks of the difference between view and CTAS; that > CTAS persists the content. Is this the approach we are taking? If so where > are we persisting? > - Amazon [4] ignores ordered by clauses ? is that the same for this > proposal? > > [1] > https://nightlies.apache.org/flink/flink-docs-master/docs/dev/table/concepts/dynamic_tables/ > [2] > https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-develop-ctas#selectinto-vs-ctas > [3] > https://docs.aws.amazon.com/athena/latest/ug/ctas-considerations-limitations.html#ctas-considerations-limitations-queries-vs-views > [4] > https://docs.aws.amazon.com/athena/latest/ug/ctas-considerations-limitations.html#ctas-considerations-limitations-order-by-ignored > > > Kind regards, David. > > From: Sergio Pena <ser...@confluent.io.INVALID> > Date: Friday, 7 June 2024 at 16:13 > To: dev@flink.apache.org <dev@flink.apache.org> > Subject: [EXTERNAL] [DISCUSS] FLIP-463: Schema Definition in CREATE TABLE > AS Statement > HI All, > > I'd like to start a discussion on FLIP-463: Schema Definition in CREATE > TABLE AS Statement [1] > > The proposal extends the CTAS statement to allow users to define their own > schema by adding columns, primary and partition keys, and table > distribution to the CREATE statement. > > Any thoughts are welcome. > > Thanks, > - Sergio Pena > > [1] > > https://cwiki.apache.org/confluence/display/FLINK/FLIP-463%3A+Schema+Definition+in+CREATE+TABLE+AS+Statement > > Unless otherwise stated above: > > IBM United Kingdom Limited > Registered in England and Wales with number 741598 > Registered office: PO Box 41, North Harbour, Portsmouth, Hants. PO6 3AU >
