Column Families on Partitioned Tables

Multiple table partitions and interpretation of COMMENT

We want users to be able to specify the names for the column families on a per partition basis. This means that there won't necessarily be one column family per partition and it should be possible to share a column family between different partitions. Let's look at a simple example:

CREATE TABLE sample (
    c1 INT,
    c2 INT,
    name VARCHAR(25),
    event DATE,
    PRIMARY KEY (`c1`, `c2`)
     COMMENT 'p0_cfname=name_for_cf;p1_cfname=name_for_another_cf;
              p2_cfname=name_for_cf;p3_cfname=rev:name_for_reverse_cf'
) ENGINE=ROCKSDB
PARTITION BY LIST(c1) (
    PARTITION p0 VALUES IN (1, 4, 7),
    PARTITION p1 VALUES IN (2, 5, 8),
    PARTITION p2 VALUES IN (3, 6, 9),
    PARTITION p3 VALUES IN (10, 11, 12),
    PARTITION p4 VALUES IN (20, 30, 40),
);

In this example a table is created with five different partitions: { p0, p1, p2, p3, p4 }. These partitions will have the following column families assigned to them:

p0 = name_for_cf
p1 = name_for_another_cf
p2 = name_for_cf
p3 = rev:name_for_reverse_cf

You'll notice that p0 and p2 will share the same column family; both p1 and p3 will belong to a different one; p4 will have a default column family assigned it to it.

Issues related to validation and specifying per partition qualifiers:

• If the column family for a partition isn't specified then default will be used.
• Overall pattern utilized is partitionname_qualifier=value; where qualifier in the current case is cfname.
• If something like ... PARTITIONS 12; is used as part of the table definition without explicitly specifying partitions names then the user specifying per partition qualifiers needs to be aware of default MySQL naming scheme: p0, p1, p2 etc.

If the partition layout (including both the partition definition and contents of the COMMENT) needs to be changed then standard MySQL sequence consisting of various ALTER TABLE statements will need to be used.