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 catches everything. -Dave On Wed, Oct 16, 2024 at 11:16 AM Yifan Cai <yc25c...@gmail.com> wrote: > "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 kinds > that are introduced in the future. > > - Yifan > > On Wed, Oct 16, 2024 at 7:46 AM guo Maxwell <cclive1...@gmail.com> wrote: > >> 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 <cclive1...@gmail.com>于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 have mentioned this, and I also specifically >>> noticed this in my code. >>> >>> I probably missed this in the cep documentation. 😅 >>> >>> Yifan Cai <yc25c...@gmail.com> 于2024年10月16日周三 07:46写道： >>> >>>> 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 LIKE. >>>> >>>> - Yifan >>>> >>>> On Sun, Sep 29, 2024 at 11:01 PM guo Maxwell <cclive1...@gmail.com> >>>> wrote: >>>> >>>>> Hello, everyone , >>>>> I have finished the doc for CEP-43 for CREATE_TABLE_LIKE >>>>> <https://cwiki.apache.org/confluence/display/CASSANDRA/CEP-43++Apache+Cassandra+CREATE+TABLE++LIKE> >>>>> as >>>>> said before, looking forward to your suggestions. >>>>> >>>>> Štefan Miklošovič <smikloso...@apache.org> 于2024年9月25日周三 03:51写道： >>>>> >>>>>> 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 <cclive1...@gmail.com> >>>>>> 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 creation and index >>>>>>> creation. >>>>>>> >>>>>>> guo Maxwell <cclive1...@gmail.com>于2024年9月24日 周二下午8:12写道： >>>>>>> >>>>>>>> +1 on splitting this task and adding the ability to copy tables >>>>>>>> through different keyspaces in the future. >>>>>>>> >>>>>>>> Štefan Miklošovič <smikloso...@apache.org> 于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 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 will name that index "tb2_name_idx". >>>>>>>>> >>>>>>>>> Hence, I would expect that when we do >>>>>>>>> >>>>>>>>> ALTER TABLE ks.to_copy LIKE ks.tb2 WITH INDICES; >>>>>>>>> >>>>>>>>> Then ks.to_copy table will have an index which is called >>>>>>>>> "to_copy_name_idx" without me doing anything. >>>>>>>>> >>>>>>>>> For types, we do not need to do anything when we deal with the >>>>>>>>> same keyspace. For simplicity, I mentioned that we might deal with >>>>>>>>> the same >>>>>>>>> keyspace scenario only for now and iterate on that in the future. >>>>>>>>> >>>>>>>>> On Mon, Sep 23, 2024 at 8:53 AM guo Maxwell <cclive1...@gmail.com> >>>>>>>>> wrote: >>>>>>>>> >>>>>>>>>> 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 >>>>>>>>>> <https://issues.apache.org/jira/browse/CASSANDRA-7662>, we will >>>>>>>>>> find that initially the original creator of this jira did not intend >>>>>>>>>> to >>>>>>>>>> implement structural copying of indexes, views, and triggers only >>>>>>>>>> the >>>>>>>>>> column and its data type. >>>>>>>>>> >>>>>>>>>> However, after investigating some db related syntax and function >>>>>>>>>> implementation, I found that it may be necessary for us to provide >>>>>>>>>> some >>>>>>>>>> rich syntax to support the replication of indexes, views, etc. >>>>>>>>>> >>>>>>>>>> In order to support selective copy of the basic structure of the >>>>>>>>>> table (columns and types), table options, table-related indexes, >>>>>>>>>> views, >>>>>>>>>> triggers, etc. We need some new syntax, it seems that the syntax of >>>>>>>>>> pg is >>>>>>>>>> relatively comprehensive, it use the keyword INCLUDING/EXCLUDING to >>>>>>>>>> flexibly control the removal and retention of indexes, table >>>>>>>>>> information, >>>>>>>>>> etc. see pg create table like >>>>>>>>>> <https://www.postgresql.org/docs/8.1/sql-createtable.html> , the >>>>>>>>>> new created index name is different from the original table's index >>>>>>>>>> name , >>>>>>>>>> seenewly copied index names are different from original >>>>>>>>>> <https://github.com/postgres/postgres/blob/master/doc/src/sgml/ref/create_table.sgml#L749> >>>>>>>>>> , the name is based on some rule. >>>>>>>>>> Mysql is relatively simple and copies columns and indexes by >>>>>>>>>> default. see mysql create table like >>>>>>>>>> <https://dev.mysql.com/doc/refman/8.4/en/create-table-like.html> >>>>>>>>>> and the newly created index name is the same with the original >>>>>>>>>> table's >>>>>>>>>> index name. >>>>>>>>>> >>>>>>>>>> So for Casandra, I hope it can also support the information copy >>>>>>>>>> of index and even view/trigger. And I also hope to be able to >>>>>>>>>> flexibly >>>>>>>>>> decide which information is copied like pg. >>>>>>>>>> >>>>>>>>>> Besides, I think the copy can happen between different keyspaces. >>>>>>>>>> And UDT needs to be taken into account. >>>>>>>>>> >>>>>>>>>> But as we know the index/view/trigger name are all under keyspace >>>>>>>>>> level, so it seems that the newly created index name (or view name/ >>>>>>>>>> trigger >>>>>>>>>> name) must be different from the original tables' ,otherwise names >>>>>>>>>> would >>>>>>>>>> clash . >>>>>>>>>> >>>>>>>>>> So regarding the above problem, one idea I have is that for newly >>>>>>>>>> created types, indexes and views under different keyspaces and the >>>>>>>>>> same >>>>>>>>>> keyspace, we first generate random names for them, and then we can >>>>>>>>>> add the >>>>>>>>>> ability of modifying the names(for types/indexes/views/triggers) so >>>>>>>>>> that >>>>>>>>>> users can manually change the names. >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> guo Maxwell <cclive1...@gmail.com> 于2024年9月20日周五 08:06写道： >>>>>>>>>> >>>>>>>>>>> No，I think still need some discuss on grammar detail after I >>>>>>>>>>> finish the first version >>>>>>>>>>> >>>>>>>>>>> Patrick McFadin <pmcfa...@gmail.com>于2024年9月20日 周五上午2:24写道： >>>>>>>>>>> >>>>>>>>>>>> Is this CEP ready for a VOTE thread? >>>>>>>>>>>> >>>>>>>>>>>> On Sat, Aug 24, 2024 at 8:56 PM guo Maxwell < >>>>>>>>>>>> cclive1...@gmail.com> wrote: >>>>>>>>>>>> >>>>>>>>>>>>> Thank you for your replies, I will prepare a CEP later. >>>>>>>>>>>>> >>>>>>>>>>>>> Patrick McFadin <pmcfa...@gmail.com> 于2024年8月20日周二 02:11写道： >>>>>>>>>>>>> >>>>>>>>>>>>>> +1 This is a CEP >>>>>>>>>>>>>> >>>>>>>>>>>>>> On Mon, Aug 19, 2024 at 10:50 AM Jon Haddad < >>>>>>>>>>>>>> j...@jonhaddad.com> 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...@bernardobotella.com> wrote: >>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> 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-createtable.html >>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> https://dev.mysql.com/doc/refman/8.4/en/create-table-like.html >>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> On top of that, and as part of the interesting questions, I >>>>>>>>>>>>>>>> would like to add the permissions to the mix. Both the >>>>>>>>>>>>>>>> question about >>>>>>>>>>>>>>>> copying them over (with a WITH keyword probably), and the need >>>>>>>>>>>>>>>> for read >>>>>>>>>>>>>>>> permissions on the source table as well. >>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> Bernardo >>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> On Aug 19, 2024, at 10:01 AM, Štefan Miklošovič < >>>>>>>>>>>>>>>> smikloso...@apache.org> wrote: >>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> 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 that new table. >>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> On Mon, Aug 19, 2024 at 6:55 PM Štefan Miklošovič < >>>>>>>>>>>>>>>> smikloso...@apache.org> wrote: >>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>> 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 >>>>>>>>>>>>>>>>> behavior we all >>>>>>>>>>>>>>>>> agree on. >>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>> If a keyspace of a new table does not exist we would need >>>>>>>>>>>>>>>>> to create that one too before. For the simplicity, I would >>>>>>>>>>>>>>>>> just make it a >>>>>>>>>>>>>>>>> must to create it on same keyspace. We might iterate on that >>>>>>>>>>>>>>>>> in the future. >>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>> UDTs are created per keyspace so there is nothing to >>>>>>>>>>>>>>>>> re-create. We just need to reference it from a new table, >>>>>>>>>>>>>>>>> right? >>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>> Indexes and MVs are interesting but in theory they might >>>>>>>>>>>>>>>>> be re-created too. >>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>> Would it be appropriate to use something like this? >>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>> CREATE TABLE ks.tb_copy LIKE ks.tb WITH INDEXES AND VIEWS >>>>>>>>>>>>>>>>> AND TRIGGERS .... >>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>> Without "WITH" it would just copy a table with nothing >>>>>>>>>>>>>>>>> else. >>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>> On Mon, Aug 19, 2024 at 6:10 PM guo Maxwell < >>>>>>>>>>>>>>>>> cclive1...@gmail.com> wrote: >>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>> Hello， everyone： >>>>>>>>>>>>>>>>>> As Jira CASSANDRA-7662 >>>>>>>>>>>>>>>>>> <https://issues.apache.org/jira/browse/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 <new_table> LIKE >>>>>>>>>>>>>>>>>> <old_table> >>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>> Before I implement this function, do you have any >>>>>>>>>>>>>>>>>> suggestions on this? >>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>> Looking forward to your reply！ >>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> -- -Dave David A. Herrington II President and Chief Engineer RhinoSource, Inc. *Data Lake Architecture, Cloud Computing and Advanced Analytics.* www.rhinosource.com
