如何创建和修改表?

在快速开始中，我们已经大概了解了在如何通过iDB Cloud创建和修改表。在这一节中，我们将详细描述如何通过DDL创建和修改表。
由于分析型数据库有一些传统关系型数据库没有的特性，所以分析型数据库的DDL在遵循类MySQL规范的基础上，有不少的独有的属性和语法。分析型数据库中创建事实表DDL语法结构如下：<PRE prettyprinted? linenums>

1. CREATE TABLE db_name.table_name (
2. col1 bigint COMMENT 'col1',
3. col2 varchar COMMENT 'col2',
4. col3 int COMMENT 'col3',
5. col4 bigint COMMENT 'col4',
6. col5 multivalue COMMENT 'col5 多值列',
7. [primary key (col1, col3)]
8. )
9. PARTITION BY HASH KEY(col1)
10. PARTITION NUM 50
11. [SUBPARTITION BY LIST (part_col2 long)]
12. [SUBPARTITION OPTIONS (available_partition_num = 30)]
13. [CLUSTERED BY (col3,col4)]
14. TABLEGROUP ads_test ;
15. [options (updateType='{realtime | batch}')]

其中’[]’中的内容为视情况填写的可选项。创建事实表（也就是非维度表）时，必须指定的是一个数据库名（db_name）和表名（table_name），以及至少一列的列信息，至少一个分区的信息以及一个表组。
首先，根据表的数据更新方式不同，分析型数据库的表根据updateType分为批量更新表（仅能够离线批量更新数据）和实时更新表（能够通过insert/delete实时更新数据），用updateType以区分，如果updateType选项不填则默认为批量更新表。需要注意的是，updateType=realtime暨为实时更新表时，必须指定合法的主键并且不能有二级分区。
列信息的基本格式为”列名 类型 COMMENT ‘注释’”，关于列的更多属性，请参照后文3.4节。”[primary key (col1, col3)]”一段指定了表的主键，如果表为批量更新的，则主键没有意义，而表为实时更新的，则必须指定主键并且主键中必须含有一级Hash分区列（可以是联合主键）。
关于分区方面，目前分析型数据库支持最多两级分区，并且一级分区仅支持HASH分区，二级分区仅支持LIST分区。HASH分区是一种动态分区值类型，暨根据实际数据中的某一列的内容进行分区。所以在语法上，一级HASH分区的用法是：<PRE prettyprinted? linenums>

1. PARTITION BY HASH KEY(col1)
2. PARTITION NUM 50

其中col1为需要进行分区的列名，必须是表中实际存在的列。’PARTITION NUM’为分区数量，一般根据该表的数据量确定，每个分区一般不超过1500万条记录为宜（亦可通过划分二级分区实现无限扩展）。另外HASH分区列的数据分布要尽可能均匀，不能有非常明显的倾斜（暨分区列值），否则会较严重的影响查询性能。
一定需要注意的一点：目前分析型数据库要求一个表组下所有表的一级分区数目一致，所以建立第一张表时指定一级分区数量时请谨慎。一级分区数量默认不能超过256个。
另外需要注意的是，若一张表仅有一级HASH分区并且是批量导入的表，则每次导入数据时会对已有进行全量覆盖。若需要每次导入数据时增量导入，则需要再指定二级List分区信息：<PRE prettyprinted? linenums>

1. SUBPARTITION BY LIST (part_col2 bigint)
2. SUBPARTITION OPTIONS (available_partition_num = 30)

二级List分区为非动态分区，暨分区值不是由数据本身决定的，而是由每次导入/写入数据时用户指定的。所以在进行分区信息定义时需要指定一个和现有数据中的列不同的新列名，以及这个列的类型（目前仅支持long）。二级分区有一个可选属性，available_partition_num，即为最大保留的二级分区数，当新的数据装载进来后，若线上存在的二级分区数大于这个值，那么会根据二级分区的值进行排序，下线最小的若干分区的数据。
0.8版本的实时更新表暂不支持二级分区，0.9版本支持。但是实时更新表的二级分区仅用于极大的扩展单表容量，以及进行生命周期管理，实时更新表的增量更新不依赖于二级分区。
惯常的用法是，将经常需要进行Join的列（例如买家ID）作为一级Hash分区列，而将日期列作为二级分区列。这样的表既可以进行大表Join的加速，又可以每天进行增量数据导入，并且指定保留若干天的数据在线上来进行生命周期管理。
但是，一级分区数量和二级分区数量的设置，并不是越多越好的。而是要看表的数据量，以及数据库拥有的资源数。若一级二级分区过多而数据库的资源数过少，则很容易让分区的数据Meta将内存耗尽。
CLUSTERED BY子句用于指定聚集列，用户可以把一列或者多列指定为聚集列，注意如果指定多列，那么该表的数据聚集顺序按照DDL中这个子句中指定的列组合顺序进行排序。通常，我们将一个表的查询中肯定会涉及到的并且数据区分度很大的列设置为聚集列，有时候能较显著的提升查询性能。
事实表的创建上，默认有如下限制：（1）一张事实表至少有一级Hash分区并且分区数不能小于8个；（2）一个事实表组最多可以创建256个事实表；（3）一个事实表最多不能超过1024个列。
与创建事实表相比，创建维度表要简单的多：<PRE prettyprinted? linenums>

1. CREATE DIMENSION TABLE db_name.dim_table_name (
2.     col1 int  comment 'col1',
3.     col2 varchar comment 'col2'
4.      [primary key (col1)]
5. )
6. [options (updateType='{realtime | batch}')]
7. ;

创建维度表时只需要指定数据库名和表名即可，维度表会创建到统一的维度表组，并且无需指定分区信息。
在表已经创建好之后，目前支持有限的修改：主要是支持增加列。<PRE prettyprinted? linenums>

1. ALTER TABLE db_name.table_name ADD column col_new varchar;

注意，批量导入表新增加的列在数据重新装载后才会生效，实时写入表新增列后，一般需要几分钟后才可以读写。
最大二级分区数目前可以在建表后进行修改，但是修改后的下一次数据导入发起后才会生效：<PRE prettyprinted? linenums>

1. ALTER TABLE db_name.table_name subpartition_available_partition_num = N

N为新的二级分区数。