Re: [DISCUSS] Introduce CREATE TABLE LIKE grammer

👍 On Thu, Nov 7, 2024 at 10:58 PM guo Maxwell wrote: > Thank you very much for your careful observation. I have corrected it, it > is out of date, just remove the OPTIONS keyword. > > > Dave Herrington 于2024年11月8日周五 14:52写道： > >> I wanted to weigh in on the options handling. >> >> Being able to

Re: [DISCUSS] Introduce CREATE TABLE LIKE grammer

Thank you very much for your careful observation. I have corrected it, it is out of date, just remove the OPTIONS keyword. Dave Herrington 于2024年11月8日周五 14:52写道： > I wanted to weigh in on the options handling. > > Being able to override table options like compaction strategy would be > valuable

Re: [DISCUSS] Introduce CREATE TABLE LIKE grammer

I wanted to weigh in on the options handling. Being able to override table options like compaction strategy would be valuable for use cases when we are evaluating side-by-side the performance of different compaction settings. We can always do ALTER TABLE after the CREATE TABLE LIKE, but it would

Re: [DISCUSS] Introduce CREATE TABLE LIKE grammer

Alright ... So, it is OK to NOT support this: CREATE TABLE cycling.cyclist_name4 LIKE cycling.cyclist_name WITH TRIGGERS AND VIEWS AND compaction = { 'class' : 'LeveledCompactionStrategy' } AND default_time_to_live = 86400; I think that if we ever wanted to support overriding of t

Re: [DISCUSS] Introduce CREATE TABLE LIKE grammer

Any update on this ?If there are no updates, I wonder if we can start a new update on the VOTE thread. guo Maxwell 于2024年11月5日周二 19:23写道： > Hello, everyone. > I have a point of view that our CREATE TABLE LIKE grammar should not > support the setting of table options, like : > >> CREATE TABLE cyc

Re: [DISCUSS] Introduce CREATE TABLE LIKE grammer

Hello, everyone. I have a point of view that our CREATE TABLE LIKE grammar should not support the setting of table options, like : > CREATE TABLE cycling.cyclist_name4 LIKE cycling.cyclist_name WITH TRIGGERS > AND VIEWS AND compaction = { 'class' : 'LeveledCompactionStrategy' } AND > default_time_

Re: [DISCUSS] Introduce CREATE TABLE LIKE grammer

Hi，stefan and Dave, I do not intend to implement the BNF of COPY TABLE based on the BNF of CREATE TABLE. All table options are indeed copied by default. Therefore, the following syntax is not supported: CREATE TABLE cycling.cyclist_name4 LIKE cycling.cyclist_name WITH TRIGGERS > AND VIEWS AND comp

Re: [DISCUSS] Introduce CREATE TABLE LIKE grammer

1) Just mention that it will not be part of phase 1, I am OK if it will be delivered later. 2) If we had "ALL" introduced, then we would have something like this: CREATE TABLE cycling.cyclist_name4 LIKE cycling.cyclist_name WITH ALL AND compaction = { 'class' : 'LeveledCompactionStrat

Re: [DISCUSS] Introduce CREATE TABLE LIKE grammer

Hi stefan 1、yes, cross-keyspace copying will be much complicated than copying under same keyspace , but I think we can support it in the future , and I think it is under the scope of this CEP , so I add it .Or is it that the work planned for the next step should not be listed here for the time bein

Re: [DISCUSS] Introduce CREATE TABLE LIKE grammer

Hi Maxwell, 1) I noticed that there is table copying across keyspaces in your goal number 2) in the CEP. Is this correct? I was thinking that we are doing same-keyspace copying for now and it will be considered later, as you elaborate on that further down the document. Cross-keyspace copying would

Re: [DISCUSS] Introduce CREATE TABLE LIKE grammer

So we should be able to start voting on this now. guo Maxwell 于2024年10月28日周一 17:20写道： > Here is the latest updated CEP-43 > > > > guo Maxwell 于2024年10月24日周四 19:53写道： > >> yes，you are right. I wi

Re: [DISCUSS] Introduce CREATE TABLE LIKE grammer

Here is the latest updated CEP-43 guo Maxwell 于2024年10月24日周四 19:53写道： > yes，you are right. I will add this > > Štefan Miklošovič 于2024年10月24日 周四下午4:42写道： > >> The CEP should also mention that co

Re: [DISCUSS] Introduce CREATE TABLE LIKE grammer

yes，you are right. I will add this Štefan Miklošovič 于2024年10月24日 周四下午4:42写道： > The CEP should also mention that copying system tables or virtual tables > or materialized views and similar are not supported and an attempt of doing > so will error out. > > On Thu, Oct 24, 2024 at 7:16 AM Dave Herr

Re: [DISCUSS] Introduce CREATE TABLE LIKE grammer

The CEP should also mention that copying system tables or virtual tables or materialized views and similar are not supported and an attempt of doing so will error out. On Thu, Oct 24, 2024 at 7:16 AM Dave Herrington wrote: > Strong +1 to copy all options by default. This is intuitive to me. The

Re: [DISCUSS] Introduce CREATE TABLE LIKE grammer

Strong +1 to copy all options by default. This is intuitive to me. Then I would like to explicitly override any options of my choosing. -Dave On Wed, Oct 23, 2024 at 9:57 PM guo Maxwell wrote: > OK，thank you for your suggestions ，I will revise the CEP and copy table > OPTIONS by default. > > J

Re: [DISCUSS] Introduce CREATE TABLE LIKE grammer

OK，thank you for your suggestions ，I will revise the CEP and copy table OPTIONS by default. Jon Haddad 于2024年10月23日 周三下午9:18写道： > Also strongly +1 to copying all the options. > > > On Wed, Oct 23, 2024 at 5:52 AM Josh McKenzie > wrote: > >> I'm a very strong +1 to having the default functionalit

Re: [DISCUSS] Introduce CREATE TABLE LIKE grammer

Also strongly +1 to copying all the options. On Wed, Oct 23, 2024 at 5:52 AM Josh McKenzie wrote: > I'm a very strong +1 to having the default functionality be to copy *ALL* > options. > > Intuitively, as a user, if I tell a software system to make a clone of > something I don't expect it to be

Re: [DISCUSS] Introduce CREATE TABLE LIKE grammer

I'm a very strong +1 to having the default functionality be to copy *ALL* options. Intuitively, as a user, if I tell a software system to make a clone of something I don't expect it to be shallow or a subset defined by some external developer somewhere. I expect it to be a clone. Adding in som

Re: [DISCUSS] Introduce CREATE TABLE LIKE grammer

options are inherently part of that table as well, same as schema. In fact, _schema_ includes all options. Not just columns and its names. If you change some option, you effectively have a different schema, schema version changes by changing an option. So if we do not copy options too, we are kind

Re: [DISCUSS] Introduce CREATE TABLE LIKE grammer

The reason for using OPTION keyword is that I want to provide users with more choices . The default behavior for copying a table is to copy the basic item of table （column and their data type，mask，constraint），others thing belongs to the table like option，views，trigger are optional in my mind. You a

Re: [DISCUSS] Introduce CREATE TABLE LIKE grammer

The problem is that when I do this minimal CQL which shows this feature: CREATE TABLE ks.tb_copy LIKE ks.tb; then you are saying that when I _do not_ specify WITH OPTIONS then I get Cassandra's defaults. Only after I specify WITH OPTIONS, it would truly be a copy. This is not a good design. Beca

Re: [DISCUSS] Introduce CREATE TABLE LIKE grammer

I just don't see OPTIONS as important. When I want to copy a table, I am copying a table _with everything_. Options included, by default. Why would I want to have a copy of a table with options different from the base one? On Mon, Oct 21, 2024 at 3:55 PM Bernardo Botella < conta...@bernardobotell

Re: [DISCUSS] Introduce CREATE TABLE LIKE grammer

Hi Guo, +1 for the CONSTRAINTS keyword to be added into the default behavior. Bernardo > On Oct 21, 2024, at 12:01 AM, guo Maxwell wrote: > > I think the CONSTRAINTS keyword keyword may be in the same situation as > datamask. > Maybe it is better to include constraints into the default be

Re: [DISCUSS] Introduce CREATE TABLE LIKE grammer

I think the CONSTRAINTS keyword keyword may be in the same situation as datamask. Maybe it is better to include constraints into the default behavior of table copy together with column name, column data type and data mask. guo Maxwell 于2024年10月21日周一 14:56写道： > To yifan : > I don't mind adding

Re: [DISCUSS] Introduce CREATE TABLE LIKE grammer

To yifan : I don't mind adding the ALL keyword, and it has been updated into CEP. As all you can see, our original intention was that the grammar would not be too complicated, which is what I described in cep

Re: [DISCUSS] Introduce CREATE TABLE LIKE grammer

Hi , To sefan : I may want to explain that if there is no OPTION keyword in the CQL statement, then the newly created table will only have the original table's column name 、column type and data mask ,I think this is the most basic choice when copying tables to users. Then we do some addition, we

Re: [DISCUSS] Introduce CREATE TABLE LIKE grammer

It seems like a natural extension of the CREATE TABLE statement. Looking forward to using it in the future. -Dave On Thu, Oct 17, 2024 at 5:11 PM Štefan Miklošovič wrote: > Right?! Reads like English, the impact on the existing CQL is minimal. One > LIKE which basically needs to be there and k

Re: [DISCUSS] Introduce CREATE TABLE LIKE grammer

Right?! Reads like English, the impact on the existing CQL is minimal. One LIKE which basically needs to be there and keywords of logical "components" which seamlessly integrate with WITH. I would _not_ use WITH CONSTRAINTS because constraints will be inherently part of a table schema. It is not a

Re: [DISCUSS] Introduce CREATE TABLE LIKE grammer

Basing it on CREATE TABLE, the BNF definition of the simple implementation would look something like this: create_table_statement::= CREATE TABLE [ IF NOT EXISTS ] table_name LIKE base_table_name [ WITH included_objects ] [ [ AND ] table_options ] table_options::= COMPACT STORAGE [ AND table_opt

Re: [DISCUSS] Introduce CREATE TABLE LIKE grammer

This simple approach resonates with me. I think the Cassandra doc uses "INDEXES" as the plural for index, i.e.: https://cassandra.apache.org/doc/stable/cassandra/cql/indexes.html -Dave On Thu, Oct 17, 2024 at 2:39 PM Štefan Miklošovič wrote: > Well we could do something like: > > > CREATE TABL

Re: [DISCUSS] Introduce CREATE TABLE LIKE grammer

Well we could do something like: CREATE TABLE ks.tb_copy LIKE ks.tb WITH INDICES AND TRIGGERS AND compaction = {'class': ' } AND ... but I can admit it might be seen as an overreach and I am not sure at all how it would look like in the implementation because we would need to distinguish WI

Re: [DISCUSS] Introduce CREATE TABLE LIKE grammer

I would second Štefan's option for functionality simplicity. It seems to be unnecessary to have the keywords for both inclusion and exclusion in the CEP. If needed, the exclusion (WITHOUT) can be introduced later. It would still be backward compatible. Regarding "CREATE TABLE ks.tb_copy LIKE ks.tb

Re: [DISCUSS] Introduce CREATE TABLE LIKE grammer

By the way, why can we not do something like this? CREATE TABLE ks.tb_copy LIKE ks.tb WITH compaction = {'class': ' } AND ... That is, I just use the "LIKE" keyword to copy it all as it is in ks.tb but I just override "compaction" as I want (if I am _really_ after that). That way, I get all t

Re: [DISCUSS] Introduce CREATE TABLE LIKE grammer

I think we are starting to complicate it. For me the most important question is who is actually this feature for? If people want to just prototype something fast or they just want to have "the same table just under a different name", I think that is going to be used in 99% of cases. My assumption

Re: [DISCUSS] Introduce CREATE TABLE LIKE grammer

Sorry that I overlooked the definition of the default in the CEP. I did look for it but I didn’t see it. I think the default behavior you explained makes perfect sense & what one would expect. I like the flexibility of INCLUDING and EXCLUDING that you are considering. Would it make sense to use

Re: [DISCUSS] Introduce CREATE TABLE LIKE grammer

To yifan : At the beginning of the period, I also thought about adding the keyword ALL, refer to pg , but I give up when writing cep as I find that there may be not so many properties (only three) to copy for C* and It is possible to d

Re: [DISCUSS] Introduce CREATE TABLE LIKE grammer

+1 That makes much more sense in my experience. On Wed, Oct 16, 2024 at 12:12 PM Dave Herrington wrote: > I'm coming at this with both a deep ANSI SQL background as well as CQL > background. > > Defining the default behavior is the starting point. What gets copied if > we do "CREATE TABLE new_t

Re: [DISCUSS] Introduce CREATE TABLE LIKE grammer

I'm coming at this with both a deep ANSI SQL background as well as CQL background. Defining the default behavior is the starting point. What gets copied if we do "CREATE TABLE new_table LIKE original_table;" without a WITH clause? Then, you build on that with the specific WITH options. WITH ALL

Re: [DISCUSS] Introduce CREATE TABLE LIKE grammer

"WITH ALL" seems to be a natural addition to the directives. What do you think about adding the fifth keyword ALL to retain all fields of the table schema? For instance, CREATE TABLE new_table LIKE original_table WITH ALL, it replicates options, indexes, triggers, constraints and any applicable ki

Re: [DISCUSS] Introduce CREATE TABLE LIKE grammer

Disscussed with Bernardo on slack，and +1 with his advice on adding a fourth keyword. The keyword would be CONSTRAINTS ， any more suggestion ？ guo Maxwell 于2024年10月16日 周三上午9:55写道： > Hi yifan, > Thanks for bringing this up. The SELECT permission on the original table > is needed. Mysql and PG all

Re: [DISCUSS] Introduce CREATE TABLE LIKE grammer

Hi yifan, Thanks for bringing this up. The SELECT permission on the original table is needed. Mysql and PG all have mentioned this, and I also specifically noticed this in my code. I probably missed this in the cep documentation. 😅 Yifan Cai 于2024年10月16日周三 07:46写道： > Thanks for creating the CEP

Re: [DISCUSS] Introduce CREATE TABLE LIKE grammer

Thanks for creating the CEP! I think it is missing Bernardo's comment on "the need for read permissions on the source table". CreateTableStatement does not check the permissions outside of the enclosing keyspace. Having the SELECT permission on the original table is a requirement for CREATE TABLE

Re: [DISCUSS] Introduce CREATE TABLE LIKE grammer

Hi Guo, Do you think it would make sense to add a fourth keyword to add after the WITH for Constraints? (See CEP-42) Copying a table without the defined constraints may be useful. Bernardo > On Sep 29, 2024, at 11:00 PM, guo Maxwell wrote: > > Hello, everyone , > I have finished the doc for

Re: [DISCUSS] Introduce CREATE TABLE LIKE grammer

Hello, everyone , I have finished the doc for CEP-43 for CREATE_TABLE_LIKE as said before, looking forward to your suggestions. Štefan Miklošovič 于2024年9月25日周三 03:51写道： > I am sorry I do not follo

Re: [DISCUSS] Introduce CREATE TABLE LIKE grammer

I am sorry I do not follow what you mean, maybe an example would help. On Tue, Sep 24, 2024 at 6:18 PM guo Maxwell wrote: > > If there are multiple schema information changes in one ddl statement, > will there be schema conflicts in extreme cases? > For example, our statement contains both table

Re: [DISCUSS] Introduce CREATE TABLE LIKE grammer

If there are multiple schema information changes in one ddl statement, will there be schema conflicts in extreme cases? For example, our statement contains both table creation and index creation. guo Maxwell 于2024年9月24日 周二下午8:12写道： > +1 on splitting this task and adding the ability to copy table

Re: [DISCUSS] Introduce CREATE TABLE LIKE grammer

+1 on splitting this task and adding the ability to copy tables through different keyspaces in the future. Štefan Miklošovič 于2024年9月23日周一 22:05写道： > If we have this table > > CREATE TABLE ks.tb2 ( > id int PRIMARY KEY, > name text > ); > > I can either specify name of an index on my ow

Re: [DISCUSS] Introduce CREATE TABLE LIKE grammer

If we have this table CREATE TABLE ks.tb2 ( id int PRIMARY KEY, name text ); I can either specify name of an index on my own like this: CREATE INDEX name_index ON ks.tb2 (name) ; or I can let Cassandra to figure that name on its own: CREATE INDEX ON ks.tb2 (name) ; in that case it wil

Re: [DISCUSS] Introduce CREATE TABLE LIKE grammer

Hello everyone, Cep is being written, and I encountered some problems during the process. I would like to discuss them with you. If you read the description of this CASSANDRA-7662 , we will find that initially the original creator of this jira

Re: [DISCUSS] Introduce CREATE TABLE LIKE grammer

No，I think still need some discuss on grammar detail after I finish the first version Patrick McFadin 于2024年9月20日 周五上午2:24写道： > Is this CEP ready for a VOTE thread? > > On Sat, Aug 24, 2024 at 8:56 PM guo Maxwell wrote: > >> Thank you for your replies, I will prepare a CEP later. >> >> Patrick M

Re: [DISCUSS] Introduce CREATE TABLE LIKE grammer

Is this CEP ready for a VOTE thread? On Sat, Aug 24, 2024 at 8:56 PM guo Maxwell wrote: > Thank you for your replies, I will prepare a CEP later. > > Patrick McFadin 于2024年8月20日周二 02:11写道： > >> +1 This is a CEP >> >> On Mon, Aug 19, 2024 at 10:50 AM Jon Haddad wrote: >> >>> Given the fairly la

Re: [DISCUSS] Introduce CREATE TABLE LIKE grammer

Thank you for your replies, I will prepare a CEP later. Patrick McFadin 于2024年8月20日周二 02:11写道： > +1 This is a CEP > > On Mon, Aug 19, 2024 at 10:50 AM Jon Haddad wrote: > >> Given the fairly large surface area for this, i think it should be a CEP. >> >> — >> Jon Haddad >> Rustyrazorblade Consul

Re: [DISCUSS] Introduce CREATE TABLE LIKE grammer

+1 This is a CEP On Mon, Aug 19, 2024 at 10:50 AM Jon Haddad wrote: > Given the fairly large surface area for this, i think it should be a CEP. > > — > Jon Haddad > Rustyrazorblade Consulting > rustyrazorblade.com > > > On Mon, Aug 19, 2024 at 10:44 AM Bernardo Botella < > conta...@bernardobotel

Re: [DISCUSS] Introduce CREATE TABLE LIKE grammer

Given the fairly large surface area for this, i think it should be a CEP. — Jon Haddad Rustyrazorblade Consulting rustyrazorblade.com On Mon, Aug 19, 2024 at 10:44 AM Bernardo Botella < conta...@bernardobotella.com> wrote: > Definitely a nice addition to CQL. > > Looking for inspiration at how

Re: [DISCUSS] Introduce CREATE TABLE LIKE grammer

Definitely a nice addition to CQL. Looking for inspiration at how Postgres and Mysql do that may also help with the final design (I like the WITH proposed by Stefan, but I would definitely take a look at the INCLUDING keyword proposed by Postgres). https://www.postgresql.org/docs/current/sql-cre

Re: [DISCUSS] Introduce CREATE TABLE LIKE grammer

BTW this would be cool to do as well: ALTER TABLE ks.to_copy LIKE ks.tb WITH INDICES; This would mean that if we create a copy of a table, later we can decide that we need indices too, so we might "enrich" that table with indices from the old one without necessarily explicitly re-creating them on

Re: [DISCUSS] Introduce CREATE TABLE LIKE grammer

I think this is an interesting idea worth exploring. I definitely agree with Benjamin who raised important questions which needs to be answered first. Also, what about triggers? It might be rather "easy" to come up with something simple but it should be a comprehensive solution with predictable be

Re: [DISCUSS] Introduce CREATE TABLE LIKE grammer

I think that one important part will be to define the boundaries of the copy. For example, will it also copy the indexes and the Materialized views or only the table schema. Can it be done in a different keyspace? If yes, how about the UDTs if the table uses some? I do not have an opinion. Just rai

[DISCUSS] Introduce CREATE TABLE LIKE grammer

Hello， everyone： As Jira CASSANDRA-7662 has described , we would like to introduce a new grammer " CREATE TABLE LIKE " ,which simplifies creating new tables duplicating the existing ones . The format may be like : CREATE TABLE LIKE Before