Sure Yuxia, I just added the support for RTAS statements too. - Sergio
On Wed, Jun 12, 2024 at 8:22 PM yuxia <luoyu...@alumni.sjtu.edu.cn> wrote: > Hi, Sergio. > Thanks for driving the FLIP. Given we also has REPLACE TABLE AS > Statement[1] and it's almost same with CREATE TABLE AS Statement, > would you mind also supporting schema definition for REPLACE TABLE AS > Statement in this FLIP? It'll be a great to align REPLACE TABLE AS Statement > to CREATE TABLE AS Statement > > > [1] > https://cwiki.apache.org/confluence/display/FLINK/FLIP-303%3A+Support+REPLACE+TABLE+AS+SELECT+statement > > Best regards, > Yuxia > > ----- 原始邮件 ----- > 发件人: "Timo Walther" <twal...@apache.org> > 收件人: "dev" <dev@flink.apache.org> > 发送时间: 星期三, 2024年 6 月 12日 下午 10:19:14 > 主题: Re: [DISCUSS] FLIP-463: Schema Definition in CREATE TABLE AS Statement > > > I just noticed the CREATE TABLE LIKE statement allows the definition > > of new columns in the CREATE part. The difference > > with this CTAS proposal is that TABLE LIKE appends the new columns at > > the end of the schema instead of adding them > > at the beginning like this proposal and Mysql do. > > This should be fine. The LIKE rather "extends from" the right table. > Whereas the SELECT in CTAS rather extends the left schema definition. > Given that "the extended part" is always appended, we could argue that > the current CTAS behavior in the FLIP is acceptable. > > > 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. > > Flink offers similar functionality in INSERT INTO. INSERT INTO also > allows syntax like: `INSERT INTO (b, c) SELECT * FROM t`. So given that > our CTAS can be seen as a CREATE TABLE + INSERT INTO. I would adopt > Jeyhun comment in the FLIP. If users don't want to add additional schema > parts, the same column reordering should be available as if one would > write a INSERT INTO. > > Regards, > Timo > > > > > On 12.06.24 04:30, Yanquan Lv wrote: > > Hi Sergio, thanks for driving it, +1 for this. > > > > I have some comments: > > 1. If we have a source table with primary keys and partition keys > defined, > > what is the default behavior if PARTITIONED and DISTRIBUTED not specified > > in the CTAS statement, It should not be inherited by default？ > > 2. I suggest providing a complete syntax that includes table_properties > > like FLIP-218. > > > > > > Sergio Pena <ser...@confluent.io.invalid> 于2024年6月12日周三 03:54写道： > > > >> I just noticed the CREATE TABLE LIKE statement allows the definition of > new > >> columns in the CREATE part. The difference > >> with this CTAS proposal is that TABLE LIKE appends the new columns at > the > >> end of the schema instead of adding them > >> at the beginning like this proposal and Mysql do. > >> > >>> create table t1(id int, name string); > >>>> create table s1(a int, b string) like t1; > >>>> describe s1; > >> > >> +-------------+-----------+----------+--------+ > >>> | Column Name | Data Type | Nullable | Extras | > >>> +-------------+-----------+----------+--------+ > >>> | id | INT | NULL | | > >>> | name | STRING | NULL | | > >>> | a | INT | NULL | | > >>> | b | STRING | NULL | | > >>> +-------------+-----------+----------+--------+ > >> > >> > >> > >> The CREATE TABLE LIKE also does not let the definition of existing > columns > >> in the CREATE part. The statement fails > >> that the column already exists. > >> > >>> create table t1(id int, name string); > >> > >>> create table s1(id double) like t1; > >>> A column named 'id' already exists in the base table. > >>> > >> > >> What do you guys think of making it similar to the CREATE TABLE LIKE? > Seems > >> the best approach in order to > >> be compatible with it. > >> > >> - Sergio > >> > >> On Tue, Jun 11, 2024 at 2:10 PM Sergio Pena <ser...@confluent.io> > wrote: > >> > >>> 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 > >>>>>>> > >>>>>> > >>>>> > >>>> > >>>> > >> > > >
