全部产品
Search
文档中心

云原生数据仓库AnalyticDB MySQL版:CREATE TABLE

更新时间:Nov 23, 2023

云原生数据仓库AnalyticDB MySQL版支持通过CREATE TABLE创建表,也支持通过CTAS将查询到的数据写入新表中。

语法

CREATE TABLE [IF NOT EXISTS] table_name
  ({column_name column_type [column_attributes] [ column_constraints ] [COMMENT 'string']
  | table_constraints}
   [, ... ]  )
   table_attribute
   [partition_options]
   [storage_policy]
   [block_size]
   [engine]
   [rt_engine]
   [table_properties]
[AS] query_expression
   COMMENT 'string'

column_attributes:
   [DEFAULT default_expr]
   [AUTO_INCREMENT]

column_constraints:
   [{NOT NULL|NULL} ]
   [PRIMARY KEY]

table_constraints:
   [{INDEX|KEY} [index_name] (column_name,...)]
   [PRIMARY KEY [index_name] (column_name,...)]
   [CLUSTERED KEY [index_name] (column_name,...)]
   [[CONSTRAINT [symbol]] FOREIGN KEY  (fk_col_name) REFERENCES tbl_name (pk_col_name)]

table_attribute:
   DISTRIBUTED BY HASH(column_name,...) | DISTRIBUTED BY BROADCAST

partition_options:
  PARTITION BY 
        {VALUE(column_name) | VALUE(date_format(column_name, ?))}
  LIFECYCLE N

storage_policy:
  STORAGE_POLICY= 'HOT'|'COLD'|'MIXED' [hot_partition_count=N]

block_size:
  BLOCK_SIZE= VALUE

engine:
  ENGINE= 'XUANWU'

rt_engine:
  RT_ENGINE= 'COLUMNSTORE'

table_properties:
  TABLE_PROPERTIES = '{"format":"columnstore"}'

参数

参数

说明

table_name

表名。

表名以字母或下划线(_)开头,可包含字母、数字以及下划线(_),长度为1到127个字符。

支持db_name.table_name格式,区分不同数据库下相同名字的表。

column_name

列名。

列名以字母或下划线(_)开头,可包含字母、数字以及下划线(_),长度为1到127个字符。

column_type

要添加的列的数据类型。

AnalyticDB MySQL版支持的数据类型,请参见基础数据类型

column_attributes

  • DEFAULT default_expr:设置列的默认值,DEFAULT为无变量表达式,例如current_timestamp

    如果未指定默认值,则列的默认值为NULL

  • AUTO_INCREMENT:定义自增列,可选项。

    自增列的数据类型必须是BIGINT类型,AnalyticDB MySQL版为自增列提供唯一值,但自增列的值不是顺序递增。不支持设置AUTO_INCREMENT从1开始。

column_constraints

  • NOT NULL|NULL:定义了NOT NULL的列不允许值为NULL;定义了NULL(默认值)的列允许值为NULL

  • PRIMARY KEY:定义主键。

    如果有多个主键,语法为PRIMARY KEY(column_name [, ... ])

table_constraints

说明

云原生数据仓库AnalyticDB MySQL版不支持创建唯一索引。

  • INDEX|KEY:定义索引。

    AnalyticDB MySQL版默认为表创建全索引,一般情况下无须手动创建索引。如果您指定了索引,则AnalyticDB MySQL版不会自动创建索引。

  • PRIMARY KEY:定义主键索引。

    • 只有定义过主键的表支持DELETE和UPDATE操作。

    • 主键中必须包含分布键和分区键,建议将分区键和分布键放在组合主键的前部。

  • CLUSTERED KEY:聚集索引,定义表中的排序列,聚集索引中键值的逻辑顺序决定了表中相应行的物理顺序。

    例如,clustered key col5_col6_cls_index(col5,col6)定义了col5 col6的聚集索引,col5 col6col6 col5是不同的聚集索引。

    聚集索引会将该列或者多列进行排序,保证与该列相同或者相近的数据存储在磁盘的相同或相近位置。当以聚集列做为查询条件时,查询结果存储在磁盘的相同位置,这样可以减少磁盘的IO,提高查询性能。

    如何判断是否需要聚集索引:查询一定会携带的字段可以作为聚集索引。例如,SaaS类应用中,用户通常只访问自己的数据,用户ID可以定义为聚集索引,保证数据的局部性,提升数据查询性能。

    聚集列有以下限制:

    • 每张表中只支持创建一个聚集列索引。

    • 由于聚集索引会进行全表排序,导致数据写入性能下降、CPU占用较高,因此一般不建议使用聚集索引。

    • 定义了聚集索引列的表,通过SHOW CREATE TABLE查询建表语句时,不会展示CLUSTERED KEY信息。

table_attribute

  • DISTRIBUTED BY HASH(column_name,...):在普通表中定义表的分布键,按照column_name的HASH值进行分片。

    AnalyticDB MySQL版支持将多个字段作为分布键。

    AnalyticDB MySQL版不支持修改分布键。如果需要修改分布键,请参见更改分区键/分布键

  • DISTRIBUTED BY BROADCAST:定义维度表,维度表会在集群的每个节点存储一份数据,因此建议维度表的数据量不宜太大。

partition_options

PARTITION BY:普通表中定义分区。

通过LIFECYCLE N方式实现表生命周期管理,即对分区进行排序,超出N的分区将被过滤掉。

可以通过如下语句,查询表的分区信息:

SELECT shard_name, table_name, partition_id, row_count
FROM INFORMATION_SCHEMA.KEPLER_CSTORE_PARTITION_XIHE_MERGED
WHERE table_name like upper("%中文字符改成表名%")
ORDER BY row_count DESC;

例如,PARTITION BY VALUE(column_name)表示使用column_name的值来做分区,PARTITION BY VALUE(DATE_FORMAT(column_name, '%Y%m%d'))表示将column_name格式化为类似20190101的日期格式做分区。LIFECYCLE 365表示每个节点最多保留的分区个数为365,即如果数据保存天数为365天,则第366天写入数据后,系统会自动删除第1天写入的数据。

说明

AnalyticDB MySQL版集群的节点数说明:

  • 数仓版(3.0)预留模式集群版集群的节点数=集群列表页显示的节点组个数。

  • 数仓版(3.0)弹性模式集群版(新版)集群的节点数=EIU数。

  • 湖仓版(3.0)集群的节点数=存储预留资源的组数(一组存储预留资源约等于24 ACU)。

AnalyticDB MySQL版不支持修改分区键。如果需要修改分区键,请参见更改分区键/分布键

说明
  • 二级分区不是整张表级,而是每个Shard级别。如果数据分布不均,则会导致整张表保留的二级保存分区数大于N。

  • 二级分区不是实时清理的,是后台异步任务清理的。

  • 当您使用PARTITION BY指定二级分区时,必须定义LIFECYCLE N,否则会报错。若不指定二级分区,数据不会被清理。

storage_policy

说明

目前仅弹性模式集群版(新版)实例支持冷热数据分层存储功能。

STORAGE_POLICY:指定热(HOT)、冷(COLD)、或混合(MIXED)的存储策略。默认值为HOT。

不同存储策略下数据读写性能不同,存储成本不同。为了降低数据存储成本,同时还要保证查询性能,您可以选择将查询频度高的数据(称为热数据)存储在SSD介质;将查询频度低的数据(称为冷数据)存储在OSS上。

根据业务需求,您还可以按表粒度、表的二级分区粒度独立选择冷、热存储介质。例如,指定这个表数据全部存储在SSD,或者全部存储在OSS,或者指定这个表的一部分二级分区存储在SSD,另一部分二级分区存储在OSS。

  • HOT、COLD、MIXED大小写兼容。

  • HOT: 所有分区都在SSD。

  • COLD: 所有分区都在OSS。

  • MIXED: 部分分区在SSD,部分分区在OSS,需要通过hot_partition_count指定存在SSD上的分区的数量。

hot_partition_count=N

指定MIXED存储策略时热分区的个数。表示按分区键的值的大小倒序排列,最大N个分区为热分区,其他分区为冷分区。

  • N为非零正整数。

  • 指定MIXED策略时,必须同时指定热分区的个数;其他策略禁止指定hot_partition_count=N

block_size

指定列式存储中每个block存储的Value的个数,也是最小的IO单元,默认值取值说明:

  • 弹性模式集群版(新版)且计算资源为32核以下(不包括32核)的集群block_size默认值为8192。

  • 维度表的block_size默认值为4096。

  • 其它情况下block_size默认值为32760。当block_size为32760,在SHOW CREATE TABLE <table_name>;时,block_size不做显示。

调大或调小会使得每次IO读取的Value个数变大或变小,具体产生的影响需要结合查询特征,例如点查询时,若block_size较大,存储读block的效率会降低,此时可以适当调小block_size。

重要

若不熟悉列式存储原理,建议不要进行更改。

engine

AnalyticDB MySQL版内表的存储引擎类型,固定取值为XUANWU

关于玄武存储引擎的详细信息,请参见玄武分析型存储

rt_engine

指定实时存储引擎类型。取值为COLUMNSTORE。当表的实时存储引擎为COLUMNSTORE时,SHOW CREATE TABLE时不会展示RT_ENGINE信息。

table_properties

指定AnalyticDB MySQL版内表全量数据的格式为列存,固定取值为{"format":"columnstore"}

说明

3.1.9.5以下内核版本的集群,如果在创建内表时显式指定了engine=XUANWU,则需同时显示指定"format":"columnstore,否则建表会失败。

注意事项

  • 创建表时,AnalyticDB MySQL版集群默认编码格式为utf-8,相当于MySQL中的utf8mb4编码,暂不支持其他编码格式。

  • AnalyticDB MySQL版不支持unsigned约束。

  • 目前AnalyticDB MySQL版集群支持创建的最大表数量如下。

    • 每个预留模式集群(具备1~20个节点组)中表数量的最大值:80000/(Shard个数/节点组数)张。

    • 每个弹性模式集群中内表数量的最大值:[80000/(Shard个数/EIU个数)]*2张。

    • 每个弹性模式集群中外表数量的最大值:50万张。

    说明
    • 执行SQL语句查询Shard个数:SELECT count(1) FROM information_schema.kepler_meta_shards;

    • 公式中的Shard个数/节点组数Shard个数/EIU个数向上取整。

    • 扩容节点组数量可以增加预留模式集群表数量的最大值,扩容节点组请参见数仓版扩缩容与跨规格变配

    • 扩容EIU数量可以增加弹性集群内表数量的最大值,扩容EIU请参见弹性IO资源(EIU)扩容

示例

  • 新建普通表test,id为自增列,分布键为id,按照id值进行HASH分区。

    CREATE TABLE test (
           id bigint auto_increment,
           name varchar,
           value int,
           ts timestamp
    )
    DISTRIBUTED BY HASH(id);
  • 新建普通表customer, customer_id为分布键,login_time为分区键,并按login_time排序,数据保存天数为30天,则第31天写入数据后,系统会自动删除第1天写入的数据,login_timecustomer_idphone_num为组合主键。

    CREATE TABLE customer (
    customer_id bigint NOT NULL COMMENT '顾客ID',
    customer_name varchar NOT NULL COMMENT '顾客姓名',
    phone_num bigint NOT NULL COMMENT '电话',
    city_name varchar NOT NULL COMMENT '所属城市',
    sex int NOT NULL COMMENT '性别',
    id_number varchar NOT NULL COMMENT '身份证号码',
    home_address varchar NOT NULL COMMENT '家庭住址',
    office_address varchar NOT NULL COMMENT '办公地址',
    age int NOT NULL COMMENT '年龄',
    login_time timestamp NOT NULL COMMENT '登录时间',
    PRIMARY KEY (login_time,customer_id,phone_num)
     )
    DISTRIBUTED BY HASH(customer_id)
    PARTITION BY VALUE(DATE_FORMAT(login_time, '%Y%m%d')) LIFECYCLE 30
    COMMENT '客户信息表';                   
  • 新增一个名为store_returns的表,通过使用外键语法FOREIGN KEYsr_item_sk列和customer表的主键列customer_id关联起来。

    CREATE TABLE store_returns
    (
      sr_sale_id bigint,
      sr_store_sk bigint,
      sr_item_sk bigint NOT NULL,
      FOREIGN KEY (sr_item_sk) REFERENCES item (customer_id)
    );

MySQL语法兼容性说明

AnalyticDB MySQL版标准建表语法中必须包含DISTRIBUTED BY ...,而MySQL建表语法中没有DISTRIBUTED BY ...AnalyticDB MySQL版默认兼容MySQL建表语法,您可以根据实际情况通过以下两种方式处理DISTRIBUTED BY ...不兼容问题。

  • 如果MySQL表含有主键,AnalyticDB MySQL版默认将主键作为DISTRIBUTED BY COLUMN

    CREATE TABLE t (c1 bigint, c2 int, c3 varchar, PRIMARY KEY(c1,c2));
    SHOW CREATE TABLE t;
    +-------+---------------------------------------------------------------------------+
    | Table | Create Table                                                              |
    +-------+---------------------------------------------------------------------------+
    | t     | Create Table `t` (
     `c1` bigint,
     `c2` int,
     `c3` varchar,
     primary key (c1,c2)
    ) DISTRIBUTED BY HASH(`c1`,`c2`) INDEX_ALL='Y' |
    +-------+----------------------------------------------------------------------------+
    1 row in set (0.04 sec)
  • 如果MySQL表不含主键,AnalyticDB MySQL版将添加一个__adb_auto_id__字段作为主键和DISTRIBUTED BY COLUMN

    CREATE TABLE t (c1 bigint, c2 int, c3 varchar);
    SHOW CREATE TABLE t;
    +-------+----------------------------------------------------------------------------------------------+
    | Table | Create Table                                                                                 |
    +-------+----------------------------------------------------------------------------------------------+
    | t     | Create Table `t` (
     `c1` bigint,
     `c2` int,
     `c3` varchar,
     `__adb_auto_id__` bigint AUTO_INCREMENT,
     primary key (__adb_auto_id__)
    ) DISTRIBUTED BY HASH(`__adb_auto_id__`) INDEX_ALL='Y' |
    +-------+----------------------------------------------------------------------------------------------+
    1 row in set (0.04 sec)

创建表时指定冷热数据存储策略

说明

目前仅弹性模式集群版(新版)实例支持冷热数据分层存储功能。

语法

CREATE TABLE [IF NOT EXISTS] table_name
  ({column_name column_type [column_attributes] [ column_constraints ] [COMMENT 'string']
  | table_constraints}
   [, ... ]  )
   table_attribute
   [partition_options]
   [storage_policy]
   [AS] query_expression
   COMMENT 'string'
   
storage_policy:
   STORAGE_POLICY= 'HOT'|'COLD'|'MIXED' [hot_partition_count=N]

示例

  • 创建表时指定冷(COLD)存储策略

    CREATE TABLE test_table (
     L_ORDERKEY bigint NOT NULL,
     L_LINENUMBER int NOT NULL,
     L_SHIPDATE date NOT NULL,
     dummy varchar,
     primary key (l_orderkey,l_linenumber,l_shipdate)
    ) DISTRIBUTE BY HASH(l_orderkey) 
    PARTITION BY VALUE(date_format(l_shipdate, '%Y%m')) LIFECYCLE 200 INDEX_ALL='Y' 
    STORAGE_POLICY='COLD';
  • 创建表时指定热(HOT)存储策略

    CREATE TABLE test_table (
     L_ORDERKEY bigint NOT NULL,
     L_LINENUMBER int NOT NULL,
     L_SHIPDATE date NOT NULL,
     dummy varchar,
     primary key (l_orderkey,l_linenumber,l_shipdate)
    ) DISTRIBUTE BY HASH(l_orderkey) 
    PARTITION BY VALUE(date_format(l_shipdate, '%Y%m')) LIFECYCLE 200 INDEX_ALL='Y' 
    STORAGE_POLICY='HOT';
  • 创建表时指定混合(MIXED)存储策略,同时指定热分区数量为16个

    CREATE TABLE test_table (
     L_ORDERKEY bigint NOT NULL,
     L_LINENUMBER int NOT NULL,
     L_SHIPDATE date NOT NULL,
     dummy varchar,
     primary key (l_orderkey,l_linenumber,l_shipdate)
    ) DISTRIBUTE BY HASH(l_orderkey) 
    PARTITION BY VALUE(date_format(l_shipdate, '%Y%m')) LIFECYCLE 200 INDEX_ALL='Y' 
    STORAGE_POLICY='MIXED' HOT_PARTITION_COUNT=16;

创建向量索引表

创建向量索引表的语法、参数说明以及示例,请参见创建向量索引