+1 That makes much more sense in my experience. On Wed, Oct 16, 2024 at 12:12 PM Dave Herrington <he...@rhinosource.com> 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_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 >
