Thanks Timo for answering Jeyhun questions. To add info more about your questions Jeyhun. This proposal is not handling NULL/NOT_NULL types. I noticed that the current CTAS impl. (as Timo said) adds this constraint as part of the resulting schema. And when defining a primary key in the CREATE part, if the resulting schema does not have a NOT NULL in the column then the CTAS will fail. This is similar to the CREATE TABLE LIKE which expects the LIKE table to have a NOT NULL column if the user defines a primary key in the CREATE part.
> In some cases, redefining the column types might be redundant, especially > when users dont change the column type. A user just wants to change the > column name from the SELECT clause. Should we also support this scenario, > similar to postgres? I looked into Postgres too. Postgres matches the columns based on the order defined in the create and select part. If you want to rename a column in the create part, then that column position must be in the same position as the query column. I didn't like the Postgres approach because it does not let us add columns that do not exist in the query schema. i.e. query has schema (a int, b string), now the `a` column is renamed to `id` because both are in the same position 0 `create table s1(id int) as select a, b from t1`; results in: [id int, b string] I think, if users want to rename then they can use a different alias in the select part. They could also do explicit casting for changing the data types, which now makes it redundant (as you said) to allow redefining the query columns again. But perhaps there are cases where explicit casting does not work and just defining the column would? i.e. making a nullable type to not null? I couldn't make `cast(c1 as int not null)` to work for instance, but it may work in the create part? > Could you also mention the casting rules in the FLIP for this case? I mentioned they're the same as insert/select when doing implicit casting. I will search for more info about the insert/select and add the casting rules in the flip.. - Sergio On Tue, Jun 11, 2024 at 12:59 AM Timo Walther <twal...@apache.org> wrote: > Hi Sergio, > > thanks for proposing this FLIP for finalizing the CTAS statement. > Adopting the logic from MySQL for deriving and potentially overwriting > parts of the schema should be easy to understand for everyone. So +1 for > the FLIP in general. > > > How do you handle CTAS statements with SELECT clauses that have > > (implicit or explicit) NULLABLE or NOT NULLABLE columns? > > @Jeyhun: I don't think there is anything special about this. The current > CTAS implementation should already cover that. It takes the nullability > of the column's data type as a constraint into derived schema. Keep in > mind that nullability is part of the data type in Flink, not only a > constraint on the schema. This decision was made due to Calcite internals. > > > redefining the column types might be redundant, especially > > when users dont change the column type > > This is indeed a good point. On one hand, I think we should avoid > further complicating the syntax. But looking at other vendors [1] this > seems indeed a valid use case. If it doesn't cause too many special > cases in the parser (and it's look-ahead), I'm fine with supporting a > list of column names as well. However, the most important use case will > be specifying a watermark, metadata columns, or other schema parts that > are not just columns names. > > Regards, > Timo > > > [1] > > https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-as-select-azure-sql-data-warehouse?view=azure-sqldw-latest > > > On 10.06.24 21:37, Jeyhun Karimov wrote: > > Hi Sergio, > > > > Thanks for driving this FLIP. +1 for it. > > I have a few questions: > > > > - How do you handle CTAS statements with SELECT clauses that have > (implicit > > or explicit) NULLABLE or NOT NULLABLE columns? Could you also mention the > > casting rules in the FLIP for this case? > > - In some cases, redefining the column types might be redundant, > especially > > when users dont change the column type. For example, a user just wants to > > change the column name from the SELECT clause. > > Should we also support this scenario, similar to the postgres [1] ? > > > > Regards, > > Jeyhun > > > > > > [1] https://www.postgresql.org/docs/8.1/sql-createtableas.html > > > > On Mon, Jun 10, 2024 at 6:28 PM Sergio Pena <ser...@confluent.io.invalid > > > > wrote: > > > >> 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 > >>> > >> > > > >
